CSC 453 Database Systems Lecture

1. CSC 453 Database SystemsLecture Tanu Malik College of CDM DePaul University

2. PL/SQL • A general-purpose procedural programming that includes SQL commands • PL/SQL can • create and issue SQL statements • store and process the results of queries • define procedures to respond to database events

3. Basic Structure of Code • Simplest form is an anonymous block: declare -- variable and subprogram declarations -- every statement must end with a ; begin -- PL/SQL statements to execute --every statement must end with a ; --statements can be nested with another B/E exception -- exception handling (optional) end;

4. Output • To display output:RAISE NOTICE ‘string %’, arguments; • Output buffer displayed in DBMS Output tab • Use View Dbms Output and ‘+’ to open tab • Single line comments with – • Multi-line with /* */

5. Data Types • Numeric • Character • Boolean • Datetime • Data types are not case sensitive DECLARE num1 INTEGER; num2 REAL; num3 DOUBLE PRECISION; BEGIN null; END; /

6. Declaring Variables • All variables must be declared: varName [CONSTANT] dataType [NOT NULL] [:= initialValue]; • Assignments use :=, and PL/SQL has typical arithmetic operations

7. Scoping DECLARE x NUMBER := 0; counter NUMBER := 0; BEGIN FOR i IN 1..4 LOOP x := x + 1000; counter := counter + 1; INSERT INTO temp VALUES (x, counter, 'in OUTER loop'); DECLARE x NUMBER := 0; BEGIN FOR i IN 1..4 LOOP x := x + 1; counter := counter + 1; INSERT INTO temp VALUES (x, counter, 'inner loop'); END LOOP; END; END LOOP; END;/

8. Declaring Variables • Only one variable can be declared per line, but variable types can be given in terms of the domain of another variable or attribute: varName otherVar%type; varName TABLE.Attribute%type;

9. Operators • Arithmetic operators • Relational operators • Comparison operators • LIKE, BETWEEN, IN, IS NULL • Logical operators • String operators

10. Branching • if-then: if condition then …’true’ statements…end if; • if-else:if condition then …’true’ statements…else …’false’ statements…end if;

11. Branching • if-elsif:if condition1 then … ‘true’ statements… elsif condition2 then … ‘false-true’ statements… elsif condition3 then … ‘false-false-true’ statements… (… as many times as needed…) else … ‘all false’ statements… end if;

12. Case Statement CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END

13. Case Statement • expression • Optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n) • condition_1, condition_2, ... condition_n • The conditions that must all be the same datatype. The conditions are evaluated in the order listed. Once a condition is found to be true, the CASE statement will return the result and not evaluate the conditions any further. • result_1, result_2, ... result_n • Results that must all be the same datatype. This is the value returned once a condition is found to be true.

14. Case Statement • If no condition is found to be true, then the CASE statement will return the value in the ELSE clause. • If the ELSE clause is omitted and no condition is found to be true, then the CASE statement will return NULL.

15. Case Statement -- Multiple if-then-else better expressed by CASE DECLARE grade CHAR(1); BEGIN grade := 'B'; CASE grade WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent'); WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good'); WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good'); WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair'); WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor'); ELSE DBMS_OUTPUT.PUT_LINE('No such grade'); END CASE; END; /

16. Loops • General loop:loop …loop body…end loop; • Repeats until exit; is executed in loop body • While loop:while condition loop …loop body…end loop; • Repeats until condition is false

17. Loops • For loop: for variable in [reverse] lower..upper loop …loop body… end loop; • Can only increment/decrement by one • lower always appears before upper in header

18. Incorporating SQL Queries • Result of a query can be stored in a set of variables by adding INTO clause to query: SELECT list of attributesINTO list of variablesFROM list of tables … • Variable types must match attribute types

19. Procedures (In Oracle) CREATE [OR REPLACE] PROCEDURE name (paramName IN [OUT] paramType …) AS …declarations…BEGIN …body of procedure…END;/ • ‘IN’ parameters are passed by value, for input only, read-only parameters • ‘OUT’ parameters are passed by reference • ‘IN OUT’ parameters are passed by reference, to return results to the calling sub-program

20. Functions CREATE [OR REPLACE] FUNCTION mode {IN|OUT|INOUT} name …) RETURNS returnType AS $$ …declarations…BEGIN …body of function…returnreturnValue;END;$$ language plpgsql; • ‘IN’ parameters are default • Specify return type and return value instead

21. Executing Procedures and Functions • A standalone procedure • Using the EXECUTE keyword • Calling the name of the procedure from a PL/SQL block • A standalone function • Calling the name of the function from a PL/SQL block • Calling the name of the function in a SQL query

22. Cursors • A cursor represents a pointer into a set of records returned by a query declare name cursor for query; • cursor name can be used to iterate through the records returned by query

23. Cursor Commands/Expressions • open name; -- initializes to beginning of set • fetch name into variableList; -- reads the next record into the variables • close name; -- closes the cursor

24. Parameterized Cursors • Can supply a parameter in cursor declaration and query declare name (parameter in type) cursorfor query; • Each time cursor is opened, value of parameter is specified in parentheses to complete the query

25. Implicit Cursors for DML statements • Whenever a DML statement (INSERT, UPDATE and DELETE) is issued, an implicit cursor is associated with this statement. • INSERT operations: the cursor holds the data that needs to be inserted. • UPDATE and DELETE operations: the cursor identifies the rows that would be affected.

26. Records • Data structure to hold data items of different kinds • Table-based Records: • Can create a record with same structure as the row of a table (fields are table attributes):recordNameTABLE%rowtype; • Can select a row of a table directly into a record, and access individual fields withrecordName.Attribute

27. Records • Cursor-based records: Assign rowtype from a query in cursor • User-defined Records: • Declare a new data type and a table of records: create type newType ( attr1 datatype, attr2 datatype )

28. Exceptions DECLARE <declarations section> BEGIN <executable command(s)> EXCEPTION <exception handling goes here > WHEN exception1 THEN exception1-handling-statements WHEN exception2 THEN exception2-handling-statements WHEN exception3 THEN exception3-handling-statements ........ WHEN others THEN exception3-handling-statements END;

29. Case Statement SELECT LastName, FirstName, (CASE Career WHEN 'UGRD' THEN 'Undergraduate' WHEN 'GRD' THEN 'Graduate' WHEN ' SAL' THEN 'Student At Large' END) AS Career FROM student; UPDATE employee SET salary = (CASE WHEN salary < 50000 THEN 50000 WHEN salary < 100000 THEN salary * 1.05 ELSE salary * 1.1 END);

30. Database Active Elements • Checks • Assertions • Triggers

31. Why Active Elements • Other wise application programs have to include checks with every DML statement to preserve data integrity. • Better to store checks in DBMS and let DBMS administer the checks.

32. Check • User-defined, verified when a tuple is added or updated • Attribute-level: • CHECK within an attribute, can only involve that attribute • Verified when a tuple is inserted/updated in that attribute • Tuple-level: • A separate CONSTRAINT, can involve any attributes • Verified when a tuple is inserted/updated in any attribute

33. Attribute-level Check create table enrolled ( StudentID number(5), CourseID number(4), Quarter varchar(6) CHECK(quarter in ('Fall','Winter','Spring')), Year number(4), … create table memberof ( StudentID number(5), GroupName varchar(40), Joined number(4) CHECK(Joined >= (SELECT Started FROM student WHERE studentID = SID)), ... has to be true (compare WHERE)
 attribute checks get evaluated when an attribute is modified i.e. when row is inserted/updated 
 subqueries not allowed in Oracle checks

34. Tuple-level CHECK create table course ( CID number(4), CourseName varchar(40), Department varchar(4), CourseNr char(3), primary key (CID), check (department <> 'CSC' OR CourseNR > 100) ); • same as attribute level check, just involves any number of attributes and different placement

35. Assertion • Boolean value SQL expression that is true at all times • Hard to implement CREATE ASSERTION joined CHECK (NOT EXISTS (SELECT * FROM student, memberof WHERE SID = StudentID and Joined < Started)); • not supported by anybody
 • can be mimicked using materialized views and/or triggers

36. Triggers • Triggers allow general responses to changes in the database state: • Enforcement of business rules • Notification of events • Maintenance of derived information • Maintenance of replicated data • Implementation of cross-table constraints

37. Event-Condition-Action Model • An event causes the trigger to fire • Before or after an insertion, deletion, or update • A condition is tested to see whether or not the trigger will respond (optional…) • May depend on original state or modified state • An action may be executed in response • May be sequence of SQL statements or some stored procedure

38. Oracle Trigger Syntax CREATE [OR REPLACE] TRIGGER NameBEFORE/AFTER INSERT/DELETE/UPDATE [OF Attribute] ON Table [REFERENCING OLD AS OldName NEW AS NewName] [FOR EACH ROW]WHEN (condition) BEGIN …PL/SQL statements or stored procedure…END;/

39. Oracle Trigger Syntax • BEFORE/AFTER • Indicates whether queries on TABLE will be performed on the original state of the table, or the modified state • INSERT/DELETE/UPDATE [OF Attribute] ON TABLE • Indicates what operation(s) will cause the trigger to fire

40. Oracle Trigger Syntax • REFERENCING OLD AS OldName, NEW AS NewName • Re-names old and new row states (only allowed if trigger is row-level…) • FOR EACH ROW • If included, the trigger fires once for each row that is modified (row-level); if not, the trigger fires just once for the entire table (statement-level)

41. Oracle Trigger Syntax • WHEN (condition) • Condition tested to see if the trigger action will actually execute – can refer to new and old row states • PL/SQL statements • The code that is executed when trigger fires and condition is satisfied; may call other stored procedures/functions

42. Trigger Restrictions • new and old can only refer to row states, so they can only be used for row-level triggers • Use new and old in WHEN condition, :new and :old elsewhere • Subqueries are not allowed in WHEN • PL/SQL block in a row-level trigger cannot query or modify the table that triggered the action

43. BEFORE vs AFTER • Order of trigger execution • All BEFORE execute in arbitrary order • …but before all AFTER triggers • Cannot modify :new values after UPDATE/INSERT

44. Triggers for Other Events • Trigger attached to DDL commands • E.g., BEFORE/AFTER DROP ON username.SCHEMA • Trigger attached to Views • INSTEAD OF INSERT ON [View] • Allows updates where they cannot be done automatically • Triggers attached to other events • CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE

45. Triggers CREATE OR REPLACE TRIGGER started BEFORE UPDATE OF started ON student FOR EACH ROW WHEN (new.started < old.started) BEGIN :new.started := :old.started; DBMS_OUTPUT.PUT_LINE('Rejected change of started'); END; / SET SERVEROUTPUT ON; UPDATE student SET Started = 2001; SELECT * FROM student;

46. Triggers Create trigger CREATE OR REPLACE TRIGGER started BEFORE UPDATE OF started ON student FOR EACH ROW WHEN (new.started < old.started) BEGIN :new.started := :old.started; DBMS_OUTPUT.PUT_LINE('Rejected change of started'); END; / triggering event attribute/table row trigger trigger restriction old: row before update new: row after update trigger action (in PL/SQL)

47. Triggering Events CREATE OR REPLACE TRIGGER started BEFORE UPDATE OF started ON student FOR EACH ROW WHEN (new.started < old.started) BEGIN :new.started := :old.started; DBMS_OUTPUT.PUT_LINE('Rejected change of started'); END; / When do we trigger: • before • after • instead of (only for views) What is doing the triggering: • insert, update, delete • system events

48. row/statement trigger WHEN only for row-level triggers CREATE OR REPLACE TRIGGER started BEFORE UPDATE OF started ON student FOR EACH ROW WHEN (new.started < old.started) BEGIN :new.started := :old.started; DBMS_OUTPUT.PUT_LINE('Rejected change of started'); END; / :new/:old only for row-level triggers vs CREATE OR REPLACE TRIGGER started AFTER UPDATE ON student BEGIN DBMS_OUTPUT.PUT_LINE(Student Table updated'); END; /

49. Restriction (WHEN) CREATE OR REPLACE TRIGGER started BEFORE UPDATE OF started ON student FOR EACH ROW WHEN (new.started < old.started) BEGIN :new.started := :old.started; DBMS_OUTPUT.PUT_LINE('Rejected change of started'); END; / • old (before change) • new (after change)

50. Trigger Action CREATE OR REPLACE TRIGGER started BEFORE UPDATE OF started ON student FOR EACH ROW WHEN (new.started < old.started) BEGIN :new.started := :old.started; DBMS_OUTPUT.PUT_LINE('Rejected change of started'); END; / • BEGIN pl/sql block END; / • :old, :new variables • dbms_output