Halo数据库Package功能简单演示

CREATE [ OR REPLACE ] PACKAGE package_name  [ { AUTHID DEFINER } | { AUTHID CURRENT_USER } ]  -- 相关权限设置  { IS | AS }  [ declaration; ] ... -- 此处可声明自定义类型、常量、变量、游标等    [                       {                 -- 存储过程声明        PROCEDURE proc_name       ([ argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ] ... ]) ;      }      |     {                 -- 函数声明          FUNCTION func_name ([ argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ] ... ])          RETURN rettype ;       }    ] ... END [ package_name ] ;  CREATE [ OR REPLACE ] PACKAGE BODY package_name  { IS | AS }  [ private_declaration; ] ...     -- 可定义私有常量、变量、游标、函数、存储过程等... 不对外开放供Pakcage内部使用     [     {       PROCEDURE proc_name[ {argname [ IN | IN OUT | OUT ] argtype [ DEFAULT value ] ... }]         { IS | AS }       [ declaration; ] [, ...]        BEGIN         statement; [...]        [ EXCEPTION           { WHEN exception [OR exception] [...]] THEN statement; }           [...]        ]       END [ proc_name ] ;    }    |    {      FUNCTION func_name [ argument_list ]        RETURN rettype [DETERMINISTIC]        { IS | AS }       [ declaration; ] [, ...]        BEGIN         statement; [...]        [ EXCEPTION          { WHEN exception [ OR exception ] [...] THEN statement; }          [...]        ]       END [ func_name ] ;    }      ] ... END [ package_name ] ;

DROP PACKAGE package_name;  -- 移除整个包DROP PACKAGE BODY package_name; -- 移除当前包体

CREATE OR REPLACE PACKAGE PKG_CONSTANT AS    C_CODE_SUCCESS  CONSTANT VARCHAR2(10) := 'SUCCESS'; -- 声明常量并赋值    PROCEDURE PRO_TEST;END PKG_CONSTANT;                                     /
CREATE OR REPLACE PACKAGE BODY PKG_CONSTANT ASPROCEDURE PRO_TEST ASBEGINDBMS_OUTPUT.PUT_LINE('PKG_CONSTANT.C_CODE_SUCCESS: '||PKG_CONSTANT.C_CODE_SUCCESS);END PRO_TEST;END PKG_CONSTANT;/
-- 执行EXEC PKG_CONSTANT.PRO_TEST; 
-- 常量不被允许修改 尝试修改将会报错BEGINPKG_CONSTANT.C_CODE_SUCCESS := 'ERROR';DBMS_OUTPUT.PUT_LINE('PKG_CONSTANT.C_CODE_SUCCESS: '||PKG_CONSTANT.C_CODE_SUCCESS);END;/

CREATE OR REPLACE PACKAGE PKG_VAR AS    VAR_A   VARCHAR2;   -- 声明变量    PROCEDURE PRO_TEST;END PKG_VAR;/
CREATE OR REPLACE PACKAGE BODY PKG_VAR ASPROCEDURE PRO_TEST ASBEGIN-- 初次赋值PKG_VAR.VAR_A := 'VAR_TEST';DBMS_OUTPUT.PUT_LINE('PKG_VAR.VAR_A: '||PKG_VAR.VAR_A);-- 再次修改变量值PKG_VAR.VAR_A := 'VAR_A';DBMS_OUTPUT.PUT_LINE('PKG_VAR.VAR_A: '||PKG_VAR.VAR_A);END PRO_TEST;END PKG_VAR;/
-- 执行EXEC PKG_VAR.PRO_TEST;

CREATE TABLE example(manager_id INT, employee_id INT, employee VARCHAR2(30));INSERT INTO example VALUES(5, 1, 'Nick');INSERT INTO example VALUES(1, 2, 'Josh');INSERT INTO example VALUES(2, 3, 'Ali');INSERT INTO example VALUES(6, 4, 'Joe');INSERT INTO example VALUES(4, 5, 'Kyle');
CREATE OR REPLACE PACKAGE PKG_CURSORASTYPE PKG_TYPE_CURSOR IS REF CURSOR;PROCEDURE PRO_TEST;END PKG_CURSOR;/
CREATE OR REPLACE PACKAGE BODY PKG_CURSORASPROCEDURE PRO_TESTIS  cur_cursor PKG_TYPE_CURSOR;  cur_val example%ROWTYPE;BEGIN  OPEN cur_cursor FOR SELECT * FROM example;  LOOP   FETCH cur_cursor INTO cur_val;   EXIT WHEN NOT FOUND;   DBMS_OUTPUT.PUT_LINE('manager_id: '||cur_val.manager_id||' employee_id: '||cur_val.employee_id||' employee: '||cur_val.employee);  END LOOP;  CLOSE cur_cursor;END PRO_TEST;END PKG_CURSOR;/
-- 执行EXEC PKG_CURSOR.PRO_TEST

CREATE OR REPLACE PACKAGE PKG_TEST AS  FUNCTION F_TEST( f_a INTEGER , f_b INTEGER ) RETURN INTEGER;  PROCEDURE PRO_TEST;END PKG_TEST;  /
CREATE OR REPLACE PACKAGE BODY PKG_TEST ASFUNCTION F_TEST( f_a IN INTEGER , f_b IN INTEGER )RETURN INTEGERAS  f_c INTEGER;BEGIN   f_c := f_a + f_b;  RETURN f_c;END F_TEST;
PROCEDURE PRO_TESTAS   var_a INTEGER;BEGIN    var_a := PKG_TEST.F_TEST(12,13);  DBMS_OUTPUT.PUT_line('PKG_TEST.F_TEST(12,13) = '|| var_a); END PRO_TEST; END PKG_TEST;/

CREATE OR REPLACE PACKAGE PKG_RECODE_TEST AS  TYPE RECODE_TYPE IS RECORD(    DS NUMBER(10, 3),    DA VARCHAR(2)    );  PROCEDURE PRO_TEST;END PKG_RECODE_TEST;  /
CREATE OR REPLACE PACKAGE BODY PKG_RECODE_TEST ASPROCEDURE PRO_TESTAS   recode_a RECODE_TYPE;BEGIN    SELECT 1,'x' INTO recode_a FROM dual;  DBMS_OUTPUT.PUT_line(recode_a.ds||' '||recode_a.da); END PRO_TEST; END PKG_RECODE_TEST;/
-- 执行EXEC PKG_RECODE_TEST.PRO_TEST;

CREATE OR REPLACE PACKAGE ERRLOGIS   no_such_table EXCEPTION;      PRAGMA EXCEPTION_INIT (no_such_table, -942);   invalid_table_name EXCEPTION;      PRAGMA EXCEPTION_INIT (invalid_table_name, -903);   PROCEDURE PRO_TEST;END;/
CREATE OR REPLACE PACKAGE BODY ERRLOG ASPROCEDURE PRO_TESTAS BEGIN  RAISE ERRLOG.no_such_table;  DBMS_OUTPUT.PUT_LINE('ERROR');EXCEPTION  WHEN no_such_table THEN  DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE||' SQLERRM: '||SQLERRM); END PRO_TEST; END ERRLOG;/
-- 执行EXEC ERRLOG.PRO_TEST;

 \set                         -- 查看ENABLE_PL_BLOCK参数 \set ENABLE_PL_BLOCK on      -- 使能ENABLE_PL_BLOCK