创建嵌套表rec_tab. 变量v_result是嵌套表类型。 存储过程:
for rec1 in(select 字段1,字段2.....) loop
将第1条记录插入嵌套表;
将第2条记录插入嵌套表;
将第N条记录插入嵌套表;
end loop; for rec2 in(select 字段1,字段2.....) loop
将第N+1条记录插入嵌套表;
将第N+2条记录插入嵌套表;
......
end loop; 即将两个循环内的数据都插入到这个嵌套表中,第一个循环插完了接着插第二个循环的数据。 返回结果集:open cur for select 字段1,字段2 from table(v_result); 问题:现在前台有4个输入项,每个输入项后有供用户选择的排序方式,用户可按这4个参数任意排序,所以这4个排序方式要作为变量传到存储过程中,但是,我试了一下,嵌套表里似乎不能连接变量,我现在要对嵌套表里的记录按客户选择进行排序怎么办呢?
也就是像这样的:open cur for select 字段1,字段2... from table(v_result) order by 字段1 || 升序,字段2||降序,字段3||升序,字段4||降序;(升序或降序是个变量)
for rec1 in(select 字段1,字段2.....) loop
将第1条记录插入嵌套表;
将第2条记录插入嵌套表;
将第N条记录插入嵌套表;
end loop; for rec2 in(select 字段1,字段2.....) loop
将第N+1条记录插入嵌套表;
将第N+2条记录插入嵌套表;
......
end loop; 即将两个循环内的数据都插入到这个嵌套表中,第一个循环插完了接着插第二个循环的数据。 返回结果集:open cur for select 字段1,字段2 from table(v_result); 问题:现在前台有4个输入项,每个输入项后有供用户选择的排序方式,用户可按这4个参数任意排序,所以这4个排序方式要作为变量传到存储过程中,但是,我试了一下,嵌套表里似乎不能连接变量,我现在要对嵌套表里的记录按客户选择进行排序怎么办呢?
也就是像这样的:open cur for select 字段1,字段2... from table(v_result) order by 字段1 || 升序,字段2||降序,字段3||升序,字段4||降序;(升序或降序是个变量)
解决方案 »
- 如何让别人的sqlplus 可以访问我电脑上的数据库
- ORACLE触发器简单问题,新手在线求解
- 关于oracle数据库钟SYS用户的一些问题
- 返回类型为ref cursor的自定义函数,如何调用这个函数
- 关于数据库如何选择
- How Oracle Allocating Space in Tablespace
- 我装了oracle8.1.7personal的,现在想再装一个client的,怕冲突.
- 哪位有LECCO Sql Expert for Oracle的注册码?
- developer2000与oracle8.1.7安装问题
- 关于切换undo表空间有个疑问
- 用SELECT做from后面的表达式的问题
- Oracle, 多个字段中有回车,换行字符,不想逐字段去REPLACE,不知道有没有在会话层级的设置,可以实现将此类字符忽略?
姓 名:_____◎升序 ◎降序
性 别:_____◎升序 ◎降序
年 龄:_____◎升序 ◎降序
出生年月:_____◎升序 ◎降序用户的对上面4个字段的排序要求可能是:姓名-升序,性别-降序,年龄-升序,出生年月-降序
所以这4个排序的值要作为变量传到存储过程中。虽然动态SQL可以解决这个问题,但嵌套表不能用在动态SQL语句中。查询嵌套表的语句中也不能连接变量。我要的语句效果是这样的:
open cur for select 字段1,字段2 from table(v_result) order by 姓名 “排序1”, 性别 “排序2”, 年龄 “排序3”, 出生年月 “排序4”;
order by 后面可以用绑定变量,但是只相当于用常量排序,绑定的值不能真正反映排序结果
order by中使用绑定变量没有意义,那是基于常量的排序,而不是基于列的排序看样子嵌套表里变量排序是行不通了。。
好象在几年前的Oracle和Sqlserver相比较的百人口水大战(A行,B不行)中即提及此条,呵呵。
create view v_test1 as select ...视图也可以和其他表进行关联使用,如:
select * from tableA,v_test1 where ...
v_order2 varchar2(4),
v_order3 varchar2(4),
v_order4 varchar2(4),
cur out sys_refcursor)
as
v_order1 varchar2(4);begin
.....
..... v_sql := 'select 字段1,字段2... from table(v_result) order by 字段1 ' ||
v_order1 ',字段2 ' || v_order2 ',字段3 ' || v_order3
',字段4 ' || v_order4; open cur for v_sql;
end;
create or replace procedure test_pro(v_order1 varchar2(4), -- order by传参
v_order2 varchar2(4),
v_order3 varchar2(4),
v_order4 varchar2(4),
cur out sys_refcursor)
as
v_sql varchar2(4);begin
.....
..... v_sql := 'select 字段1,字段2... from table(v_result) order by 字段1 ' ||
v_order1 ',字段2 ' || v_order2 ',字段3 ' || v_order3
',字段4 ' || v_order4; open cur for v_sql;
end;
然后就是拼SQL了,
我上面拼的SQL还少了几个||,如果这方式可能你修改一下.
这种可采用open cursor for '字符串的形式',很管用,只要你拼成的SQL能正确执行即可。
干脆写个完整的example:SQL> select name, birthdate
2 from table (select animals
3 from breeder
4 where breedername = 'mary')
5 where breed = 'dog'
6 /NAME BIRTHDATE
------------------------- -----------
butch 2008-12-12
rover 2008-11-12
julio 2008-5-12-- 不加desc,上面嵌套表查出的结果是升序的.SQL> create or replace procedure pro_t(v_order varchar2,cur out sys_refcursor)
2 as
3 v_sql varchar2(400);
4 begin
5 v_sql :='select name,birthdate from table(select animals from breeder
6 where breedername=''mary'') where breed=''dog''
7 order by name '||v_order;
8 open cur for v_sql;
9 end;
10 /Procedure createdSQL> var cur refcursor
SQL> begin
2 zyf.pro_t('desc',:cur);
3 end;
4 /PL/SQL 过程已成功完成。SQL> print :cur ; -- 看下面结果,是倒序的了NAME BIRTHDATE
------------------------- --------------
rover 12-11月-08
julio 12-5月 -08
butch 12-12月-08
假设嵌套表名为tblResult,
open cursor for 'select * from table(tblResult)',像这个SQL,如果把查询嵌套表的语句当作字符串,程序会报找不到表的错误。
create or replace type test_typ as object
(
id number,
val varchar2(20)
)
/
create or replace type test_tbl_typ as table of test_typ
/declare
l_tbl test_tbl_typ := test_tbl_typ();
begin
l_tbl.extend(2);
l_tbl(1) := test_typ(10, 'ten');
l_tbl(2) := test_typ(20, 'two'); for rec in (select id, val from table(cast(l_tbl as test_tbl_typ))) loop
dbms_output.put_line(rec.id || ' -> ' || rec.val);
end loop;
end;
/