저장 프로시저와 저장 함수

-- 1. 저장 프로시저
-- 실습: 저장 프로시저 생성하기
-- 사원 테이블에 저장된 모든 사원을 삭제하는 프로시저 작성

DROP TABLE EMP01;

CREATE TABLE EMP01
AS
SELECT * FROM EMP;

SELECT * FROM EMP01;

CREATE OR REPLACE PROCEDURE DEL_ALL
IS
BEGIN
    DELETE FROM EMP01;
END;
/

EXECUTE DEL_ALL;

SELECT * FROM EMP01;

-- 저장 프로시저의 오류 원인 살피기

DROP TABLE EMP01;

CREATE TABLE EMP01
AS
SELECT * FROM EMP;

SELECT * FROM EMP01;

DROP PROCEDURE DEL_ALL;

CREATE OR REPLACE PROCEDURE DEL_ALL
IS
-- BEGIN
    DELETE FROM EMP01;
END;
/

SHOW ERROR

-- 저장 프로시저 조회하기
-- 사용자가 저장 프로시저를 생성했는지 확인하려면 USER_SOURCE를 살펴보면 된다.

DESC USER_SOURCE
SELECT NAME, TEXT FROM USER_SOURCE;

 

-- 2. 저장 프로시저의 매개 변수
-- 저장 프로시저에 값을 전달해 주기 위해서 매개 변수를 사용한다.

DROP TABLE EMP01;

CREATE TABLE EMP01
AS
SELECT * FROM EMP;

SELECT * FROM EMP01;

CREATE OR REPLACE PROCEDURE DEL_ENAME(VENAME EMP01.ENAME%TYPE)
IS
BEGIN
    DELETE FROM EMP01
    WHERE ENAME=VENAME;
END;
/

SELECT * FROM EMP01 WHERE ENAME='SMITH';

EXECUTE DEL_ENAME('SMITH');

SELECT * FROM EMP01 WHERE ENAME='SMITH';

 

-- 3. IN, OUT, INPUT 매개 변수
-- IN 매개 변수

CREATE PROCEDURE DEL_ENAME(VENAME IN EMP01.ENAME%TYPE)

-- OUT 매개 변수

DROP PROCEDURE SEL_EMPNO;

CREATE OR REPLACE PROCEDURE SEL_EMPNO(
    VEMPNO IN EMP.EMPNO%TYPE,
    VENAME OUT EMP.ENAME%TYPE,
    VSAL OUT EMP.SAL%TYPE,
    VJOB OUT EMP.JOB%TYPE
)
IS
BEGIN
    SELECT ENAME, SAL, JOB INTO VENAME, VSAL, VJOB
    FROM EMP
    WHERE EMPNO=VEMPNO;
END;
/

-- 바인드 변수
-- ':'를 덧붙여주는 변수는 미리 선언되어 있어야 한다.
VARIABLE VAR_ENAME VARCHAR2(15);
VARIABLE VAR_SAL NUMBER;
VARIABLE VAR_JOB VARCHAR2(9);

-- OUT 매개 변수에서 값을 받아오기 위해서는 프로시저 호출 시 변수 앞에 ':'를 덧붙인다.
EXECUTE SEL_EMPNO(7788, :VAR_ENAME, :VAR_SAL, :VAR_JOB);

PRINT VAR_ENAME;

SELECT ENAME, SAL FROM EMP WHERE EMPNO=7788;

-- IN, OUT 매개변수 활용
-- 사원명으로 검색하여 해당 사원의 직급을 얻어 오는 저장 프로시저를 SEL_EMPNAME라는 이름으로 작성하라.

CREATE OR REPLACE PROCEDURE SEL_EMPNAME(
    VENAME IN EMP.ENAME%TYPE,
    VJOB OUT EMP.JOB%TYPE
)
IS
BEGIN
    SELECT JOB INTO VJOB
    FROM EMP
    WHERE ENAME = VENAME;
END;
/

VARIABLE VAR_JOB VARCHAR2(9);
EXECUTE SEL_EMPNAME('SCOTT', :VAR_JOB);
PRINT VAR_JOB

-- 4. 저장함수
-- 저장 프로시저와 거의 유사한 용도로 사용된다.
-- 차이점은 함수는 실행 결과를 되돌려 받을 수 있다는 점이다.

-- 실습: 저장함수 작성하기
-- 특별 보너스를 지급하기 위한 저장함수를 작성해보자. 보너스는 급여의 200%를 지급한다고 가정한다.

CREATE OR REPLACE FUNCTION CAL_BONUS(VEMPNO IN EMP.EMPNO%TYPE)
    RETURN NUMBER
IS
    VSAL NUMBER(7, 2);
BEGIN
    SELECT SAL INTO VSAL
    FROM EMP
    WHERE EMPNO=VEMPNO;
    
    RETURN  (VSAL*2);
END;
/

VARIABLE VAR_RES NUMBER;
EXECUTE :VAR_RES := CAL_BONUS(7788);
PRINT VAR_RES;

-- 실습: 판매된 도서에 대한 이익을 계산하는 함수
-- 판매된 도서의 이익을 계산하기 위해 각 주문 건별로 실제 판매가격인 SALEPRICE를 입력받아

-- 가격에 맞는 이익(30,000원 이상이면 10%, 30,000원 미만이면 5%)을 계산해 반환하는 함수를 작성

CREATE OR REPLACE FUNCTION FNC_INTEREST(PRICE NUMBER)
    RETURN NUMBER
IS
    MYINTEREST NUMBER;
BEGIN
    -- 가격이 30,000원 이상이면 10%, 30,000원 미만이면 5%
    IF PRICE >= 30000 THEN MYINTEREST := PRICE * 0.1;
    ELSE MYINTEREST := PRICE * 0.05;
    END IF;
    RETURN MYINTEREST;
END;
/

-- 저장함수 생성
-- 사원명으로 검색하여 해당 사원의 직급을 얻어 오는 저장 함수를 SEL_EMPNAME02라는 이름으로 작성하라.

CREATE FUNCTION SEL_EMPNAME02(VENAME IN EMP.ENAME%TYPE)
    RETURN VARCHAR2
IS
    VJOB EMP.JOB%TYPE;
BEGIN
    SELECT JOB INTO VJOB
    FROM EMP
    WHERE ENAME = VENAME;
    
    RETURN VJOB;
END;
/

VARIABLE VAR_JOB VARCHAR2(9); -- SQL SESSION에 저장되는 변수 선언
EXECUTE :VAR_JOB := SEL_EMPNAME02('SCOTT');
PRINT VAR_JOB;