1 / 142

CSC 453 Database Systems Lecture

CSC 453 Database Systems Lecture. Tanu Malik College of CDM DePaul University. 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.

Download Presentation

CSC 453 Database Systems Lecture

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related