17,086
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure print_table(table_name in varchar2)
is
cursor cur_columns(tn varchar2) is
select column_name,data_type from user_tab_columns
where upper(table_name) = upper(tn)
order by column_id;
statement varchar2(2000);
print_line varchar2(2000);
begin
statement := 'declare ' || chr(10);
statement := statement || ' cursor cur_main is ' || chr(10);
statement := statement || ' select ' || chr(10);
print_line := ' dbms_output.put_line(';
for col in cur_columns(table_name) loop
if col.data_type = 'DATE' then
statement := statement || 'to_char(' || col.column_name || ', ''yyyymmdd hh24:mi:ss'') AS ' || col.column_name;
else
statement := statement || col.column_name;
end if;
statement := statement || ',';
print_line := print_line || 'r.' || col.column_name || ' || '','' || ';
end loop;
statement := substr(statement, 1, length(statement) - 1) || chr(10);
statement := statement || ' from ' || table_name || ';' || chr(10);
print_line := substr(print_line, 1, length(print_line) - 11);
print_line := print_line || ');' || chr(10);
statement := statement || 'begin ' || chr(10);
statement := statement || 'for r in cur_main loop ' || chr(10);
statement := statement || print_line;
statement := statement || 'end loop;' || chr(10);
statement := statement || 'end;';
dbms_output.put_line(statement);
execute immediate statement;
end;
-- 測試代碼
begin
print_table('employees');
end;
-- 輸出結果
198,Donald,OConnell,DOCONNEL,650.507.9833,19990621 00:00:00,SH_CLERK,2600,,124,50
199,Douglas,Grant,DGRANT,650.507.9844,20000113 00:00:00,SH_CLERK,2600,,124,50
...
...