Chapter 9: Advanced SQL and PL/SQL

1. Chapter 9: Advanced SQL and PL/SQL Guide to Oracle 10g

2. Lesson A Objectives After completing this lesson, you should be able to: • Work with PL/SQL stored units Guide to Oracle 10g

3. PL/SQL Stored Program Units • A program unit is a self-contained group of program statements that can be used within a large program • PL/SQL part written in the form builder that may be called by a form trigger is an example of a unit. • The unit you created and executed so far in a form builder or SQL*Plus are anonymous PL/SQL programs which are • Programs submitted to the interpreter and run • Do not interact with other program units • They only exist within forms Guide to Oracle 10g

4. PL/SQL Stored Program Units • Stored PL/SQL program units are: • program units that other PL/SQL program can reference, • and that other database users can use and execute. • What do stored units can do? • It can receive input values from other program units • It can pass output values to other program units • Stored unit can be linked to database application components created in Form builder to enhance their functionality. Guide to Oracle 10g

5. PL/SQL Stored Program Units • It can be either • Server-side program units: stored as database object and execute on the sever (can be used by all users) • Client-side program units: stored in the file system of a client workstation and execute on the client workstation Guide to Oracle 10g

6. Creating Stored program Units • Stored program units can be either: • Procedures: • Can receive multiple input parameters and return multiple output values (or no output values) • Can perform an action such insert, update or delete record • Functions: • Can receive multiple input parameters and always returns a single output value Guide to Oracle 10g

7. Creating Procedures • CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter mod datatype [,parameter]) ] IS [local variable declaration_section] BEGIN executable_section [EXCEPTION exception_section] END [procedure_name]; Header Section body Exception section Guide to Oracle 10g

8. Creating Procedures Guide to Oracle 10g

9. Creating Procedures • The header section defines: • The procedure name (name should be unique ) • The parameters that the procedure receives or delivers (IN, OUT, IN OUT) • IS keyword: follows the parameters list • The local procedure variables • Create or replace: instructs the DBMS to create the procedure if it does it exists, otherwise; it replaces the existing one • OR REPLACE clause is optional. But if omitted and a procedure exists with the same name, an error occurs Guide to Oracle 10g

10. Creating Procedures (parameters list) • Parameter name, mod and data type are enclosed in parentheses, each is separated by a comma. • Parameter mod: describes how the procedure change the value. It can be: • IN: passed parameter is a Read only value. Cannot be changed by the procedure • OUT: write-only value. Always comes on the left side of an assignment statement • IN OUT: its value can be changed Guide to Oracle 10g

11. Creating Procedures (parameters list) • The data type cannot specify a length, precision, or scale value for a numerical data type, or maximum length of a character data type. • Oracle Database derives the length, precision, or scale of the return value from the environment from which the function is called. • When the procedure or function is created and debugged, it ca be called from any application. Guide to Oracle 10g

12. Creating Procedures (Example) • UPDATE_ENROLLMENT_GRADE: a procedure that updates a student’s grade for a course. • It accepts 3 input parameters (student ID, Course section ID and grade) • Upon successful, the stored procedure exists as an object in your schema. Guide to Oracle 10g

13. Creating Procedures (Example) NO Data Size • The (%ROWTYPE) reference data type creates composite variables that reference the entire data record. • Faculty_row FACULTY%ROWTYPE; • The variable faculty_row references all of the columns in the FACULTY table, and each column has the same data type as its associated DB column. Guide to Oracle 10g

14. Debugging program units (SQL*Plus) • Interpreter displays only a warning message. It does not contain a compiler error message nor a line location. • Instead, it writes all compiler errors to a system table accessed using USER_ERRORS data dictionary view. • It displays a summary listing of compile errors generated by the last program unit that was compiled. • Use the SHOW ERRORS command: • See Figure 9-12. Guide to Oracle 10g

15. Calling a Stored Procedure • Execute directly from SQL*Plus command line • Create separate PL/SQL program that contains • Command to call stored procedure • Passes parameter values to procedure • Calling stored procedure from SQL*Plus command line: EXECUTE procedure_name (parameter1_value, parameter2_value, ...); Execute update_enrollment_grade(‘MA100’, 12, ‘B’); • Single quote for character is important. • Variables passed for each parameter must be in same order as parameters appear in parameter declarations list Guide to Oracle 10g

16. Calling a Stored Procedure • Calling stored procedure from separate PL/SQL program • Similar to calling stored procedure from SQL*Plus command line • Omit EXECUTE command update_enrollment_grade(‘MA100’, 12, ‘B’); Guide to Oracle 10g

17. Creating Functions • A function is similar to a procedure, except that it returns a single value. Guide to Oracle 10g

18. Creating Functions • The function name (name should be unique ) • The syntax of the parameter list is identical to that of the procedure. • After the parameters list, RETURN sentence determines the data type of the return value of the function. (remember that procedure does not return a value, so it contains NO Return statement) • After IS, the return_value_variable declares the variable that represents the function return value. Guide to Oracle 10g

19. Creating Functions Guide to Oracle 10g

20. Calling a Functions • Calling a function requires assignment the command to a previously declared variable in the calling program, since the function returns a single data value. variable_name := function_name(parameter1, parameter2, ...); • Variables passed for parameter values • Must be in same order as parameters appear in function declaration Guide to Oracle 10g

21. Calling a Functions • Pay attention to to_date function. Guide to Oracle 10g

22. End of Chapter 9: Advanced SQL and PL/SQL Guide to Oracle 10g