Oracle에서 열 이름에 따라 열을 선택하는 방법

질문:

나는 다음과 같은 방법을 시도했지만 효과가 없었다.

select (
    select COLUMN_NAME from ALL_TAB_COLUMNS 
    WHERE COLUMN_NAME LIKE '%_<SOME_SUFFIX>' 
    AND OWNER = '<SCHEMA>' AND TABLE_NAME = '<TABLE_NAME>'
)
FROM <SCHEMA>.<TABLE_NAME>

답안

에서 이 예에서 나는 표(매개 변수로 함수에 전달)의 열 목록에 따라 select 문장을 작성하고 있는데 이 표의 명칭은 일부 조건을 충족시킨다.이것은 Scott의 emp 표이고, 열에 접미사가 없기 때문에, 나는 이름에 자모 E가 포함된 열을 선택했다.
이 열 목록listagg과 집합)을 바탕으로 l_str 변수는 완전한 select 문장을 포함하고 이 문장은 역으로refcursor의 원본이다.

SQL> CREATE OR REPLACE FUNCTION f_test (par_table_name IN VARCHAR2)
  2     RETURN SYS_REFCURSOR
  3  IS
  4     l_cols  VARCHAR2 (100);
  5     l_str   VARCHAR2 (200);
  6     rc      SYS_REFCURSOR;
  7  BEGIN
  8     -- list of column names that satisfy some condition; for example, have some suffix or, as
  9     -- in my example, have a letter "E" in their name
 10     SELECT LISTAGG (column_name, ', ') WITHIN GROUP (ORDER BY column_name)
 11       INTO l_cols
 12       FROM user_tab_columns
 13      WHERE     table_name = DBMS_ASSERT.sql_object_name (par_table_name)
 14            AND INSTR (column_name, 'E') > 0;
 15
 16     l_str :=
 17           'select '
 18        || l_cols
 19        || ' from '
 20        || DBMS_ASSERT.sql_object_name (par_table_name);
 21
 22     OPEN rc FOR l_str;
 23
 24     RETURN rc;
 25  END;
 26  /

Function created.

테스트:

SQL> select f_test('EMP') from dual;

F_TEST('EMP')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO      EMPNO ENAME      HIREDATE
---------- ---------- ---------- ----------
        20       7369 SMITH      17/12/1980
        30       7499 ALLEN      20/02/1981
        30       7521 WARD       22/02/1981
        20       7566 JONES      02/04/1981
        30       7654 MARTIN     28/09/1981
        30       7698 BLAKE      01/05/1981
        10       7782 CLARK      09/06/1981
        20       7788 SCOTT      09/12/1982
        10       7839 KING       17/11/1981
        30       7844 TURNER     08/09/1981
        20       7876 ADAMS      12/01/1983
        30       7900 JAMES      03/12/1981
        20       7902 FORD       03/12/1981
        10       7934 MILLER     23/01/1982

14 rows selected.

    
SQL> select f_test('DEPT') from dual;

F_TEST('DEPT')
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

    DEPTNO DNAME
---------- --------------
        10 ACCOUNTING
        20 RESEARCH
        30 SALES
        40 OPERATIONS


SQL>