자바 프로그래밍_Day_41_PL/SQL(2), PROCEDURE, FUNCTION

 

 

2021. 10. 15

 

     > PL/SQL 반복문

/*
    2-2) 반복문
     1) BASIC LOOP
        [표현법]
            LOOP
                반복적으로 실행시킬 구문
                
                [반복문을 빠져나갈 조건문 작성]
                    1) IF 조건식 THEN EXIT; END IF;
                    2) EXIT WHEN 조건식;
            END LOOP;
*/
-- 1 ~ 5까지 순차적으로 1씩 증가하는 값을 출력
DECLARE
    NUM NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
        
        NUM := NUM + 1;
        
--        IF NUM > 5 THEN
--            EXIT;
--        END IF;
 
        EXIT WHEN NUM > 5;
    END LOOP;
END;
/

 

 

/*
     2) WHILE LOOP
        [표현법]
            WHILE 조건식
            LOOP
                반복적으로 실행할 구문;
            END LOOP;
 
*/
-- 1 ~ 5까지 순차적으로 1씩 증가하는 값을 출력
DECLARE
    NUM NUMBER := 1;
BEGIN 
    WHILE (NUM < 5)
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
        
        NUM := NUM + 1;
    END LOOP;
END;
/

 

 

-- 구구단(2 ~ 9단) 출력
DECLARE
    DAN NUMBER := 2;
    SU NUMBER;
BEGIN
    WHILE DAN <= 9
    LOOP
        SU := 1;
        
        WHILE SU <= 9
        LOOP
            DBMS_OUTPUT.PUT_LINE(DAN || ' X ' || SU || ' = ' || DAN * SU);
            
            SU := SU + 1;
        END LOOP;
        
        DBMS_OUTPUT.PUT_LINE('');
        DAN := DAN + 1;
    END LOOP;
END;
/
 

 

 

/*
        3) FOR LOOP
            [표현법]
                FOR 변수 IN [REVERSE] 초기값,.. 최종값
                LOOP
                    반복적으로 실행할 구문;
                END LOOP;
*/
-- 1 ~ 5까지 순차적으로 1씩 증가하는 값을 출력
BEGIN
    FOR NUM IN 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
 
    END LOOP;
END;

 

 

 

-- 역순으로 출력
BEGIN
    FOR NUM IN REVERSE 1..5
    LOOP
        DBMS_OUTPUT.PUT_LINE(NUM);
    END LOOP;
END;
/

 

       -> 구구단 - 짝수만 출력 - reverse

-- 구구단 (2 ~ 9단) 출력 (단, 짝수단만 출력한다.)
BEGIN
    FOR DAN IN REVERSE 2..9
    LOOP
        IF (MOD(DAN, 2) = 0) THEN
            FOR SU IN 1..9
            LOOP
                DBMS_OUTPUT.PUT_LINE(DAN || ' X '|| SU || ' = ' || DAN * SU);
            END LOOP;
            
            DBMS_OUTPUT.PUT_LINE('');
        END IF;
    END LOOP;
END;
/

 

 

 

-- 반복문(FOR 구문)을 이용한 데이터 삽입
CREATE TABLE TEST(
    NUM NUMBER,
    CREATE_DATE DATE
);
 
SELECT * FROM TEST;
TRUNCATE TABLE TEST;
 
-- TEST 테이블에 10개의 행을 INSERT하는 PL/SQL 작성
BEGIN
    FOR NUM IN 1..10
    LOOP
        INSERT INTO TEST VALUES(NUM, SYSDATE);
        IF (MOD(NUM, 2) != 0) THEN
            COMMIT;
        ELSE
            ROLLBACK;
        END IF;
    END LOOP;
END;
/

 

 

/*
    3) 예외처리부 (EXCEPTION SECTION)
        예외란 실행 중 발생하는 오류를 뜻하고 PL/SQL 문에서 발생한 예외를 예외처리부에서 코드적으로 처리가 가능하다.
        
        [표현식]
            DECLARE
            ...
            BEGIN
            ...
            EXCEPTION
                WHEN 예외명 1 THEN 예외처리구문 1;
                WHEN 예외명 2 THEN 예외처리구문 2;
                ...
                WHEN OUTERS THEN 예외처리구문;
            
            * 오라클에서 미리 정의되어 있는 예외(시스템 예외)
                - NO_DATE_FOUND    : SELECT 문의 수행 결과가 한 행도 없을 경우 발생한다.
                - TOO_MANY_ROWS    : 한 행이 리턴되어야 하는데 SELECT 문에서 여러 개의 행을 반환할 때 발생한다.
                - ZERO_DIVIDE      : 숫자를 0으로 나눌 때 발생한다.
                - DUP_VAL_ON_INDEX : UNIQUE 제약 조건을 가진 컬럼에 중복된 데이터가 INSERT 될 때 발생한다.
*/
-- 사용자가 입력한 수로 나눗셈 연산
DECLARE
    RESULT NUMBER;
BEGIN
    RESLUT := 10 / &숫자;
    
    DBMS_OUTPUT.PUT_LINE('결과 :' || RESULT);
EXCEPTION
    WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('나누기 연산시 0으로 나눌 수 없습니다.');
END;
/

10으로 나눴을 때 / 0으로 나눴을 때

 

-- UNIQUE 제약조건 위배시
BEGIN
    UPDATE EMPLOYEE
    SET EMP_ID = 200
    WHERE EMP_NAME = '&이름';
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('이미 존재하는 사번입니다.');
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('오류가 발생했습니다.');
END;
/

방명수 사원 검색

 

-- 너무 많은 행이 조회가 되었을 때
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME
    INTO EID, ENAME
    FROM EMPLOYEE
    WHERE MANAGER_ID = '&사수번호';
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('사원명 : ' || ENAME);
EXCEPTION
    -- WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('너무 많은 행이 조회되었습니다.');
    -- WHEN TNO_DATE_FOUND THEN DBMS_OUTPUT.PUT_LINE('조회 결과가 없습니다.');
    WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('오류가 발생했습니다.');
END;
/

 

 

 

     > PROCEDURE

       -> PL/SQL문을 저장하는 객체로 필요할 때마다 복잡한 구문을 다시 입력할 필요 없이 간단하게 호출해서 실행 결과를 얻을 수 있음

       -> 작성하고 나서 바로 결과값을 반환하지 않고, 실행 구문을 호출하여 실행한다.(EXEC)

       -> 매개변수 있는 프로시저

          -> 프로시저를 정의할 때, CREATE, IS 사이에 프로시저가 전달받을 매개변수 선언이 가능하다.

       -> IN/OUT 매개변수 있는 프로시저

          -> 매개변수를 선언할 때 IN / OUT으로 구분 -> 매개변수 타입 지정

          -> IN : 프로시저 내부에서 사용할 변수

          -> OUT : 매개변수를 통해 프로시저 외부에 전달 가능

 

/*
    <PROCEDURE>
        PL/SQL문을 저장하는 객체
        필요할 때마다 복잡한 구문을 다시 입력할 필요 없이 간단하게 호출해서 실행 결과를 얻을 수 있다.
        특정 로직을 처리하기만 하고 결과값을 반환하지 않는다.
        
        [표현법]
            CREATE OR REPLACE PROCEDURE 프로시저명
            (
                매개변수명 1 [IN|OUT] 데이터타입 [:= DEFAULT 값],
                매개변수명 2 [IN|OUT] 데이터타입 [:= DEFAULT 값],
                ...
            )
            IS [AS]
                선언부
            BEGIN
                실행부
            EXCEPTION
                예외처리부
            END [프로시저명];
            /
            
        [실행방법]
            EXECUTE(EXEC) 프로시저명[(매개값1, 매개값2, ...)];
*/
 
-- 테스트용 테이블 생성
CREATE TABLE EMP_DUP
AS SELECT * FROM EMPLOYEE;
 
SELECT * FROM EMP_DUP;

테스트용 테이블

 

 

 
-- 테스트 테이블의 데이터를 모두 삭제하는 프로시저 생성
CREATE OR REPLACE PROCEDURE DEL_ALL_EMP
IS
BEGIN
    DELETE FROM EMP_DUP;
    
    COMMIT;
END;
/

       -> 데이터가 삭제된 것을 확인할 수 있다.

 

       -> 프로시저도 객체이기 때문에 DROP 구문을 통해 삭제가 가능하다.

 

/*
    1) 매개변수가 있는 프로시저
        프로시저 실행 시 매개변수로 인자값을 전달해야 한다.
 
*/
CREATE OR REPLACE PROCEDURE DEL_EMP_ID
(
    P_EMP_ID EMPLOYEE.EMP_ID%TYPE
)
IS
BEGIN
    DELETE FROM EMPLOYEE
    WHERE EMP_ID = P_EMP_ID;
END;
/
 
SELECT * FROM EMPLOYEE;

       -> 사번 204 삭제.

 

 

       -> 202번 사번 입력

 

 

/*
    2) IN/OUT 매개변수가 있는 프로시저
    IN 매개변수 : 프로시저 내부에서 사용될 변수
    OUT 매개변수 : 프로시저 호출부(외부)에서 사용될 값을 담아줄 변수
*/
 
CREATE OR REPLACE PROCEDURE SELECT_EMP_ID
(
    V_EMP_ID IN EMPLOYEE.EMP_ID%TYPE,
    V_EMP_NAME OUT EMPLOYEE.EMP_NAME%TYPE,
    V_SALARY OUT EMPLOYEE.SALARY%TYPE,
    V_BONUS OUT EMPLOYEE.BONUS%TYPE
)
IS
BEGIN
    SELECT EMP_NAME, SALARY, NVL(BONUS, 0)
    INTO V_EMP_NAME, V_SALARY, V_BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = V_EMP_ID;
END;
/
 
-- 바인드 변수(VARIABLE, VAR)
VAR VAR_EMP_NAME VARCHAR2(30);
VAR VAR_SALARY NUMBER;
VAR VAR_BONUS NUMBER;
 
-- 바인드 변수는 ':변수명' 형태로 참조 가능
EXEC SELECT_EMP_ID('200', :VAR_EMP_NAME, :VAR_SALARY, :VAR_BONUS)
 
-- 프로시저 밖에서 찍어본다.
PRINT VAR_EMP_NAME;
PRINT VAR_SALARY;
PRINT VAL_BONUS;
 
-- 바인드 변수 참조 형태 없이도 가능한 구문
SET AUTOPRINT ON;
 
EXEC SELECT_EMP_ID('201', :VAR_EMP_NAME, :VAR_SALARY, :VAL_BONUS);
EXEC SELECT_EMP_ID('&사번', :VAR_EMP_NAME, :VAR_SALARY, :VAL_BONUS);

       -> 바인드 변수 -> :변수명 형태로 참조하여 조회 가능

 

 

 

     > FUNCTION

       -> 프로시저와 거의 유사한 용도로 사용하지만 실행결과를 되돌려 받을 수 있다는 점에서 프로시저와 다름

 

/*
    <FUNCTION>
     프로시저와 사용 용도가 거의 비슷하지만
     프로시저와 다르게 OUT 변수를 사용하지 않아도 실행 결과를 되돌려 받을 수 있다. (RETURN)
     
     [표현법]
        CREATE OR REPLACE FUNCTION 함수명
        (
            매개변수 1 타입,
            매개변수 2 타입,
            ...
        )
        RETURN 데이터타입
        IS
            선언부
        BEGIN
            실행부
            
            RETURN 반환값; -- 프로시저와 다르게 RETURN 구문이 추가된다.
        EXCEPTION
            예외처리부
        END [함수명];
        /
 
*/
-- 사번을 입력받아 해당 사원의 보너스를 포함하는 연봉을 계산하고 리턴하는 함수 생성
CREATE OR REPLACE FUNCTION BONUS_CALC
(
    V_EMP_ID EMPLOYEE.EMP_ID%TYPE
)
RETURN NUMBER
IS
    V_SAL EMPLOYEE.SALARY%TYPE;
    V_BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT SALARY, NVL(BONUS, 0)
    INTO V_SAL, V_BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = V_EMP_ID;
    
    RETURN (V_SAL + (V_SAL * V_BONUS)) * 12;
END;
/
 
-- FUNCTION 확인 가능
SELECT * FROM USER_SOURCE;
 
-- 함수 결과를 반환받아 저장할 바인드 변수 선언
VAR VAR_CALC_SALARY NUMBER;
 
-- 함수 호출
-- EXEC BONUS_CALC('&사번');  -- 반환하는 값이 있기 때문에 반환값을 받아줘야 한다.
EXEC :VAR_CALC_SALARY := BONUS_CALC('&사번');
 
PRINT VAR_CALC_SALARY;
 
-- 함수를 SELECT 문에서 사용하기 (함수는 RETURN 값이 있어서 SELECT 문에서 사용가능 (EXEC 생략 가능))
SELECT EMP_ID, EMP_NAME, SALARY, BONUS, BONUS_CALC(EMP_ID) AS "연봉"
FROM EMPLOYEE
WHERE BONUS_CALC(EMP_ID) > 40000000
-- ORDER BY "연봉";
ORDER BY BONUS_CALC(EMP_ID) DESC;