Oracle PL/SQL Tutorial/Function Procedure Packages/Package Body
Содержание
- 1 Call function in a Package
- 2 Create a package containing stored procedure DELETE_ORDERS and stored function GET_employee_NAME.
- 3 Method overload
- 4 Package declaration and body
- 5 Package Function with "PRAGMA AUTONOMOUS_TRANSACTION"
- 6 Package initialization.
- 7 Package method overloading
- 8 Package with only one function
- 9 Package with two procedures
- 10 Reference method from another package
- 11 Use of "get" and "set" prefixes
- 12 Use package member variable to pass value
- 13 Use package method in a procedure
Call function in a Package
SQL>
SQL>
SQL>
SQL> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE myPackage
2 IS
3 FUNCTION getName(ip_product_id NUMBER,ip_company_id NUMBER)
4 RETURN VARCHAR2;
5 PRAGMA RESTRICT_REFERENCES(getName,WNDS,WNPS);
6 END myPackage;
7 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY myPackage
2 IS
3 FUNCTION getName(ip_product_id NUMBER,ip_company_id NUMBER)
4 RETURN VARCHAR2
5 IS
6 v_name VARCHAR2(120);
7 BEGIN
8 SELECT "Org Name: (Short) "||company_short_name||" (Long) "||company_long_name
9 INTO v_name
10 FROM company
11 WHERE product_id = ip_product_id
12 AND company_id = ip_company_id;
13 RETURN (v_name);
14 END getName;
15 END myPackage;
16 /
Package body created.
SQL>
SQL> -- Calling the above packaged function from SQL
SQL> SELECT myPackage.getName(product_id,company_id) "Formatted Org Name"
2 FROM company
3 ORDER BY product_id,company_id;
Formatted Org Name
----------------------------------------------------------------------
Org Name: (Short) A Inc. (Long) Long Name A Inc.
Org Name: (Short) B Inc. (Long) Long Name B Inc.
Org Name: (Short) C Inc. (Long) Long Name C Inc.
Org Name: (Short) D Inc. (Long) Long Name D Inc.
Org Name: (Short) E Inc. (Long) Long Name E Inc.
Org Name: (Short) F Inc. (Long) Long Name F Inc.
6 rows selected.
SQL>
SQL>
SQL> drop table company;
Table dropped.
SQL>
Create a package containing stored procedure DELETE_ORDERS and stored function GET_employee_NAME.
SQL>
SQL>
SQL>
SQL> create table employee
2 (
3 empl_no integer primary key
4 ,lastname varchar2(20) not null
5 ,firstname varchar2(15) not null
6 ,midinit varchar2(1)
7 ,street varchar2(30)
8 ,city varchar2(20)
9 ,state varchar2(2)
10 ,zip varchar2(5)
11 ,zip_4 varchar2(4)
12 ,area_code varchar2(3)
13 ,phone varchar2(8)
14 ,company_name varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created.
SQL>
SQL>
SQL>
SQL> create table ord
2 (
3 order_no integer primary key
4 ,empl_no integer
5 ,order_date date not null
6 ,total_order_price number(7,2)
7 ,deliver_date date
8 ,deliver_time varchar2(7)
9 ,payment_method varchar2(2)
10 ,emp_no number(3,0)
11 ,deliver_name varchar2(35)
12 ,gift_message varchar2(100)
13 );
Table created.
SQL>
SQL>
SQL>
SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,add_months(sysdate, -1), 235.00, "14-Feb-1999", "12 noon", "CA",1, null, "Gift for wife");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,add_months(sysdate, -2), 50.98, "14-feb-1999", "1 pm", "CA",7, "Rose", "Happy Valentines Day to Mother");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,add_months(sysdate, -3), 35.99, "14-feb-1999", "1 pm", "VS",2, "Ruby", "Happy Valentines Day to Mother");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,add_months(sysdate, -4), 19.95, "14-feb-1999", "5 pm", "CA",2, "Coy", "Happy Valentines Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,add_months(sysdate, -7), 35.95, "21-jun-1999", "12 noon", "VS", 2, "Fill", "Happy Birthday from Joe");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, add_months(sysdate, -8), 35.95, "1-jan-2000", "12 noon", "DI",3, "Laura", "Happy New Year""s from Lawrence");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, add_months(sysdate, -9), 75.95, "2-jan-2000", "12 noon", "CA",7, "Sara", "Happy Birthday from Lawrence" );
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, add_months(sysdate, -10), 19.95, sysdate, "2:30 pm", "VG",2, "OK", "Happy Valentines Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, add_months(sysdate, -11), 30.00, sysdate+2, "1:30 pm", "VG",2, "Hi", "Happy Birthday Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, add_months(sysdate, -12), 21.95, sysdate-2, "3:30 pm", "CA",2, "Jack", "Happy Birthday Day to You");
1 row created.
SQL>
SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, add_months(sysdate, -1), 21.95, sysdate, "3:30 pm", "CA",2, "Jay", "Thanks for giving 100%!");
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> create table ord_history
2 (
3 order_no integer primary key
4 ,empl_no integer
5 ,order_date date not null
6 ,total_order_price number(7,2)
7 ,deliver_date date
8 ,deliver_time varchar2(7)
9 ,payment_method varchar2(2)
10 ,emp_no number(3,0)
11 ,deliver_name varchar2(35)
12 ,gift_message varchar2(100)
13 );
Table created.
SQL>
SQL>
SQL> create or replace package mypackage as
2 procedure delete_orders (p_days in number);
3 function get_employee_name (p_empl_no in number) return varchar2 ;
4 end ;
5 /
Package created.
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> create or replace package body mypackage as
2 procedure delete_orders
3 (p_days in number)
4 is
5 begin
6 insert into ord_history
7 select *
8 from ord
9 where order_date < sysdate - p_days;
10 if sql%notfound then
11 dbms_output.put_line("No orders old than " || p_days || "days");
12 end if;
13 delete from ord
14 where order_date < sysdate - p_days;
15 commit;
16 end;
17
18 function get_employee_name
19 (p_empl_no in number)
20 return varchar2
21 is
22 v_name varchar2(40);
23 begin
24 select lastname || ", " || firstname into v_name
25 from employee
26 where empl_no = p_empl_no;
27 return(v_name);
28 exception
29 when no_data_found then
30 raise_application_error(-20001, "employee not found.");
31 when others then
32 raise_application_error (-20002, "Unexpected error.");
33 end;
34 end;
35 /
Package body created.
SQL>
SQL> show errors
No errors.
SQL>
SQL> select mypackage.get_employee_name(1) from dual ;
MYPACKAGE.GET_EMPLOYEE_NAME(1)
--------------------------------------------------------------------------------
Jones, Joe
SQL>
SQL> drop table ord;
Table dropped.
SQL> drop table ord_history;
Table dropped.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
Method overload
SQL> create table gender (
2 id number,
3 code varchar2(30),
4 description varchar2(80),
5 active_date date default SYSDATE not null,
6 inactive_date date );
Table created.
SQL>
SQL>
SQL>
SQL> insert into gender ( id, code, description ) values ( 1, "F", "Female" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 2, "M", "Male" );
1 row created.
SQL> insert into gender ( id, code, description ) values ( 3, "U", "Unknown" );
1 row created.
SQL>
SQL>
SQL> create or replace PACKAGE genderS as
2 PROCEDURE get_code_descr(
3 ain_id in gender.id%TYPE,
4 aov_code out gender.code%TYPE,
5 aov_description out gender.description%TYPE);
6
7 PROCEDURE get_code_id_descr(
8 aiov_code in out gender.code%TYPE,
9 aon_id out gender.id%TYPE,
10 aov_description out gender.description%TYPE,
11 aid_on in gender.active_date%TYPE);
12
13 PROCEDURE get_code_id_descr(
14 aiov_code in out gender.code%TYPE,
15 aon_id out gender.id%TYPE,
16 aov_description out gender.description%TYPE);
17
18 FUNCTION get_id
19 return gender.id%TYPE;
20
21 FUNCTION get_id(
22 aiv_code in gender.code%TYPE)
23 return gender.id%TYPE;
24
25 end genderS;
26 /
Package created.
SQL>
SQL>
SQL> create or replace PACKAGE BODY genderS as
2 FUNCTION get_id
3 return gender.id%TYPE is
4 n_id gender.id%TYPE;
5 begin
6 select 1 into n_id from SYS.DUAL;
7 return n_id;
8 end get_id;
9
10
11 FUNCTION get_id(aiv_code in gender.code%TYPE )
12 return gender.id%TYPE is
13
14 n_id gender.id%TYPE;
15
16 begin
17 select id into n_id from gender where code = aiv_code;
18
19 return n_id;
20 end get_id;
21
22 PROCEDURE get_code_descr(
23 ain_id in gender.id%TYPE,
24 aov_code out gender.code%TYPE,
25 aov_description out gender.description%TYPE ) is
26
27 begin
28 select code,description into aov_code,aov_description
29 from gender
30 where id = ain_id;
31 end get_code_descr;
32
33
34 PROCEDURE get_code_id_descr(
35 aiov_code in out gender.code%TYPE,
36 aon_id out gender.id%TYPE,
37 aov_description out gender.description%TYPE,
38 aid_on in gender.active_date%TYPE ) is
39
40 v_code gender.code%TYPE;
41
42 begin
43 select id,description into aon_id,aov_description
44 from gender
45 where code = aiov_code
46 and aid_on between active_date and nvl(inactive_date, DATES.d_MAX);
47 exception
48 when NO_DATA_FOUND then
49 select id, code,description
50 into aon_id,v_code,aov_description
51 from gender
52 where code like aiov_code||"%"
53 and aid_on between active_date and nvl(inactive_date, DATES.d_MAX);
54
55 aiov_code := v_code;
56 end get_code_id_descr;
57
58
59 PROCEDURE get_code_id_descr(
60 aiov_code in out gender.code%TYPE,
61 aon_id out gender.id%TYPE,
62 aov_description out gender.description%TYPE ) is
63
64 begin
65 get_code_id_descr(
66 aiov_code,
67 aon_id,
68 aov_description,
69 SYSDATE );
70 end get_code_id_descr;
71 end genderS;
72 /
Package body created.
SQL>
SQL> show errors
No errors.
SQL>
SQL>
SQL> drop table gender;
Table dropped.
SQL>
Package declaration and body
SQL>
SQL>
SQL> set serveroutput on
SQL>
SQL> CREATE OR REPLACE PACKAGE myPackage
2 IS
3 PROCEDURE calc_total (zone_in IN VARCHAR2);
4
5 PROCEDURE calc_total (reg_in IN VARCHAR2);
6
7 END myPackage;
8 /
Package created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY myPackage
2 IS
3 PROCEDURE calc_total (zone_in IN VARCHAR2)
4 IS
5 BEGIN dbms_output.put_line ("zone"); END;
6
7 PROCEDURE calc_total (reg_in IN VARCHAR2)
8 IS
9 BEGIN dbms_output.put_line ("region"); END;
10
11 END myPackage;
12 /
Package body created.
SQL>
SQL>
SQL>
SQL>
Package Function with "PRAGMA AUTONOMOUS_TRANSACTION"
SQL>
SQL>
SQL> CREATE TABLE stuff_to_fix
2 (stuff VARCHAR2(1000),
3 fixed VARCHAR2(1));
Table created.
SQL>
SQL> CREATE OR REPLACE PACKAGE fixer AS
2
3 PROCEDURE fix_stuff;
4 PROCEDURE fix_this ( p_thing_to_fix VARCHAR2 );
5
6 END fixer;
7 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY fixer AS
2
3 PROCEDURE fix_this ( p_thing_to_fix VARCHAR2 ) IS
4 PRAGMA AUTONOMOUS_TRANSACTION;
5 BEGIN
6 INSERT INTO stuff_to_fix(stuff,fixed)VALUES(p_thing_to_fix,"N");
7 COMMIT;
8 END fix_this;
9
10 PROCEDURE fix_stuff IS
11 CURSOR curs_get_stuff_to_fix IS
12 SELECT stuff,ROWID FROM stuff_to_fix WHERE fixed = "N";
13
14 BEGIN
15
16 FOR v_stuff_rec IN curs_get_stuff_to_fix LOOP
17
18 EXECUTE IMMEDIATE v_stuff_rec.stuff;
19
20 UPDATE stuff_to_fix SET fixed = "Y" WHERE ROWID = v_stuff_rec.rowid;
21
22 END LOOP;
23
24 COMMIT;
25
26 END fix_stuff;
27
28 END fixer;
29 /
Package body created.
SQL>
SQL> DROP TABLE stuff_to_fix;
Table dropped.
SQL>
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 method overloading
SQL>
SQL> CREATE OR REPLACE PACKAGE outputPackage
2 IS
3 PROCEDURE printLine (val IN VARCHAR2);
4
5 PROCEDURE printLine (val IN DATE);
6
7 PROCEDURE printLine (val IN NUMBER);
8
9 PROCEDURE printLine (val IN BOOLEAN);
10 END outputPackage;
11 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY outputPackage
2 IS
3 PROCEDURE printLine (val IN DATE) IS
4 BEGIN
5 printLine (TO_CHAR (val, "MM/DD/YYYY HH24:MI:SS"));
6 END;
7
8 PROCEDURE printLine (val IN NUMBER) IS
9 BEGIN
10 printLine (TO_CHAR (val));
11 END;
12
13 PROCEDURE printLine (val IN BOOLEAN) IS
14 BEGIN
15 IF val
16 THEN
17 printLine ("TRUE");
18 ELSIF NOT val
19 THEN
20 printLine ("FALSE");
21 ELSE
22 printLine ("");
23 END IF;
24 END;
25
26 PROCEDURE printLine (val IN VARCHAR2)
27 IS
28 BEGIN
29 IF LENGTH (val) > 80
30 THEN
31 DBMS_OUTPUT.put_line (SUBSTR (val, 1, 80));
32 printLine (SUBSTR (val, 81));
33 ELSE
34 DBMS_OUTPUT.put_line (val);
35 END IF;
36 EXCEPTION
37 WHEN OTHERS
38 THEN
39 DBMS_OUTPUT.enable (1000000);
40 printLine (val);
41 END;
42 END outputPackage;
43 /
Package body created.
Package with only one function
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE valerr
2 IS
3 FUNCTION get RETURN VARCHAR2;
4 END valerr;
5 /
Package created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY valerr
2 IS
3 v VARCHAR2(1) := "ABC";
4
5 FUNCTION get RETURN VARCHAR2
6 IS
7 BEGIN
8 RETURN v;
9 END;
10 BEGIN
11 DBMS_OUTPUT.PUT_LINE ("Before I show you v...");
12
13 EXCEPTION
14 WHEN OTHERS
15 THEN
16 DBMS_OUTPUT.PUT_LINE ("Trapped the error!");
17
18 END valerr;
19 /
Package body created.
SQL>
SQL>
Package with two procedures
SQL>
SQL> CREATE OR REPLACE PACKAGE myPackage
2 IS
3 PROCEDURE proc1;
4 PROCEDURE proc2;
5 END;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY myPackage
2 IS
3 PROCEDURE proc1
4 IS
5 BEGIN
6 NULL;
7 END;
8
9 PROCEDURE proc2
10 IS
11 BEGIN
12 NULL;
13 END;
14 END;
15 /
Package body created.
Reference method from another package
SQL>
SQL> CREATE OR REPLACE PACKAGE myPackage1
2 IS
3 PROCEDURE proc;
4 END;
5 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY myPackage1
2 IS
3 PROCEDURE proc
4 IS
5 BEGIN
6 NULL;
7 END;
8 END;
9 /
Package body created.
SQL> CREATE OR REPLACE PACKAGE not_myPackage2
2 IS
3 PROCEDURE proc;
4 END;
5 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY not_myPackage2
2 IS
3 PROCEDURE proc
4 IS
5 BEGIN
6 NULL;
7 END;
8 END;
9 /
Package body created.
SQL>
SQL> CREATE OR REPLACE PACKAGE myPackage
2 IS
3 PROCEDURE proc1;
4 PROCEDURE proc2;
5 END;
6 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY myPackage
2 IS
3 PROCEDURE proc1
4 IS
5 BEGIN
6 myPackage1.proc;
7 END;
8
9 PROCEDURE proc2
10 IS
11 BEGIN
12 not_myPackage2.proc;
13 END;
14 END;
15 /
Package body created.
SQL>
SQL>
Use of "get" and "set" prefixes
SQL>
SQL> CREATE OR REPLACE PACKAGE myDate
2 IS
3 PROCEDURE set_mydate (date_in IN DATE);
4 FUNCTION get_mydate RETURN DATE;
5 END myDate;
6 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY myDate
2 IS
3 g_mydate DATE;
4
5 PROCEDURE set_mydate (date_in IN DATE)
6 IS
7 BEGIN
8 g_mydate := date_in;
9 END;
10
11 FUNCTION get_mydate RETURN DATE
12 IS
13 BEGIN
14 RETURN g_mydate;
15 END;
16 END myDate;
17 /
Package body created.
SQL>
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>
Use package method in a procedure
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>
SQL>
SQL> CREATE OR REPLACE PACKAGE onecur
2 IS
3 CURSOR onerow (id_in IN emp.empno%TYPE)
4 IS
5 SELECT * FROM emp WHERE empno = id_in;
6 END onecur;
7 /
Package created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE procA
2 IS
3 BEGIN
4 OPEN onecur.onerow (1005);
5 END procA;
6 /
Procedure created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE procB
2 IS
3 BEGIN
4 OPEN onecur.onerow (2356);
5 procA;
6 END procB;
7 /
Procedure created.
SQL>
SQL>
SQL> BEGIN
2 procB;
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-04063: package body "sqle.ONECUR" has errors
ORA-06508: PL/SQL: could not find program unit being called: "sqle.ONECUR"
ORA-06512: at "sqle.PROCB", line 4
ORA-06512: at line 2
SQL>
SQL>
SQL> drop table emp;
Table dropped.