没用集合,用游标了! Create or replace Procedure A_FOR_TEST (tab_name VARCHAR2 ) IS v_str VARCHAR2(1000); TYPE type_name IS REF CURSOR; v_cur type_name; v_colname VARCHAR2(50); BEGIN v_str:='SELECT column_name FROM user_tab_columns WHERE table_name=UPPER('''||tab_name||''')'; OPEN v_cur FOR v_str;
LOOP FETCH v_cur INTO v_colname; EXIT WHEN v_cur%NOTFOUND; DBMS_OUTPUT.put_line(v_colname); END LOOP; END A_FOR_TEST;
也可以这样: create or replace procedure print_table(p_query in varchar2) AUTHID CURRENT_USER is l_thecursor integer default dbms_sql.open_cursor; l_status integer; l_colCnt number; l_descTbl dbms_sql.desc_tab; l_colunmValue varchar2(4000); begin dbms_sql.parse(l_thecursor,p_query,dbms_sql.native); dbms_sql.describe_columns(l_thecursor,l_colCnt,l_descTbl);
for i in 1 .. l_colCnt loop dbms_output.put_line (l_descTbl(i).col_name); end loop;
Create or replace Procedure A_FOR_TEST
(tab_name VARCHAR2
) IS
v_str VARCHAR2(1000);
TYPE type_name IS REF CURSOR;
v_cur type_name;
v_colname VARCHAR2(50);
BEGIN
v_str:='SELECT column_name FROM user_tab_columns
WHERE table_name=UPPER('''||tab_name||''')';
OPEN v_cur FOR v_str;
LOOP
FETCH v_cur INTO v_colname;
EXIT WHEN v_cur%NOTFOUND;
DBMS_OUTPUT.put_line(v_colname);
END LOOP; END A_FOR_TEST;
create or replace
procedure print_table(p_query in varchar2)
AUTHID CURRENT_USER is
l_thecursor integer default dbms_sql.open_cursor;
l_status integer;
l_colCnt number;
l_descTbl dbms_sql.desc_tab;
l_colunmValue varchar2(4000);
begin
dbms_sql.parse(l_thecursor,p_query,dbms_sql.native);
dbms_sql.describe_columns(l_thecursor,l_colCnt,l_descTbl);
for i in 1 .. l_colCnt loop
dbms_output.put_line
(l_descTbl(i).col_name);
end loop;
end print_table;