[Oracle] PL/SQL 구조

※ 모든 예제는 Text편집기에서 확장자 *.sql 파일로 만들어 실행한다.

■ PL/SQL 이란 ?
 -. PL/SQL 은 Oracle's Procedural Language extension to SQL. 의 약자 이다.
 -. SQL 문장에서 변수정의, 조건처리(IF), 반복처리(LOOP, WHILE, FOR)등을 지원하며, 오라클 자체에 내장되어 있는 Procedure Language 이다.
 -. DECLARE 문을 이용하여 정의되며, 선언문의 사용은 선택 사항이다.
 -. PL/SQL 문은 블록 구조로 되어 있고 PL/SQL 자신이 컴파일 엔진을 가지고 이다.

■ PL/SQL 의 장점
 -. PL/SQL 문은 BLOCK 구조로 다수의 SQL 문을 한번에 ORACLE DB 로 보내서 처리하므로 수행속도를 향상 시킬 수 있다.
 -. PL/SQL 의 모든 요소는 하나 또는 두개이상의 블록으로 구성하여 모듈화가 가능하다.
 -. 보다 강력한 프로그램을 작성하기 위해서 큰 블록안에 소블럭을 위치시킬 수 있다.
 -. Variable, Constant, Cursor, Exception 을 정의하고, SQL 문장과 Procedural 문장에서 사용하다.
 -. 단순, 복잡한 데이터형태의 변수를 선언한다.
 -. 테이블의 데이터 구조와 DataBase 의 컬럼럼에 준하여 동적으로 변수를 선언 할 수 있다.
 -. Exception 처리 루틴을 이용하여 Oracle Server Error 를 처리한다.
 -. 사용자 정의 에러를 선언하고 Exception 처리 루틴으로 처리 가능 하다.

■ PL/SQL Block Structure
 -. PL/SQL 은 프로그램을 논리적인 블록으로 나누는 구조화된 블록 언어 입니다.
 -. PL/SQL 블록은 선언부(선택적), 실행부(필수적),예외 처리부(선택적)로 구성되어 있고, BEGIN 과 END 키워드는 반드시 기술해 주어야 한다.
 -. PL/SQL 블록에서 사용하는 변수는 블록에 대해 논리적으로 선언할 수 있고 사용할 수 있습니다.

■ PL/SQL 구조

 -. PL/SQL은 PASCAR과 유사한 구조로 선언부(Declarative Section) → 실행부(Executable Section) → 예외 처리부(Exception Handling Section) 순서를 갖는다.

 -. PL/SQL은 다음과 같은 블로(BLOCK)구조의 언어로서 크게 3부분으로 나눌 수 있다.

 01) 선언부(DECLARE)

  -. PL/SQL에서 사용하는 모든 변수나 상수를 선언하는 부분으로서 DECLARE로 시작한다.

 02) 실행부(BEGIN)

  -. 절차적 형식으로 SQL문을 실행할 수 있도록 절차적 언어의 요소인 제어문, 반복문, 함수 정의 등

    로직을 기술 할 수 있으는 부분으로 BEGIN으로 시작한다.

 03) ERROR 처리를 할 수 있다.(예외처리)

  -. PL/SQL문이 실행되는 중에 에러가 발생할 수 있는데 이를 예외 사항이라고 한다.

  -. 이러한 예외사항이 발생했을 때 이를 해결하기 위한 문장으로 구성된다.

① 사원 번호가 7788인 사번과 사원의 이름을 얻어 와서 변수에 저장하는 예제

ex01.sql

CLARE     vempno    NUMBER(4);     vename    VARCHAR2(20);     BEGIN     SELECT empno, ename INTO vempno, vename     FROM emp     WHERE empno = 7788;     END; /

풀이

※ PL/SQL은 실행된 결괄르 출력하는 기능이 없으므로 일단 불러온 값들을 변수에 저장해야 한다.     (Compiler를 Code 형태로 생성한다.)

② 터미널을 열고 ex01.sql 실행하기

SQL> sqlplus scott/tiger

SQL> @C:\SQLWork\ex01.sql

출력화면

-. PL/SQL 자체는 산출된 내용을 화면에 출력하는 기능이 없다. 그래서 두 변수 vempno, vename에 어떤 값이 저장되었는지 확인할 수 없다.

-. 오라클에서는 PL/SQL에서 검색 내용을 화면에 출력하기 위해 dbms_output 이라는 패키지를 제공한다.

※ dbms_output 패키지에 정의된 프로시저

SQL> DESC dbms_output;

③ dbms_output으로 결과 출력하기

ex01.sql

DECLARE     vempno    NUMBER(4);     vename    VARCHAR2(20);     BEGIN     SELECT empno, ename INTO vempno, vename     FROM emp     WHERE empno = 7788;         dbms_output.put_line('***** 실 행 결 과 *****');     dbms_output.put_line(TO_CHAR(vempno) ||'        '|| vename);     END; /

출력화면

SQL> SET SERVEROUTPUT ON

SQL> @C:\SQLWork\ex01.sql

■ 변수 선언과 대입문

 -. PL/SQL의 선언부에서는 실행부에서 사용할 변수를 선언한다.

 -. 변수를 선언할 때 변수명 다음에 자료형을 기술해야 한다.

 -. PL/SQL에서 변수 선언할 때 사용되는 자료형은 SQL에서 사용하던 자료형과 거의 유사

 -. 아래와 같이 선언한 변수를 스칼라(SCALAR)변수라고 한다.(PL/SQL에서 사용되는 대부분의 변수가 스칼라 변수이다.)

DECLARE      vempno    NUMBER(4);      vename    VARCHAR2(20);

 -. 오라클에서는 해당 테이블의 해당 컬럼의 자료형을 그대로 참조해서 정의할 수 있는 레퍼런스(REFFERENCES) 변수를 제공한다.

    레퍼런스 형으로는 %TYPE과 %ROWTYPE형이 있다.

01) %TYPE형

 -. %TYPE형은 테이블에서 단 한개 컬럼의 데이터타입 및 사이즈를 참조한다.

 -. %TYPE 앞에 어떤 테이블의 어떤 컬럼의 자료형을 참조할 것인지를 지정

vdeptno    emp.deptno%TYPE;                  ①     ②

-. %TYPE을 사용하여 변수를 선언한느 경우에는 따로 데이터 형을 언급하지 않아도 테이블(①)내의 컬럼(②) 형을 정의할 때 사용한 자료형을 따른다.

 -. 테이블의 컬럼 자료형이 바뀌더라도 PL/SQL에서 자료형을 수정할 필요가 없다는 장점이 있다.

02) %ROWTYPE

vemp    emp%ROWTYPE;

 -. emp테이블의 ROW 타입(empno, ename, job, mgr, hiredate, sal, comm, deptno)과 같다는 의미

 -. emp테이블 안에 들어있는 8개의 컬럼 및 사이즈를 참조한 8개의 칸으로 나눠진 한 줄의 변수라고 생각 할 수 있다.

① %ROWTYPE 예제

ex02.sql

DECLARE     vemp    emp%ROWTYPE;     BEGIN     SELECT * INTO vemp     FROM emp     WHERE empno = 7788;         dbms_output.put_line('사번    이름    직업    고용일');     dbms_output.put_line(vemp.empno||'    '||vemp.ename||'    '||vemp.job||'    '||vemp.hiredate);     END; /

출력화면

SQL> SET SERVEROUTPUT ON

SQL> @C:\SQLWork\ex02.sql

■ 선택문

01) IF-THEN-END IF

 -. IF절 다음에는 비교 대상과 조건을 기술한다.

 -. 조건에 만족할 경우에는 THEN 이후의 문장이 수행된다.

 -. IF문이 끝났을 때에는 반드시 END IF를 기술해야 한다.

① 사원 번호가 7788인 사원의 부서 번호를 얻어 와서 부서 번호에 따른 부서명을 구하는 예제

ex03.sql

DECLARE     vempno    NUMBER(4);     vename    VARCHAR2(20);     vdeptno    emp.deptno%TYPE;     vdname    VARCHAR2(20) := NULL;     BEGIN     SELECT empno, ename, deptno INTO vempno, vename, vdeptno     FROM emp     WHERE empno = 7788;         IF (vdeptno = 10) THEN         vdname := 'ACCOUNTING';     END IF;     IF (vdeptno = 20) THEN         vdname := 'RESEARCH';     END IF;     IF (vdeptno = 30) THEN         vdname := 'SALES';     END IF;     IF (vdeptno = 40) THEN         vdname := 'OPERATIONS';     END IF;         dbms_output.put_line('사번    이름    부서명');     dbms_output.put_line(TO_CHAR(vempno)||'    '||vename||'    '||vdname);     END; /

출력화면

SQL> SET SERVEROUTPUT ON

SQL> @C:\SQLWork\ex03.sql

 -. PL/SQL에서는 변수에 값을 할당하기 위해서 := 연산자를 사용한다.

vdname  :=  'ACCOUNTING';

 -. 변수를 선언할 때에도 연산자를 이용해서 변수에 기본값을 정의할 수 있다.

vdname VARCHAR2(20)  :=  NULL;

02) IF-THEN-ELSIF-END IF

 -. 앞서 IF절에서 제시한 조건에 만족하지 않는 경우 또 다른 조건을 제시하기 위해서 ELSIF절을 사용한다.

ex04.sql

DECLARE     vempno    NUMBER(4);     vename    VARCHAR2(20);     vdeptno      emp.deptno%TYPE;     vdname    VARCHAR2(20) := NULL;    BEGIN     SELECT empno, ename, deptno INTO vempno, vename, vdeptno     FROM emp     WHERE empno = 7788;        IF (vdeptno = 10) THEN         vdname := 'ACCOUNTING';     ELSIF (vdeptno = 20) THEN         vdname := 'RESEARCH';     ELSIF (vdeptno = 30) THEN         vdname := 'SALES';     ELSIF (vdeptno = 40) THEN         vdname := 'OPERATIONS';     END IF;        dbms_output.put_line('사번    이름    부서명');     dbms_output.put_line(TO_CHAR(vempno)||'    '||vename||'    '||vdname);    END; /

출력화면

SQL> SET SERVEROUTPUT ON

SQL> @C:\SQLWork\ex04.sql

03) IF-THEN-ELS-END IF

 -. IF절에서 제시한 조건에 만족하는 경우에는 THEN 이후의 문장이 수행되고 만족하지 못했을 경우에는 ELSE 이후의 문장이 수행된다.

ex05.sql

DECLARE     vempno    NUMBER(4);     vename    VARCHAR2(20);     vcomm      emp.deptno%TYPE;    BEGIN     SELECT empno, ename, comm INTO vempno, vename, vcomm     FROM emp     WHERE empno = 7788;        IF vcomm > 0 THEN         dbms_output.put_line(vename||'의 커미션은 '||TO_CHAR(vcomm)||'입니다.');     ELSE         dbms_output.put_line(vename||'의 커미션을 받지 않습니다.');     END IF;    END; /

출력화면

SQL> SET SERVEROUTPUT ON

SQL> @C:\SQLWork\ex05.sql

■ 반복문

 -. SQL 문을 반복적으로 여러 번 실행하고자 할 때 반복문을 사용한다.

 -. 반복문은 BASIC LOOP, FOR IN LOOP, WHILE LOOP문이 존재한다.

01) BASIC LOOP문

LOOP      처리문장;      EXIT WHEN 조건절;  END LOOP;

ex06.sql

SET SERVEROUTPUT ON DECLARE     VDAN    NUMBER(2) := 2;     I           NUMBER(2) DEFAULT 0; -- 변수 I에 초기값으로 0을 설정한다.     TOT      NUMBER := 0;     BEGIN     LOOP         I := I + 1;         TOT := VDAN * I;         DBMS_OUTPUT.PUT_LINE(VDAN ||' * '|| I ||' = '|| TOT);            EXIT WHEN I = 9;        END LOOP;        END; /

출력화면

SQL> @C:\SQLWork\ex06.sql

02) FOR IN LOOP문

FOR 변수 IN 최소값 .. 최대값 LOOP      처리문장  END LOOP;

ex07.sql

DECLARE     VDAN    NUMBER(2) := 2;     I           NUMBER(2) DEFAULT 0; -- 변수 I에 초기값으로 0을 설정한다.     TOT      NUMBER := 0;     BEGIN     FOR I IN 1..9 LOOP         TOT := VDAN * I;         DBMS_OUTPUT.PUT_LINE(VDAN ||' * '|| I ||' = '|| TOT);        END LOOP;        END; /

출력화면

SQL> @C:\SQLWork\ex07.sql

03) FOR IN LOOP문에서 REVERSE를 사용한 경우

 -. REVERSE를 사용하면 이 최소값 .. 최대값 역순으로 출력된다.

FOR 변수 IN REVERSE 최소값 .. 최대값 LOOP      처리문장  END LOOP;

ex08.sql

SET SERVEROUTPUT ON DECLARE     VDAN    NUMBER(2) := 2;     I           NUMBER(2) DEFAULT 0; -- 변수 I에 초기값으로 0을 설정한다.     TOT      NUMBER := 0;     BEGIN     FOR I IN REVERSE 1..9 LOOP         TOT := VDAN * I;         DBMS_OUTPUT.PUT_LINE(VDAN ||' * '|| I ||' = '|| TOT);        END LOOP;        END; /

출력화면

SQL> @C:\SQLWork\ex08.sql

04) WHILE LOOP문

 -. 조건이 거짓이 되면 LOOP문이 종료한다.

WHILE 조건절 LOOP      처리문장  END LOOP;

ex09.sql

SET SERVEROUTPUT ON DECLARE     VDAN    NUMBER(2) := 2;     I           NUMBER(2) DEFAULT 0; -- 변수 I에 초기값으로 0을 설정한다.     TOT      NUMBER := 0;     BEGIN     WHILE 9 > I LOOP         TOT := VDAN * I;         DBMS_OUTPUT.PUT_LINE(VDAN ||' * '|| I ||' = '|| TOT);         I := I + 1;        END LOOP;        END; /

출력화면

SQL> @C:\SQLWork\ex09.sql

05) 값을 입력받을 수 있는 ACCEPT문

ex10.sql

SET SERVEROUTPUT ON ACCEPT PDAN PROMPT '출력하고자 하는 단 입력 : '    -- 입력된 값은 PDAN에 입력된다. DECLARE     VDAN    NUMBER(2) := &PDAN;    -- 입력된 PDAN의 값을 초기값으로 할당한다.     I           NUMBER(2) DEFAULT 0;  -- 변수 I에 초기값으로 0을 설정한다.     TOT      NUMBER := 0; BEGIN     WHILE 9 > I LOOP         TOT := VDAN * I;         DBMS_OUTPUT.PUT_LINE(VDAN ||' * '|| I ||' = '|| TOT);         I := I + 1;        END LOOP;        END; /

출력화면

SQL> @C:\SQLWork\ex10.sql

■ 테이블에 저장

1) 원하는 단을 입력받아 구구단을 화면과 동시에 테이블에 저장

① 테이블 A를 생성한다.

SQL> CREATE TABLE A          (A1 NUMBER, A2 NUMBER, A3 NUMBER);

② 구조를 확인한다.

SQL> DESC A;

③ 구구단을 입력하는 PL/SQL 쿼리의 FOR문 내부에 테이블 A를 추가하기 위한 쿼리문 INSERT를 사용한다.

ex11.sql

ACCEPT PDAN PROMPT '출력하고자 하는 단 입력 : ' DECLARE     VDAN    NUMBER(2) := &PDAN;     I        NUMBER(2) DEFAULT 0;     TOT        NUMBER := 0;     BEGIN     FOR I IN 1..9 LOOP         TOT := VDAN * I;         DBMS_OUTPUT.PUT_LINE(VDAN ||' * '|| I ||' = '|| TOT);         INSERT INTO A         VALUES(VDAN, I, TOT);        END LOOP;        END; /

④ Oracle SQL Developer에서  ex11.sql 파일을 컴파일 한다.

SQL> SET SERVEROUTPUT ON;

SQL> @C:\SQLWork\ex11.sql;

■ 시퀀스 생성

    

1) 테이블 A에 시퀀스에서 자동 발생하는 숫자를 삽입한다.

① A에 시퀀스 값을 저장할 컬럼을 하나 추가한다.

SQL> ALTER TABLE A         ADD (a_no NUMBER);

table A이(가) 변경되었습니다.

② 구조확인

SQL> DESC A;

③ a_no 칼럼의 데이터 확인

SQL> SELECT * FROM A;

④ 시퀀스를 생성하여 자동으로 값을 부여한다.

SQL> CREATE SEQUENCE A_NO_SEQ          START WITH 1          INCREMENT BY 1          NOCYCLE          NOCACHE;

시퀀스가 생성되었습니다.

⑤ 생성된 시퀀스를 테이블 A에 추가한 컬럼인 a_no에 적용하여 자동으로 번호가 부여되도록 한다.

ex12.sql

ACCEPT PDAN PROMPT '출력하고자 하는 단 입력 : ' DECLARE     VDAN    NUMBER(2) := &PDAN;     I        NUMBER(2) DEFAULT 0;     TOT        NUMBER := 0;     BEGIN     FOR I IN 1..9 LOOP         TOT := VDAN * I;         DBMS_OUTPUT.PUT_LINE(VDAN ||' * '|| I ||' = '|| TOT);         INSERT INTO A         VALUES(VDAN, I, TOT, A_NO_SEQ.NEXTVAL);        END LOOP;        END; /

⑥ ex12.sql을 컴파일한다.

SQL> @C:\SQLWork\ex12.sql;

⑦ A테이블을 조회한다.

SQL> SELECT * FROM A          WHERE a_no IS NOT NULL;

■ PL/SQL 테이블과 레코드
 -. PL/SQL 테이블이란 컬럼의 자료형이 들어갈 연속된 공간으로 배열과 유사하다.

 -. 형태가 마치 약식의 테이블과 같아서 PL/SQL 테이블이라는 명칭을 부여 받게 된다.

    (PL/SQL 언어에서 사용되는 배열 변수를 의미한다.)

① 사원번호가 7788인 사원의 이름을 알아내서 테이블 형 변수에 저장한 후에 그 값을 출력한다.

ex13.sql

DECLARE     VENAME    VARCHAR2(20);      TYPE E1_TABLE_TYPE IS TABLE OF     EMP.ENAME%TYPE     INDEX BY BINARY_INTEGER;         TAB1    E1_TABLE_TYPE;     BEGIN     SELECT ename INTO vename     FROM emp     WHERE empno = 7788;         TAB1(0) := VENAME;     dbms_output.put_line(TAB1(0));        END; /

풀이

출력화면

SQL> SET SERVEROUTPUT ON;

SQL> @C:\SQLWork\ex13.sql;

② 부서 번호가 10인 부서의 번호와 부서명과 지역명을 알아내서 레코드 형 변수에 저장한 후에 그 값을 출력

ex14.sql

DECLARE     TYPE    D1_RECORD_TYPE IS RECORD     (         deptno    NUMBER(2),         dname    VARCHAR2(14),         loc        VARCHAR2(13)     );         REC1 D1_RECORD_TYPE;     BEGIN     SELECT * INTO REC1     FROM dept     WHERE deptno = 10;         dbms_output.put_line('번호    부서명    지역');     dbms_output.put_line(TO_CHAR(REC1.deptno)||'    '||REC1.dname||'    '||REC1.loc);        END; /

출력화면

SQL> SET SERVEROUTPUT ON;

SQL> @C:\SQLWork\ex13.sql;