PL/SQL로 작성한 내용을 단 한 번 실행하는 데 사용하는 경우는 익명 블록이라고 한다. 익명 블록은 오라클에 저장 되지 않기 때문에 다시 실행하려면 PL/SQL블록을 다시 작성하여 실행하여야 한다. 이런 불편함 때문에 필요한 것이 이름을 지정하여 오라클에 저장해두는 PL/SQL 프로그램인 '저장 서브프로그램'이다. 저장 서브프로그램은 저장할 때 한 번 컴파일을 하며, 공유하여 사용할 수 있고, 다른 응용 프로그램에서 호출도 가능하다.
대표적인 구현 방식은 다음과 같다.
| 저장 프로시저 | 특정 처리 작업 수행을 위한 서브 프로그램. SQL문에서는 사용할 수 없다. |
| 저장 함수 | 특정 연산을 거친 결과 값을 반환하는 서브프로그램. SQL문에서 사용할 수 있다. |
| 패키지 | 저장 서브프로그램을 그룹화 하는데 사용한다. |
| 트리거 | 특정 이벤트가 발생할 때 자동으로 연달아 수행할 기능을 구현하는 데 사용한다. |
1. 프로시저
1) 기본 형식
CREATE [OR REPLACE] PROCEDURE 프로시저이름
( 파라미터이름N 모드(기본값: IN) 자료형 [:= | DEFAULT 기본값],
...
)
IS | AS
선언부
BEGIN
실행부
EXCEPTION
예외 처리부
END [프로시저 이름];
* 모드
| IN | 호출할 때 값을 입력 받음(기본값) |
| OUT | 호출할 때 값을 반환 |
| IN OUT | 호출할 때 값을 입력받은 후 실행 결과 값을 반환 |
2) 실행하기
기본 값이 지정되어 있는 파라미터는 호출할 때 값을 지정하지 않아도 기본 값이 지정된 채로 실행 가능하다. 그 외 기본값이 지정되어 있지 않은 파라미터는 모두 지정해주어야만 오류 없이 실행될 수 있다. 파라미터 이름에 직접 값을 대입하는 방식도 가능하다.
ex) EXCUTE 프로시저이름(param1 => 1, param2 => 20);
SQL*PLUS로 실행하기
EXCUTE 프로시저이름( );
SET SERVEROUTPUT ON;
EXECUTE 프로시저이름( );
PL/SQL 블록에서 프로시저 실행하기
BEGIN
프로시저이름( );
END;
/
3) 조회하기
USER_SOURCE 데이터 사전에서 조회
4) 삭제하기
DROP PROCEDUTRE 프로시저이름;
5) 오류 정보 확인하기
서브 프로그램을 만들 때 발생한 오류는 SHOW ERRORS 명령어와 USER_ERRORS 데이터 사전을 조회하여 확인할 수 있다.
SHOW ERRORS 명령어
가장 최근에 생성되거나 변경된 서브프로그램의 오류 정보를 출력
SHOW ERRORS;
SHOW ERR;
SHOW ERRORS 프로그램종류 프로그램이름;
SHOW ERR 프로그램종류 프로그램이름;
USER_ERROS 데이터 사전 조회
SELECT *
FROM USER_ERRORS
WHERE NAME = '프로그램이름';
2. 함수
프로시저와 함수의 차이
- 함수는 SQL문에서 직접 실행 가능
- 프로시저는 IN, OUT, IN OUT 모드를 모두 사용할 수 있지만, 함수는 IN 모드만 사용 가능
- 함수는 반드시 하나의 값만을 반환 해야 하고, OUT 모드의 파라미터를 사용하는 프로시저와는 달리 RETURN문을 통해 반환
1) 함수 기본 형식
CREATE [OR REPLACE] FUNCTION 함수이름
(파라미터이름N 자료형N,
...
)
RETURN 자료형
IS | AS
선언부
BEGIN
실행부
RETURN (반환 값);
EXCEPTION
예외 처리부
END [함수 이름];
2) 함수 실행하기
SQL 문에서 실행하기
SELECT 함수이름()
FROM DUAL;
PL/SQL로 함수 실행하기
DECLARE
반환받을변수이름 자료형;
BEGIN
반환받을변수 := 함수이름( );
END;
/
3) 함수 삭제하기
DROP FUNCTION 함수이름;
3. 패키지
연관성이 높은 프로시저, 함수 등 여러 PL/SQL 서브 프로그램을 하나의 논리 그룹으로 묶어 통합 관리하는 데 사용하는 객체이다.
1) 패키지의 장점
- 모듈성: 잘 묶어둔다는 뜻. 패키지 사이의 상호 작용을 더 간편하고 명료하게 해 주는 역할
- 쉬운 설계: 패키지에 포함할 서브 프로그램은 완벽하게 완성되지 않아도 정의가 가능하기 때문에, 전체 소스 코드를 작성하기 전에 미리 패키지에 저장할 서브프로그램을 지정할 수 있으므로 설계가 쉬워질 수 있다.
- 정보 은닉: 패키지에 포함하는 서브 프로그램의 외부 노출 여부 또는 접근 여부를 지정 가능하다. 즉 보안을 강화할 수 있다.
- 기능성 향상: 서브 프로그램 외에 변수.커서.예외 등도 각 세션이 유지되는 동안 선언해서 공용으로 사용할 수 있다. 예를 들어 특정 커서 데이터는 세션이 종료되기 전까지 보존되므로 여러 서브프로그램에서 사용할 수 있다.
- 성능 향상: 패키지에 포함한 모든 서브프로그램들은 메모리에 한 번에 로딩되는데, 메모리에 로딩된 후의 호출은 디스크 I/O를 일으키지 않으므로 성능이 향상된다.
2) 패키지의 구조
명세와 본문을 나누어 작성한다. 패키지 명세에 선언한 여러 객체는 패키지 내부 뿐 아니라 외부에서도 참조 가능
명세 : 패키지에 포함할 변수, 상수, 예외, 커서 와 PL/SQL 서브 프로그램을 선언하는 용도로 작성
본문 : 명세에 선언한 서브 프로그램 코드를 작성한다. 패키지 본문에만 존재하는 프로그램은 패키지 내부에서만 사용할 수 있다. 패키지 본문 이름은 패키지 명세 이름과 같게 지정해야 한다.
패키지 명세 기본 형식
CREATE [OR REPLACE] PACKAGE 패키지 이름
IS | AS
선언부
END [패키지 이름];
패키지 본문 기본 형식
CREATE [OR REPLACE] PACKAGE BODY 패키지 이름
IS | AS
패키지 명세에서 선언한 서브프로그램 정의;
패키지 명세에 존재하지 않는 객체 및 서브 프로그램 정의(패키지 내부에서만 사용가능);
END [패키지 이름];
예제. 패키지 본문 생성하기
CREATE OR REPLACE PACKAGE BODY pkg_example
IS
body_no NUMBER := 10;
FUNCTION func_aftertax(sal NUMBER) RETURN NUMBER
IS
tax NUMBER := 0.05;
BEGIN
RETURN (ROUND(sal - (sal * tax)));
END func_aftertax;
PROCEDURE pro_emp(in_empno IN EMP.EMPNO%TYPE)
IS
out_ename EMP.ENAME%TYPE;
out_sal EMP.SAL%TYPE;
BEGIN
SELECT ENAME, SAL INTO out_ename, out_sal
FROM EMP
WHERE EMPNO = in_empno;
DBMS_OUTPUT.PUT_LINE('ENAME : ' || out_ename);
DBMS_OUTPUT.PUT_LINE('SAL : ' || out_sal);
END pro_emp;
PROCEDURE pro_dept(in_deptno IN DEPT.DEPTNO%TYPE)
IS
out_dname DEPT.DNAME%TYPE;
out_loc DEPT.LOC%TYPE;
BEGIN
SELECT DNAME, LOC INTO out_dname, out_loc
FROM DEPT
WHERE DEPTNO = in_deptno;
DBMS_OUTPUT.PUT_LINE('DNAME : ' || out_dname);
DBMS_OUTPUT.PUT_LINE('LOC : ' || out_loc);
END pro_dept;
END;
/
3) 패키지 조회하기
USER_SOURCE 데이터 사전 조회
SELECT *
FROM USER_SOURCE
WEHRE TYPE='PACKAGE' AND NAME ='패키지이름';
DESC 명령어로 조회
DESC 패키지이름;
4) 패키지 사용하기
패키지이름.객체이름;
5) 패키지 삭제하기
패키지 명세와 본문을 한 번에 삭제
DROP PACKAGE 패키지이름;
패키지의 본문만 삭제
DROP PACKAGE BODY 패키지이름;
6) 서브프로그램 오버로드
사용하는 파라미터의 개수, 자료형, 순서가 다를 경우에 한해서는 같은 패키지 내에서 이름이 같은 서브프로그램을 정의할 수 있다. 단, 서브프로그램 종류가 같아야 오버로드가 가능하다.
4. 트리거
특정 상황이나 동작 등의 이벤트가 발생할 경우에 자동으로 실행되는 기능을 정의하는 PL/SQL 서브 프로그램
- EX. 어떤 테이블의 데이터를 특정 사용자가 변경하려고 할 때 해당 데이터나 사용자 기록을 확인한다거나, 데이터를 변경하지 못하게 막도록 함
- EX. 데이터베이스가 가동하거나 종료할 때 데이터베이스 관리자 등 관련 업무자에게 메일을 보내는 기능
기대 효과
1. 여러 작업을 수행하기 위해 PL/SQL문, 서브프로그램을 일일이 실행해야 하는 번거로움을 줄일 수 있음
2. 제약조건 만으로 구현이 어려운 복잡한 데이터 규칙을 정할 수 있어 더 수준 높은 데이터 정의가 가능함
3. 데이터 변경과 관련된 일련의 정보를 기록해 둘 수 있으므로 여러 사용자가 공유하는 데이터 보안성과 안정성, 그리고 문제가 발생했을 때 대처 능력을 높일 수 있음
다만 무분별하게 사용할 경우 데이터 베이스 성능을 떨어뜨리는 원인이 될 수 있으므로 주의가 필요하다.
트리거가 동작을 지정할 수 있는 이벤트
- DML: INSERT, UPDATE, DELETE
- DDL: CREATE, ALTER, DROP
- DB 동작: SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN
트리거 종류
| DML트리거 | DML 명령어를 기점으로 동작 |
| DDL트리거 | DDL 명령어를 기점으로 동작 |
| INSTEAD OF 트리거 | VIEW에 사용하는 DML 명령어를 기점으로 동작 |
| 시스템 트리거 | 데이터 베이스, 스키마 이벤트로 동작 |
| 단순 트리거 | 특정 시점에 동작 - 특정 문장이 실행되기 전 시점 - 특정 문장이 실행된 후 시점 - 특정 문장이 행에 영향을 미치기 전 시점 - 특정 문장이 행에 영향을 준 후 시점 |
| 복합 트리거 | 단순 트리거의 여러 시점에 동작 |
1) DML 트리거
기본 형식
CREATE [OR REPLACE] TRIGGER 트리거이름
BEFORE | AFTER
INSERT | UPDATE | DELETE ON 테이블이름
REFERENCING OLD as old | NEW AS new
FOR EACH ROW [WHEN 조건식]
FOLLOWS 트리거이름2, 트리거이름3 ...ENABLE | DISABLE
DECLARE
선언부;
BEGIN
실행부;
EXCEPTION예외처리부
;
END;
트리거 예제 (BEFORE)
CREATE OR REPLACE TRIGGER trg_emp_nodml_weekend
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(-20001, '주말 사원정보 수정 불가');
ELSIF DELETING THEN
raise_application_error(-20002, '주말 사원정보 삭제 불가');
ELSE
raise_application_error(-20003, '주말 사원정보 변경 불가');
END IF;
END IF;
END;
/
트리거 예제 (AFTER)
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
VALUES ('EMP_TRG', 'INSERT', :new.empno,
SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate);
ELSIF UPDATING THEN
INSERT INTO emp_trg_log
VALUES ('EMP_TRG', 'UPDATE', :old.empno,
SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate);
ELSIF DELETING THEN
INSERT INTO emp_trg_log
VALUES ('EMP_TRG', 'DELETE', :old.empno,
SYS_CONTEXT('USERENV', 'SESSION_USER'), sysdate);
END IF;
END;
/
트리거 조회하기
USER_TRIGGERS 데이터 사전을 조회한다.
트리거 변경
ALTER TRIGGER 트리거이름 ENABLE | DISABLE;
특정 테이블과 관련된 모든 트리거의 상태를 활성화/비활성화 하기
ALTER TABLE 테이블이름 ENABLE | DIABLE ALL TRIGGERS;
트리거 삭제
DROP TRIGGER 트리거이름;
'BackEnd > DB, SQL' 카테고리의 다른 글
| SQL 23편: 예외 처리 (0) | 2022.01.25 |
|---|---|
| SQL 22편: 커서 (0) | 2022.01.23 |
| SQL 22편: 레코드와 컬렉션 (0) | 2022.01.20 |
| SQL 21편: 조건 제어문(IF, CASE), 반복 제어문(LOOP) (0) | 2022.01.19 |
| SQL 20편: PL/SQL 기초(블록, 변수와 상수 정의하기) (0) | 2022.01.18 |
댓글