ORACLE Archives - icodebroker

[DATABASE/ORACLE] 해당 월 달력 조회하기

■ 해당 월 달력을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT
    CASE WHEN (LEVEL * 7 - 6 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 6 - WEEK) ELSE '' END AS 일
   ,CASE WHEN (LEVEL * 7 - 5 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 5 - WEEK) ELSE '' END AS 월
   ,CASE WHEN (LEVEL * 7 - 4 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 4 - WEEK) ELSE '' END AS 화
   ,CASE WHEN (LEVEL * 7 - 3 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 3 - WEEK) ELSE '' END AS 수
   ,CASE WHEN (LEVEL * 7 - 2 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 2 - WEEK) ELSE '' END AS 목
   ,CASE WHEN (LEVEL * 7 - 1 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 1 - WEEK) ELSE '' END AS 금
   ,CASE WHEN (LEVEL * 7 - 0 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 0 - WEEK) ELSE '' END AS 토
FROM DUAL
    ,(SELECT TO_CHAR(LAST_DAY(TO_DATE('202002', 'YYYYMM')), 'DD') AS LAST FROM DUAL)
    ,(SELECT TO_CHAR(TO_DATE('202002', 'YYYYMM'), 'D') - 1 AS WEEK FROM DUAL)
CONNECT BY LEVEL <= CEIL((WEEK + LAST) / 7);

SELECT

CASE WHEN (LEVEL * 7 - 6 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 6 - WEEK) ELSE '' END AS 일

,CASE WHEN (LEVEL * 7 - 5 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 5 - WEEK) ELSE '' END AS 월

,CASE WHEN (LEVEL * 7 - 4 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 4 - WEEK) ELSE '' END AS 화

,CASE WHEN (LEVEL * 7 - 3 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 3 - WEEK) ELSE '' END AS 수

,CASE WHEN (LEVEL * 7 - 2 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 2 - WEEK) ELSE '' END AS 목

,CASE WHEN (LEVEL * 7 - 1 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 1 - WEEK) ELSE '' END AS 금

,CASE WHEN (LEVEL * 7 - 0 - WEEK) BETWEEN 1 AND LAST THEN TO_CHAR(LEVEL * 7 - 0 - WEEK) ELSE '' END AS 토

FROM DUAL

,(SELECT TO_CHAR(LAST_DAY(TO_DATE('202002', 'YYYYMM')), 'DD') AS LAST FROM DUAL)

,(SELECT TO_CHAR(TO_DATE('202002', 'YYYYMM'), 'D') - 1 AS WEEK FROM DUAL)

CONNECT BY LEVEL <= CEIL((WEEK + LAST) / 7);

[DATABASE/ORACLE] INDEX 힌트 : 테이블의 인덱스 사용 설정하기

■ INDEX 힌트를 사용해 테이블의 인덱스를 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT /*+ INDEX(EMP EMPNO_INDEX) */
    EMPNO
   ,ENAME
FROM SCOTT.EMP
WHERE DEPTNO = 10;

SELECT /*+ INDEX(EMP EMPNO_INDEX) */

EMPNO

,ENAME

FROM SCOTT.EMP

WHERE DEPTNO = 10;

※ /*+ INDEX(테이블명 인덱스명) */

[DATABASE/ORACLE] FULL 힌트 : 테이블 FULL SCAN 사용하기

■ FULL 힌트를 사용해 테이블을 FULL SCAN 하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT /*+ FULL(ORDERS) */
    ORDER_ID
   ,ORDER_DATE
   ,ORDER_AMOUNT
FROM  ORDERS
WHERE ORDER_DATE BETWEEN TO_CHAR('2020-01-01', 'YYYY-MM-DD') AND TO_CHAR('2020-01-31', 'YYYY-MM-DD')
ORDER BY ORDER_ID;

SELECT /*+ FULL(ORDERS) */

ORDER_ID

,ORDER_DATE

,ORDER_AMOUNT

FROM ORDERS

WHERE ORDER_DATE BETWEEN TO_CHAR('2020-01-01', 'YYYY-MM-DD') AND TO_CHAR('2020-01-31', 'YYYY-MM-DD')

ORDER BY ORDER_ID;

※ /*+ FULL(테이블명) */

[DATABASE/ORACLE] USE_HASH 힌트 : 해시 함수를 이용해 테이블 간 접근하기

■ USE_HASH 힌트를 사용해 해시 함수를 이용해 테이블 간 접근하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT /*+ USE_HASH(B) */
    A.DEPARTMENT_NAME
   ,A.DEPARTMENT_ID
   ,SUM(B.SALES_AMOUNT)
FROM  DEPARTMENT A, SALES B
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
AND   A.BUSINESS_NAME = '가전사업부'
AND   B.SALES_YM      = '202001'
GROUP BY
    A.DEPARTMENT_NAME
   ,A.DEPARTMENT_ID

SELECT /*+ USE_HASH(B) */

A.DEPARTMENT_NAME

,A.DEPARTMENT_ID

,SUM(B.SALES_AMOUNT)

FROM DEPARTMENT A, SALES B

WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID

AND A.BUSINESS_NAME = '가전사업부'

AND B.SALES_YM = '202001'

GROUP BY

A.DEPARTMENT_NAME

,A.DEPARTMENT_ID

※ /*+ USE_HASH(테이블명 [테이블명 …])

[DATABASE/ORACLE] USE_NL 힌트 : 순차적인 루프에 의한 테이블 간 접근하기

■ USE_NL 힌트를 사용해 순차적인 루프에 의한 테이블 간 접근하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT /*+ USE_NL(B) */
    A.CUSTOMER_ID
   ,A.CUSTOMER_NAME
   ,B.ORDER_DATE
   ,B.ORDER_NAME
FROM CUSTOMER A, ORDER B
WHERE A.CUSTOMER_ID   = B.CUSTOMER_ID
AND   A.CUSTOMER_NAME = '홍길동'
AND   B.ORDER_DATE    = '20200201'

SELECT /*+ USE_NL(B) */

A.CUSTOMER_ID

,A.CUSTOMER_NAME

,B.ORDER_DATE

,B.ORDER_NAME

FROM CUSTOMER A, ORDER B

WHERE A.CUSTOMER_ID = B.CUSTOMER_ID

AND A.CUSTOMER_NAME = '홍길동'

AND B.ORDER_DATE = '20200201'

※ /*+ USE_NL(테이블명 [테이블명 …])

[DATABASE/ORACLE] LEADING 힌트 : 테이블 접근 순서 설정하기

■ LEADING 힌트를 사용해 테이블 접근 순서를 설정하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT /*+ LEADING(B A) */
    A.CUSTOMER_ID
   ,A.CUSTOMER_NAME
   ,B.ORDER_DATE
   ,B.ORDER_NAME
FROM CUSTOMER A, ORDER B
WHERE A.CUSTOMER_ID   = B.CUSTOMER_ID
AND   A.CUSTOMER_NAME = '홍길동'
AND   B.ORDER_DATE    = '20200201'

SELECT /*+ LEADING(B A) */

A.CUSTOMER_ID

,A.CUSTOMER_NAME

,B.ORDER_DATE

,B.ORDER_NAME

FROM CUSTOMER A, ORDER B

WHERE A.CUSTOMER_ID = B.CUSTOMER_ID

AND A.CUSTOMER_NAME = '홍길동'

AND B.ORDER_DATE = '20200201'

※ /*+ LEADING(테이블명 [테이블명 …]) */

[DATABASE/ORACLE] ORDERED 힌트 : FROM절의 테이블 순서대로 접근하기

■ ORDERED 힌트를 사용해 FROM절의 테이블 순서대로 접근하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SELECT /*+ ORDERED */
    A.CUSTOMER_ID
   ,A.CUSTOMER_NAME
   ,B.ORDER_DATE
   ,B.ORDER_NAME
FROM CUSTOMER A, ORDER B
WHERE A.CUSTOMER_ID   = B.CUSTOMER_ID
AND   A.CUSTOMER_NAME = '홍길동'
AND   B.ORDER_DATE    = '20200201'

SELECT /*+ ORDERED */

A.CUSTOMER_ID

,A.CUSTOMER_NAME

,B.ORDER_DATE

,B.ORDER_NAME

FROM CUSTOMER A, ORDER B

WHERE A.CUSTOMER_ID = B.CUSTOMER_ID

AND A.CUSTOMER_NAME = '홍길동'

AND B.ORDER_DATE = '20200201'

[DATABASE/ORACLE] 힌트 분류표

■ 힌트 분류표를 보여준다. ▶ 힌트 분류표


────────────────────────────────
구분                                힌트
─────────────────  ──────────────
Optimization Goals and Approaches   ALL_ROWS 혹은 FIRST_ROWS
                                    CHOOSE
                                    RULE
Acess Method Hints                  AND_EQUAL
                                    CLUSTER
                                    FULL
                                    HASH
                                    INDEX 혹은 NO_INDEX
                                    INDEX_ASC 혹은 INDEX_DESC
                                    INDEX_COMBINE
                                    INDEX_FFS
                                    ROWID
Join Order Hints                    ORDERED
                                    STAR
Join Operation Hints                DRIVING_SITE
                                    HASH_SJ, MERGE_SJ 혹은 NL_SJ
                                    LEADING
                                    USE_HASH 혹은 USE_MERGE
                                    USE_NL
Parallel Execution Hints            PARALLEL 혹은 NOPARALLEL
                                    PARALLEL_INDEX
                                    PQ_DISTRIBUTE
                                    NOPARALLEL_INDEX
Query Transformation Hints          EXPAND_GSET_TO_UNION
                                    FACT 혹은 NOFACT
                                    MERGE
                                    NO_EXPAND
                                    NO_MERGE
                                    REWIRTE 혹은 NOREWRITE
                                    STAR_TRANSFORMATION
                                    USE_CONCAT
Other Hints                         APPEND 혹은 NOAPPEND
                                    CACHE 혹은 NOCACHE
                                    CURSOR_SHARED_EXACT
                                    DYNAMIC_SAMPLING
                                    NESTED_TABLE_GET_REFS
                                    UNNEST 혹은 NO_UNNEST
                                    ORDERED_PREDICATES
────────────────────────────────

────────────────────────────────

구분 힌트

───────────────── ──────────────

Optimization Goals and Approaches ALL_ROWS 혹은 FIRST_ROWS

CHOOSE

RULE

Acess Method Hints AND_EQUAL

CLUSTER

FULL

HASH

INDEX 혹은 NO_INDEX

INDEX_ASC 혹은 INDEX_DESC

INDEX_COMBINE

INDEX_FFS

ROWID

Join Order Hints ORDERED

STAR

Join Operation Hints DRIVING_SITE

HASH_SJ, MERGE_SJ 혹은 NL_SJ

LEADING

USE_HASH 혹은 USE_MERGE

USE_NL

Parallel Execution Hints PARALLEL 혹은 NOPARALLEL

PARALLEL_INDEX

PQ_DISTRIBUTE

NOPARALLEL_INDEX

Query Transformation Hints EXPAND_GSET_TO_UNION

FACT 혹은 NOFACT

MERGE

NO_EXPAND

NO_MERGE

REWIRTE 혹은 NOREWRITE

STAR_TRANSFORMATION

USE_CONCAT

Other Hints APPEND 혹은 NOAPPEND

CACHE 혹은 NOCACHE

CURSOR_SHARED_EXACT

DYNAMIC_SAMPLING

NESTED_TABLE_GET_REFS

UNNEST 혹은 NO_UNNEST

ORDERED_PREDICATES

────────────────────────────────

[DATABASE/ORACLE] CREATE TABLESPACE 명령 : 테이블 스페이스 생성하기

■ CREATE TABLESPACE 명령을 사용해 테이블 스페이스를 생성하는 방법을 보여준다. ▶ 예제 코드 (SQL)


CREATE TABLESPACE ARCA DATAFILE 'C:\app\king\oradata\orcl\ARCA.dbf' SIZE 500M;
                  ----          -----------------------------------      -----
                  명칭          파일 경로                                파일 크기

CREATE TABLESPACE ARCA DATAFILE 'C:\app\king\oradata\orcl\ARCA.dbf' SIZE 500M;

---- ----------------------------------- -----

명칭 파일 경로 파일 크기

[DATABASE/ORACLE] CREATE USER 명령 : 오라클 계정 생성하기

■ CREATE USER 명령을 사용해 오라클 계정을 생성하는 방법을 보여준다. ▶ 예제 코드 (SQL)


CREATE USER SCOTT IDENTIFIED BY passw0rd DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
            -----               --------                    -----                      ----
            계정명              패스워드                    테이블 스페이스            테이블 스페이스

CREATE USER SCOTT IDENTIFIED BY passw0rd DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

----- -------- ----- ----

계정명 패스워드 테이블 스페이스 테이블 스페이스

[DATABASE/ORACLE] ALTER USER 명령 : 계정 잠금 해제하기

■ ALTER USER 명령을 사용해 계정 잠금을 해제하는 방법을 보여준다. ▶ 예제 코드 (SQL)


ALTER USER SCOTT ACCOUNT UNLOCK;
           -----
           계정명

ALTER USER SCOTT ACCOUNT UNLOCK;

-----

계정명

[DATABASE/ORACLE] GRANT 명령 : 계정 권한 부여하기

■ GRANT 명령을 사용해 계정 권한을 부여하는 방법을 보여준다. ▶ 예제 코드 (SQL)


GRANT CONNECT, DBA, RESOURCE TO SCOTT;
      ----------------------    -----
      접근 권한                 계정명

GRANT CONNECT, DBA, RESOURCE TO SCOTT;

---------------------- -----

접근 권한 계정명

[DATABASE/ORACLE] CONNECT 명령 : 계정 생성 확인하기

■ CONNECT 명령을 사용해 계정 생성을 확인하는 방법을 보여준다. ▶ 예제 코드 (SQL)


CONNECT SCOTT/passw0rd

CONNECT SCOTT/passw0rd

※ SCOTT : 계정명 ※ passw0rd : 패스워드

[DATABASE/ORACLE] SELECT ~ BULK COLLECT INTO문 사용하기

■ SELECT ~ BULK COLLECT INTO문을 사용해 데이터를 일괄로 추가하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 직원 테이블을 생성한다.
CREATE TABLE EMP
(
    EMPNO    NUMBER(4, 0)
   ,ENAME    VARCHAR2(10)
   ,SAL      NUMBER(7, 2)
   ,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);

-- 직원 테이블에 데이터를 추가한다.
INSERT INTO EMP VALUES(7369, 'SMITH' ,  800);
INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600);
INSERT INTO EMP VALUES(7521, 'WARD'  , 1250);
INSERT INTO EMP VALUES(7566, 'JONES' , 2975);
INSERT INTO EMP VALUES(7654, 'MARTIN', 1250);
INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850);
INSERT INTO EMP VALUES(7782, 'CLARK' , 2450);
INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000);
INSERT INTO EMP VALUES(7839, 'KING'  , 5000);
INSERT INTO EMP VALUES(7844, 'TURNER', 1500);
INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100);
INSERT INTO EMP VALUES(7900, 'JAMES' , 950 );
INSERT INTO EMP VALUES(7902, 'FORD'  , 3000);
INSERT INTO EMP VALUES(7934, 'MILLER', 1300);

COMMIT;

SET SERVEROUTPUT ON;

DECLARE
    TYPE NUMBER_TABLE_TYPE IS TABLE OF NUMBER;

    V_SAL_NUMBER_TABLE NUMBER_TABLE_TYPE;
BEGIN
    SELECT SAL BULK COLLECT INTO V_SAL_NUMBER_TABLE
    FROM   EMP;

    IF SQL%ROWCOUNT > 0
    THEN
        FOR I IN V_SAL_NUMBER_TABLE.FIRST..V_SAL_NUMBER_TABLE.LAST
        LOOP
            DBMS_OUTPUT.PUT_LINE(V_SAL_NUMBER_TABLE(I));
        END LOOP;
    END IF;
END;

-- 직원 테이블을 생성한다.

CREATE TABLE EMP

(

EMPNO NUMBER(4, 0)

,ENAME VARCHAR2(10)

,SAL NUMBER(7, 2)

,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)

);

-- 직원 테이블에 데이터를 추가한다.

INSERT INTO EMP VALUES(7369, 'SMITH' , 800);

INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600);

INSERT INTO EMP VALUES(7521, 'WARD' , 1250);

INSERT INTO EMP VALUES(7566, 'JONES' , 2975);

INSERT INTO EMP VALUES(7654, 'MARTIN', 1250);

INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850);

INSERT INTO EMP VALUES(7782, 'CLARK' , 2450);

INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000);

INSERT INTO EMP VALUES(7839, 'KING' , 5000);

INSERT INTO EMP VALUES(7844, 'TURNER', 1500);

INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100);

INSERT INTO EMP VALUES(7900, 'JAMES' , 950 );

INSERT INTO EMP VALUES(7902, 'FORD' , 3000);

INSERT INTO EMP VALUES(7934, 'MILLER', 1300);

COMMIT;

SET SERVEROUTPUT ON;

DECLARE

TYPE NUMBER_TABLE_TYPE IS TABLE OF NUMBER;

V_SAL_NUMBER_TABLE NUMBER_TABLE_TYPE;

BEGIN

SELECT SAL BULK COLLECT INTO V_SAL_NUMBER_TABLE

FROM EMP;

IF SQL%ROWCOUNT > 0

THEN

FOR I IN V_SAL_NUMBER_TABLE.FIRST..V_SAL_NUMBER_TABLE.LAST

LOOP

DBMS_OUTPUT.PUT_LINE(V_SAL_NUMBER_TABLE(I));

END LOOP;

END IF;

END;

[DATABASE/ORACLE] 커서 속성

■ 커서 속성을 사용하는 방법에 대해 보여준다. ▶ 커서 속성 사용법


────────────────────────────────────────────────
이름              설명
────────  ───────────────────────────────────────
%FOUND            레코드가 성공적으로 페치되면 TRUE를 아니면 FALSE가 반환된다.
%NOTFOUND         레코드가 성공적으로 페치되지 않으면 TRUE를 아니면 FALSE가 반환된다.
%ROWCOUNT         어떤 순간에 커서로부터 페치된 레코드의 수를 반환한다.
%ISOPEN           커서가 열리면 TRUE를 아니면 FALSE를 반환한다.
%BULK_ROWCOUNT    각각의 컬렉션 요소에서 FORALL문에 의해 수정되는 레코드 수를 반환한다.
%BULK_EXCEPTIONS  각각의 컬렉션 요소에서 FORALL문에 의해 수정되는 행들의 예외 정보를 반환한다.
────────────────────────────────────────────────

────────────────────────────────────────────────

이름 설명

──────── ───────────────────────────────────────

%FOUND 레코드가 성공적으로 페치되면 TRUE를 아니면 FALSE가 반환된다.

%NOTFOUND 레코드가 성공적으로 페치되지 않으면 TRUE를 아니면 FALSE가 반환된다.

%ROWCOUNT 어떤 순간에 커서로부터 페치된 레코드의 수를 반환한다.

%ISOPEN 커서가 열리면 TRUE를 아니면 FALSE를 반환한다.

%BULK_ROWCOUNT 각각의 컬렉션 요소에서 FORALL문에 의해 수정되는 레코드 수를 반환한다.

%BULK_EXCEPTIONS 각각의 컬렉션 요소에서 FORALL문에 의해 수정되는 행들의 예외 정보를 반환한다.

────────────────────────────────────────────────

[DATABASE/ORACLE] FORALL ~ UPDATE ~ RETURNING ~ BULK COLLECT INTO문 사용하기

■ FORALL ~ UPDATE ~ RETURNING ~ BULK COLLECT INTO문을 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 직원 테이블을 생성한다.
CREATE TABLE EMP
(
    EMPNO    NUMBER(4, 0)
   ,ENAME    VARCHAR2(10)
   ,SAL      NUMBER(7, 2)
   ,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);

-- 직원 테이블에 데이터를 추가한다.
INSERT INTO EMP VALUES(7369, 'SMITH' ,  800);
INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600);
INSERT INTO EMP VALUES(7521, 'WARD'  , 1250);
INSERT INTO EMP VALUES(7566, 'JONES' , 2975);
INSERT INTO EMP VALUES(7654, 'MARTIN', 1250);
INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850);
INSERT INTO EMP VALUES(7782, 'CLARK' , 2450);
INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000);
INSERT INTO EMP VALUES(7839, 'KING'  , 5000);
INSERT INTO EMP VALUES(7844, 'TURNER', 1500);
INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100);
INSERT INTO EMP VALUES(7900, 'JAMES' , 950 );
INSERT INTO EMP VALUES(7902, 'FORD'  , 3000);
INSERT INTO EMP VALUES(7934, 'MILLER', 1300);

COMMIT;

SET SERVEROUTPUT ON;

DECLARE
    -- 숫자 VARRAY 타입을 정의한다.
    TYPE NUMBER_ARRAY_TYPE IS VARRAY(14) OF NUMBER;

    -- 숫자 VARRAY 타입의 직원 번호 VARRAY 변수를 정의하고 초기화 한다.
    V_EMPNO_NUMBER_ARRAY NUMBER_ARRAY_TYPE := NUMBER_ARRAY_TYPE
    (
        7369, 7499, 7521,
        7566, 7654, 7698,
        7782, 7788, 7839,
        7844, 7876, 7900,
        7902, 7934
    );

    -- 숫자 VARRAY 타입의 급여 VARRAY 변수를 정의한다.
    V_SAL_NUMBER_ARRAY NUMBER_ARRAY_TYPE;
BEGIN
    FORALL I IN V_EMPNO_NUMBER_ARRAY.FIRST..V_EMPNO_NUMBER_ARRAY.LAST
        UPDATE EMP
        SET    SAL   = SAL + 100
        WHERE  EMPNO = V_EMPNO_NUMBER_ARRAY(I)
        RETURNING SAL BULK COLLECT INTO V_SAL_NUMBER_ARRAY;

    FOR I IN V_SAL_NUMBER_ARRAY.FIRST..V_SAL_NUMBER_ARRAY.LAST
    LOOP
        DBMS_OUTPUT.PUT_LINE(V_SAL_NUMBER_ARRAY(I));
    END LOOP;
END;

-- 직원 테이블을 생성한다.

CREATE TABLE EMP

(

EMPNO NUMBER(4, 0)

,ENAME VARCHAR2(10)

,SAL NUMBER(7, 2)

,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)

);

-- 직원 테이블에 데이터를 추가한다.

INSERT INTO EMP VALUES(7369, 'SMITH' , 800);

INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600);

INSERT INTO EMP VALUES(7521, 'WARD' , 1250);

INSERT INTO EMP VALUES(7566, 'JONES' , 2975);

INSERT INTO EMP VALUES(7654, 'MARTIN', 1250);

INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850);

INSERT INTO EMP VALUES(7782, 'CLARK' , 2450);

INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000);

INSERT INTO EMP VALUES(7839, 'KING' , 5000);

INSERT INTO EMP VALUES(7844, 'TURNER', 1500);

INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100);

INSERT INTO EMP VALUES(7900, 'JAMES' , 950 );

INSERT INTO EMP VALUES(7902, 'FORD' , 3000);

INSERT INTO EMP VALUES(7934, 'MILLER', 1300);

COMMIT;

SET SERVEROUTPUT ON;

DECLARE

-- 숫자 VARRAY 타입을 정의한다.

TYPE NUMBER_ARRAY_TYPE IS VARRAY(14) OF NUMBER;

-- 숫자 VARRAY 타입의 직원 번호 VARRAY 변수를 정의하고 초기화 한다.

V_EMPNO_NUMBER_ARRAY NUMBER_ARRAY_TYPE := NUMBER_ARRAY_TYPE

(

7369, 7499, 7521,

7566, 7654, 7698,

7782, 7788, 7839,

7844, 7876, 7900,

7902, 7934

);

-- 숫자 VARRAY 타입의 급여 VARRAY 변수를 정의한다.

V_SAL_NUMBER_ARRAY NUMBER_ARRAY_TYPE;

BEGIN

FORALL I IN V_EMPNO_NUMBER_ARRAY.FIRST..V_EMPNO_NUMBER_ARRAY.LAST

UPDATE EMP

SET SAL = SAL + 100

WHERE EMPNO = V_EMPNO_NUMBER_ARRAY(I)

RETURNING SAL BULK COLLECT INTO V_SAL_NUMBER_ARRAY;

FOR I IN V_SAL_NUMBER_ARRAY.FIRST..V_SAL_NUMBER_ARRAY.LAST

LOOP

DBMS_OUTPUT.PUT_LINE(V_SAL_NUMBER_ARRAY(I));

END LOOP;

END;

[DATABASE/ORACLE] FORALL 명령 : 레코드 기반 데이터 추가하기

■ FORALL 명령을 사용해 레코드 기반 데이터를 추가하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 직원 테이블을 생성한다.
CREATE TABLE EMPLOYEE
(
    EMPLOYEE_ID   NUMBER(4, 0)
   ,EMPLOYEE_NAME VARCHAR2(10)
   ,CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID)
);

DECLARE
    -- 직원 중첩 테이블 타입을 정의한다.
    TYPE EMPLOYEE_TABLE_TYPE IS TABLE OF EMPLOYEE%ROWTYPE;

    -- 직원 중첩 테이블 타입의 변수를 정의하고 초기화 한다.
    V_EMPLOYEE_TABLE EMPLOYEE_TABLE_TYPE := EMPLOYEE_TABLE_TYPE();
BEGIN
    -- 직원 중첩 테이블 변수에 데이터를 추가한다.
    V_EMPLOYEE_TABLE.EXTEND(2);

    V_EMPLOYEE_TABLE(1).EMPLOYEE_ID   := 100;
    V_EMPLOYEE_TABLE(1).EMPLOYEE_NAME := '홍길동';

    V_EMPLOYEE_TABLE(2).EMPLOYEE_ID   := 200;
    V_EMPLOYEE_TABLE(2).EMPLOYEE_NAME := '김철수';

    -- 직원 중첩 테이블 변수의 데이터를 직원 테이블에 추가한다.
    FORALL I IN V_EMPLOYEE_TABLE.FIRST..V_EMPLOYEE_TABLE.LAST
        INSERT INTO EMPLOYEE VALUES V_EMPLOYEE_TABLE(I);

    COMMIT;
END;

-- 직원 테이블 데이터를 조회한다.
SELECT * FROM EMPLOYEE;

-- 직원 테이블을 생성한다.

CREATE TABLE EMPLOYEE

(

EMPLOYEE_ID NUMBER(4, 0)

,EMPLOYEE_NAME VARCHAR2(10)

,CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID)

);

DECLARE

-- 직원 중첩 테이블 타입을 정의한다.

TYPE EMPLOYEE_TABLE_TYPE IS TABLE OF EMPLOYEE%ROWTYPE;

-- 직원 중첩 테이블 타입의 변수를 정의하고 초기화 한다.

V_EMPLOYEE_TABLE EMPLOYEE_TABLE_TYPE := EMPLOYEE_TABLE_TYPE();

BEGIN

-- 직원 중첩 테이블 변수에 데이터를 추가한다.

V_EMPLOYEE_TABLE.EXTEND(2);

V_EMPLOYEE_TABLE(1).EMPLOYEE_ID := 100;

V_EMPLOYEE_TABLE(1).EMPLOYEE_NAME := '홍길동';

V_EMPLOYEE_TABLE(2).EMPLOYEE_ID := 200;

V_EMPLOYEE_TABLE(2).EMPLOYEE_NAME := '김철수';

-- 직원 중첩 테이블 변수의 데이터를 직원 테이블에 추가한다.

FORALL I IN V_EMPLOYEE_TABLE.FIRST..V_EMPLOYEE_TABLE.LAST

INSERT INTO EMPLOYEE VALUES V_EMPLOYEE_TABLE(I);

COMMIT;

END;

-- 직원 테이블 데이터를 조회한다.

SELECT * FROM EMPLOYEE;

[DATABASE/ORACLE] DML 명령문 : RETURNING 키워드 사용하기

■ DML 명령문에서 RETURNING 키워드를 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 직원 테이블을 생성한다.
CREATE TABLE EMP
(
    EMPNO    NUMBER(4, 0)
   ,ENAME    VARCHAR2(10)
   ,SAL      NUMBER(7, 2)
   ,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);

-- 직원 테이블에 데이터를 추가한다.
INSERT INTO EMP VALUES(7369, 'SMITH' ,  800);
INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600);
INSERT INTO EMP VALUES(7521, 'WARD'  , 1250);
INSERT INTO EMP VALUES(7566, 'JONES' , 2975);
INSERT INTO EMP VALUES(7654, 'MARTIN', 1250);
INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850);
INSERT INTO EMP VALUES(7782, 'CLARK' , 2450);
INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000);
INSERT INTO EMP VALUES(7839, 'KING'  , 5000);
INSERT INTO EMP VALUES(7844, 'TURNER', 1500);
INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100);
INSERT INTO EMP VALUES(7900, 'JAMES' , 950 );
INSERT INTO EMP VALUES(7902, 'FORD'  , 3000);
INSERT INTO EMP VALUES(7934, 'MILLER', 1300);

COMMIT;

DECLARE
    V_ENAME EMP.ENAME%TYPE;
    V_SAL   EMP.SAL%TYPE;
BEGIN
    FOR EMPLOYEE_RECORD IN (SELECT * FROM EMP)
    LOOP
        UPDATE EMP
        SET    SAL   = SAL + 100
        WHERE  EMPNO = EMPLOYEE_RECORD.EMPNO
        RETURNING ENAME, SAL INTO V_ENAME, V_SAL;

        DBMS_OUTPUT.PUT_LINE(V_ENAME || ', ' || V_SAL);
    END LOOP;
END;

-- 직원 테이블을 생성한다.

CREATE TABLE EMP

(

EMPNO NUMBER(4, 0)

,ENAME VARCHAR2(10)

,SAL NUMBER(7, 2)

,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)

);

-- 직원 테이블에 데이터를 추가한다.

INSERT INTO EMP VALUES(7369, 'SMITH' , 800);

INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600);

INSERT INTO EMP VALUES(7521, 'WARD' , 1250);

INSERT INTO EMP VALUES(7566, 'JONES' , 2975);

INSERT INTO EMP VALUES(7654, 'MARTIN', 1250);

INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850);

INSERT INTO EMP VALUES(7782, 'CLARK' , 2450);

INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000);

INSERT INTO EMP VALUES(7839, 'KING' , 5000);

INSERT INTO EMP VALUES(7844, 'TURNER', 1500);

INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100);

INSERT INTO EMP VALUES(7900, 'JAMES' , 950 );

INSERT INTO EMP VALUES(7902, 'FORD' , 3000);

INSERT INTO EMP VALUES(7934, 'MILLER', 1300);

COMMIT;

DECLARE

V_ENAME EMP.ENAME%TYPE;

V_SAL EMP.SAL%TYPE;

BEGIN

FOR EMPLOYEE_RECORD IN (SELECT * FROM EMP)

LOOP

UPDATE EMP

SET SAL = SAL + 100

WHERE EMPNO = EMPLOYEE_RECORD.EMPNO

RETURNING ENAME, SAL INTO V_ENAME, V_SAL;

DBMS_OUTPUT.PUT_LINE(V_ENAME || ', ' || V_SAL);

END LOOP;

END;

[DATABASE/ORACLE] SQL%ROWCOUNT 암시적 커서 속성 사용하기

■ SQL%ROWCOUNT 암시적 커서 속성을 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 직원 테이블을 생성한다.
CREATE TABLE EMP
(
    EMPNO    NUMBER(4, 0)
   ,ENAME    VARCHAR2(10)
   ,SAL      NUMBER(7, 2)
   ,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);

-- 직원 테이블에 데이터를 추가한다.
INSERT INTO EMP VALUES(7369, 'SMITH' ,  800);
INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600);
INSERT INTO EMP VALUES(7521, 'WARD'  , 1250);
INSERT INTO EMP VALUES(7566, 'JONES' , 2975);
INSERT INTO EMP VALUES(7654, 'MARTIN', 1250);
INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850);
INSERT INTO EMP VALUES(7782, 'CLARK' , 2450);
INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000);
INSERT INTO EMP VALUES(7839, 'KING'  , 5000);
INSERT INTO EMP VALUES(7844, 'TURNER', 1500);
INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100);
INSERT INTO EMP VALUES(7900, 'JAMES' , 950 );
INSERT INTO EMP VALUES(7902, 'FORD'  , 3000);
INSERT INTO EMP VALUES(7934, 'MILLER', 1300);

COMMIT;

-- 직원 테이블 데이터를 조회한다.
SELECT * FROM EMP;

-- 직원명 변경하기 프로시저를 생성한다.
CREATE OR REPLACE PROCEDURE ChangeEmployeeName(P_OLD_NAME IN EMP.ENAME%TYPE, P_NEW_NAME IN EMP.ENAME%TYPE, P_RECORD_COUNT OUT PLS_INTEGER)
IS
BEGIN
    UPDATE EMP
    SET    ENAME = P_NEW_NAME
    WHERE  ENAME = P_OLD_NAME;

    P_RECORD_COUNT := SQL%ROWCOUNT;
END;

SET SERVEROUTPUT ON;

DECLARE
    V_RECORD_COUNT PLS_INTEGER;
BEGIN
    ChangeEmployeeName('ALLEN', 'ALLEN_NEW', V_RECORD_COUNT);

    DBMS_OUTPUT.PUT_LINE('RECORD COUNT : ' || V_RECORD_COUNT);
END;

-- 직원 테이블을 생성한다.

CREATE TABLE EMP

(

EMPNO NUMBER(4, 0)

,ENAME VARCHAR2(10)

,SAL NUMBER(7, 2)

,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)

);

-- 직원 테이블에 데이터를 추가한다.

INSERT INTO EMP VALUES(7369, 'SMITH' , 800);

INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600);

INSERT INTO EMP VALUES(7521, 'WARD' , 1250);

INSERT INTO EMP VALUES(7566, 'JONES' , 2975);

INSERT INTO EMP VALUES(7654, 'MARTIN', 1250);

INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850);

INSERT INTO EMP VALUES(7782, 'CLARK' , 2450);

INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000);

INSERT INTO EMP VALUES(7839, 'KING' , 5000);

INSERT INTO EMP VALUES(7844, 'TURNER', 1500);

INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100);

INSERT INTO EMP VALUES(7900, 'JAMES' , 950 );

INSERT INTO EMP VALUES(7902, 'FORD' , 3000);

INSERT INTO EMP VALUES(7934, 'MILLER', 1300);

COMMIT;

-- 직원 테이블 데이터를 조회한다.

SELECT * FROM EMP;

-- 직원명 변경하기 프로시저를 생성한다.

CREATE OR REPLACE PROCEDURE ChangeEmployeeName(P_OLD_NAME IN EMP.ENAME%TYPE, P_NEW_NAME IN EMP.ENAME%TYPE, P_RECORD_COUNT OUT PLS_INTEGER)

BEGIN

UPDATE EMP

SET ENAME = P_NEW_NAME

WHERE ENAME = P_OLD_NAME;

P_RECORD_COUNT := SQL%ROWCOUNT;

END;

SET SERVEROUTPUT ON;

DECLARE

V_RECORD_COUNT PLS_INTEGER;

BEGIN

ChangeEmployeeName('ALLEN', 'ALLEN_NEW', V_RECORD_COUNT);

DBMS_OUTPUT.PUT_LINE('RECORD COUNT : ' || V_RECORD_COUNT);

END;

[DATABASE/ORACLE] SQL%FOUND 암시적 커서 속성 사용하기

■ SQL%FOUND 암시적 커서 속성을 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 직원 테이블을 생성한다.
CREATE TABLE EMP
(
    EMPNO    NUMBER(4, 0)
   ,ENAME    VARCHAR2(10)
   ,SAL      NUMBER(7, 2)
   ,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);

-- 직원 테이블에 데이터를 추가한다.
INSERT INTO EMP VALUES(7369, 'SMITH' ,  800);
INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600);
INSERT INTO EMP VALUES(7521, 'WARD'  , 1250);
INSERT INTO EMP VALUES(7566, 'JONES' , 2975);
INSERT INTO EMP VALUES(7654, 'MARTIN', 1250);
INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850);
INSERT INTO EMP VALUES(7782, 'CLARK' , 2450);
INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000);
INSERT INTO EMP VALUES(7839, 'KING'  , 5000);
INSERT INTO EMP VALUES(7844, 'TURNER', 1500);
INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100);
INSERT INTO EMP VALUES(7900, 'JAMES' , 950 );
INSERT INTO EMP VALUES(7902, 'FORD'  , 3000);
INSERT INTO EMP VALUES(7934, 'MILLER', 1300);

COMMIT;

-- 직원명 변경하기 프로시저를 생성한다.
CREATE PROCEDURE ChangeEmployeeName(P_OLD_NAME IN EMP.ENAME%TYPE, P_NEW_NAME IN EMP.ENAME%TYPE, P_CHANGED OUT BOOLEAN)
IS
BEGIN
    UPDATE EMP
    SET    ENAME = P_NEW_NAME
    WHERE  ENAME = P_OLD_NAME;

    P_CHANGED := SQL%FOUND;
END;

SET SERVEROUTPUT ON;

DECLARE
    V_CHANGED BOOLEAN;
BEGIN
    ChangeEmployeeName('ALLEN', 'ALLEN_NEW', V_CHANGED);

    IF V_CHANGED = TRUE
    THEN
        DBMS_OUTPUT.PUT_LINE('UPDATED');
    ELSE
        DBMS_OUTPUT.PUT_LINE('NO UPDATED');
    END IF;
END;

-- 직원 테이블을 생성한다.

CREATE TABLE EMP

(

EMPNO NUMBER(4, 0)

,ENAME VARCHAR2(10)

,SAL NUMBER(7, 2)

,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)

);

-- 직원 테이블에 데이터를 추가한다.

INSERT INTO EMP VALUES(7369, 'SMITH' , 800);

INSERT INTO EMP VALUES(7499, 'ALLEN' , 1600);

INSERT INTO EMP VALUES(7521, 'WARD' , 1250);

INSERT INTO EMP VALUES(7566, 'JONES' , 2975);

INSERT INTO EMP VALUES(7654, 'MARTIN', 1250);

INSERT INTO EMP VALUES(7698, 'BLAKE' , 2850);

INSERT INTO EMP VALUES(7782, 'CLARK' , 2450);

INSERT INTO EMP VALUES(7788, 'SCOTT' , 3000);

INSERT INTO EMP VALUES(7839, 'KING' , 5000);

INSERT INTO EMP VALUES(7844, 'TURNER', 1500);

INSERT INTO EMP VALUES(7876, 'ADAMS' , 1100);

INSERT INTO EMP VALUES(7900, 'JAMES' , 950 );

INSERT INTO EMP VALUES(7902, 'FORD' , 3000);

INSERT INTO EMP VALUES(7934, 'MILLER', 1300);

COMMIT;

-- 직원명 변경하기 프로시저를 생성한다.

CREATE PROCEDURE ChangeEmployeeName(P_OLD_NAME IN EMP.ENAME%TYPE, P_NEW_NAME IN EMP.ENAME%TYPE, P_CHANGED OUT BOOLEAN)

BEGIN

UPDATE EMP

SET ENAME = P_NEW_NAME

WHERE ENAME = P_OLD_NAME;

P_CHANGED := SQL%FOUND;

END;

SET SERVEROUTPUT ON;

DECLARE

V_CHANGED BOOLEAN;

BEGIN

ChangeEmployeeName('ALLEN', 'ALLEN_NEW', V_CHANGED);

IF V_CHANGED = TRUE

THEN

DBMS_OUTPUT.PUT_LINE('UPDATED');

ELSE

DBMS_OUTPUT.PUT_LINE('NO UPDATED');

END IF;

END;

[DATABASE/ORACLE] DML문의 암시적 커서 속성

■ DML문의 암시적 커서 속성을 사용하는 방법을 보여준다. ▶ 암시적 커서 속성 사용법


─────────────────────────────────────────────────────
이름                설명
─────────  ───────────────────────────────────────────
SQL%FOUND           마지막 SQL문의 실행에서 하나 또는 그 이상의 행들을 수정하였으면 TRUE가 반환된다.
SQL%NOTFOUND        마지막 SQL문의 실행에서 수정한 행이 없으면 TRUE가 반환된다.
SQL%ROWCOUNT        마지막 문이 아닌 모든 SQL문의 실행으로 처리된 행의 총 수를 반환한다.
SQL%ISOPEN          항상 FALSE를 반환하고 사용되지 않아야 한다.
SQL%BULK_ROWCOUNT   FORALL을 통해 대응되는 각 SQL문에 의해 처리된 행의 수를 알려주는 컬렉션을 반환한다.
                    주의할 것은 %BULK_ROWCOUNT(i)이 0 이면 %FOUND와 %NOTFOUND는 각각 FALSE와 TRUE가 된다.
─────────────────────────────────────────────────────

─────────────────────────────────────────────────────

이름 설명

───────── ───────────────────────────────────────────

SQL%FOUND 마지막 SQL문의 실행에서 하나 또는 그 이상의 행들을 수정하였으면 TRUE가 반환된다.

SQL%NOTFOUND 마지막 SQL문의 실행에서 수정한 행이 없으면 TRUE가 반환된다.

SQL%ROWCOUNT 마지막 문이 아닌 모든 SQL문의 실행으로 처리된 행의 총 수를 반환한다.

SQL%ISOPEN 항상 FALSE를 반환하고 사용되지 않아야 한다.

SQL%BULK_ROWCOUNT FORALL을 통해 대응되는 각 SQL문에 의해 처리된 행의 수를 알려주는 컬렉션을 반환한다.

주의할 것은 %BULK_ROWCOUNT(i)이 0 이면 %FOUND와 %NOTFOUND는 각각 FALSE와 TRUE가 된다.

─────────────────────────────────────────────────────

[DATABASE/ORACLE] INSERT INTO ~ SELECT ~ FROM 명령 : 데이터 추가하기

■ INSERT INTO ~ SELECT ~ FROM 명령을 사용해 데이터를 추가하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 직원 테이블을 생성한다.
CREATE TABLE EMP
(
    EMPNO    NUMBER(4, 0)
   ,ENAME    VARCHAR2(10)
   ,JOB      VARCHAR2(9)
   ,MGR      NUMBER(4, 0)
   ,HIREDATE DATE
   ,SAL      NUMBER(7, 2)
   ,COMM     NUMBER(7, 2)
   ,DEPTNO   NUMBER(2, 0)
   ,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)
);

-- SCOTT의 직원 테이블 데이터를 직원 테이블에 추가한다.
INSERT INTO EMP
SELECT *
FROM   SCOTT.EMP;

COMMIT;

-- 직원 테이블을 생성한다.

CREATE TABLE EMP

(

EMPNO NUMBER(4, 0)

,ENAME VARCHAR2(10)

,JOB VARCHAR2(9)

,MGR NUMBER(4, 0)

,HIREDATE DATE

,SAL NUMBER(7, 2)

,COMM NUMBER(7, 2)

,DEPTNO NUMBER(2, 0)

,CONSTRAINT PK_EMP PRIMARY KEY (EMPNO)

);

-- SCOTT의 직원 테이블 데이터를 직원 테이블에 추가한다.

INSERT INTO EMP

SELECT *

FROM SCOTT.EMP;

COMMIT;

[DATABASE/ORACLE] 중첩 테이블 데이터 정렬해 출력하기

■ 중첩 테이블 데이터를 정렬해 출력하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 작가 중첩 테이블 타입을 생성한다.
CREATE TYPE AUTHOR_TABLE_TYPE IS TABLE OF VARCHAR2(100);

SET SERVEROUTPUT ON;

DECLARE
    -- 작가 중첩 테이블 타입의 변수를 정의하고 초기화 한다.
    V_AUTHOR_TABLE AUTHOR_TABLE_TYPE := AUTHOR_TABLE_TYPE
    (
        'Sheri S. Tepper'
       ,'Orson Scott Card'
       ,'Gene Wolfe'
    );
BEGIN
    -- 작가 중첩 테이블의 데이터를 정렬하고 출력한다.
    FOR AUTHOR_RECORD IN
    (
        SELECT COLUMN_VALUE AS NAME
        FROM TABLE(V_AUTHOR_TABLE)
        ORDER BY COLUMN_VALUE
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(AUTHOR_RECORD.NAME);
    END LOOP;
END;

/*
Gene Wolfe
Orson Scott Card
Sheri S. Tepper
*/

-- 작가 중첩 테이블 타입을 생성한다.

CREATE TYPE AUTHOR_TABLE_TYPE IS TABLE OF VARCHAR2(100);

SET SERVEROUTPUT ON;

DECLARE

-- 작가 중첩 테이블 타입의 변수를 정의하고 초기화 한다.

V_AUTHOR_TABLE AUTHOR_TABLE_TYPE := AUTHOR_TABLE_TYPE

(

'Sheri S. Tepper'

,'Orson Scott Card'

,'Gene Wolfe'

);

BEGIN

-- 작가 중첩 테이블의 데이터를 정렬하고 출력한다.

FOR AUTHOR_RECORD IN

(

SELECT COLUMN_VALUE AS NAME

FROM TABLE(V_AUTHOR_TABLE)

ORDER BY COLUMN_VALUE

)

LOOP

DBMS_OUTPUT.PUT_LINE(AUTHOR_RECORD.NAME);

END LOOP;

END;

Gene Wolfe

Orson Scott Card

Sheri S. Tepper

[DATABASE/ORACLE] 중첩 테이블과 테이블 데이터 병합하기

■ 중첩 테이블과 테이블 데이터를 병합하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 작가 중첩 테이블 타입을 생성한다.
CREATE TYPE AUTHOR_TABLE_TYPE IS TABLE OF VARCHAR2(100);

-- 작가 테이블을 생성한다.
CREATE TABLE AUTHOR (NAME VARCHAR2(100));

-- 작가 테이블에 데이터를 추가한다.
INSERT INTO AUTHOR VALUES('Robert Harris');
INSERT INTO AUTHOR VALUES('Tom Segev'    );
INSERT INTO AUTHOR VALUES('Toni Morrison');

SET SERVEROUTPUT ON;

DECLARE
    -- 작가 중첩 테이블 타입의 변수를 정의하고 초기화 한다.
    V_AUTHOR_TABLE AUTHOR_TABLE_TYPE := AUTHOR_TABLE_TYPE
    (
        'Sheri S. Tepper'
       ,'Orson Scott Card'
       ,'Gene Wolfe'
    );
BEGIN
    -- 작가 중첩 테이블의 데이터와 작가 테이블 데이터를 병합하고 출력한다.
    FOR AUTHOR_RECORD IN
    (
        SELECT COLUMN_VALUE AS NAME FROM TABLE(V_AUTHOR_TABLE)
        UNION
        SELECT NAME FROM AUTHOR
    )
    LOOP
        DBMS_OUTPUT.PUT_LINE(AUTHOR_RECORD.NAME);
    END LOOP;
END;

-- 작가 중첩 테이블 타입을 생성한다.

CREATE TYPE AUTHOR_TABLE_TYPE IS TABLE OF VARCHAR2(100);

-- 작가 테이블을 생성한다.

CREATE TABLE AUTHOR (NAME VARCHAR2(100));

-- 작가 테이블에 데이터를 추가한다.

INSERT INTO AUTHOR VALUES('Robert Harris');

INSERT INTO AUTHOR VALUES('Tom Segev' );

INSERT INTO AUTHOR VALUES('Toni Morrison');

SET SERVEROUTPUT ON;

DECLARE

-- 작가 중첩 테이블 타입의 변수를 정의하고 초기화 한다.

V_AUTHOR_TABLE AUTHOR_TABLE_TYPE := AUTHOR_TABLE_TYPE

(

'Sheri S. Tepper'

,'Orson Scott Card'

,'Gene Wolfe'

);

BEGIN

-- 작가 중첩 테이블의 데이터와 작가 테이블 데이터를 병합하고 출력한다.

FOR AUTHOR_RECORD IN

(

SELECT COLUMN_VALUE AS NAME FROM TABLE(V_AUTHOR_TABLE)

UNION

SELECT NAME FROM AUTHOR

)

LOOP

DBMS_OUTPUT.PUT_LINE(AUTHOR_RECORD.NAME);

END LOOP;

END;

[DATABASE/ORACLE] CAST 의사 함수 사용하기

■ CAST 의사 함수를 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 색상 연관 배열 타입을 생성한다.
CREATE TYPE COLOR_ARRAY_TYPE IS VARRAY(16) OF VARCHAR2(30);

-- 색상 모델 테이블을 생성한다.
CREATE TABLE COLOR_MODEL
(
    MODEL_TYPE       VARCHAR2(10)
   ,NAME_COLOR_ARRAY COLOR_ARRAY_TYPE
);

-- 색상 모델 테이블에 데이터를 추가한다.
INSERT INTO COLOR_MODEL VALUES ('RGB', COLOR_ARRAY_TYPE('RED', 'GREEN', 'BLUE'));

-- 색상 중첩 테이블 타입을 생성한다.
CREATE TYPE COLOR_TABLE_TYPE AS TABLE OF VARCHAR2(30);

-- CAST 의사 함수를 사용해 색상 연관 배열 컬럼을 색상 중첩 테이블 타입으로 변환해 해당 컬럼 데이터를 조회한다.
SELECT COLUMN_VALUE
FROM THE
(
    SELECT CAST(NAME_COLOR_ARRAY AS COLOR_TABLE_TYPE)
    FROM   COLOR_MODEL
    WHERE  MODEL_TYPE = 'RGB'
);

-- 색상 연관 배열 타입을 생성한다.

CREATE TYPE COLOR_ARRAY_TYPE IS VARRAY(16) OF VARCHAR2(30);

-- 색상 모델 테이블을 생성한다.

CREATE TABLE COLOR_MODEL

(

MODEL_TYPE VARCHAR2(10)

,NAME_COLOR_ARRAY COLOR_ARRAY_TYPE

);

-- 색상 모델 테이블에 데이터를 추가한다.

INSERT INTO COLOR_MODEL VALUES ('RGB', COLOR_ARRAY_TYPE('RED', 'GREEN', 'BLUE'));

-- 색상 중첩 테이블 타입을 생성한다.

CREATE TYPE COLOR_TABLE_TYPE AS TABLE OF VARCHAR2(30);

-- CAST 의사 함수를 사용해 색상 연관 배열 컬럼을 색상 중첩 테이블 타입으로 변환해 해당 컬럼 데이터를 조회한다.

SELECT COLUMN_VALUE

FROM THE

(

SELECT CAST(NAME_COLOR_ARRAY AS COLOR_TABLE_TYPE)

FROM COLOR_MODEL

WHERE MODEL_TYPE = 'RGB'

);