17,082
社区成员
发帖
与我相关
我的任务
分享
create table t_test (id number(9) not null, code varchar2(255));
declare
begin
for c in (select t.* from t_test t) loop
dbms_output.put_line(c.id);
end loop;
end;
/
declare
v_colname varchar2(50);
begin
v_colname := 'id';
for c in (select t.* from t_test t ) loop
dbms_output.put_line(c.v_colname);
end loop;
end;
/
SQL> desc test2
Name Type Nullable Default Comments
---- ------------ -------- ------- --------
COL1 NUMBER Y
ID VARCHAR2(10) Y
SQL> select col1,id from test2;
COL1 ID
---------- ----------
1 a
2 b
3 c
SQL> exec pt('col1');
1
2
3
PL/SQL procedure successfully completed
SQL> exec pt('id');
a
b
c
PL/SQL procedure successfully completed
SQL> exec pt('name');
null
PL/SQL procedure successfully completed
SQL>
create or replace procedure pt(arg_colname in varchar2) is
cnt number;
vsql varchar2(1000);
res varchar2(100);
TYPE ref_cursor IS REF CURSOR;
c ref_cursor;
begin
select count(*)
into cnt
from user_tab_cols tc
where upper(tc.COLUMN_NAME) = upper(arg_colname)
and table_name = 'TEST2';
if cnt <= 0 then
dbms_output.put_line('null');
return;
end if;
vsql := 'select ' || arg_colname || ' from test2';
open c for vsql;
fetch c
into res;
while c %FOUND LOOP
dbms_output.put_line(res);
fetch c
into res;
end loop;
close c;
end pt;