oracle之存储过程,临时表,游标示例

参考资料

1ORACLE存储过程返回临时表结果集

http://hi.baidu.com/h_sn999/blog/item/4211810f4d7542fdaa645738.html

2ORACLE在存储过程中使用临时表

http://blog.csdn.net/wekily/article/details/6120900

3Oracle存储过程中创建临时表<原创>

http://blog.sina.com.cn/s/blog_4c7ae2a80100bki3.html

4在ORACLE存储过程中创建临时表

http://huqiji.iteye.com/blog/782067

总结如下：

DDL是一种消耗资源非常大的操作，运行时尽量不要使用DDL语句，应用程序需要的临时表应在运行之前就开始创建。不必在每个存储过程中创建一次。临时表总是存在的，他们作为对象存在于数据字典中，并且总是保持为空，直到有会话在其中放入数据

1创建临时表

create global temporary table 表名
(
  ID               VARCHAR2(100 CHAR),
  NAME         VARCHAR2(100 CHAR) 
)
on commit preserve rows;

2创建存储过程

create or replace procedure proc_XXX(
mycur out SYS_REFCURSOR
as
TYPE My_CurType IS REF CURSOR;
CUR_1 My_CurType;
tempa varchar2;
tempb varchar2;
--此处可声明更多变更^_^
begin
 
    OPEN CUR_1 FOR  select * from 表名;
  
    --使用前先清空
    execute immediate 'truncate table  临时表表名';

  LOOP
  FETCH CUR_1 INTO  tempa;
  EXIT WHEN CUR_1%NOTFOUND;
    
      --进行相关的业务查询,将结果返回于更多变量上,插入临时表数据      
     tempa:='1';
     tempb:='jack';
     insert into 临时表表名(ID,NAME)values(tempa,tempb); 
     commit;  
end loop;    
  open mycur for  select * from  临时表表名;      
  CLOSE  CUR_1;
  message :='查询临时表成功';
  EXCEPTION
  WHEN OTHERS THEN
   message :='查询临时表失败';
end  proc_XXX;

参考更多

1创建临时表,插入数据,返回结果集

CREATE OR REPLACE PROCEDURE Report_Month_Responsibility(
 o_cur OUT SYS_REFCURSOR
)
IS
STR VARCHAR2(200);
tb_count INT;
BEGIN
  --先判断全局临时表是否存在，没存在则重新建立：
  select count(*) into tb_count from dba_tables where table_name='REPROTTEST';
  if tb_count=0 then
    STR:=' CREATE GLOBAL TEMPORARY TABLE REPROTTEST(
           ID INT,
           ANAME VARCHAR2(20)
    ) ON COMMIT PRESERVE ROWS';
     execute immediate STR;
  end if;
  
  STR:='INSERT INTO REPROTTEST(ID,ANAME)  VALUES(1,''1'')';
  execute immediate STR;
  COMMIT;
  STR:='SELECT * FROM REPROTTEST';
  OPEN o_cur FOR STR; -- 给游标变量赋值
END Report_Month_Responsibility;

2调用存储过程

CREATE OR REPLACE PROCEDURE proc_X()
IS
  v_ID INT;
  v_ANAME VARCHAR2(20);      
   --定义游标:
   v_account_cur SYS_REFCURSOR;
BEGIN
     --调用存储过程：
       Report_Month_Responsibility(v_account_cur);       
       fetch v_account_cur into v_ID,v_ANAME;
      --用循环显示游标中的记录：
       while v_account_cur%found loop
             dbms_output.put_line('The value of column ID is: '||v_ID);--打引列ID
             dbms_output.put_line('The value of column ANAME is: '||v_ANAME);  
             --打引列ANAME          
             fetch v_account_cur into v_ID,v_ANAME;
       end loop;
       close v_account_cur;    
       execute immediate 'truncate TABLE REPROTTEST';   
end proc_X;