SQL在存储过程中使用临时表

1 CREATE OR REPLACE PROCEDURE PRODUCT_TEMP_UPDATE_PRC AS
2 PC_DELESTR VARCHAR2(50); --删除临时表记录语句
3 PC_CREATESTR VARCHAR2(500); --创建临时表
4 TABEXT VARCHAR2(10); --用于判断临时表是否存在中间变量
5
6 CUR_CTGY PRODUCTINFO.CATEGORY%TYPE;
7 CUR_PRTIFO PRODUCTINFO%ROWTYPE;
8
9 CURSOR CUR_CATEGORY --产品表中的产品类型游标
10 IS
11 SELECT CATEGORY FROM PRODUCTINFO GROUP BY CATEGORY;
12
13 CURSOR CUR_PROINFO(CTGY VARCHAR) IS
14 SELECT *
15 FROM (SELECT *
16 FROM PRODUCTINFO
17 WHERE CATEGORY = CTGY
18 ORDER BY PRODUCTPRICE ASC)
19 WHERE ROWNUM < 2;
20
21 BEGIN
22 SELECT COUNT(1)
23 INTO TABEXT
24 FROM ALL_TABLES
25 WHERE TABLE_NAME = ‘productinfo_tmp’;
26
27 PC_DELESTR := ‘delete from productinfo_tmp’;
28 PC_CREATESTR := ‘create global temporary table productinfo_tmp
29 (productid varchar2(10) not null,
30 productname varchar2 (20)