2021.02.23 Oracle 커서

--커서를 활용해서 평균 키를 구하는 스토어드 프로시저를 작성하자.
--(1-1)커서가 포함된 스토어드 프로시저를 작성한다.
CREATE OR REPLACE PROCEDURE cursorProc AS
    v_height NUMBER; --회원의 키
    v_cnt NUMBER := 0; --회원의 인원수(=읽은 행의 수)
    v_total NUMBER := 0; --회원 키의 합계
    --(1) 커서 선언
    CURSOR userCursor IS
        SELECT height FROM userTbl;
    BEGIN
    --(2) 커서 열기
        OPEN userCursor;
    --(3) 커서에서 데이터 가져오기 및 (4) 데이터 처리
    LOOP
        FETCH userCursor INTO v_height;
        EXIT WHEN userCursor%NOTFOUND; --데이터가 없으면 LOOP 종료
        v_total := v_total + v_height;
        v_cnt := v_cnt + 1;
    END LOOP;
    --(5) 커서 닫기
    CLOSE userCursor;
    DBMS_OUTPUT.PUT_LINE('회원 키의 평균 ==>' || (v_total/v_cnt));
END;

--(1-2)스토어드 프로시저를 호출하자.
SET SERVEROUTPUT ON;
EXECUTE cursorProc();




--(2-0)회원 테이블(userTBL)에 회원 등급(grade) 열을 하나 추가한 후에, 각 구매 테이블(buyTBL)에서 회원이 구매한 총액에 따라서
--회원 등급(grade) 열에 최우수회원/우수회원/일반회원/유령회원 등의 값을 입력하는 스토어드 프로시저를 작성해 보자.

--(2-1)먼저 userTBL에 회원 등급을 입력할 열을 추가한다
ALTER TABLE userTBL ADD grade NVARCHAR2(5); --회원 등급 열 추가

--(2-2)스토어드 프로시저를 작성하자
CREATE OR REPLACE PROCEDURE gradeProc AS
    v_id CHAR(8); --회원 아이디
    v_total NUMBER(5) := 0; --총 구매액
    v_grade NVARCHAR2(5); --회원 등급
    
    --(1)커서 선언
    CURSOR userCursor IS
        SELECT U.userid, SUM(price*amount)
            FROM buyTBL B
                RIGHT OUTER JOIN userTBL U
                ON B.userid = U.userid
            GROUP BY U.userid, U.userName;
BEGIN
        --(2)커서 열기
        OPEN userCursor;
        
        --(3)커서에서 데이터 가져오기 및 (4)데이터 처리
         LOOP
            FETCH userCursor INTO v_id, v_total;
        EXIT WHEN userCursor%NOTFOUND; --데이터가 없으면 LOOP 종료
        CASE
             WHEN (v_total >= 1500) THEN v_grade := '최우수회원';
            WHEN (v_total >= 1000) THEN v_grade := '우수회원';
            WHEN (v_total >= 1) THEN v_grade := '일반회원';
            ELSE v_grade := '유령회원';
        END CASE;
        UPDATE userTBL SET grade = v_grade WHERE userID = v_id;
    END LOOP;
    
    --(5) 커서닫기
    CLOSE userCursor;
END;

--스토어드 프로시저를 호출하고, 회원 등급이 완성되었는지 확인
EXEC gradeProc();
SELECT userId, userName, grade FROM userTBL;





--(3-0)간단한 패키지를 생성하자.
--(3-1)스토어드 프로시저 1개와 함수 1개를 포함하는 패키지를 생성하자. 먼저 패키지 선언부를 만들자.
CREATE OR REPLACE PACKAGE totalPackage AS --패키지 선언부
    v_age NUMBER;
    v_bYear NUMBER;
    PROCEDURE sampleProc(pi_userName IN NCHAR);
    FUNCTION sampleFunc(bYear NCHAR) RETURN NUMBER;
END totalPackage;


--(3-2)패키지 본문을 만들자.
CREATE OR REPLACE PACKAGE BODY totalPackage AS
    PROCEDURE sampleProc(pi_userName IN NCHAR) AS
    BEGIN
        SELECT birthYear INTO v_bYear FROM userTBL
            WHERE userName = pi_userName;
        DBMS_OUTPUT.PUT_LINE ('나이 --> ' || sampleFunc(v_bYear));
    END sampleProc;
    
    FUNCTION sampleFunc(bYear NCHAR)
        RETURN NUMBER AS
    BEGIN
        v_age := EXTRACT(YEAR FROM SYSDATE) - bYear;
        RETURN v_age;
    END sampleFunc;
END totalPackage;

--(3-3)패키지 선언부와 패키지 본문을 실행해 보자.
EXECUTE totalPackage.sampleProc('이승기');
SELECT totalPackage.sampleFunc(birthYear) FROM userTBL
    WHERE userName = '이승기';