[PL/SQL] 커서(CURSOR), 패키지, 트리커(TRIGGER)

 

 

 

2022.02.04(30일 차)

커서(CURSOR)

패키지 - 눈대중만

트리거(TRIGGER) - 이 작업은 팀장급 DBA가 만든다. 종류는 AFTER, BEFORE TRIGGER 2개가 있다.

 

 

 

커서 (CURSOR)
- 특정 열을 선택하여 처리하는 커서

 

커서(CURSOR)란?? 

 

CURSOR는 SELECT문 또는 데이터 조작어(DML) 같은 SQL문을 실행했을 때 해당 SQL문을 처리하는 정보를

저장한 메모리 공간을 뜻한다.

데이터를 조회할 때 결과 값은 1개인 경우보다 여러 개인 경우가 흔하며 결과 행이 하나일지 여러 개일지

알 수 없는 경우도 존재하므로 대부분 커서(CURSOR)를 활용한다.

 

CURSOR(커서)는 사용 방법에 따라 명시적(explicit) , 묵시적(implicit) 커서로 나뉜다.

 

 

명시적 커서

명시적 커서는 사용자가 직접 커서를 선언하고 사용하는 커서를 뜻함.

단계	명칭	설명
1단계	커서 선언(DECLARATION)	사용자가 직접 이름을 지정하여 사용할 커서를 SQL문과 함께 선언한다.
2단계	커서 열기(OPEN)	커서를 선언할 때 작성한 SQL문을 실행한다. 이때 실행한 SQL문에 영향을 받는 행을 active set이라 한다.
3단계	커서에서 읽어온 데이터 사용 (FETCH)	실행된 SQL문의 결과 행 정보를 하나씩 읽어 와서 변수에 저장한 후 필요한 작업을 수행한다. 각 행별로 공통 작업을 반복해서 실행하기 위해 여러 종류의 LOOP문을 함께 사용할 수 있다.
4단계	커서 닫기(CLOSE)	모든 행의 사용이 끝나고 커서를 종료한다.

 

명시적 커서

명시적 커서 / 쿼리박스에서 실행

 

여기서는 여러 행의 데이터를 커서(CURSOR)에 저장해서 사용하기 위해 LOOP문을 이용한다.

커서에 지정한 SELECT문이 여러 행의 결과 값을 가질 경우에 여러 방식의 LOOP문을 사용할 수 있다.

커서의 모든 행을 사용한 후(더 이상 추출한 데이터가 없으면) LOOP문을 빠져나오기 위해 

%NOTFOUND, EXIT WHEN을 사용한다.

 

cmd에서 실행

 

결과

결과

 

 

FOR문을 이용한 커서 생성

• 상단 코드를 FOR으로 바꿔서 하기

쿼리박스에서 실행

 

FOR LOOP문 - (여러 개의 행이 조회되는 경우)

 

LOOP문을 사용하여 커서를 처리하는 방식은 커서 속성을 사용하여 반복 수행을 제어해야 한다.

커서에서 FOR LOOP문을 사용하면 좀 더 간판 하게 여러 행을 다룰 수 있다.

--FOR LOOP문 기본 형식

FOR 루프 인덱스 이름 IN 커서 이름 LOOP
	결과 행별로 반복 수행할 작업;
END LOOP;

 

커서에 FOR LOOP문을 사용하면 OPEN, FETCH, CLOSE문을 작성하지 않는다. FOR LOOP를 통해 각 명령어를

자동으로 수행하므로 사용 방법이 간단해지는 장점이 있음.

 

• 루프 인덱스(Loop index) : 커서에 저장된 각 행이 저장되는 변수. 점(.)을 통해 행의 각 필드에 접근 가능

cmd에서 실행

 

결과

결과

 

---

[문제]

- 사원번호(USERID)를 입력 시 아이디, 이름, 구입한 제품명, 구입 횟수를 출력하시오.

 

사원번호(USERID)를 입력 시 아이디, 이름, 구입한 제품명, 구입 횟수를 출력하시오.

---

 

패키지 - (PACKAGE)

패키지(package)란?

패키지는 업무나 기능 면에서 연관성이 높은 프로시저, 함수 등 여러 개의 PL/SQL 서브 프로그램을

하나의 논리 그룹으로 묶어 통합&관리하는 데 사용하는 객체를 뜻한다.

• 여러 개의 프로시저를 하나로 묶을 수 있다.
• PACKAGE : 프로시저의 묶음
• package와 body로 구성되어 있음
• FOR EACH ROW : 이걸 사용 안 하면 테이블 레벨 트리거에서 NEW 혹은 OLD 참조는 허용되지 않는다.
• NEW 사용자가 지정한 작업의 실행 후 데이터
• OLD 사용자가 지정한 작업이 실행 전 데이터

 

--PACKAGE 눈도장만 찍기

CREATE OR REPLACE PACKAGE BODY SAWON_PACK
IS
    CURSOR SW_CUR
    IS
    SELECT * FROM 사원;
PROCEDURE SANO_OUT
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('사원번호');
DBMS_OUTPUT.PUT_LINE('--------');
FOR K IN SW_CUR LOOP --FOR문 사용
DBMS_OUTPUT.PUT_LINE(K.사원번호);
END LOOP;
END;
PROCEDURE SANNAME_OUT
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('사원명');
DBMS_OUTPUT.PUT_LINE('------');
FOR K IN SW_CUR LOOP --FOR문 사용
DBMS_OUTPUT.PUT_LINE(K.사원명);
END LOOP;
END;
PROCEDURE BUNO_OUT
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('부서번호');
DBMS_OUTPUT.PUT_LINE('------');
FOR K IN SW_CUR LOOP --FOR문 사용
DBMS_OUTPUT.PUT_LINE(K.부서번호);
END LOOP;
END;
PROCEDURE JIK_OUT
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('직급');
DBMS_OUTPUT.PUT_LINE('----');
FOR K IN SW_CUR LOOP --FOR문 사용
DBMS_OUTPUT.PUT_LINE(K.직급);
END LOOP;
END;
END;
/
--검색 하기
EXEC SAWON_PACK.JIK_OUT;

 

결과

 

패키지 결과

---

TRIGGER (트리거)

TRIGGER(트리거) 란?

오라클에서 트리거는 데이터베이스 안의 특정 상황이나 동작, 즉 이벤트가 발생할 경우에 자동으로 실행되는

기능을 정의하는 PL/SQL 서브 프로그램이다.

• AFTER TRIGGER : 사용자의 작업을 끝낸 뒤 실행되는 트리거(INSERT, UPDATE, DELETE)
• BEFORE TRIGGER : 사용자의 작업을 하기 전에 먼저 진행되는 트리거

ATER TRIGGER(트리거)

• 사용자의 작업을 끝낸 뒤 실행되는 트리거(INSERT, UPDATE, DELETE)

시작 전에 테이블 및 시퀀스 생성하기

 

테이블 & 시퀀스 생성

 

AFTER -INSERT생성(NEW, OLD 개념 기억하기)

 

AFTER -INSERT생성(NEW, OLD 개념 기억하기)

결과

 

결과

결과

 

결과2

 

 

UPDATE TRIGGER

 

UPDATE TRIGGER

 

UPDATE TRIGGER 결과

 

DELETE TRIGGER

 

DELETE TRIGGER

 

DELETE TRIGGER 결과

---

BEFORE TRIGGER(트리거)

• 두 트리거가 하는 일이 같으면 먼저 만들어진 트리거만 실행된다.

먼저 테이블 생성!

 

BEFORE TRIGGER(트리거)

 

트리거 생성 및 데이터 삽입

월요일부터 금요일까지 낮 12시~오후 5시까지만 DML작업이 가능( 조건)

 

트리거 생성 및 데이터 삽입

 

트리거 생성 및 데이터 삽입 결과

 

트리거 생성 및 데이터 삽입 결과

 

주민번호 검사

 

먼저 테이블 생성!

주민번호 검사

 

 

주민번호 검사

결과

맞으면 데이터가 삽입되고 틀리면 주민번호가 틀렸다고 나옴.

주민번호 검사 결과

 

--BEFORE 트리거 ( 주민번호 검사 )

--먼저 테이블 만들기
CREATE TABLE MEMBER
(ID NUMBER(3) PRIMARY KEY,
NAME VARCHAR2(10),
JUBUN VARCHAR2(13));


CREATE OR REPLACE TRIGGER TRI_MEMBER_JUBUN
BEFORE INSERT OR UPDATE OF JUBUN
ON LEE.MEMBER
FOR EACH ROW
DECLARE
A NUMBER; B NUMBER; C NUMBER; D NUMBER; E NUMBER;
F NUMBER; G NUMBER; H NUMBER; I NUMBER; J NUMBER;
K NUMBER; L NUMBER; M NUMBER;
CK NUMBER;
JUBUNFAIL EXCEPTION;
BEGIN
A := SUBSTR(:NEW.JUBUN, 1, 1);
B := SUBSTR(:NEW.JUBUN, 2, 1);
C := SUBSTR(:NEW.JUBUN, 3, 1);
D := SUBSTR(:NEW.JUBUN, 4, 1);
E := SUBSTR(:NEW.JUBUN, 5, 1);
F := SUBSTR(:NEW.JUBUN, 6, 1);
G := SUBSTR(:NEW.JUBUN, 7, 1);
H := SUBSTR(:NEW.JUBUN, 8, 1);
I := SUBSTR(:NEW.JUBUN, 9, 1);
J := SUBSTR(:NEW.JUBUN, 10, 1);
K := SUBSTR(:NEW.JUBUN, 11, 1);
L := SUBSTR(:NEW.JUBUN, 12, 1);
M := SUBSTR(:NEW.JUBUN, 13, 1);
CK := (A*2)+(B*3)+(C*4)+(D*5)+(E*6)+(F*7)+(G*8)+(H*9)+(I*2)+(J*3)+(K*4)+(L*5); --뒤에 곱하는건 주민번호에 고유의 키값이 있는데
CK := MOD(CK, 11);                                           -- 숫자랑 키값을 곱해야 하기때문( 주민번호가 맞는지 확인하는 공식)
CK := 11-CK;                                                 --마지막자리M을 곱하기 안한 이유는 부호비트이기때문
CK := MOD(CK,10);
IF M != CK THEN
RAISE JUBUNFAIL;
END IF;
EXCEPTION
WHEN JUBUNFAIL THEN
RAISE_APPLICATION_ERROR(-20001, '주민번호가 틀렸습니다!');
END;
/

--검사
INSERT INTO MEMBER VALUES (10, 'SUZI', '1111111111111'); --예외처리에 걸려서 틀렸다나옴
INSERT INTO MEMBER VALUES (10, 'SUZI', '9705271211111'); -- 삽입 성공

---