프로시저
개요
개념
: 매개 변수를 사용할 수 있고 반복해서 사용할 수 있는 이름이 있는 PL/SQL 블록
: 리턴값 없음
용도 : 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL블록을 데이터베이스에 저장하기 위해 생성
장점 : 데이터베이스 내에서 미리 컴파일되어 저장되므로, 실행시간 때 컴파일해야 하는 SQL문보다 빠르게 실행
형식
CREATE문을 사용하여 생성
IS로 PL/SQL의 블록을 시작
지역변수는 IS와 BEGIN사이에 선언
CREATE[OR REPLACE] PROCEDURE 프로시저명
[(매개변수이름 종류 자료형)]
--매개변수가 여러개일 때는 ,로 구분
--종류: IN, OUT, INOUT중 1개 명시
IS
[변수의 선언]
BEGIN
[PL/SQL Block]
--SQL문장, PL/SQL제어 문장
[EXCEPTION]
--예외가 발생할 때 수행하는 문장
END;
매개변수
특징
파라미터 종류
IN
- 호출 환경으로부터 프로시저로 값을 전달
- 상수, 수식 또는 초기화된 변수 사용
- 생략 가능
OUT
- 프로시저로부터 호출 환경으로 값을 전달
- 초기화되지 않은 변수를 매개변수로 사용
- 명시적으로 지정해야 함
INOUT
- 호출 환경에서 프로시저로 값을 전달하고, 다시 프로시저로부터 호출환경으로변경된값을전달
- 초기화된 변수를 사용
- 명시적으로 지정해야 함
프로시저의 생성과 실행
프로시저 생성
- CREATE PROCEDURE 구문을 사용하여 생성
CREATE or replace PROCEDURE
select_ename(v_empno IN number)
IS
v_ename emp.ename%type;
BEGIN
SELECT ename
INTO v_ename
FROM emp
WHERE empno = v_empno;
DBMS_OUTPUT.PUT_LINE('이름 : ' || v_ename);
END;
프로시저생성문오류검사
프로시저 실행
프로시저 삭제
주의사항
함수
개요
형식
CREATE문을 사용하여 생성
IS로 PL/SQL의 블록을 시작
지역변수는 IS와 BEGIN사이에 선언
CREATE[OR REPLACE] FUNCTION 함수명
[(매개변수이름 종류 자료형)]
--매개변수가 여러개일 때는 ,로 구분
RETURN 자료형
--종류: IN, OUT, INOUT중 1개 명시
IS
[변수의 선언]
BEGIN
[PL/SQL Block]
--PL/SQL블록에는 적어도 한 개의 RETURN문이 있어야 함
END;
함수의 생성과 실행
함수생성
- CREATE FUNCTION 구문을 사용하여 생성
```jsx
CREATE or replace FUNCTION get_incsal
(v_empno NUMBER, rate IN NUMBER)
RETURN NUMBER
IS
v_sal emp.sal%type;
BEGIN
SELECT sal
INTO v_sal
FROM emp
WHERE empno = v_empno;
v_sal := v_sal * rate;
RETURN v_sal;
END;
```
함수생성문오류검사
함수실행
함수삭제
- DROP FUNCTION 함수명
****
트리거
개요
트리거란?
: 데이터베이스에 특정한 변경이 가해졌을 때 DBMS가 이에
대응해서 자동적으로 호출하는 일종의 프로시저
: 프로시저와함수는그실행이외부적인실행명령에의해 이루어지는데 반해, 트리거의 실행은 트리거링 사건 (Triggering Event)에의해내부적으로이루어짐
: 트리거를 일으키는 사건(event)
: INSERT, UPDATE, DELETE문의사용에사건을정의할수 있으며이들을실행할때정의된트리거도자동실행
: 데이터베이스에 저장
: 뷰에 대해서가 아니라 테이블에 관해서만 정의
구성
사건(event) : 트리거를 가동
조건(condition) : 트리거 수행 여부 검사
동작 (action) : 트리거가 수행될 때 일어나는 일
예)
CREATE TRIGGER incr_count
BEFORE INSERT ON student --- 사건
FOR EACH ROW
WHEN (:new.age < 18) -- 조건 BEGIN -- 동작
DBMS_OUTPUT.PUT_LINE(’미성년자 : ' || : new.ename);
END;
용도
테이블 생성시 참조무결성과 데이터 무결성 그밖의 다른 제약조건으로 정의할 수 없는 복잡한 요구사항에 대한 제약조건을 생성할 수 있다.
테이블의 데이터에 생기는 작업을 감시, 보안할 수 있다.
테이블에 생기는 변화에 따라 필요한 다른 프로그램을 실행 시킬 수 있다.
형식
```jsx
CREATE [OR REPLACE] TRIGGER 트리거명
BEFORE|AFTER 트리거사건 ON 테이블명
[FOR EACH ROW]
[WHEN (condition)]
PL/SQL block
```
BEFORE : INSERT, UPDATE, DELETE문이실행되기전트리거실행
AFTER : INSERT, UPDATE, DELETE문이실행된후트리거실행
트리거사건 : INSERT, UPDATE, DELETE 중 한 개 이상
FOR EACH ROW : 행트리거
문장 트리거와 행트리거
문장 트리거
: 트리거 사건에 의해 단 한번 실행
: 컬럼의 각 데이터 행 제어 불가능
: 컬럼의 데이터 값에 상관없이 변화가 일어남을 감지하여 실행되는 트리거
CREATE or replace TRIGGER enable_emp
BEFORE
INSERT OR DELETE OR UPDATE
ON EMP
DECLARE - - 변수를 선언할 때 DECLARE문 사용
disable_time EXCEPTION;
BEGIN
IF (TO_CHAR(SYSDATE,'HH24:MI') >= ‘14:00' and
TO_CHAR(SYSDATE,'HH24:MI') <= ‘17:00') THEN
RAISE disable_time;
END IF;
EXCEPTION
WHEN disable_time THEN
RAISE_APPLICATION_ERROR(-20500, '업무시간에만 사용 가능합니다.');
END;
행 트리커
: 컬럼의 각각의 데이터 행에 변화가 생길 때마다 실행
: 변화가 생긴 데이터 행의 실제 값 제어 가능
: 데이터 행의 실제 값을 수정, 변경 또는 저장할 때 사용
CREATE or replace TRIGGER trigger_test
BEFORE UPDATE ON dept
FOR EACH ROW -- 행 트리거
BEGIN
DBMS_OUTPUT.PUT_LINE('변경 전 컬럼 값 : ' || :old.dname);
DBMS_OUTPUT.PUT_LINE('변경 후 컬럼 값 : ' || :new.dname);
END;
행트리거의 컬럼값 참조
- “:old”, “:new” 연산자 사용
- INSERT문 : 입력할 데이터값) :new.column_name에 지정 (단,column_name은 테이블의 컬럼 이름)
- UPDATE문
변경하기전컬럼데이터값) :old.column_name 수정할새로운데이터값) :new.column_name
- DELETE문
삭제되는컬럼) :old.column_name
트리거의 생성과 실행
트리거 생성 : CREATE TRIGGER구문을 사용하여 생성
트리거 생성문 오류 검사
트리거실행 : 트리거에서 명시된 테이블 변경시 자동 실행
트리거삭제 : DROP TRIGGER 트리거명