자바 프로그래밍_Day_40_QL/SQL

 

 

2021. 10. 14

 

 

     > PL / SQL

       -> Procedural Language extension to SQL의 약자로, 오라클 자체에 내장되어 있는 절차적 언어(프로그래밍 언어)이다.

       -> SQL의 단점을 보완하여 SQL문장 내에서 변수의 정의, 조건처리, 반복처리 등을 지원해준다.

       -> PUT_LINE이라는 프로시저를 이용하여 출력(DBMS_OUTPUT패키지에 속해있다.)

 

     > PL / SQL 구조

구조	설명
DECLARE SECTION (선언부)	DECLARE로 시작 변수나 상수를 선언하는 부분
EXECUTABLE SECTION (실행부)	BEGIN으로 시작 제어문, 반복문, 함수 정의 등 로직 기술
EXCEPTION SECTION (예외처리부)	EXCEPTION으로 시작 예외사항 발생 시 해결하기 위한 문장 기술

 

 

 

 

DECLARE
    EID NUMBER;
    ENAME VARCHAR2(30);
    PI CONSTANT NUMBER := 3.14;
BEGIN
    EID := 888;
    ENAME := '배장남';
--    PI := 3.15; -- 에러 발생
    
    DBMS_OUTPUT.PUT_LINE('EID : ' || EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME); DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
    
END;
/

 

 

 

       -> '&NAME' : &(앰퍼샌드) 기호는 대체 변수(값을 입력)를 입력하기 위한 창을 띄워주는 구문이다.

 

 

/*
    1-2) 레퍼런스 타입 변수 선언 및 초기화
        [표현법]
            변수명 테이블명.컬럼명%TYPE;
        
        - 해당하는 테이블의 컬럼에 데이터 타입을 참조해서 그 타입으로 변수를 지정한다.
*/
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
BEGIN
    -- 사원명을 입력받아서 사원의 사번, 사원명, 급여 정보를 각각 EID, ENAME, SAL 변수에 대입 후 출력한다.
    SELECT EMP_ID, EMP_NAME, SALARY
    INTO EID, ENAME, SAL
    FROM EMPLOYEE
    WHERE EMP_NAME = '&NAME';    -- &(앰퍼샌드) 기호는 대체 변수(값을 입력)를 입력하기 위한 창을 띄워주는 구문이다.
 
    DBMS_OUTPUT.PUT_LINE('EID : ' || EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('SAL : ' || SAL);
 
    
END;
/

 

     > 실습문제

----------------------------- 실습 문제 ------------------------------
-- 레퍼런스 타입 변수로 EID, ENAME, JCODE, DTITLE, SAL를 선언하고
-- 각 자료형은 EMPLOYEE 테이블의 EMP_ID, EMP_NAME, JOB_CODE, SALARY 컬럼의 타입과
-- DEPARTMENT 테이블의 DEPT_TITLE 컬럼의 타입을 참조한다.
-- 사용자가 입력한 사번과 일치하는 사번을 조회(사번, 사원명, 직급 코드, 부서명, 급여)한 후 조회 결과를 각 변수에 대입 후 출력한다.
 
SELECT E.EMP_ID,
       E.EMP_NAME,
       E.JOB_CODE,
       D.DEPT_TITLE,
       E.SALARY
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
WHERE EMP_ID = &사번;
 
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    JCODE EMPLOYEE.JOB_CODE%TYPE;
    DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
BEGIN
    SELECT E.EMP_ID,
       E.EMP_NAME,
       E.JOB_CODE,
       D.DEPT_TITLE,
       E.SALARY
    INTO EID, ENAME, JCODE, DTITLE, SAL
    FROM EMPLOYEE E
    JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
    WHERE EMP_ID = &사번;
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('직급 코드 : ' || JCODE);
    DBMS_OUTPUT.PUT_LINE('부서명 : ' || DTITLE);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || SAL);
 
END;
/

 

 

/*
    1-3) ROW 타입 변수 선언 및 초기화
        [표현법]
            변수명 테이블명%ROWTYPE;
        
        - 하나의 테이블의 여러 컬럼의 값을 한꺼번에 저장할 수 있는 변수를 의미한다.
        - 모든 컬럼을 조회하는 경우에 사용하기 편리하다.
 
*/
 
DECLARE
    EMP EMPLOYEE%ROWTYPE;
BEGIN
    SELECT *
    INTO EMP
    FROM EMPLOYEE
    WHERE EMP_NAME = '&사원명';
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('주민번호 : ' || EMP.EMP_NO);
    DBMS_OUTPUT.PUT_LINE('이메일 : ' || EMP.EMAIL);
    DBMS_OUTPUT.PUT_LINE('전화번호 : ' || EMP.PHONE);
    DBMS_OUTPUT.PUT_LINE('부서 코드 : ' || EMP.DEPT_CODE);
    DBMS_OUTPUT.PUT_LINE('직급 코드 : ' || EMP.JOB_CODE);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || EMP.SALARY);
END;
/

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

 

/*
    2) 실행부(EXECUTABLE SECTION)
        2-1) 선택문
            1) 단일 IF 구문
                [표현법]
                    IF 조건식 THEN
                        실행 문장
                    END IF;
*/
-- 사번을 입력받은 후에 해당 사원의 사번, 이름, 급여, 보너스를 출력
-- 단, 보너스를 받지 않는 사원은 보너스율 출력 전에 '보너스를 지급받지 않는 사원입니다.'라는 문구룰 출력한다.
 
SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)
FROM EMPLOYEE
WHERE EMP_ID = '&사번';
 
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, NVL(BONUS, 0)
    INTO EID, ENAME, SAL, BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || TO_CHAR(SAL, 'FM99,999,999') || '원');
    
    IF (BONUS = 0) THEN
        DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('보너스율 : ' || BONUS * 100 || '%');
END;
/

       -> IF 문으로 보너스를 받는 사원 조회

 

 

/*
    2) IF ~ ELSE 구문
        [표현법]
            IF 조건식 THEN
             실행 문장
            ELSE
             실행 문장
            END IF;
*/
-- 위의 PL/SQL을 IF ~ ELSE 구문으로 변경하여 작성
 
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
    BONUS EMPLOYEE.BONUS%TYPE;
BEGIN
    SELECT EMP_ID, EMP_NAME, SALARY, BONUS
    INTO EID, ENAME, SAL, BONUS
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('급여 : ' || TO_CHAR(SAL, 'FM99,999,999') || '원');
    
    IF (BONUS IS NULL) THEN
        DBMS_OUTPUT.PUT_LINE('보너스를 지급받지 않는 사원입니다.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('보너스율 : ' || BONUS * 100 || '%');
    END IF;
END;
/

       -> IF ~ ELSE 문으로 보너스를 받는 사원 조회

 

 

실습문제

-- 사번을 입력받아 해당 사원의 사번, 이름, 부서명, 국가명를 조회한 후 출력한다.
-- 단, 국가 코드가 'KO'이면 국내팀 그 외는 해외팀으로 출력한다.
 
-- 내가 했던 방법
SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, L.NATIONAL_CODE
FROM EMPLOYEE E
JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE);
 
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
    NCODE LOCATION.NATIONAL_CODE%TYPE;
BEGIN
    SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, L.NATIONAL_CODE
    INTO EID, ENAME, DTITLE, NCODE
    FROM EMPLOYEE E
    JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
    JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
    WHERE EMP_ID = '&사번';
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('부서명 : ' || DTITLE);
 
    IF (NCODE = 'KO') THEN
        DBMS_OUTPUT.PUT_LINE('국내팀');
    ELSE 
        DBMS_OUTPUT.PUT_LINE('해외팀');
    END IF;
END;
/

 

       -> 내가 했던 방법도 출력이 되긴 했지만 '팀:'이 빠져있어서 아쉽

 

-- 강사님 방법
 
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    DTITLE DEPARTMENT.DEPT_TITLE%TYPE;
    NCODE LOCATION.NATIONAL_CODE%TYPE;
    TEAM VARCHAR2(10);
BEGIN
    SELECT E.EMP_ID, E.EMP_NAME, D.DEPT_TITLE, L.NATIONAL_CODE
    INTO EID, ENAME, DTITLE, NCODE
    FROM EMPLOYEE E
    JOIN DEPARTMENT D ON (E.DEPT_CODE = D.DEPT_ID)
    JOIN LOCATION L ON (D.LOCATION_ID = L.LOCAL_CODE)
    WHERE E.EMP_ID = '&사번';
    
    IF (NCODE = 'KO') THEN
        TEAM := '국내팀';
    ELSE
        TEAM := '해외팀';
    END IF;
    
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('부서 : ' || DTITLE);
    DBMS_OUTPUT.PUT_LINE('국가 코드 : ' || NCDOE);
    DBMS_OUTPUT.PUT_LINE('소속 : ' || TEAM);
 
END;
/

       -> 강사님 방법

 

/*
    3) IF ~ ELSIF ~ ELSE 구문
        [표현법]
            IF 조건식 THEN
             실행 문장
            ELSIF 조건식 THEN
             실행 문장
            ...
            [ELSE
              실행 문장]
            END IF;
*/
 
-- 점수를 입력받아 SCORE 변수에 저장한 후 학점은 입력된 점수에 따라 GRADE 변수에 저장한다.
-- 90점 이상은 'A'
-- 80점 이상은 'B'
-- 70점 이상은 'C'
-- 60점 이상은 'D'
-- 60점 미만은 'F'
-- 출력은 '당신의 점수는 95점이고, 학점은 A학점입니다.' 와 같이 출력한다.
 
DECLARE
    SCORE NUMBER;
    GRADE CHAR(1);
BEGIN
    SCORE := '&점수';
    
    IF(SCORE >= 90) THEN
        GRADE := 'A';
    ELSIF(SCORE >= 80) THEN
        GRADE := 'B';
    ELSIF(SCORE >= 70) THEN
        GRADE := 'C';
    ELSIF(SCORE >= 60) THEN
        GRADE := 'D';
    ELSE
        GRADE := 'F';
    END IF;
 
    
    DBMS_OUTPUT.PUT_LINE('당신의 점수는 ' || SCORE || '점이고, 학점은 ' || GRADE || '학점 입니다.');
END;
/

 

 

-- 사용자에게 입력받은 사번과 일치하는 사원의 급여 조회 후 급여 등급을 출력한다.
-- 500만원 이상이면 '고급'
-- 300만원 이상이면 '중급'
-- 300만원 미만이면 '초급'
-- 출력은 '해당 사원의 급여 등급은 고급입니다.' 와 같이 출력한다.
DECLARE
    SAL EMPLOYEE.SALARY%TYPE;
    GRADE VARCHAR(10);
BEGIN
    SELECT SALARY
    INTO SAL
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    IF (SAL >= 5000000) THEN
        GRADE := '고급';
    ELSIF(SAL >= 3000000) THEN
        GRADE := '중급';
    ELSE
        GRADE := '초급';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('해당 사원의 급여 등급은 ' || GRADE || '입니다.');
END;
/

 

 

DECLARE
    SAL EMPLOYEE.SALARY%TYPE;
    GRADE SAL_GRADE.SAL_LEVEL%TYPE;
BEGIN
    SELECT SALARY
    INTO SAL
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    SELECT SAL_LEVEL
    INTO GRADE
    FROM SAL_GRADE
    WHERE SAL BETWEEN MIN_SAL AND MAX_SAL;
    
    DBMS_OUTPUT.PUT_LINE('해당 사원의 급여 등급은 ' || GRADE || '입니다.');
END;
/

 

       -> SAL_GRADE 범위로 등급 표시

 

 

/*
    4) CASE 구문
        [표현법]
         CASE 비교 대상
            WHEN 비교값 1 THEN 결과값 1
            WHEN 비교값 2 THEN 결과값 2
            WHEN 비교값 3 THEN 결과값 3
            ...
            [ELSE 결과값]
        END;
*/
-- 사번을 입력받은 후에 사원의 모든 컬럼 데이터를 EMP에 대입하고(ROYTYPE 변수), DEPT_CODE에 따라 알맞는 부서를 출력한다.
DECLARE
    EMP EMPLOYEE%ROWTYPE;
    DNAME VARCHAR2(30);
BEGIN
    SELECT *
    INTO EMP
    FROM EMPLOYEE
    WHERE EMP_ID = '&사번';
    
    DNAME := CASE EMP.DEPT_CODE
                WHEN 'D1' THEN '인사관리부'
                WHEN 'D2' THEN '회계관리부'
                WHEN 'D3' THEN '마케팅부'
                WHEN 'D4' THEN '국내영업부'
                WHEN 'D5' THEN '해외영업1부'
                WHEN 'D6' THEN '해외영업2부'
                WHEN 'D7' THEN '해외영업3부'
                WHEN 'D8' THEN '기술지원부'
                WHEN 'D9' THEN '총무부'
 
             END;
    
    DBMS_OUTPUT.PUT_LINE('사번 : ' || EMP.EMP_ID);
    DBMS_OUTPUT.PUT_LINE('이름 : ' || EMP.EMP_NAME);
    DBMS_OUTPUT.PUT_LINE('부서 코드 : ' || EMP.DEPT_CODE);
    DBMS_OUTPUT.PUT_LINE('부서명 : ' || DNAME);
 
END;
/

       -> CASE문으로 대입 후 결과값 출력