Oracle proc 输出问题求助

Question

我想把一段SELECT的结果通过CALL SP 输出到结果集，下面的代码好像没有作用，我是新手第一次写proc，还请大家多指教，当然这个存储过程还没有写完的，主要是去系统表里按照schema name 和 table name 和条件排除一些不需要的字段，把它拼成select col1,...coln from schema_name.table_name的效果
现在就是想通过call SP，把Stmt_3输出到结果集CREATE OR REPLACE procedure TEST.SP_ODS_1 (schema_name varchar2, table_name varchar2,exclude_column_name1 varchar2 default '',exclude_column_name2 varchar2 default '',exclude_column_name3 varchar2 default '',exclude_column_name4 varchar2 default '')
asStmt_1 varchar2(2000);
Stmt_2 varchar2(2000);
Stmt_3 varchar2(2000);
Stmt_4 varchar2(2000);beginStmt_1 := 'truncate table TEST.TEMP_TABLE_1';execute immediate Stmt_1;Stmt_2 := '
INSERT INTO TEST.TEMP_TABLE_1
SELECT  distinct COLUMN_NAME, COLUMN_ID, SYSDATE
FROM all_tab_cols  WHERE OWNER = '''||schema_name||''' AND TABLE_NAME = '''||table_name||'''
AND COLUMN_NAME NOT IN ('''||exclude_column_name1||''','''||exclude_column_name2||''','''||exclude_column_name3||''','''||exclude_column_name4||''')
';execute immediate Stmt_2;Stmt_3 := 'SELECT CASE WHEN COLUMN_ID = 1 THEN COLUMN_NAME ELSE '',''||COLUMN_NAME END AS COLUMN_NAME FROM TEST.TEMP_TABLE_1 ORDER BY COLUMN_ID';execute immediate Stmt_3;commit;end;
/

Answer 1

可以用下面的输出语句显示你组成的SQL是否有问题：
dbms_output.put_line('Stmt_2 ='|| Stmt_2);
dbms_output.put_line('Stmt_3 ='|| Stmt_3);

Answer 2

第一个问题
Stmt_1 := 'truncate table TEST.TEMP_TABLE_1';
execute immediate Stmt_1;可以直接写成truncate table TEST.TEMP_TABLE_1第二个问题
Stmt_3 := 'SELECT CASE WHEN COLUMN_ID = 1 THEN COLUMN_NAME ELSE '',''||COLUMN_NAME END AS COLUMN_NAME FROM TEST.TEMP_TABLE_1 ORDER BY COLUMN_ID';execute immediate Stmt_3;pl/SQL 代码中，单纯的select语句必须与into语句相结合。。如果你要返回，得用游标。。

Answer 3

通过游标返回结果集，你执行完了之后就可以直接查看结果集中的内容，加入红色部分就可以了
CREATE OR REPLACE procedure TEST.SP_ODS_1 (schema_name varchar2, table_name varchar2,exclude_column_name1 varchar2 default '',exclude_column_name2 varchar2 default '',exclude_column_name3 varchar2 default '',exclude_column_name4 varchar2 default '',v_cursor out sys_refcursor)
asStmt_1 varchar2(2000);
Stmt_2 varchar2(2000);
Stmt_3 varchar2(2000);
Stmt_4 varchar2(2000);beginStmt_1 := 'truncate table TEST.TEMP_TABLE_1';execute immediate Stmt_1;Stmt_2 := '
INSERT INTO TEST.TEMP_TABLE_1
SELECT  distinct COLUMN_NAME, COLUMN_ID, SYSDATE
FROM all_tab_cols  WHERE OWNER = '''||schema_name||''' AND TABLE_NAME = '''||table_name||'''
AND COLUMN_NAME NOT IN ('''||exclude_column_name1||''','''||exclude_column_name2||''','''||exclude_column_name3||''','''||exclude_column_name4||''')
';execute immediate Stmt_2;Stmt_3 := 'SELECT CASE WHEN COLUMN_ID = 1 THEN COLUMN_NAME ELSE '',''||COLUMN_NAME END AS COLUMN_NAME FROM TEST.TEMP_TABLE_1 ORDER BY COLUMN_ID';execute immediate Stmt_3;
open v_cursor for Stmt_3;commit;end;
/