PL/SQL Package
PACKAGES
-A package groups related variables, cursors, exceptions, procedures and functions into a single location
-.
- A package can consist of either
(1) just a package specification
A package specification defines an interface to the user of the package
This can include:
Procedure, Function and Trigger declarations, i.e parameter names and types but no procedural code
Data declarations (global variables) and Exceptions
or
(2) a specification and body
. A body contains the implementation code of the operations defined in the package.
-A Specification can be compiled w/o its body.
-The DD view USER_OBJECTS keeps the specification and body name.
-Public Package Constructs
-Declared w/n the specification and defined w/n the body
-Can be referenced from any Oracle server environment
-That is, procedures declared in the spec. can be called from outside the package
-
-Private Package Constructs
-Declared and defined w/n the body
-Can only be referenced by other constructs that are part of the same package
- Packages provide three major capabilities:
(1) Encapsulate data and operations
(2) Provide a means for implementing information hiding
(users can see the parameters required to run a procedure but not the procedural code itself)
(3) Provide a mechanism for declaring data objects such as tables and records, which are persistent for the duration of a session
- Global Declarations
-A package spec. and body can declare persistent objects that exist for the duration of an Oracle session.
- Overloading
-Procedures and functions within a package can be overloaded
-
Overloading
Two procedures or functions which perform the same logical task but use different data types for parameters can have the same name.
For instance you may want to find the name of the attending physician for a patient.
On some occasions you want to find them using the patient ID and in other circumstances you want to use the patient name.
-Functions that differ only in the data type returned cannot be overloaded
FUNCTION get_data (id IN NUMBER) RETURN DATE;
FUNCTION get_data (id IN NUMBER) RETURN CHAR;
-Difference in parameter mode cannot be overloaded
Example of Overloading
1. Create package specification
CREATE OR REPLACE PACKAGE HOSPITALPACKAGE AS
-- USE PATIENT ID
FUNCTION GET_DOCTOR (p_P# IN NUMBER)
RETURN VARCHAR2;
-- USE PATIENT FIRST AND LAST NAMES
FUNCTION GET_DOCTOR (p_Fn IN VARCHAR2,p_Ln IN VARCHAR2)
RETURN VARCHAR2;
END HOSPITALPACKAGE;
/
Package created.
2. Create package body
SQL> CREATE OR REPLACE PACKAGE BODY HOSPITALPACKAGE AS
FUNCTION GET_DOCTOR (p_P# IN NUMBER)
RETURN VARCHAR2 IS
v_Doctor PHYSICIAN.PHY_LNAME%TYPE;
BEGIN
SELECT PHY_LNAME INTO v_DOCTOR FROM PHYSICIAN,PATIENT
WHERE PRIMARY_PHY_ID = PHY_ID AND P_NUM = p_P#;
RETURN(v_DOCTOR);
END GET_DOCTOR;
FUNCTION GET_DOCTOR (p_Fn IN VARCHAR2,p_Ln IN VARCHAR2)
RETURN VARCHAR2 IS
v_Doctor PHYSICIAN.PHY_LNAME%TYPE;
BEGIN
SELECT PHY_LNAME INTO v_DOCTOR FROM PHYSICIAN,PATIENT
WHERE PRIMARY_PHY_ID = PHY_ID AND P_FNAME = p_Fn AND
P_LNAME = p_Ln;
RETURN(v_Doctor);
END GET_DOCTOR;
END HOSPITALPACKAGE;
/
Package body created.
Testing the two GET_DOCTOR Functions
USING PATIENT NAME
INSERT INTO VISIT_DETAILS VALUES(SYSDATE,222333444,GET_DOCTOR('AARON','AARDVARK');
SELECT * FROM VISIT_DETAILS WHERE PATIENT_ID = 222333444;
VISIT_DATE PATIENT_ID ATTENDING_PHYSICIAN
------
17-MAY-02 222333444 WATERS
USING PATIENT ID
INSERT INTO VISIT_DETAILS
VALUES(SYSDATE-1,111222333,GET_DOCTOR(111222333);
SELECT * FROM VISIT_DETAILS WHERE PATIENT_ID = 111222333;
VISIT_DATE PATIENT_ID ATTENDING_PHYSICIAN
------
16-MAY-02 111222333 COSTELLO
• Others
-If a package body contains a procedure that accesses the patient table and the patient table is dropped, the package becomes INVALID.
-When we change SP code, views, tables, and other objects, the recompilation is automatically done at run-time.
-Use Packages Whenever Possible Packages are the most effective way of preventing unnecessary dependency checks from being performed
-For significant DB changes, more efficient to manually recompile the SPs
ALTER PROCEDURE Annual_Review COMPILE;
ALTER FUNCTION Raise_Salary COMPILE;
ALTER PACKAGE End_Of_Year COMPILE;
ALTER PACKAGE BODY End_Of_Year COMPILE;
-- Compile all procedures, functions, and packages in the specified schema
PROCEDURE DBMS_UTILITY.COMPILE_SCHEMA (schema VARCHAR2);
-- Compile all the procedures, functions, and packages that I created
SQL> exec DBMS_UTILITY.COMPILE_SCHEMA(user)
/*
complete example of a package inc package specification,
package body and some examples of running routines created within the package.
Start by declaring the package specification
*/
CREATE OR REPLACE PACKAGE HospitalPackageAS
-- variable definitions
v_Doctor PHYSICIAN.PHY_LNAME%TYPE;
v_Patient PATIENT.P_NUM%TYPE;
v_Patient_Num BINARY_INTEGER; -- index for table
v_Loop NUMBER;
v_Idx NUMBER;
-- create a new TYPE as a Table
TYPE t_PatientIDTable IS TABLE OF PATIENT.P_NUM%TYPE
INDEX BY BINARY_INTEGER;
-- make an instance of that new type
v_Pat_Ids t_PatientIDTable;
-- exception definitions
e_Patient_Not_Found EXCEPTION;
-- declare function and procedure stubs inc returns
FUNCTION GET_DOCTOR (p_P# IN VARCHAR2)
RETURN VARCHAR2;
PROCEDURE DEL_PATIENT(p_P# IN VARCHAR2);
PROCEDURE GET_PATIENTS(p_Phy_Id IN PHYSICIAN.PHY_ID%TYPE);
END HospitalPackage;
/
/*
Now define package body including procedural code for routines and exception handling
*/
CREATE OR REPLACE PACKAGE BODY HospitalPackageAS
-- find the name of a patient's doctor
FUNCTION GET_DOCTOR (p_P# IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
SELECT PHY_LNAME INTO v_Doctor FROM PHYSICIAN,PATIENT
WHERE PRIMARY_PHY_ID = PHY_ID AND P_NUM = p_P#;
RETURN (v_Doctor); -- declared in specification
EXCEPTION -- deal with incorrect patient ID
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20001,'PATIENT NOT FOUND ');
END GET_DOCTOR;
/*
Find all patients for specified physician.
Note as variables were declared in specification they need not be declared here but can be used here.
*/
PROCEDURE GET_PATIENTS(p_Phy_Id IN PHYSICIAN.PHY_ID%TYPE) AS
-- we need to hold several patients so declare a cursor
-- to hold relevant patients
CURSOR C_PATIENTS IS
SELECT P_NUM FROM PATIENT
WHERE PRIMARY_PHY_ID = p_Phy_Id;
BEGIN
v_Patient_Num := 1; -- initialize index
OPEN C_PATIENTS;-- open the cursor
LOOP-- walk through patients found
FETCH C_PATIENTS INTO v_Patient; -- retrieve patient ID
EXIT WHEN C_PATIENTS%NOTFOUND;
v_Pat_Ids(v_Patient_Num) := v_Patient; -- store in table
v_Patient_Num := v_Patient_Num + 1;
END LOOP;
-- now walk through table displaying patient IDs
v_Idx := 1;
v_Loop := v_Pat_Ids.COUNT; -- COUNT is rows in table
FOR FREE_VARIABLE IN 1..v_Loop LOOP
DBMS_OUTPUT.PUT_LINE(v_Pat_Ids(v_Idx));
v_Idx := v_Idx + 1;
END LOOP;
END GET_PATIENTS;
-- delete a patient from the table
PROCEDURE DEL_PATIENT(p_P# IN VARCHAR2) AS
BEGIN
v_Patient := p_P#;
DELETE FROM PATIENT WHERE P_NUM = v_Patient;
-- what if invalid patient given
IF SQL%NOTFOUND THEN
RAISE e_Patient_Not_Found;
END IF;
EXCEPTION
WHEN e_Patient_Not_Found THEN
RAISE_APPLICATION_ERROR(-20001,'PATIENT '||v_Patient||' NOT FOUND - TRY AGAIN');
END DEL_PATIENT;
END HOSPITALPACKAGE;
/
Examples of package Use
SQL> VARIABLE DOC VARCHAR2(20)
SQL> EXEC :DOC := HOSPITALPACKAGE.GET_DOCTOR(111222333);
PL/SQL procedure successfully completed.
SQL> PRINT DOC
DOC
------
WATERS
SQL> EXEC HOSPITALPACKAGE.GET_PATIENTS(123456789);
111222333
555666777
SQL> EXEC HOSPITALPACKAGE.DEL_PATIENT('777888999');
BEGIN HOSPITALPACKAGE.DEL_PATIENT('777888999'); END;
*
ERROR at line 1:
ORA-20001: PATIENT 777888999 NOT FOUND - TRY AGAIN
ORA-06512: at "JW65.HOSPITALPACKAGE", line 42
ORA-06512: at line 1
• DBMS_OUTPUT Package
-Accumulates information into a buffer and retrieves from the buffer
-DBMS_OUPUT Procedures
-PUTAppend text to the current line of the output buffer
-PUT_LINECombine PUT and NEW_LINE
-NEW_LINEPlaces an EOL marker in the output buffer
-GET_LINERetrieves the current line from the output buffer into the procedure
-GET_LINESRetrieves an array of lines from the output buffer into the procedure
-ENABLEEnables calls to the DBMS_OUTPUT procedure
-DISABLEDisables calls to the DBMS_OUTPUT procedure
- The buffer size can be set to a size between 2,000 to 1,000,000 bytes with the ENABLE procedure
- The default buffer size is 20000 bytes.
- If you do not enable the package, then no info will be displayed or retrievable from the buffer.
- The buffer stores three different types of data - VARCHAR2, NUMBER, and DATE
- The buffer is contained in the SGA (Shared Global Area) of the db instance.
• DISABLE procedure
- Use
DBMS_OUTPUT.DISABLE;
or
SQL> SET SERVEROUTPUT OFF
- Disables all calls to the DBMS_OUTPUT package, except ENABLE
- Purges the buffer of any remaining lines
- After this command, all calls to PUT_LINE and other modules will be ignored
• ENABLE procedure
- Enables calls to the other DBMS_OUTPUT modules
- With this option on, SQL*Plus will automatically call DBMS_OUTPUT.GET_LINES and prints the results to the screen
- SET SERVEROUTPUT ON or DBMS_OUTPUT.ENABLE;
- SET SERVEROUTPUT ON SIZE 1000000 or
DBMS_OUTPUT.ENABLE (1000000); clears DBMS output buffer
- Good idea to add the above stmt in "login.sql" file to automatically enable whenever you get into SQL*Plus.
• GET_LINE procedure
- Retrieves one line (up to 255 bytes in char format) from the buffer
- PROCEDURE GET_LINE (line OUT VARCHAR2, status OUT NUMBER);
. If reading is successful, status is set to zero, otherwise one
• GET_LINES procedure
- Retrieves multiple lines from the buffer with one call
- It reads the buffer into a PL/SQL table
- PROCEDURE GET_LINES (lines OUT chararr, num_lines IN OUT INTEGER);
TYPE chararr IS TABLE OF VARCHAR2(255) INDEX by BINARY_INTEGER;
. num_lines (line numbers start with zero not one!)
. num_lines: number of lines to be read or number of lines read
• PUT procedure
- Puts data into the buffer, but does not append a newline marker
• PUT_LINE procedure
- Puts data into the buffer, and then appends a newline marker
- In SQL*Plus, a call to PUT_LINE will immediately display all data in the current line of the buffer to the screen
Usages:
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT(‘Visit Date '|| SYSDATE );
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SYSDATE,’mm/dd/yyyy’));
• Data Dictionary VIEWS for SPs and SFs
- USER_OBJECTSdisplay object name, type, status, and dates
- USER_SOURCEdisplay the text of SPs, SFs AND packages
- USER_ERRORSdisplay the text for compile errors
(or use SHOW ERRORS to see the errors of the last procedure compiled)
• DESCRIBE Procedures
-Display the argument list, type, mode (IN/OUT), and default value.
-DESCRIBE Get_Doctor;
• Controlling Security
-Granting execution privilege to a user
GRANT EXECUTEON Get_Doctor TO jim_waters;
1