无意中看到的一个题目,存储过程计算出字符串‘ABCD’的全部排列组合(比如:ABCD、ACBD、ABDC....)贴在这里大家讨论下,看都有那些解法?
解决方案 »
- oracle 字符串的分割
- 存储过程里根据传过来的参数 如何操作插入CLOB字段?
- 为什么建分区表时,提示说:为启用partitioning 特性???
- 求教一SQL语句
- 怎样找出含有错误数字的记录!!??
- 请问一下,安装oracle的ole db 驱动程序有什么要求,
- sql server2000 和oracle 9i数据传输:在线等待
- 各位,如何在PB中引用ORACLE的序列
- oracle入门求助
- MSSQL远程连接ORCALE报错,ORCALE服务器装MSSQL连接正常。
- SQL循环赋值问题求助
- 急:ORA-01653: unable to extend table DEV1.ATOMIC_TRANSFER_LOG by 1024 in
oracle 关注中
2 select a.name||b.name||c.name||d.name from tab a,tab b,tab c,tab d
3 where a.name<>b.name and a.name<>c.name and a.name<>d.name
4 and b.name<>c.name and b.name<>d.name
5 and c.name<>d.name;
A.NAME||B.NAME||C.NAME||D.NAME
------------------------------
ABCD
ABDC
ACBD
ACDB
ADBC
ADCB
BACD
BADC
BCAD
BCDA
BDAC
BDCA
CABD
CADB
CBAD
CBDA
CDAB
CDBA
DABC
DACB
A.NAME||B.NAME||C.NAME||D.NAME
------------------------------
DBAC
DBCA
DCAB
DCBA
24 rows selected
SQL>
是的,在procedure中可以使用execute immediate执行动态sql;SQL> with tab as(select substr('ABCD',rownum,1) value from dual connect by rownum<=length('ABCD'))
2 select REPLACE(sys_connect_by_path(value, '#'), '#') combo
3 from tab
4 where level = 4
5 connect by nocycle prior value != value
6 and level <= 4;
COMBO
--------------------------------------------------------------------------------
ABCD
ABDC
ACBD
ACDB
ADBC
ADCB
BACD
BADC
BCAD
BCDA
BDAC
BDCA
CABD
CADB
CBAD
CBDA
CDAB
CDBA
DABC
DACB
COMBO
--------------------------------------------------------------------------------
DBAC
DBCA
DCAB
DCBA
24 rows selected
create or replace procedure prc_permutation_with(i_string varchar2) as
/***
i_string :'ABCD'
***/
v_sql_permutation varchar2(2000);
begin
declare
type type_cur_result is ref cursor;
cur_result type_cur_result;
v_element varchar2(4);
begin
dbms_output.put_line('The Permutation is:');
v_sql_permutation := 'with tab as(select substr(' || '''' || i_string || '''' ||
',rownum,1) value from dual connect by rownum<=length(' || '''' ||
i_string || '''))
select REPLACE(sys_connect_by_path(value, ''#''), ''#'') combo
from tab
where level = 4
connect by nocycle prior value != value
and level <= 4';
open cur_result for v_sql_permutation;
LOOP
FETCH cur_result
INTO v_element;
EXIT WHEN cur_result%NOTFOUND;
dbms_output.put_line(v_element);
END LOOP;
CLOSE cur_result;
exception
when others then
raise_application_error(-20005,
'ERROR:' || to_char(SQLCODE) || '-' ||
SQLERRM);
end;
end prc_permutation_with;