Oracle PL/SQL Tutorial/Function Procedure Packages/Packages
Содержание
- 1 All packages can be recompiled by using the Oracle utility dbms_utility:
- 2 A Package Specification and its body
- 3 A Subtypes Example
- 4 Calling a Cursor Declared in a Different Package
- 5 Calling Functions and Procedures in a Package
- 6 Calls procedure in a package
- 7 Controlling access to packages
- 8 Creating a Package Body
- 9 Creating a Package Specification
- 10 Creating Packages and call its functions
- 11 Crosss reference between two packages
- 12 Dropping a Package
- 13 Dynamically create packages
- 14 Generate Random number
- 15 Globals Stored in a Package
- 16 Overloading Packaged Subprograms
- 17 package RECURSION
- 18 Packages
- 19 Package State
- 20 PLS-00452: Subprogram "GETNAME" violates its associated pragma
- 21 Private Versus Public Package Objects
- 22 Recompiling Packages
- 23 Reference fields and methods in package
- 24 Using RESTRICT_REFERENCES in a Package
All packages can be recompiled by using the Oracle utility dbms_utility:
<source lang="sql">
execute dbms_utility.rupile_all</source>
A Package Specification and its body
<source lang="sql">
SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE PACKAGE EmployeePackage AS
2 PROCEDURE AddEmployee(p_EmployeeID IN Employee.id%TYPE, 3 p_first_name IN Employee.first_name%TYPE, 4 p_last_name IN Employee.last_name%TYPE); 5 6 -- Removes the specified student from the specified class. 7 PROCEDURE RemoveEmployee(p_EmployeeID IN Employee.id%TYPE, 8 p_first_name IN Employee.first_name%TYPE, 9 p_last_name IN Employee.last_name%TYPE); 10 11 -- Exception raised by RemoveEmployee. 12 e_EmployeeNotRegistered EXCEPTION; 13 14 -- Table type used to hold student info. 15 TYPE t_EmployeeIDTable IS TABLE OF Employee.id%TYPE INDEX BY BINARY_INTEGER; 16 17 -- Returns a PL/SQL table containing the Employee currently 18 -- in the specified class. 19 PROCEDURE EmployeeList(p_first_name IN Employee.first_name%TYPE, 20 p_last_name IN Employee.last_name%TYPE, 21 p_IDs OUT t_EmployeeIDTable, 22 p_NumEmployees IN OUT BINARY_INTEGER); 23 END EmployeePackage; 24 /
SP2-0808: Package created with compilation warnings SQL> SQL> CREATE OR REPLACE PACKAGE BODY EmployeePackage AS
2 PROCEDURE AddEmployee(p_EmployeeID IN Employee.id%TYPE, 3 p_first_name IN Employee.first_name%TYPE, 4 p_last_name IN Employee.last_name%TYPE) IS 5 BEGIN 6 INSERT INTO Employee (id, first_name, last_name) VALUES (p_EmployeeID, p_first_name, p_last_name); 7 COMMIT; 8 END AddEmployee; 9 10 PROCEDURE RemoveEmployee(p_EmployeeID IN Employee.id%TYPE, 11 p_first_name IN Employee.first_name%TYPE, 12 p_last_name IN Employee.last_name%TYPE) IS 13 BEGIN 14 DELETE FROM Employee 15 WHERE id = p_EmployeeID 16 AND first_name = p_first_name 17 AND last_name = p_last_name; 18 19 -- Check to see if the DELETE operation was successful. If 20 -- it didn"t match any rows, raise an error. 21 IF SQL%NOTFOUND THEN 22 RAISE e_EmployeeNotRegistered; 23 END IF; 24 25 COMMIT; 26 END RemoveEmployee; 27 28 29 -- Returns a PL/SQL table containing the Employee currently 30 -- in the specified class. 31 PROCEDURE EmployeeList(p_first_name IN Employee.first_name%TYPE, 32 p_last_name IN Employee.last_name%TYPE, 33 p_IDs OUT t_EmployeeIDTable, 34 p_NumEmployees IN OUT BINARY_INTEGER) IS 35 36 v_EmployeeID Employee.id%TYPE; 37 38 -- Local cursor to fetch the registered Employee. 39 CURSOR c_RegisteredEmployees IS 40 SELECT id 41 FROM Employee 42 WHERE first_name = p_first_name 43 AND last_name = p_last_name; 44 BEGIN 45 /* p_NumEmployees will be the table index. It will start at 46 0, and be incremented each time through the fetch loop. 47 At the end of the loop, it will have the number of rows 48 fetched, and therefore the number of rows returned in 49 p_IDs. */ 50 p_NumEmployees := 0; 51 52 OPEN c_RegisteredEmployees; 53 LOOP 54 FETCH c_RegisteredEmployees INTO v_EmployeeID; 55 EXIT WHEN c_RegisteredEmployees%NOTFOUND; 56 57 p_NumEmployees := p_NumEmployees + 1; 58 p_IDs(p_NumEmployees) := v_EmployeeID; 59 END LOOP; 60 END EmployeeList; 61 END EmployeePackage; 62 /
SP2-0810: Package Body created with compilation warnings SQL> SQL> --Calling a Packaged Procedure SQL> DECLARE
2 v_HistoryEmployees EmployeePackage.t_EmployeeIDTable;
3 v_NumEmployees BINARY_INTEGER := 20;
4 BEGIN
5 EmployeePackage.EmployeeList("James", "Cat", v_HistoryEmployees,v_NumEmployees);
6
7 -- Insert these Employee into temp_table.
8 FOR v_LoopCounter IN 1..v_NumEmployees LOOP
9 DBMS_OUTPUT.put_line(v_HistoryEmployees(v_LoopCounter));
10 END LOOP;
11 END;
12 /
08 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
A Subtypes Example
<source lang="sql">
SQL> SQL> create or replace package pkg_subtype is
2 subtype fullName is VARCHAR2(61); 3 end pkg_subtype; 4 /
Package created. SQL> declare
2 v_fullName_sty pkg_subtype.fullName; 3 begin 4 v_fullName_sty := "M C"; 5 end; 6 /
PL/SQL procedure successfully completed. SQL> SQL></source>
Calling a Cursor Declared in a Different Package
<source lang="sql">
-- create demo table create table Employee(
ID VARCHAR2(4 BYTE) NOT NULL, First_Name VARCHAR2(10 BYTE), Last_Name VARCHAR2(10 BYTE), Start_Date DATE, End_Date DATE, Salary Number(8,2), City VARCHAR2(10 BYTE), Description VARCHAR2(15 BYTE)
) / -- prepare data insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
/ insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
/ insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
/ insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
/ insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
/ insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
/ insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
/ insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
/ -- display data in the table select * from Employee / --Here is the package spec where the cursor is declared. create or replace package pkg_Util is
cursor c_emp is select * from employee; r_emp c_emp%ROWTYPE;
end; / ge created. --Here is a different package that references the cursor create or replace package body pkg_aDifferentUtil is
procedure p_printEmps is
begin
open pkg_Util.c_emp;
loop
fetch pkg_Util.c_emp into pkg_Util.r_emp;
exit when pkg_Util.c_emp%NOTFOUND;
DBMS_OUTPUT.put_line(pkg_Util.r_emp.first_Name);
end loop;
close pkg_Util.c_emp;
end;
end; /
-- clean the table
drop table Employee /SQL></source>
Calling Functions and Procedures in a Package
When calling functions and procedures in a package, you include the package name in the call.
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE PACKAGE employee_package AS
2 TYPE t_ref_cursor IS REF CURSOR; 3 FUNCTION get_employee_ref_cursor RETURN t_ref_cursor; 4 PROCEDURE update_salary (p_id IN VARCHAR2,p_factor IN NUMBER); 5 END employee_package; 6 /
Package created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE BODY employee_package AS
2 FUNCTION get_employee_ref_cursor 3 RETURN t_ref_cursor IS 4 employee_ref_cursor t_ref_cursor; 5 BEGIN 6 -- get the REF CURSOR 7 OPEN employee_ref_cursor FOR 8 SELECT id, first_name, salary 9 FROM employee; 10 -- return the REF CURSOR 11 RETURN employee_ref_cursor; 12 END get_employee_ref_cursor; 13 14 PROCEDURE update_salary (p_id IN VARCHAR2, p_factor IN NUMBER) AS 15 v_employee_count INTEGER; 16 BEGIN 17 UPDATE employee 18 SET salary = salary * p_factor; 19 COMMIT; 20 EXCEPTION 21 WHEN OTHERS THEN 22 -- perform a rollback when an exception occurs 23 ROLLBACK; 24 END update_salary; 25 END employee_package; 26 /
Package body created. SQL> SQL> SELECT employee_package.get_employee_ref_cursor
2 FROM dual;
GET_EMPLOYEE_REF_CUR
CURSOR STATEMENT : 1 CURSOR STATEMENT : 1 ID FIRST_NAME SALARY
---------- ----------
01 Jason 1234.56 02 Alison 6661.78 03 James 6544.78 04 Celia 2344.78 05 Robert 2334.78 06 Linda 4322.78 07 David 7897.78 08 James 1232.78 8 rows selected.
SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
Calls procedure in a package
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE PACKAGE employee_package AS
2 TYPE t_ref_cursor IS REF CURSOR; 3 FUNCTION get_employee_ref_cursor RETURN t_ref_cursor; 4 PROCEDURE update_salary (p_id IN VARCHAR2,p_factor IN NUMBER 5 ); 6 END employee_package; 7 /
Package created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE BODY employee_package AS
2 FUNCTION get_employee_ref_cursor 3 RETURN t_ref_cursor IS 4 employee_ref_cursor t_ref_cursor; 5 BEGIN 6 -- get the REF CURSOR 7 OPEN employee_ref_cursor FOR 8 SELECT id, first_name, salary 9 FROM employee; 10 -- return the REF CURSOR 11 RETURN employee_ref_cursor; 12 END get_employee_ref_cursor; 13 14 PROCEDURE update_salary (p_id IN VARCHAR2,p_factor IN NUMBER 15 ) AS 16 v_employee_count INTEGER; 17 BEGIN 18 -- count the number of employees with the 19 -- supplied id (should be 1 if the employee exists) 20 SELECT COUNT(*) 21 INTO v_employee_count 22 FROM employee 23 WHERE id = p_id; 24 -- if the employee exists (v_employee_count = 1) then 25 -- update that employee"s pass1 26 IF v_employee_count = 1 THEN 27 UPDATE employee 28 SET salary = salary * p_factor 29 WHERE id = p_id; 30 COMMIT; 31 END IF; 32 EXCEPTION 33 WHEN OTHERS THEN 34 -- perform a rollback when an exception occurs 35 ROLLBACK; 36 END update_salary; 37 END employee_package; 38 /
Package body created. SQL> SQL> SQL> CALL employee_package.update_salary(3, 1.25); Call completed. SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL></source>
Controlling access to packages
You can do this in one of two ways: a simple command or a wrapper package.
To grant a user rights to access a particular package:
<source lang="sql">
grant execute on package_name to user</source>
Creating a Package Body
You create a package body using the CREATE PACKAGE BODY statement.
The simplified syntax for the CREATE PACKAGE BODY statement is as follows:
<source lang="sql">
SQL> SQL> CREATE [OR REPLACE] PACKAGE BODY package_name
2 {IS | AS}
3 package_body
4 END package_name;</source>
where
package_name specifies the name of the package, which must match the package name previously set in the package specification.
package_body specifies the code for the procedures and functions, along with any variables and cursors.
Creating a Package Specification
You create a package specification using the CREATE PACKAGE statement.
The simplified syntax for the CREATE PACKAGE statement is as follows:
<source lang="sql">
CREATE [OR REPLACE] PACKAGE package_name {IS | AS}
package_specification
END package_name;</source>
Creating Packages and call its functions
After the specification is created, you create the body of the package.
The body of a package is a collection of schema objects that was declared in the specification.
If you perform any initialization in the package body, it is executed once when the package is initially referenced.
To reference the package"s subprograms and objects, you must use dot notation.
The Syntax for Dot Notation
<source lang="sql">
package_name.type_name package_name.object_name package_name.subprogram_name</source>
Crosss reference between two packages
<source lang="sql">
SQL> SQL> create or replace package myPackage
2 as 3 PROCEDURE P1; 4 PROCEDURE P2; 5 PROCEDURE P3; 6 FUNCTION F1 return number; 7 FUNCTION F2 return number; 8 end; 9 /
Package created. SQL> SQL> create or replace package myPackage2
2 as 3 PROCEDURE P3; 4 PROCEDURE P4; 5 PROCEDURE P5; 6 FUNCTION F1 return number; 7 FUNCTION F3 return number; 8 end; 9 /
Package created. SQL> SQL> create or replace package body myPackage2
2 as 3 PROCEDURE P3 4 as 5 begin 6 myPackage.P3; 7 end; 8 9 FUNCTION F1 return number 10 as 11 begin 12 return myPackage.F1; 13 end; 14 15 PROCEDURE P4 16 as 17 begin 18 null; 19 end; 20 21 PROCEDURE P5 22 as 23 begin 24 null; 25 end; 26 27 FUNCTION F3 return number 28 as 29 begin 30 return 1; 31 end; 32 end; 33 /
Package body created. SQL> SQL> create or replace package body myPackage
2 as 3 PROCEDURE P1 4 as 5 begin 6 null; 7 end; 8 9 PROCEDURE P2 10 as 11 begin 12 null; 13 end; 14 15 PROCEDURE P3 16 as 17 begin 18 null; 19 end; 20 21 FUNCTION F1 return number 22 as 23 begin 24 return 1; 25 end; 26 27 FUNCTION F2 return number 28 as 29 begin 30 return 1; 31 end; 32 end; 33 /
Package body created. SQL> SQL></source>
Dropping a Package
You drop a package using DROP PACKAGE.
<source lang="sql">
SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE PACKAGE employee_package AS
2 TYPE t_ref_cursor IS REF CURSOR; 3 FUNCTION get_employee_ref_cursor RETURN t_ref_cursor; 4 PROCEDURE update_salary (p_id IN VARCHAR2, 5 p_factor IN NUMBER 6 ); 7 END employee_package; 8 /
Package created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE BODY employee_package AS
2 FUNCTION get_employee_ref_cursor 3 RETURN t_ref_cursor IS 4 employee_ref_cursor t_ref_cursor; 5 BEGIN 6 -- get the REF CURSOR 7 OPEN employee_ref_cursor FOR 8 SELECT id, first_name, salary 9 FROM employee; 10 -- return the REF CURSOR 11 RETURN employee_ref_cursor; 12 END get_employee_ref_cursor; 13 14 PROCEDURE update_salary (p_id IN VARCHAR2, 15 p_factor IN NUMBER 16 ) AS 17 v_employee_count INTEGER; 18 BEGIN 19 -- count the number of employees with the 20 -- supplied id (should be 1 if the employee exists) 21 SELECT COUNT(*) 22 INTO v_employee_count 23 FROM employee 24 WHERE id = p_id; 25 -- if the employee exists (v_employee_count = 1) then 26 -- update that employee"s pass1 27 IF v_employee_count = 1 THEN 28 UPDATE employee 29 SET salary = salary * p_factor 30 WHERE id = p_id; 31 COMMIT; 32 END IF; 33 EXCEPTION 34 WHEN OTHERS THEN 35 -- perform a rollback when an exception occurs 36 ROLLBACK; 37 END update_salary; 38 END employee_package; 39 /
Package body created. SQL> SQL> SQL> SELECT object_name, procedure_name
2 FROM user_procedures 3 WHERE object_name = "EMPLOYEE_PACKAGE";
OBJECT_NAME PROCEDURE_NAME
------------------------------
EMPLOYEE_PACKAGE GET_EMPLOYEE_REF_CURSOR EMPLOYEE_PACKAGE UPDATE_SALARY SQL> SQL> DROP PACKAGE EMPLOYEE_PACKAGE; SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL> SQL> SQL></source>
Dynamically create packages
<source lang="sql">
SQL> declare
2 x varchar2(32767); 3 begin 4 for i in 1 .. 300 loop 5 x := x || " procedure X"||i||";"; 6 end loop; 7 execute immediate "create or replace package aTest is "||x||" end;"; 8 x := replace(x,";"," is y number; begin y := 1; end;"); 9 10 execute immediate "create or replace package body aTest is "||x||" end;"; 11 end; 12 /
PL/SQL procedure successfully completed. SQL></source>
Generate Random number
<source lang="sql">
SQL> REM 05-RAND.SQL SQL> REM This file contains the code for the Random package in SQL> REM Chapter 5 of "Oracle PL/SQL Programming". It illustrates SQL> REM package initialization. SQL> SQL> REM This is version 1.0 of this file, updated 2/18/96. SQL> REM Comments and questions should go to Scott Urman at SQL> REM surman@us.oracle.ru. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE Random AS
2 /* Random number generator. Uses the same algorithm as the 3 rand() function in C. */ 4 5 -- Used to change the seed. From a given seed, the same 6 -- sequence of random numbers will be generated. 7 PROCEDURE ChangeSeed(p_NewSeed IN NUMBER); 8 9 -- Returns a random integer between 1 and 32767. 10 FUNCTION Rand RETURN NUMBER; 11 --PRAGMA RESTRICT_REFERENCES(rand, WNDS ); 12 13 -- Same as Rand, but with a procedural interface. 14 PROCEDURE GetRand(p_RandomNumber OUT NUMBER); 15 16 -- Returns a random integer between 1 and p_MaxVal. 17 FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER; 18 -- PRAGMA RESTRICT_REFERENCES(RandMax, WNDS); 19 20 -- Same as RandMax, but with a procedural interface. 21 PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER, 22 p_MaxVal IN NUMBER); 23 END Random; 24 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY Random AS
2 3 /* Used for calculating the next number. */ 4 v_Multiplier CONSTANT NUMBER := 22695477; 5 v_Increment CONSTANT NUMBER := 1; 6 7 /* Seed used to generate random sequence. */ 8 v_Seed number := 1; 9 10 PROCEDURE ChangeSeed(p_NewSeed IN NUMBER) IS 11 BEGIN 12 v_Seed := p_NewSeed; 13 END ChangeSeed; 14 15 FUNCTION Rand RETURN NUMBER IS 16 BEGIN 17 v_Seed := MOD(v_Multiplier * v_Seed + v_Increment, 18 (2 ** 32)); 19 RETURN BITAND(v_Seed/(2 ** 16), 32767); 20 END Rand; 21 22 PROCEDURE GetRand(p_RandomNumber OUT NUMBER) IS 23 BEGIN 24 -- Simply call Rand and return the value. 25 p_RandomNumber := Rand; 26 END GetRand; 27 28 FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER IS 29 BEGIN 30 RETURN MOD(Rand, p_MaxVal) + 1; 31 END RandMax; 32 33 PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER, 34 p_MaxVal IN NUMBER) IS 35 BEGIN 36 -- Simply call RandMax and return the value. 37 p_RandomNumber := RandMax(p_MaxVal); 38 END GetRandMax; 39 40 BEGIN 41 /* Package initialization. Initialize the seed to the current 42 time in seconds. */ 43 ChangeSeed(TO_NUMBER(TO_CHAR(SYSDATE, "SSSSS"))); 44 END Random; 45 /
Package body created. SQL></source>
Globals Stored in a Package
<source lang="sql">
SQL> SQL> SQL> create or replace package pkg_global is
2 function f_countryUSA_cd return VARCHAR2; 3 end; 4 /
Package created. SQL> create or replace package body pkg_global is
2 gv_countryUSA_cd VARCHAR2(3) := "USA"; 3 4 function f_countryUSA_cd return VARCHAR2 is 5 begin 6 return gv_countryUSA_cd; 7 end; 8 9 end; 10 /
Package body created. SQL> SQL> select pkg_global.f_countryUSA_cd from dual; F_COUNTRYUSA_CD
USA SQL></source>
Overloading Packaged Subprograms
<source lang="sql">
SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE PACKAGE EmployeePackage AS
2 PROCEDURE AddEmployee(p_EmployeeID IN Employee.id%TYPE, 3 p_first_name IN Employee.first_name%TYPE, 4 p_last_name IN Employee.last_name%TYPE); 5 6 PROCEDURE AddEmployee(p_FirstName IN Employee.first_name%TYPE, 7 p_LastName IN Employee.last_name%TYPE, 8 p_city IN Employee.city%TYPE); 9 10 END EmployeePackage; 11 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY EmployeePackage AS
2 3 PROCEDURE AddEmployee(p_EmployeeID IN Employee.id%TYPE, 4 p_first_name IN Employee.first_name%TYPE, 5 p_last_name IN Employee.last_name%TYPE) IS 6 BEGIN 7 INSERT INTO Employee (id, first_name, last_name) 8 VALUES (p_EmployeeID, p_first_name, p_last_name); 9 COMMIT; 10 END AddEmployee; 11 12 -- Add a new student by name, rather than ID. 13 PROCEDURE AddEmployee(p_FirstName IN Employee.first_name%TYPE, 14 p_LastName IN Employee.last_name%TYPE, 15 p_city IN Employee.city%TYPE) IS 16 v_EmployeeID Employee.ID%TYPE; 17 BEGIN 18 SELECT ID 19 INTO v_EmployeeID 20 FROM Employee 21 WHERE first_name = p_FirstName 22 AND last_name = p_LastName; 23 24 -- Now we can add the student by ID. 25 INSERT INTO Employee (id, first_name, city) 26 VALUES (v_EmployeeID, p_firstname, p_city); 27 COMMIT; 28 END AddEmployee; 29 30 END EmployeePackage; 31 /
Package body created. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
package RECURSION
<source lang="sql">
SQL> create package RECURSION is
2 procedure A(p number); 3 procedure B(p number); 4 end; 5 /
SQL> create or replace package body RECURSION is
2 3 procedure A(p number) is 4 begin 5 B(p+1); 6 end; 7 8 procedure B(p number) is 9 begin 10 A(p+1); 11 end; 12 13 end; 14 /
Package body created. SQL> SQL></source>
Packages
- Packages encapsulate related functionality into one self-contained unit.
- Packages are typically made up of two components: a specification and a body.
- The package specification contains information about the package.
- The package specification lists the available procedures and functions.
- These are potentially available to all database users.
- The package specification generally doesn"t contain the code.
- The package body contains the actual code.
CREATE OR REPLACE PACKAGE command:
<source lang="sql">
SQL> create or replace package pkg_test1
2 as 3 function getArea (i_rad NUMBER) return NUMBER; 4 procedure p_print (i_str1 VARCHAR2 :="hello", 5 i_str2 VARCHAR2 :="world", 6 i_end VARCHAR2 :="!" ); 7 end; 8 /
Package created. SQL> SQL> create or replace package body pkg_test1
2 as 3 function getArea (i_rad NUMBER)return NUMBER 4 is 5 v_pi NUMBER:=3.14; 6 begin 7 return v_pi * (i_rad ** 2); 8 end; 9 10 procedure p_print(i_str1 VARCHAR2 :="hello", 11 i_str2 VARCHAR2 :="world", 12 i_end VARCHAR2 :="!" ) 13 is 14 begin 15 DBMS_OUTPUT.put_line(i_str1||","||i_str2||i_end); 16 end; 17 end; 18 /
Package body created.</source>
Package State
A package is always either valid or invalid.
A package is valid if none of its source code or objects it references have been dropped, replaced, or altered since the package specification was last recompiled.
a package is invalid if its source code or any object that it references has been dropped, altered, or replaced since the package specification was last recompiled.
When a package becomes invalid, Oracle will also make invalid any object that references the package.
27. 10. Packages 27. 10. 1. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Packages.htm">Packages</a> 27. 10. 2. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/PrivateVersusPublicPackageObjects.htm">Private Versus Public Package Objects</a> 27. 10. 3. Package State 27. 10. 4. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/RecompilingPackages.htm">Recompiling Packages</a> 27. 10. 5. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/AllpackagescanberecompiledbyusingtheOracleutilitydbmsutility.htm">All packages can be recompiled by using the Oracle utility dbms_utility:</a> 27. 10. 6. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/CreatingaPackageSpecification.htm">Creating a Package Specification</a> 27. 10. 7. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/CreatingaPackageBody.htm">Creating a Package Body</a> 27. 10. 8. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/CreatingPackagesandcallitsfunctions.htm">Creating Packages and call its functions</a> 27. 10. 9. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/CallingFunctionsandProceduresinaPackage.htm">Calling Functions and Procedures in a Package</a> 27. 10. 10. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/APackageSpecificationanditsbody.htm">A Package Specification and its body</a> 27. 10. 11. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/OverloadingPackagedSubprograms.htm">Overloading Packaged Subprograms</a> 27. 10. 12. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Callsprocedureinapackage.htm">Calls procedure in a package</a> 27. 10. 13. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/DroppingaPackage.htm">Dropping a Package</a> 27. 10. 14. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/CallingaCursorDeclaredinaDifferentPackage.htm">Calling a Cursor Declared in a Different Package</a> 27. 10. 15. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Referencefieldsandmethodsinpackage.htm">Reference fields and methods in package</a> 27. 10. 16. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Controllingaccesstopackages.htm">Controlling access to packages</a> 27. 10. 17. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/GlobalsStoredinaPackage.htm">Globals Stored in a Package</a> 27. 10. 18. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/ASubtypesExample.htm">A Subtypes Example</a> 27. 10. 19. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/GenerateRandomnumber.htm">Generate Random number</a> 27. 10. 20. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Crosssreferencebetweentwopackages.htm">Crosss reference between two packages</a> 27. 10. 21. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/packageRECURSION.htm">package RECURSION</a> 27. 10. 22. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/UsingRESTRICTREFERENCESinaPackage.htm">Using RESTRICT_REFERENCES in a Package</a> 27. 10. 23. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/PLS00452SubprogramGETNAMEviolatesitsassociatedpragma.htm">PLS-00452: Subprogram "GETNAME" violates its associated pragma</a> 27. 10. 24. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Dynamicallycreatepackages.htm">Dynamically create packages</a>
PLS-00452: Subprogram "GETNAME" violates its associated pragma
<source lang="sql">
SQL> SQL> SQL> create table product(
2 product_id number(4) not null, 3 product_description varchar2(20) not null 4 );
Table created. SQL> SQL> insert into product values (1,"Java"); 1 row created. SQL> insert into product values (2,"Oracle"); 1 row created. SQL> insert into product values (3,"C#"); 1 row created. SQL> insert into product values (4,"Javascript"); 1 row created. SQL> insert into product values (5,"Python"); 1 row created. SQL> SQL> SQL> create table company(
2 product_id number(4) not null, 3 company_id NUMBER(8) not null, 4 company_short_name varchar2(30) not null, 5 company_long_name varchar2(60) 6 );
Table created. SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc."); 1 row created. SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc."); 1 row created. SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc."); 1 row created. SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc."); 1 row created. SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc."); 1 row created. SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc."); 1 row created. SQL> SQL> create table product_company(
2 product_id number(4) not null, 3 product_description varchar2(20) not null, 4 company_id NUMBER(8) not null, 5 company_short_name varchar2(30) not null, 6 company_long_name varchar2(60) 7 );
Table created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PACKAGE myPackage
2 IS 3 FUNCTION getName(ip_product_id NUMBER,ip_company_id NUMBER) 4 RETURN VARCHAR2; 5 PRAGMA RESTRICT_REFERENCES(getName,WNDS,WNPS); 6 END myPackage; 7 /
Package created. SQL> SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE PACKAGE BODY myPackage
2 IS 3 FUNCTION getName(ip_product_id NUMBER,ip_company_id NUMBER) 4 RETURN VARCHAR2 5 IS 6 v_name VARCHAR2(120); 7 v_product_description VARCHAR2(20); 8 v_company_short_name VARCHAR2(30); 9 v_company_long_name VARCHAR2(60); 10 BEGIN 11 SELECT "Org Name: (Short) "||company_short_name||" (Long) "||company_long_name 12 INTO v_name 13 FROM company 14 WHERE product_id = ip_product_id 15 AND company_id = ip_company_id; 16 SELECT product_description 17 INTO v_product_description 18 FROM product 19 WHERE product_id = ip_product_id; 20 SELECT company_short_name,company_long_name 21 INTO v_company_short_name,v_company_long_name 22 FROM company 23 WHERE product_id = ip_product_id 24 AND company_id = ip_company_id; 25 INSERT INTO product_company VALUES 26 (ip_product_id,v_product_description,ip_company_id, 27 v_company_short_name,v_company_long_name); 28 RETURN (v_name); 29 END getName; 30 END myPackage; 31 /
Warning: Package Body created with compilation errors. SQL> show errors Errors for PACKAGE BODY MYPACKAGE: LINE/COL
ERROR
3/3 PLS-00452: Subprogram "GETNAME" violates its associated pragma SQL> SQL> SQL> drop table company; Table dropped. SQL> SQL> drop table product; Table dropped. SQL> SQL> drop table product_company; Table dropped. SQL></source>
Private Versus Public Package Objects
For objects that are declared inside the package body, you are restricted to use within that package.
Therefore, PL/SQL code outside the package cannot reference any of the variables that were privately declared within the package.
Any items declared inside the package specification are visible outside the package.
These objects declared in the package specification are called public.
If the variable, constant, or cursor was declared in a package specification or body, their values persist for the duration of the user"s session.
The values are lost when the current user"s session terminates or the package is recompiled.
27. 10. Packages 27. 10. 1. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Packages.htm">Packages</a> 27. 10. 2. Private Versus Public Package Objects 27. 10. 3. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/PackageState.htm">Package State</a> 27. 10. 4. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/RecompilingPackages.htm">Recompiling Packages</a> 27. 10. 5. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/AllpackagescanberecompiledbyusingtheOracleutilitydbmsutility.htm">All packages can be recompiled by using the Oracle utility dbms_utility:</a> 27. 10. 6. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/CreatingaPackageSpecification.htm">Creating a Package Specification</a> 27. 10. 7. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/CreatingaPackageBody.htm">Creating a Package Body</a> 27. 10. 8. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/CreatingPackagesandcallitsfunctions.htm">Creating Packages and call its functions</a> 27. 10. 9. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/CallingFunctionsandProceduresinaPackage.htm">Calling Functions and Procedures in a Package</a> 27. 10. 10. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/APackageSpecificationanditsbody.htm">A Package Specification and its body</a> 27. 10. 11. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/OverloadingPackagedSubprograms.htm">Overloading Packaged Subprograms</a> 27. 10. 12. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Callsprocedureinapackage.htm">Calls procedure in a package</a> 27. 10. 13. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/DroppingaPackage.htm">Dropping a Package</a> 27. 10. 14. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/CallingaCursorDeclaredinaDifferentPackage.htm">Calling a Cursor Declared in a Different Package</a> 27. 10. 15. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Referencefieldsandmethodsinpackage.htm">Reference fields and methods in package</a> 27. 10. 16. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Controllingaccesstopackages.htm">Controlling access to packages</a> 27. 10. 17. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/GlobalsStoredinaPackage.htm">Globals Stored in a Package</a> 27. 10. 18. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/ASubtypesExample.htm">A Subtypes Example</a> 27. 10. 19. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/GenerateRandomnumber.htm">Generate Random number</a> 27. 10. 20. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Crosssreferencebetweentwopackages.htm">Crosss reference between two packages</a> 27. 10. 21. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/packageRECURSION.htm">package RECURSION</a> 27. 10. 22. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/UsingRESTRICTREFERENCESinaPackage.htm">Using RESTRICT_REFERENCES in a Package</a> 27. 10. 23. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/PLS00452SubprogramGETNAMEviolatesitsassociatedpragma.htm">PLS-00452: Subprogram "GETNAME" violates its associated pragma</a> 27. 10. 24. <A href="/Tutorial/Oracle/0540__Function-Procedure-Packages/Dynamicallycreatepackages.htm">Dynamically create packages</a>
Recompiling Packages
To recompile a package, use the ALTER PACKAGE command with the compile keyword.
Recompiling a package recompiles all objects defined within the package.
The following examples recompile just the body of a package.
<source lang="sql">
ALTER PACKAGE inventory_pkg compile body</source>
Reference fields and methods in package
<source lang="sql">
SQL> SQL> SQL> create or replace package pkg_a is
2 v_a number:=0; 3 function a1 return NUMBER; 4 end; 5 /
Package created. SQL> create or replace package body pkg_a is
2 function a1 return NUMBER is 3 begin 4 return 0; 5 end; 6 end; 7 /
Package body created. SQL> create or replace package pkg_b is
2 function b1 return NUMBER; 3 end; 4 /
Package created. SQL> create or replace package body pkg_b is
2 function b1 return NUMBER is 3 begin 4 return pkg_a.a1+1; 5 end; 6 end; 7 /
Package body created. SQL> SQL> SQL> select pkg_a.a1, pkg_b.b1 from dual;
A1 B1
----------
0 1
SQL></source>
Using RESTRICT_REFERENCES in a Package
<source lang="sql">
SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL, 3 First_Name VARCHAR2(10 BYTE), 4 Last_Name VARCHAR2(10 BYTE), 5 Start_Date DATE, 6 End_Date DATE, 7 Salary Number(8,2), 8 City VARCHAR2(10 BYTE), 9 Description VARCHAR2(15 BYTE) 10 ) 11 /
Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
-------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> CREATE OR REPLACE PACKAGE EmployeeOps AS
2 FUNCTION FullName(p_ID IN employee.ID%TYPE) 3 RETURN VARCHAR2; 4 PRAGMA RESTRICT_REFERENCES(FullName, WNDS, WNPS, RNPS); 5 6 FUNCTION NumCity 7 RETURN NUMBER; 8 PRAGMA RESTRICT_REFERENCES(NumCity, WNDS, WNPS, RNPS); 9 END EmployeeOps; 10 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY EmployeeOps AS
2 3 v_NumHist NUMBER; 4 5 FUNCTION FullName(p_ID IN employee.ID%TYPE) 6 RETURN VARCHAR2 IS 7 v_Result VARCHAR2(100); 8 BEGIN 9 SELECT first_name || " " || last_name 10 INTO v_Result 11 FROM employee 12 WHERE ID = p_ID; 13 14 RETURN v_Result; 15 END FullName; 16 17 FUNCTION NumCity RETURN NUMBER IS 18 v_Result NUMBER; 19 BEGIN 20 IF v_NumHist IS NULL THEN 21 SELECT COUNT(*) 22 INTO v_Result 23 FROM employee 24 WHERE city = "Vancouver"; 25 v_NumHist := v_Result; 26 ELSE 27 v_Result := v_NumHist; 28 END IF; 29 30 RETURN v_Result; 31 END NumCity; 32 END EmployeeOps; 33 /
Warning: Package Body created with compilation errors. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL></source>
