17,086
社区成员
发帖
与我相关
我的任务
分享
--建立T_TEST表
CREATE TABLE T_TEST(COL1 VARCHAR2(10));
INSERT INTO T_TEST VALUES('NAME');
INSERT INTO T_TEST VALUES('ID');
--建立T_STUDENT表
CREATE TABLE T_STUDENT(ID NUMBER,NAME VARCHAR2(10));
INSERT INTO T_STUDENT VALUES(1,'YF');
INSERT INTO T_STUDENT VALUES (2,'GN');
--建立函数
CREATE OR REPLACE function f_test(COL IN VARCHAR2) return sys_refcursor AS
R_C SYS_REFCURSOR;
v_sql varchar2(1000);
BEGIN
v_sql:='select '||col||' from t_student';
OPEN R_C FOR v_sql;
return(r_c);
END;
--调用函数
DECLARE
r_c SYS_REFCURSOR;
NAME VARCHAR2(10);
BEGIN
select f_test(col1) into r_c from t_test where rownum<=1;
LOOP
FETCH r_c INTO NAME;
EXIT WHEN r_c%NOTFOUND;
dbms_output.put_Line(NAME);
END LOOP;
CLOSE r_c;
END;