Oracle proc 输出问题求助
我想把一段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 '')
as

Stmt_1 varchar2(2000);
Stmt_2 varchar2(2000);
Stmt_3 varchar2(2000);
Stmt_4 varchar2(2000);

begin

Stmt_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;
/
[解决办法]
可以用下面的输出语句显示你组成的SQL是否有问题：
dbms_output.put_line('Stmt_2 ='
[解决办法]
Stmt_2);
dbms_output.put_line('Stmt_3 ='
[解决办法]
Stmt_3);
[解决办法]
第一个问题
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语句相结合。。

如果你要返回，得用游标。。



[解决办法]
通过游标返回结果集，你执行完了之后就可以直接查看结果集中的内容，加入红色部分就可以了
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)
as

Stmt_1 varchar2(2000);
Stmt_2 varchar2(2000);
Stmt_3 varchar2(2000);
Stmt_4 varchar2(2000);

begin

Stmt_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;
/