PL/SQL
(Procedural Language extension to SQL)
: 관계형 데이터 베이스에서 사용되는 ORACLE 의 표준 데이터 엑세스 언어.
: SQL에서는 할수 없는 절차적 프로그래밍 기능을 가지고 있음 ==> SQL 단점 보완.
- 구조
- DECLARE (선언부) : 실행에 필요한 변수, 상수, 커서 등을 선언. ==> 선택적
- BEGIN (실행부 시작) : SQL, 반복문,조건문등을 실행. ==> 필수적
- EXCEPTION (예외처리부): 오류를 정의하고 처리. ==> 선택적
- END (실행부 끝) ==> 필수적
DECLARE
변수 + 자료형 + (조건) := 데이터값
BEGIN
출력 실행문
END ;
/ (블럭 끝)
: 블록내에서는 문장끝마나 ; 을 사용.
: END 뒤에 ; 을 사용하여 블록의 끝을 명시.
: -- 단일행 주석 , /* */ 여러행 주석
: 행에 / 가 있으면 종결됨.
ex)
- 기본구조
declare
v_deptno number(2) not null default 10;
v_tax constant number(1) :=3;
begin
dbms_output.put_line('v_deptno :'|| v_deptno);
dbms_output.put_line('v_tax: '|| v_tax);
end;
/- 다른테이블행의 자료형을 참조할 때
declare
v_dept_row dept%ROWTYPE(TYPE); // 변수명 + 테이블 이름 %ROWTYPE (TYPE)
begin
select deptno, dname, loc INTO v_dept_row //ROWTYPE 은 모든 행을다 참조해서 다 가져옴.
from dept
where deptno=40; // 40번 부서의 정보를 v_dept_row 에 넣기.
dbms_output.put_line('deptno : '||v_dept_row.deptno);
dbms_output.put_line('dname : '||v_dept_row.dname);
dbms_output.put_line('loc : '||v_dept_row.loc);
end;
/- 다른테이블의 한 행만 참조할때
declare
v_deptno dept.deptno%TYPE := 10; // 변수명 + 테이블이름.참조할행이름%TYPE := 데이터값.
begin
case v_deptno
when 10 then dbms_output.put_line('DNAME: ; ACCONTING');
when 20 then dbms_output.put_line('DNAME: ; RESEARCH');
when 30 then dbms_output.put_line('DNAME: ; MANAGER');
when 40 then dbms_output.put_line('DNAME: ; SALES');
ELSE dbms_output.put_line('DNAME: ; N/A ');
end case;
end;
/- IF 조건절
declare
v_number number :=13;
begin
IFmod(v_number,2) =1 //v_number를 2로 나눴을때 나머지값이 1이라면
THEN dbms_output.put_line('v_number는 홀수입니다.');
ELSE dbms_output.put_line('v_number는 짝수입니다.');
ENDN IF;
end;
/- LOOP 반복
declare
v_num number := 0;
begin
LOOP
dbms_output.put_line('현재 v_num: '|| v_num);
v_num := v_num + 1; //1씩 증가.
EXIT WHEN v_num > 4; // 4미만일때 까지 반복
END LOOP;
end;
/- WHILE 반복
declare
v_num number := 0;
begin
WHILE v_num < 4 LOOP //LOOP가 4까지 돌동안
dbms_output.put_line('현재 v_num: '|| v_num);
v_num := v_num + 1;
END LOOP;
end;
/- FOR 반복
declare
v_num number := 0;
begin
FOR i in 0..10 LOOP // FOR 변수 IN REVERSE : 거꾸로반복 10에서 0으로 갈때
continue when mod(i,2)=1; // 홀수 일때 처음으로 돌아가라. ↔ when mod(i,2)=0 ==> 짝수일때
dbms_output.put_line('현재 i의 값:'||i); // 짝수만 출력.
END LOOP;
end;
/레코드 (RECORD)
: 여러 컬럼의 값을가지는 하나의 열.
: 가져오고 싶은 컬럼만 골라서 참조할 수 있음.
: 자료형이 다른 여러 데이터를 저장하는 레코드
: TYPE 레코드이름 IS RECORD(변수 이름 자료형 NOTNULL :=값 또는 표현식)
ex)
- 레코드 타입 정의
declare
TYPE rec_dept IS RECORD( //TYPE 레코드이름 IS RECORD
deptno number(2)not null :=99,
dname dept.dname%type, //참조해온 자료형.
loc dept.loc%type);
aa rec_dept; //변수명 레코드이름 : rec_dept라는 레코드타입의 aa생성
begin
aa.deptno :=99; // 레코드 타입의 변수에 데이터를 저장.
aa.dname := 'DATABASE';
aa.loc :='SEOUL';
dbms_output.put_line('deptno : '||aa.deptno); -- 데이터 출력
dbms_output.put_line('dname: '||aa.dname);
dbms_output.put_line('loc : '||aa.loc);
-----------------------------------------------
aa.deptno :=50;
aa.dname := 'DB';
aa.loc :='SEOUL';- 값을새로 넣을때
insert into 테이블이름 values aa;- 수정할 때
update dept_record
set row = aa
where deptno = 99;
end;
/* 중첩 레코드
: 하나의 레코드를 선언하고 또 다른 레코드를 선언할 때 두 번째 레코드의 필드 타입을 첫 번째 레코드 타입으로 선언하는 형태
declare
TYPE rec_dept is record(
deptno number(2)not null :=99,
dname dept.dname%type,
loc dept.loc%TYPE);
TYPE rec_emp is record( // 중첩레코드
empno emp.empno%type,
ename emp.ename%type,
dinfo rec_dept); // 변수 + 다른 레코드이름 : 위의 레코드 재정의 하며 포함.
bb rec_emp;
begin
select e.empno,e.ename,d.deptno, d.dname,d.loc // 중첩된 두개의 레코드에 포함된 테이블행 목록.
into bb.empno, bb.ename, bb.dinfo.deptno, bb.dinfo.dname, bb.dinfo.loc
from emp e, dept d
where e.deptno = d.deptno
and e.empno = 7788;
dbms_output.put_line('empno : '||bb.empno);
dbms_output.put_line('ename : '||bb.ename);
dbms_output.put_line('deptno : '||bb.dinfo.deptno);
dbms_output.put_line('dname : '||bb.dinfo.dname);
dbms_output.put_line('loc : '||bb.dinfo.loc);
end;
/컬렉션 (COLLECTION)
: 자료형이 같은 여러 데이터를 저장.
: 생성자를 이용해서 초기화 가능.
- 연관배열( associative array )
- 중첩 테이블
- VARRAY
1.연관배열 ( associative array )
: 키(INDEX)+ 값 ==>INDEX -BY테이블 이라고도 함.
: TYPE +이름+ IS TABLE OF +자료형 + INDEX BY + 인덱스타입
ex)
declare
TYPE itab_ex IS TABLE OF varchar2(20)
INDEX BY pls_integer;
text_arr itab_ex; // 테이블 타입의 배열 객체 생성
begin
text_arr(1):='1 data';
text_arr(2):='2 data';
text_arr(3):='3 data';
text_arr(4):='4 data';
dbms_output.put_line('1번값 :'||text_arr(1));
dbms_output.put_line('2번값 :'||text_arr(2));
dbms_output.put_line('3번값 :'||text_arr(3));
dbms_output.put_line('4번값 :'||text_arr(4));
end;
/* 레코드 활용
- 열의 타입 참조
declare
type rec_ex is record ( //dept 테이블 전체행의 타입을 참조하는 레코드 생성
deptno dept.deptno%type, 1열 : number 2열: varchar2 3열 : varchar2
dname dept.dname%type,
loc dept.loc%type );
type itab_ex is table of rec_ex // 생성된 레코드의 구조로 테이블 생성.
index by pls_integer; // 인덱스 타입 설정
dept_arr itab_ex;
idx PLS_INTEGER := 0;
begin
for i in (select deptno, dname, loc from dept)loop
idx:=idx+1;
dept_arr(idx).deptno := i.deptno;
dept_arr(idx).dname := i.dname;
dept_arr(idx).loc := i.loc;
dbms_output.put_line(
dept_arr(idx).deptno||':'||dept_arr(idx).dname||':'||dept_arr(idx).loc );
end loop;
end;
/
- 행 전체 타입 참조
: 레코드를 따로 생성하지 않고 바로 테이블로 생성해서 배열로
declare
type itab_ex is table of dept%rowtype
index by pls_integer;
dept_arr itab_ex;
idx PLS_INTEGER := 0;
begin
for i in (select * from dept)loop
idx:=idx+1;
dept_arr(idx).deptno := i.deptno;
dept_arr(idx).dname := i.dname;
dept_arr(idx).loc := i.loc;
dbms_output.put_line(
dept_arr(idx).deptno||':'||dept_arr(idx).dname||':'||dept_arr(idx).loc );
end loop;
end;
/* 여러가지 출력방법
declare
type itab_ex is table of varchar2(20)
index by pls_integer;
text_arr itab_ex;
begin
text_arr(1):='1 data';
text_arr(2):='2 data';
text_arr(3):='3 data';
text_arr(50):='4 data';
dbms_output.put_line('1번값 :'||text_arr.count); -- 4 인덱스 갯수
dbms_output.put_line('2번값 :'||text_arr.first); -- 1 첫번째 인덱스 번호
dbms_output.put_line('3번값 :'||text_arr.last); -- 50 마지막 인덱스 번호
dbms_output.put_line('4번값 :'||text_arr.prior(50)); -- 3 50번인덱스의 앞 인덱스 번호
dbms_output.put_line('4번값 :'||text_arr.next(50)); -- null 50 인덱스 다음 인덱스 번호
end;
/2. 중첩테이블(Nested Table)
: TYPE 중첩_테이블명 IS TABLE OF 값타입
: 실제 테이블이 아닌 컬렉션 타입의 한 종류.
: 크기에 제한이 없고, 연관 배열과 같지만 숫자형 인덱스만사용할수 있음.
: 생성자를 사용하며 일반 테이블의 컬럼타입으로 사용될수 있다는 점은 VARRAY와 같음.
3. VARRAY(Variable-Size Array)
: TYPE VARRAY명 IS VARRAY(최대 크기) OF 요소값_타입
: 가변길이의 배열로 크기에 제한이 있음.
: 선언할 때 크기를 지정하면 이보다 큰 수로 요소를 만들 수 없음.
커서(CURSOR)
: 쿼리문(DML)에 의해 얻게되는 결과들의 집합을 저장하는 메모리 공간 (포인터) ==> SELECT문과 연관.
: 오라클 서버 프로세스 내부의 PRIVATE SQL AREA에 저장되어 요청할때마다 계속반환해줌.
* 커서의 속성
커서이름 %FOUND: 추출된 행이 있으면 TRUE, 없으면 FALSE커서이름 %NOTFOUND:추출된 행이 없으면 TRUE, 있으면 FALSE커서이름 %ISOPEN: 커서가 오픈이면 TRUE커서이름 %ROWCOUNT: FETCH에 의해 추출된 행의 수를 세어줌.
- 명시적(Explicit) 커서 : 이름을 붙여서 생성하며, 주로 여러개의 행을 처리할 때 사용.
- 묵시적(Implicit) 커서 : 오라클에서 자동으로 선언해주며, 사용자는 생성 유무를 알 수 없음.
커서 사용방법
1) 커서 선언
2) 커서 열기
3) 읽어온 데이터 사용 (Fetch)
4) 커서 닫기
==> 명시적 커서
<기본구조>
declare
- 커서데이터를 입력할 변수 선언.
CURSOR 커서이름 IS SQL문 ; -- 선언
begin
OPEN 커서이름;
FETCH 커서이름 INTO 변수 -- 가져올 내용
CLOSE 커서이름;
end;
/ex)
- 한 행만 가져올 때
declare
dept_row dept%rowtype;
CURSOR c1 IS
select * from dept where deptno= 40; // 가져올 행을 지정.
begin
OPEN c1;
FETCH c1 INTO dept_row;
dbms_output.put_line('부서번호 '||': '|| dept_row.deptno);
dbms_output.put_line('부서이름 '||': '|| dept_row.dname);
dbms_output.put_line('부서위치 '||': '|| dept_row.loc);
CLOSE c1;
end;
/- 내용 전체를 가져올때
declare
dept_row dept%rowtype;
CURSOR c1 IS
select * from dept;
begin
OPEN c1;
LOOP // 루프는 fetch를 감싸야한다.
FETCH c1 INTO dept_row;
EXIT when c1%NOTFOUND; // c1에 내용이 없을 때까지 루프 (종료조건을 항상 적어줘야함)
dbms_output.put_line('부서번호 '||': '|| dept_row.deptno||', '||
'부서이름 '||': '|| dept_row.dname ||', '||
'부서위치 '||': '|| dept_row.loc);
end LOOP;
CLOSE c1;
end;
/- 내용 전체 가져올때 (FOR사용)
: ROWTYPE 가져올필요 없고, 커서만 선언하면됨. ==> 가장 간단해서 실무에서 많이 쓰임.
declare
cursor c1 is
select * from dept;
begin
FOR c1_rec IN c1 loop //(자동 OPEN, FETCH, CLOSE)
dbms_output.put_line('deptno:'||c1_rec.deptno);
dbms_output.put_line('dname:'||c1_rec.dname);
dbms_output.put_line('loc:'||c1_rec.loc);
end loop;
close c1;
end;
/- 사용자가 원하는 항목만 출력할 때
: 파라메터 활용 (클래스 안의 메소드처럼 입력값을 받을 변수를 생성)
DECLARE
-- 커서 데이터를 입력할 변수 선언
deptrow DEPT%ROWTYPE;
-- 명시적 커서 선언(Declaration)
CURSOR c1 (p_deptno DEPT.DEPTNO%TYPE) IS // 사용자가 원하는 행을 입력받기.
SELECT * FROM DEPT
WHERE DEPTNO = p_deptno;
BEGIN
-- 10번 부서 처리를 위해 커서 사용
OPEN c1 (10);
LOOP
FETCH c1 INTO deptrow;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line('10번 부서 - DEPTNO : ' || deptrow.DEPTNO
|| ', DNAME : ' || deptrow.DNAME
|| ', LOC : ' || deptrow.LOC);
END LOOP;
CLOSE c1;
-- 20번 부서 처리를 위해 커서 사용
OPEN c1 (20);
LOOP
FETCH c1 INTO deptrow;
EXIT WHEN c1%NOTFOUND;
dbms_output.put_line('20번 부서 - DEPTNO : ' || deptrow.DEPTNO
|| ', DNAME : ' || deptrow.DNAME
|| ', LOC : ' || deptrow.LOC);
END LOOP;
CLOSE c1;
END;
/
DECLARE
-- 사용자가 입력한 부서 번호를 저장하는 변수선언
deptno DEPT.DEPTNO%TYPE;
-- 명시적 커서 선언(Declaration)
CURSOR c1 (p_deptno DEPT.DEPTNO%TYPE) IS
SELECT * FROM DEPT
WHERE DEPTNO = p_deptno;
BEGIN
-- INPUT_DEPTNO에 부서 번호 입력받고 deptno에 대입
deptno := &INPUT_DEPTNO; // scanner와 같은 기능, & = 주소값지정하는 포인터
-- 커서 FOR LOOP 시작. c1 커서에 deptno를 대입
FOR c1_rec IN c1(deptno) LOOP
DBMS_OUTPUT.PUT_LINE('DEPTNO : ' || c1_rec.DEPTNO
|| ', DNAME : ' || c1_rec.DNAME
|| ', LOC : ' || c1_rec.LOC);
END LOOP;
END;
/ ===> 묵시적 커서
: PL/SQL 내 DML 명령중 INSERT, DELETE, UPDAE 를 사용할 때 유리.
: 내부에서 자체적으로 상태를 체크 할 수 있는 속성을 제공함.
ex)
BEGIN
UPDATE dept SET dname='DATABASE'
WHERE deptno = 50;
dbms_output.put_line('갱신된 행의 수 : ' || SQL%ROWCOUNT); // 갱신이 되었다면 +1
IF (SQL%FOUND) THEN //DEPTNO가 40인 행이 있다면 ( 갱신 할 행이 존재 한다면)
dbms_output.put_line('갱신 대상 행 존재 여부 : true');
ELSE
dbms_output.put_line('갱신 대상 행 존재 여부 : false');
END IF;
IF (SQL%ISOPEN) THEN //커서가 열려 있다면 ==>자동 CLOSE 로 항상FALSE
dbms_output.put_line('커서의 OPEN 여부 : true');
ELSE
dbms_output.put_line('커서의 OPEN 여부 : false');
END IF;
END;
/ 예외처리
: 오라클에서 SQL 또는 PL/SQL이 정상저으로 수행되지 못했을 때(ERROR) 의 상황 처리
==> 프로그램의 정상적인 종료를 위해
- 문법이 잘못되었거나 오타로 인한 오류
- 런타임오류 /실해오류 (Exception:예외)
ex)
- 데이터 타입 오류
declare
v_wrong number;
begin
select dname into v_wrong // dname 과 v_wrong 은 타입이 달라서 오류
from dept where deptno=10;
dbms_output.put_line('예외가 발생하면 다음문장은 실행 안됨'); // 실행X
EXCEPTION //BEGIN과 END 사이에 예외처리
when VALUE_ERROR then
dbms_output.put_line('예외처리 : 수치 또는 값 오류 발생 ');
end;
/PL/SQL활용
프로시저: 특정 로직을 처리. (결과 반환 X)함수: 프로시저로 처리한 결과를 반환.패키징: 프로시저를 그룹화.트리거: 이벤트가 발생했을 때 자동으로 연달아 수행하도록 구현.
프로시저
: 특정 로직을 처리하기만 하고 결과값을 반환하지 않는 서브 프로그램.
: 업무를 수행하기 위한 절차 하나의 함수(메소드) 처럼 실행하기 위한 쿼리의 집합
: SQL 문 사용 불가.
: 익명 블록에서도 사용 가능.
ex)
<기본 구조>
CREATE OR REPLACE PROCEDURE 프로시저 이름 ( 파라미터, 파라미터2...)
IS AS 변수와 데이터타입 +데이터 값;
BEGIN
실행부 ;
EXCEPTION
예외처리부 ;
END 프로시저 이름 ;
/- 프로시저 생성
CREATE OR REPLACE PROCEDURE pro_noparam
IS
V_EMPNO NUMBER(4) := 7788;
V_ENAME VARCHAR2(10);
BEGIN
V_ENAME := 'SCOTT';
dbms_output.put_line('V_EMPNO : ' || V_EMPNO);
dbms_output.put_line('V_ENAME : ' || V_ENAME);
END;
/- 프로시저 실행
==> 한번만 사용할때
EXECUTE pro_noparam; ==> 블록에서 순차적으로 사용할때
begin
PRO_NOPARAM;
end;
/ - 프로시저 확인하기 (USER_SOURCE 테이블에 저장.)
select * from USER_SOURCE
where name = 'PRO_NOPARAM';
select TEXT from USER_SOURCE (텍스트 타입으로 조회 )
where name = 'PRO_NOPARAM';-
프로시저 삭제
DROP PROCEDURE PRO_NOPARAM; -
프로시저 수정
: 프로시저는 수정할 때 ALTER 를 사용하지 않고 CREATE OR REPLACE 로 수정하여 재생성.
==>ALTER PROCEDURE: 프로시저를 재 컴파일 할때 사용하며 작성한 내용을 변경하지 않는다.
ex) 프로시저 활용
- 파라미터 입력
CREATE OR REPLACE PROCEDURE pro_noparam_in
(
param1 in number, // 지정하지 않으면 기본값으로 프로시저 호출시 입력받은 값을 넣음.
param2 number,
param3 number:=3,
param4 number default 4
)
IS
BEGIN
dbms_output.put_line('param1 : ' || param1);
dbms_output.put_line('param2 : ' || param2);
dbms_output.put_line('param3 : ' || param3);
dbms_output.put_line('param4 : ' || param4);
END;
/==> 위치 지정 방식 : 지정한 파라미터 순서대로 값 지정
execute pro_noparam_in(10,9,8,7);
execute pro_noparam_in(10,9);
execute pro_noparam_in(10); // param2 는 비어있기 때문에 지정해주지 않으면 출력 X ==> 오류==> 이름 지정 방식 : => (비교 연산자 아님)연산자로 파라미터 이름을 명시하여 값 지정.
execute pro_noparam_in( param1 => 10, param2 => 20 );- 파라미터 출력
CREATE [OR REPLACE] PROCEDURE pro_noparam_out
(
in_empno IN EMP.EMPNO%TYPE, // empno 입력받아서
out_ename OUT EMP.ENAME%TYPE, // empno 에 해당하는 ename 과 sal 출력
out_sal OUT EMP.SAL%TYPE
)
IS
BEGIN
SELECT ENAME, SAL INTO out_ename, out_sal // 가져올 ename과 sal을 내보내기 위해 넣어줌.
FROM EMP
WHERE EMPNO = in_empno;
END pro_noparam_out;
/
DECLARE
v_ename EMP.ENAME%TYPE; // out으로 나온 값을 받기 위해 지정
v_sal EMP.SAL%TYPE;
BEGIN
pro_param_out(7788, v_ename, v_sal); // 입력값에 대한 출력값을 파라미터로 받아서 출력
DBMS_OUTPUT.PUT_LINE('ENAME : ' || v_ename);
DBMS_OUTPUT.PUT_LINE('SAL : ' || v_sal);
END;
/- 입력과 출력을 동시에
CREATE OR REPLACE PROCEDURE pro_noparam_inout
(
inout_no IN OUT NUMBER
)
IS
BEGIN
inout_no := inout_no * 2;
END pro_noparam_inout;
/
declare
no number ;
begin
no :=5;
pro_noparam_inout(no);
dbms_output.put_line(no);
end;
/함수 (Function)
-
프로시저와의 차이점.
: SQL 문과 서브 프로그램 내에서 실행이 가능함.
: 변수를 사용해서 EXECUTE 명령이 가능.
: 파라미터를 지정한 IN 모드만 가능 (생략가능)
: RETURN 절 또는 RETURN 문을 통해 결과값 반환. ( 반드시 하나의 값을 반환해야함). -
내장함수 -
사용자 지정함수
ex)
<기본 구조>
CREATE [OR REPLACE] FUNCTION 이름
(파라미터1 [IN] 자료형1,
파라미터2 [IN] 자료형2.....)
RETURN 자료형 ;
IS | AS
선언부 ;
BEGIN
실행부 ;
RETURN (반환값); // 위의 RETURN 과 같은 자료형으로 반환.
EXCEPTION
예외처리부;
END [함수이름];
/- 함수 생성
CREATE OR REPLACE FUNCTION func_aftertax(sal IN NUMBER) // number 타입의 파라미터 입력
RETURN NUMBER // number 타입으로 리턴
IS
tax NUMBER := 0.05;
BEGIN
RETURN (ROUND(sal - (sal * tax))); // 입력값에대한 실행부분
END func_aftertax;
/- 함수 실행
DECLARE
aftertax number;
begin
aftertax := func_aftertax(3000);
dbms_output.put_line( 'after-tax income : '|| aftertax);
end;
/- 함수 활용
select func_aftertax(3000) from dual; // SQL 문으로도 출력 가능
select empno, ename, sal ,func_aftertax(sal)as aftertax from emp; 트리거(Trigger)
: DB 안의 특정 상황이 발생할 경우 자동으로 실행되는 기능.
-트리거 사용의 장점
1. 연관된 여러 작업을 간편하게 수행 할 수 있음.
2. 복잡한 규칙의 정의가 가능해서 더욱 섬세한작업이 가능.
3. 데이터 변경에 대한 모든 정보를 기록 => 데이터의 보안성과 안정성이 보장 되고,인터럽트 처리능력 상승.
DML 트리거DDL 트리거INSTEAD OF 트리거: 뷰에 사용됨.SIMPLE 트리거: timing point 에 직접 걸어서
==> 트리거 문장이 실행되기 전 or 후 , 행에 영향을 미치기 전 or 후복합트리거: SIMPLE 트리거를 여러 시점에
1. DML 트리거
: INSERT, UPDATE, DELETE 문에 사용되는 트리거
< 기본구조 >
CREATE OR REPLACE TRIGGER 트리거 이름 // 트리거 생성
BEFORE | AFTER // 트리거 작동 timing point 설정.
INSERT | UPDATE | DELETE ON 테이블 이름 // 지정 테이블에 작동할 트리거의 DML 명령어.
REFERENCING Old as old | New as new // DML 로 변경되는행의 전, 후의 값을 참조 ( 생략가능)
FOR EACH ROW WHEN 조건식 // 트리거가 작동하는 문장(명령어)의 반복횟수를 설정.
FOLLOWS 트리거 이름 1, 2,3 ... // 관련트리거 실행 순서
ENABLE | DISABLE // 트리거의 활성 | 비활성화 상태 지정.DECLARE
선언부
BEGIN
실행부
END;
/
ex)
- 명령어 실행 전에 제한할때
CREATE OR REPLACE TRIGGER trg_emp_nodml_weekend
BEFORE
INSERT OR UPDATE OR DELETE emp_trg
begin
if to_char (sysdate, 'DY') in ('토','일') then
if inserting then raise_application_error(-20000, '주말 사원 정보 추가 불가');
elsif updating then raise_application_error(-20000,'주말 사원 정보 수정 불가');
elsif deleting then raise_application_error(-20000,'주말 사원 정보 삭제 불가');
else raise_application_error(-20000,'주말 사원 정보 변경 불가');
end if;
end if;
end;
/- 명령어 실행후
CREATE TABLE EMP_TRG_LOG( // 변경사항을 저장할 테이블 생성
TABLENAME VARCHAR2(10), -- DML이 수행된 테이블 이름
DML_TYPE VARCHAR2(10), -- DML 명령어의 종류
EMPNO NUMBER(4), -- DML 대상이 된 사원 번호
USER_NAME VARCHAR2(30), -- DML을 수행한 USER 이름
CHANGE_DATE DATE -- DML이 수행된 날짜
);create or replace trigger trg_emp_log
after -- emp_trg 테이블에 대한 DML 명령어 적용 후에 작동.
insert or update or delete on emp_trg
for each row
begin
if inserting then
insert into emp_trg_log
values('EMP_TRG','INSERT', :new.empno,
sys_context('USERENV','SESSION_USER'),sysdate);
elsif updating then
insert into emp_trg_log
values('EMP_TRG','UPDATE', :old.empno,
sys_context('USERENV','SESSION_USER'),sysdate); // sys_context: 세션정보를 얻어오는 함수
// 'USETENV' : 현재 세션의 환경정보를 반환하는 네임스페이스(오라클)
elsif deleting then
insert into emp_trg_log
values('EMP_TRG','DELETE', :old.empno,
sys_context('USERENV','SESSION_USER'),sysdate);
end if ;
end;
/