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 AS
PROCEDURE PRO_TEST AS
BEGIN
DBMS_OUTPUT.PUT_LINE('PKG_CONSTANT.C_CODE_SUCCESS: '||PKG_CONSTANT.C_CODE_SUCCESS);
END PRO_TEST;
END PKG_CONSTANT;
/

-- 执行
EXEC PKG_CONSTANT.PRO_TEST; 

-- 常量不被允许修改 尝试修改将会报错
BEGIN
PKG_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 AS
PROCEDURE PRO_TEST AS
BEGIN
-- 初次赋值
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_CURSOR
AS
TYPE PKG_TYPE_CURSOR IS REF CURSOR;
PROCEDURE PRO_TEST;
END PKG_CURSOR;
/

CREATE OR REPLACE PACKAGE BODY PKG_CURSOR
AS
PROCEDURE PRO_TEST
IS
  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 AS
FUNCTION F_TEST( f_a IN INTEGER , f_b IN INTEGER )
RETURN INTEGER
AS
  f_c INTEGER;
BEGIN 
  f_c := f_a + f_b;
  RETURN f_c;
END F_TEST;

PROCEDURE PRO_TEST
AS 
  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 AS
PROCEDURE PRO_TEST
AS 
  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 ERRLOG
IS
   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 AS
PROCEDURE PRO_TEST
AS 
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;

三、注意事项

3.1、 无法正常结束，进入$状态

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