![Page 1: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/1.jpg)
Advanced Databases
Advanced PL/SQL Programming:Procedure, Function and Package
![Page 2: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/2.jpg)
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
![Page 3: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/3.jpg)
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
![Page 4: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/4.jpg)
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
![Page 5: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/5.jpg)
Named Program Types
• Program Units (client or server-side)– Procedures– Functions
• Libraries (client-side)
• Packages (client or server-side)
• Triggers (server-side)
![Page 6: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/6.jpg)
Program Units
• Procedures– Can receive and pass multiple parameter
values– Can call other program units
• Functions– Like procedures, except they return a single
value
![Page 7: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/7.jpg)
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
Parameters
![Page 8: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/8.jpg)
Parameter Modes
• IN– Incoming values, read-only (default)
• OUT– Outgoing values, write-only
• IN OUT– Can be both incoming and outgoing
![Page 9: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/9.jpg)
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
![Page 10: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/10.jpg)
Executing a Procedure
EXECUTE procedure_name
(parameter1_value, parameter2_value, …);
![Page 11: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/11.jpg)
Formal parameters: declared in procedure header
Actual parameters: values placed in parameter list when procedure is called
Values correspond based on order
Procedure Header:PROCEDURE calc_gpa (s_id IN NUMBER, term_id IN NUMBER, gpa OUT NUMBER);
Procedure Call:calc_gpa (current_s_id, 4, current_gpa);
Parameter Types
Formal Parameters
Actual Parameters
![Page 12: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/12.jpg)
Dropping A Procedure
• DROP PROCEDURE proc_name
![Page 13: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/13.jpg)
Creating a Function
CREATE OR REPLACE FUNCTION function_name (parameter1 mode datatype,
parameter2 mode datatype, … ) RETURN function_return_data_typeIS local variable declarationsBEGIN program statements RETURN return_value;EXCEPTION exception handlers
RETURN EXCEPTION_NOTICE;END;
header
body
![Page 14: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/14.jpg)
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
![Page 15: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/15.jpg)
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, …);
![Page 16: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/16.jpg)
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;
![Page 17: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/17.jpg)
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’);
![Page 18: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/18.jpg)
Calling Procedures From Other Procedures
• Use procedure name followed by parameter list
procedure_name
(parameter1_value, parameter2_value, …);
![Page 19: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/19.jpg)
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;
Example// Procedure that prints all employees for a given department number
![Page 20: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/20.jpg)
Example// Function that returns the monthly salary for an employee
CREATE OR REPLACE FUNCTION MYFUNC1 (emp_no integer)RETURN NUMBERIS
Monthly_sal NUMBER(10,2);BEGIN SELECT round (annsalary/12) INTO Monthly_sal FROM Emp WHERE empno = emp_no;RETURN (Monthly_sal);
END MYFUNC1;/
![Page 21: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/21.jpg)
Packages
• Server-side code library
• Can contain:– Global variable declarations– Cursors– Procedures– Functions
![Page 22: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/22.jpg)
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
![Page 23: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/23.jpg)
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;
![Page 24: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/24.jpg)
Creating a Package Specification in SQL*Plus
![Page 25: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/25.jpg)
Creating a Package Body in SQL*Plus
CREATE OR REPLACE PACKAGE BODY package_name IS private variable declarations program unit blocksEND;
![Page 26: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/26.jpg)
Creating a Package Body in SQL*Plus
![Page 27: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/27.jpg)
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’);
![Page 28: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/28.jpg)
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
![Page 29: Advanced Databases Advanced PL/SQL Programming: Procedure, Function and Package](https://reader036.vdocuments.us/reader036/viewer/2022081503/56649e195503460f94b06833/html5/thumbnails/29.jpg)
Package Specification With Overloaded Procedures