[SIST] Oracle_days15

1. PL/SQL

1) SQL 확장 + PL == [P]rocedural [L]anguage extensions to SQL을 의미

2) 이는 비절차적인 SQL 언어를 '절차적'으로 사용할 수 있도록 한다.

    (1) 변수 선언

    (2) 제어문

    (3) 예외 처리 등등

 

3) PL/SQL은 블록 구조로 된 언어이며, 3가지의 블럭으로 구조되어있다.

   [ 선언 기능 블럭 ]

   [ 실행 기능 블럭 ]

   [ 예외 처리 블럭 ]

 

4) PL/SQL 선언 형식 ***

【형식】
[ DECLARE ]         -- 선언블럭(declarations) : 변수 선언, 상수 선언
BEGIN                 -- 실행블럭(statements) : INSERT, DELETE, UPDATE 등
[ EXCEPTION ]      -- 예외 처리 블럭(handlers)

END; 

 

5) PL/SQL의 블록 작성요령

(1) 블럭 내에 SQL문을 여러 번 사용할 수 있다. ***

(2) 블럭 내에는 CREATEST, LEAST, DECODE, 그룹함수를 사용할 수 없다. *** 

(3) 식별자는 최대 30문자로 작성

(4) 식별자는 테이블 또는 컬럼명과 같을 수 없음

(5) 식별자는 알파벳으로 시작해야 함

(6) 문자와 날짜 타입은 단일 인용부호로 표시함

(7) 주석은 단일 라인인 경우 2개의 대시(--), 여러 라인 경우 /* ... */로 표기

 

6) PL/SQL의 6가지 종류

(1) 익명 프로시저(anonymous procedure)

- DECLARE ...로 시작되며, 사용자가 반복적으로 실행하려는 SQL문을 필요할 때마다 작성하여 실행하는 방법, 데이터베이스에 그 정보가 저장되지 않음

 

(2) 저장 프로시저(stored procedure)

- CREATE PROCEDURE name ...문에 의해서 생성된 후, 데이터베이스 내에 그 정보가 저장됨. stored procedure는 로직을 처리만 하고 끝남 

 

(3) 저장 함수(stored function)

- stored procedure와 같으며, stored procedure는 로직을 처리만 하고 끝나지만, stored function은 그 처리 결과를 사용자에게 반환함. 

 

(4) 패키지(package)

- 자주 사용되는 여러 procedure, function들을 하나의 package묶어서 관리에 편리토록 함  -> ex) dbms_random 패키지

 

(5) 트리거(trigger)

- 어떤 작업전, 또는 작업 후 trigger에 정의한 로직을 실행시키는 PL/SQL 블럭임.

 

(6) 객체 타입(object type)

- 객체에 데이터를 입력, 수정, 삭제, 조회하기 위해서는 반드시 PL/SQL 언어를 사용해야 함

 

익명 프로시저와 저장 프로시저에 대해서만 배웠기 때문에 2가지에 대해서 정리를 할 것이며, EXCEPTION에 대해서는 아직 배우지 않아 주석처리를 하여 쿼리를 실행하였다.

---

2. 익명 프로시저(anonymous procedure)

Oracle SQL Developer 프로그램을 실행시킨 후 상단에 보기 탭에 DBMS출력을 누르면 에디터가 나오게 되는데 + 모양을 눌러 접속할 계정을 선택해주면 사용가능하다.

 

SCOTT 계정에서 아래와 같은 익명프로시저를 작성하고 실행시켜보면 위 그림과 같은 결과가 나온다.

 

       DECLARE
        vname VARCHAR2(10);
        vsal NUMBER(7, 2);
       BEGIN
        SELECT ename, sal
                INTO vname, vsal  -- SELECT한 결과를 DECLATE에 선언된 변수에 담겠다.
        FROM emp
        WHERE empno = 7369;

        DBMS_OUTPUT.PUT_LINE(vname);
        DBMS_OUTPUT.PUT_LINE(vsal);
       -- EXCEPTION
       -- WHEN THEN
       END;

 

DECLARE에서 선언한 변수를 이용하여

BEGIN에서 SELECT한 데이터를 선언한 변수에 담아서 출력하는 것!

---

[참고] DBMS_OUTPUT 패키지

- 이 패키지는 PL/SQL 내에서 처리된 어떤 결과를 사용자의 화면에 출력할 때 사용한다.

 

- DBMS_OUTPUT 패키지의 서브프로그램(함수)은 다음과 같다.
> PUT() 또는 PUT_LINE() : 정의된 문자 값을 화면에 출력하는 프로세서 
> NEW_LINE() : GET_LINE에 의해 읽힌 행의 다음 라인을 읽을 때 사용 
> GET_LINE() 또는 GET_LINES() : 현재 라인의 문자값을 읽는 프로세서 
> ENABLE()    : 화면에 문자값을 출력하는 모드로 설정하며 문자값을 지정할 수 있는 버퍼크기를 정의함 
> DISABLE()   : 화면에 문자값을 출력하는 모드로 해제함 

---

예시1) 홍길동의 이름과 나이를 변수에 저장해서 DBMS로 출력하기
    DECLARE
        vname VARCHAR2(20);
        vage NUMBER(3);
    BEGIN
        vname := '홍길동';
        vage := 20;
        
        DBMS_OUTPUT.PUT_LINE(vname || ', ' || vage);
    -- EXCEPTION
    END;

 

여기서 vname := '홍길동'; 을 vname = '홍길동'; 이렇게 쿼리를 작성하면 에러가 발생하니 주의하자!

---

예시2) 30번 부서의 지역명(loc)를 가져와서 10번 부서의 loc로 수정

 

- 익명 프로시저 생성 + 실행
    DECLARE
        -- vloc VARCHAR2(13);
        vloc dept.loc%TYPE;  -- 타입형 변수(dept 테이블의 loc와 자료형을 똑같이 주겠다.)
    BEGIN
        SELECT loc INTO vloc
        FROM dept
        WHERE deptno = 30;
        
        UPDATE dept
        SET loc = vloc
        WHERE deptno = 10;
        
        -- COMMIT;
    -- EXCEPTION
    END;

---

예시3) 기본값 선언해보기

DECLARE
    vname VARCHAR2(20);
    vage NUMBER(3) := 20;  -- 여기서 선언하면 기본값
BEGIN
    vname := '익순이';
    vage := 28;                 -- BEGIN에서 값을 넣어주면 기본값이 아닌 이 값으로 출력
    DBMS_OUTPUT.PUT_LINE(vname || ', ' || vage);
--EXCEPTION
END;

---

3-1. %TYPE형 변수

[형식]

변수명 table명.column명%TYPE;

 

예시)

tempno emp.empno%TYPE;

- tempno 변수의 자료형을 emp 테이블의 empno 컬럼의 자료형을 사용하겠다.

 

예제)

DECLARE
    tempno emp.empno%TYPE;
    tdeptno emp.deptno%TYPE;
    tename emp.ename%TYPE;
    tjob emp.job%TYPE;
    tmgr emp.mgr%TYPE;
    thiredate emp.hiredate%TYPE;
    tpay emp.sal%TYPE;  

    vmax_pay NUMBER;

BEGIN 
    -- 1번 수행 *** 서브쿼리를 따로 빼와서 변수에 저장
    SELECT MAX(sal+NVL(comm,0)) max_pay
        INTO vmax_pay
    FROM emp
    WHERE deptno = 10;

    -- 2번 수행
    SELECT empno, deptno, ename, job, mgr, hiredate, sal + NVL(comm, 0) pay
         INTO tempno, tdeptno, tename, tjob, tmgr, thiredate, tpay
    FROM emp
    WHERE deptno = 10 AND sal + NVL(comm, 0) = vmax_pay;

    DBMS_OUTPUT.PUT_LINE(tempno || ', '|| tdeptno || ', '|| tename || ', '|| tjob || ', '|| tmgr || ', '|| thiredate || ', '|| tpay);
--EXCEPTION
END;

 

<결과>

 

- 수행할 때마다 서브쿼리가 있으면 성능이 떨어지기 때문에 변수를 하나 선언하여 서브쿼리를 따로 담을 수 있도록 한 예제이다.

---

3-2. %ROWTYPE형 변수

[형식]

변수명 table명%ROWTYPE;

 

예시)

vemprow emp%ROWTYPE;

- emp 테이블의 한 행(레코드) 전체를 저장할 변수(vemprow) 선언

 

예제)

DECLARE
    -- emp 테이블의 한 행(레코드) 전체를 저장할 변수 선언
 vemprow emp%ROWTYPE;
    vpay NUMBER;

    vmax_pay NUMBER;
BEGIN 
    -- 1번 수행 *** 서브쿼리를 따로 빼와서 변수에 저장
SELECT MAX(sal+NVL(comm,0)) max_pay INTO vmax_pay
    FROM emp
    WHERE deptno = 10;

    -- 2번 수행
 SELECT empno, deptno, ename, job, mgr, hiredate, sal + NVL(comm, 0) pay
         INTO vemprow.empno, vemprow.deptno, vemprow.ename, vemprow.job, vemprow.mgr, vemprow.hiredate, vpay
    FROM emp
    WHERE deptno = 10 AND sal + NVL(comm, 0) = vmax_pay;

    DBMS_OUTPUT.PUT_LINE(vemprow.empno || ', '|| vemprow.deptno || ', '|| vemprow.ename || ', '|| vemprow.job
                            || ', '|| vemprow.mgr || ', '|| vemprow.hiredate || ', '|| vpay);
-- EXCEPTION
END;

 

<결과>

 

<주의>

- PL/SQL 문 안에서 SELECT한 처리 결과가 여러 개의 행을 반환할 경우에는 아래와 같이 에러가 발생한다.

- 에러 : ORA-01422: exact fetch returns more than requested number of rows

- 에러 발생한 쿼리 : 

    DECLARE
        vename emp.ename%TYPE;
        vjob emp.job%TYPE;
    BEGIN
        SELECT ename, job -- 12명 사원 == 12 row (행)
            INTO vename, vjob
        FROM emp;
        --WHERE empno = 7369;
    --EXCEPTION
        DBMS_OUTPUT.PUT_LINE(vename || ',' || vjob);
    END;

 

- 해결방법 : 커서를 사용하면 가능하다. 후반부에 배웠기 때문에 하단 내용을 확인하면 해결할 수 있을 것!

---

3-3. RECORD

- 사용자가 정의하는 새로운 구조의 자료형 => '사용자 정의 구조체'

 

예시)

    DECLARE
        -- 사용자가 정의하는 새로운 구조의 자료형 => '사용자 정의 구조체'
        TYPE EmpDeptType IS RECORD -- 구조체 이름 정의
        (
            vdeptno dept.deptno%TYPE,
            vdname dept.dname%TYPE, -- 구분자는 ,(콤마)!!
            vempno emp.empno%TYPE,
            vename emp.ename%TYPE,
            vpay NUMBER
        );

    vrow EmpDeptType; -- RECORD형 변수 선언
    BEGIN
        SELECT d.deptno, dname, empno, ename, sal + NVL(comm, 0) pay
            INTO vrow.vdeptno, vrow.vdname, vrow.vempno, vrow.vename, vrow.vpay
        FROM emp e JOIN dept d ON e.deptno = d.deptno
        WHERE empno = 7369;

        DBMS_OUTPUT.PUT_LINE(vrow.vdeptno || ', ' ||  vrow.vdname || ', ' || vrow.vempno || ', ' || vrow.vename || ', ' || vrow.vpay);
    -- EXCEPTION
    END;

 

<결과>

---

4. CURSOR

1) CURSOR 란? PL/SQL 블럭 내에서 실행되는 SELECT문을 의미

 

2) 여러 개의 레코드를 처리하기위해서 커서(CURSOR)를 사용해야된다.

 

3) 커서의 2가지 종류

ㄱ. implicit cursor 묵시적(암시적, 자동) 커서

 

    예시)
        DECLARE
            -- vrow 선언X 왜? RECORD형 변수를 선언했기 때문에
        BEGIN
            FOR vrow IN (SELECT empno, ename, job FROM emp)
            LOOP
                DBMS_OUTPUT.PUT_LINE(vrow.empno || ', ' || vrow.ename || ', ' || vrow.job);
            END LOOP;
        --EXCEPTION
        END;

 

ㄴ. explicit cursor 명시적 커서

     (1) 커서 선언
     (2) 커서 OPEN
     (3) LOOP
            -- 커서로부터 SELECT한 것을 가져오는 코딩(FETCH)
            EXIT WHEN 커서가 읽을 것이 없을 때 까지의 조건 (%NOTFOUND 참이될 때 까지)
         END LOOP;

     (4) 커서 CLOSE

 

4) CURSOR 형식 및 CURSOR 속성

[커서선언형식]
    CURSOR 커서명 IS (서브쿼리);
    OPEN [커서명];
    FOR
     FETCH [커서명] INTO [변수명];
     EXIT WHEN [조건절];
    END LOOP;
    CLOSE [커서명];

 

[커서의 속성]
> %ROWCOUNT  실행된 커서문장에서 읽힌 행의 수 
> %FOUND  실행된 커서문장에서 검색된 행이 발견되었는지 알 수 있는 속성 
> %NOTFOUND  실행된 커서문장에서 검색된 행이 발견되지 않았음을 알 수 있는 속성 
> %ISOPEN  선언된 커서가 현재 OPEN되어 있는지를 반환 

 

5) 예시

방법1)

        DECLARE
            vename emp.ename%TYPE;
            vsal emp.sal%TYPE;
            vhiredate emp.hiredate%TYPE;

            -- 1) 커서 선언 : CURSOR 커서명 IS (서브쿼리);
            CURSOR emp30_cursor IS(
                                        SELECT ename, sal, hiredate
                                        FROM emp
                                        WHERE deptno = 30
                                );
        BEGIN

            --2) OPEN : OPEN 커서명;
            OPEN emp30_cursor;

            --3) LOOP ~ FETCH 작업(반복적으로 가져오는 작업)
            LOOP
                FETCH emp30_cursor INTO vename, vsal, vhiredate;
                DBMS_OUTPUT.PUT_LINE(vename || ', ' || vsal || ', ' || vhiredate );
                EXIT WHEN emp30_cursor%NOTFOUND;
            END LOOP;   

            --4) CLOSE : CLOSE 커서명;
            CLOSE emp30_cursor;

        -- EXCEPTION
        END;  

 

방법2) 

        DECLARE
            CURSOR emp_cursor IS( SELECT empno, ename, job FROM emp );
        BEGIN
            FOR vrow IN emp_cursor     
            LOOP
                DBMS_OUTPUT.PUT_LINE(vrow.ename || ', ' || vrow.ename  || ', ' || vrow.job );
            END LOOP;
            
        -- EXCEPTION
        END;   

 

방법3)

        DECLARE
            vename emp.ename%TYPE;
            vsal emp.sal%TYPE;
            vhiredate emp.hiredate%TYPE;

            -- 1) 커서 선언 : CURSOR 커서명 IS (서브쿼리);
            CURSOR emp30_cursor IS(
                                        SELECT ename, sal, hiredate
                                        FROM emp
                                        WHERE deptno = 30
                                );
        BEGIN
            --2) OPEN : OPEN 커서명;
            OPEN emp30_cursor;

            --3) LOOP ~ FETCH 작업(반복적으로 가져오는 작업)
            LOOP
                FETCH emp30_cursor INTO vename, vsal, vhiredate;
                DBMS_OUTPUT.PUT_LINE(vename || ', ' || vsal || ', ' || vhiredate );
                EXIT WHEN emp30_cursor%NOTFOUND OR emp30_cursor%ROWCOUNT >= 3;
            END LOOP;     

            --4) CLOSE : CLOSE 커서명;
            CLOSE emp30_cursor;

        -- EXCEPTION
        END;

 

<결과>

---

5. PL/SQL의 제어문

1) IF THEN ELSE 문

(1) IF문 형식

IF( 조건식 ) THEN

    -- 코딩
IF 조건식 THEN

    -- 코딩
END IF;

 

-> IF 조건식을 쓸 때 괄호를 써도 되고, 안써도 된다.

 

(2) IF ELSE문 형식

IF( 조건식 ) THEN
    -- 코딩
ELSE
    -- 코딩
END IF;

 

(3) ELSEIF문 형식

IF( 조건식 ) THEN

      -- 코딩
ELSIF( 조건식 ) THEN

      -- 코딩
ELSIF( 조건식 ) THEN

      -- 코딩
ELSIF( 조건식 ) THEN

      -- 코딩
ELSE 

      -- 코딩
END IF;

---

예시1) 변수를 하나 선언해서 정수를 입력받아서 짝수/홀수 출력

    DECLARE
        vnum NUMBER := 0;
        vresult VARCHAR2(20);
    BEGIN
        vnum :=  :bindNumber;         -- 변수를 입력받아서 대입하겠다.
        
        IF(mod(vnum, 2) = 0) THEN
            vresult := '짝수';
        ELSE
            vresult := '홀수';
        END IF;

        DBMS_OUTPUT.PUT_LINE(vresult);
    -- EXCEPTION
    END;

 

 

예시2) 국어 점수를 입력받아서 수우미양가라고 출력하는 익명 프로시저 작성

ELSEIF문 사용)

    DECLARE
        kor NUMBER(3) := 0;
        grade VARCHAR2(3) := '가';
    BEGIN
        kor := :bindNumber;
        
        IF kor >= 90 THEN
            grade := '수';
        ELSIF kor >= 80 THEN
            grade := '우';
        ELSIF kor >= 70 THEN
            grade := '미';
        ELSIF kor >= 60 THEN
            grade := '양';        
        ELSE
            grade := '가';
        END IF;
        
        DBMS_OUTPUT.PUT_LINE(grade);
    -- EXCEPTION
    END;

 

CASE문 사용)

    DECLARE
        kor NUMBER(3) := 0;
        grade VARCHAR2(3) := '가';
    BEGIN
        kor := :bindKor;
        kor := TRUNC(kor / 10);
        
        CASE kor
            WHEN 10 THEN grade := '수';
            WHEN 9 THEN grade := '수';
            WHEN 8 THEN grade := '우';
            WHEN 7 THEN grade := '미';
            WHEN 6 THEN grade := '양';
            ELSE grade := '가';
        END CASE;
        
        DBMS_OUTPUT.PUT_LINE(grade);
    -- EXCEPTION
    END;

---

2) FOR...LOOP 문(제한적 반복)

【형식】 
        FOR counter변수 IN [REVERSE] 시작값 .. 끝값
        LOOP 
          실행문; -- 반복처리할 코딩
        END LOOP; 

 

예시1) 1 ~ 10까지 합을 출력

    DECLARE
        vi NUMBER;
        vsum NUMBER := 0;
    BEGIN
        FOR vi IN 1..10
        LOOP
            vsum := vsum + vi;
            -- DBMS_OUTPUT.PUT_LINE(vi || '+' );
            IF(vi = 10) THEN
                DBMS_OUTPUT.PUT(vi);
            ELSE
                DBMS_OUTPUT.PUT(vi || '+' );
            END IF;
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE('=' || vsum );
    -- EXCEPTION
    END;

 

<결과>

---

3) WHILE...LOOP 문(제한적 반복)    

[형식]
     WHILE 조건
     LOOP
       실행문; -- 조건이 참일동안 실행되는 문
     END LOOP; 

 

예시1) 1 ~ 10까지 합을 출력

    DECLARE
        vi NUMBER := 1;
        vsum NUMBER := 0;
    BEGIN
        vi := 1;
        WHILE (vi <= 10)
        LOOP
            DBMS_OUTPUT.PUT(vi || '+');
            vsum := vsum + vi;
            vi := vi + 1;
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE('=' || vsum);
    -- EXCEPTION
    END;

---

4) LOOP END LOOP 문(단순 반복)

[형식]
     LOOP 
       [EXIT WHEN 빠져나가는조건;]
       실행문; 

       EXIT WHEN 빠져나가는조건;
     END LOOP; 

 

> EXIT WHEN 빠져나가는조건;은 어디에 있어도 상관없으나 없으면 무한루프에 빠지게 되니 주의하자!

 

예시1) 1 ~ 10까지 합을 출력

    DECLARE
        vi NUMBER := 1;
        vsum NUMBER := 0;
    BEGIN
        vi := 1;
        LOOP
          EXIT WHEN vi = 11;
            DBMS_OUTPUT.PUT(vi || '+');
            vsum := vsum + vi;
            vi := vi + 1;
        END LOOP;

        DBMS_OUTPUT.PUT_LINE('=' || vsum);
    -- EXCEPTION
    END;

---

예시) 구구단

1) FOR LOOP문 2개 사용
    DECLARE
        vdan NUMBER(1);
        vi NUMBER(1);
    BEGIN
        FOR vdan IN 2..9
        LOOP
            FOR vi IN 1..9
            LOOP
                DBMS_OUTPUT.PUT(vdan || '*' || vi || '=' || RPAD(vdan * vi, 4, ' ') );
            END LOOP;
            DBMS_OUTPUT.PUT_LINE('');
        END LOOP;
    -- EXCEPTION
    END;

 

2) WHILE LOOP문 2개 사용
    DECLARE
        vdan NUMBER(2) := 2;
        vi NUMBER(2) := 1;
    BEGIN
        WHILE (vdan <= 9)
        LOOP
            vi := 1;
            WHILE (vi <= 9)
            LOOP
                DBMS_OUTPUT.PUT(vdan || '*' || vi || '=' || RPAD(vdan * vi, 4, ' ') );
                vi := vi + 1;
            END LOOP;
                DBMS_OUTPUT.PUT_LINE('');
                vdan := vdan + 1;
        END LOOP;
    END;


    3) LOOP END LOOP 문 사용   
    DECLARE
        vdan NUMBER;
        vnum NUMBER;
    BEGIN
        vdan := 2;
        LOOP
            vnum := 1;

            LOOP
                DBMS_OUTPUT.PUT_LINE(vdan || '*' || vnum || '=' ||vdan*vnum);
                vnum := vnum +1;
                EXIT WHEN (vnum > 9); 
            END LOOP;

            vdan := vdan +1;
            EXIT WHEN (vdan > 9);    
        END LOOP; 
    --EXCEPTION
    END;

---

6. 저장 프로시저(stored procedure)

1) PL/SQL 6가지 중에 가장 대표적인 구조

 

2) 개발자가 자주 실행해야 하는 업무를 이 문법에 의해 미리 작성하고 DB 내에 저장해 두었다가 필요할 때 마다 호출해서 사용할 수 있다. (성능 때문에..)

 

3) 저장 프로시저 선언 형식

    CREATE OR REPLACE PROCEDURE 프로시저명
    (
        -- 파라미터를 여러개 준다면 ,(콤마) 구분 / 자료형은 크기 설정 안한다.
        파라미터 선언 MODE (IN/OUT/INOUT) 자료형, -- 기본은 IN(입력용) 
        파라미터 선언 MODE (IN/OUT/INOUT) 자료형, -- 기본은 IN(입력용)
        파라미터 선언 MODE (IN/OUT/INOUT) 자료형 -- 기본은 IN(입력용)
    )
    IS -- DECLARE 대신에 사용함
        -- 변수,
        -- 변수,
        -- 변수,
    BEGIN
        -- 실행 쿼리
    EXCEPTION
        -- 예외처리
    END;

 

4) 저장 프로시저 사용하는 방법

    ㄱ. EXECUTE문 실행
    ㄴ. 또 다른 저장 프로시저 안에서 호출해서 실행
    ㄷ. 익명 프로시저에서 호출할 수 있음

 

5) 예시

(1) 프로시저 생성
CREATE OR REPLACE PROCEDURE up_delDept
(
    -- 파라미터 MODE IN 자료형,
    pdeptno IN NUMBER -- 삭제하고자 하는 부서번호를 입력받을 파라미터(인자)
)
IS
    -- 변수
BEGIN
    -- 실행
    DELETE FROM dept
    WHERE deptno = pdeptno;
    
    -- COMMIT; -- 커밋 또는 롤백해줘야 작업이 완료됨. 현재는 다시 돌릴 거라 하지 않음
    
-- EXCEPTION
END up_delDept;

--> rocedure UP_DELDEPT이(가) 컴파일되었습니다.

 

 

(2) 익명 프로시저에서 저장 프로시저 호출
DECLARE
BEGIN
    up_deldept(40); -- 40이 pdeptno임!
--EXCEPTION
END;

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

 

(3) EXECUTE에서 사용
EXECUTE up_deldept(40);

ROLLBACK;