使用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的表类型又是集合中很好使用的一个。
以下是使用表类型作返回值的一个函数示例：
由代码可看到，用table()函数结合返回表类型的函数或者存储过程，可以很灵活的把计算结果作为一个记录集嵌套到select语句中，以便和真实表进行关联，或把多个计算结果进行组合。这样可以使通过select语句获取更复杂的计算结果。
如果在sql window下，使用declare执行批量sql时想得到某个select语句的结果，可以使用以下方式：
原理和上面函数使用的原理差不多。就不说太多了。
到现在为止，我还是没有办法搞明白，带变量作为条件的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之后，情况就有点改变。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
3create type objrow as object(fid number,title varchar2(400));
4--------------------------------------------------创建基于objrow类型的表类型
5create or replace type ty_List as table of objrow;
6--------------------------------------------------创建试验用的表
7create table t_L (
8fid number,
9fT varchar2(400)
10);
11-------------------------插入一些记录作为测试数据
12declare
13v_i number;
14begin
15v_i:=0;
16
17while(v_i<10)
18loop
19insert into t_L(Fid,Ft) values (v_i,'TTT'||v_i);
20v_i:=V_i+1;
21end loop;
22
23end;
24--------------------------------------------------创建返回ty_List表类型的函数
25create or replace function get_TList
26return ty_List
27--声明ty_List表类型作为返回值
28is
29 --声明ty_List表类型变量，并初始化
30 v_list ty_list:=ty_List();
31
32begin
33
34--查询试验表，并把结果放到表类型中，留意BULK COLLECT关键字
35 select new objrow(fid,fT) BULK COLLECT into v_list from t_L;
36
37 return v_list;
38end;
39--------------------------------------------------查询试验表的记录
40select * from t_L;
41----------------------------------------调用函数get_TList
42----------通过table()函数把get_TList返回的表类型作为表使用
43select * from table(get_tlist());
44--执行该语句，可以看淡函数的返回值是一个集合
45select get_tlist() from dual;
--创建全局类型2
---------------创建对象类型objrow3
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
loop19
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_TList26
return ty_List27
--声明ty_List表类型作为返回值28
is29
--声明ty_List表类型变量，并初始化30
v_list ty_list:=ty_List();31
32
begin33
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_TList42
----------通过table()函数把get_TList返回的表类型作为表使用43
select * from table(get_tlist());44
--执行该语句，可以看淡函数的返回值是一个集合45
select get_tlist() from dual;由代码可看到，用table()函数结合返回表类型的函数或者存储过程，可以很灵活的把计算结果作为一个记录集嵌套到select语句中，以便和真实表进行关联，或把多个计算结果进行组合。这样可以使通过select语句获取更复杂的计算结果。
如果在sql window下，使用declare执行批量sql时想得到某个select语句的结果，可以使用以下方式：
1declare
2 TYPE TYPE_STATION IS RECORD (
3fid number,
4title 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;
9begin
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;
16end;
declare2
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
begin10
11
select fid,fT BULK COLLECT into v_list from t_L;12
13
for v_i in 1..v_list.count loop14
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
