[6/26] 객체권한, 저장프로시저, 트리거

📌객체권한

사용자의 외부스키마 관련 객체 명령 사용권한
INSERT, UPDATE, SELECT 등의 명령과 관련된 권한
형식 ) GRANT [ALL | PRIVILEGE,...] ON 객체명 TO 객체명 [WITH GRANT OPTION]
ALL : 객체에 관련된 모든 명령 사용 권한
WITH GRANT OPTION : 부여받은 객체권한을 다른 사용자에게 부여하거나 회수할 수 있는 기능을 제공

SCOTT 스키마에 존재하는 DEPT테이블에 대한 SELECT 명령 사용권한이 없으므로 에러가 발생
SCOTT계정이 KIM계정에게 DEPT테이블에 대한 SELECT 명령 사용권한을 부여을 해주면 접근 가능하다

--권한 부여
GRANT SELECT ON DEPT TO KIM;

객체 사용을 부여받은 후 KIM계정이 SCOTT스키마에 존재하는 DEPT테이블의 모든 부서정보를 검색할 수 있다.

1) 다른계정에게 부여한 객체 권한 확인

USER_TABLE_PRIVS_MADE : 객체 권한을 부여한 정보를 제공

2) 다른계정에게 부여받은 객체 권한 확인

USER_TABLE_PRIVS_RECD : 객체 권한을 받은 정보를 제공

3) 시스템 권한 회수

계정에게 부여한 모든 시스템 권한을 회수해도 계정 미삭제
형식) REVOKE FROM [ALL | PRIVIEGE,...] 계정명 [WITH ADMIN OPTION]

4) 객체 권한 회수

형식 ) REVOKE [ALL | PRIVIEGE,...] ON 객체명 FROM 계정명 [WITH GRANT OPTION]

--SCOTT계정이 KIM계정에게 부여한 DEPT테이블 SELECT 명령 권한회수
REVOKE SELECT ON DEPT FROM KIM;

5) ROLE : 관리자가 계정의 시스템 권한 보다 효율적으로 부여하거나 회수하기 위해 사용하는 권한그룹

오라클은 기본적으로 롤이 제공되어 사용이 가능하다.
CONNECT : 8개의 시스템 권한 - CREATE, SESSION, CREATE TABLE, ALTER SESSION CREATE SYSNONYM
RESOURCE : 객체 관련 시스템 권한 - CREATE TABLE, CREATE SEQUNCE, CREATE TIGGER등
DBA : 시스템관리에 필요한 모든 시스템권한 - 관리자 생성을 위해 제공되는 롤

존재하지 않는 계정인 LEE에게 권한을 부여하면 자동으로 계정이 생성된다(비밀번호를 꼭 설정해주어야함)

📌PL/SQL

PL/SQL을 사용하면 오라클이 프로그램적인 요소를 가질 수 있다.
PL/SQL (PROCEDUARAL LANGUAGE EXTENSION TO SQL) : SQL에 없는 변수 선언 선택처리, 반복처리 기능을 제공하는 언어
세부분의 영영으로 구분하여 선언

1) DECLARE 영역(선언부) : DECLARE - 선택

2) EXECUTABLE 영역(실행부) : BEGIN - 필수

3) EXCEPTION 영역(예외처리부) : EXCEPTION - 선택

영역에서 하나의 명령을 종료할 때마다 ;를 사용하여 구분
마지막 영역은 END키워드로 마무리 후 ; 사용
PL/SQL실행을 위해 마지막에 /를 사용
메세지를 출력할 수 있도록 세션 환경변수의 설정값을 변경

SET SERVEROUT ON;

메세지를 출력하는 함수 : PL/SQL실행부에서 호출하여 사용
형식 ) DBMS_OUTPUT.PUT_LINE(출력메세지)

BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO, ORACLE');
END;
/

📌변수선언과 초기값 입력

1) 변수선언과 초기값 입력 - 선언부

형식) 변수명 [CONSTANT] 자료형 [NOT NULL] [(:= | DEFAULT) 표현식]
CONSTANT : 변수에 저장된 초기값 변경 불가능 키워드
NOT NULL : 변수에 NULL 저장 불가능
:= : 대입연산자
DEFAULT : 기본값
표현식 : 변수에 저장되는 값에대한 표현방법(값 , 변수, 연산식, 함수) 호출

2) 선언된 변수에 저장된 값 변경 - 실행부

스칼라변수 : 자료형을 이용하여 변수를 선언
형식) 변수명 := 표현식;

DECLARE 
    VEMPNO NUMBER(4); -- 초기값이 없이 생성 (NULL)
    VENAME VARCHAR2(20);
BEGIN
    VEMPNO := 7788;
    VENAME := 'SCOTT';
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름');
    DBMS_OUTPUT.PUT_LINE('---------------------');
    DBMS_OUTPUT.PUT_LINE(VEMPNO||' / ' || VENAME);
END;
/

레퍼런스 변수 : 다른 변수의 자료형 또는 테이블 컬럼의 자료형을 이용하여 변수 선언 - 선언부
형식) [변수명 TYPE[테이블 명, 컬럼명% TYPE]]

3) 테이블 검색행의 컬럼값을 변수에 저장하는 명령 - 실행부

형식) SELECT 검색대상, 검색대상,...INTO 변수명, 변수명,...FROM 테이블 명 WHERE 조건식
검색대상과 변수의 자료형 및 개수가 반드시 동일하게 선언

--EMP테이블의 EMPNO컬럼과 ENAME컬럼의 자료형을 이용하여 레퍼런스 변수를 선언하고 EMP테이블에서 사원이름이 SCOTT인 사원을 검색
DECLARE
    VEMPNO EMP.EMPNO%TYPE;
    VENAME EMP.ENAME%TYPE;
BEGIN
    SELECT EMPNO, ENAME INTO VEMPNO, VENAME FROM EMP WHERE ENAME = 'SCOTT';
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름');
    DBMS_OUTPUT.PUT_LINE('---------------------');
    DBMS_OUTPUT.PUT_LINE(VEMPNO||' / ' || VENAME);
END;
/

테이블 변수 : 테이블의 다중 검색행에 대한 컬럼값을 저장하기 위한 변수 - 배열
변수를 선언하기 전에 테이블 변수에 대한 자료형(테이블 타입) 선언

--형식) 
TYPE 테이블타입명 IS TABLE OF 
	(자료형 | 변수명%TYPE | 테이블명.컬럼명%TPYE) 
    [NOT NULL] [INDEX OF BINARY_INTTEGER];
	
    테이블변수명 테이블 타입명; <-변수 만들기

테이블 변수의 사용 방법 : 테이블 변수의 요소는 첨자를 이용하여 표현 - 첨자는 1부터 1씩 증가
형식) 테이블변수명(첨자)

DECLARE
    --테이블 타입 먼저 선언
    TYPE EMP_TABLE_TYPE1 IS TABLE OF EMP.EMPNO%TYPE INDEX BY BINARY_INTEGER;
    TYPE EMP_TABLE_TYPE2 IS TABLE OF EMP.ENAME%TYPE INDEX BY BINARY_INTEGER;
    --테이블 변수 선언=배열
    VEMPNO_TABLE EMP_TABLE_TYPE1;
    VENAME_TABLE EMP_TABLE_TYPE2;
    --테이블 변수의 첨자대신 반복처리 하기 위한 변수를 선언 - 초기값 저장
    I BINARY_INTEGER := 0;
BEGIN
    --EMP테이블의 모든 사원을 검색(반복문이용)
    --테이블 변수의 요소에 저장되도록 반복처리
    FOR K IN (SELECT EMPNO,ENAME FROM EMP) LOOP --K에는 다중행이 저장되어 있을것임
        I := I + 1;
        VEMPNO_TABLE(I) := K.EMPNO;
        VENAME_TABLE(I) := K.ENAME;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 / 사원이름');
    DBMS_OUTPUT.PUT_LINE('---------------------');
   -- DBMS_OUTPUT.PUT_LINE(VEMPNO||' / ' || VENAME);
   --테이블 변수의 요소값을 화면에 출력되도록 반복처리
   FOR J IN 1.. I LOOP
        DBMS_OUTPUT.PUT_LINE(VEMPNO_TABLE(J) || ' / ' || VENAME_TABLE(J));
    END LOOP;
END;
/

레코드 변수 : 테이블 행의 모든 컬럼값을 저장하기 위한 변수 - VO클래스
변수를 선언하기 전에 레코드 변수에 대한 자료형(레코드타입) 선언

--형식)
TYPE 레코드타입명 IS RECORD(필드명 
	{자료형|변수명%TYPE|테이블명.컬럼명%TYPE}
	[NOT NULL] [{:=|DEFAULT} 표현식],...);
	
    레코드변수명 레코드 타입명;

레코드타입없이 테이블의 행을 이용하여 변수선언 가능 : 레코드 변수의 필드는 컬럼명을 사용
예시 ) EMP_RECORD EMP%ROWTYPE;
필드의 이름은 컬럼명이 된다.

DECLARE
    --레코드 타입선언
    TYPE EMP_RECORD_TYPE IS RECORD(VEMPNO EMP.EMPNO%TYPE,
    VENAME EMP.ENAME%TYPE, 
    VJOB EMP.JOB%TYPE, 
    VSAL EMP.SAL%TYPE, 
    VDEPTNO EMP.DEPTNO%TYPE);
    
    --레코드 변수선언
    EMP_RECORD EMP_RECORD_TYPE;
BEGIN
    SELECT EMPNO,ENAME,JOB,SAL,DEPTNO INTO EMP_RECORD.VEMPNO, 
    EMP_RECORD.VENAME, 
    EMP_RECORD.VJOB,
    EMP_RECORD.VSAL, 
    EMP_RECORD.VDEPTNO FROM EMP
    WHERE DEPTNO = 7788;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 = ' ||EMP_RECORD.VEMPNO);
    DBMS_OUTPUT.PUT_LINE('사원이름 = ' ||EMP_RECORD.VENAME);
    DBMS_OUTPUT.PUT_LINE('업무 = ' ||EMP_RECORD.VJOB);
    DBMS_OUTPUT.PUT_LINE('급여 = ' ||EMP_RECORD.VSAL);
    DBMS_OUTPUT.PUT_LINE('부서번호 = ' ||EMP_RECORD.VDEPTNO);
END;
/
----

DECLARE
    EMP_RECORD EMP%ROWTYPE;
BEGIN
    SELECT * INTO EMP_RECORD FROM EMP WHERE EMPNO= 7844;
    DBMS_OUTPUT.PUT_LINE('사원번호 = ' ||EMP_RECORD.EMPNO);
    DBMS_OUTPUT.PUT_LINE('사원이름 = ' ||EMP_RECORD.ENAME);
    DBMS_OUTPUT.PUT_LINE('업무 = ' ||EMP_RECORD.JOB);
    DBMS_OUTPUT.PUT_LINE('급여 = ' ||EMP_RECORD.SAL);
    DBMS_OUTPUT.PUT_LINE('부서번호 = ' ||EMP_RECORD.DEPTNO);
END;
/

📌선택문

명령을 선택하여 실행하기 위한 구문

1) IF문

조건식에 의해 명령을 선택 실행
형식1 ) IF(조건식) THEN 명령; 명령;.... END IF;

DECLARE
    VEMP EMP%ROWTYPE;
    VDNAME VARCHAR2(20) := NULL;
BEGIN
    SELECT * INTO VEMP FROM EMP WHERE EMPNO = 7788;
    IF(VEMP.DEPTNO = 10) THEN
        VDNAME := 'RESEARCH';
    END IF;
    
     IF(VEMP.DEPTNO = 20) THEN
        VDNAME := 'RESEARCH';
    END IF;
    
     IF(VEMP.DEPTNO = 30) THEN
        VDNAME := 'SALES';
    END IF;
    
     IF(VEMP.DEPTNO = 40) THEN
        VDNAME := 'OPERATION';
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 = ' || VEMP.EMPNO);
    DBMS_OUTPUT.PUT_LINE('사원번호 = ' || VEMP.ENAME);
    DBMS_OUTPUT.PUT_LINE('사원번호 = ' || VDEPTNO);

END;
/

형식2 ) IF(조건식) THEN 명령;...ELSE 명령;... END IF;

DECLARE
    VEMP EMP%ROWTYPE;
    ANNUAL NUMBER(7,2) := 0;
BEGIN
    SELECT * INTO VEMP FROM EMP WHERE EMPNO = 7788;
    IF(VEMP.COMM IS NULL) THEN
        ANNUAL := (VEMP.SAL) * 12;
    ELSE
        ANNUAL := (VEMP.SAL + VEMP.COMM) * 12;
    END IF;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 = ' || VEMP.EMPNO);
    DBMS_OUTPUT.PUT_LINE('사원이름 = ' || VEMP.ENAME);
    DBMS_OUTPUT.PUT_LINE('연봉 = ' || TO_CHAR(ANNUAL, '999,999.00'));
END;
/

형식3 ) IF(조건식) THEN 명령; .... ELSIF(조건식) THEN 명령; ....[ELSE 명령....] END IF;

2) CASE

변수에 저장된 값을 비교하여 명령을 선택 실행하거나 조건식을 이용하여 명령을 선택수행할 수 있다.
형식) CASE 변수명 WHEN 값1 THEN 명령; ... WHEN 값2 THEN 명령 END CASE;

DECLARE
    VEMP EMP%ROWTYPE;
    VPAY NUMBER(7,2);
BEGIN
    SELECT * INTO VEMP FROM EMP WHERE EMPNO = 7788;
    
    CASE VEMP.JOB
        WHEN 'ANALYST' THEN VPAY := VEMP.SAL * 1.2;
        WHEN 'CLERK' THEN VPAY := VEMP.SAL * 1.3;
        WHEN 'MANAGER' THEN VPAY := VEMP.SAL * 1.4;
        WHEN 'PRESIDENT' THEN VPAY := VEMP.SAL * 1.5;
        WHEN 'SALESMAN' THEN VPAY := VEMP.SAL * 1.6;
    END CASE;
    
    DBMS_OUTPUT.PUT_LINE('사원번호 = ' || VEMP.EMPNO);
    DBMS_OUTPUT.PUT_LINE('사원이름 = ' || VEMP.ENAME);
    DBMS_OUTPUT.PUT_LINE('업무 = ' || VEMP.JOB);
    DBMS_OUTPUT.PUT_LINE('급여 = ' || VEMP.SAL);
    DBMS_OUTPUT.PUT_LINE('실지급액 = ' || VPAY);
END;
/

형식) CASE WHEN 조건식 THEN 명령; ... WHEN 조건식 THEN 명령; ... END CASE;

DECLARE 
    VEMP EMP%ROWTYPE;
    VGRADE VARCHAR2(1);
BEGIN
    SELECT * INTO VEMP FROM EMP WHERE EMPNO = 7788;
    CASE WHEN VEMP.SAL BETWEEN 0 AND 1000 THEN VGRADE := 'E';
         WHEN VEMP.SAL BETWEEN 1001 AND 2000 THEN VGRADE := 'D';
         WHEN VEMP.SAL BETWEEN 2001 AND 3000 THEN VGRADE := 'C';
         WHEN VEMP.SAL BETWEEN 3001 AND 4000 THEN VGRADE := 'B';
         WHEN VEMP.SAL BETWEEN 4001 AND 5000 THEN VGRADE := 'A';
    END CASE;
    DBMS_OUTPUT.PUT_LINE('사원번호 = ' || VEMP.EMPNO);
    DBMS_OUTPUT.PUT_LINE('사원이름 = ' || VEMP.ENAME);
    DBMS_OUTPUT.PUT_LINE('업무 = ' || VEMP.JOB);
    DBMS_OUTPUT.PUT_LINE('급여 = ' || VEMP.SAL);
    DBMS_OUTPUT.PUT_LINE('실지급액 = ' || VGRADE);
END;
/

📌반복문

명령을 반복적으로 실행하기 위한 구문

1) BASIC LOOP : 무한 반복 - 선택문을 이용하여 EXIT명령을 실행해 반복문 종료

--형식)
LOOP
	명령;
    ....
END LOOP;

DECLARE
    I NUMBER(1) :=1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE(I);
        I := I+1;
        IF(I >5) THEN
            EXIT;
        END IF;
    END LOOP;
END;
/

2) FOR LOOP : 반복횟수가 정해져 있는 경우 사용하는 반복문

FOR INDEX_COUNTER (REVERSE) IN LOWER_BOUND..HIGH_BOUND LOOP  
   	명령; 
   	.. 
 END LOOP;

DECLARE
    TOT NUMBER(2) := 0;
BEGIN 
    --FOR LOOP구문의 변수는 반복문에서만 사용가능
    FOR I IN 1..10 LOOP
        TOT := TOT + I;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(TOT);
END;
/

테이블의 다중 검색행의 반복처리 - 내부적 커서(CURSOR) 사용

--형식)
FOR 레코드 변수 IN (다중행 검색 명령) LOOP
	명령;
    ..
 END LOOP;

BEGIN
    FOR VEMP IN (SELECT * FROM EMP) LOOP--행을 하나씩 변수 VEMP에게 전달
        DBMS_OUTPUT.PUT_LINE('사원번호 = ' || VEMP.EMPNO || ', 사원이름 = ' || VEMP.ENAME);
    END LOOP;
END;
/

2) WHILE LOOP : 반복횟수가 정해져 있지 않을 경우 사용하는 반복문 ->오라클에서 횟수가 정해져 있지 않는 경우는 별로 없음.

--형식)
WHILE 조건식 LOOP 
	명령;
    ..
END LOOP;

DECLARE
    I NUMBER(2) := 1;
    TOT NUMBER(2) := 0;
BEGIN
    WHILE I <= 10 LOOP
        TOT := TOT + I;
        I := I + 1;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE(TOT);
END;

📌저장 프로시저 (STORED PROCEDURE)★★

PL/SQL 프로시저에 이름을 부여하여 저장하고 필요한 경우 호출하여 사용하는 기능을 제공
위의 명령문은 그냥 한번 실행하고 끝나는 명령문들임
값을 반환하는 것이 아니라 명령일 뿐임.
자바에서도 오라클서버에접속하여 불러와서 사용할 수 있다.
만들었다고 실행되는것이 아니라, 호출되어야 실행되는 것이다.

--형식)
CREATE [OR REPLACE] PROCEDURE 프로시저명([매개변수 [MOD] 자료형, ...) IS [변수 선언부]
BEGIN 
	명령;
    ...
END;

1) 저장 프로시저 확인 : USER_SOURCE : 저장 프로시저와 함수 정보를 제공하는 딕셔너리

SELECT * FROM EMP2;
CREATE OR REPLACE PROCEDURE DELETE_ALL_EMP2 IS 
BEGIN
    DELETE FROM EMP2;
    COMMIT;
END;
/

--저장된 프로시저 확인
SELECT NAME, TEXT FROM USER_SOURCE WHERE NAME = 'DELETE_ALL_EMP2';

2) 저장프로시저 호출

--형식)
EXECUTE 프로시저명[(값 또는 변수, ...)]

--프로시저 호출
EXECUTE DELETE_ALL_EMP2; --결과 : 전부 다 삭제

만약, 저장프로시저 생성시 컴파일 에러가 발생된 경우 컴파일 로그를 확인하면 된다.

3) 저장 프로시저 삭제

DROP PROCEDURE DELETE_ALL_EMP2;

3) 매개변수 모드

IN : 외부값을 저장 프로시저 에 전달받아 PL/SQL에서 사용할 목적의 매개변수
OUT : PL/SQL 실행 결과값을 저장 프로시저 외부에 전달할 목적의 매개변수
INOUT : IN과 OUT 모드를 모두 제공하는 매개변수

CREATE OR REPLACE PROCEDURE SELECT_EMPNO(
    VEMPNO IN EMP.EMPNO%TYPE,
    VENAME OUT EMP.ENAME%TYPE,
    VJOB OUT EMP.JOB%TYPE,
    VSAL OUT EMP.SAL%TYPE) IS --변수명 생략
BEGIN
    SELECT ENAME, JOB, SAL INTO VENAME, VJOB, VSAL FROM EMP WHERE EMPNO = VEMPNO;
END;

3-1) OUT 매개변수에 의해 제공되는 값을 저장하기 위한 바인딩 변수 선언

바인딩 변수 : 세션에서 사용하는 변수 (일종의 지역변수라고 생각하면 된다)

--형식)
VARIABLE 변수명 자료형;

--EX)
VARIABLE VAR_ENAME VARCHAR2(15);
VARIABLE VAR_JOB VARCHAR2(20);
VARIABLE VAR_SAL NUMBER;

IN매개변수에는 값을 전달하고 OUT매개변수에는 바인딩 변수값을 전달한다?
바인딩 변수에 값을 저장해야 될 경우 변수명 앞에 " : " 을 붙여 사용한다.
자바와 함께 사용하려면 바인딩변수 자리에 자바 변수를 넣으면 자바변수에 저장할 수 있다.

EXECUTE SELECT_EMPNO(7788, :VAR_ENAME, :VAR_JOB, :VAR_SAL);

3-2) 바인딩 변수에 저장된 값 출력

--형식)
PRINT 바인딩변수명;

--EX)
PRINT VAR_ENAME;

📌저장 함수(STORED FUCTION)

저장 프로시저와 유사한 기능을 제공하지만 반드시 값을 반환

1) 저장 함수 생성

--형식)
CREATE [OR REPLACE] FUNCTION 함수명 ([매개변수 [MODE]] 자료형, ...)
	RETURN 자료형 IS [변수 선언부]
BEGIN
 	명령;
    ..
END;

실행부에서 RETURN 명령을 이용하여 반드시 값을 반환해야한다.

CREATE OR REPLACE FUNCTION CAL_SAL(VEMPNO IN EMP.EMPNO%TYPE)
    RETURN NUMBER IS VSAL NUMBER(7,2); --스칼라변수
BEGIN
    SELECT SAL IN VSAL FROM EMP WHERE EMPNO = VEMPNO;
    RETURN (VAL*2.0);
END;
/

2) 반환값을 저장하기 위한 바인딩 변수 선언

VARIABLE VAR_SAL NUMBER;

3)저장함수 호출 : 반환값을 바인딩 변수에 저장한다.

EXECUTE :VAR_SAL := CAL_SAL(7788);

4)바인딩 변수에 저장된 값을 확인

PRINT VAR_SAL;

저장함수는 SQL명령에 포함되어 사용가능하다

SELECT EMPNO, ENAME, SAL,CAL_SAL(EMPNO) 특별수당 FROM EMP;

📌커서

테이블의 검색행을 처리하기 위한 기능을 제공

(1) 묵시적 커서 : 검색 결과가 단일행인 경우에 대해 처리하기 위한 커서

(2) 명시적 커서 : 검색 결과가 다중행인 경우에 대해 처리하기 위한 커서

1) 명시적 커서의 선언 및 사용방법

--형식 )
DECLARE
	CURSOR 커서명 IS 검색명령;
    BEGIN
    	OPEN 커서명;
        FETCH 커서명 INTO 변수명;
        CLOSE 커서명;
    END;

CREATE OR REPLACE PROCEDURE PROCEDURE_CURSOR1 IS
    VDEPT DEPT%ROWTYPE;
    --커서 선언 및 다중행 검색결과 저장
    CURSOR C IS SELECT * FROM DEPT ORDER BY DEPTNO;
BEGIN
    OPEN C; --커서를 사용하겠다.
    --커서에 저장된 다중행 검색결과를 반복문으로 처리
    LOOP
         --커서 위치의 검색행에서 컬럼값을 변수에 저장 : 검색행 처리한 후 커서 위치는 자동으로 아래로 위치함
        FETCH C INTO VDEPT.DEPTNO, VDEPT.DNAME, VDEPT.LOC;
        --커서 위치에 검색행이 존재하지 않을 경우 반복문 종료
        EXIT WHEN C%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE('부서번호 = ' || VDEPT.DEPTNO);
        DBMS_OUTPUT.PUT_LINE('부서이름 = ' || VDEPT.DNAME);
        DBMS_OUTPUT.PUT_LINE('부서위치 = ' || VDEPT.LOC);
    END LOOP;
    CLOSE C;
END;
/

--실행
EXECUTE PROCEDURE_CURSOR1;

2) 묵시적 커서의 선언 및 사용방법

CREATE OR REPLACE PROCEDURE PROCEDURE_CURSOR2 IS
    VDEPT DEPT%ROWTYPE;
    --커서 선언 및 다중행 검색결과 저장
    CURSOR C IS SELECT * FROM DEPT ORDER BY DEPTNO;
BEGIN
    --FOR LOOP를 이용할 경우, 커서에대한 OPEN, FETCH, CLOSE 구문 사용하지 않아도 됨
    FOR VDEPT IN C LOOP
        DBMS_OUTPUT.PUT_LINE('부서번호 = ' || VDEPT.DEPTNO);
        DBMS_OUTPUT.PUT_LINE('부서이름 = ' || VDEPT.DNAME);
        DBMS_OUTPUT.PUT_LINE('부서위치 = ' || VDEPT.LOC);
    END LOOP;
END;
/

--결과는 위와 동일하다.

3) 내부 커서 선언 및 사용방법

CREATE OR REPLACE PROCEDURE PROCEDURE_CURSOR3 IS
  --  VDEPT DEPT%ROWTYPE; -- 생략가능(BEGIN이후 블럭에서만 사용되므로)
BEGIN
    FOR VDEPT IN (SELECT * FROM DEPT ORDER BY DEPTNO) LOOP
        DBMS_OUTPUT.PUT_LINE('부서번호 = ' || VDEPT.DEPTNO);
        DBMS_OUTPUT.PUT_LINE('부서이름 = ' || VDEPT.DNAME);
        DBMS_OUTPUT.PUT_LINE('부서위치 = ' || VDEPT.LOC);
    END LOOP;
END;
/

📌트리거

특정 SQL명령이 실행 될 경우 PL/SQL 프로시저를 이용하여 자동실행되는 기능을 제공함
저장프로시저는 호출을 해야하지만 얘는 특정명령을 실행하면 자동 호출이 된다.

1)트리거 생성

FOR EACH ROW : 생략된 경우 문자 레벨 트리거를 생성하며 선언된 경우 행 레벨 트리거를 생성한다.
문자레벨 트리거 : 이벤트 DML명령이 실행되면 트리거에 작성된 명령이 한번만 실행된다.
행레벨 트리거 : 이벤트 DML명령이 실행되면 트리거에 작성된 명령이 여러번 실행된다.

--형식)
CREATE [OR REPLACE] TRIGGER 트리거명 [BEFORE | AFTER] (INSERT | UPDATE | DELETE)
	ON 테이블명 (FOR EACH ROW) (WITH 조건식) 
BEGIN 
    명령;  --PL/SQL작성
    ...
END;

CREATE OR REPLACE TRIGGER SAWON_INSERT AFTER INSERT ON SAWON --뒤에 INSERT
BEGIN
    DBMS_OUTPUT.PUT_LINE('새로운 사원이 입사하였습니다.');
END;
/

2) 트리거 확인

USER_TRIGGERS - 트리거 정보를 제공하는 딕셔너리

SELECT * FROM USER_TRIGGERS;

3) 테이블에 정보를 삽입/삭제할 경우

CREATE OR REPLACE TRIGGER SAWON_INSERT2 BEFORE INSERT ON SAWON FOR EACH ROW
BEGIN
    --NEW.컬럼명 : 저장행 또는 변경행의 컬럼을 표현
    --OLD.컬럼명 : 삭제행 또는 변경행의 컬럼을 표현
    INSERT INTO BUSEO VALUES(:NEW.BNO, NULL);
END;
/

--사원번호가 6000번인 사원이 새로 입사했는데, 부서번호가 30번 번호가 없으나, 트리거때문에 부서에
--새로운 부서가 자동으로 추가된다
--FOR EACH ROW를 사용했기 때문임
INSERT INTO SAWON VALUES(6000,'전우치',30);

저작자표시 (새창열림)

'💻 수업정리 (2020) > 오라클' 카테고리의 다른 글

[6/30]ConnectionFactory와 ResultSet커서 이용 (0)	2020.06.30
[6/29]JDBC연동 ( + 리플랙션에 대해서 알아두기) (0)	2020.06.29
[6/25] 인라인뷰와 시퀀스, 권한 부여 (0)	2020.06.25
[6/19] 제약조건과 VIEW (0)	2020.06.19
[6/18] TCL과 TRANSATION (0)	2020.06.18