[SQL응용] 저장 서브프로그램

예지성준·2024년 5월 20일
db

DB

목록 보기
19/19
post-thumbnail

저장 서브프로그램

저장 서브 프로그램이란?

PL/SQL로 만든 프로그램을 주기적으로 또는 필요할 때마다 여러 번 사용할 목적으로 이름을 지정하여 오라클에 저장해 두는 PL/SQL 프로그램을 저장 서브프로그램(stored subprogram)이라고 합니다.

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

3) 종류

  • 저장 프로시저(stored procedure) : 일반적으로 특정 처리 작업 수행을 위한 서브프로그램으로 SQL문에서는 사용할 수 없습니다.
  • 저장 함수(stored function) : 일반적으로 특정 연산을 거친 결과 값을 반환하는 서브프로그램으로 SQL문에서 사용할 수 있습니다.
  • 패키지(package) : 저장 서브프로그램을 그룹화하는 데 사용합니다.
  • 트리거(trigger) : 특정 상황(이벤트)이 발생할 때 자동으로 연달아 수행할 기능을 구현하는 데 사용합니다.

프로시저

1. 파라미터를 사용하지 않는 프로시저

1) 문법

CREATE [OR REPLACE] PROCEDURE 프로시저 이름
IS | AS 
    선언부 - 변수 정의, 커서 정의
BEGIN 
    실행부
EXCEPTION - 
    예외 처리부
END [프로시저 이름];

2) 프로시저 생성하기

  • 파라미터가 없는 형태
CREATE OR REPLACE PROCEDURE PRO_NOPARAM
IS 
	V_DEPTNO DEPT.DEPTNO%TYPE;
	V_DNAME DEPT.DNAME%TYPE;
BEGIN 
	SELECT DEPTNO, DNAME INTO V_DEPTNO, V_DNAME
	--DEPTNO, DNAME를 조회해 후 복사해서 INTO뒤 변수에 넣기
	FROM DEPT WHERE DEPTNO = 30;
	
	DBMS_OUTPUT.PUT_LINE('DEPTNO: '||V_DEPTNO);
	DBMS_OUTPUT.PUT_LINE('DNAME: '|| V_DNAME);
END PRO_NOPARAM;

3) 프로시저 실행하기

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

4) PL/SQL 블록에서 프로시저 실행하기

BEGIN
	프로시저명(값,...);
END;

BEGIN
	PRO_NOPARAM;
    --매개변수가 없는 경우엔 이름 형태도 가능
END;

5) 프로시저 내용 확인하기

  • USER_SOURCE: 사전테이블

6) 프로시저 삭제하기
DROP PROCEDURE 프로시저명;

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

1) 문법

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

2) 파라미터 모드

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

3) IN 모드 파라미터 - 내부에서 사용할 목적

  • 필요한 값을 직접 입력받는 형식의 파라미터를 지정할 때
  • 기본값, 생략 가능
CREATE OR REPLACE PROCEDURE PRO_PARAM_IN(
	P_DEPTNO IN NUMBER, -- 내부에서 쓰이는 변수
	P_JOB IN VARCHAR2 -- IN생략 가능
	--두개의 매개변수 존재
)
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_PARAM_IN; --프로시저명 꼭 같이 입력해줘야함


SELECT * FROM USER_ERRORS;

CALL PRO_PARAM_IN(20,'CLERK');

4) OUT 모드 파라미터 - 외부로 내보냄

  • 프로시저 실행 후 호출한 프로그램으로 값을 반환합니다. 
CREATE OR REPLACE PROCEDURE PRO_PARAM_OUT(
	P_DEPTNO EMP.DEPTNO%TYPE,
	O_SUM OUT NUMBER, -- OUT생략 불가
	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; --프로시저명 꼭 같이 입력해줘야함

5) IN OUT 모드 파라미터

  • IN, OUT으로 선언한 파라미터 기능을 동시에 수행합니다.
  • 값을 입력받을 때와 프로시저 수행 후 결과 값을 반환할 때 사용합니다.
  1. 프로시저 오류 정보 확인하기

1) SHOW ERRORS /sqlplus

2) USER_ERRORS: 사전 테이블

함수

  1. 문법 
CREATE [OR REPLACE] FUNCTION 함수 이름
[(
   파라미터 이름1 [IN] 자료형1, - (1) 
   파라미터 이름2 [IN] 자료형2,
   ...
   파라미터 이름N [IN] 자료형N  
)]
RETURN 자료형 - (2)
IS | AS 
    선언부 
BEGIN 
    실행부
    RETURN (반환 값); - (3) 
EXCEPTION
    예외 처리부 
END [함수 이름];
  1. 함수 생성하기
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;
  1. 함수 실행하기

1) PL/SQL로 함수 실행하기

2) SQL문에서 함수 실행하기

  1. 함수 삭제하기
    DROP FUNCTION 함수명

패키지

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

1) 패키지 명세

  • 패키지 명세는 패키지에 포함할 변수, 상수, 예외, 커서 그리고 PL/SQL 서브프로그램을 선언하는 용도로 작성합니다.

  • 패키지 명세에 선언한 여러 객체는 패키지 내부뿐만 아니라 외부에서도 참조할 수 있습니다.

  • 문법

CREATE [OR REPLACE] PACKAGE 패키지 이름
IS | AS 
    서브프로그램을 포함한 다양한 객체 선언
END [패키지 이름];
  • 패키지 생성하기
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;

2) 패키지 본문

  • 패키지 명세에서 선언한 서브프로그램 코드를 작성합니다.
  • 문법
CREATE [OR REPLACE] PACKAGE BODY 패키지 이름
IS | AS 
    패키지 명세에서 선언한 서브프로그램을 포함한 여러 객체를 정의
    경에우 따라 패키지 명세에 존재하지 않는 객체 및 서브프로그램도 정의 가능
END [패키지 이름];
  • 패키지 본문 생성하기
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;

  1. 패키지 사용하기

  2. 패키지 삭제하기

DROP PACKAGE 패키지명; -> 패키지 명세 + 패키지

DROP PACKAGE BODY 패키지명; -> 패키지 본문만 삭제

트리거

  1. 트리거란?
    1) 데이터베이스 안의 특정 상황이나 동작, 즉 이벤트가 발생할 경우에 자동으로 실행되는 기능을 정의하는 PL/SQL 서브프로그램
    2) 트리거는 특정 작업 또는 이벤트 발생으로 다른 데이터 작업을 추가로 실행하기 때문에 무분별하게 사용하면 데이터베이스 성능을 떨어뜨리는 원인이 되므로 주의

  2. DML 트리거
    1) 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;
  1. DML 트리거의 제작 및 사용(BEFORE)
    1) EMP_TRG 테이블 생성하기
CREATE TABLE EMP_TRG AS SELECT * FROM EMP;

2) DML 실행 전에 수행할 트리거 생성하기

CREATE OR REPLACE TRIGGER TRG_EMP_NODML
BEFORE 
INSERT OR UPDATE OR DELETE ON 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;
  1. DML 트리거의 제작 및 사용(AFTER)
    1) EMP_TRG_LOG 테이블 생성하기
CREATE TABLE EMP_TRG_LOG (
	DML_TYPE VARCHAR2(20),
	EMPNO NUMBER(4),
	ENAME VARCHAR2(20),
	REGDT DATE DEFAULT SYSDATE
);

2) EMP_TRG_LOG 테이블에 EMP_TRG 테이블 데이터 변경 사항을 기록하는 트리거를 생성
:NEW - INSERT시 추가된 레코드 참조
:OLD - UPDATE, DELTENTE시 수정된 레코드 참조

CREATE OR REPLACE TRIGGER TRG_EMP_LOG
AFTER 
INSERT OR UPDATE OR DELETE ON EMP_TRG
FOR EACH ROW 
BEGIN 
	IF INSERTING THEN 
		INSERT INTO EMP_TRG_LOG (DML_TYPE, EMPNO, ENAME)
			VALUES ('INSERT', :NEW.EMPNO, :NEW.ENAME);
	ELSIF UPDATING THEN 
		INSERT INTO EMP_TRG_LOG (DML_TYPE, EMPNO, ENAME)
			VALUES ('UPDATE', OLD.EMPNO, OLD.ENAME);
	ELSIF DELETING THEN 
		INSERT INTO EMP_TRG_LOG (DML_TYPE, EMPNO, ENAME)
			VALUES ('DELETING', OLD.EMPNO, OLD.ENAME);
	END IF;
END;

  1. 트리거 정보 조회
  • USER_TRIGGERS
  1. 트리거 변경
ALTER TRIGGER 트리거 이름 ENABLE | DISABLE;
  1. 트리거 삭제

DROP TRIGGER 트리거명

profile
예지성준
꽁꽁 얼어붙은 한강 위로 😺
이전 포스트

[SQL응용] 커서와 예외처리

0개의 댓글