将带函数的sql语句转换为Oracle存储过程
将带函数的sql语句转换为Oracle存储过程
提问于 2012-08-14 21:29:52
科尔斯，你好。有人能告诉我如何将下一个“非常规”select转换成存储过程吗？
SELECT * FROM TABLE
(parallel_dump –- this is a pipelined function which helps to create a huge file using UTL
(
CURSOR(
SELECT /*+ PARALLEL(s,4)*/
to_clob(B1)||to_clob(B2)||to_clob(B3)||to_clob(B4) AS cvs
FROM
(select
(A1||…||A200) as B1,
(A201||…||A400) as B2,
(A401||…||A600) as B3,
(A601||…||A839) as B4 from
(
Select blabla from Dual
Union all
select * from anytable
union all
select blablaba from DUAL
union all
select blabla from DUAL
)
) s),
'filename',
'DIRECTORY_NAME'
)
) nt;我不明白“into”的用法和用法。我尝试了一些变体，但是proc不能编译。
回答 1
Stack Overflow用户
回答已采纳
发布于 2012-08-14 22:24:31
create or replace procedure my_proc
IS
begin
for rec in (
SELECT *
FROM TABLE (parallel_dump –- this IS a pipelined FUNCTION which helps TO CREATE a huge file USING UTL ( CURSOR
(SELECT
/*+ PARALLEL(s,4)*/
to_clob(B1)
||to_clob(B2)
||to_clob(B3)
||to_clob(B4) AS cvs
FROM
(SELECT (A1
||…
||A200) AS B1,
(A201
||…
||A400) AS B2,
(A401
||…
||A600) AS B3,
(A601
||…
||A839) AS B4
FROM
( SELECT blabla FROM Dual
UNION ALL
SELECT * FROM anytable
UNION ALL
SELECT blablaba FROM DUAL
UNION ALL
SELECT blabla FROM DUAL
)
) s
), 'filename', 'DIRECTORY_NAME' ) ) nt
)
LOOP
-- do whatever you want with the data for example:
dbms_output.put_line('value of col1 ' || rec.col1);
END LOOP;
END;
/页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/11953361
复制相关文章
相似问题
腾讯云开发者
