更新时间:2021-11-11 22:00:39
您需要从REF CURSOR
获取并在打开游标时将column_name动态附加到select语句.在这里,我从表EMPLOYEES
的USER_TAB_COLUMNS
中获取所有列名,并将其对应的值分配给v_temp.
You need to fetch from a REF CURSOR
and dynamically append the column_name to the select statement while opening the cursor. Here I am fetching all the column names from USER_TAB_COLUMNS
for table EMPLOYEES
and assigning their corresponding values to v_temp.
SET SERVEROUTPUT ON;
DECLARE
v_temp VARCHAR(50);
query1 VARCHAR2(1000);
c1 SYS_REFCURSOR;
CURSOR c2
IS
SELECT COLUMN_NAME xxx FROM USER_TAB_COLUMNS WHERE TABLE_NAME = 'EMPLOYEES';
BEGIN
FOR s2 IN c2
LOOP
--do something
query1 := 'SELECT ' ||s2.xxx||' FROM EMPLOYEES';
OPEN c1 FOR query1 ;
LOOP
FETCH c1 INTO v_temp;
DBMS_OUTPUT.PUT_LINE('COLUMN:'||s2.xxx||', VALUE:'|| v_temp);
EXIT
WHEN c1%NOTFOUND;
END LOOP;
CLOSE c1;
END LOOP;
END;
/
由于Employees
的所有列的长度均为< 50,它正常工作.对于NUMBER
和DATE
数据类型,隐式进行转换.
Since lengths of all the columns of Employees
are < 50 , it is working Fine.The conversion happens implicitly for NUMBER
and DATE
data types.
这是示例输出.
COLUMN:EMPLOYEE_ID, VALUE:100
COLUMN:EMPLOYEE_ID, VALUE:101
COLUMN:EMPLOYEE_ID, VALUE:102
COLUMN:FIRST_NAME, VALUE:Eleni
COLUMN:FIRST_NAME, VALUE:Eleni
COLUMN:LAST_NAME, VALUE:Whalen
COLUMN:LAST_NAME, VALUE:Fay
COLUMN:HIRE_DATE, VALUE:17-06-03
COLUMN:HIRE_DATE, VALUE:21-09-05