Oracle存储过程中使用临时表的替代方案

      近日接手一个分析类老项目改造工作，用户要求使用Oracle数据库（原先版本为SQL Server2005)，由于原项目中大量使用存储过程实现复杂的业务数据查询，在SQL Server中由于使用表值函数、临时表等非常方便，所以当时实现起来并不费事，而现在转为Oracle就不一样了，限制太多（说实话：甲骨文的东西实在没有微软的用起来舒服）。

比如存储过程吧，直接建临时表，再往里面塞加工后数据，Oracle里不允许，非要用动态查询语句，如：

strSql  := 'Create Global temporary Table Tmp_Test(...)';

Execute Immediate strSql;

strSql  := 'Insert into Tmp_Test ....';

Execute Immediate strSql;

很麻烦，在拼接动态语句时很容易出错，往往运行后才知道有没有问题。

查阅了很多资料也没有使用临时表的好办法。

 

后来想到用集合变量来代替临时表，并使用游标返回数据。具体方法如下：

1、先建对象类型

     Create Type TestRecord Is Object(Id int,Name Varchar2(20));

 

2、再建一个集合类型

     Create Type TestTable  Is Table Of TestRecord;

 

3、存储过程中使用方法如下：

Create Procedure P_Test(p_type int,retcur out Sys_refcursor)
Is
rec1    TestRecord;
rec2    TestRecord;
datatab TestTable; 
Begin
    --实例化一条记录
    rec1:=TestRecord(1,'test01');
    --用此方法扩展表集合
    datatab.Extend;
    --往集合中插入数据
    datatab(1):=rec;
    --实例化另一条记录再插入
    rec2:=TestRecord(2,'test02');
    datatab.Extend;
    datatab(2):=rec;
    --可以使用以上方法往表集合动态插入数据行
 
    --下面是批量插入数据的方法
    --假设存在表  T_Test 并包含sId,sName和stype字段
     Select TestRecord(sId,sName)   --注意这里的方式
     Bulk collect into datatab
     From T_Test
     Where sType=p_type;
 
     --打开游标返回数据
     Open retcur For select * from table(datatab);
End;

 

4、说明：上面两个类型必须在表空间中先建好，Oracle中不允许使用游标方式打开本地集合，使用table(datatab)方式可以在存储过程中象访问普通表一样访问表集合类型实例里的数据。