자바 프로그래밍_Day_42_CURSOR, TRIGGER

 

 

2021. 10. 18

 

 

 

     > CURSOR

       -> 결과가 여러 개의 행으로 구해지는 SELECT문을 처리하기 위해 실행 결과를 저장해놓은 객체

       -> CURSOR - OPEN - FETCH - CLOSE 단계로 진행된다.

       -> FOR IN LOOP를 이용하면 LOOP 반복 시 자동으로 CURSOR를 OPEN하고 행 인출(FETCH) LOOP 종료 시 자동으로 CURSOR CLOSE된다.

       -> FOR IN문을 사용하면 커서의 선언도 생략이 가능하다.

속성	설명
%NOTFOUND	커서 영역의 자료가 모두 FETCH되어 다음 영역에 존재하지 않으면 TRUE
%FOUND	커서 영역에 아직 FETCH되지 않은 자료가 있으면 TRUE
%ISOPEN	커서가 OPEN된 상태이면 TRUE
%ROWCOUNT	커서가 얻어 온 레코드의 개수

 

/*
    <CURSOR>
        SQL 문의 처리결과(처리 겨로가가 여러 행(ROW))를 담고 있는 객체이다.
        커서 사용 시 여러 행으로 나타난 처리 결과에 순차적으로 접근이 가능하다.
        
        * 커서 종류
        묵시적 / 명시적 커서 두 종류가 존재한다.
        
        * 커서 속성 (묵시적 커서의 경우 커서명은 SQL로 사용된다.)
         - 커서명%NOTFOUND : 커서 영역에 남아있는 ROW 수가 없으면 TRUE, 아니면 FALSE
         - 커서명%FOUND    : 커서 영역에 남아있는 ROW 수가 한 개 이상일 경우 TRUE, 아니면 FALSE
         - 커서명%ISOPEN   : 커서가 OPEN 상태인 경우 TRUE, 아니면 FALSE(묵시적 커서는 항상 FALSE)
         - 커서명%ROWCOUNT : SQL 처리 결과로 얻어온 행(ROW)의 수
        
        1) 묵시적 커서
            - 오라클에서 자동으로 생성되어 사용하는 커서이다.
            - PL / SQL 블록에서 SQL 문을 실행 시마다 자동으로 만들어져서 사용된다.
            - 사용자는 생성 유무를 알 수 없지만, 커서 속성을 활용하여 커서의 정보를 얻어올 수 있다.
*/
 
SET SERVEROUTPUT ON;
 
-- PL/SQL 구문으로 BONUS가 NULL인 사원의 BONUS를 0으로 수정
SELECT * FROM EMPLOYEE;
COMMIT;
 
BEGIN
    UPDATE EMPLOYEE
    SET BONUS = 0
    WHERE BONUS IS NULL;
    
    -- 묵시적 커서 사용 (ROWCOUNT)
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || '행 수정됨');
END;
/
 
SELECT * FROM EMPLOYEE;
ROLLBACK;

 

       -> 묵시적 커서 사용(ROWCOUNT)

 

/*
    2) 명시적 커서
        - 사용자가 직접 선언해서 사용할 수 있는 커서이다.
        
        [사용방법]
            1) CURSOR 커서명 IS ..       : 커서 선언
            2) OPEN 커서명;              : 커서 오픈
            3) FETCH 커서명 INTO 변수 ... : 커서에서 데이터 추출(한 행씩 데이터를 가져온다.)
            4) CLOSE 커서명              : 커서 닫기
            
        [표현법]
            CURSOR 커서명 IS [SELECT 문]
            
            OPEN 커서명;
            FETCH 커서명 INTO 변수;
            ...
            CLOSE 커서명;
 
*/
-- 급여가 3000000원 이상인 사원의 사번, 이름, 급여 출력(PL/SQL)
DECLARE
    EID EMPLOYEE.EMP_ID%TYPE;
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
    
    CURSOR C1 IS        -- 커서 선언
        SELECT EMP_ID, EMP_NAME, SALARY
        FROM EMPLOYEE
        WHERE SALARY > 3000000;
BEGIN
    OPEN C1;            -- 커서 오픈
    
    LOOP
        -- 서브 쿼리의 결과에서 한 행씩 차례대로 데이터를 가져온다.
        FETCH C1 INTO EID, ENAME, SAL;
        
        EXIT WHEN C1%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(EID || ' ' || ENAME || ' ' || SAL);
    END LOOP;
    
    CLOSE C1;   -- 커서 종료
END;
/

 

 

-- 전체 부서에 대해 부서 코드, 부서명, 지역 조회(PROCEDURE)
CREATE OR REPLACE PROCEDURE CURSOR_DEPT
IS
    V_DEPT DEPARTMENT%ROWTYPE;
    
    CURSOR C1 IS
        SELECT * FROM DEPARTMENT;
BEGIN
    OPEN C1;
    
    LOOP
        FETCH C1 INTO V_DEPT.DEPT_ID, V_DEPT.DEPT_TITLE, V_DEPT.LOCATION_ID;
        
        EXIT WHEN C1%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('부서 코드 :  ' || V_DEPT.DEPT_ID || ', 부서명 : ' || V_DEPT.DEPT_TITLE || ', 지역 : ' || V_DEPT.LOCATION_ID);
        
    END LOOP;
    
    CLOSE C1;
END;
/
 
EXEC CURSOR_DEPT;

 

 

-- FOR IN LOOP를 이용한 커서 사용
CREATE OR REPLACE PROCEDURE CURSOR_DEPT
IS
    V_DEPT DEPARTMENT%ROWTYPE;
    
--    CURSOR C1 IS
--        SELECT * FROM DEPARTMENT;
BEGIN
-- LOOP 시작 시 자동으로 커서를 생성(선언)하고 커서를 OPEN 한다.
-- 반복할 때마다 FETCH도 자동으로 실행된다.
-- LOOP 종료 시 자동으로 커서가 CLOSE 된다.
--    FOR V_DEPT IN C1
      FOR V_DEPT IN (SELECT * FROM DEPARTMENT)
 
    LOOP
        DBMS_OUTPUT.PUT_LINE('부서 코드 :  ' || V_DEPT.DEPT_ID || ', 부서명 : ' || V_DEPT.DEPT_TITLE || ', 지역 : ' || V_DEPT.LOCATION_ID);
    END LOOP;
END;
/
 
EXEC CURSOR_DEPT;

 

 

/*
    <PACKAGE>
        프로시저와 함수를 효율적으로 관리하기 위해 묶는 단위로 패키지는 선언부, 본문(BODY)으로 나눠진다.
*/
-- 1) 패키지 선언부에 변수, 상수 선언 및 사용법
CREATE OR REPLACE PACKAGE TEST_PACKAGE
IS
    NAME VARCHAR2(20);  -- 변수
    PI CONSTANT NUMBER := 3.14; -- 상수
END;
/
 
-- 패키지에 선언된 변수, 상수 사용
BEGIN
    TEST_PACKAGE.NAME := '홍길동';
    
    DBMS_OUTPUT.PUT_LINE('이름 : ' || TEST_PACKAGE.NAME);
    DBMS_OUTPUT.PUT_LINE('PI : ' || TEST_PACKAGE.PI);
 
END;
/

       -> 선언부에 변수, 상수 입력

 

 

-- 2) 패키지 선언부에 프로시저, 함수, 커서 및 사용방법
CREATE OR REPLACE PACKAGE TEST_PACKAGE
IS
    NAME VARCHAR2(20);  -- 변수
    PI CONSTANT NUMBER := 3.14; -- 상수
    PROCEDURE SHOW_EMP;
END;
/
 
-- 에러 발생, 패키지 BODY 부분을 생성해줘야 한다.
-- EXEC SHOW_EMP;  
EXEC TEST_PACKAGE.SHOW_EMP;
 
 
-- 패키지 본문 생성
CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE
IS
    PROCEDURE SHOW_EMP
    IS
        V_EMP EMPLOYEE%ROWTYPE;
    BEGIN
        FOR V_EMP IN (SELECT EMP_ID, EMP_NAME, EMP_NO FROM EMPLOYEE)
        LOOP
            DBMS_OUTPUT.PUT_LINE('사번 : ' || V_EMP.EMP_ID || ', 이름 : ' || V_EMP.EMP_NAME || ', 주민번호 : ' || V_EMP.EMP_NO);
        END LOOP;
    END;
END;
/
 
EXEC TEST_PACKAGE.SHOW_EMP;

 

 

 

     > TRIGGER

       -> 테이블이나 뷰가 INSERT, UPDATE, DELETE 등(이벤트가 발생)의 DML문에 의해 변경될 경우 자동으로 실행될 내용을 정의하여 저장하는 객체.

       -> 사용자가 직접 실행할 수 없고, 정의한 이벤트에 의해서만 자동으로 실행된다.

 

 

/*
    <TRIGGER>
        테이블이 INSERT, UPDATE, DELETE 등 DML 구문에 의해서 변경될 경우(테이블에 이벤트 발생 시)
        자동으로 실행될 내용을 정의해놓는 객체이다.
        
        * 트리거의 종류
            1) SQL 문의 실행 시기에 따른 분류
                - BEFORE TRIGGER : 해당 SQL 문장 실행 전 트리거를 실행한다.
                - AFTER TRIGGER  : 해당 SQL 문장 실행 후 트리거를 실행한다.
                
            2) SQL 문에 의해 영향을 받는 행에 따른 분류
                - 문장 트리거 (STATEMENT TRIGGER) : 해당 SQL 문에 대해 한 번만 트리거를 실행한다.
                - 행 트리거 (ROW TRIGGER)        : 해당 SQL 문에 영향을 받는 행마다 트리거를 실행한다. (FOR EACH ROW 옵션을 기술)
            
        [표현법]
            CREATE OR REPLACE TRIGGER 트리거명
            BEFORE|AFTER INSERT|UPDATE|DELETE ON 테이블명
            [FOR EACH ROW]  -- 행 트리거 실행하고자 하면 기술해줘야 한다.
            [DECLARE
                선언부]
            BEGIN
                실행부
            [EXCEPTION
                예외처리부]
            END;
            /
*/
CREATE OR REPLACE TRIGGER TRG_01
AFTER UPDATE ON EMPLOYEE
BEGIN
    DBMS_OUTPUT.PUT_LINE('업데이트 실행!!');
END;
/
 
CREATE OR REPLACE TRIGGER TRG_02
AFTER UPDATE ON EMPLOYEE
    FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('변경 전 : ' || :OLD.DEPT_CODE || ', 변경 후 : ' <span style="color: #4be

 

-- 상품 입고, 출고 관련 예시
-- 1. 상품에 대한 데이터를 보관할 테이블 생성 (PRODUCT)
 
CREATE TABLE PRODUCT(
    PCODE NUMBER PRIMARY KEY, -- 상품코드
    PNAME VARCHAR2(30),       -- 상품명
    BRAND VARCHAR2(30),       -- 브랜드명
    PRICE NUMBER,             -- 가격
    STOCK NUMBER DEFAULT 0    -- 재고
);
 
-- 상품코드가 중복되지 않게 새로운 번호를 발생하는 시퀀스 객체를 생성한다.
CREATE SEQUENCE SEQ_PCODE;
 
INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, 'Z플립', '삼성', 1500000, DEFAULT);
INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, 'IPHONE13', '애플', 1000000, DEFAULT);
INSERT INTO PRODUCT VALUES(SEQ_PCODE.NEXTVAL, '샤오미폰', '샤오미', 800000, DEFAULT);
 
SELECT * FROM PRODUCT;

 

 

-- 2. 상품 입출고 상세 이력을 보관할 테이블 (PRODETAIL)
CREATE TABLE PRODETAIL (
    DCODE NUMBER PRIMARY KEY,            -- 입출력 이력 코드
    PDATE DATE,                          -- 상품 입/출고 일
    AMOUNT NUMBER,                       -- 수량
    STATUS VARCHAR2(10),                 -- 상태(입고/출고)
    PCODE NUMBER,                        -- 상품 코드(외래 키로 지정, PRODUCT 테이블을 참조)
    CHECK(STATUS IN ('입고', '출고')),
    FOREIGN KEY(PCODE) REFERENCES PRODUCT
);
 
CREATE SEQUENCE SEQ_DCODE;
 
-- 1번 상품이 오늘 날짜로 10개 입고
INSERT INTO PRODETAIL VALUES (SEQ_DCODE.NEXTVAL, SYSDATE, 10, '입고', 1);
 
-- 재고 수량도 변경해야 한다.
UPDATE PRODUCT
SET STOCK = STOCK + 10
WHERE PCODE = 1;
 
-- 2번 상품이 오늘 날짜로 20개 입고
INSERT INTO PRODETAIL VALUES (SEQ_DCODE.NEXTVAL, SYSDATE, 20, '입고', 2);
 
-- 재고 수량도 변경해야 한다.
UPDATE PRODUCT
SET STOCK = STOCK + 20
WHERE PCODE = 2;
 
-- 3번 상품이 오늘 날짜로 5개 입고
INSERT INTO PRODETAIL VALUES (SEQ_DCODE.NEXTVAL, SYSDATE, 5, '입고', 3);
 
-- 재고 수량도 변경해야 한다.
UPDATE PRODUCT
SET STOCK = STOCK + 5
WHERE PCODE = 3;
 
-- 2번 상품이 오늘 날짜로 5개 출고
INSERT INTO PRODETAIL VALUES (SEQ_DCODE.NEXTVAL, SYSDATE, 5, '출고', 2);
 
-- 재고 수량도 변경해야 한다.
UPDATE PRODUCT
SET STOCK = STOCK - 5
WHERE PCODE = 2;
 
SELECT * FROM PRODUCT;
SELECT * FROM PRODETAIL;
 

       -> 

 

 

 

-- PRODETAIL 테이블에 데이터 삽입 시 PRODUCT 테이블에 재고 수량이 업데이트 되도록 트리거를 생성한다.
CREATE OR REPLACE TRIGGER TRG_PRO_STOCK
AFTER INSERT ON PRODETAIL
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE(:NEW.STATUS || ' ' || :NEW.AMOUNT || ' ' || :NEW.PCODE);
    
    -- 상품이 입고된 경우
    IF (:NEW.STATUS = '입고') THEN
        UPDATE PRODUCT
        SET STOCK = STOCK + :NEW.AMOUNT
        WHERE PCODE = :NEW.PCODE;
 
    END IF;
    
    -- 상품이 출고된 경우
    IF (:NEW.STATUS = '출고') THEN
        UPDATE PRODUCT
        SET STOCK = STOCK - :NEW.AMOUNT
        WHERE PCODE = :NEW.PCODE;
    END IF;
END;
/
 
-- 2번 상품이 오늘 날짜로 20개가 입고
INSERT INTO PRODETAIL VALUES (SEQ_DCODE.NEXTVAL, SYSDATE, 20, '입고', 2);
 
-- 2번 상품이 오늘 날짜로 25개가 출고
INSERT INTO PRODETAIL VALUES (SEQ_DCODE.NEXTVAL, SYSDATE, 25, '출고', 2);
 
-- 2번 상품이 오늘 날짜로 25개가 출고 -- 혼자해보기
INSERT INTO PRODETAIL VALUES (SEQ_DCODE.NEXTVAL, SYSDATE, 25, '출고', 2);
 
SELECT * FROM PRODUCT;
SELECT * FROM PRODETAIL;
 
ROLLBACK;

 

 

 

 

     > DCL(Data Control Language)

       -> 계정에게 시스템 권한 또는 객체에 대한 접근 권한을 부여(GRANT)하거나 회수(REVOKE)하는 언어이다.

 

/*
    <DCL(Data Control Language)>
        계정에게 시스템 권한 또는 객체에 대한 접근 권한을 부여(GRANT)하거나 회수(REVOKE)하는 언어이다.
        
        시스템 권한의 종류
            - CREATE SESSION    : 데이터베이스에 접속할 수 있는 권한
            - CREATE TABLE      : 테이블을 생성할 수 있는 권한
            - CREATE VIEW       : 뷰를 생성할 수 있는 권한
            - CREATE SEQUENCE   : 시퀀스를 생성할 수 있는 권한
            - CREATE USER       : 계정을 생성할 수 있는 권한
            ...
            
        [표현법]
            GRANT 권한 1, 권한 2, ... TO 사용자 계정명;
            REVOKE 권한 1, 권한 2, ... FROM 사용자 계정명;
*/
-- 1. 계정 생성
CREATE USER SAMPLE IDENTIFIED BY SAMPLE;
 
-- 2. 계정에 접속하기 위해서 CREATE SESSION 권한 부여
GRANT CREATE SESSION TO SAMPLE;
 
-- 3. 계정에서 테이블을 생성할 수 있는 CREATE TABLE 권한 부여
GRANT RESOURCE TO SAMPLE;
 
-- 4. 테이블 스페이스 할당 : 테이블, 뷰, 인덱스 등 객체들이 저장되는 공간
ALTER USER SAMPLE QUOTA 2M ON SYSTEM;
 
 
-------------------------------------------------
/*
    객체 권한
    특정 객체를 조작할 수 있는 권한
    
    권한 종류     설정 객체
    SELECT      TABLE, VIEW, SEQUENCE
    INSERT      TABLE, VIEW
    UPDATE      TABLE, VIEW
    DELETE      TABLE, VIEW
    ALTER       TABLE, SEQUENCE
    
    등등
    
    [표현법]
        GRANT 권한 종류 ON 특정 객체 TO 사용자 계정;
*/
-- 5. KH.EMPLOYEE 테이블을 조회할 수 있는 권한 부여
GRANT SELECT ON KH.EMPLOYEE TO SAMPLE;
 
-- 6. KH.DEPARTMENT 테이블을 조회할 수 있는 권한 부여
GRANT SELECT ON KH.DEPARTMENT TO SAMPLE;
 
-- 7. KH.DEPARTMENT 테이블에 데이터를 삽입할 수 있는 권한 부여
GRANT INSERT ON KH.DEPARTMENT TO SAMPLE;
 
-- 8. KH.DEPARTMENT 테이블에 데이터를 삽입할 수 있는 권한 회수
REVOKE INSERT ON KH.DEPARTMENT FROM SAMPLE;
 
-- 9. 모든 테이블에 대한 조회 권한 설정
GRANT SELECT ANY TABLE TO SAMPLE;
 
-- 10. 모든 테이블에 대한 조회 권한 회수
REVOKE SELECT ANY TABLE FROM SAMPLE;

       -> SYSTEM계정(관리자 계정)

 

-- CREATE TABLE 권한이 없어서 오류 발생
-- 3. 계정에서 테이블을 생성할 수 있는 CREATE TABLE 권한을 부여받기
CREATE TABLE TEST(
    TID NUMBER
);
 
-- 본인이 소유하고 있는 테이블들은 바로 조작이 가능하다.
SELECT * FROM TEST;
INSERT INTO TEST VALUES(1);
 
-- 다른 계정의 테이블에 접근할 수 있는 권한이 없기 때문에 오류가 발생한다.
-- 5. KH.EMPLOYEE 테이블을 조회할 수 있는 권한 부여받기
SELECT * FROM KH.EMPLOYEE;
 
-- 6. KH.DEPARTMENT 테이블을 조회할 수 있는 권한 부여받기
GRANT SELECT ON KH.DEPARTMENT;
 
-- 7. KH.DEPARTMENT 테이블에 데이터를 삽입할 수 있는 권한 부여받기
INSERT INTO KH.DEPARTMENT
VALUES('D0', '개발부', 'L2');
 
ROLLBACK;
 
--9. 모든 테이블에 대한 조회할 수 있는 권한 부여
SELECT * FROM KH.LOCATION;
SELECT * FROM SUDENT.TB_CLASS;

       -> SAMPLE계정