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),
31 productprice number(8,2),
32 quantity number(10),
33 category varchar2(10),
34 desperaction varchar2(1000),
35 origin varchar2(10))on commit preserve rows’;
36
37 IF TABEXT = 0 THEN
38 --不存在临时表就创建一个
39 EXECUTE IMMEDIATE PC_CREATESTR;
40 DBMS_OUTPUT.PUT_LINE(‘创建临时表成功！’);
41 ELSE
42 EXECUTE IMMEDIATE PC_DELESTR;
43 DBMS_OUTPUT.PUT_LINE(‘删除记录完成！’);
44 END IF;
45 OPEN CUR_CATEGORY;
46 LOOP
47 FETCH CUR_CATEGORY
48 INTO CUR_CTGY;
49 EXIT WHEN CUR_CATEGORY%NOTFOUND;
50 OPEN CUR_PROINFO(CUR_CTGY);
51 FETCH CUR_PROINFO
52 INTO CUR_PRTIFO;
53 IF CUR_PROINFO%FOUND THEN
54 IF CUR_PRTIFO.PRODUCTPRICE < 20 THEN
55 —产品价格低于20
56 DBMS_OUTPUT.PUT_LINE(‘产品ID’ || CUR_PRTIFO.PRODUCTID || ‘产品名称’ ||
57 CUR_PRTIFO.PRODUCTNAME || ‘产品价格’ ||
58 CUR_PRTIFO.PRODUCTPRICE);
59 ELSE
60 --非低于20价格的产品输入到临时表productinfo_tmp
61 EXECUTE IMMEDIATE ‘insert into productinfo_tmp(
62 productid,productname,productprice,quantity,category,desperaction,origin) values
63 (’’’ || CUR_PRTIFO.PRODUCTID || ‘’’,’’’ ||
64 CUR_PRTIFO.PRODUCTNAME || ‘’’,’’’ ||
65 CUR_PRTIFO.PRODUCTPRICE || ‘’’,’’’ ||
66 CUR_PRTIFO.QUANTITY || ‘’’,’’’ ||
67 CUR_PRTIFO.CATEGORY || ‘’’,’’’ ||
68 CUR_PRTIFO.DESPERACTION || ‘’’,’’’ ||
69 CUR_PRTIFO.ORIGIN || ‘’’)’;
70 END IF;
71 END IF;
72 CLOSE CUR_PROINFO;
73 END LOOP;
74 COMMIT;
75 CLOSE CUR_CATEGORY;
76 EXECUTE IMMEDIATE ‘update productinfo_tmp set desperaction = ‘‘热销产品’’’;
77 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表中的数据修改为热销产品。