Advanced Databases

1. Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package

2. Anonymous PL/SQL Programs • Write code in text editor, execute it in SQL*Plus • Code can be stored as text in file system • Program cannot be called by other programs, or executed by other users • Cannot accept or pass parameter values

3. Named PL/SQL Programs • Can be created: • Using text editor & executed in SQL*Plus • Using Procedure Builder • Can be stored: • As compiled objects in database • As source code libraries in file system • Can be called by other programs • Can be executed by other users

4. Named Program Locations • Server-side • Stored in database as database objects • Execute on the database server • Client-side • Stored in the client workstation file system • Execute on the client workstation

5. Named Program Types • Program Units (client or server-side) • Procedures • Functions • Libraries (client-side) • Packages (client or server-side) • Triggers (server-side)

6. Program Units • Procedures • Can receive and pass multiple parameter values • Can call other program units • Functions • Like procedures, except they return a single value

7. Parameters • Variables used to pass data values in/out of program units • Declared in the procedure header • Parameter values are passed when the procedure is called from the calling program

8. Parameter Modes • IN • Incoming values, read-only (default) • OUT • Outgoing values, write-only • IN OUT • Can be both incoming and outgoing

9. Creating a Procedure CREATE OR REPLACE PROCEDURE procedure_name (parameter1 mode datatype, parameter2 mode datatype, … ) IS | AS local variable declarations BEGIN program statements EXCEPTION exception handlers END; header body

10. Executing a Procedure EXECUTE procedure_name (parameter1_value, parameter2_value, …);

11. Parameter Types • Formal parameters: declared in procedure header • Actual parameters: values placed in parameter list when procedure is called • Values correspond based on order Procedure Header: Formal Parameters PROCEDURE calc_gpa (s_id IN NUMBER, term_id IN NUMBER, gpa OUT NUMBER); Procedure Call: Actual Parameters calc_gpa (current_s_id, 4, current_gpa);

12. Dropping A Procedure • DROP PROCEDURE proc_name

13. Creating a Function CREATE OR REPLACE FUNCTION function_name (parameter1 mode datatype, parameter2 mode datatype, … ) RETURN function_return_data_type IS local variable declarations BEGIN program statements RETURN return_value; EXCEPTION exception handlers RETURN EXCEPTION_NOTICE; END; header body

14. Function Syntax Details • RETURN command in header specifies data type of value the function will return • RETURN command in body specifies actual value returned by function

15. Calling a Function • Can be called from either named or anonymous PL/SQL blocks • Can be called within SQL queries return_value := function_name(parameter1_value, parameter2_value, …);

16. Stored Program UnitObject Privileges • Stored program units exist as objects in your database schema • To allow other users to use your units, you must grant them EXECUTE privileges GRANT EXECUTE ON unit_name TO username;

17. Using Stored Program UnitsThat Belong to Other Users • You must have been granted the privilege to use it • You must preface the unit name with the owner’s username return_value := LHOWARD.my_function TO_DATE(’07/14/1958’, ‘MM/DD/YYYY’);

18. Calling Procedures From Other Procedures • Use procedure name followed by parameter list procedure_name (parameter1_value, parameter2_value, …);

19. Example// Procedure that prints all employees for a given department number Create or replace PROCEDURE Get_emp_names (Dept_num IN NUMBER) IS Emp_name VARCHAR2(30); CURSOR c1 (Depno NUMBER) IS SELECT Empname FROM Emp WHERE deptno = Depno;BEGIN OPEN c1(Dept_num); LOOP FETCH c1 INTO Emp_name; EXIT WHEN C1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(Emp_name); END LOOP; CLOSE c1; END;

20. Example// Function that returns the monthly salary for an employee CREATE OR REPLACE FUNCTION MYFUNC1 (emp_no integer) RETURN NUMBER IS Monthly_sal NUMBER(10,2); BEGIN SELECT round (annsalary/12) INTO Monthly_sal FROM Emp WHERE empno = emp_no; RETURN (Monthly_sal); END MYFUNC1; /

21. Packages • Server-side code library • Can contain: • Global variable declarations • Cursors • Procedures • Functions

22. Package Components • Specification • Used to declare all public variables, cursors, procedures, functions • Body • Contains underlying code for procedures and functions • Rationale: • Specification is visible to users, gives details on how to use • Body is not visible, users don’t care about details

23. Creating a Package Specification in SQL*Plus CREATE OR REPLACE PACKAGE package_name IS --public variables variable_name datatype; --program units PROCEDURE procedure_name (parameter_list); FUNCTION function_name (parameter_list); END;

24. Creating a Package Specification in SQL*Plus

25. Creating a Package Body in SQL*Plus CREATE OR REPLACE PACKAGE BODY package_name IS private variable declarations program unit blocks END;

26. Creating a Package Body in SQL*Plus

27. Calling a Program Unit That Is In a Package • Preface the program unit name with the package name PACKAGE_NAME.program_unit_name(parameter_list); • Example: DBMS_OUTPUT.PUT_LINE(‘Hello World’);

28. Overloading Program Units in Packages • Overloading • Multiple program units have the same name, but accept different input parameters • Allows user to use the same command to perform an action but pass different parameter values

29. Package Specification With Overloaded Procedures