oracle 字段名 参数,请问能将字段名作为参数传递么？

zhyuh

中级会员

REF CURSOR 小结

利用REF CURSOR，可以在程序间传递结果集(一个程序里打开游标变量，在另外的程序里处理数据)。

也可以利用REF CURSOR实现BULK SQL，提高SQL性能。

REF CURSOR分两种，Strong REF CURSOR 和 Weak REF CURSOR。

create or replace procedure open_cv(choice IN INT,

return_cv OUT SYS_REFCURSOR) is

--参数return_cv为weak REF CURSOR,利用SYS_CURSOR来定义

begin

if choice = 1 then

open return_cv for 'select * from emp';

elsif choice = 2 then

open return_cv for 'select * from dept';

end if;

end open_cv;

--procedure retrieve_data------------------------------------

create or replace procedure retrieve_data(choice IN INT) is

emp_rec emp%rowtype;

dept_rec dept%rowtype;

return_cv SYS_REFCURSOR;

invalid_choice exception;

begin

if choice=1 then

dbms_output.put_line('employee information');

open_cv(1,return_cv); --调用procedure open_cv;

loop

fetch return_cv into emp_rec;

exit when return_cv%notfound;

dbms_output.put_line(emp_rec.empno||'-'||emp_rec.ename||'-'||emp_rec.sal);

end loop;

elsif choice=2 then

dbms_output.put_line('department information');

open_cv(2,return_cv);

loop

fetch return_cv into dept_rec;

exit when return_cv%notfound;

dbms_output.put_line(dept_rec.deptno||'-'||dept_rec.dname||'-'||dept_rec.loc);

end loop;

else

raise invalid_choice;

end if;

exception

when invalid_choice then

dbms_output.put_line('The CHOICE should be one of 1 and 2!');

when others then

dbms_output.put_line('Errors in procedure retrieve_data');

end retrieve_data;

-----------------------------------------------------------------

执行：

SQL> exec retrieve_data(1);

employee information

7369-SMITH-800

7499-ALLEN-1600

7521-WARD-1250

7566-JONES-2975

7654-MARTIN-1250

7698-BLAKE-2850

......

PL/SQL procedure successfully completed

SQL> exec retrieve_data(2);

department information

10-ACCOUNTING-NEW YORK

20-RESEARCH-DALLAS

30-SALES-CHICAGO

40-OPERATIONS-BOSTON

PL/SQL procedure successfully completed