오라클 SQL - 저장 프로시저, 함수, 트리거 (코드버전)

[DATABASE/Oracle] - 오라클(Oracle) SQL - 저장 프로시저, 함수, 트리거

저장 프로시저, 함수, 트리거.sql

-- 저장 프로시저
drop table emp01;
create table emp01 as select * from emp;

select * from emp01;

-- 저장 프로시저 생성
-- 만들기 --> 컴파일
CREATE OR REPLACE PROCEDURE DEL_ALL
IS
BEGIN
    DELETE FROM EMP01;
    COMMIT;
    END;
    /

    -- 저장 프로시저를 실행하기
    EXECUTE DEL_ALL;
    -- 한번 만들어둔 프로시저를 계속 실행할 수 있다.
    INSERT INTO EMP01(EMPNO) VALUES(1);
SELECT * FROM EMP01;
--------------------------------------------------------

-- 저장 프로시저 조회하기
DESC USER_SOURCE;
SELECT * FROM USER_SOURCE WHERE NAME IN ('DEL_ALL');

--------------------------------------------------------
-- 실습 --
--------------------------------------------------------

CREATE OR REPLACE PROCEDURE DEL_ENAME(VENAME EMP01.ENAME%TYPE)
-- 프로시저명(변수명 테이블명.컬럼명%TYPE)
IS
    BEGIN
        DELETE FROM EMP01 WHERE ENAME LIKE VENAME;
    -- ENAME 컬럼에 VENAME 변수값이랑 같은것을 찾아서 지우라.
        COMMIT;
    END;
/
 --------------------------------------------------------
 -- 실행하기
EXECUTE DEL_ENAME('%이%');
SELECT * FROM EMP01 WHERE ENAME LIKE '%이%';
-- 이름에 '이'가 들어간사람 삭제 되었는지 확인
INSERT INTO EMP01 SELECT * FROM EMP WHERE ENAME LIKE '%이%'; -- 다시 다른 테이블에서 값을 가져와서 넣기
--------------------------------------------------------
-- IN, OUT, INOUT 매개변수 (파라미터)parameter
-- 저장 프로시저를 생성할 때 MODE로 3가지 있다.
-- IN : 데이터를 전달 받을 때 사용한다. (default), 실행환경에서 program으로 값을 전달
-- OUT : 수행된 결과를 받아 갈 때 사용한다., program에서 실행환경으로 값을 전달
-- INOUT : in + out은 두가지 모드(기능)를 가질 수 있다.
--  이 모드를 생략하면 IN에 해당된다.

--  1) IN 매개변수
EXECUTE DEL_ENAME ('%이%');

CREATE OR REPLACE PROCEDURE DEL_ENAME
( VENAME IN EMP01.ENAME%TYPE) -- VENAME에서 데이터를 전달 받을 것이기 때문에 IN, 생략 가능한 모드이다.(DEFAULT)

 -- 2) OUT 매개변수

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;
    -- EMPNO와 VEMPNO가 같으면
END;
/
-- 바인드 변수 선언
VARIABLE VAR_ENAME VARCHAR2(15);
VARIABLE VAR_SAL NUMBER;
VARIABLE VAR_JOB VARCHAR2(9);
-- 프로시저 실행
EXECUTE SEL_EMPNO(1002, :VAR_ENAME, :VAR_SAL, :VAR_JOB);

-- 바인드 변수의 값을 출력하기
print var_ename
print var_sal
print var_job

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

--  저장함수 작성하기
-- 문제: 특별 보너스를 지급하기 위한 저장 함수를 작성하기, 보너스는 급여의 200%를 지급
-- 1. 저장함수를 만든다.
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;
/

-- 2. 바인드 변수 선언하기

variable var_res number;

-- 3. 저장함수 실행하기
execute :var_res := cal_bonus ( 1001 );
-- 4. 바인드 변수 출력하기
print var_res

-- 5. 저장함수를 호출하는 SQL 문장
select sal, CAL_BONUS(1001) FROM EMP WHERE EMPNO=1001;

--------------------------------------------------------
-- 커서
-- 문제 : 부서 테이블의 모든 내용 조회하기
-- 1. 커서를 사용하여 부서 테이블의 모든 내용을 출력하는 저장 프로시저를 만든다.
CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE01
IS
   VDEPT DEPT%ROWTYPE;
   CURSOR  C1
   IS
   SELECT * FROM DEPT;

BEGIN
   DBMS_OUTPUT.PUT_LINE('부서번호    부서명   지역명');
   DBMS_OUTPUT.PUT_LINE('------------------------');

   OPEN C1; -- 커서 오픈

   LOOP --FETCH
     FETCH  C1  INTO  VDEPT.DEPTNO, VDEPT.DNAME, VDEPT.LOC; -- C1의 커서가 처리하는 서브쿼리가 SELECT 문을 처리한다.
      -- 결과 셋에서 ROW 단위로 데이터를 읽어 들인다. 인출 후 변수에 데이터를 담아준다.
     EXIT WHEN C1%NOTFOUND; -- 탈출, 커서C1 영역 자료가 더 이상 읽어들일 것이 없다면 TRUE (반복문 탈출)

     DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO ||  '   ' || VDEPT.DNAME || '     ' || VDEPT.LOC );
   END LOOP;  -- END FETCH

   CLOSE C1; -- 커서 닫기
END; -- 프로시저 끝낸 후 실행
/

-- 2. 저장 프로시저 호출(실행)
SET SERVEROUTPUT ON;

EXECUTE CURSOR_SAMPLE01;


-- 문제2 : EMP테이블에서 부서번호가 10인 모든 행 출력하기

SELECT * FROM EMP WHERE DEPTNO=10;

CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE2
IS
  VEMP EMP%ROWTYPE;
  CURSOR C2
  IS
  SELECT * FROM EMP WHERE DEPTNO=10;
BEGIN
   DBMS_OUTPUT.PUT_LINE('사원번호  사원명  직급  매니저번호  입사일 급여 보너스 부서번호');
   DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------');

   OPEN C2;

   LOOP
      FETCH C2 INTO VEMP.EMPNO, VEMP.ENAME, VEMP.JOB, VEMP.MGR,
                  VEMP.HIREDATE, VEMP.SAL, VEMP.COMM, VEMP.DEPTNO;
      EXIT WHEN C2%NOTFOUND;
      DBMS_OUTPUT.PUT_LINE( VEMP.EMPNO || '  ' || VEMP.ENAME || '  ' ||VEMP.JOB || '  ' ||VEMP.MGR || '  ' ||
                  VEMP.HIREDATE|| '  ' ||VEMP.SAL|| '  ' ||VEMP.COMM|| '  ' ||VEMP.DEPTNO ) ;
   END LOOP;
    CLOSE C2;
END;
/

EXECUTE CURSOR_SAMPLE2;


 -----------------------
 -- OPEN ~ FETCH ~ CLOSE가 대신에 FOR~LOOP ~ENDLOOP로 사용할 수도 있다.
 -- 간단하게 커서를 처리할 수 있는 방법이 있다.

 CREATE OR REPLACE PROCEDURE CURSOR_SAMPLE03
 IS
    VDEPT DEPT%ROWTYPE;
    CURSOR C1
    IS
    SELECT * FROM DEPT;

BEGIN
    DBMS_OUTPUT.PUT_LINE('부서번호      부서명     지역');
    DBMS_OUTPUT.PUT_LINE('-----------------------------------');

    FOR VDEPT IN C1 LOOP
        EXIT WHEN C1%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE(VDEPT.DEPTNO ||  '   ' || VDEPT.DNAME || '     ' || VDEPT.LOC );

     END LOOP;

END;
/

execute CURSOR_SAMPLE03;

-----------------------------------------------------
---------------트리거--------------------------------

--트리거 예제
-- 문 ) 테이블에 데이터를 입력할 수 있는 시간 지정하기
 -- 1. 사용할 테이블 만들기
 CREATE TABLE T_ORDER (
    NO NUMBER,
    ORD_CODE VARCHAR2(10),
    OR_DATE DATE );

-- 2. 생성한 테이블에 데이터를 입력할 때 입력시간이 13:00~18:00 일 경우만 입력을 허용하고
-- 그 외 시간일 경우에는 에러를 발생시키는 트리거를 만들기

CREATE OR REPLACE TRIGGER T_ORDER
BEFORE INSERT ON T_ORDER
BEGIN
    IF ( TO_CHAR(SYSDATE, 'HH24:MI' )
    NOT BETWEEN '13:00' AND '15:00' ) -- 허용할 시간
    THEN RAISE_APPLICATION_ERROR(-20100, '허용 시간이 아닙니다.');
    END IF;
END;
/

-- 3. 시스템의 날짜 확인하기
select SYSDATE FROM DUAL;

-- 4. 삽입해보기
INSERT INTO T_ORDER VALUES (3, 'C100', SYSDATE);

SELECT * FROM T_ORDER;

-------트리거 예제2
-- 문제) 테이블에 입력될 데이터 값을 지정하고 그 값 이외에는 에러를 발생시키는 트리거를 생성한다.(행레벨 트리거)
-- 제품코드가 'C100'번인 제품에 입력될 경우 입력을 허용하고 나머지 제품은 모두 에러를 발생시키는 트리거를 작성한다.

-- 1. 트리거 생성하기
CREATE OR REPLACE TRIGGER T_ORDER2
    BEFORE INSERT ON T_ORDER -- T_ORDER라는 테이블에 삽입하기 전에
     FOR EACH ROW -- 행 레벨 트리거
      BEGIN
          IF(:NEW.ORD_CODE) NOT IN ('C200') THEN -- 만약, 컬럼ORD_CODE가 새로운 값이 들어올 때 C100이 아니면,
            RAISE_APPLICATION_ERROR(-20200, '제품코드가 틀립니다!');
         END IF;
    END;
/

-- 2. 값 넣기
INSERT INTO T_ORDER VALUES(4,'C100',SYSDATE);
INSERT INTO T_ORDER VALUES(5,'C200',SYSDATE); -- C100이 아니므로 오류 발생
select * from T_ORDER;


------- 트리거 예제3
-- 트리거의 작동 조건을 WHEN절로 더 자세히 정의하는 내용
-- 문제 ) 모든 제품에 트리거가 작동되는 것이 아니라 ORD_CODE가 'C500'인 제품에 대해서만
-- 14:30~15:00 까지만 입력되도록 허용하는 트리거를 만든다.
-- 'c500'이외의 다른 제품 코드는 시간에 관계없이 정상적으로 입력된다.

-- 1. 트리거 생성
CREATE OR REPLACE TRIGGER T_ORDER3 -- T_ORDER3 트리거 생성
    BEFORE INSERT ON T_ORDER  -- T_ORDER에 값넣기전에 확인하기 위한 BEFORE문
        FOR EACH ROW -- 입력할 행에 대한 조건을 주기위한 FOR EACH
            WHEN ( NEW.ORD_CODE = 'C500' )
                BEGIN
                    IF(TO_CHAR(SYSDATE, 'HH24:MI') NOT BETWEEN '14:00' AND '14:05' ) THEN
                        RAISE_APPLICATION_ERROR(-20300, 'C500제품의 입력 허용 시간이 아닙니다.');
                    end if;
                end;
/


insert into T_ORDER values (1,'C500',sysdate);
select * from T_ORDER;

---------------
-- 트리거 예제4
-- 문제 ) 급여 정보를 자동으로 추가하는 트리거 작성하기

-- 1. 급여 테이블 만들기
DROP TABLE SAL01;
DROP TABLE EMP02;
CREATE TABLE EMP02 (
  EMPNO NUMBER(4) PRIMARY KEY,
  ENMAE VARCHAR2(20),
  JOB VARCHAR2(20) );

CREATE TABLE SAL01 (
   SALNO NUMBER(4) PRIMARY KEY,
   SAL NUMBER(7,2),
   EMPNO NUMBER(4) REFERENCES EMP02(EMPNO) );



SELECT * FROM EMP02;
SELECT * FROM SAL01;

-- 2. 급여 번호를 자동으로 생성하는 시퀀스 생성하기
create sequence SAL01_SALNO_SEQ;

-- 3. 트리거 작성하기
CREATE OR REPLACE TRIGGER TRG_02
    AFTER INSERT ON EMP02 -- 값을 넣고 나서 이후에 처리
    FOR EACH ROW
    BEGIN
        INSERT INTO SAL01 VALUES (SAL01_SALNO_SEQ.NEXTVAL, 100, :NEW.EMPNO);
    end;
/

-- 4. 사원테이블에 ROW가 추가되면 자동으로 수행할 트리거가 생성
INSERT INTO EMP02 VALUES (2, 'A', '과장');
INSERT INTO EMP02 VALUES (500, 'B', '과장');
INSERT INTO EMP02 VALUES (300, 'C', '과장');

SELECT * FROM EMP02;
SELECT * FROM SAL01;

-- 급여정보를 자동 추가하는 트리거 제거하기
drop trigger trg_02;

----------------------------------
-- INSERT 트리거 작성하기
-- 1. 사용할 테이블 만들기

-- 1) 상품 테이블
CREATE TABLE 상품(
    상품코드 CHAR(6) PRIMARY KEY,
    상품명 VARCHAR2(12) NOT NULL,
    제조사 VARCHAR2(12),
    소비자가격 NUMBER(8),
    재고수량 NUMBER DEFAULT 0
);
-- 2) 입고 테이블 생성하기
CREATE TABLE 입고(
    입고번호 NUMBER(6) PRIMARY KEY ,
    상품코드 CHAR(6) REFERENCES 상품(상품코드),
    입고일자 DATE DEFAULT SYSDATE,
    입고수량 NUMBER(6),
    입고단가 NUMBER(8),
    입고금액 NUMBER(8)
);

-- 3) 자료 삽입
INSERT INTO 상품 VALUES ('A100', '세탁기', 'LG', 800000, 0);
INSERT INTO 상품 VALUES ('A101', '냉장고', '삼성', 700000, 0);
INSERT INTO 상품 VALUES ('A102', '노트북', 'LG', 300000, 0);

-- 4) 입고 테이블에 상품이 입력되면 입고수량을 상품테이블의 재고 수량에 추가하는 트리거를 작성

-- 4-1. 입고 트리거 생성하기
CREATE OR REPLACE TRIGGER TRG_04
    AFTER INSERT ON 입고
    FOR EACH ROW
        BEGIN
            UPDATE 상품 SET 재고수량 - 재고수량 + :NEW.입고수량
            WHERE 상품코드 = :NEW.상품코드;
        end;
    /