Oracle PL/SQL/PL SQL/Package
Содержание
- 1 A package with one method
- 2 A PL/SQL package with two methods
- 3 call utitilities package
- 4 Counter package
- 5 Create a package declaration and create its body after
- 6 Create a package level variable
- 7 Create package and member cursor
- 8 Crosss reference between two packages
- 9 Cursor operation between package functions
- 10 dependencies between an anonymous calling block and package runtime state.
- 11 Global definition
- 12 Nested package reference
- 13 Overloaded packages.
- 14 Overloading based on user defined object types.
- 15 Overloading Packaged Subprograms
- 16 Package for output employee table and log message
- 17 Package initialization.
- 18 Package level Exception
- 19 Package level variable as global level variable
- 20 package RECURSION
- 21 Packages allows forward references, thus opening the possibilities for recursion.
- 22 Package with package level cursor variable
- 23 Persistance of packaged variables.
- 24 RESTRICT_REFERENCES pragma.
- 25 Student fetch package
- 26 The pragma is not valid at the package level.
- 27 This package will not compile because the body does not match the specification.
- 28 Use package level type as global variables
- 29 Use package member variable to pass value
- 30 Without RESTRICT_REFERENCES pragma.
A package with one method
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE PersistPkg AS
2 PRAGMA SERIALLY_REUSABLE; 3 4 TYPE t_StudentTable IS TABLE OF lecturer.ID%TYPE 5 INDEX BY BINARY_INTEGER; 6 7 v_MaxRows NUMBER := 5; 8 9 PROCEDURE Readlecturer(p_StudTable OUT t_StudentTable, 10 p_NumRows OUT NUMBER); 11 END PersistPkg; 12 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY PersistPkg AS
2 PRAGMA SERIALLY_REUSABLE; 3 CURSOR StudentCursor IS 4 SELECT ID 5 FROM lecturer 6 ORDER BY last_name; 7 8 PROCEDURE Readlecturer(p_StudTable OUT t_StudentTable, 9 p_NumRows OUT NUMBER) IS 10 v_Done BOOLEAN := FALSE; 11 v_NumRows NUMBER := 1; 12 BEGIN 13 IF NOT StudentCursor%ISOPEN THEN 14 OPEN StudentCursor; 15 END IF; 16 17 WHILE NOT v_Done LOOP 18 FETCH StudentCursor INTO p_StudTable(v_NumRows); 19 IF StudentCursor%NOTFOUND THEN 20 CLOSE StudentCursor; 21 v_Done := TRUE; 22 ELSE 23 v_NumRows := v_NumRows + 1; 24 IF v_NumRows > v_MaxRows THEN 25 v_Done := TRUE; 26 END IF; 27 END IF; 28 END LOOP; 29 30 p_NumRows := v_NumRows - 1; 31 32 END Readlecturer; 33 END PersistPkg; 34 /
Package body created. SQL> SQL> drop table lecturer; Table dropped.
</source>
A PL/SQL package with two methods
<source lang="sql">
SQL> SQL> CREATE TABLE place (
2 room_id NUMBER(5) PRIMARY KEY, 3 building VARCHAR2(15), 4 room_number NUMBER(4), 5 number_seats NUMBER(4), 6 description VARCHAR2(50) 7 );
Table created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20001, "Building 7", 201, 1000, "Large Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20002, "Building 6", 101, 500, "Small Lecture Hall");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20003, "Building 6", 150, 50, "Discussion Room A");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20004, "Building 6", 160, 50, "Discussion Room B");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20005, "Building 6", 170, 50, "Discussion Room C");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20006, "Music Building", 100, 10, "Music Practice Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20007, "Music Building", 200, 1000, "Concert Room");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats, description)
2 VALUES (20008, "Building 7", 300, 75, "Discussion Room D");
1 row created. SQL> SQL> INSERT INTO place (room_id, building, room_number, number_seats,description)
2 VALUES (20009, "Building 7", 310, 50, "Discussion Room E");
1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PACKAGE RoomsPkg AS
2 PROCEDURE NewRoom(p_Building place.building%TYPE, 3 p_RoomNum place.room_number%TYPE, 4 p_NumSeats place.number_seats%TYPE, 5 p_Description place.description%TYPE); 6 7 PROCEDURE DeleteRoom(p_RoomID IN place.room_id%TYPE); 8 END RoomsPkg; 9 /
Package created. SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE PACKAGE BODY RoomsPkg AS
2 PROCEDURE NewRoom(p_Building place.building%TYPE, 3 p_RoomNum place.room_number%TYPE, 4 p_NumSeats place.number_seats%TYPE, 5 p_Description place.description%TYPE) IS 6 BEGIN 7 INSERT INTO place 8 (room_id, building, room_number, number_seats, description) 9 VALUES 10 (1100, p_Building, p_RoomNum, p_NumSeats, 11 p_Description); 12 END NewRoom; 13 14 PROCEDURE DeleteRoom(p_RoomID IN place.room_id%TYPE) IS 15 BEGIN 16 DELETE FROM place 17 WHERE room_id = p_RoomID; 18 END DeleteRoom; 19 END RoomsPkg; 20 /
Package body created. SQL> show errors No errors. SQL> SQL> SQL> drop table place; Table dropped. SQL> SQL>
</source>
call utitilities package
<source lang="sql">
SQL> SQL> create or replace
2 package utilities as 3 procedure swap( p_parm1 in out number, 4 p_parm2 in out number ); 5 procedure swap( p_parm1 in out varchar2, 6 p_parm2 in out varchar2 ); 7 procedure swap( p_parm1 in out date, 8 p_parm2 in out date ); 9 end utilities; 10 /
Package created. SQL> SQL> create or replace
2 package body utilities as 3 4 procedure swap( p_parm1 in out number, 5 p_parm2 in out number ) is 6 l_temp number; 7 begin 8 dbms_output.put_line( "Swapping number" ); 9 l_temp := p_parm1; 10 p_parm1 := p_parm2; 11 p_parm2 := l_temp; 12 end swap; 13 14 procedure swap( p_parm1 in out varchar2, 15 p_parm2 in out varchar2 ) is 16 l_temp varchar2(32767); 17 begin 18 dbms_output.put_line( "Swapping varchar2" ); 19 l_temp := p_parm1; 20 p_parm1 := p_parm2; 21 p_parm2 := l_temp; 22 end swap; 23 24 procedure swap( p_parm1 in out date, 25 p_parm2 in out date ) is 26 l_temp date; 27 begin 28 dbms_output.put_line( "Swapping date" ); 29 l_temp := p_parm1; 30 p_parm1 := p_parm2; 31 p_parm2 := l_temp; 32 end swap; 33 34 end utilities; 35 /
Package body created. SQL> SQL> SQL> declare
2 l_num1 number := 1; 3 l_num2 number := 2; 4 l_date1 date := sysdate; 5 l_date2 date := sysdate + 1; 6 begin 7 utilities.swap( l_num1, l_num2 ); 8 dbms_output.put_line( "l_num1 = " || l_num1 ); 9 dbms_output.put_line( "l_num2 = " || l_num2 ); 10 utilities.swap( l_date1, l_date2 ); 11 dbms_output.put_line( "l_date1 = " || l_date1 ); 12 dbms_output.put_line( "l_date2 = " || l_date2 ); 13 end; 14 /
Swapping number l_num1 = 2 l_num2 = 1 Swapping date l_date1 = 12-JUN-08 l_date2 = 11-JUN-08 PL/SQL procedure successfully completed. SQL>
</source>
Counter package
<source lang="sql">
SQL> SQL> SQL> SQL> create or replace package counter is
2 procedure reset; 3 procedure inc; 4 procedure show; 5 end; 6 /
Package created. SQL> create or replace package body counter is
2 cnt pls_integer := 0;
3 procedure reset is
4 begin
5 cnt := 0;
6 end;
7
8 procedure inc is
9 begin
10 cnt := cnt + 1;
11 end;
12
13 procedure show is
14 begin
15 dbms_output.put_line("Execution Count: "||cnt);
16 end;
17 end;
18 /
Package body created. SQL> SQL>
</source>
Create a package declaration and create its body after
<source lang="sql">
SQL> SQL> SQL> create or replace
2 package shield as 3 procedure foo; 4 end shield; 5 /
Package created. SQL> SQL> create or replace
2 package body shield as 3 procedure foo is 4 l_n number; 5 begin 6 null; 7 end foo; 8 end; 9 /
Package body created. SQL> SQL> show errors; No errors.
</source>
Create a package level variable
<source lang="sql">
SQL> SQL> create or replace
2 package variables as 3 g_public_number number := null; 4 procedure setPrivateNumber( p_num number ); 5 procedure print_private_number; 6 end variables; 7 /
Package created. SQL> SQL> create or replace
2 package body variables as 3 myNumber number := null; 4 5 procedure setPrivateNumber( p_num number ) is 6 begin 7 myNumber := p_num; 8 end setPrivateNumber; 9 10 procedure print_private_number is 11 begin 12 dbms_output.put_line( nvl(to_char(myNumber),"null" ) ); 13 end print_private_number; 14 15 end variables; 16 /
Package body created. SQL>
</source>
Create package and member cursor
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE gifts (
2 gift_id INTEGER CONSTRAINT gifts_pk PRIMARY KEY, 3 gift_type_id INTEGER , 4 name VARCHAR2(30) NOT NULL, 5 description VARCHAR2(50), 6 price NUMBER(5, 2) 7 );
Table created. SQL> SQL> SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (1, 1, "Flower", "Birthday", 19.95); 1 row created. SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (2, 1, "Computer", "New Year", 30.00); 1 row created. SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (3, 2, "iPod", "Birthday", 25.99); 1 row created. SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (4, 2, "iPhone", "New Year", 13.95); 1 row created. SQL> INSERT INTO gifts (gift_id, gift_type_id, name, description, price) VALUES (5, 2, "Book", "Birthday", 49.99); 1 row created. SQL> SQL> CREATE OR REPLACE PACKAGE gift_package AS
2 TYPE t_ref_cursor IS REF CURSOR; 3 FUNCTION get_gifts_ref_cursor RETURN t_ref_cursor; 4 PROCEDURE updatePrice (p_gift_id IN gifts.gift_id%TYPE,p_factor IN NUMBER); 5 END gift_package; 6 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY gift_package AS
2 FUNCTION get_gifts_ref_cursor 3 RETURN t_ref_cursor IS 4 gifts_ref_cursor t_ref_cursor; 5 BEGIN 6 OPEN gifts_ref_cursor FOR SELECT gift_id, name, price FROM gifts; 7 8 RETURN gifts_ref_cursor; 9 END get_gifts_ref_cursor; 10 11 PROCEDURE updatePrice(p_gift_id IN gifts.gift_id%TYPE,p_factor IN NUMBER) AS 12 v_gift_count INTEGER; 13 BEGIN 14 SELECT COUNT(*)INTO v_gift_count FROM gifts WHERE gift_id = p_gift_id; 15 IF v_gift_count = 1 THEN 16 UPDATE gifts 17 SET price = price * p_factor 18 WHERE gift_id = p_gift_id; 19 COMMIT; 20 END IF; 21 EXCEPTION 22 WHEN OTHERS THEN 23 ROLLBACK; 24 END updatePrice; 25 END gift_package; 26 /
Package body created. SQL> SQL> drop table gifts; Table dropped.
</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>
Cursor operation between package functions
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PACKAGE StudentFetch AS
2 TYPE t_lecturer IS TABLE OF lecturer%ROWTYPE 3 INDEX BY BINARY_INTEGER; 4 PROCEDURE OpenCursor; 5 PROCEDURE CloseCursor; 6 FUNCTION FetchRows(p_BatchSize IN OUT NUMBER,p_lecturer OUT t_lecturer) 7 RETURN BOOLEAN; 8 PROCEDURE PrintRows(p_BatchSize IN NUMBER, 9 p_lecturer IN t_lecturer); 10 END StudentFetch; 11 /
Package created. SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE PACKAGE BODY StudentFetch AS
2 CURSOR myAllLecturer IS
3 SELECT *
4 FROM lecturer
5 ORDER BY ID;
6
7 PROCEDURE OpenCursor IS
8 BEGIN
9 OPEN myAllLecturer;
10 END OpenCursor;
11
12 PROCEDURE CloseCursor IS
13 BEGIN
14 CLOSE myAllLecturer;
15 END CloseCursor;
16
17 FUNCTION FetchRows(p_BatchSize IN OUT NUMBER,
18 p_lecturer OUT t_lecturer)
19 RETURN BOOLEAN IS
20 v_Finished BOOLEAN := TRUE;
21 BEGIN
22 FOR v_Count IN 1..p_BatchSize LOOP
23 FETCH myAllLecturer INTO p_lecturer(v_Count);
24 IF myAllLecturer%NOTFOUND THEN
25 v_Finished := FALSE;
26 p_BatchSize := v_Count - 1;
27 EXIT;
28 END IF;
29 END LOOP;
30 RETURN v_Finished;
31 END FetchRows;
32
33 PROCEDURE PrintRows(p_BatchSize IN NUMBER,
34 p_lecturer IN t_lecturer) IS
35 BEGIN
36 FOR v_Count IN 1..p_BatchSize LOOP
37 DBMS_OUTPUT.PUT("ID: " || p_lecturer(v_Count).ID);
38 DBMS_OUTPUT.PUT(" Name: " || p_lecturer(v_Count).first_name);
39 DBMS_OUTPUT.PUT_LINE(" " || p_lecturer(v_Count).last_name);
40 END LOOP;
41 END PrintRows;
42 END StudentFetch;
43 /
Package body created. SQL> show errors No errors. SQL> SQL> SQL> SQL> drop table lecturer; Table dropped. SQL>
</source>
dependencies between an anonymous calling block and package runtime state.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PACKAGE SimplePkg AS
2 v_GlobalVar NUMBER := 1; 3 PROCEDURE UpdateVar; 4 END SimplePkg; 5 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY SimplePkg AS
2 PROCEDURE UpdateVar IS 3 BEGIN 4 v_GlobalVar := 7; 5 END UpdateVar; 6 END SimplePkg; 7 /
Package body created. SQL> SQL>
</source>
Global definition
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PACKAGE global_def IS
2 pv_execution_num PLS_INTEGER := 0; 3 PROCEDURE increment_value (p_increment_num PLS_INTEGER); 4 END global_def; 5 /
Package created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE BODY global_def IS
2 lv_execution_num PLS_INTEGER := 0;
3 PROCEDURE increment_display (p_value_num PLS_INTEGER)IS
4 BEGIN
5 DBMS_OUTPUT.PUT_LINE("Variable Value: " || p_value_num);
6 END increment_display;
7 PROCEDURE increment_value (p_increment_num PLS_INTEGER) IS
8 BEGIN
9 lv_execution_num := lv_execution_num + p_increment_num;
10 increment_display (lv_execution_num);
11 END increment_value;
12 END global_def;
13 /
Package body created. SQL> SQL> SQL> SQL> BEGIN
2 global_def.increment_value(5); 3 global_def.increment_value(3); 4 global_def.increment_value(1); 5 END; 6 /
Variable Value: 14 Variable Value: 17 Variable Value: 18 PL/SQL procedure successfully completed.
</source>
Nested package reference
<source lang="sql">
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> create or replace package myPackage1 is
2 procedure P1; end; 3 /
Package created. SQL> create or replace package body myPackage1 is
2 procedure P1 is 3 v_cnt number; 4 begin 5 select count(*) into v_cnt from emp; 6 end; 7 end; 8 /
Package body created. SQL> create or replace package myPackage2 is procedure P2; end;
2 /
Package created. SQL> create or replace package body myPackage2 is
2 procedure P2 is 3 begin 4 myPackage1.P1; 5 end; 6 end; 7 /
Package body created. SQL> create or replace package myPackage3 is procedure P3; end;
2 /
Package created. SQL> create or replace package body myPackage3 is
2 procedure P3 is 3 begin 4 myPackage2.P2; 5 end; 6 end; 7 /
Package body created. SQL> create or replace package myPackage4 is
2 procedure P4; end; 3 /
Package created. SQL> create or replace package body myPackage4 is
2 procedure P4 is 3 begin 4 myPackage3.P3; 5 end; 6 end; 7 /
Package body created. SQL> create or replace package body myPackage1 is
2 procedure P1 is 3 v_cnt number; 4 begin 5 select count(*) into v_cnt from emp where empno > 0; 6 end; 7 end; 8 /
Package body created. SQL> SQL> select object_name, object_type, status
2 from user_objects
3 where object_name in ("MYPACKAGE1","MYPACKAGE2","MYPACKAGE3","MYPACKAGE4");
no rows selected SQL> SQL> SQL> drop table emp; Table dropped.
</source>
Overloaded packages.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PACKAGE Overload AS
2 FUNCTION TestFunc(p1 IN NUMBER) 3 RETURN VARCHAR2; 4 PRAGMA RESTRICT_REFERENCES(TestFunc, WNDS, RNDS, WNPS, RNPS); 5 6 FUNCTION TestFunc(pA IN VARCHAR2,pB IN DATE) 7 RETURN VARCHAR2; 8 PRAGMA RESTRICT_REFERENCES(TestFunc, WNDS, RNDS, WNPS, RNPS); 9 END Overload; 10 /
Package created. SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE PACKAGE BODY Overload AS
2 FUNCTION TestFunc(p1 IN NUMBER) 3 RETURN VARCHAR2 IS 4 BEGIN 5 RETURN "Version 1"; 6 END TestFunc; 7 8 FUNCTION TestFunc(pA IN VARCHAR2,pB IN DATE) 9 RETURN VARCHAR2 IS 10 BEGIN 11 RETURN "Version 2"; 12 END TestFunc; 13 END Overload; 14 /
Package body created. SQL> show errors No errors. SQL> SQL> SELECT Overload.TestFunc(1) FROM dual; OVERLOAD.TESTFUNC(1)
Version 1 1 row selected. SQL> SELECT Overload.TestFunc("abc", SYSDATE) FROM dual; OVERLOAD.TESTFUNC("ABC",SYSDAT
Version 2 1 row selected. SQL>
</source>
Overloading based on user defined object types.
<source lang="sql">
SQL> CREATE OR REPLACE TYPE myTable1 AS OBJECT (
2 f NUMBER 3 ); 4 /
Type created. SQL> SQL> SQL> CREATE OR REPLACE TYPE myTable2 AS OBJECT (
2 f NUMBER 3 ); 4 /
Type created. SQL> SQL> SQL> SQL> CREATE OR REPLACE PACKAGE Overload AS
2 PROCEDURE Proc(p1 IN myTable1); 3 PROCEDURE Proc(p1 IN myTable2); 4 END Overload; 5 /
Package created. SQL> SQL> SQL> SQL> show errors No errors. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE BODY Overload AS
2 PROCEDURE Proc(p1 IN myTable1) IS
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("Proc(myTable1): " || p1.f);
5 END Proc;
6
7 PROCEDURE Proc(p1 IN myTable2) IS
8 BEGIN
9 DBMS_OUTPUT.PUT_LINE("Proc(myTable2): " || p1.f);
10 END Proc;
11 END Overload;
12 /
Package body created. SQL> SQL> show errors No errors. SQL> SQL> SQL> set serveroutput on SQL> DECLARE
2 v_Obj1 myTable1 := myTable1(1); 3 v_OBj2 myTable2 := myTable2(2); 4 BEGIN 5 Overload.Proc(v_Obj1); 6 Overload.proc(v_Obj2); 7 END; 8 /
Proc(myTable1): 1 Proc(myTable2): 2 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> drop type myTable1 force; Type dropped. SQL> SQL> drop type myTable2 force; Type dropped. SQL> SQL> SQL> SQL> SQL>
</source>
Overloading Packaged Subprograms
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL, 3 department CHAR(3) NOT NULL, 4 course NUMBER(3) NOT NULL, 5 grade CHAR(1) 6 );
Table created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created. SQL> SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "HIS", 101, "A");
1 row created. SQL> SQL> SQL> SQL> CREATE TABLE session (
2 department CHAR(3), 3 course NUMBER(3), 4 description VARCHAR2(2000), 5 max_lecturer NUMBER(3), 6 current_lecturer NUMBER(3), 7 num_credits NUMBER(1), 8 room_id NUMBER(5) 9 );
Table created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 101, "History 101", 30, 11, 4, 20000);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("HIS", 301, "History 301", 30, 0, 4, 20004);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 101, "Computer Science 101", 50, 0, 4, 20001);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 203, "Economics 203", 15, 0, 3, 20002);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("CS", 102, "Computer Science 102", 35, 3, 4, 20003);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 410, "Music 410", 5, 4, 3, 20005);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("ECN", 101, "Economics 101", 50, 0, 4, 20007);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("NUT", 307, "Nutrition 307", 20, 2, 4, 20008);
1 row created. SQL> SQL> INSERT INTO session(department, course, description, max_lecturer, current_lecturer, num_credits, room_id)
2 VALUES ("MUS", 100, "Music 100", 100, 0, 3, NULL);
1 row created. SQL> SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> CREATE OR REPLACE PACKAGE ClassPackage AS
2 PROCEDURE AddStudent(p_ID IN lecturer.id%TYPE, 3 p_Department IN session.department%TYPE, 4 p_Course IN session.course%TYPE); 5 6 PROCEDURE AddStudent(p_FirstName IN lecturer.first_name%TYPE, 7 p_LastName IN lecturer.last_name%TYPE, 8 p_Department IN session.department%TYPE, 9 p_Course IN session.course%TYPE); 10 11 END ClassPackage; 12 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY ClassPackage AS
2 PROCEDURE AddStudent(p_ID IN lecturer.id%TYPE, 3 p_Department IN session.department%TYPE, 4 p_Course IN session.course%TYPE) IS 5 BEGIN 6 INSERT INTO myStudent (student_id, department, course) 7 VALUES (p_ID, p_Department, p_Course); 8 COMMIT; 9 END AddStudent; 10 11 PROCEDURE AddStudent(p_FirstName IN lecturer.first_name%TYPE, 12 p_LastName IN lecturer.last_name%TYPE, 13 p_Department IN session.department%TYPE, 14 p_Course IN session.course%TYPE) IS 15 myLecturerID lecturer.ID%TYPE; 16 BEGIN 17 SELECT ID 18 INTO myLecturerID 19 FROM lecturer 20 WHERE first_name = p_FirstName 21 AND last_name = p_LastName; 22 23 INSERT INTO myStudent (student_id, department, course) 24 VALUES (myLecturerID, p_Department, p_Course); 25 COMMIT; 26 END AddStudent; 27 28 END ClassPackage; 29 /
Package body created. SQL> drop table myStudent; Table dropped. SQL> drop table session; Table dropped. SQL> drop table lecturer; Table dropped.
</source>
Package for output employee table and log message
<source lang="sql">
SQL> SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> create table myLogTable(
2 username varchar2(30), 3 date_time timestamp, 4 message varchar2(4000) );
Table created. SQL> SQL> SQL> SQL> create or replace
2 package employee_pkg as 3 procedure print_ename( p_empno number ); 4 procedure print_sal( p_empno number ); 5 end employee_pkg; 6 /
Package created. SQL> SQL> create or replace
2 package body employee_pkg as 3 4 procedure log_message( p_message varchar2 ) is 5 pragma autonomous_transaction; 6 begin 7 insert into myLogTable( username, date_time, message ) 8 values ( user, current_date, p_message ); 9 commit; 10 end log_message; 11 12 function get_emp_record( p_empno number ) return emp%rowtype is 13 l_emp_record emp%rowtype; 14 begin 15 log_message( "Looking for record where EMPNO = " || p_empno ); 16 select * 17 into l_emp_record 18 from emp 19 where empno = p_empno; 20 return l_emp_record; 21 exception 22 when NO_DATA_FOUND then 23 return null; 24 end get_emp_record; 25 26 procedure print_data( p_emp_record emp%rowtype, 27 p_column varchar2 ) is 28 l_value varchar2(4000); 29 begin 30 if p_emp_record.empno is null then 31 log_message( "No Data Found." ); 32 dbms_output.put_line( "No Data Found." ); 33 else 34 case p_column 35 when "ENAME" then 36 l_value := p_emp_record.ename; 37 when "SAL" then 38 l_value := nvl(p_emp_record.sal,0); 39 else 40 l_value := "Invalid Column"; 41 end case; 42 log_message( "About to print " || p_column || " = " || l_value ); 43 dbms_output.put_line( p_column || " = " || l_value ); 44 end if; 45 end print_data; 46 47 procedure print_ename( p_empno number ) is 48 begin 49 print_data( get_emp_record( p_empno ), "ENAME" ); 50 end print_ename; 51 52 procedure print_sal( p_empno number ) is 53 begin 54 print_data( get_emp_record( p_empno ), "SAL" ); 55 end print_sal; 56 57 end employee_pkg; 58 /
Package body created. SQL> SQL> exec employee_pkg.print_ename( 7781 ); No Data Found. PL/SQL procedure successfully completed. SQL> SQL> exec employee_pkg.print_ename( 7782 ); ENAME = CLARK PL/SQL procedure successfully completed. SQL> SQL> select * from myLogTable; USERNAME DATE_TIME
---------------------------------------------------------------------------
MESSAGE
SYS 11-JUN-08 08.44.45.000000 PM Looking for record where EMPNO = 7781 SYS 11-JUN-08 08.44.45.000000 PM No Data Found. SYS 11-JUN-08 08.44.45.000000 PM Looking for record where EMPNO = 7782
USERNAME DATE_TIME
---------------------------------------------------------------------------
MESSAGE
SYS 11-JUN-08 08.44.45.000000 PM About to print ENAME = CLARK
SQL> SQL> execute employee_pkg.print_ename( 1234 ); No Data Found. PL/SQL procedure successfully completed. SQL> SQL> execute employee_pkg.print_ename( 7782 ); ENAME = CLARK PL/SQL procedure successfully completed. SQL> SQL> execute employee_pkg.print_sal( 7782 ); SAL = 2450 PL/SQL procedure successfully completed. SQL> SQL> drop table myLogTable; Table dropped. SQL> drop table emp; Table dropped.
</source>
Package initialization.
<source lang="sql">
SQL> SQL> SQL> CREATE OR REPLACE PACKAGE Random AS
2 3 PROCEDURE ChangeSeed(p_NewSeed IN NUMBER); 4 5 FUNCTION Rand RETURN NUMBER; 6 7 PROCEDURE GetRand(p_RandomNumber OUT NUMBER); 8 9 FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER; 10 11 PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,p_MaxVal IN NUMBER); 12 END Random; 13 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY Random AS
2 3 v_Multiplier CONSTANT NUMBER := 2; 4 v_Increment CONSTANT NUMBER := 1; 5 6 v_Seed number := 1; 7 8 PROCEDURE ChangeSeed(p_NewSeed IN NUMBER) IS 9 BEGIN 10 v_Seed := p_NewSeed; 11 END ChangeSeed; 12 13 FUNCTION Rand RETURN NUMBER IS 14 BEGIN 15 v_Seed := MOD(v_Multiplier * v_Seed + v_Increment,(2 ** 32)); 16 RETURN BITAND(v_Seed/(2 ** 16), 32767); 17 END Rand; 18 19 PROCEDURE GetRand(p_RandomNumber OUT NUMBER) IS 20 BEGIN 21 p_RandomNumber := Rand; 22 END GetRand; 23 24 FUNCTION RandMax(p_MaxVal IN NUMBER) RETURN NUMBER IS 25 BEGIN 26 RETURN MOD(Rand, p_MaxVal) + 1; 27 END RandMax; 28 29 PROCEDURE GetRandMax(p_RandomNumber OUT NUMBER,p_MaxVal IN NUMBER) IS 30 BEGIN 31 -- Simply call RandMax and return the value. 32 p_RandomNumber := RandMax(p_MaxVal); 33 END GetRandMax; 34 35 BEGIN 36 ChangeSeed(TO_NUMBER(TO_CHAR(SYSDATE, "SSSSS"))); 37 END Random; 38 /
Package body created. SQL>
</source>
Package level Exception
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PACKAGE Globals AS
2 myException EXCEPTION; 3 END Globals; 4 /
Package created. SQL> SQL> BEGIN
2 BEGIN 3 RAISE Globals.myException; 4 END; 5 EXCEPTION 6 WHEN Globals.myException THEN 7 RAISE; 8 END; 9 /
BEGIN
ERROR at line 1: ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at line 7
SQL> SQL>
</source>
Package level variable as global level variable
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PACKAGE global_def IS
2 pv_execution_num PLS_INTEGER := 0; 3 PROCEDURE increment_value (p_increment_num PLS_INTEGER); 4 END global_def; 5 /
Package created. SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE("Variable Value: " || global_def.pv_execution_num);
3 global_def.pv_execution_num := global_def.pv_execution_num + 1;
4 DBMS_OUTPUT.PUT_LINE("Variable Value: " || global_def.pv_execution_num);
5 global_def.pv_execution_num := global_def.pv_execution_num + 1;
6 DBMS_OUTPUT.PUT_LINE("Variable Value: " || global_def.pv_execution_num);
7 global_def.pv_execution_num := global_def.pv_execution_num + 1;
8 DBMS_OUTPUT.PUT_LINE("Variable Value: " || global_def.pv_execution_num);
9 END;
10 /
Variable Value: 3 Variable Value: 4 Variable Value: 5 Variable Value: 6 PL/SQL procedure successfully completed.
</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 allows forward references, thus opening the possibilities for recursion.
<source lang="sql">
SQL> SQL> SQL> create package RECURSION is
2 procedure A(p number); 3 procedure B(p number); 4 end; 5 /
SQL> SQL> SQL> create or replace
2 package body RECURSION is 3 4 procedure A(p number) is 5 begin 6 if p < 5 then 7 B(p+1); 8 end if; 9 end; 10 11 procedure B(p number) is 12 begin 13 if p < 5 then 14 A(p+1); 15 end if; 16 end; 17 18 end; 19 /
Package body created. SQL>
</source>
Package with package level cursor variable
<source lang="sql">
SQL> SQL> CREATE TABLE employees
2 ( employee_id number(10) not null, 3 last_name varchar2(50) not null, 4 email varchar2(30), 5 hire_date date, 6 job_id varchar2(30), 7 department_id number(10), 8 salary number(6), 9 manager_id number(6) 10 );
Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)
2 values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)
2 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1004, "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)
2 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);
1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)
2 values( 1008, "Oracle", "oracle1@g.ru", SYSDATE, "DBA", 20000, 4, 1006);
1 row created. SQL> SQL> create or replace package people as
2 type employees_ref_cursor_type is ref cursor return employees%rowtype; 3 4 function get_employee( p_employee_id in number ) 5 return employees_ref_cursor_type; 6 7 function get_employees( p_search in varchar2 ) 8 return employees_ref_cursor_type; 9 10 end people; 11 /
Package created. SQL> show error No errors. SQL> SQL> create or replace package body people as
2 function get_employee( p_employee_id in number ) 3 return employees_ref_cursor_type 4 is 5 l_emp employees_ref_cursor_type; 6 begin 7 open l_emp for 8 select * from employees where employee_id = p_employee_id; 9 return l_emp; 10 end get_employee; 11 12 function get_employees( p_search in varchar2 ) 13 return employees_ref_cursor_type 14 is 15 l_emps employees_ref_cursor_type; 16 begin 17 open l_emps for 18 select * from employees 19 order by employee_id; 20 return l_emps; 21 end get_employees; 22 end people; 23 /
Package body created. SQL> show error No errors. SQL> SQL> drop table employees; Table dropped. SQL> SQL> --
</source>
Persistance of packaged variables.
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE PersistPkg AS
2 TYPE t_StudentTable IS TABLE OF lecturer.ID%TYPE 3 INDEX BY BINARY_INTEGER; 4 5 v_MaxRows NUMBER := 5; 6 7 PROCEDURE Readlecturer(p_StudTable OUT t_StudentTable, p_NumRows OUT NUMBER); 8 9 END PersistPkg; 10 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY PersistPkg AS
2 CURSOR StudentCursor IS 3 SELECT ID 4 FROM lecturer 5 ORDER BY last_name; 6 7 PROCEDURE Readlecturer(p_StudTable OUT t_StudentTable, 8 p_NumRows OUT NUMBER) IS 9 v_Done BOOLEAN := FALSE; 10 v_NumRows NUMBER := 1; 11 BEGIN 12 IF NOT StudentCursor%ISOPEN THEN 13 OPEN StudentCursor; 14 END IF; 15 16 WHILE NOT v_Done LOOP 17 FETCH StudentCursor INTO p_StudTable(v_NumRows); 18 IF StudentCursor%NOTFOUND THEN 19 CLOSE StudentCursor; 20 v_Done := TRUE; 21 ELSE 22 v_NumRows := v_NumRows + 1; 23 IF v_NumRows > v_MaxRows THEN 24 v_Done := TRUE; 25 END IF; 26 END IF; 27 END LOOP; 28 29 p_NumRows := v_NumRows - 1; 30 END Readlecturer; 31 END PersistPkg; 32 /
Package body created. SQL> SQL> DECLARE
2 myLecturerTable PersistPkg.t_StudentTable;
3 v_NumRows NUMBER := PersistPkg.v_MaxRows;
4 myFirstName lecturer.first_name%TYPE;
5 v_LastName lecturer.last_name%TYPE;
6 BEGIN
7 PersistPkg.Readlecturer(myLecturerTable, v_NumRows);
8 DBMS_OUTPUT.PUT_LINE(" Fetched " || v_NumRows || " rows:");
9 FOR v_Count IN 1..v_NumRows LOOP
10 SELECT first_name, last_name
11 INTO myFirstName, v_LastName
12 FROM lecturer
13 WHERE ID = myLecturerTable(v_Count);
14 DBMS_OUTPUT.PUT_LINE(myFirstName || " " || v_LastName);
15 END LOOP;
16 END;
17 /
Fetched 5 rows: Jone Bliss Barbara Blues Rose Bond Sharon Clear Chris Elegant PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table lecturer; Table dropped. SQL>
</source>
RESTRICT_REFERENCES pragma.
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE StudentOps AS
2 FUNCTION FullName(p_ID IN lecturer.ID%TYPE) 3 RETURN VARCHAR2; 4 PRAGMA RESTRICT_REFERENCES(FullName, WNDS, WNPS, RNPS); 5 6 FUNCTION NumHistoryMajors 7 RETURN NUMBER; 8 PRAGMA RESTRICT_REFERENCES(NumHistoryMajors, WNDS); 9 END StudentOps; 10 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY StudentOps AS
2 v_NumHist NUMBER; 3 4 FUNCTION FullName(p_ID IN lecturer.ID%TYPE) 5 RETURN VARCHAR2 IS 6 myResult VARCHAR2(100); 7 BEGIN 8 SELECT first_name || " " || last_name 9 INTO myResult 10 FROM lecturer 11 WHERE ID = p_ID; 12 13 RETURN myResult; 14 END FullName; 15 16 FUNCTION NumHistoryMajors RETURN NUMBER IS 17 myResult NUMBER; 18 BEGIN 19 IF v_NumHist IS NULL THEN 20 SELECT COUNT(*) 21 INTO myResult 22 FROM lecturer 23 WHERE major = "History"; 24 v_NumHist := myResult; 25 ELSE 26 myResult := v_NumHist; 27 END IF; 28 29 RETURN myResult; 30 END NumHistoryMajors; 31 END StudentOps; 32 /
Package body created. SQL> SQL> drop table lecturer; Table dropped. SQL> SQL>
</source>
Student fetch package
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE StudentFetch AS
2 TYPE t_lecturer IS TABLE OF lecturer%ROWTYPE 3 INDEX BY BINARY_INTEGER; 4 PROCEDURE OpenCursor; 5 PROCEDURE CloseCursor; 6 FUNCTION FetchRows(p_BatchSize IN NUMBER := 5,p_lecturer OUT t_lecturer)RETURN BOOLEAN; 7 PROCEDURE PrintRows(p_BatchSize IN NUMBER,p_lecturer IN t_lecturer); 8 END StudentFetch; 9 /
Package created. SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE PACKAGE BODY StudentFetch AS
2 CURSOR myAllLecturer IS SELECT * FROM lecturer ORDER BY ID;
3 PROCEDURE OpenCursor IS
4 BEGIN
5 OPEN myAllLecturer;
6 END OpenCursor;
7
8 PROCEDURE CloseCursor IS
9 BEGIN
10 CLOSE myAllLecturer;
11 END CloseCursor;
12
13 FUNCTION FetchRows(p_BatchSize IN NUMBER := 5,p_lecturer OUT t_lecturer)
14 RETURN BOOLEAN IS
15 v_Finished BOOLEAN := TRUE;
16 BEGIN
17 FOR v_Count IN 1..p_BatchSize LOOP
18 FETCH myAllLecturer INTO p_lecturer(v_Count);
19 IF myAllLecturer%NOTFOUND THEN
20 v_Finished := FALSE;
21 EXIT;
22 END IF;
23 END LOOP;
24 RETURN v_Finished;
25 END FetchRows;
26
27 PROCEDURE PrintRows(p_BatchSize IN NUMBER,
28 p_lecturer IN t_lecturer) IS
29 BEGIN
30 FOR v_Count IN 1..p_BatchSize LOOP
31 DBMS_OUTPUT.PUT("ID: " || p_lecturer(v_Count).ID);
32 DBMS_OUTPUT.PUT(" Name: " || p_lecturer(v_Count).first_name);
33 DBMS_OUTPUT.PUT_LINE(" " || p_lecturer(v_Count).last_name);
34 END LOOP;
35 END PrintRows;
36 END StudentFetch;
37 /
Package body created. SQL> show errors No errors. SQL> SQL> SQL> set serveroutput on SQL> SQL> DECLARE
2 v_BatchSize NUMBER := 5; 3 v_lecturer StudentFetch.t_lecturer; 4 BEGIN 5 StudentFetch.OpenCursor; 6 WHILE StudentFetch.FetchRows(v_BatchSize, v_lecturer) LOOP 7 StudentFetch.PrintRows(v_BatchSize, v_lecturer); 8 END LOOP; 9 StudentFetch.CloseCursor; 10 END; 11 /
ID: 10001 Name: Scott Lawson ID: 10002 Name: Mar Wells ID: 10003 Name: Jone Bliss ID: 10004 Name: Man Kyte ID: 10005 Name: Pat Poll ID: 10006 Name: Tim Viper ID: 10007 Name: Barbara Blues ID: 10008 Name: David Large ID: 10009 Name: Chris Elegant ID: 10010 Name: Rose Bond PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table lecturer; Table dropped. SQL> SQL>
</source>
The pragma is not valid at the package level.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PACKAGE Auto3 AS
2 PRAGMA AUTONOMOUS_TRANSACTION; 3 PROCEDURE P; 4 PROCEDURE Q; 5 END Auto3; 6 /
Warning: Package created with compilation errors. SQL> show errors Errors for PACKAGE AUTO3: LINE/COL ERROR
-----------------------------------------------------------------
2/10 PLS-00710: PRAGMA AUTONOMOUS_TRANSACTION cannot be specified here SQL>
</source>
This package will not compile because the body does not match the specification.
<source lang="sql">
SQL> SQL> CREATE OR REPLACE PACKAGE PackageA AS
2 FUNCTION FunctionA(p_Parameter1 IN NUMBER, 3 p_Parameter2 IN DATE) 4 RETURN VARCHAR2; 5 END PackageA; 6 /
Package created. SQL> show errors No errors. SQL> SQL> CREATE OR REPLACE PACKAGE BODY PackageA AS
2 FUNCTION FunctionA(p_Parameter1 IN CHAR, 3 p_Parameter2 IN DATE) 4 RETURN VARCHAR2; 5 END PackageA; 6 /
Warning: Package Body created with compilation errors. SQL> show errors Errors for PACKAGE BODY PACKAGEA: LINE/COL ERROR
-----------------------------------------------------------------
2/12 PLS-00323: subprogram or cursor "FUNCTIONA" is declared in a
package specification and must be defined in the package body
2/12 PLS-00328: A subprogram body must be defined for the forward
declaration of FUNCTIONA.
SQL> SQL> SQL>
</source>
Use package level type as global variables
<source lang="sql">
SQL> create or replace package APPLICATION_TYPES is
2 subtype short_varchar2 is varchar2(40); 3 end; 4 /
Package created. SQL> create or replace procedure MY_PROC(p_input application_types.short_varchar2) is
2 v1 application_types.short_varchar2; 3 begin 4 null; 5 end; 6 /
Procedure created. SQL> create or replace package MY_PKG is
2 glob_var application_types.short_varchar2 := "asdf"; 3 end; 4 /
Package created. SQL>
</source>
Use package member variable to pass value
<source lang="sql">
SQL> SQL> create table t as select * from all_objects; Table created. SQL> SQL> create or replace package myPackage
2 as 3 type varchar2_array is table of varchar2(30) index by binary_integer; 4 type rc is ref cursor; 5 procedure index_by( p_owner in varchar2, p_object_name out varchar2_array,p_object_type out varchar2_array,p_timestamp out varchar2_array ); 6 procedure ref_cursor( p_owner in varchar2, p_cursor in out rc ); 7 end; 8 /
Package created. SQL> SQL> create or replace package body myPackage
2 as 3 procedure index_by( p_owner in varchar2,p_object_name out varchar2_array,p_object_type out varchar2_array,p_timestamp out varchar2_array ) 4 is 5 begin 6 select object_name, object_type, timestamp 7 bulk collect into p_object_name, p_object_type, p_timestamp 8 from t 9 where owner = p_owner; 10 end; 11 12 procedure ref_cursor( p_owner in varchar2,p_cursor in out rc ) 13 is 14 begin 15 open p_cursor for select object_name, object_type, timestamp from t where owner = p_owner; 16 end; 17 end; 18 /
Package body created. SQL> SQL> SQL> drop table t; Table dropped. SQL> SQL> SQL>
</source>
Without RESTRICT_REFERENCES pragma.
<source lang="sql">
SQL> SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY, 3 first_name VARCHAR2(20), 4 last_name VARCHAR2(20), 5 major VARCHAR2(30), 6 current_credits NUMBER(3) 7 );
Table created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created. SQL> SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created. SQL> SQL> SQL> CREATE OR REPLACE PACKAGE StudentOps AS
2 3 FUNCTION FullName(p_ID IN lecturer.ID%TYPE) 4 RETURN VARCHAR2; 5 FUNCTION NumHistoryMajors 6 RETURN NUMBER; 7 END StudentOps; 8 /
Package created. SQL> SQL> CREATE OR REPLACE PACKAGE BODY StudentOps AS
2 v_NumHist NUMBER; 3 4 FUNCTION FullName(p_ID IN lecturer.ID%TYPE) 5 RETURN VARCHAR2 IS 6 myResult VARCHAR2(100); 7 BEGIN 8 SELECT first_name || " " || last_name 9 INTO myResult 10 FROM lecturer 11 WHERE ID = p_ID; 12 13 RETURN myResult; 14 END FullName; 15 16 FUNCTION NumHistoryMajors RETURN NUMBER IS 17 myResult NUMBER; 18 BEGIN 19 IF v_NumHist IS NULL THEN 20 SELECT COUNT(*) 21 INTO myResult 22 FROM lecturer 23 WHERE major = "History"; 24 v_NumHist := myResult; 25 ELSE 26 myResult := v_NumHist; 27 END IF; 28 29 RETURN myResult; 30 END NumHistoryMajors; 31 END StudentOps; 32 /
Package body created. SQL> SQL> SQL> drop table lecturer; Table dropped. SQL>
</source>
