使用pl sql的表类型作为返回值

在使用oracle以前一直是用sql server的。使用sql server期间让我印象最深刻的就是它的select语句和记录集灵活性很强。一个select语句的结果既可以作为记录集使用，也能作为集合使用。而select本身充当查询语句的同时，也能作为赋值和计算语句使用。这样就使得在分析器写sql语句很方便，不用非要写个存储过程，通过定义变量，然后写一串sql就能完成表间很复杂的功能。当需要重用的时候，把这串语句加上声明就能定义为存储过程，方便灵活。

到使用oracle之后，情况就有点改变。select语句的作用受了很多限制。
select @a=@a+fchar form table1;
这个句在sql server是正确的，@a得到table1表所有记录的fchar列的值连接后的结果，但它在oracle是不允许的。要得到相同的结果就只能使用游标。

另外，在sql server的函数和存储过程中，如果包含了select语句，那么它们会直接返回记录集。但是在oracle的函数和存储过程中，不允许返回记录集的select语句存在，因此要想返回记录集，就必须使用动态游标或者集合类型。

如果函数或者存储过程返回的是游标，一方面会容易出现类型安全的问题，另一方面在PL/SQL Developer的sql window上调用后，观察结果也是一件麻烦的事。所以返回集合是一个不错的选择。而pl sql的表类型又是集合中很好使用的一个。

以下是使用表类型作返回值的一个函数示例：

 1 -- 创建全局类型
 2 -- -------------创建对象类型objrow
 3 create  type objrow  as  object(fid  number ,title  varchar2 ( 400 ));
 4 -- ------------------------------------------------创建基于objrow类型的表类型
 5 create   or   replace  type ty_List  as   table   of  objrow;
 6 -- ------------------------------------------------创建试验用的表
 7 create   table  t_L (
 8 fid  number ,
 9 fT  varchar2 ( 400 )
10 );
11 -- -----------------------插入一些记录作为测试数据
12 declare  
13 v_i  number ;
14 begin  
15 v_i: = 0 ;
16
17 while (v_i < 10 )
18 loop
19 insert   into  t_L(Fid,Ft)  values  (v_i, ' TTT ' || v_i);
20 v_i: = V_i + 1 ;
21 end  loop;
22
23 end ;
24 -- ------------------------------------------------创建返回ty_List表类型的函数
25 create   or   replace   function  get_TList
26 return  ty_List
27 -- 声明ty_List表类型作为返回值
28 is
29   -- 声明ty_List表类型变量，并初始化
30  v_list ty_list: = ty_List();
31
32 begin
33
34 -- 查询试验表，并把结果放到表类型中，留意BULK COLLECT关键字
35   select  new objrow(fid,fT)  BULK  COLLECT  into  v_list  from  t_L;
36
37   return  v_list;
38 end ;
39 -- ------------------------------------------------查询试验表的记录
40 select   *   from  t_L;
41 -- --------------------------------------调用函数get_TList
42 -- --------通过table()函数把get_TList返回的表类型作为表使用
43 select   *   from   table (get_tlist());
44 -- 执行该语句，可以看淡函数的返回值是一个集合
45 select  get_tlist()  from  dual;

由代码可看到，用table()函数结合返回表类型的函数或者存储过程，可以很灵活的把计算结果作为一个记录集嵌套到select语句中，以便和真实表进行关联，或把多个计算结果进行组合。这样可以使通过select语句获取更复杂的计算结果。

如果在sql window下，使用declare执行批量sql时想得到某个select语句的结果，可以使用以下方式：

 1 declare
 2  TYPE TYPE_STATION  IS  RECORD (
 3 fid  number ,
 4 title  varchar2 ( 400 )
 5 );
 6  type v_ty_List  is   table   of  TYPE_STATION;
 7  v_list v_ty_List: = v_ty_List();
 8  v_i  number ;
 9 begin
10
11   select  fid,fT  BULK  COLLECT  into  v_list  from  t_L;
12  
13   for  v_i  in   1 ..v_list. count  loop
14  dbms_output.put_line(v_list(v_i).fid || v_list(v_i).title);
15   end  loop;
16 end ;

原理和上面函数使用的原理差不多。就不说太多了。

到现在为止，我还是没有办法搞明白，带变量作为条件的select语句，难道在PL/SQL Developer的sql window上是没有办法调用的.....
sql server上有这样的通过变量查询结果集的：
declare @i int;
set @i=1;
select * from table1 where id=@i;
这样便能通过改变变量的值来得到不同的查询结果。但是在oracle里面，
declare
v_i number;
begin
v_i:=1;
select * from table1 where id=v_i;
end;
显然，这个写法是不行的，那在oracle里面应该怎样才能调用带变量的select并返回结果呢？
其实这个才是我最想解决的问题。结果没有解决，反而为返回表类型和select * from table(**);搞了一晚。下面是一些翻阅过的文章，多谢其作者的无私奉献！嘻嘻

关于嵌套表为record类型与bulk bind的结合使用
http://flysky0814.itpub.net/post/35477/419397

初步总结oracle pl/sql 的record
http://bigboar.itpub.net/post/8411/52348

PL/SQL表---table()函数用法－－－－－－－－－－－这篇文章让我有点郁闷，它写得东西和我这里写得很大程度上是相同的，但是我绝对没有抄袭，因为我是写到一半找资料才找到它的。不过作者真的很厉害，文章让我解决了全局表变量的创建问题。原来全局表变量的声明后面是不能带index by binary_integer之类的索引声明的.....5555
http://www.itpub.net/617298,1.html

使用PL/SQL table 来构造连续数集合表 －－－－－－文章对使用表变量很有启发性
http://blog.itpub.net/post/6/30895

ORACLE 数组在过程中的应用
http://itpub120.itpub.net/post/25009/234635

Oracle使用手册(一)---声明变量
http://www.cnblogs.com/furenjun/archive/2006/07/04/442482.html

转载于:https://www.cnblogs.com/baoli/archive/2008/01/12/1036220.html