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
<source lang="sql">
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.</source>
Define constant in a package
<source lang="sql">
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.</source>
Demonstrate using a packaged ref cursor for passing sets
<source lang="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> 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></source>
Package constant variable
<source lang="sql">
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></source>
Package level cursor variable
<source lang="sql">
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></source>
Pre-filled table collection of varchars in a package
<source lang="sql">
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.</source>
Private field
<source lang="sql">
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></source>
Serially Reusable Packages
<source lang="sql">
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></source>
Test unit for package scopes
<source lang="sql">
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></source>
Use function to initialize the package level variable
<source lang="sql">
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></source>
Use package to define variable and use across code blocks
<source lang="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> 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></source>
