[211007~8] FUNCTION, 복합 연습문제

Oracle

[211007~8] FUNCTION, 복합 연습문제

감자탈출기 2021. 10. 8. 01:38

사용자 정의 함수

-- FUNCTION : 함수, 오라클 기본 제공 함수 외의 사용자 정의 함수를 생성하여 사용하기 위한 객체
CREATE OR REPLACE FUNCTION FUNC_TEST1(val NUMBER) RETURN NUMBER --사용자정의함수 반드시 리턴타입 명시
IS
BEGIN
    RETURN val + 1;
END;
/

SELECT FUNC_TEST1(1) FROM DUAL;
-- 1이라는 값 매개변수로 전달하면 함수식을 통해 계산 후 리턴해서 돌려준다.

-- 평소 급여 + 커미션 연봉 계산하던 식
SELECT EMPLOYEE_ID
     , FIRST_NAME || ' ' || LAST_NAME
     , SALARY
     , COMMISSION_PCT
     , SALARY * (1 + DECODE(COMMISSION_PCT, NULL, 0, COMMISSION_PCT)) * 12 AS YEAR_SALARY
  FROM EMPLOYEES;

-- 오류 'DECODE' may be used inside a SQL statement only (디코드는 SQL구문 안에서 써야 함)
-- CREATE OR REPLACE FUNCTION FUNC_YEAR_SALARY(salary NUMBER, commission NUMBER) RETURN NUMBER
-- IS
-- BEGIN
--     RETURN salary * (1 + DECODE(commission, NULL, 0, commission)) * 12;
-- END;
-- /

-- 수정
CREATE OR REPLACE FUNCTION FUNC_YEAR_SALARY(salary NUMBER, commission NUMBER) RETURN NUMBER
IS
    val_comm NUMBER;
BEGIN
    IF commission IS NULL THEN
        val_comm := 0 ;
    ELSE
        val_comm := commission;
    END IF;

    RETURN (salary * (1 + val_comm)) * 12;
END;
/

-- 만든 함수 넣으면 간편하다
SELECT EMPLOYEE_ID
     , FIRST_NAME || ' ' || LAST_NAME
     , SALARY
     , COMMISSION_PCT
     , FUNC_YEAR_SALARY(SALARY, COMMISSION_PCT) * 12 AS YEAR_SALARY
  FROM EMPLOYEES;

-- 부가세 계산
CREATE OR REPLACE FUNCTION FUNC_PROD_OUT_CALC(amount NUMBER, cnt NUMBER) RETURN NUMBER
IS
BEGIN
    RETURN amount * cnt * 1.1;
END;
/

CREATE OR REPLACE FUNCTION FUNC_PROD_IN_CALC(amount NUMBER, cnt NUMBER) RETURN NUMBER
IS
BEGIN
    RETURN amount * cnt * 0.9;
END;
/

UPDATE PROD_INOUT
   SET PRICE = FUNC_PROD_OUT_CALC(AMOUNT,CNT) -- 매개변수를 컬럼으로 넣어야 한번에 바뀜.
WHERE IN_OUT = 'O';

UPDATE PROD_INOUT
   SET PRICE = FUNC_PROD_IN_CALC(AMOUNT,CNT)
WHERE IN_OUT = 'I';

SELECT * FROM PROD_INOUT;

SELECT LAST_DAY(SYSDATE) FROM DUAL;
SELECT FIRST_DAY(SYSDATE) FROM DUAL; -- 첫날 구해주는 함수는 없음 (yyyymm 에다가 || 01 붙이는 방법 쓰기)

/* 날짜를 매개변수로 전달하여 해당 월의 가장 첫번째 날짜를 반환 */
CREATE OR REPLACE FUNCTION TO_FIRST_DATE(d DATE) RETURN DATE
IS
    ret VARCHAR(8);
BEGIN
    ret := TO_CHAR(d, 'yyyymm') || '01';    -- yyyymm 포맷으로 일자(dd)를 없앤 뒤 01을 붙임
    RETURN TO_DATE(ret, 'yyyymmdd');
END;
/
SELECT TO_FIRST_DATE(SYSDATE) FROM DUAL;

/* 날짜를 매개변수로 전달하여 해당 월의 가장 마지막 날짜를 반환 */
CREATE OR REPLACE FUNCTION TO_LAST_DATE(d DATE) RETURN DATE
IS
    ret VARCHAR(8);
BEGIN
    RETURN LAST_DAY(d);
END;
/
SELECT TO_LAST_DATE(SYSDATE) FROM DUAL;
SELECT TO_LAST_DATE(TO_DATE('202108', 'yyyymm')) FROM DUAL;

-- cf. SELECT TO_CHAR(SYSDATE, 'yyyy-mm-dd') FROM DUAL; 날짜 보기 좋게 출력

연습문제

수업자료 - WORKBOOK_0_script_v2.0.sql

한글 깨지는 경우 [211007. 18:00]

<혼자 푼 내용>

<선생님 풀이 : 응용.ver>

SELECT * FROM TB_STUDENT;           -- 학생 정보 테이블
SELECT * FROM TB_PROFESSOR;         -- 교수 정보 테이블
SELECT * FROM TB_CLASS;             -- 과목 정보 테이블
SELECT * FROM TB_DEPARTMENT;        -- 학과 정보 테이블
SELECT * FROM TB_GRADE;             -- 성적 정보 테이블
SELECT * FROM TB_CLASS_PROFESSOR;   -- 학과별 담당 교수 테이블
SET SERVEROUTPUT ON;

-- 조인, 서브쿼리, 집계, 뷰, 프로시저, 함수 등 모든 오라클 객체를 활용.

/*
    학생 정보를 출력할 때 학번, 이름, 학과명, 주민번호, 성별, 집주소 컬럼이 출력될 수 있게 한다.
        - 주민번호는 210101-******* 형식으로 출력되게 한다.
        - 성별은 주민번호 뒷자리의 첫번째 숫자가 1이면 남자, 2이면 여자이다.
*/
-- [1007 21:30 추가 된 다른 방법들]

CREATE OR REPLACE FUNCTION JUMIN_HIDDEN(jumin_number VARCHAR) RETURN VARCHAR2
IS
BEGIN
    RETURN CONCAT(SUBSTR(jumin_number, 1, 6), '-*******');
END;
/

CREATE OR REPLACE FUNCTION JUMIN_GENDER(jumin_number VARCHAR) RETURN VARCHAR2
IS
    jumin_length_error EXCEPTION;
    jumin_pattern_error EXCEPTION;
BEGIN
    IF LENGTH(jumin_number) = 14 THEN
        IF INSTR(jumin_number, '-') = 7 THEN
            IF SUBSTR(jumin_number, 8, 1) = 1 THEN
                RETURN '남자';
            ELSE
                RETURN '여자';
            END IF;
        ELSE
            RAISE jumin_pattern_error;
        END IF;
    ELSE
        RAISE jumin_length_error;
    END IF;
EXCEPTION
    WHEN jumin_length_error THEN
         DBMS_OUTPUT.PUT_LINE('주민등록 번호 길이가 맞지 않습니다.');
    WHEN jumin_pattern_error THEN
          DBMS_OUTPUT.PUT_LINE('주민등록 번호 형식이 올바르지 않습니다. (123456-1234567)');
END;
/

-- 서브쿼리 사용
SELECT STUDENT_NO AS 학번
     , STUDENT_NAME AS 이름
     , (SELECT DEPARTMENT_NAME FROM TB_DEPARTMENT B WHERE A.DEPARTMENT_NO = B.DEPARTMENT_NO) AS 학과명
     , JUMIN_HIDDEN(STUDENT_SSN) AS 주민번호
     , JUMIN_HIDDEN(STUDENT_SSN) AS 성별
     , STUDENT_ADDRESS AS 집주소
  FROM TB_STUDENT A;

-- 뷰로 만들어서 응용, 조인 사용 (서브쿼리보다 성능 좋음)
CREATE OR REPLACE VIEW V_STUDENT_INFO
AS
    SELECT STUDENT_NO AS 학번
        , STUDENT_NAME AS 이름
        , DEPARTMENT_NAME AS 학과명
        , JUMIN_HIDDEN(STUDENT_SSN) AS 주민번호
        , JUMIN_GENDER(STUDENT_SSN) AS 성별
        , STUDENT_ADDRESS AS 집주소
    FROM TB_STUDENT A JOIN TB_DEPARTMENT B
        ON A.DEPARTMENT_NO = B.DEPARTMENT_NO
WITH READ ONLY;

SELECT * FROM V_STUDENT_INFO WHERE 성별 = '여자';
SELECT * FROM V_STUDENT_INFO WHERE 이름 LIKE '조%'; -- LIKE 중요

/*
    교수 정보를 출력할 때 교번, 이름, 학과명, 주민번호, 성별, 집주소 컬럼이 출력될 수 있게 한다.
        - 주민번호는 210101-******* 형식으로 출력되게 한다.
        - 성별은 주민번호 뒷자리의 첫번째 숫자가 1이면 남자, 2이면 여자이다.
*/
CREATE OR REPLACE VIEW V_PROFESSOR_INFO
AS
    SELECT PROFESSOR_NO AS 교번
        , PROFESSOR_NAME AS 이름
        , DEPARTMENT_NAME AS 학과명
        , JUMIN_HIDDEN(PROFESSOR_SSN) AS 주민번호
        , JUMIN_GENDER(PROFESSOR_SSN) AS 성별
        , PROFESSOR_ADDRESS AS 집주소
    FROM TB_PROFESSOR A JOIN TB_DEPARTMENT B
    ON A.DEPARTMENT_NO = B.DEPARTMENT_NO
WITH READ ONLY;

SELECT * FROM V_PROFESSOR_INFO;

/*
    성적 정보를 출력할 때 전체 학기에 대한 평균 성적이 집계에 출력될 수 있게 한다.
        - 뷰로 생성해서 조건절에 학생명을 입력하면 입력한 학생의 성적 정보가 출력될 수 있게 한다.
*/

-- 방법 1
CREATE OR REPLACE VIEW V_GRADE_AVG AS
    SELECT A.STUDENT_NO AS 학번
         , B.STUDENT_NAME AS 이름
         , ROUND(AVG(POINT), 2) AS 평균학점
      FROM TB_GRADE A JOIN TB_STUDENT B
        ON A.STUDENT_NO = B.STUDENT_NO
     GROUP BY A.STUDENT_NO, B.STUDENT_NAME
WITH READ ONLY;

-- 방법 2 (출제자 의도에 맞는)
CREATE OR REPLACE VIEW V_GRADE_FOR_YEAR AS
    SELECT SUBSTR(TERM_NO, 1, 4) AS 년도
         , B.STUDENT_NAME AS 이름
         , ROUND(AVG(POINT), 2) AS 평균학점
      FROM TB_GRADE A JOIN TB_STUDENT B
        ON A.STUDENT_NO = B.STUDENT_NO
     GROUP BY ROLLUP(SUBSTR(TERM_NO, 1, 4)), B.STUDENT_NAME
-- 이름과 년도로 그룹화해서 낸 평균 값을 년도(롤업 지정한 컬럼)기준으로 추가 집계(롤업)한 것(전체 년도 평균 산출)
WITH READ ONLY;

SELECT * FROM TB_GRADE WHERE STUDENT_NO = 'A473023';
SELECT 년도, 평균학점 FROM V_GRADE_FOR_YEAR WHERE 이름 = '이용태';

/*
    학생 정보 테이블의 ABSENCE_YN 컬럼은 휴학여부를 구분하는 컬럼이다. 이를 바탕으로 특정 학생이
    휴학신청을 하거나, 복학신청을 하는 경우 이를 처리할 수 있는 프로시저를 생성하도록 한다.
        - 휴학신청을 하는 학생들의 휴학기간을 파악하기 위해 TB_ABSENCES 테이블을 생성하여
          휴학신청을 하는 학생들을 별도로 관리할 수 있게 한다.
        - TB_ABSENCES 테이블은 ID, 학번, 학과, 휴학신청일, 복학신청일, 예상기간 컬럼을 생성하여
          관리한다.(예상기간은 휴학신청을 할 때 작성하는 기간/년 이다.)
*/
CREATE TABLE TB_ABSENCES (
      ID            NUMBER
    , 학번          VARCHAR2(10)
    , 학과          VARCHAR2(10)
    , 휴학신청일    DATE
    , 복학신청일    DATE
    , 예상기간      NUMBER
);
ALTER TABLE TB_ABSENCES ADD CONSTRAINT TB_ABSENCES_ID_PK PRIMARY KEY(ID);
ALTER TABLE TB_ABSENCES ADD CONSTRAINT TB_ABSENCES_학번_FK FOREIGN KEY(학번) REFERENCES TB_STUDENT(STUDENT_NO);
ALTER TABLE TB_ABSENCES ADD CONSTRAINT TB_ABSENCES_학과_FK FOREIGN KEY(학과) REFERENCES TB_DEPARTMENT(DEPARTMENT_NO);
ALTER TABLE TB_ABSENCES MODIFY 예상기간 CONSTRAINT TB_ABSENCES_예상기간_NN NOT NULL;

CREATE SEQUENCE TB_ABSENCES_SEQ;

-- 휴학 프로시저
CREATE OR REPLACE PROCEDURE 휴학처리(학번 IN VARCHAR, 신청일 IN DATE, 예상기간 IN NUMBER)
IS
    data_count NUMBER;
    absence TB_STUDENT.ABSENCE_YN%TYPE;
    row_data TB_STUDENT%ROWTYPE;
    not_found EXCEPTION;
    already_absence EXCEPTION;
BEGIN
    SELECT COUNT(*) INTO data_count FROM TB_STUDENT WHERE STUDENT_NO = 학번;
    IF data_count = 0 THEN
        RAISE not_found;
    END IF;

    SELECT ABSENCE_YN INTO absence FROM TB_STUDENT WHERE STUDENT_NO = 학번;
    IF absence = 'Y' THEN
        RAISE already_absence;
    END IF;

    UPDATE TB_STUDENT
    SET ABSENCE_YN = 'Y'
    WHERE STUDENT_NO = 학번;

    SELECT * INTO row_data FROM TB_STUDENT WHERE STUDENT_NO = 학번;

    INSERT INTO TB_ABSENCES
        VALUES(TB_ABSENCES_SEQ.NEXTVAL, 학번
                , row_data.DEPARTMENT_NO, 신청일, NULL, 예상기간);

    COMMIT;
EXCEPTION
    WHEN not_found THEN
        DBMS_OUTPUT.PUT_LINE('해당 학번의 학생이 존재하지 않습니다. 학번을 확인하세요.');
    WHEN already_absence THEN
        DBMS_OUTPUT.PUT_LINE('이미 휴학처리가 된 학생입니다.');
END;
/

-- 복학 프로시저
CREATE OR REPLACE PROCEDURE 복학처리(i_id IN NUMBER, i_학번 IN VARCHAR, 신청일 IN DATE)
IS
    data_count NUMBER;
    absence TB_STUDENT.ABSENCE_YN%TYPE;
    not_found EXCEPTION;
    already_absence EXCEPTION;
BEGIN
    SELECT COUNT(*) INTO data_count FROM TB_STUDENT WHERE STUDENT_NO = i_학번;
    IF data_count = 0 THEN
        RAISE not_found;
    END IF;

    SELECT ABSENCE_YN INTO absence FROM TB_STUDENT WHERE STUDENT_NO = i_학번;
    IF absence = 'N' THEN
        RAISE already_absence;
    END IF;

    UPDATE TB_STUDENT
       SET ABSENCE_YN = 'N'
     WHERE STUDENT_NO = i_학번;

    SELECT COUNT(*) INTO data_count FROM TB_ABSENCES WHERE ID = i_id AND 학번 = i_학번;
    IF data_count = 0 THEN
        RAISE not_found;
    END IF;

    UPDATE TB_ABSENCES
       SET 복학신청일 = 신청일
     WHERE ID = i_id
       AND 학번 = i_학번;

    COMMIT;
EXCEPTION
    WHEN not_found THEN
        DBMS_OUTPUT.PUT_LINE('복학 신청을 위한 데이터가 존재하지 않습니다. 신청번호와 학번을 확인하세요.');
        ROLLBACK;
    WHEN already_absence THEN
        DBMS_OUTPUT.PUT_LINE('이미 복학처리가 된 학생입니다.');
        ROLLBACK;
END;
/

SELECT * FROM TB_STUDENT;
SELECT * FROM TB_ABSENCES;
EXEC 휴학처리('A031290', SYSDATE, 1);
EXEC 휴학처리('A031344', SYSDATE, 1);

EXEC 복학처리(31, 'A031290', SYSDATE);
EXEC 복학처리(29, 'A031344', SYSDATE);

<추가 문제>

/*
TB_DEPARTMENT 테이블의 CATEGORY 범주에 속하는 학생들의 인원을 파악하기 위한 쿼리 작성
*/
SELECT CATEGORY
, COUNT(*)
FROM TB_DEPARTMENT A JOIN TB_STUDENT B
ON A.DEPARTMENT_NO = B.DEPARTMENT_NO
GROUP BY CATEGORY;
-- 응용 1
SELECT CATEGORY
, DEPARTMENT_NAME
, COUNT(*)
FROM TB_DEPARTMENT A JOIN TB_STUDENT B
ON A.DEPARTMENT_NO = B.DEPARTMENT_NO
GROUP BY CATEGORY, DEPARTMENT_NAME;
-- 응용 2
SELECT CATEGORY
, DEPARTMENT_NAME
, COUNT(*)
FROM TB_DEPARTMENT A JOIN TB_STUDENT B
ON A.DEPARTMENT_NO = B.DEPARTMENT_NO
GROUP BY CATEGORY, ROLLUP(DEPARTMENT_NAME);
-- 학과명과 카테고리로 그룹화해서 낸 카운트 값을 학과명(롤업 지정한 컬럼)기준으로 추가 집계한 것
-- 상세 설명 [chap05, sample6, 555line]

/*
학과별 학생수가 CAPACITY 컬럼의 정원과 얼마나 차이가 나는지 조회하기 위한 쿼리 작성
정원 미달, 정원 초과, 정원 달성으로 구분으로 구분할 수 있게 하고 비율도 계산하여 조회될 수
있게 한다.
*/
SELECT * FROM TB_STUDENT ORDER BY DEPARTMENT_NO;
SELECT * FROM TB_DEPARTMENT;
-- 학과별 학생 수 먼저 조회
SELECT DEPARTMENT_NO AS 학과
, COUNT(*)
FROM TB_STUDENT
GROUP BY DEPARTMENT_NO
ORDER BY DEPARTMENT_NO;
-- 답
SELECT A.DEPARTMENT_NO AS 학과
, B.CAPACITY
, COUNT(*)
, CASE WHEN COUNT(*) < B.CAPACITY THEN '정원미달'
WHEN COUNT(*) > B.CAPACITY THEN '정원초과'
ELSE '정원달성'
END AS 구분
, ROUND(COUNT(*) / B.CAPACITY * 100, 2) AS 비율
FROM TB_STUDENT A JOIN TB_DEPARTMENT B
ON A.DEPARTMENT_NO = B.DEPARTMENT_NO
GROUP BY A.DEPARTMENT_NO, B.CAPACITY
-- 학과와 CAPACITY 1:1 대응이므로 2개의 컬럼으로 GROUP BY해도 문제가 없는 것
ORDER BY A.DEPARTMENT_NO;

-- 응용 함수1 생성
CREATE OR REPLACE FUNCTION FN_정원구분(인원수 NUMBER, 정원수 NUMBER) RETURN VARCHAR2
IS
BEGIN
IF 인원수 > 정원수 THEN
RETURN '정원초과';
ELSIF 인원수 < 정원수 THEN
RETURN '정원미달';
ELSE
RETURN '정원달성';
END IF;
END;
/
-- 응용 함수2 생성
CREATE OR REPLACE FUNCTION FN_PERCENTAGE(인원수 NUMBER, 정원수 NUMBER) RETURN NUMBER
IS
BEGIN
RETURN ROUND(인원수 / 정원수 * 100, 2);
END;
/

-- 함수 사용한 최종 식
SELECT A.DEPARTMENT_NO AS 학과
, B.CAPACITY
, COUNT(*)
, FN_정원구분(COUNT(*), B.CAPACITY) AS 정원구분
, FN_PERCENTAGE(COUNT(*), B.CAPACITY) AS 비율
FROM TB_STUDENT A JOIN TB_DEPARTMENT B
ON A.DEPARTMENT_NO = B.DEPARTMENT_NO
GROUP BY A.DEPARTMENT_NO, B.CAPACITY
ORDER BY A.DEPARTMENT_NO;