데이터베이스 -17

김정현·2024년 5월 20일

procedure 저장 서브프로그램 트리거 프로시저 함수

데이터베이스

목록 보기

16/16

저장 서브프로그램

:PL/SQL로 만든 프로그램을 주기적으로 또는 필요할 때마다 여러 번 사용할 목적으로 이름을 지정하여 오라클에 저장해 두는 PL/SQL 프로그램

장점
익명 블록과 달리 저장 서브프로그램은 오라클에 저장하여 공유할 수 있으므로 메모리,성능,재사용성 등 여러 면에서 장점

종류

-저장 프로시저(stored procedure) : 일반적으로 특정 처리 작업 수행을 위한 서브프로그램으로 SQL문에서는 사용불가

-저장 함수(stored function) : 일반적으로 특정 연산을 거친 결과 값을 반환하는 서브프로그램으로 SQL문에서 사용

-패키지(package) : 저장 서브프로그램을 그룹화하는 데 사용

-트리거(trigger) : 특정 상황(이벤트)이 발생할 때 자동으로 연달아 수행할 기능을 구현하는 데 사용

프로시저

1.파라티머를 사용하지 않는 프로시저

문법

CREATE [OR REPLACE(1)] PROCEDURE 프로시저 이름
IS | AS 
    선언부 
BEGIN 
    실행부
EXCEPTION - 
    예외 처리부
END [프로시저 이름];

프로시저 실행하기

CALL 프로시저명(값, ...);

랑

BEGIN 
	PRO_NOPARAM();
END

같음

프로지서 내용 확인
USER_SOURCE : 사전 테이블
프로시저 삭제하기

DROP PROCEDURE 프로시저명

2.파라미터를 사용하는 프로시저

문법

CREATE [OR REPLACE(1)] PROCEDURE 프로시저 이름
[(파라미터 이름1 [modes] 자료형 [ := | DEFAULT 기본값],
  파라미터 이름2 [modes] 자료형 [ := | DEFAULT 기본값],
  ...
  파라미터 이름N [modes] 자료형 [ := | DEFAULT 기본값]
)]
IS | AS
    선언부
BEGIN
    실행부
EXCEPTION
    예외 처리부
END [프로시저 이름];

EX)

CREATE OR REPLACE PROCEDURE PRO_PARAM_IN (
	P_DEPTNO NUMBER,
	P_JOB VARCHAR2
)
IS 
	CURSOR C1 IS 
		SELECT * FROM EMP 
			WHERE DEPTNO = P_DEPTNO AND JOB = P_JOB;
BEGIN 
	FOR d IN C1 LOOP
		DBMS_OUTPUT.PUT_LINE('EMPNO : ' || d.EMPNO);
		DBMS_OUTPUT.PUT_LINE('ENAME : ' || d.ENAME);
		DBMS_OUTPUT.PUT_LINE('JOB : ' || d.JOB);
		DBMS_OUTPUT.PUT_LINE('---------------------');	
	END LOOP;
	
END PRO_PARAM_IN;

파라미터 모드

IN : 지정하지 않으면 기본값으로 프로시저를 호출할 때 값을 입력
OUT : 호출할 때 값을 반환.
IN OUT : 호출할 때 값을 입력받은 후 실행 결과 값을 반환

3) IN 모드 파라미터

필요한 값을 직접 입력받는 형식의 파라미터를 지정할 때
기본값, 생략 가능

4) OUT 모드 파라미터

프로시서 실행 후 호출한 프로그램으로 값을 반환.

//부서별 급여 평균 구하기

CREATE OR REPLACE PROCEDURE PRO_PARAM_OUT(
	P_DEPTNO EMP.DEPTNO%TYPE,
	O_SUM OUT NUMBER,
	O_AVG OUT NUMBER
)
IS

BEGIN 
	SELECT SUM(SAL), ROUND (AVG(SAL), 2) INTO O_SUM, O_AVG 
	FROM EMP WHERE DEPTNO = P_DEPTNO;
END PRO_PARAM_OUT;

DECLARE
	V_SUM NUMBER;
	V_AVG NUMBER;
BEGIN
	PRO_PARAM_OUT(20, V_SUM, V_AVG);
	DBMS_OUTPUT.PUT_LINE('SUM : ' || V_SUM);
	DBMS_OUTPUT.PUT_LINE('AVG : ' || V_AVG);
END;

5) IN OUT 모드 파라미터

IN, OUT으로 선언한 파라미터 기능을 동시에 수행
값을 입력받을 때와 프로시저 수행 후 결과 값을 반환할 때 사용

//입력 받은 값을 제곱하여 출력함

CREATE OR REPLACE PROCEDURE PRO_PARAM_INOUT(
	P_NUM IN OUT NUMBER
) 
IS 
BEGIN 
	
	P_NUM := P_NUM * P_NUM;

END PRO_PARAM_INOUT;

SELECT * FROM USER_ERRORS;

DECLARE 
	V_NUM NUMBER := 10;
BEGIN
	PRO_PARAM_INOUT(V_NUM);

	DBMS_OUTPUT.PUT_LINE('V_NUM: ' || V_NUM);
END;

프로시저 오류 정보 확인

1) SHOW ERRORS / sqlplus

2) USER_ERRORS : 사전 테이블

SELECT * FROM USER_ERRORS;

함수

문법

CREATE [OR REPLACE] FUNCTION 함수 이름
[(
   파라미터 이름1 [IN] 자료형1, - (1) 
   파라미터 이름2 [IN] 자료형2,
   ...
   파라미터 이름N [IN] 자료형N  
)]
RETURN 자료형 - (2)
IS | AS 
    선언부 
BEGIN 
    실행부
    RETURN (반환 값); - (3) 
EXCEPTION
    예외 처리부 
END [함수 이름];

EX)

CREATE OR REPLACE FUNCTION FUNC_AFTERTAX(
	P_SAL NUMBER
)RETURN NUMBER
IS
	TAX NUMBER := 0.05;
	SAL NUMBER;
BEGIN 	
	
	SAL := ROUND(P_SAL - P_SAL * TAX);

	RETURN SAL;

END FUNC_AFTERTAX;

SELECT * FROM USER_ERRORS;

DECLARE
	AFTERTAX NUMBER;
BEGIN
	AFTERTAX := FUNC_AFTERTAX(2500);
	DBMS_OUTPUT.PUT_LINE('세후 급여 : ' || AFTERTAX);
END;

SELECT FUNC_AFTERTAX(2500) FROM DUAL;

함수 삭제하기

DROP FUNCTION 함수명;

패키지

기능 면에서 연관성이 높은 프로시저, 함수 등 여러 개의 PL/SQL 서브프로그램을 하나의 논리 그룹으로 묶어 통합,관리하는 데 사용하는 객체

패키지 명세

패키지 명세는 패키지에 포함할 변수, 상수, 예외, 커서 그리고 PL/SQL 서브프로그램을 선언하는 용도로 작성,
패키지 명세에 선언한 여러 객체는 패키지 내부뿐만 아니라 외부에서도 참조

CREATE [OR REPLACE] PACKAGE 패키지 이름
IS | AS 
    서브프로그램을 포함한 다양한 객체 선언
END [패키지 이름];

EX)

CREATE OR REPLACE PACKAGE PKG_EMP
IS 
	FUNCTION FUNC_AFTERTAX(P_SAL NUMBER) RETURN NUMBER;
	PROCEDURE PRO_SEARCH_EMP(P_DEPTNO EMP.DEPTNO%TYPE, P_JOB EMP.JOB%TYPE);
END PKG_EMP;

패키지 본문

패키지 명세에서 선언한 서브프로그램 코드를 작성

CREATE [OR REPLACE] PACKAGE BODY 패키지 이름
IS | AS 
    패키지 명세에서 선언한 서브프로그램을 포함한 여러 객체를 정의
    경에우 따라 패키지 명세에 존재하지 않는 객체 및 서브프로그램도 정의 가능
END [패키지 이름];

EX)

CREATE OR REPLACE PACKAGE BODY PKG_EMP
IS 
	FUNCTION FUNC_AFTERTAX(P_SAL NUMBER) RETURN NUMBER
		IS 
			TAX NUMBER := 0.05;
			SAL NUMBER;
		BEGIN 
			SAL := ROUND(P_SAL - P_SAL * TAX);
			
			RETURN SAL;
		
		END FUNC_AFTERTAX;
	
	PROCEDURE PRO_SEARCH_EMP(
		P_DEPTNO EMP.DEPTNO%TYPE, 
		P_JOB EMP.JOB%TYPE
	)
	IS 
		
	BEGIN 
		FOR d IN (SELECT * FROM EMP WHERE DEPTNO = P_DEPTNO AND JOB = P_JOB) LOOP 
			DBMS_OUTPUT.PUT_LINE('EMPNO : ' || d.EMPNO);
			DBMS_OUTPUT.PUT_LINE('ENAME : ' || d.ENAME);
			DBMS_OUTPUT.PUT_LINE('JOB : ' || d.JOB);
			DBMS_OUTPUT.PUT_LINE('--------------------------');
		END LOOP;
 		
	END PRO_SEARCH_EMP;
	
END PKG_EMP;

패키지 삭제

DROP PACKAGE PKG_EMP;

DML 트리거

DML 트리거 형식

CREATE [OR REPLACE] TRIGGER 트리거 이름 - (1)
BEFORE | AFTER - (2) 
INSERT | UPDATE | DELETE ON 테이블 이름 - (3)
REFERENCING OLD as old | NOW as new - (4)
FOR EACH ROW WHEN 조건식 - (5) 
FOLLOWS 트리거 이름2, 트리거 이름3 ...  - (6)
ENABLE | DISABLE  - (7)

DECLARE
  선언부 
BEGIN 
  실행부 
EXCEPTION
  예외 처리부
END;

EMP_TRG_LOG 테이블에 EMP_TRG 테이블 데이터 변경 사항을 기록하는 트리거를 생성

DML 실행전에 수행할 트리거 생성

INSERTING : 추가 중
UPDATING : 수정 중
DELETING : 삭제 중

raise_application_error(에러 코드, 에러 메세지);

트리거 정보 조회
USER_TRIGGERS : 사전 테이블
트리거 변경
활성화, 비활성화

ALTER TRIGGER 트리거명 ENABLE | DISABLE

트리거 삭제

DROP TRIGGER 트리거명;

김정현

이전 포스트

데이터베이스 -17