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

📌객체권한

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

없는게 아니라 권한이 없어서 KIM에게는 DEPT테이블이 보이지 않는다

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

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

객체사용을 부여받은 후 KIM계정이 DEPT테이블에 접근

객체 사용을 부여받은 후 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에게 권한을 부여하면 자동으로 계정이 생성된다(비밀번호를 꼭 설정해주어야함)

LEE로 접속하여 CREATE TABLE명령 실행

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