17,086
社区成员
发帖
与我相关
我的任务
分享
SQL> SELECT * FROM EMP;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-04-02 2975.00 20
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 10
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-03 950.00 30
7902 FORD ANALYST 7566 1981-12-03 3000.00 20
7934 MILLER CLERK 7782 1982-01-23 1300.00 10
14 rows selected
SQL>
SQL> CREATE OR REPLACE PROCEDURE MY_PRO(COL_NAME VARCHAR2) IS VCUR SYS_REFCURSOR;
2 V_SQL VARCHAR2(4000);
3 V_COL_VALUE VARCHAR2(100);
4 BEGIN
5 V_SQL := 'select ' || COL_NAME || ' from emp';
6 OPEN VCUR FOR V_SQL;
7 LOOP
8 FETCH VCUR
9 INTO V_COL_VALUE;
10 EXIT WHEN VCUR%NOTFOUND;
11 DBMS_OUTPUT.PUT_LINE(V_COL_VALUE);
12 END LOOP;
13 CLOSE VCUR;
14 END;
15 /
Procedure created
SQL> set serveroutput on
SQL> exec my_pro('ENAME');
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
PL/SQL procedure successfully completed