存储过程示例:在存储过程中使用临时表
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表中的数据修改为热销产品。
【执行】
SQL>exec PRODUCT_TEMP_UPDATE_PRC ;
对于一件事情,往往超过我们的想象,自信、坚持,勇往直前!
参与者:Rainbow Gu
地址:南都
***********************************************posted on 2015-02-26 13:37 RainbowGu 阅读(15160) 评论(0) 编辑 收藏 举报