我需要在存储过程里面动态生成一个sql,这个sql union了几个表,需要把结果集里面字段的值循环拼接成一整个字符串,怎么做?
这个动态sql类似:
select field1,field2,field3 from table1 where xx=xx
union
select field1,field2,field3 from table2 where yy=yy
union
select field1,field2,field3 from table3 where zz=zz在存储过程里面用这样的写法好像不能实现:
OPEN cursor_d FOR m_querysql;
LOOP
FETCH cursor_d INTO tab;
EXIT WHEN cursor_d %NOTFOUND;
/*循环拼接的代码*/
END LOOP;
CLOSE cursor_d;
这个动态sql类似:
select field1,field2,field3 from table1 where xx=xx
union
select field1,field2,field3 from table2 where yy=yy
union
select field1,field2,field3 from table3 where zz=zz在存储过程里面用这样的写法好像不能实现:
OPEN cursor_d FOR m_querysql;
LOOP
FETCH cursor_d INTO tab;
EXIT WHEN cursor_d %NOTFOUND;
/*循环拼接的代码*/
END LOOP;
CLOSE cursor_d;
解决方案 »
- oracle 启动不起来,急急急!
- ORACLE VM SERVER mount OPENFILER iscsi 出現錯誤
- 表无法创建到自已的用户空间下,请教
- oracle function 中的空值判断问题
- oracle9i安装后还需要设环境变量吗
- sql语句
- 同一条SQL在9i和10g中的不同结果问题
- 求助在windows 2003 Web Edition 操作系统安装Oracle的问题
- 如何查找数据库中前十条记录?select top 10 * from table ?
- 误删了注册表中的managementserver这个service,用configuration assistant再也创建不了了,请问改怎么办
- 请教大家一个问题
- oracle 9i 不支持子查询中带order by 吗?
cur_x sys_refcursor;
v_sql VARCHAR2(32767);
v_deptno dept.deptno%type;
begin
v_sql := 'SELECT deptno FROM dept UNION ALL SELECT deptno from EMP';
open cur_x for v_sql;
loop
FETCH cur_x INTO v_deptno;
EXIT WHEN cur_x%notfound;
dbms_output.put_line(v_deptno);
end loop;
close cur_x;
end;
11 admin [email protected]
12 zhang [email protected]
13 xiu [email protected]
然后拼接完了就是
"11/admin/[email protected] 12/zhang/[email protected] 13/xiu/[email protected]"
SELECT REPLACE(WMSYS.WM_CONCAT(FIELDS),',',' ')
FROM (
SELECT FIELD1||'/'||FIELD2||'/'||FIELD3 FIELDS FROM TABLE1 WHERE XX=XX
UNION
SELECT FIELD1||'/'||FIELD2||'/'||FIELD3 FIELDS FROM TABLE2 WHERE YY=YY
UNION
SELECT FIELD1||'/'||FIELD2||'/'||FIELD3 FIELDS FROM TABLE3 WHERE ZZ=ZZ
)TT;