PL/SQL(3)

inthyes·2023년 6월 24일

PL/SQL db mysql oracle sql_developer

DB

목록 보기

3/4

💡 PL/SQL문 활용

프로시저

개요
- 개념
  
  : 매개 변수를 사용할 수 있고 반복해서 사용할 수 있는 이름이 있는 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;
```
- 프로시저생성문오류검사
  - 방법1 : SQL 디벨로퍼 프로시저 개발 도구 사용
  - 방법2 : SQL 오류 검사 방법과 동일
- 프로시저 실행
  - 방법1 : SQL 디벨로퍼 프로시저 개발 도구 사용
  - 방법 2 : 프로시저 호출문 작성
- 프로시저 삭제
  - DROP PROCEDURE 프로시저명
- 주의사항
  1. 반드시 하나의 행만 검색
  2. 검색되는 데이터행이 없거나 2개 이상일 경우 예외 발생
    - TOO_MANY_ROWS: 2개 이상의 데이터 행 추출시
    - NO_DATA_FOUND: 어떤 데이터도 추출하지 못할 때
  3. 다수 개의 데이터행을 검색할 때는 명시적 커서 사용
함수

개요
- 개념 : 매개 변수를 사용할 수 있고 반복해서 사용할 수 있는 이름이 있는 PL/SQL 블록 : 리턴값 있음, IN 매개변수만 사용
- 용도 : 연속 실행 또는 구현이 복잡한 트랜잭션을 수행하는 PL/SQL블록을 데이터베이스에 저장하기 위해 생성
- 장점 : 데이터베이스 내에서 미리 컴파일되어 저장되므로, 실행시간 때 컴파일해야 하는 SQL문보다 빠르게 실행
- 프로시저와의 차이점
  - 결과값을리턴 : 리턴될 값의 데이터 타입을 RETURN문에 선언
  - IN 매개변수만사용가능
형식
- 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;
```
```
- 함수생성문오류검사
  - SQL 디벨로퍼사용시 : 함수개발도구사용
  - 오라클라이브사용시 : 프로시저오류검사방법과동일
- 함수실행
  - SQL 디벨로퍼사용시 : 함수개발도구사용
  - 오라클라이브사용시 : 함수호출문작성
- 함수삭제
  - 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 트리거명

inthyes

이전 포스트

PL/SQL(2)

다음 포스트

PL/SQL(3)

DB

PL/SQL(2)

ORM

0개의 댓글