Oracle PL/SQL 笔记 : 动态取得Table栏位

当使用CURSOR时不确定有哪些TABLE栏位，可透过以下实作动态取得Table栏位

建立新TABLE

CREATE TABLE "DYNAMICTABLE"    (    "COUNTRY" VARCHAR2(40 BYTE),         "CAPITAL" VARCHAR2(40 BYTE),         "ENGLISHNAME" VARCHAR2(40 BYTE)    );

新增TABLE资料

INSERT INTO "DYNAMICTABLE" (COUNTRY, CAPITAL, ENGLISHNAME) VALUES ('United States', 'Washington', 'US');INSERT INTO "DYNAMICTABLE" (COUNTRY, CAPITAL, ENGLISHNAME) VALUES ('Korea', 'Seoul', 'KR');

建立FUNCTION : COLUMNRETURN 回传 TABLE栏位

CREATE OR REPLACE FUNCTION COLUMNRETURN(                   in_v_TABLE  IN VARCHAR2      ) RETURN  VARCHAR2 ASPRAGMA AUTONOMOUS_TRANSACTION;       V_ID INTEGER;      V_CNT NUMBER;      V_SQL VARCHAR(2000);      V_DTBL DBMS_SQL.DESC_TAB;      V_RESULT VARCHAR(2000);BEGIN    V_SQL :='select * from  '||in_v_TABLE||' ';    V_ID := dbms_sql.open_cursor();    dbms_sql.parse(V_ID,V_SQL,dbms_sql.native);    Dbms_Sql.Describe_Columns(V_ID,V_CNT,V_DTBL);        for i in 1..V_DTBL.count loop      if V_RESULT is not null then        V_RESULT := V_RESULT || ' , ' || V_DTBL(i).col_name;      else        V_RESULT :=V_DTBL(i).col_name;      end if;    end loop;    dbms_sql.close_cursor(V_ID);  return V_RESULT;EXCEPTION    WHEN OTHERS    THEN       return 'Fail';END COLUMNRETURN;

执行

select COLUMNRETURN('DYNAMICTABLE') from dual;

执行结果