PL/SQL 대입문: SELECT INTO, TABLE TYPE, RECORD TYPE

PL/SQL (Oracle's Procedural Language extension to SQL)

SQL 문장에서 변수 정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR) 등을 지원. 오라클 자체에 내장되어 있는 절차적 언어.

1. PL/SQL의 구조

1) 선언부(DECLARE SECTION)

: 변수나 상수를 선언. DECLARE로 시작

2) 실행부(EXECUTABLE SECTION)

: 절차적 형식으로 SQL문을 실행할 수 있도록 제어문, 반복문, 함수정의 등 로직을 기술할 수 있는 부분. BEGIN으로 시작.

3) 예외처리부(EXCEPTION SECTION)

: 에러가 발생할 수 있는 예외사항이 발생했을 때 이를 해결하기 위한 문장을 기술할 수 있는 부분. EXCEPTION 으로 시작.

예제)

SET SERVEROUTPUT ON -- SERVEROUTPUT : 출력하는 내용이 화면에 보여주도록 설정하는 환경 변수

BEGIN 

  DBMS_OUTPUT.PUT_LINE('Hello Oracle'); -- DBMS_OUTPUT.PUT_LINE: 화면 출력 프로시저

END;

/

결과)

PL/SQL 프로시저가 성공적으로 완료되었습니다.

Hello Oracle

2. 변수 선언과 대입문

변수를 사용하려면 선언부에서 선언해둬야 하고, 값을 지정하려면 :=을 사용한다.

[형식]

identifier [CONSTANT] datatype [NOT NULL]

[ := | DEFAULT expression]; 

identifier : 변수명

CONSTANT : 변수값을 변경할 수 없도록 제약

datatype : 자료형

NOT NULL : 값을 반드시 포함하도록 변수를 제약

expression : Literal, 다른 변수, 연산자나 함수를 포함하는 표현식

1) 변수 선언과 값 지정

변수선언 (스칼라 변수)

VEMPNO NUMBER(4);

VENAME VARCHAR2(10);

변수에 값 지정

VEMPNO := 7788;

VENAME := 'SCOTT';

예제) 변수 선언하고 출력하기

DECLARE

  VEMPNO NUMBER(4);

  VENAME VARCHAR2(10);

BEGIN

  VEMPNO := 7788;

  VENAME := 'SCOTT';

  DBMS_OUTPUT.PUT_LINE('사번/이름');

  DBMS_OUTPUT.PUT_LINE('----------------------');

  DBMS_OUTPUT.PUT_LINE(VEMPNO||'/'||VENAME);

END;

결과)

사번/이름

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

7788/SCOTT

2) 스칼라 변수 / 레퍼런스 변수

- 스칼라 변수

위와 같이 SQL 자료형과 마찬가지로 숫자나 문자를 저장.

VEMPNO NUMBER(4);

VENAME VARCHAR2(10);

- 레퍼런스 변수

이전에 선언된 다른 변수나 데이터 베이스 컬럼에 맞춰 변수를 선언. %TYPE 속성을 사용한다.

VEMPNO EMP.EMPNO%TYPE;

VENAME  EMP.ENAME%TYPE;

3) PL/SQL에서 SELECT INTO문

[형식]

SELECT select_list

INTO {variable_name1[,variable_name2] | record_name}

FROM table_name

WHERE condition;

예제) SELECT INTO문으로 EMP 테이블에서 사번과 이름 조회하기

DECLARE

  VEMPNO EMP.EMPNO%TYPE;

  VENAME  EMP.ENAME%TYPE;

BEGIN

  DBMS_OUTPUT.PUT_LINE('사번/이름');

  DBMS_OUTPUT.PUT_LINE('---------------');

  SELECT EMPNO, ENAME

  INTO VEMPNO, VENAME

  FROM EMP

  WHERE ENAME = 'SCOTT';  

  DBMS_OUTPUT.PUT_LINE(VEMPNO||'/'||VENAME);

END;

결과)

사번/이름

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

7788/SCOTT

3) PL/SQL 테이블 TYPE

배열처럼 사용하고자 할 경우에 사용

[형식]

TYPE table_name IS TABLE OF

{column_type | variable%TYPE | table.column%TYPE} [NOT NULL]

[INDEX BY BINARY_INTEGER];

indentifier table_type_name;

예제) TABLE 변수를 사용하여 EMP 테이블에서 이름과 업무 출력하기

DECLARE

-- 테이블 타입 정의

  TYPE ENAME_TABLE_TYPE IS TABLE OF EMP.ENAME%TYPE

  INDEX BY BINARY_INTEGER;

  TYPE JOB_TABLE_TYPE IS TABLE OF EMP.JOB%TYPE

  INDEX BY BINARY_INTEGER;

-- 테이블 변수 선언

  ENAME_TABLE ENAME_TABLE_TYPE;

  JOB_TABLE JOB_TABLE_TYPE;  

  

  I BINARY_INTEGER := 0;

BEGIN

  FOR K IN (SELECT ENAME, JOB FROM EMP) LOOP

    I := I + 1;

    ENAME_TABLE(I) := K.ENAME;

    JOB_TABLE(I) := K.JOB;

  END LOOP;

  

   FOR J IN 1..I LOOP

     DBMS_OUTPUT.PUT_LINE(RPAD(ENAME_TABLE(J),12)

     ||'/'||RPAD(JOB_TABLE(J),9));

   END LOOP;

END;

/

결과)

PL/SQL 프로시저가 성공적으로 완료되었습니다.

KING        /PRESIDENT

CLARK       /MANAGER  

JONES       /MANAGER  

FORD        /ANALYST  

SMITH       /CLERK    

SCOTT       /ANALYST  

ADAMS       /CLERK    

MILLER      /CLERK    

AAAA        /

  

  

4) PL/SQL RECORD TYPE

여러 개의 필드르 묶어서하나의 레코드 타입으로 선언

[형식]

TYPE type_name IS RECORD

field_name1 {scalar_datatype|record_type}

[NOT NULL] [{:= | DEFAULT} expr],

field_name2 {scalar_datatype|record_type}

[NOT NULL] [{:= | DEFAULT} expr],

...);

indentifier table_type_name;

예제) 레코트 타입을 사용하여 EMP 테이블에서 SCOTT 사원의 정보 출력하기

DECLARE

-- 레코드 타입을 정의

  TYPE EMP_RECORD_TYPE IS RECORD(

    V_EMPNO EMP.EMPNO%TYPE,

    V_ENAME EMP.ENAME%TYPE,

    V_JOB EMP.JOB%TYPE,

    V_DEPTNO EMP.DEPTNO%TYPE);

-- 레코드로 변수 선언

  EMP_RECORD EMP_RECORD_TYPE;

BEGIN

-- SCOTT 사원의 정보를 레코드 변수에 저장

SELECT EMPNO, ENAME, JOB, DEPTNO

INTO EMP_RECORD

FROM EMP

WHERE ENAME = UPPER('SCOTT');

-- 레코드 변수에 저장된 사원 정보를 출력

DBMS_OUTPUT.PUT_LINE('사원번호 : ' || TO_CHAR(EMP_RECORD.V_EMPNO));

DBMS_OUTPUT.PUT_LINE('사원명 : ' || TO_CHAR(EMP_RECORD.V_ENAME));

DBMS_OUTPUT.PUT_LINE('직책 : ' || TO_CHAR(EMP_RECORD.V_JOB));

DBMS_OUTPUT.PUT_LINE('부서번호 : ' || TO_CHAR(EMP_RECORD.V_DEPTNO));

END;

/

결과)

사원번호 : 7788

사원명 : SCOTT

직책 : ANALYST

부서번호 : 20