Oracle PL/SQL/PL SQL/Package — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:45, 26 мая 2010
Содержание
- 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
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.
A PL/SQL package with two methods
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>
call utitilities package
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>
Counter package
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>
Create a package declaration and create its body after
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.
Create a package level variable
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>
Create package and member cursor
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.
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>
Cursor operation between package functions
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>
dependencies between an anonymous calling block and package runtime state.
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>
Global definition
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.
Nested package reference
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.
Overloaded packages.
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>
Overloading based on user defined object types.
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>
Overloading Packaged Subprograms
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.
Package for output employee table and log message
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.
Package initialization.
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>
Package level Exception
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>
Package level variable as global level variable
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.
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 allows forward references, thus opening the possibilities for recursion.
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>
Package with package level cursor variable
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> --
Persistance of packaged variables.
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>
RESTRICT_REFERENCES pragma.
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>
Student fetch package
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>
The pragma is not valid at the package level.
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>
This package will not compile because the body does not match the specification.
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>
Use package level type as global variables
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>
Use package member variable to pass value
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>
Without RESTRICT_REFERENCES pragma.
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>