在存储过程中使用临时表

• 要求把各种不同类型的产品中价格最低的输入到临时表productinfo——tmp中，需要创建。并在其desperation字段注明‘热销商品’,如果价格低于20则表示数据有问题，需要输出到屏幕。该存储过程需要使用游标以及execute immediate语句处理相关问题。

create or replace procedure product_temp_update_prc is
pc_delestr varchar2(50);--删除临时表记录语句
pc_createstr varchar2(1000);--创建临时表
pc_insrtstr 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 CLOBAL TEMPORARY TABLE PRODUCTINFO_TMP(
                                          productid varchar2(10) not null,
                                          productname varchar2(20),
                                          productprice number(8,2),
                                          quantity number(10),
                                          category varchar2(10),
                                          desperation 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,
                                             desperation,
                                             origin)
                                        values('''||cur_prtifo.productid||''',
                                             '''||cur_prtifo.productname||''',
                                             '''||cur_prtifo.productprice||''',
                                             '''||cur_prtifo.quantity||''',
                                             '''||cur_prtifo.category||''',
                                             '''||cur_prtifo.desperation||''',
                                             '''||cur_prtifo.origin||''')';
                 end if;
               end if;
          close cur_proinfo;
      end loop;
      commit;
      close cur_category;
      execute immediate 'update productinfo_tmp set desperation = ''热销产品''';
end product_temp_update_prc;

begin --执行
  product_temp_update_prc();
end;


select * from PRODUCTINFO_TMP --查看结果