sqlplus里面 16:30:21 SQL> select * from tb order by decode(&a,1,col1,2,col2,colnew) desc; 输入 a 的值: 1 原值 1: select * from tb order by decode(&a,1,col1,2,col2,colnew) desc 新值 1: select * from tb order by decode(1,1,col1,2,col2,colnew) descCOL1 COL2 COLNEW ---------- ------------------------------ -------------------- 8 bbb ccc 5 bbb bbb 4 bba bba 3 aaa aaa 2 aaa aaa 1 aaa aaa已选择6行。已用时间: 00: 00: 00.62 16:30:50 SQL> / 输入 a 的值: 2 原值 1: select * from tb order by decode(&a,1,col1,2,col2,colnew) desc 新值 1: select * from tb order by decode(2,1,col1,2,col2,colnew) descCOL1 COL2 COLNEW ---------- ------------------------------ -------------------- 5 bbb bbb 8 bbb ccc 4 bba bba 1 aaa aaa 2 aaa aaa 3 aaa aaa已选择6行。已用时间: 00: 00: 00.46 16:30:55 SQL>
存储过程里面也可以使用类似的方法。 用变量代替&a即可。
我是存储过程里的游标的ORDER BY 条件,怎么使用动态SQL语句?
用带参数的游标即可 如: cursor cur_name(col varchar) is select * from tbname order by decode(col,'1',col1,'2',col2,col3) desc; ... open cur_name('2'); fetch ...into ...;
不用decode,直接用变量为什么不好用?
直接用变量应该是不行的。 执行后相当于select * from tb order by 'col1'; 没有效果的。 16:51:44 SQL> select * from tb order by 'col1' desc;COL1 COL2 COLNEW ---------- ------------------------------ ------------- 1 aaa aaa 2 aaa aaa 3 aaa aaa 4 bba bba 5 bbb bbb 8 bbb ccc已选择6行。已用时间: 00: 00: 00.63 16:51:49 SQL>
刚才试了一下,SQL PLUS直接用变量也可以的:[email protected]> r 1 select t$pdno,t$mitm 2 from ttisfc001300 3 where rownum < 5 4* order by &a Enter value for a: t$pdno old 4: order by &a new 4: order by t$pdnoT$PDNO T$MITM --------- ----------------------------------------------- PRO000002 WG0162AA PRO000003 WA0162AA PRO000004 WG0162AA PRO000005 WA0180AG
16:30:21 SQL> select * from tb order by decode(&a,1,col1,2,col2,colnew) desc;
输入 a 的值: 1
原值 1: select * from tb order by decode(&a,1,col1,2,col2,colnew) desc
新值 1: select * from tb order by decode(1,1,col1,2,col2,colnew) descCOL1 COL2 COLNEW
---------- ------------------------------ --------------------
8 bbb ccc
5 bbb bbb
4 bba bba
3 aaa aaa
2 aaa aaa
1 aaa aaa已选择6行。已用时间: 00: 00: 00.62
16:30:50 SQL> /
输入 a 的值: 2
原值 1: select * from tb order by decode(&a,1,col1,2,col2,colnew) desc
新值 1: select * from tb order by decode(2,1,col1,2,col2,colnew) descCOL1 COL2 COLNEW
---------- ------------------------------ --------------------
5 bbb bbb
8 bbb ccc
4 bba bba
1 aaa aaa
2 aaa aaa
3 aaa aaa已选择6行。已用时间: 00: 00: 00.46
16:30:55 SQL>
用变量代替&a即可。
如:
cursor cur_name(col varchar) is
select * from tbname order by decode(col,'1',col1,'2',col2,col3) desc;
...
open cur_name('2');
fetch ...into ...;
执行后相当于select * from tb order by 'col1';
没有效果的。
16:51:44 SQL> select * from tb order by 'col1' desc;COL1 COL2 COLNEW
---------- ------------------------------ -------------
1 aaa aaa
2 aaa aaa
3 aaa aaa
4 bba bba
5 bbb bbb
8 bbb ccc已选择6行。已用时间: 00: 00: 00.63
16:51:49 SQL>
1 select t$pdno,t$mitm
2 from ttisfc001300
3 where rownum < 5
4* order by &a
Enter value for a: t$pdno
old 4: order by &a
new 4: order by t$pdnoT$PDNO T$MITM
--------- -----------------------------------------------
PRO000002 WG0162AA
PRO000003 WA0162AA
PRO000004 WG0162AA
PRO000005 WA0180AG
1.要求wkPTORDER_DB='S'时按SLNNO排列
2.要求wkPTORDER_DB='B'时按BLKCD,SLNNO这两列进行排列
3.要求wkPTORDER_DB='T'时按SLNINSDT排列
4.否则按SLNNO排列
第二种条件有两个自段怎么实现??
急.....................