22.05.19

오혜원·2022년 5월 19일
PL/SQL
0

SQL

목록 보기
7/7

PL/SQL

: Procedureal Language
: SQL을 기초로 한 절차적 언어

기초 문법

: SET SERVEROUTPUT ON;
: BEGIN ~ END; /
: DECLARE ~ BEGIN ~ END; /

▶ 기본 출력
: DBMS_OUTPUT.PUT_LINE()

-- 서버의 작업 결과 출력 구문
SET SERVEROUTPUT ON;

-- BEGIN ~ END; / : 기본 형태
BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO');
END;
/

출력 결과

HELLO

▶ 변수 선언
: DECLARE [변수 선언 구문]
: 대입 연산자(:=) 사용으로 우항의 값을 좌항에 대입 가능

DECLARE
    ENO NUMBER(4);
    ENM VARCHAR2(10);
BEGIN
    ENO := 9999;
    ENM := 'CHARLES';
    DBMS_OUTPUT.PUT_LINE(ENO || ' ' || ENM);
END;
/

출력 결과

9999 CHARLES

▶ USER DEFINED DATA TYPE
: TABLE_NAME.COLUMN_NAME%TYPE 을 통해 해당 COLUMN의 DATA TYPE 지정

DECLARE
    ENO EMP.EMPNO%TYPE;
    ENM EMP.ENAME%TYPE;
BEGIN
    ENO := 9999;
    ENM := 'CHARLES';
    DBMS_OUTPUT.PUT_LINE(ENO || ' ' || ENM);
END;
/

출력 결과

9999 CHALRES

▶ SELECT를 통해 DATA 입력
: SELECT ~ INTO ~ FROM ~
: SELECT 로 선택한 COLUMN 순서에 맞게 INTO 뒤에 변수 입력

DECLARE
    ENO EMP.EMPNO%TYPE;
    ENM EMP.ENAME%TYPE;
BEGIN
    SELECT EMPNO, ENAME INTO ENO, ENM FROM EMP WHERE ENAME = 'SMITH';
    DBMS_OUTPUT.PUT_LINE(ENO || ' ' || ENM);
END;
/

출력 결과

7369 SMITH

▶ 다수의 SELECT 사용

DECLARE
    ENM EMP.ENAME%TYPE;
    DNM DEPT.DNAME%TYPE;
BEGIN
    SELECT ENAME INTO ENM FROM EMP WHERE EMPNO = 7499;
    SELECT DNAME INTO DNM FROM DEPT WHERE DEPTNO = 20;
    DBMS_OUTPUT.PUT_LINE(ENM || ' ' || DNM);
END;
/

출력 결과

ALLEN RESERACH

▶ SELECT 결과를 이용해 다시 SELECT

DECLARE
    DNO EMP.DEPTNO%TYPE;
    DNM DEPT.DNAME%TYPE;
BEGIN
    SELECT DEPTNO INTO DNO FROM EMP WHERE ENAME = 'SMITH';
    SELECT DNAME INTO DNM FROM DEPT WHERE DEPTNO = DNO;
    DBMS_OUTPUT.PUT_LINE('SMITH : ' || DNO || ' : ' || DNM);
END;
/

출력 결과

SMITH : 20 : RESEARCH

▶ IF문
: IF(조건) THEN ~ ELSIF(조건) THEN ~ ELSE ~ END IF;

DECLARE
    DNO EMP.DEPTNO%TYPE;
    DNM DEPT.DNAME%TYPE;
BEGIN
    SELECT DEPTNO INTO DNO FROM EMP WHERE ENAME = 'MILLER';
    IF(DNO = 10) THEN DNM := '회계';
    ELSIF(DNO = 20) THEN DNM := '기획';
    ELSE DNM := '영업';
    END IF;
    DBMS_OUTPUT.PUT_LINE('MILLER : ' || DNO || ' : ' || DNM);
END;
/

출력 결과

MILLER : 10 : 회계

▶ 반복문
: LOOP / FOR / WHILE

▷ LOOP
: LOOP ~ END LOOP;
: 탈출 조건 - IF() THEN EXIT; END IF;

DECLARE
    LOOPCNT NUMBER(2) := 1; -- 변수 선언 및 초기화
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(LOOPCNT);
        LOOPCNT := LOOPCNT + 1;
        IF(LOOPCNT > 10) THEN EXIT;
        END IF;
    END LOOP;
END;
/

출력 결과

1
2
3
4
5
6
7
8
9
10

▷ FOR
: FOR ~ IN ~ LOOP ~ END LOOP;
: N..M - N부터 M까지 1씩 증가

DECLARE
    LOOPCNT NUMBER(2) := 1; -- 변수 선언 및 초기화
BEGIN
	-- FOR 문에서의 LOOPCNT는 새로 선언되는 지역 변수
    FOR LOOPCNT IN 1..10 LOOP
    	DBMS_OUTPUT.PUT_LINE(LOOPCNT);
    END LOOP;
END;
/

출력 결과

1
2
3
4
5
6
7
8
9
10

▷ WHILE
: WHILE() ~ LOOP ~ END LOOP;

DECLARE
    LOOPCNT NUMBER(2) := 1; -- 변수 선언 및 초기화
BEGIN
    WHILE LOOPCNT < 11 LOOP
        DBMS_OUTPUT.PUT_LINE(LOOPCNT);
        LOOPCNT := LOOPCNT + 1;
    END LOOP;
END;
/

출력 결과

1
2
3
4
5
6
7
8
9
10

▶ 외부에서 DATA 입력
: FUNCTION과 PROCEDURE에서 유용
: 외부 입력키(&) 사용

DECLARE
    TMPINT NUMBER(5);
    TMPSTR VARCHAR2(10);
BEGIN
    TMPINT := &NUM;
    TMPSTR := '&STR';
    DBMS_OUTPUT.PUT_LINE(TMPINT);
    DBMS_OUTPUT.PUT_LINE(TMPSTR);
END;
/

출력 결과
29
STRING

QUIZ

  1. 사원 이름을 입력 받아, 부서명 출력
DECLARE
    STR EMP.ENAME%TYPE;
    DNO EMP.DEPTNO%TYPE;
    DNM DEPT.DNAME%TYPE;
BEGIN
    STR := '&NAME';
    SELECT DEPTNO INTO DNO FROM EMP WHERE ENAME = STR;
    SELECT DNAME INTO DNM FROM DEPT WHERE DEPTNO = DNO;
    DBMS_OUTPUT.PUT_LINE(STR || ' ' || DNO || ' ' || DNM);
END;
/

출력 결과
KING 10 ACCOUNTING

  1. 사원 이름을 입력 받아 부서명 출력 (SUB QUERY 사용, DNO 선언 X)
DECLARE
    STR EMP.ENAME%TYPE;
    DNM DEPT.DNAME%TYPE;
BEGIN
    STR := '&NAME';
    SELECT DNAME INTO DNM FROM DEPT 
    WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = STR);
    DBMS_OUTPUT.PUT_LINE(STR || ' ' || DNM);
END;
/

출력 결과
SMITH RESEARCH

  1. 사원 이름을 입력 받아 부서명 출력 (JOIN을 통해 검색)
DECLARE
    STR EMP.ENAME%TYPE;
    DNM DEPT.DNAME%TYPE;
BEGIN
    STR := '&NAME';
    SELECT DNAME INTO DNM FROM EMP, DEPT 
    WHERE EMP.DEPTNO = DEPT.DEPTNO
    AND ENAME = STR;
    DBMS_OUTPUT.PUT_LINE(STR || ' ' || DNM);
END;
/

출력 결과
WARD SALES

PROCEDURE / FUNCTION

▶ PROCEDURE
: PL/SQL을 DBMS에 저장했다가 사용하는 객체
: 복잡한 업무용

-- PROC_TEST1 컴파일
CREATE OR REPLACE PROCEDURE PROC_TEST1(INPUTSTR IN VARCHAR2)
IS BEGIN
    DBMS_OUTPUT.PUT_LINE(INPUTSTR);
END;
/

-- PROC_TEST1 실행
EXECUTE PROC_TEST1('HELLO WORLD');

출력 결과
HELLO WORLD

CREATE OR REPLACE PROCEDURE PROC_TEST2 (NUM1 IN NUMBER, NUM2 IN NUMBER)
-- PROCEDURE 내에서 사용할 변수 선언
IS RESNUM NUMBER;
BEGIN
    RESNUM := NUM1 + NUM2;
    DBMS_OUTPUT.PUT_LINE('NUM1 + NUM2 = ' || RESNUM);
END;
/

EXECUTE PROC_TEST2(3, 5);

출력 결과
NUM1 + NUM2 = 8

CREATE OR REPLACE PROCEDURE CALC_TEST (NUM1 IN NUMBER, GIHO IN VARCHAR2, NUM2 IN NUMBER)
IS RESNUM NUMBER;
BEGIN
    IF(GIHO = '+') THEN RESNUM := NUM1 + NUM2;
    ELSIF (GIHO = '-') THEN RESNUM := NUM1 - NUM2;
    ELSIF (GIHO = 'X') THEN RESNUM := NUM1 * NUM2;
    ELSIF (GIHO = '/') THEN RESNUM := NUM1 / NUM2;
    ELSIF (GIHO = '%') THEN RESNUM := MOD(NUM1, NUM2);
    ELSE DBMS_OUTPUT.PUT_LINE('WRONG INPUT');
    END IF;
    DBMS_OUTPUT.PUT_LINE(NUM1 || ' ' || GIHO || ' ' || NUM2 || ' = ' || RESNUM);
END;
/

EXECUTE CALC_TEST(3, '%', 4);

출력 결과
3 % 4 = 3

▶ FUNCTION
: PL/SQL을 DBMS에 저장했다가 사용하는 객체 (기능적으로 PROCEDURE와 동일)
: 간단한 기능용

CREATE OR REPLACE FUNCTION FUNC_TEST1 RETURN NUMBER
IS intToOut NUMBER;
BEGIN
    INTTOOUT := 213;
    RETURN INTTOOUT;
END;
/

VARIABLE IVAR NUMBER;
EXECUTE : IVAR := FUNC_TEST1;
PRINT IVAR;
SELECT FUNC_TEST1 FROM DUAL;

출력 결과

      IVAR  FUNC_TEST1
----------  ----------
       213         213
CREATE OR REPLACE FUNCTION SEARCHDNAME(ENM IN VARCHAR2) RETURN VARCHAR2 
IS DNM DEPT.DNAME%TYPE;
BEGIN
    SELECT DNAME INTO DNM FROM DEPT
    WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME = ENM);
    RETURN DNM;
END;
/

SELECT SEARCHDNAME('ADAMS') FROM DUAL;

출력 결과

SEARCHDNAME('ADAMS')                                                                 --------------------
RESEARCH

QUIZ

: 숫자 2개와 기호 1개를 입력받아 계산하는 FUNCTION 작성

CREATE OR REPLACE FUNCTION FUNC_CALC(NUM1 IN NUMBER, GIHO IN VARCHAR2, NUM2 IN NUMBER) RETURN NUMBER
IS RESNUM NUMBER;
BEGIN
    IF (GIHO = '+') THEN RESNUM := NUM1 + NUM2;
    ELSIF (GIHO = '-') THEN RESNUM := NUM1 - NUM2;
    ELSIF (GIHO = 'X') THEN RESNUM := NUM1 * NUM2;
    ELSIF (GIHO = '/') THEN RESNUM := NUM1 / NUM2;
    ELSIF (GIHO = '%') THEN RESNUM := MOD(NUM1, NUM2);
    ELSE DBMS_OUTPUT.PUT_LINE('WRONG INPUT'); -- NULL 출력
    END IF;
    RETURN RESNUM;
END;
/

SELECT FUNC_CALC(4, '/', 4) RESULT FROM DUAL;

출력 결과

    RESULT
----------
         1

CURSOR

: java의 ResultSet과 같은 역할

CREATE OR REPLACE PROCEDURE TEST_CURSOR1
IS
    DEPTROW DEPT%ROWTYPE;
    CURSOR CSR IS SELECT * FROM DEPT;
BEGIN
    OPEN CSR;
    FETCH CSR INTO DEPTROW;
    DBMS_OUTPUT.PUT_LINE(DEPTROW.DEPTNO || ' ' || DEPTROW.DNAME || ' ' || DEPTROW.LOC);
END;
/

EXECUTE TEST_CURSOR1;

출력 결과
10 ACCOUNTING NEW YORK

CREATE OR REPLACE PROCEDURE TEST_CURSOR2
IS
    DEPTROW DEPT%ROWTYPE;
    CURSOR CSR IS SELECT * FROM DEPT;
BEGIN
    OPEN CSR;
    LOOP
        FETCH CSR INTO DEPTROW;
        EXIT WHEN CSR%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(DEPTROW.DEPTNO || ' ' || DEPTROW.DNAME || ' ' || DEPTROW.LOC);
    END LOOP;
    CLOSE CSR;
END;
/

EXECUTE TEST_CURSOR2;

출력 결과
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

TRIGGER

: 하나의 테이블에서 변경이 생겼을 때, 관련 있는 다른 테이블에 변경을 적용하는 기술
: INSERT, UPDATE, DELETE 등의 DML 문이 수행될 때, 자동(DB)으로 추가적 변경
: 새롭게 추가, 변경된 값(:NEW.COLUMN_NAME)과 변경 이전의 값(:OLD.COLUMN_NAME) 활용

▷ SETTING

CREATE TABLE TRI_SALES ( SALENO NUMBER(3)
                       , PRODNO NUMBER(3)
                       , SALEQTY NUMBER(5));

CREATE TABLE TRI_STOCK ( PRODNO NUMBER(3)
                       , PRODNM VARCHAR2(60)
                       , PRODQTY NUMBER(5));
                       
INSERT INTO TRI_STOCK VALUES(1, '새우깡', 100);
INSERT INTO TRI_STOCK VALUES(2, '포카칩', 100);
INSERT INTO TRI_STOCK VALUES(3, '감자깡', 100);
COMMIT;

▷ INSERT TRIGGER
: 판매된 수량만큼 재고량을 변경
→ (변경된 재고량) = (기존 재고량) - (판매 수량)

CREATE OR REPLACE TRIGGER TRI_INS
-- TRIGGER 수행 시점 지정 : TRI_SALES 각 행에서 INSERT 수행 이후
AFTER INSERT ON TRI_SALES FOR EACH ROW
BEGIN
    UPDATE TRI_STOCK SET PRODQTY = PRODQTY - :NEW.SALEQTY
    WHERE PRODNO = :NEW.PRODNO;
END;
/

INSERT INTO TRI_SALES VALUES(1, 2, 5);

실행 결과

    PRODNO PRODNM       PRODQTY
---------- --------- ----------
         1 새우깡            100
         2 포카칩             95
         3 감자깡            100

▷ UPDATE TRIGGER
: 부분 반품된 수량만큼 재고량을 변경
→ (변경된 재고량) = (기존 재고량) + (기존 판매 수량) - (최종 판매 수량)

CREATE OR REPLACE TRIGGER TRI_UPD
AFTER UPDATE ON TRI_SALES FOR EACH ROW
BEGIN
    -- :OLD.COLUMN_NAME - 수정 전 데이터 값 :NEW.COLUMN_NAME - 수정 후 데이터 값
    UPDATE TRI_STOCK SET PRODQTY = PRODQTY + :OLD.SALEQTY - :NEW.SALEQTY
    WHERE PRODNO = :OLD.PRODNO;
END;
/

UPDATE TRI_SALES SET SALEQTY = 3 WHERE SALENO = 1;

실행 결과

    PRODNO PRODNM       PRODQTY
---------- --------- ----------
         1 새우깡            100
         2 포카칩             97
         3 감자깡            100

▷ DELETE TRIGGER
: 반품된 수량만큼 재고량을 변경
→ (변경된 재고량) = (기존 재고량) + (기존 판매 수량)

CREATE OR REPLACE TRIGGER TRI_DEL
AFTER DELETE ON TRI_SALES FOR EACH ROW
BEGIN
    UPDATE TRI_STOCK SET PRODQTY = PRODQTY + :OLD.SALEQTY
    WHERE PRODNO = :OLD.PRODNO;
END;
/

DELETE FROM TRI_SALES WHERE SALENO = 1;

실행 결과

    PRODNO PRODNM       PRODQTY
---------- --------- ----------
         1 새우깡            100
         2 포카칩            100
         3 감자깡            100

EXCEPTION

: 예외 처리
: EXCEPTION WHEN ERROR_NAME THEN ~

DECLARE
    DNM DEPT.DNAME%TYPE;
BEGIN
    SELECT DNAME INTO DNM FROM DEPT 
    WHERE DEPTNO = (SELECT DEPTNO FROM EMP);
    DBMS_OUTPUT.PUT_LINE(DNM);
    EXCEPTION WHEN OTHERS 
    THEN DBMS_OUTPUT.PUT_LINE('CHECK EXCEPTION');
END;
/

출력 결과

CHECK EXCEPTION
profile
오혜원
이전 포스트

22.05.18

0개의 댓글