Transcript PL/SQL2

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
(parameter1 mode datatype,
parameter2 mode datatype, …
) IS | AS
local variable declarations
BEGIN
program statements
EXCEPTION
exception handlers
body
END;
header
Executing a Procedure
EXECUTE procedure_name
(parameter1_value, parameter2_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
(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
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(parameter1_value,
parameter2_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
(parameter1_value, parameter2_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
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// 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;
/
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