Oracle PL/SQL Tutorial/Function Procedure Packages/Package Variables
Содержание
- 1 Cursor variable in a package
- 2 Define constant in a package
- 3 Demonstrate using a packaged ref cursor for passing sets
- 4 Package constant variable
- 5 Package level cursor variable
- 6 Pre-filled table collection of varchars in a package
- 7 Private field
- 8 Serially Reusable Packages
- 9 Test unit for package scopes
- 10 Use function to initialize the package level variable
- 11 Use package to define variable and use across code blocks
Cursor variable in a package
SQL>
SQL>
SQL> create table company_site(
2 site_no number(4) not null,
3 site_descr varchar2(20) not null
4 );
Table created.
SQL> insert into company_site values (1,"New York");
1 row created.
SQL> insert into company_site values (2,"Washington");
1 row created.
SQL> insert into company_site values (3,"Chicago");
1 row created.
SQL> insert into company_site values (4,"Dallas");
1 row created.
SQL> insert into company_site values (5,"San Francisco");
1 row created.
SQL>
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE myPackage
2 IS
3 PRAGMA SERIALLY_REUSABLE;
4 CURSOR cursor_site IS
5 SELECT * from company_site ORDER BY site_no;
6 PROCEDURE displaySites;
7 END myPackage;
8 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY myPackage
2 IS
3 PRAGMA SERIALLY_REUSABLE;
4 PROCEDURE displaySites
5 IS
6 site_rec company_site%ROWTYPE;
7 BEGIN
8 OPEN cursor_site;
9 FETCH cursor_site INTO site_rec;
10 dbms_output.put_line(TO_CHAR(site_rec.site_no)||" "||site_rec.site_descr);
11 FETCH cursor_site INTO site_rec;
12 dbms_output.put_line(TO_CHAR(site_rec.site_no)||" "||site_rec.site_descr);
13 END displaySites;
14 END myPackage;
15 /
Package body created.
SQL> BEGIN
2 myPackage.displaySites;
3 END;
4 /
1 New York
2 Washington
PL/SQL procedure successfully completed.
SQL>
SQL> drop table company_site;
Table dropped.
Define constant in a package
SQL>
SQL> CREATE TABLE employee
2 (employee_id NUMBER(7),
3 last_name VARCHAR2(25),
4 first_name VARCHAR2(25),
5 userid VARCHAR2(8),
6 start_date DATE,
7 comments VARCHAR2(255),
8 manager_id NUMBER(7),
9 title VARCHAR2(25),
10 department_id NUMBER(7),
11 salary NUMBER(11, 2),
12 commission_pct NUMBER(4, 2)
13 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE empinfo
2 IS
3 bysal CONSTANT INTEGER := 1;
4 bysaldesc CONSTANT INTEGER := 2;
5 bydept CONSTANT INTEGER := 3;
6 byname CONSTANT INTEGER := 4;
7 TYPE two_pieces_t IS RECORD (str VARCHAR2(100), num NUMBER);
8 TYPE emp_cvt IS REF CURSOR RETURN two_pieces_t;
9 FUNCTION open (query_number IN INTEGER) RETURN emp_cvt;
10 PROCEDURE show (query_number IN INTEGER);
11 END;
12 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY empinfo
2 IS
3 FUNCTION open (query_number IN INTEGER) RETURN emp_cvt
4 IS
5 retval emp_cvt;
6 BEGIN
7 IF query_number = bysal
8 THEN
9 OPEN retval FOR
10 SELECT last_name, salary FROM employee ORDER BY salary;
11 ELSIF query_number = bysaldesc
12 THEN
13 OPEN retval FOR
14 SELECT last_name, salary FROM employee ORDER BY salary DESC;
15 ELSIF query_number = bydept
16 THEN
17 OPEN retval FOR
18 SELECT last_name, department_id FROM employee ORDER BY department_id;
19 ELSIF query_number = byname
20 THEN
21 OPEN retval FOR
22 SELECT first_name || " " || last_name, salary
23 FROM employee ORDER BY last_name;
24 END IF;
25 RETURN retval;
26 END;
27
28 PROCEDURE show (query_number IN INTEGER)
29 IS
30 cv emp_cvt;
31 rec cv%ROWTYPE;
32 BEGIN
33 cv := open (query_number);
34 LOOP
35 FETCH cv INTO rec;
36 EXIT WHEN cv%NOTFOUND;
37 IF cv%ROWCOUNT = 1
38 THEN
39 DBMS_OUTPUT.PUT_LINE (RPAD ("-", 60, "-"));
40 DBMS_OUTPUT.PUT_LINE ("Contents of Query " || query_number);
41 DBMS_OUTPUT.PUT_LINE (RPAD ("-", 60, "-"));
42 END IF;
43 DBMS_OUTPUT.PUT_LINE (RPAD (rec.str, 30) || rec.num);
44 END LOOP;
45 CLOSE cv;
46 END;
47
48 END;
49 /
Package body created.
SQL> drop table employee;
Table dropped.
Demonstrate using a packaged ref cursor for passing sets
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> 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>
SQL> create or replace package mytypes
2 as
3 type g_rc is ref cursor;
4 end;
5 /
Package created.
SQL> show errors
No errors.
SQL>
SQL> create or replace procedure cust_names
2 (p_rc out mytypes.g_rc)
3 as
4 begin
5 open p_rc for "select firstname, lastname
6 from employee";
7 end;
8 /
Procedure created.
SQL> show errors
No errors.
SQL>
SQL> var temp refcursor
SQL> exec cust_names(:temp)
PL/SQL procedure successfully completed.
SQL> print temp
FIRSTNAME LASTNAME
--------------- --------------------
Joe Jones
Sue Smith
Peggy Anderson
Jill Andy
Carl OK
Jee Peter
Paul Baker
Steve Young
Joe Mona
Karen Hackett
Jack Bob
11 rows selected.
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
SQL>
Package constant variable
SQL>
SQL> CREATE OR REPLACE PACKAGE cc_debug
2 IS
3 debug_active CONSTANT BOOLEAN := TRUE;
4
5 trace_level CONSTANT PLS_INTEGER := 10;
6
7 END cc_debug;
8 /
Package created.
SQL>
Package level cursor variable
SQL>
SQL> CREATE TABLE employee (
2 employee_id NUMBER(38,0)
3 ,deptno NUMBER(3,0) NOT NULL
4 ,first_name VARCHAR2(95) NOT NULL
5 ,last_name VARCHAR2(95) NOT NULL
6 ,salary NUMBER(11,2)
7 );
Table created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE onecur
2 IS
3 CURSOR onerow (employee_id_in IN employee.employee_id%TYPE) IS
4 SELECT * FROM employee WHERE employee_id = employee_id_in;
5
6 PROCEDURE open_onerow(employee_id_in IN employee.employee_id%TYPE,close_if_open IN BOOLEAN := TRUE);
7 PROCEDURE close_onerow;
8 END onecur;
9 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY onecur IS
2 PROCEDURE open_onerow (employee_id_in IN employee.employee_id%TYPE,close_if_open IN BOOLEAN := TRUE)
3 IS
4 v_close BOOLEAN := NVL (close_if_open, TRUE);
5 v_open BOOLEAN := TRUE;
6 BEGIN
7 IF onerow%ISOPEN AND v_close
8 THEN
9 CLOSE onerow;
10 ELSIF onerow%ISOPEN AND NOT v_close
11 THEN
12 v_open := FALSE;
13 END IF;
14 IF v_open THEN
15 OPEN onerow (employee_id_in);
16 END IF;
17 END;
18
19 PROCEDURE close_onerow IS
20 BEGIN
21 IF onerow%ISOPEN
22 THEN
23 CLOSE onerow;
24 END IF;
25 END;
26 END onecur;
27 /
Package body created.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Pre-filled table collection of varchars in a package
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE strings_nt IS TABLE OF VARCHAR2(100);
2 /
Type created.
SQL>
SQL>
SQL> CREATE OR REPLACE PACKAGE employees_pkg
2 IS
3 vancouver_employees strings_nt := strings_nt ("R", "H", "D", "S", "C");
4 newyork_employees strings_nt := strings_nt ("H", "S", "A");
5 boston_employees strings_nt := strings_nt ("S", "D");
6
7 PROCEDURE show_employees (title_in IN VARCHAR2, employees_in IN strings_nt
8 );
9 END;
10 /
Package created.
SQL> SHO ERR
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY employees_pkg
2 IS
3 PROCEDURE show_employees (title_in IN VARCHAR2,employees_in IN strings_nt)
4 IS
5 BEGIN
6 DBMS_OUTPUT.put_line (title_in);
7
8 FOR indx IN employees_in.FIRST .. employees_in.LAST
9 LOOP
10 DBMS_OUTPUT.put_line (indx || " = " || employees_in (indx));
11 END LOOP;
12
13 END show_employees;
14 END;
15 /
Package body created.
SQL> SHOw error
No errors.
Private field
SQL>
SQL> CREATE OR REPLACE PACKAGE valerr
2 IS
3 FUNCTION get RETURN VARCHAR2;
4 END valerr;
5 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY valerr
2 IS
3 v VARCHAR2(1);
4
5 FUNCTION get RETURN VARCHAR2 -- Added Line
6 IS
7 BEGIN
8 RETURN v;
9 END;
10 BEGIN
11 v := "ABC";
12
13 EXCEPTION
14 WHEN OTHERS
15 THEN
16 DBMS_OUTPUT.PUT_LINE ("Error initializing valerr:");
17 DBMS_OUTPUT.PUT_LINE (SQLERRM);
18
19 END valerr;
20 /
Package body created.
SQL>
SQL>
SQL>
Serially Reusable Packages
SQL> CREATE OR REPLACE PACKAGE myPackage
2 IS
3 PRAGMA SERIALLY_REUSABLE;
4 num_var NUMBER;
5 char_var VARCHAR2(20);
6 PROCEDURE initialize;
7 FUNCTION display_num RETURN NUMBER;
8 FUNCTION display_char RETURN VARCHAR2;
9 END myPackage;
10 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY myPackage
2 IS
3 PRAGMA SERIALLY_REUSABLE;
4 PROCEDURE initialize
5 IS
6 BEGIN
7 num_var :=100;
8 char_var :="Test String1";
9 END;
10 FUNCTION display_num RETURN NUMBER
11 IS
12 BEGIN
13 RETURN (num_var);
14 END;
15 FUNCTION display_char RETURN VARCHAR2
16 IS
17 BEGIN
18 RETURN (char_var);
19 END;
20 END myPackage;
21 /
Package body created.
SQL> DECLARE
2 v_num NUMBER;
3 v_char VARCHAR2(20);
4 BEGIN
5 myPackage.initialize;
6 v_num :=myPackage.display_num;
7 v_char :=myPackage.display_char;
8 dbms_output.put_line(TO_CHAR(v_num)||" "||v_char);
9 END;
10 /
100 Test String1
PL/SQL procedure successfully completed.
SQL>
Test unit for package scopes
SQL>
SQL>
SQL> create or replace package SCOPES as
2 gv_scope varchar2(80) :=
3 "I""m a global (or package spec) variable";
4
5 FUNCTION my_scope_is_global
6 return varchar2;
7
8 PROCEDURE my_scope_is_global;
9
10 end SCOPES;
11 /
Package created.
SQL>
SQL>
SQL> create or replace package body SCOPES as
2 iv_scope varchar2(80) := "an instance variable";
3
4
5 FUNCTION my_scope_is_instance
6 return varchar2 is
7 v_answer_1 varchar2(3) := "Yes";
8 begin
9 dbms_output.put_line(chr(9)||gv_scope);
10 return v_answer_1;
11 end my_scope_is_instance;
12
13
14 FUNCTION my_scope_is_global
15 return varchar2 is
16 v_answer_2 varchar2(3) := "Yes";
17 begin
18 dbms_output.put_line(chr(9)||iv_scope);
19 return v_answer_2;
20 end my_scope_is_global;
21
22
23 PROCEDURE my_scope_is_instance is
24 v_answer_3 varchar2(3) := "Yes";
25 begin
26 dbms_output.put_line(chr(9)||gv_scope);
27 dbms_output.put_line(v_answer_3);
28 end my_scope_is_instance;
29
30
31 PROCEDURE my_scope_is_global is
32 v_answer_4 varchar2(3) := "Yes";
33 begin
34 dbms_output.put_line(chr(9)||iv_scope);
35 dbms_output.put_line(v_answer_4);
36 end my_scope_is_global;
37
38
39 end SCOPES;
40 /
Package body created.
SQL>
SQL>
SQL>
SQL> declare
2 v_scope varchar2(40) := "I""m a local variable";
3 FUNCTION my_scope_is_local
4 return varchar2 is
5 v_answer_0 varchar2(3) := "Yes";
6 begin
7 return v_answer_0;
8 end my_scope_is_local;
9
10 PROCEDURE my_scope_is_local is
11 v_answer varchar2(3) := "Yes";
12 begin
13 dbms_output.put_line(v_answer);
14 end my_scope_is_local;
15 begin
16
17 dbms_output.put_line(v_scope);
18
19 dbms_output.put_line(SCOPES.gv_scope);
20
21 dbms_output.put_line(my_scope_is_local());
22
23 dbms_output.put_line(SCOPES.my_scope_is_global());
24
25 my_scope_is_local();
26
27 SCOPES.my_scope_is_global();
28
29
30 end;
31 /
I"m a local variable
I"m a global (or package spec) variable
Yes
an instance variable
Yes
Yes
an instance variable
Yes
PL/SQL procedure successfully completed.
SQL>
SQL>
Use function to initialize the package level variable
SQL>
SQL> CREATE TABLE keepcount (counter INTEGER);
Table created.
SQL>
SQL> INSERT INTO keepcount VALUES (1);
1 row created.
SQL>
SQL> CREATE OR REPLACE FUNCTION updcount RETURN INTEGER
2 IS
3 BEGIN
4 UPDATE keepcount SET counter = counter + 1;
5 RETURN 1;
6 END;
7 /
Function created.
SQL>
SQL> CREATE OR REPLACE PACKAGE demo
2 AS
3 PRAGMA SERIALLY_REUSABLE;
4 global_x PLS_INTEGER := updcount;
5 END;
6 /
Package created.
SQL>
SQL> CREATE OR REPLACE PACKAGE demo2
2 AS
3 PRAGMA SERIALLY_REUSABLE;
4 FUNCTION global_x RETURN PLS_INTEGER;
5 END;
6 /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY demo2
2 AS
3 PRAGMA SERIALLY_REUSABLE;
4 g_global_x PLS_INTEGER := updcount;
5 FUNCTION global_x RETURN PLS_INTEGER
6 IS BEGIN RETURN g_global_x; END;
7 END;
8 /
Package body created.
SQL>
SQL> DROP TABLE keepcount;
Table dropped.
SQL>
Use package to define variable and use across code blocks
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>
SQL> CREATE OR REPLACE PACKAGE custpack AS
2 TYPE cust_table_type IS TABLE OF employee%rowtype
3 INDEX BY BINARY_INTEGER;
4 END;
5 /
Package created.
SQL>
SQL>
SQL> CREATE OR REPLACE PROCEDURE cust_list (p1 OUT custpack.cust_table_type)
2 IS
3 indx PLS_INTEGER := 1;
4 BEGIN
5 FOR cust_rec IN (select * from employee) LOOP
6 p1(indx) := cust_rec;
7 indx := indx + 1;
8 END LOOP;
9 END;
10 /
SP2-0804: Procedure created with compilation warnings
SQL>
SQL> DECLARE
2 cust_table custpack.cust_table_type;
3 BEGIN
4 cust_list(cust_table);
5 FOR x IN 1..cust_table.COUNT LOOP
6 dbms_output.put_line(cust_table(x).lastname);
7 END LOOP;
8 END;
9 /
Jones
Smith
Anderson
Andy
OK
Peter
Baker
Young
Mona
Hackett
Bob
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table employee;
Table dropped.
SQL>