PL/SQL

1. PL/SQL

2. Procedural Language for SQL (PL/SQL) is an extension of Oracle SQL • The basic intent of PL/SQL is • increase the expressiveness of SQL • process query results in a tuple-oriented way • develop modular database application programs • reuse program code, and • reduce the cost for maintaining and changing applications. • The basic construct of PL/SQL is a block • constants and variables can be declared, and variables can be used to store query results. Statements in a PL/SQL block include: • SQL statements • Control structures (loops) • Condition statements (if-then-else) • Exception handling • Calls of other PL/SQL blocks.

3. Each block builds a (named) program unit, and blocks can be nested. • The structure of a PL/SQL looks as follows: (brackets [ ] enclose optional parts) [<Block header>] [declare <Constants> <Variables> <Cursors> <User defined exceptions>] begin <PL/SQL statements> [exception <Exception handling>] end;

4. Consider the following simple code: Needed to display output Our local variable of data type DATE Get today’s date and store it in our variable Display the contents of the variable Program Output

5. Numeric operations in PL/SQL:

6. Bind Variables: • Host variables: • Declared in the SQL “Host Environment” • Remain active for the length of the SQL Session • Can be displayed with the SQL Print Command • Can only be accessed in the program when prefaced with a colon (:)

7. Interactive Input:

8. Control Structures: IF-THEN:

9. Control Structures: IF-THEN-ELSE:

10. Control Structures: IF-THEN-ELSIF: (Note Spelling)

11. Control Structures: Basic Loop:

12. Control Structures: While:

13. Control Structures: For:

14. SQL in PL: • We must be careful about single record/field queries and multiple return queries

15. Single Return Queries: The data type applied to field studentname is automatically applied

16. Multiple Record Queries:

17. Remember our problem about calculating a student grade? • Variable Declarations: /* This program calculates a grade */ declare cursor studentgrade is select lastname, firstname, quiz1, quiz2, quiz3 from grades; studentlastname grades.lastname%type; studentfirstname grades.firstname%type; q1 grades.quiz1%type; q2 grades.quiz2%type; q3 grades.quiz3%type; average number; sgrade grades.grade%type; nblanks number; blanks char(5);

18. Remember our problem about calculating a student grade? begin dbms_output.put_line('Student Name Grade'); dbms_output.put_line('------------------ -----'); open studentgrade; loop fetch studentgrade into studentlastname, studentfirstname, q1, q2, q3; exit when studentgrade%notfound; average := (q1 + q2 + q3)/3; nblanks := 20 - (length(trim(studentfirstname)) + length(trim(studentlastname))); if average >= 90 then sgrade := 'A'; elsif average >= 80 then sgrade := 'B'; elsif average >= 70 then sgrade := 'C'; elsif average >= 60 then sgrade := 'D'; else sgrade := 'F'; end if; dbms_output.put_line(trim(studentfirstname) || ' ' || trim(studentlastname) || lpad(' ',nblanks,' ') || sgrade); update grades set grade = sgrade; end loop; close studentgrade; end;

19. Remember our problem about calculating a student grade?

20. Exceptions: • Act as error handling routines

21. Types of Exceptions: Exception Name Description No_data_found Single row select returned no data Too_Many_rows Single row select returned multiple rows Zero_Divide Attempt to divide by zero Value_Error Arithmetic, Conversion, Truncation error Storage_Error PL/SQL ran out of memory or memory corrupted Login_Denied Invalid Username or password Program_Error Run Time error Access_Into_Null Attempt to assign values to uninitialized object Invalid_Cursor Illegal cursor operation Rowtype_Mismatch Cursor variable involved in incompatible return types --- And Others ---

22. Procedures: Remember our Grading program? • We could have created it as stored Procedure:

23. To run the program:

24. Functions, which are called by procedures, can also be created and stored:

25. To call the function:

26. Triggers: • A stored block which is implicitly called when an event occurs • A triggering event is based on a Data Manipulation Language statement such as: • INSERT • UPDATE • DELETE • Execution of the trigger is known as firing the trigger

27. Recall our problem about determining whether or not a student had enrolled for two classes at the same time • Information about students in a class was found only in the table enrollment • Information about when a class met was found only in the table class • If the information were in one table we could apply a constraint which would not allow a student to enroll in both classes:

28. However, even if we had created this table, it still would not stop a student from enrolling in two classes that meet at the same time • Enrollment in a class is done by entering a record in table enrollment (not table temp_table) • One way to stop dual enrollment is to set a trigger which tries to insert the record (from enrollment) into table temp_table (which contains the constraint) • If the record can be inserted into temp_table, it will then be inserted into table enrollment • If the record can NOT be inserted into temp_table, it will NOT be inserted into table enrollment

29. The trigger might appear as: • FOR EACH ROW is a row trigger which fires once for each row inserted: • :NEW refers to the new record to be inserted

30. Suppose we look up the Spring 2003 (semester = 102) schedule for Yao Ming (studentid = 21098765): • There is one other class that meets at the same time

31. If we now try and enroll Yao for the other class: