Cascading

Relational Databases and Web Integration Week 9 [email protected]

Tuesday, 18 August 2009

Composition UML classes may be related by composition implies that child memory management is handled by parent How do we implement this in SQL? need to introduce cascading constraints into our tables

Tuesday, 18 August 2009

Cascading Normally when you delete a parent table, its referenced child tables are not deleted Cascading forces child tables to be deleted when their parent is deleted Cascade constraints are added to foreign key constraints ... on delete cascade

Tuesday, 18 August 2009

PL/SQL Procedural programming language Combines SQL commands with procedural programming Sequential access to Oracle database Strongly typed declare before use language <SQL type>;

Tuesday, 18 August 2009

Scalar Datatypes Database varchar2, char, date, long, number Non-database int, double, numeric, boolean

Tuesday, 18 August 2009

Composite Datatypes For referencing multiple data elements record, table, varray

Tuesday, 18 August 2009

Reference Datatypes Reference a database item Assume type of an item %type

datatype of field/column

%rowtype

Tuesday, 18 August 2009

datatype of entire row

Program Structure Declare * Begin * Exception * End;

Tuesday, 18 August 2009

Comments Multiline as for Java ie. /* and */ Single line, starts with double hypens ie. --

Tuesday, 18 August 2009

Assignment assignment symbol is colan-equals ie. := until a variable has a value assigned to it, its value is null functional evaluation using null is typically strict ie. null values are propagated Always initialize variables!!

Tuesday, 18 August 2009

Printing Strings First ensure output is enabled with the SQLPlus command: set serveroutput on size 4000;

Then use the following command to print: dbms_output.put_line(‘Hello World’);

Tuesday, 18 August 2009

String Concatenation

To concatenate two strings use the || operator given := ‘Carl’; surname := ‘Pulley’; fullname := given || ‘ ‘ || surname;

Tuesday, 18 August 2009

String Functions Trim - trims white space from the start and end of a string Length - returns length of a string argument Upper and Lower - converts a string into upper or lower case respectively

Tuesday, 18 August 2009

String Functions Instr(given, search) - starting position of a search string within a given string Substr(string, start, end) - returns a substring of string starting at position start and upto position end

Tuesday, 18 August 2009

Debugging

Simplest way is to add in print statements!

Tuesday, 18 August 2009

Conditionals if-then if then <program statement>* end if

if-then-else if then <program statement>* else <program statement>* end if

Tuesday, 18 August 2009

null Conditions Should an expression evaluate to null, it will be interpreted as a false boolean value values may arise because of variables that have not been initialized null

Tuesday, 18 August 2009

Pre-Test Loop Exit condition tested before loop body is executed While Loop <program statement>* End Loop For in <start value> .. <end value> Loop <program statement>* End Loop

Tuesday, 18 August 2009

Post-Test Loop exit condition is tested after loop body has been executed Loop <program statement>* exit when ; End Loop

Tuesday, 18 August 2009

Cursors Pointer to a server memory location the command context area cursor

context area active set

Tuesday, 18 August 2009

Number of rows processed CID 1 2 3 4 5

CallerID MIS 101 MIS 301 MIS 441 CS 155 MIS 451

Parsed command statement CName CCredi Intro. to Info. Systems 3 t System Analysis 3 Database Management 3 Programmin gin C++ 3 Client/Server Systems 3

Implicit Cursors Created automatically whenever an insert, update, select or delete command is used Used to assign output of a select command to a PL/SQL variable Can only be used if query returns exactly 1 result!

Tuesday, 18 August 2009

Implicit Cursors select , , .. into , , .. from where <single result query>;

Tuesday, 18 August 2009

Explicit Cursors Must be declared in PL/SQL declare section Again, allows output of a select query may be assigned to a PL/SQL variable Can be used if query returns zero or more results

Tuesday, 18 August 2009

Explicit Cursor Steps Declare cursor cursor <cursor name> is <select statement>;

Open cursor open <cursor name>;

Fetch query result into cursor fetch <cursor name> into *;

Close the cursor close <cursor name>;

Tuesday, 18 August 2009

Cursor Attributes - true precisely when their are now rows to fetch %NotFound

%Found

- inverse of %NotFound

%RowCount

- number of rows a cursor has so far

fetched %IsOpen

Tuesday, 18 August 2009

- true precisely if the cursor is open

Reference Datatypes Declaring a row type reference variable Declare <cursor variable>%RowType

Referencing a row type reference variable .

Tuesday, 18 August 2009

Example Declare emp_name VarChar2(10); Cursor c1 Is select ename from emp_tab where deptno = 20; Begin Open c1; Loop Fetch c1 Into emp_name; -- debugging line dbms_output.put_line(‘Employers name: ’ || emp_name); Exit When c1%NotFound; End Loop; End;

Tuesday, 18 August 2009

Exceptions Some exceptions have pre-defined names All exceptions have error numbers when <exception nos/name> then <program statement>* when <exception nos/name> then <program statement>* .. when others then <program statement>*

Tuesday, 18 August 2009