版本：Next

兼容Oracle的Package语法

Package是一组包含procedure、functions函数和游标等元素的组合。使用package来包装存储过程function/ procedure，包头(package)：包头部分申明包内数据类型，常量，变量，游标，子程序和异常错误处理，这些元素为包的公有元素，此包具有面向对象设计的特点，是对这些PL/SQL程序设计元素的封装。

package函数和存储过程

halo0root=# CREATE OR REPLACE PACKAGE pkg_a AS

halo0root $# FUNCTION f_test( f_a IN INTEGER , f_b IN INTEGER ) RETURN INTEGER;

halo0root $# PROCEDURE pro_test;

halo0root $# END pkg_a;

halo0root $# /

CREATE PACKAGE

halo0root=# CREATE OR REPLACE PACKAGE BODY pkg_a AS

halo0root $# FUNCTION f_test( f_a IN INTEGER , f_b IN INTEGER )

halo0root $# RETURN INTEGER

halo0root $# AS

halo0root $# f_c INTEGER;

halo0root $# BEGIN

halo0root $# f_c := f_a + f_b +5 ;

halo0root $# RETURN f_c;

halo0root $# END f_test;

halo0root $# PROCEDURE pro_test

halo0root $# AS

halo0root $# a INTEGER;

halo0root $# BEGIN

halo0root $# a:= pkg_a.f_test(12,13);

halo0root $# --RAISE NOTICE 'pkg_a.f_test(12,13) = %',a;

halo0root $# END pro_test;

halo0root $# END pkg_a;

halo0root $# /

CREATE PACKAGE BODY

package自定义类型

halo0root=# CREATE TABLE procedure_test(

ROWKEY VARCHAR(200),

COLUMNKEY VARCHAR(200),

CELLVALUE VARCHAR(200),

OLDVALUE VARCHAR(200),

TAG VARCHAR(200)

);

CREATE TABLE

halo0root=# INSERT INTO procedure_test VALUES('12345', '23456', '34567', '45678','56789');

INSERT 0 1

halo0root=# CREATE OR REPLACE package pkg_b AS

halo0root $# PROCEDURE my_procedure;

halo0root $# TYPE cur_pkg_type IS RECORD

halo0root $# (

halo0root $# ROWKEY VARCHAR(200),

halo0root $# COLUMNKEY VARCHAR(200),

halo0root $# CELLVALUE VARCHAR(200),

halo0root $# OLDVALUE VARCHAR(200),

halo0root $# TAG VARCHAR(200)

halo0root $# );

halo0root $# END pkg_b;

halo0root $# /

CREATE PACKAGE

alo0root=# CREATE OR REPLACE PACKAGE BODY pkg_b AS

halo0root $# PROCEDURE my_procedure IS

halo0root $# pkg_type cur_pkg_type;

halo0root $# BEGIN

halo0root $# SELECT \* INTO pkg_type FROM procedure_test AS t WHERE t.TAG = '56789';

halo0root $# DBMS_OUTPUT.put_line(pkg_type.ROWKEY | |' ' ||pkg_type.COLUMNKEY | |' ' | |pkg_type.CELLVALUE | |' '| |pkg_type.OLDVALUE | |' ' | |pkg_type.TAG);

halo0root $# END my_procedure;

halo0root $# END pkg_b;

halo0root $# /

CREATE PACKAGE BODY

调用

halo0root=# BEGIN

halo0root $# pkg_b.my_procedure();

halo0root $# END;

halo0root $# /

12345 23456 34567 45678 56789

Package游标

声明游标

游标是一种数据访问机制。可以将游标简单的看成是查询的结果集的一个指针，可以根据需要在结果集上面来回滚动，浏览需要的数据。

halo0root=# INSERT INTO my_procedure_test VALUES('ROWKEY', 'COLUMNKEY', 'CELLVALUE', 'OLDVALUE','TAG');

INSERT 0 1

halo0root $# TYPE pkg_type_cursor IS REF CURSOR;

halo0root $# PROCEDURE my_procedure;

halo0root $# END pkg_c;

halo0root $# /

CREATE PACKAGE

halo0root=# CREATE OR REPLACE PACKAGE BODY pkg_c

halo0root-# AS

halo0root $# PROCEDURE my_procedure

halo0root $# IS

halo0root $# cur_cursor pkg_type_cursor;

halo0root $# cur_rowtype procedure_test%ROWTYPE;

halo0root $# BEGIN

halo0root $# OPEN cur_cursor FOR SELECT \* FROM procedure_test ;

halo0root $# LOOP

halo0root $# FETCH cur_cursor INTO cur_rowtype;

halo0root $# EXIT WHEN NOT FOUND;

halo0root $# RAISE NOTICE '% % % % %',cur_rowtype.rowkey,cur_rowtype.columnkey,cur_rowtype.cellvalue,cur_rowtype.oldvalue,cur_rowtype.tag;

halo0root $# END LOOP;

halo0root $# CLOSE cur_cursor;

halo0root $# END ;

halo0root $# END pkg_c;

halo0root $# /

CREATE PACKAGE BODY

halo0root=# DECLARE

halo0root $# cur_cursor pkg_c.pkg_type_cursor;

halo0root $# BEGIN

halo0root $# RAISE notice 'a';

halo0root $# END;

halo0root $# /

调用

alo0root=# BEGIN

halo0root $# pkg_c.my_procedure();

halo0root $# END;

halo0root $# /

注意: 12345 23456 34567 45678 56789

DO

另外一种游标

CREATE OR REPLACE PACKAGE pkg_d IS

CURSOR cur_cursor IS SELECT * FROM my_procedure_test;

PROCEDURE my_procedure;

END;

/

halo0root=# CREATE OR REPLACE PACKAGE BODY pkg_d IS

halo0root $# PROCEDURE my_procedure IS

halo0root $# cur_rowtype cur_cursor%ROWTYPE;

halo0root $# BEGIN

halo0root $# OPEN cur_cursor;

halo0root $# LOOP

halo0root $# FETCH cur_cursor INTO cur_rowtype;

halo0root $# EXIT WHEN NOT FOUND;

halo0root $# RAISE NOTICE '% % % % %',cur_rowtype.rowkey,cur_rowtype.columnkey,cur_rowtype.cellvalue,cur_rowtype.oldvalue,cur_rowtype.tag;

halo0root $# END LOOP;

halo0root $# CLOSE cur_cursor;

halo0root $# END;

halo0root $# END pkg_d;

halo0root $# /

CREATE PACKAGE BODY

调用

halo0root=# BEGIN

halo0root $# pkg_d.my_procedure();

halo0root $# END;

halo0root $# /

注意:12345 23456 34567 45678 56789

注意:ROWKEY COLUMNKEY CELLVALUE OLDVALUE TAG

Package中的变量/异常/常量

变量：PACKAGE相当于JAVA中的一个class，在运行的时候被实例化，包变量就像实例中的实例变量一样。在Oracle中对包一个会话调用中，包变量是该会话中的“全局”变量。

创建

halo0root=# CREATE TABLE system_parameters (

halo0root(# audit_on VARCHAR(1) NOT NULL,

halo0root(# trace_on VARCHAR(1) NOT NULL,

halo0root(# debug_on VARCHAR(1) NOT NULL

halo0root(# );

CREATE TABLE

halo0root=# INSERT INTO system_parameters VALUES ('Y', 'N', 'N');

INSERT 0 1

halo0root=# CREATE OR REPLACE PACKAGE pkg_e AS

halo0root $# audit_on_ system_parameters.audit_on%TYPE;

halo0root $# trace_on_ system_parameters.trace_on%TYPE;

halo0root $# debug_on_ system_parameters.debug_on%TYPE;

halo0root $# PROCEDURE pro_test;

halo0root $# END pkg_e;

halo0root $# /

注意:类型关联 system_parameters.audit_on%TYPE 转换为 character varying

注意:类型关联 system_parameters.trace_on%TYPE 转换为 character varying

注意:类型关联 system_parameters.debug_on%TYPE 转换为 character varying

CREATE PACKAGE

call pkg_e.pro_test();

BEGIN

halo0root=# CREATE OR REPLACE PACKAGE BODY pkg_e AS

halo0root $# PROCEDURE pro_test AS

halo0root $# BEGIN

halo0root $# SELECT audit_on,

halo0root $# trace_on,

halo0root $# debug_on

halo0root $# INTO audit_on_,

halo0root $# trace_on_,

halo0root $# debug_on_

halo0root $# FROM system_parameters;

halo0root $# END pro_test;

halo0root $# END pkg_e;

halo0root $# /

注意:类型关联 system_parameters.audit_on%TYPE 转换为 character varying

注意:类型关联 system_parameters.trace_on%TYPE 转换为 character varying

注意:类型关联 system_parameters.debug_on%TYPE 转换为 character varying

CREATE PACKAGE BODY

调用：

call pkg_e.pro_test();

BEGIN

halo0root $# RAISE NOTICE 'Show global data';

halo0root $# RAISE NOTICE 'audit_on: %' , pkg_e.audit_on_;

halo0root $# RAISE NOTICE 'trace_on: %' , pkg_e.trace_on_;

halo0root $# RAISE NOTICE 'debug_on: %' , pkg_e.debug_on_;

halo0root $# RAISE NOTICE'Reset global data';

halo0root $# pkg_e.audit_on_ := 'N';

halo0root $# pkg_e.trace_on_ := 'Y';

halo0root $# pkg_e.debug_on_ := 'Y';

halo0root $# RAISE NOTICE 'audit_on: %' , pkg_e.audit_on_;

halo0root $# RAISE NOTICE 'trace_on: %' , pkg_e.trace_on_;

halo0root $# RAISE NOTICE 'debug_on: %' , pkg_e.debug_on_;

halo0root $# END;

halo0root $# /

注意: Show global data

注意: audit_on: Y

注意: trace_on: N

注意: debug_on: N

注意: Reset global data

注意: audit_on: N

注意: trace_on: Y

注意: debug_on: Y

DO

package异常

自定义异常

新功能允许我们定义自己的异常。与预定义异常不同的是，用户自定义异常必须声明，并且需要用 RAISE 语句显式地抛出。

halo0root=# DECLARE

halo0root $# v_student_id int := -20;

halo0root $# v_total_courses EXCEPTION;

halo0root $# BEGIN

halo0root $# if v_student_id < 0 THEN

halo0root $# raise v_total_courses;

halo0root $# end if;

halo0root $# exception

halo0root $# when v_total_courses then

halo0root $# dbms_output.put_line('v_total_courses');

halo0root $# end;

halo0root $# /

v_total_courses

DO

package常量

常量指的是不会变化的量，一旦赋值，就不能修改值。

halo0root=# CREATE OR REPLACE PACKAGE PG_CONSTANT2 AS

halo0root $# C_CODE_ERROR constant VARCHAR2(10) := 'ERROR';

halo0root $# END PG_CONSTANT2;

halo0root $# /

CREATE PACKAGE

halo0root=# BEGIN

halo0root $# raise notice '%',PG_CONSTANT2.C_CODE_ERROR;

halo0root $# PG_CONSTANT2.C_CODE_ERROR:='a';

halo0root $# end;

halo0root $# /

ERROR: variable "pg_constant2.c_code_error" is declared CONSTANT

LINE 3: PG_CONSTANT2.C_CODE_ERROR:='a';

^

QUERY: BEGIN

raise notice '%',PG_CONSTANT2.C_CODE_ERROR;

PG_CONSTANT2.C_CODE_ERROR:='a';

end;

package函数和存储过程​

package自定义类型​

调用​

Package游标​

声明游标​

调用​

另外一种游标​

调用​

Package中的变量/异常/常量​

调用：​

package异常​

自定义异常​

package常量​