DATABASE Archives - 30 중 4 번째 페이지

[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'

);

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

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


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

-- 색상 모델 테이블을 생성한다.
CREATE TABLE COLOR_MODEL
(
    MODEL_TYPE       VARCHAR2(10)
   ,NAME_COLOR_TABLE COLOR_TABLE_TYPE
)
NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

-- 명칭 색상 테이블 컬럼에 'RED'가 있는 색상 모델 테이블 데이터를 조회한다.
SELECT *
FROM   COLOR_MODEL C
WHERE  'RED' IN (SELECT * FROM TABLE(c.NAME_COLOR_TABLE));

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

CREATE TYPE COLOR_TABLE_TYPE IS TABLE OF VARCHAR2(30);

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

CREATE TABLE COLOR_MODEL

(

MODEL_TYPE VARCHAR2(10)

,NAME_COLOR_TABLE COLOR_TABLE_TYPE

)

NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

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

-- 명칭 색상 테이블 컬럼에 'RED'가 있는 색상 모델 테이블 데이터를 조회한다.

SELECT *

FROM COLOR_MODEL C

WHERE 'RED' IN (SELECT * FROM TABLE(c.NAME_COLOR_TABLE));

[DATABASE/ORACLE] THE 의사 함수 : 중첩 테이블 컬럼 데이터 수정하기

■ THE 의사 함수를 사용해 중첩 테이블 컬럼의 데이터를 수정하는 방법을 보여준다. ▶ 예제 코드 (SQL)


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

-- 색상 모델 테이블을 생성한다.
CREATE TABLE COLOR_MODEL
(
    MODEL_TYPE       VARCHAR2(10)
   ,NAME_COLOR_TABLE COLOR_TABLE_TYPE
)
NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

-- 모델 타입이 'RGB'인 레코드의 명칭 색상 중첩 테이블 컬럼에서 'BLUE'인 데이터를 'BURGUNDY'로 변경한다.
UPDATE THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB')
SET    COLUMN_VALUE = 'BURGUNDY'
WHERE  COLUMN_VALUE = 'BLUE';

SELECT COLUMN_VALUE FROM THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB');

/*
RED
GREEN
BURGUNDY
*/

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

CREATE TYPE COLOR_TABLE_TYPE IS TABLE OF VARCHAR2(30);

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

CREATE TABLE COLOR_MODEL

(

MODEL_TYPE VARCHAR2(10)

,NAME_COLOR_TABLE COLOR_TABLE_TYPE

)

NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

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

-- 모델 타입이 'RGB'인 레코드의 명칭 색상 중첩 테이블 컬럼에서 'BLUE'인 데이터를 'BURGUNDY'로 변경한다.

UPDATE THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB')

SET COLUMN_VALUE = 'BURGUNDY'

WHERE COLUMN_VALUE = 'BLUE';

SELECT COLUMN_VALUE FROM THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB');

RED

GREEN

BURGUNDY

[DATABASE/ORACLE] THE 의사 함수 : 중첩 테이블 컬럼 데이터 삭제하기

■ THE 의사 함수를 사용해 중첩 테이블 컬럼의 데이터를 삭제하는 방법을 보여준다. ▶ 예제 코드 (SQL)


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

-- 색상 모델 테이블을 생성한다.
CREATE TABLE COLOR_MODEL
(
    MODEL_TYPE       VARCHAR2(10)
   ,NAME_COLOR_TABLE COLOR_TABLE_TYPE
)
NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

-- 모델 타입이 'BLUE'인 레코드의 명칭 색상 중첩 테이블 컬럼에서 'BLUE'인 데이터를 삭제한다.
DELETE THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB')
WHERE  COLUMN_VALUE = 'BLUE';

SELECT COLUMN_VALUE FROM THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB');

/*
RED
GREEN
*/

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

CREATE TYPE COLOR_TABLE_TYPE IS TABLE OF VARCHAR2(30);

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

CREATE TABLE COLOR_MODEL

(

MODEL_TYPE VARCHAR2(10)

,NAME_COLOR_TABLE COLOR_TABLE_TYPE

)

NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

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

-- 모델 타입이 'BLUE'인 레코드의 명칭 색상 중첩 테이블 컬럼에서 'BLUE'인 데이터를 삭제한다.

DELETE THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB')

WHERE COLUMN_VALUE = 'BLUE';

SELECT COLUMN_VALUE FROM THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB');

RED

GREEN

[DATABASE/ORACLE] THE 의사 함수 : 중첩 테이블 컬럼 데이터 추가하기

■ THE 의사 함수를 사용해 중첩 테이블 컬럼의 데이터를 추가하는 방법을 보여준다. ▶ 예제 코드 (SQL)


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

-- 색상 모델 테이블을 생성한다.
CREATE TABLE COLOR_MODEL
(
    MODEL_TYPE       VARCHAR2(10)
   ,NAME_COLOR_TABLE COLOR_TABLE_TYPE
)
NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

-- 모델 타입이 'RGB'인 레코드에서 명칭 색상 중첩 테이블 컬럼에 'EXTRA-COLOR' 데이터를 추가한다.
INSERT INTO THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB') VALUES ('EXTRA-COLOR');

SELECT COLUMN_VALUE FROM THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB');

/*
RED
GREEN
BLUE
EXTRA-COLOR
*/

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

CREATE TYPE COLOR_TABLE_TYPE IS TABLE OF VARCHAR2(30);

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

CREATE TABLE COLOR_MODEL

(

MODEL_TYPE VARCHAR2(10)

,NAME_COLOR_TABLE COLOR_TABLE_TYPE

)

NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

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

-- 모델 타입이 'RGB'인 레코드에서 명칭 색상 중첩 테이블 컬럼에 'EXTRA-COLOR' 데이터를 추가한다.

INSERT INTO THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB') VALUES ('EXTRA-COLOR');

SELECT COLUMN_VALUE FROM THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB');

RED

GREEN

BLUE

EXTRA-COLOR

[DATABASE/ORACLE] THE 의사 함수 : 중첩 테이블 컬럼 조회하기

■ THE 의사 함수를 사용해 중첩 테이블 컬럼을 조회하는 방법을 보여준다. ▶ 예제 코드 (SQL)


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

-- 색상 모델 테이블을 생성한다.
CREATE TABLE COLOR_MODEL
(
    MODEL_TYPE       VARCHAR2(10)
   ,NAME_COLOR_TABLE COLOR_TABLE_TYPE
)
NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

-- 모델 타입이 'RGB'인 레코드에서 명칭 색상 중첩 테이블 컬럼의 데이터를 조회한다.
SELECT VALUE(C) FROM THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB') C;

또는

SELECT COLUMN_VALUE FROM THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB');

/*
RED
GREEN
BLUE
*/

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

CREATE TYPE COLOR_TABLE_TYPE IS TABLE OF VARCHAR2(30);

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

CREATE TABLE COLOR_MODEL

(

MODEL_TYPE VARCHAR2(10)

,NAME_COLOR_TABLE COLOR_TABLE_TYPE

)

NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

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

-- 모델 타입이 'RGB'인 레코드에서 명칭 색상 중첩 테이블 컬럼의 데이터를 조회한다.

SELECT VALUE(C) FROM THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB') C;

또는

SELECT COLUMN_VALUE FROM THE(SELECT NAME_COLOR_TABLE FROM COLOR_MODEL WHERE MODEL_TYPE = 'RGB');

RED

GREEN

BLUE

[DATABASE/ORACLE] 객체 타입 사용하기

■ 객체 타입을 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


-- 애완동물 객체 타입을 생성한다.
CREATE TYPE PET_OBJECT_TYPE IS OBJECT
(
    TAG_NO INTEGER
   ,NAME   VARCHAR2(50)
);

SET SERVEROUTPUT ON;

DECLARE
    -- 애완동물 중첩 테이블 타입을 정의한다.
    TYPE PET_TABLE_TYPE IS TABLE OF PET_OBJECT_TYPE;

    -- 애완동물 중첩 테이블 변수를 정의한다.
    V_PET_TABLE PET_TABLE_TYPE := PET_TABLE_TYPE(PET_OBJECT_TYPE(1050, 'Sammy'), PET_OBJECT_TYPE(1075, 'Mercury'));
BEGIN
    FOR I IN V_PET_TABLE.FIRST..V_PET_TABLE.LAST
    LOOP
        DBMS_OUTPUT.put_line(V_PET_TABLE(I).NAME);
    END LOOP;
END;

-- 애완동물 객체 타입을 생성한다.

CREATE TYPE PET_OBJECT_TYPE IS OBJECT

(

TAG_NO INTEGER

,NAME VARCHAR2(50)

);

SET SERVEROUTPUT ON;

DECLARE

-- 애완동물 중첩 테이블 타입을 정의한다.

TYPE PET_TABLE_TYPE IS TABLE OF PET_OBJECT_TYPE;

-- 애완동물 중첩 테이블 변수를 정의한다.

V_PET_TABLE PET_TABLE_TYPE := PET_TABLE_TYPE(PET_OBJECT_TYPE(1050, 'Sammy'), PET_OBJECT_TYPE(1075, 'Mercury'));

BEGIN

FOR I IN V_PET_TABLE.FIRST..V_PET_TABLE.LAST

LOOP

DBMS_OUTPUT.put_line(V_PET_TABLE(I).NAME);

END LOOP;

END;

[DATABASE/ORACLE] 레코드 타입을 항목으로 갖는 연관 배열 사용하기

■ 레코드 타입을 항목으로 갖는 연관 배열을 사용하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SET SERVEROUTPUT ON;

DECLARE
    -- 직원 테이블을 조회하는 커서를 정의한다.
    CURSOR EMPLOYEE_CURSOR IS
        SELECT *
        FROM   SCOTT.EMP
        ORDER BY ENAME;

    -- 직원 커서의 레코드 타입을 항목으로 갖는 연관 배열 타입을 정의한다.
    TYPE EMPLOYEE_ARRAY_TYPE IS TABLE OF EMPLOYEE_CURSOR%ROWTYPE INDEX BY BINARY_INTEGER;

    -- 직원 연관 배열 타입의 변수를 정의한다.
    V_EMPLOYEE_ARRAY EMPLOYEE_ARRAY_TYPE;

    -- 연관 배열을 조작하는 인덱스 변수를 정의한다.
    V_CURRENT_INDEX BINARY_INTEGER;
    V_LAST_INDEX    BINARY_INTEGER;
BEGIN
    -- 직원 커서를 조회해 직원 연관 배열 데이터를 설정한다.
    FOR EMPLOYEE_RECORD IN EMPLOYEE_CURSOR
    LOOP
        V_EMPLOYEE_ARRAY(EMPLOYEE_RECORD.EMPNO) := EMPLOYEE_RECORD;
    END LOOP;

    -- 연관 배열 데이터를 출력한다.
    V_CURRENT_INDEX := V_EMPLOYEE_ARRAY.FIRST;
    V_LAST_INDEX    := V_EMPLOYEE_ARRAY.LAST;

    LOOP
        DBMS_OUTPUT.PUT_LINE(V_EMPLOYEE_ARRAY(V_CURRENT_INDEX).ENAME);

        EXIT WHEN V_CURRENT_INDEX >= V_LAST_INDEX;

        V_CURRENT_INDEX := V_EMPLOYEE_ARRAY.NEXT(V_CURRENT_INDEX);
    END LOOP;
END;

SET SERVEROUTPUT ON;

DECLARE

-- 직원 테이블을 조회하는 커서를 정의한다.

CURSOR EMPLOYEE_CURSOR IS

SELECT *

FROM SCOTT.EMP

ORDER BY ENAME;

-- 직원 커서의 레코드 타입을 항목으로 갖는 연관 배열 타입을 정의한다.

TYPE EMPLOYEE_ARRAY_TYPE IS TABLE OF EMPLOYEE_CURSOR%ROWTYPE INDEX BY BINARY_INTEGER;

-- 직원 연관 배열 타입의 변수를 정의한다.

V_EMPLOYEE_ARRAY EMPLOYEE_ARRAY_TYPE;

-- 연관 배열을 조작하는 인덱스 변수를 정의한다.

V_CURRENT_INDEX BINARY_INTEGER;

V_LAST_INDEX BINARY_INTEGER;

BEGIN

-- 직원 커서를 조회해 직원 연관 배열 데이터를 설정한다.

FOR EMPLOYEE_RECORD IN EMPLOYEE_CURSOR

LOOP

V_EMPLOYEE_ARRAY(EMPLOYEE_RECORD.EMPNO) := EMPLOYEE_RECORD;

END LOOP;

-- 연관 배열 데이터를 출력한다.

V_CURRENT_INDEX := V_EMPLOYEE_ARRAY.FIRST;

V_LAST_INDEX := V_EMPLOYEE_ARRAY.LAST;

LOOP

DBMS_OUTPUT.PUT_LINE(V_EMPLOYEE_ARRAY(V_CURRENT_INDEX).ENAME);

EXIT WHEN V_CURRENT_INDEX >= V_LAST_INDEX;

V_CURRENT_INDEX := V_EMPLOYEE_ARRAY.NEXT(V_CURRENT_INDEX);

END LOOP;

END;

[DATABASE/ORACLE] 중첩 테이블을 테이블 컬럼으로 저장하기

■ 중첩 테이블을 테이블 컬럼으로 저장하는 방법을 보여준다. ▶ 예제 코드 (SQL)


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

-- 색상 모델 테이블을 생성한다.
CREATE TABLE COLOR_MODEL
(
    MODEL_TYPE       VARCHAR2(10)
   ,NAME_COLOR_TABLE COLOR_TABLE_TYPE
)
NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

SET SERVEROUTPUT ON;

DECLARE
    -- 색상 중첩 테이블 변수를 정의한다.
    V_NAME_COLOR_TABLE COLOR_TABLE_TYPE;
BEGIN
    -- 색상 모델 테이블에서 모델 타입이 'RGB'인 레코드를 조회해 색상 중첩 테이블 변수에 설정한다.
    SELECT NAME_COLOR_TABLE INTO V_NAME_COLOR_TABLE
    FROM   COLOR_MODEL
    WHERE  MODEL_TYPE = 'RGB';

    FOR I IN 1..V_NAME_COLOR_TABLE.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(V_NAME_COLOR_TABLE(I));
    END LOOP;
END;

/*
REG
GREEN
BLUE
*/

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

CREATE TYPE COLOR_TABLE_TYPE IS TABLE OF VARCHAR2(30);

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

CREATE TABLE COLOR_MODEL

(

MODEL_TYPE VARCHAR2(10)

,NAME_COLOR_TABLE COLOR_TABLE_TYPE

)

NESTED TABLE NAME_COLOR_TABLE STORE AS NAME_COLOR;

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

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

SET SERVEROUTPUT ON;

DECLARE

-- 색상 중첩 테이블 변수를 정의한다.

V_NAME_COLOR_TABLE COLOR_TABLE_TYPE;

BEGIN

-- 색상 모델 테이블에서 모델 타입이 'RGB'인 레코드를 조회해 색상 중첩 테이블 변수에 설정한다.

SELECT NAME_COLOR_TABLE INTO V_NAME_COLOR_TABLE

FROM COLOR_MODEL

WHERE MODEL_TYPE = 'RGB';

FOR I IN 1..V_NAME_COLOR_TABLE.COUNT

LOOP

DBMS_OUTPUT.PUT_LINE(V_NAME_COLOR_TABLE(I));

END LOOP;

END;

REG

GREEN

BLUE

[DATABASE/ORACLE] VARRAY 타입 : LIMIT 함수를 사용해 최대 항목 수 구하기

■ VARRAY 타입의 LIMIT 함수를 사용해 최대 항목 수를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


CREATE TYPE NUMBER_ARRAY_TYPE IS VARRAY(100) OF NUMBER;

SET SERVEROUTPUT ON;

DECLARE
    V_NUMBER_ARRAY NUMBER_ARRAY_TYPE := NUMBER_ARRAY_TYPE(7369, 7499, 7521);
BEGIN
    DBMS_OUTPUT.PUT_LINE(V_NUMBER_ARRAY.LIMIT); -- 100
    DBMS_OUTPUT.PUT_LINE(V_NUMBER_ARRAY.COUNT); -- 3
END;

CREATE TYPE NUMBER_ARRAY_TYPE IS VARRAY(100) OF NUMBER;

SET SERVEROUTPUT ON;

DECLARE

V_NUMBER_ARRAY NUMBER_ARRAY_TYPE := NUMBER_ARRAY_TYPE(7369, 7499, 7521);

BEGIN

DBMS_OUTPUT.PUT_LINE(V_NUMBER_ARRAY.LIMIT); -- 100

DBMS_OUTPUT.PUT_LINE(V_NUMBER_ARRAY.COUNT); -- 3

END;

[DATABASE/ORACLE] 중첩 테이블 : TRIM 함수를 사용해 항목 제거하기

■ 중첩 테이블의 TRIM 함수를 사용해 항목을 제거하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SET SERVEROUTPUT ON;

DECLARE
    TYPE DATE_TABLE_TYPE IS TABLE OF DATE;

    V_BIRTHDAY_TABLE DATE_TABLE_TYPE := DATE_TABLE_TYPE('1958-09-23', '1986-10-01', '1990-03-02', '1993-06-01');
BEGIN
    V_BIRTHDAY_TABLE.EXTEND(3);

    V_BIRTHDAY_TABLE(5) := '2019-11-03';
    V_BIRTHDAY_TABLE(6) := '2019-11-04';
    V_BIRTHDAY_TABLE(7) := '2019-11-05';

    V_BIRTHDAY_TABLE.TRIM(2); -- 6번, 7번 항목을 제거한다.

    DBMS_OUTPUT.PUT_LINE(V_BIRTHDAY_TABLE.COUNT); -- 5
END;

SET SERVEROUTPUT ON;

DECLARE

TYPE DATE_TABLE_TYPE IS TABLE OF DATE;

V_BIRTHDAY_TABLE DATE_TABLE_TYPE := DATE_TABLE_TYPE('1958-09-23', '1986-10-01', '1990-03-02', '1993-06-01');

BEGIN

V_BIRTHDAY_TABLE.EXTEND(3);

V_BIRTHDAY_TABLE(5) := '2019-11-03';

V_BIRTHDAY_TABLE(6) := '2019-11-04';

V_BIRTHDAY_TABLE(7) := '2019-11-05';

V_BIRTHDAY_TABLE.TRIM(2); -- 6번, 7번 항목을 제거한다.

DBMS_OUTPUT.PUT_LINE(V_BIRTHDAY_TABLE.COUNT); -- 5

END;

[DATABASE/ORACLE] 중첩 테이블 : NEXT 함수를 사용해 다음 항목의 인덱스 구하기

■ 중첩 테이블의 NEXT 함수를 사용해 다음 항목의 인덱스를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SET SERVEROUTPUT ON;

DECLARE
    TYPE DATE_TABLE_TYPE IS TABLE OF DATE;

    V_BIRTHDAY_TABLE DATE_TABLE_TYPE := DATE_TABLE_TYPE('1958-09-23', '1986-10-01', '1990-03-02', '1993-06-01');
BEGIN
    V_BIRTHDAY_TABLE.EXTEND(3);

    V_BIRTHDAY_TABLE(5) := '2019-11-03';
    V_BIRTHDAY_TABLE(6) := '2019-11-04';
    V_BIRTHDAY_TABLE(7) := '2019-11-05';

    DBMS_OUTPUT.PUT_LINE(V_BIRTHDAY_TABLE.NEXT(V_BIRTHDAY_TABLE.FIRST)); -- 2
END;

SET SERVEROUTPUT ON;

DECLARE

TYPE DATE_TABLE_TYPE IS TABLE OF DATE;

V_BIRTHDAY_TABLE DATE_TABLE_TYPE := DATE_TABLE_TYPE('1958-09-23', '1986-10-01', '1990-03-02', '1993-06-01');

BEGIN

V_BIRTHDAY_TABLE.EXTEND(3);

V_BIRTHDAY_TABLE(5) := '2019-11-03';

V_BIRTHDAY_TABLE(6) := '2019-11-04';

V_BIRTHDAY_TABLE(7) := '2019-11-05';

DBMS_OUTPUT.PUT_LINE(V_BIRTHDAY_TABLE.NEXT(V_BIRTHDAY_TABLE.FIRST)); -- 2

END;

[DATABASE/ORACLE] 중첩 테이블 : PRIOR 함수를 사용해 이전 항목의 인덱스 구하기

■ 중첩 테이블의 PRIOR 함수를 사용해 이전 항목의 인덱스를 구하는 방법을 보여준다. ▶ 예제 코드 (SQL)


SET SERVEROUTPUT ON;

DECLARE
    TYPE DATE_TABLE_TYPE IS TABLE OF DATE;

    V_BIRTHDAY_TABLE DATE_TABLE_TYPE := DATE_TABLE_TYPE('1958-09-23', '1986-10-01', '1990-03-02', '1993-06-01');
BEGIN
    V_BIRTHDAY_TABLE.EXTEND(3);

    V_BIRTHDAY_TABLE(5) := '2019-11-03';
    V_BIRTHDAY_TABLE(6) := '2019-11-04';
    V_BIRTHDAY_TABLE(7) := '2019-11-05';

    DBMS_OUTPUT.PUT_LINE(V_BIRTHDAY_TABLE.PRIOR(V_BIRTHDAY_TABLE.LAST)); -- 6
END;

SET SERVEROUTPUT ON;

DECLARE

TYPE DATE_TABLE_TYPE IS TABLE OF DATE;

V_BIRTHDAY_TABLE DATE_TABLE_TYPE := DATE_TABLE_TYPE('1958-09-23', '1986-10-01', '1990-03-02', '1993-06-01');

BEGIN

V_BIRTHDAY_TABLE.EXTEND(3);

V_BIRTHDAY_TABLE(5) := '2019-11-03';

V_BIRTHDAY_TABLE(6) := '2019-11-04';

V_BIRTHDAY_TABLE(7) := '2019-11-05';

DBMS_OUTPUT.PUT_LINE(V_BIRTHDAY_TABLE.PRIOR(V_BIRTHDAY_TABLE.LAST)); -- 6

END;