오라클(Oracle) SQL - 저장 프로시저, 함수, 트리거
목차
[DATABASE/Oracle] - 오라클 SQL - 저장 프로시저, 함수, 트리거 (코드버전)
프로시져란..
특정 작업을 수행할 수 있고, 이름이 있는 PL/SQL 블록으로서 매개 변수를 받을 수 있고, 반복적으로 사용할 수 있다.
보통 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/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(1001, :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 * 200);
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;커서
PL/SQL 예제에서 처리 결과가 1개의 행인 SELECT 문을 다루었다. 하지만 대부분 SELECT 문은 수행 후 반환되는 행의 개수가 한 개 이상이다.
이렇게 여러 갱의 행으로 구해지는 SELECT 문을 처리하기 위해서 커서로 처리해야 한다.
DECLARE
-- 커서 선언
CURSOR cursor_name IS statement;
BEGIN
-- 커서 열기
OPEN cursor_name;
-- 커서로부터 데이터를 읽어와 변수에 저장
FETCH cur_name INTO variable_name;
-- 커서 닫기
CLOSE cursor_name;
END;
DECLARE - 이름있는 SQL 영역 생성 -->
OPEN - active set을 지정 -->
FETCH - 현재 행을 READ하여 변수에 전달 -->
EMPTY? - 처리할 행이 있는가를 검사, 처리할 행 있다면 fetch를 반복 -->
CLOSE - active set을 해제(release)
커서의 상태
| 속성 | 의미 |
| %NOTFOUND | 커서 영역의 자료가 모두 FETCH됐었다면 TRUE |
| %FOUND | 커서 영역에 FETCH되지 않은 자료가 있다면 TRUE |
| %ISOPEN | 커서가 OPEN 된 상태이면 TRUE |
| %ROWCOUNT | 커서가 얻어 온 레코드의 개수 |
-- 커서
-- 문제 : 부서 테이블의 모든 내용 조회하기
select * from emp; -- 이것을 프로시저로 만들 것이다.
-- 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;3. 트리거
--트리거 예제
문제 ) 테이블에 데이터를 입력할 수 있는 시간 지정하기
- 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 '10:00' AND '11:00' ) -- 허용할 시간
THEN RAISE_APPLICATION_ERROR(-20100, '허용 시간이 아닙니다.');
END IF;
END;
/
-- 3. 시스템의 날짜 확인하기
select SYSDATE FROM DUAL;
-- 4. 삽입해보기
INSERT INTO T_ORDER VALUES (1, 'A', 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 ('C100') 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이 아니므로 오류 발생정상 값 입력이 이루어질 경우
오류가 발생할 경우
트리거 예제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;IF(TO_CHAR(SYSDATE, 'HH24:MI') NOT BETWEEN '14:00' AND '14:05' ) THEN 에서 허용 시간을 제대로 설정하고 했을 경우 제대로 값이 입력된다.
허용 시간이 아닐 경우
트리거 예제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) );2. 급여 번호를 자동으로 생성하는 시퀀스 생성하기
create sequence SAL01_SALNO_SEQ;
3. 트리거 작성하기
CREATE OR REPLACE TRIGGER TRG_02
AFTER INSERT ON -- 값을 넣고 나서 이후에 처리
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;
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;
/
5) 트리거를 실행시킨 후 입고 테이블에 행을 추가한다. 입고 테이블에는 물론 상품 테이블의 재고 수량에 변경됨을 확인할 수 있다.
-- 5-1. 상품 입고하기
insert into 입고 (입고번호, 상품코드, 입고수량, 입고단가, 입고금액)
VALUES (1,'A100',5,320,1600);-- 5-2. 입고테이블 조회하기
SELECT * FROM 입고;
-- 5-3. 상품 정보 조회하기
SELECT * FROM 상품-- 5-4. 입고테이블에 상품이 입력되면 자동으로 상품 테이블의 재고 수량이 증가하게 된다.
-- 입고 테이블에 또 다른 상품을 입력한다.
'🗄DATABASE > Oracle' 카테고리의 다른 글
| SQL 응용 - 시험(코드 버전) (0) | 2021.01.18 |
|---|---|
| SQL - 응용 시험 (0) | 2021.01.18 |
| 오라클 SQL - 활용 시험 (0) | 2021.01.15 |
| 스칼라(scala)- 변수(var), 값(val) (0) | 2021.01.14 |
| Oracle 오라클 - PL/SQL (0) | 2021.01.14 |
댓글