oracle hibernate 临时表 存储过程

任2519

　　

参考资料 

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

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

 

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

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

 

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

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

 

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

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

 

总结如下： 

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

 

1 创建临时表

 

Sql代码 

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

　　

2 创建存储过程 

Sql代码 

• 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 创建临时表,插入数据,返回结果集 

Sql代码 

• 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 调用存储过程 

Sql代码 

• 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;  
  

• 解决一个上周很郁闷的问题：
  项目中有一个需求，一个表 数据量很大，有几百万条吧，保守估计，呵呵。页面上填入 每组 要显示的个数，租用时间（以秒为单位），逻辑就是我先分组，然后循环 每组里根据填入的个数查询出相应的数据，这些数据要写入到xml里，然后更新。
  只用java+sql，就是那种最普通的方式，测试之后发现查询+更新需要18分钟完成100000条记录的操作，这根本不能满足需求。
  还是请教一下别人哈，得到的答案是 在Oracle里写存储过程，我测了一下，确实快了不少。呵呵，就用它了。
  先看 存储过程怎么写吧!
  思路就是先分组，然后根据分组 每组再查询出用户要求的个数，放到一个临时表里，然后更新这些数据，最后查询出临时表里的数据返回一个游标。（注：临时表的创建放在command window 里执行）
  create or replace procedure updatePro(returndataCur out IPINFO_PACKAGE.curList,curTime in number,endTime in number,dateTime in number,num in number) is
  authId number;
  --声明变量
  pro VARCHAR2(32);
  ipInfoId number;
  info_ip number;
  maskLen number;
  portStart number;
  portEnd number;
  direct number;
  rownumber number;
  str varchar2(300);
  cnt int;
  Cursor groupCur is select auth_id,property  from ip_info group by property,auth_id;
  Cursor curListCur is select id,ip,mask_len,port_start,port_end,dir,property,auth_id from (select t.*,rownum rn from (select id,ip,mask_len,port_start,port_end,dir,property,auth_id from ip_info ip_info where USABLE_TIME_START + dateTime  < =  endTime  and  USABLE_TIME_END + dateTime  > =  endTime  and NEXT_USE_TIME <  curTime and property=pro and auth_id=authId  order by id desc) t where rownum < = num ) s where rn> 0;
      
   begin
  --赋值
  authId:=0;
  pro:='m';
  ipInfoId:=0;
  info_ip:=0;
  maskLen:=0;
  portStart:=0;
  portEnd:=0;
  direct:=0;
  rownumber:=0;
  cnt:=0;
  str:='delete from RETURNDATA';
  select count(*) into cnt from all_tables where table_name='RETURNDATA';
  --if cnt!=0 then
  --execute immediate str;
  --end if;
   --此处是游标嵌套
  open groupCur; 
  loop
  fetch groupCur into authId,pro;
  exit when groupCur%notfound;
       --dbms_output.put_line(authId||pro);
      open curListCur;   
      loop
      
        fetch curListCur into ipInfoId,info_ip,maskLen,portStart,portEnd,direct,pro,authId;
        exit when curListCur%notfound;
             dbms_output.put_line(ipInfoId);
             insert into RETURNDATA values(ipInfoId,info_ip,maskLen,portStart,portEnd,direct,pro,authId);
             update ip_info set last_use_time=curTime,next_use_time=endTime where id=ipInfoId;
            
        end loop;
        commit;
        
        close curListCur;
  end loop;
  close groupCur;
  open returndataCur for select * from RETURNDATA;
  end updatePro;
   
   
  hibernate里调用存储过程
  public List findAllIpInfoList(Long curTime,Long tempNum,Long dateTime,Integer number) {
  ResultSet rs=null;
  Session ses = this.getHibernateTemplate().getSessionFactory().openSession();  
  List ipInfoList=new ArrayList();
      try{    
          Connection conn = ses.connection();        
          conn.setAutoCommit(false);    
          String proc="{Call updatepro(?,?,?,?,?)}";    
          CallableStatement st = conn.prepareCall(proc); 
          st.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);//输出参数
          st.setInt(2,Integer.valueOf(curTime.toString())); 
          st.setInt(3,Integer.valueOf(tempNum.toString())); 
          st.setInt(4,Integer.valueOf(dateTime.toString())); 
          st.setInt(5,number);
          
          st.execute();
          rs = (ResultSet) st.getObject(1);
          while(rs.next())
          { IpInfo ipInfo=new IpInfo();
           ipInfo.setIpinfoIpString(PublicClass.getJustIp(rs.getString(2)));
           ipInfo.setMaskLin(rs.getInt(3));
           ipInfo.setPortStart(rs.getInt(4));
           ipInfo.setPortEnd(rs.getInt(5));
           ipInfo.setDir(rs.getInt(6));
           ipInfo.setProperty(rs.getString(7));
           ipInfo.setAuthId(rs.getInt(8));
             
           ipInfoList.add(ipInfo);
           //System.out.println("<tr><td>" +rs.getLong(2)+"</td><td>"+ rs.getInt(3)+rs.getInt(4)+rs.getInt(5)+rs.getInt(6)+rs.getString(7)+rs.getInt(8)+"</td><td>");
          } 
          
          conn.commit();
          st.close();
          conn.close();
          ses.close();
         
      }catch(Exception e){    
          e.printStackTrace();    
      }
  return ipInfoList;      
  }