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:
execute dbms_utility.rupile_all
A Package Specification and its body
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>
A Subtypes Example
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>
Calling a Cursor Declared in a Different Package
-- 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>
Calling Functions and Procedures in a Package
When calling functions and procedures in a package, you include the package name in the call.
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>
Calls procedure in a package
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>
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:
grant execute on package_name to user
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:
SQL>
SQL> CREATE [OR REPLACE] PACKAGE BODY package_name
2 {IS | AS}
3 package_body
4 END package_name;
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:
CREATE [OR REPLACE] PACKAGE package_name
{IS | AS}
package_specification
END package_name;
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
package_name.type_name
package_name.object_name
package_name.subprogram_name
Crosss reference between two packages
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>
Dropping a Package
You drop a package using DROP PACKAGE.
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>
Dynamically create packages
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>
Generate Random number
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>
Globals Stored in a Package
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>
Overloading Packaged Subprograms
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>
package RECURSION
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>
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:
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.
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
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>
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.
ALTER PACKAGE inventory_pkg compile body
Reference fields and methods in package
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>
Using RESTRICT_REFERENCES in a Package
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>