[오라클로 배우는 데이터베이스 입문] 19. 저장 서브프로그램

1. 저장 서브프로그램

: 여러 번 사용할 목적으로 이름을 지정하여 오라클에 저장해 두는 PL/SQL프로그램

○ 익명블록과 저장 서브 프로그램의 차이

※ 익명블록: 한 번 실해한 뒤 다시 실행하려면 다시 작성해야하는 PL/SQL블록

	익명 블록	저장 서브 프로그램
이름	이름 없음	이름 지정
오라클 저장	저장할 수 없음	저장함
컴파일	실행할 때마다 컴파일	저장할 때 한 번 컴파일
공유	공유할 수 없음	공유하여 사용 가능
다른 응용 프로그램에서의 호출 가능 여부	호출할 수 없음	호출 가능

○ 저장 서브프로그램 구현 방식

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

2. 프로시저

- 파라미터를 사용하지 않는 프로시저
: 작업 수행에 별다은 입력 데이터가 필요하지 않을 경우 파라미터를 사용하지 않는 프로시저 사용

○ 프로시저 생성하기

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

[OR REPLACE] : 저장한 프로시저 이름을 가진 프로시저가 이미 존재하는 경우 현재 작성한 내용으로 대체(생략가능 옵션)

프로시저 이름: 저장할 프로시저 고유의 이름 지정/같은 스키마 내에서 중복 불가

IS | AS: 선언부를시작하기 위해 IS또는AS키워드 사용, 선언부가 존재하지 않더라도 반드시 명시,DECLARE 키워드는 사용x

EXCEPTION: 예외 처리부는 생략 가능

END[프로시저 이름]; : 프로시저 생성의 종료를 뜻하며 프로시저 이름은 생략 가능

○ SQL*PLUS로 프로시저 생성하기

생성한 프로시저는 SQL*PLUS에서 바로 사용하거나 다른 PL/SQL블록에서 실행가능

EXECUTE 프로시저 이름;

○ PL/SQL블록에서 프로세저 실행하기

BEGIN 프로시저 이름; END;

○ 익명 블록에서 프로시저 실행하기

○ 프로시저 내용 확인하기 - USER_SOURCE 데이터 사전에서 조회

USER_SOURCE의 열	설명
NAME	서브 프로그램(생성 객체) 이름
TYPE	서브 프로그램 종류(PROCEDURE, FUNCTION) 등
LINE	서브 프로그램에 작성한 줄 번호
TEXT	서브 프로그램에 작성한 소스 코드

USER_SOURCE를 통해 프로시저 확인하기(SQL DEVELOPER)

USER_SOURCE를 통해 프로시저 확인하기(SQL*PLUS )

○ 프로시저 삭제하기

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

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

[OR REPLACE] : 지정한 프로시저 이름을 가진 프로시저가 이미 존재하는 경우에 현재 작성한 내용으로 대체합니다. 즉 덮어쓴다는 뜻이며 생략 가능한 옵션입니다.

프로시저 이름 : 저장할 프로시저의 고유 이름을 지정/ 같은 스키마 내에서 중복될 수 없음

[(파라미터 이름1[modes] 자료형 [:=|DEFAULT 기본값], : 실행에 필요한 파라미터를 정의, 파라미터는 쉼표(,)로 구분하여 여러 개 지정할 수 있음/ 기본값과 모드는 생략가능, 자료형은 자릿수 지정 및 NOT NULL 제약 조건 사용이 불가능

IS | AS : 선언부를 시작하기 위해 IS 또는 AS키워드를 사용. 선언부가 존재하지 않더라도 명시, DECLARE키워드는 사용X

EXCEPTION : 예외 처리부는 생략 가능

END[프로시저 이름]; : 프로시저 생성 종료를 뜻하며 프로시저 이름은 생략 가능

○ 파라미터 지정할 때 사용하는 모드

파라미터 모드	설명
IN	지정하지 않으면 기본값으로 프로시저를 호출할 때 값을 입력받음
OUT	호출할 때 값을 반환
IN OUT	호출할 때 값을 입력받은 후 실행 결과 값을 반환

○ IN 모드 파라미터

프로시저 실행에 필요한 값을 직접 입력받는 형식의 파라미터를 지정할 때 IN을 사용, 기본값이라 생략가능

프로시저에 파라미터 설정하기

파라미터를 입력하여 프로시저 사용하기

PARAM3,4는 기본값이 지정되어 값을 입력받지 않아도 출력됨

실행에 필요한 개수보다 적은 파라미터를 입력하면 오류발생

파라미터 이름을 활용하여 프로시저에 값 입력하기

파라미터에 값을 지정하는 방식
종류	설명
위치 지정	지정한 파라미터 순서대로 값을 지정하는 방식
이름 지정	=> 연산자로 파라미터 이름을 명시하여 값을 지정하는 방식
혼합 지정	일부 파라미터는 순서대로 값만 지정하고 일부 파라미터는 =>연산자로 값을 지정하는방식

○ OUT 모드 파라미터: 프로시저 실행 후 호출한 프로그램으로 값을 반환

OUT 모드 파라미터 정의하기

OUT모드 파라미터 사용하기

○ IN OUT모드 파라미터 : 값을 입력받을 때와 프로시저 수행 후 결과 값 반활할 때 사용

IN OUT 모드 파라미터 정의

IN OUT모드파라미터 사용하기

- 프로시저 오류 정보 확인하기

SHOW ERRORS는 줄여서 SHOW ERR로 쓸 수도 있음/ 특정 프로그램의 오류 정보를 확인하고 싶은 경우 아래의 질의로 실행

SHOW ERR 프로그램 종류 프로그램 이름;

○ USER_ERRORS로 오류 확인하기

3. 함수

○ 프로시저와 함수의 차이점

특징	프로시저	함수
실행	EXECUTE명령어 또는 다른 PL/SQL 서브프로그램 내에서 호출하여 실행	변수를 사용한 EXECUTE 명령어 또는 다른PL/SQL 서브프로그램에서 호출하여 실행하거나 SQL문에서 직접 실행 가능
파라미터 지정	필요에 따라 지정하지 않을 수도 있고 여러 개 지정할 수도 있으며 IN, OUT, IN OUT 세 가지 모드를 사용할 수 있음	프로시저와 같게 지정하지 않을 수도 있고 여러 개 지정할 수 있지만 IN모드(또는 생략)만 사용
값의 반환	실행 후 값의 반환이 없을 수도 있고 OUT, IN OUT 모드의 파라미터 수에 따라 여러 개 값을 반환할 수 있음	반드시 하나의 값을 반환해야 하며 값의 반화는 프로시저와 달리 OUT, IN OUT모드의 파라미터를 사용하는 것이 아니라 RETURN절과 RETURN문을 통해 반환

- 함수 생성하기

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

[(파라미터 이름1 [IN] 자료형1, : 함수 실행에 사용할 입력 값이 필요하면 파라미터를 지정, 파라미터 지정은 생략 가능하며 필요에 따라 여러 개 정의가능, 프로시저와 달리 IN모드만 지정, :=, DEFAULT 옵션으로 기본값 지정가능

RETURN 자료형: 함수의 실행 후 반환 값의 자료형을 정의

RETURN (반환 값);: 함수의 반환 값을 지정

함수 생성하기

- 함수 실행하기

○ PL/SQL로 함수 실행하기

PL/SQL에서 함수 사용하기

○ SQL문에서 함수 실행하기

함수에 테이블 데이터 사용하기

- 함수 삭제하기

함수 삭제

4. 패키지

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

장점	설명
모듈성	서브프로그램을 포함한 여러 PL/SQL 구성 요소를 모듈화할 수 있음. 모듈성은 잘 묶어 둔다는 뜻으로 프로그램의 이해를 쉽게 하고 패키지 사아의 상호 작용을 더 간편하고 명료하게 해 주는 역할, 즉PL/SQL로 제작한 프로그램의 사용 및 관리에 큰 도움
쉬운 응용 프로그램 설계	패키지에 포함할 서브프로그램은 완벽하게 완성되지 않아도 정의 가능 이 때문에 전체 소스 코드를 다 작성하기 전에 미리 패키지에 저장할 서브프로그램을 지정할 수 있으므로 설계가 수월해짐
정보 은닉	제작 방식에 따라 패키지에 포함하는 서브프로그램의 외부 노출 여부 또는 접근 여부를 지정할 수 있음. 즉 서브 프로그램을 사용할 때 보안 강화됨
기능성 향상	패키지 내부에는 서브프로그램 외에 변수,커서,예외 등도 각 세션이 유지되는 동안 선언해서 공용(public)으로 사용할 수 있음. 예를 들어 특정 커서 데이터는 세션이 종료되기 전까지 보존되므로 여러 서브프로그램에서 사용할 수 있음
성능 향상	패키지를 사용할 때 패키지에 포함한 모든 서브 프로그램이 메모리에 한 번에 로딩되는데 메모리에 로딩된 후의 호출은 디스크 I/O를 일으키지 않으므로 성능이 향상됨

- 패키지 구조와 생성: 패키지는 명세, 본문으로 나누어 제작함

○ 패키지 명세 : 변수, 상수, 예외, 커서 그리고 PL/SQL 서브프로그램을 선언하는 용도로 작성

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

패키지 생성하기

패키지 명세 확인하기(USER_SOURCE)

패키지 명세 확인하기(DESC 명령어)

○ 패키지 본문 : 패키지 명세에서 선언한 서브프로그램 코드작성, 본문이름과 명세이름은 같게 지정해야 함

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

 

○ 서브 프로그램 오버로드 : 서브프로그램 이름은 중복될 수 없으나 같은 패키지에서 사용하는 파라미터의 개수, 자료형, 순서가 다를경우에 한해서만 이름이 같은 서브프로그램을 정의하는 것

CREATE [OR REPLACE] PACKAGE 패키지이름 IS | AS 서브프로그램 종류 서브프로그램 이름(파라미터 정의); 서브프로그램 종류 서브프로그램 이름(개수나 자료형, 순서가 다른 파라미터 정의); END [패키지 이름];

사원 번호, 사원 이름을 받아 사원 이름과 급여를 출력하기 위한 PRO_EMP프로시저 오버로드하는 패키지

패키지 본문에서 오버로드된 프로시저 작성하기

- 패키지 사용하기

패키지 이름.객체 이름;

PKG_EX, PKG_OVER 패키지의 서브 프로그램 실행한 결과

- 패키지 삭제하기

패키지 명세와 본문을 한 번에 삭제하기 DROP PACKAGE 패키지 이름;

패키지의 본문만을 삭제 DROP PACKAGE BODY 패키지 이름;

5. 트리거

데이터베이스 안의 특정 상황이나 동작, 이벤트가 발생할 경우 자동으로 실행되는 기능을 정의하는 PL/SQL 서브 프로그램

○ 트리거의 장점

1. 데이터가 연관된 여러 작업을 수행하기 위해 여러 PL/SQL문 또는 서브프로그램을 일일이 실행해야 하는 번거로움을 줄일 수 있음. 2. 제약 조건만으로 구현이 어렵거나 불가능한 좀 더 복잡한 데이터 규칙을 정할 수 있어 수준높은 데이터 정의 가능 3. 데이터 변경과 관련된 일련의 정보를 기록해 둘 수 있어 여러 사용자가 공유하는데이터 보안성과 안정성 그리고 문제가 발생했을 때 대처 능력을 높일 수 있음

○ 트리거 동작을 지정할 수 있는 수준

- 데이터 조작어(DML): INSERT, UPDATE, DELETE - 데이터 정의어(DDL): CREATE, ALTER, DROP - 데이터베이스 동작: SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN

○ 트리거가 발생할 수 있는 이벤트 종류에 따른 구분

종류	설명
DML 트리거	INSERT, UPDATE, DELETE와 같은 DML명령어를 기점으로 동작
DDL 트리거	CREATE, ALTER, DROP과 같은 DDL 명령어를 기점으로 동작
INSTEAD OF 트리거	뷰에 사용하는 DML명령어를 기점으로 동작
시스템(system)트리거	데이터베이스나 스키마 이벤트로 동작
단순(simple) 트리거	다음 각 시점에 동작함 - 트리거를 작동시킬 문장이 실행되기 전 - 트리거를 작동시킬 문장이 실행된 후 - 트리거를 작동시킬 문장이 행에 영향을 미치기 전 - 트리거를 작동시킬 문장이 행에 영향을 준 후
복합(compound)트리거	단순 트리거의 여러 시점에 동작

- DML트리거(사용빈도가 가장 높음)

CREATE [OR REPLACE] TRIGGER 트리거 이름--트리거 이름을명시하고 트리거 생성, 갱신하려면 OR REPLACE 키워드 사용 BEFORE | AFTER--트리거가 작동할 타이밍 지정, BEFORE은 실행되기 전 AFTER은 실행된 후 작동하게 됨 INSERT | UPDATE | DELETE ON 테이블 이름--지정한 테이블에 트리거가 작동할 DML명령어 작성 여러 종류 지정 OR로 구분 REFERENCING OLD AS old | New AS new--DML로 변경되는 행의 변경 전 값과 변경 후 값 참조할 때 사용(생략 가능) FOR EACH ROW WHEN 조건식 --트리거를 실행하는 DML문장에 한 번만 실행할 지 영향받는 행별로 실행할 지 지정, 생략하는 경우 DML명령어 실행될 때 한 번만 실행, 생략하지 않는 경우 DML명령어에 영향받는 행별로 트리거를 작동하되 WHEN을 사용하면 DML영향받는 행 중 트리거를 작동시킬 행을 조건식으로 지정가능 FOLLOWS 트리거 이름2, 트리거 이름 3... ENABLE | DISABLE DECLARE 선언부 BEGIN 실행부 EXCEPTION 예외 처리부 END;

- DML 트리거의 제작 및 사용(BEFORE)

트리거 작동 확인

- DML 트리거의 제작 및 사용(AFTER)

EMP_TRG테이블에 DML명령어를 수행한 후 EMP_TRG_LOG 테이블에 EMP_TRG테이블 데이터의 변경사항을 기록하는 트리거 생성

INSERT 후 기록 확인하기

UPDATE 후 기록 확인

- 트리거 관리

○ 트리거 정보 조회: USER_TRIGGERS 데이터 사전 조회

- 트리거 변경

ALTER TRIGGER 트리거 이름 ENABLE | DISABLE;                                       (특정 트리거 활성화 비활성화 옵션)

특정 테이블과 관련된 모든 트리거의 상태 활성화 ALTER TABLE 테이블 이름 ENABLE ALL TRIGGERS;

특정 테이블과 관련된 모든 트리거의 상태 비활성화 ALTER TABLE 테이블 이름 DISABLE ALL TRIGGERS;

- 트리거 삭제

DROP TRIGGER 트리거 이름;