Oracle-包的创建和使用（游标的简单使用，临时表，动态 sql）

[目录]

参考
测试表-test_v_hwhao1
测试数据
例子1-无参
例子2-存储过程有入参，出参

参考

Oracle 参照游标(SYS_REFCURSOR)使用

ORACLE中%TYPE和%ROWTYPE的使用

测试表-test_v_hwhao1

测试数据

例子1-无参

新建包说明

CREATE OR REPLACE PACKAGE P_Test_Update IS
  /* =============================================
  *   PROCEDURE:
  *     update_table_test_v_hwhao1_opdate
  *   DESCRIPTION:
  *     更行表 test_v_hwhao1 的 opdate 时间
  *   ARGUMENT:
  *   RETURN:
  *
  *   HISTORY:
  * =============================================*/
  PROCEDURE update_table_opdate;
END P_Test_Update;

查看创建结果

新建包体

CREATE OR REPLACE PACKAGE BODY P_Test_Update IS
/* =============================================
  *   PROCEDURE:
  *     update_table_test_v_hwhao1_opdate
  *   DESCRIPTION:
  *     更行表 test_v_hwhao1 的 opdate 时间
  *   ARGUMENT:
  *   RETURN:
  *
  *   HISTORY:
  * =============================================*/
PROCEDURE update_table_opdate IS
--定义游标 
CURSOR test_v_hwhao1_cursor IS
SELECT * FROM test_v_hwhao1;
--定义变量 opdate，并赋值当前时间
--opdate date := sysdate;
--开始
BEGIN
--循环读取游标数据
FOR item in test_v_hwhao1_cursor LOOP 
  update test_v_hwhao1 t set t.opdate = sysdate where t.no = item.no;
END LOOP;
END update_table_opdate;
END P_Test_Update;

查看创建结果

扩展

包说明和包体同时在一个 sql 中创建，中间需要加 /，否则会报错，具体参考

Oracle-已编译但有错误

调用

--调用
begin
  -- Call the procedure
  p_test_update.update_table_opdate;
end;

查看测试结果

--查询更新结果
select * from test_v_hwhao1

结果：

例子2-存储过程有入参，出参

创建包说明

CREATE OR REPLACE PACKAGE P_Test_Calc IS
  /*****************************************************************************************************************/
  -- 存储过程： 删除临时表
  -- 参数说明： IN_TABLE_NAME（临时表名称）
  /*****************************************************************************************************************/
  PROCEDURE PRO_DROP_TABLE(IN_TABLE_NAME IN VARCHAR2);
  /*****************************************************************************************************************/
  -- 存储过程： 把数据添加到临时表，并返回数据
  -- 参数说明： 
  --            IN_TABLE_NAME（临时表名称）
  --            OUT_CALC_RESULT（返回结果集）
  /*****************************************************************************************************************/
  PROCEDURE PRO_CALC_TABLE(IN_TABLE_NAME IN VARCHAR2, OUT_CALC_RESULT OUT SYS_REFCURSOR);
END P_Test_Calc;

创建包体

CREATE OR REPLACE PACKAGE BODY P_Test_Calc IS
  /*****************************************************************************************************************/
  -- 存储过程： 删除临时表
  -- 参数说明： IN_TABLE_NAME（临时表名称）
  /*****************************************************************************************************************/
  PROCEDURE PRO_DROP_TABLE(IN_TABLE_NAME IN VARCHAR2) IS
    VAR_NUM NUMBER;
  BEGIN
    SELECT COUNT(1) INTO VAR_NUM FROM USER_TABLES WHERE TABLE_NAME = UPPER(IN_TABLE_NAME);
    IF VAR_NUM > 0 THEN
      EXECUTE IMMEDIATE 'DROP TABLE ' || IN_TABLE_NAME;
    END IF;
  END PRO_DROP_TABLE;
    /*****************************************************************************************************************/
  -- 存储过程： 把数据添加到临时表，并返回数据
  -- 参数说明： 
  --            IN_TABLE_NAME（临时表名称）
  --            OUT_CALC_RESULT（返回结果集）
  /*****************************************************************************************************************/
  PROCEDURE PRO_CALC_TABLE(IN_TABLE_NAME IN VARCHAR2, OUT_CALC_RESULT OUT SYS_REFCURSOR) IS
     VAR_STR_SQL VARCHAR2(4000);      --SQL语句
  BEGIN
    --1、创建返回数据的临时表
    PRO_DROP_TABLE(IN_TABLE_NAME);   --先删除临时表
    VAR_STR_SQL := 'CREATE GLOBAL TEMPORARY TABLE '||IN_TABLE_NAME||' (NO VARCHAR2(500), NAME VARCHAR2(500)) ON COMMIT DELETE ROWS';
    EXECUTE IMMEDIATE VAR_STR_SQL;
    --2、根据指标类型，获取并遍历计算指标数据
    FOR Q IN (SELECT * FROM test_v_hwhao1 T WHERE T.NO > 1)
    LOOP
      --将结果插入到临时表中
      VAR_STR_SQL := 'INSERT INTO ' ||IN_TABLE_NAME|| ' VALUES(''' ||Q.NO|| ''',''' ||Q.NAME|| ''')';
      EXECUTE IMMEDIATE VAR_STR_SQL;
    END LOOP;
    --3、将临时表数据赋值返回
    VAR_STR_SQL := 'SELECT * FROM ' ||IN_TABLE_NAME;
    OPEN OUT_CALC_RESULT FOR VAR_STR_SQL;
  END PRO_CALC_TABLE;
END P_Test_Calc;

调用-使用 PL\SQL

这里留个坑，目前还没找到直接使用 sql 测试的方法，我是使用查看包体的方式来测试的。

查看包体内容

右键测试

运行测试

运行没有报错

删除包

--删除
drop package body P_Test_Calc;
drop package P_Test_Calc;

调用-使用 sql 调用测试并查看结果

不指定参数

2019年7月23日16:02:16
这里留个坑，希望通过直接使用 sql 调用对应包，并通过返回参数查看数据。
2019年9月9日-填坑

declare
   --定义一个游标变量
  Out_Calc_Result sys_refcursor;
  --定义一个表示表中一行记录的变量
  Item test_v_hwhao1%rowtype;
  --临时表名称
  In_Table_Name VARCHAR2(30) := 'test_v_hwhao1_temp';
begin
  --调用存储过程创建临时表，添加数据
  P_Test_Calc.PRO_CALC_TABLE(In_Table_Name,Out_Calc_Result);
  --读取游标
  loop 
  fetch Out_Calc_Result into Item.NO, Item.NAME;          
    --游标没有数据，退出循环
    exit when Out_Calc_Result%notfound;
    --输出临时表数据
    dbms_output.put_line(Item.NO||'-'||Item.NAME);
  end loop;
  --删除临时表
  P_Test_Calc.PRO_DROP_TABLE(In_Table_Name);
end;

输出

2-0
3-理想的生活是排除T
5-想到一百年后无少T
6-我想有人能理解我T
4-睡到二三更时凡荣T
10-测试老大哥
11-测试老大哥-11
7-老大哥T-T
8-小老弟T
9-99T

指定参数

declare
   --定义一个游标变量
  Out_Calc_Result sys_refcursor;
  --定义一个表示表中一行记录的变量
  Item test_v_hwhao1%rowtype;
  --临时表名称
  In_Table_Name VARCHAR2(30) := 'test_v_hwhao1_temp';
begin
  --调用存储过程创建临时表，添加数据
  P_Test_Calc.PRO_CALC_TABLE(OUT_CALC_RESULT => Out_Calc_Result,IN_TABLE_NAME => In_Table_Name);
  --读取游标
  loop 
  fetch Out_Calc_Result into Item.NO, Item.NAME;          
    --游标没有数据，退出循环
    exit when Out_Calc_Result%notfound;
    --输出临时表数据
    dbms_output.put_line(Item.NO||'-'||Item.NAME);
  end loop;
  --删除临时表
  P_Test_Calc.PRO_DROP_TABLE(In_Table_Name);
end;

输出

2-0
3-理想的生活是排除T
5-想到一百年后无少T
6-我想有人能理解我T
4-睡到二三更时凡荣T
10-测试老大哥
11-测试老大哥-11
7-老大哥T-T
8-小老弟T
9-99T