Advanced Databases Advanced PLSQL Programming Procedure Function and
Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package
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
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
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
Named Program Types • Program Units (client or server-side) – Procedures – Functions • Libraries (client-side) • Packages (client or server-side) • Triggers (server-side)
Program Units • Procedures – Can receive and pass multiple parameter values – Can call other program units • Functions – Like procedures, except they return a single value
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
Parameter Modes • IN – Incoming values, read-only (default) • OUT – Outgoing values, write-only • IN OUT – Can be both incoming and outgoing
Creating a Procedure CREATE OR REPLACE PROCEDURE procedure_name (parameter 1 mode datatype, parameter 2 mode datatype, … ) IS | AS local variable declarations BEGIN program statements EXCEPTION exception handlers body END; header
Executing a Procedure EXECUTE procedure_name (parameter 1_value, parameter 2_value, …);
Parameter Types § Formal parameters: declared in procedure header § Actual parameters: values placed in parameter list when procedure is called § Values correspond based on order Formal Parameters Procedure Header: PROCEDURE calc_gpa (s_id IN NUMBER, term_id IN NUMBER, gpa OUT NUMBER); Actual Parameters Procedure Call: calc_gpa (current_s_id, 4, current_gpa);
Dropping A Procedure • DROP PROCEDURE proc_name
Creating a Function CREATE OR REPLACE FUNCTION function_name (parameter 1 mode datatype, parameter 2 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
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
Calling a Function • Can be called from either named or anonymous PL/SQL blocks • Can be called within SQL queries return_value : = function_name(parameter 1_value, parameter 2_value, …);
Stored Program Unit Object 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;
Using Stored Program Units That 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’);
Calling Procedures From Other Procedures • Use procedure name followed by parameter list procedure_name (parameter 1_value, parameter 2_value, …);
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 VARCHAR 2(30); CURSOR c 1 (Depno NUMBER) IS SELECT Empname FROM Emp WHERE deptno = Depno; BEGIN OPEN c 1(Dept_num); LOOP FETCH c 1 INTO Emp_name; EXIT WHEN C 1%NOTFOUND; DBMS_OUTPUT. PUT_LINE(Emp_name); END LOOP; CLOSE c 1; END;
Example// Function that returns the monthly salary for an employee CREATE OR REPLACE FUNCTION MYFUNC 1 (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 MYFUNC 1; /
Packages • Server-side code library • Can contain: – Global variable declarations – Cursors – Procedures – Functions
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
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;
Creating a Package Specification in SQL*Plus
Creating a Package Body in SQL*Plus CREATE OR REPLACE PACKAGE BODY package_name IS private variable declarations program unit blocks END;
Creating a Package Body in SQL*Plus
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’);
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
Package Specification With Overloaded Procedures
- Slides: 29