存储过程示例：在存储过程中使用临时表

 

CREATE OR REPLACE PROCEDURE PRODUCT_TEMP_UPDATE_PRC AS
  PC_DELESTR   VARCHAR2(50); --删除临时表记录语句
  PC_CREATESTR VARCHAR2(500); --创建临时表
  TABEXT       VARCHAR2(10); --用于判断临时表是否存在中间变量

  CUR_CTGY   PRODUCTINFO.CATEGORY%TYPE;
  CUR_PRTIFO PRODUCTINFO%ROWTYPE;

  CURSOR CUR_CATEGORY --产品表中的产品类型游标
  IS
    SELECT CATEGORY FROM PRODUCTINFO GROUP BY CATEGORY;

  CURSOR CUR_PROINFO(CTGY VARCHAR) IS
    SELECT *
      FROM (SELECT *
              FROM PRODUCTINFO
             WHERE CATEGORY = CTGY
             ORDER BY PRODUCTPRICE ASC)
     WHERE ROWNUM < 2;

BEGIN
  SELECT COUNT(1)
    INTO TABEXT
    FROM ALL_TABLES
   WHERE TABLE_NAME = 'productinfo_tmp';

  PC_DELESTR   := 'delete from productinfo_tmp';
  PC_CREATESTR := 'create global temporary table productinfo_tmp
  (productid varchar2(10) not null,
  productname varchar2 (20),
  productprice number(8,2),
  quantity number(10),
  category varchar2(10),
  desperaction varchar2(1000),
  origin varchar2(10))on commit preserve rows';

  IF TABEXT = 0 THEN
    --不存在临时表就创建一个
    EXECUTE IMMEDIATE PC_CREATESTR;
    DBMS_OUTPUT.PUT_LINE('创建临时表成功！');
  ELSE
    EXECUTE IMMEDIATE PC_DELESTR;
    DBMS_OUTPUT.PUT_LINE('删除记录完成！');
  END IF;
  OPEN CUR_CATEGORY;
  LOOP
    FETCH CUR_CATEGORY
      INTO CUR_CTGY;
    EXIT WHEN CUR_CATEGORY%NOTFOUND;
    OPEN CUR_PROINFO(CUR_CTGY);
    FETCH CUR_PROINFO
      INTO CUR_PRTIFO;
    IF CUR_PROINFO%FOUND THEN
      IF CUR_PRTIFO.PRODUCTPRICE < 20 THEN
        ---产品价格低于20
        DBMS_OUTPUT.PUT_LINE('产品ID' || CUR_PRTIFO.PRODUCTID || '产品名称' ||
                             CUR_PRTIFO.PRODUCTNAME || '产品价格' ||
                             CUR_PRTIFO.PRODUCTPRICE);
      ELSE
        --非低于20价格的产品输入到临时表productinfo_tmp
        EXECUTE IMMEDIATE 'insert into productinfo_tmp(
        productid,productname,productprice,quantity,category,desperaction,origin) values
        (''' || CUR_PRTIFO.PRODUCTID || ''',''' ||
                          CUR_PRTIFO.PRODUCTNAME || ''',''' ||
                          CUR_PRTIFO.PRODUCTPRICE || ''',''' ||
                          CUR_PRTIFO.QUANTITY || ''',''' ||
                          CUR_PRTIFO.CATEGORY || ''',''' ||
                          CUR_PRTIFO.DESPERACTION || ''',''' ||
                          CUR_PRTIFO.ORIGIN || ''')';
      END IF;
    END IF;
    CLOSE CUR_PROINFO;
  END LOOP;
  COMMIT;
  CLOSE CUR_CATEGORY;
  EXECUTE IMMEDIATE 'update productinfo_tmp set desperaction = ''热销产品''';
END;

第1行表示创建存储过程，名称为PRODUCT_TEMP_UPDATE_PRC 。

第2~7行表示声明变量。

第9~11行表示创建游标cur_category;

第13~19行表示创建游标CUR_PROINFO；该游标带有参数，其参数代表产品类型的编码。游标根据产品的类型不同，获取产品类型中价格最低的数据。

第22~25表示判断临时表productinfo_tmp是否存在。此处利用select into语句把结果放到变量tabext中，如果该表存在结果为1，否则为0.tabext变量将在第37行使用。

第27行表示为变量pc_delestr赋值，他的值是一条SQL语句，该SQL语句表示删除表productinfo_tmp中的数据。这种写法常常用在动态SQL语句上。

第28行表示为变量pc_createstr赋值。他的值是一条DDL语句，该语句用来创建临时表productinfo_tmp。

第37~44行完成分析步骤中的第一步：创建临时表productinfo_tmp。首先判断临时表是否存在，如果不存在，则创建，如果存在则删除表中数据。这里使用了execute immediate语句，利用它执行DDL语句及动态语句。

第45~49行表示打开游标cur_category，并进入流循环取值。当游标的%nofound属性为true时退出。

第50~53行表示打开游标cur_proinfo，它的参数是cur_category中的结果。

第54~58行表示判断价格是否低于20，如果低于20输出到屏幕。

第60~69行表示如果非低于20的插入表productinfo_tmp中。

第76行表示将productinfo_tmp表中的数据修改为热销产品。

【执行】

SQL>exec PRODUCT_TEMP_UPDATE_PRC ;