반응형
PLT 6.9 SUBPROGRAM
PL/SQL을 지원하는 어떤 툴이나 언어에서도 SUBPROGRAM(프로시저와 함수)을 실행할 수 있다. PL/SQL내부에서 식의 일부로서 함수를 실행할 수 있다. EXECUTE는 명령 다음에 입력되는 Stored Procedure를 실행한다.
SUBPROGRAM의 개요
PL/SQL 프로시저와 함수는 3GL의 프로시저 및 함수와 매우 비슷하게 동작된다. 모듈화를 통해 관리가 용이하고 적절히 논리적 단위로 나누어진 프로그래밍을 할 수 있다. 즉, 잘 정의된 논리적인 단위로 코드를 분할할 수 있다. PL/SQL에서 이들 단위를 단위 프로그램 또는 SUBPROGRAM이라 부른다. PL/SQL에는 프로시저와 함수라는 두 가지 유형의 SUBPROGRAM이 있다. SUBPROGRAM은 컴파일된 상태로 데이터베이스에 저장되어 있어 Performance가 향상된다.
SUBPROGRAM 작성 단계
구문 작성
TEXT 편집기를 이용하여 SCRIPT FILE에 CREATE PROCEDURE나 CREATE FUNCTION문을 작성한다.
|
SQL> ed emp_up
CREATE OR REPLACE PROCEDURE emp_sal_update(
p_empno IN emp.empno%TYPE, p_sal IN emp.sal%TYPE)
IS
BEGIN
UPDATE emp
SET sal = p_sal
WHERE empno = p_empno;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(p_empno) ||
'는 없는 사원번호입니다.');
ELSE
DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQL%ROWCOUNT) ||
'명의 자료를 수정하였습니다.');
END IF;
END emp_sal_update;
/ |
코드 컴파일
SCRIPT FILE을 실행 시켜 컴파일하여 컴파일된 코드를 데이터베이스에 저장한다.
|
SQL> @emp_up
Procedure created. |
에러 수정
코드 컴파일시 에러가 발생하면 에러를 확인하고 수정하여 코드를 다시 컴파일한다.
|
SQL> @emp_up
Warning: Procedure created with compilation errors.
SQL> ed emp_up
-- emp_up를 수정한 후 저장하고 종료한다.
SQL> @emp_up
Procedure created. |
실행
SQL*Plus에서 EXECUTE명령으로 SUBPROGRAM을 실행한다.
|
SQL> EXECUTE emp_sal_update(7788,3500)
PL/SQL procedure successfully completed.
SQL> SELECT empno,ename,job,sal
2 FROM emp
3 WHERE empno = 7788;
EMPNO ENAME JOB SAL
--------- ---------- --------- ---------
7788 SCOTT ANALYST 3500 |
PROCEDURE 생성
나중에 실행할 일련의 동작을 저장하기 위해 PL/SQL프로시저를 작성한다. 프로시저는 실행할 때 사용하는 Parameter가 없거나 여러 개를 가질 수도 있다. 프로시저에서는 DECLARE절이 생략되고 IS와 BEGIN사이에 필요한 변수를 선언하여 사용한다
|
CREATE [OR REPLACE] PROCEDURE procedure_name
[(argument1 [mode1] datatype [{:= | DEFAULT} expression]
[,argument2 [mode2] datatype [{:= | DEFAULT} expression], . . .])]
{IS | AS}
BEGIN
pl/sql_block;
END;
OR REPLACE : procedure_name이 존재할 경우 PROCEDURE의 내용을 지우고 다시 생성
procedure_name : PROCEDURE 명
argument : 매개변수의 이름
mode : 3가지가 있다
- IN : 입력 매개변수로 상용
- OUT : 출력 매개변수로 사용
- IN OUT : 입력, 출력 매개변수로 상용
pl/sql_block : PROCEDURE를 구성하는 코드를 구성하는 PL/SQL의 블록
|
n SQL*Plus에서 프로시저를 작성할 때 CREATE OR REPLACE를 사용합니다.
n 어떠한 Parameter라도 사용 가능합니다.
n IS로 PL/SQL블록을 시작합니다.
n Local변수 선언은 IS와 BEGIN사이에 선언 합니다.
PROCEDURE 실행
PL/SQL을 지원하는 어떤 툴이나 언어에서도 프로시저를 실행할 수 있다. SQL*Plus에서 프로시저 호출은 Stored Procedure를 참조하는 PL/SQL 문을 실행하기 위해 EXECUTE 명령을 사용할 수 있다. EXECUTE는 명령 다음에 입력되는 Stored Procedure를 실행한다.
|
procedure_name[(argument1[,argument2, . . . .])] |
|
SQL> EXECUTE emp_sal_update(7902,4000)
PL/SQL procedure successfully completed. |
|
CREATE OR REPLACE PROCEDURE emp_input(
v_name IN emp.ename %TYPE,
v_job IN emp.job %TYPE,
v_mgr IN emp.mgr %TYPE,
v_sal IN emp.sal %TYPE)
IS
v_comm emp.comm%TYPE;
v_deptno emp.deptno%TYPE;
manager_error EXCEPTION;
BEGIN
IF UPPER(v_job) NOT IN ('PRESIDENT','MANAGER','ANALYST',
'SALESMAN','CLERK') THEN
RAISE manager_error;
ELSIF UPPER(v_job) = 'SALESMAN' THEN
v_comm := 0;
ELSE
v_comm := NULL;
END IF;
SELECT deptno
INTO v_deptno
FROM emp
WHERE empno = v_mgr;
INSERT INTO emp
VALUES (empno_sequence.NEXTVAL,v_name,UPPER(v_job),
v_mgr,SYSDATE,v_sal,v_comm,v_deptno);
EXCEPTION
WHEN manager_error THEN
DBMS_OUTPUT.PUT_LINE('담당 업무가 잘못 입력되었습니다.');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('입력한 MANAGER는 없습니다.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('기타 에러입니다.');
END;
/
SQL> SET SERVEROUTPUT ON
SQL> EXECUTE emp_input('YOONJB','MANAGER',7788,2500) |
FUNCTION 생성
실행 환경에 반드시 하나의 값을 Return하기 위해 PL/SQL 함수를 사용한다. 함수 선언에서 Datatype이 있는 RETURN 절을 추가하고 PL/SQL 블록에 적어도 한 개의 이상의 RETURN 문을 포함한다. PARAMETER에서 사용하는 IN,OUT,IN OUT는 PROCEDURE에서 사용한 것과 동일하게 사용 가능하나 대부분 IN을 사용한다.
|
CREATE [OR REPLACE] FUNCTION function_name
[(argument1 [mode1] datatype [{:= | DEFAULT} expression]
[,argument2 [mode2] datatype [{:= | DEFAULT} expression], . . .])]
RETURN data_type
{IS | AS}
BEGIN
pl/sql_block;
END;
OR REPLACE : function_name이 존재할 경우 FUNCTION의 내용을 지우고 다시 생성
function_name : Function의 이름은 표준 Oracle 명명법에 따른 함수이름
argument : 매개변수의 이름
mode : 3가지가 있다
- IN : 입력 매개변수로 상용
- OUT : 출력 매개변수로 사용
- IN OUT : 입력, 출력 매개변수로 상용
data_type : 반환되는 값의 datatype
pl/sql_block : FUNCTION를 구성하는 코드를 구성하는 PL/SQL의 블록 |
RETURN 문
n PL/SQL 블록에는 RETURN문이 있어야 한다.
n 함수는 RETURN 절에 지정된 것과 동일한 datatype으로 RETURN 값을 설정해야 한다.
n 다중 RETURN 문은 사용할 수 있지만 한 번의 호출로는 한 개의 RETURN 문만 실행된다.
n 일반적으로 다중 RETURN 문은 IF 문에서 사용한다.
FUNCTION 실행
PL/SQL을 지원하는 어떤 툴이나 언어에서도 함수를 실행할 수 있고 PL/SQL내부에서 식의 일부로서 함수를 실행할 수 있다. SQL*Plus에서 FUNCTION 호출은 Stored Function를 참조하는 PL/SQL 문을 실행하기 위해 EXECUTE 명령을 사용할 수 있다. EXECUTE는 명령 다음에 입력되는 Stored Function를 실행한다.
|
output_variable := function_name[(argument1[,argument2, . . . . .])] |
|
SQL> EXECUTE :g_deptno := ename_deptno('ALLEN')
PL/SQL procedure successfully completed. |
|
CREATE OR REPLACE FUNCTION ename_deptno(
v_ename IN emp.ename%TYPE)
RETURN NUMBER
IS
v_deptno emp.deptno%TYPE;
BEGIN
SELECT deptno
INTO v_deptno
FROM emp
WHERE ename = UPPER(v_ename);
DBMS_OUTPUT.PUT_LINE('부서번호 : ' || TO_CHAR(v_deptno));
RETURN v_deptno;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('입력한 MANAGER는 없습니다.');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('자료가 2건 이상입니다.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('기타 에러입니다.');
END;
/
SQL> SET SERVEROUTPUT ON
SQL> VAR g_deptno NUMBER
SQL> EXECUTE :g_deptno := ename_deptno('SCOTT')
부서번호 : 10
PL/SQL procedure successfully completed.
SQL> PRINT g_deptno
G_DEPTNO
---------
10 |
함수와 프로시저 비교
|
프로시저 |
함수 |
|
PL/SQL 문으로서 실행 |
식의 일부로서 사용 |
|
RETURN Datatype이 없음 |
RETURN Datatype이 필수 |
|
값을 Return할 수 있음 |
값을 Return하는 것이 필수 |
n 프로시저는 parameter리스트를 가질 수 있지만 값 반환이 필수적 이지는 않다.
n 함수는 다음과 같은 두 가지 점에서 프로시저와 다르다.
n 식(expression)의 일부로서 함수를 사용한다.
n 함수는 값을 return하는 것이 필수적이다.
TRIGGER
특정 테이블에 DML(INSERT,UPDATE,DELETE)문장이 수행되었을 때 데이터베이스에서 자동적으로 PL/SQL 블록을 수행 시키기 위해서 데이터베이스 TRIGGER를 사용한다. TRIGGER는 트리거링 이벤트가 일어날 때마다 암시적으로 실행된다. 트리거링 이벤트에는 데이터베이스 테이블에서 INSERT, UPDATE, DELETE 오퍼레이션이다.
TRIGGER가 사용되는 경우
n 테이블 생성시 CONSTRAINT로 선언 제한이 불가능하고 복잡한 무결성 제한을 유지
n DML문장을 사용한 사람,변경한 내용,시간 등을 기록함으로써 정보를 AUDIT하기
n 테이블을 변경할 때 일어나야 할 동작을 다른 테이블 또는 다른 프로그램들에게 자동적으로 신호하기
TRIGGER에 대한 제한
n TRIGGER는 트랜잭션 제어 문(COMMIT,ROLLBACK,SAVEPOINT)장을 사용하지 못한다.
n TRIGGER 주요부에 의해 호출되는 프로시저나 함수는 트랜잭션 제어 문장을 사용하지 못한다.
n TRIGGER 주요부는 LONG또는 LONG RAW변수를 선언할 수 없다.
n TRIGGER 주요부가 액세스하게 될 테이블에 대한 제한이 있다.
TRIGGER생성
CREATE TRIGGER문장에 의해 TRIGGER를 생성할 수 있다.
|
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} triggering_event [OF column1, . . .] ON table_name
[FOR EACH ROW [WHEN trigger_condition]
trigger_body;
trigger_name : TRIGGER의 식별자
BEFORE | AFTER : DML문장이 실행되기 전에 TRIGGER를 실행할 것인지 실행된 후에 TRIGGER를 실행할 것인지를 정의
triggering_event : TRIGGER를 실행하는 DML(INSERT,UPDATE,DELETE)문을 기술한다.
OF column : TRIGGER가 실행되는 테이블에서 COLUMN명을 기술한다.
table_name : TRIGGER가 실행되는 테이블 이름
FOR EACH ROW : 이 옵션을 사용하면 행 레벨 트리거가 되어 triggering문장에 의해 영향받은 행에 대해 각각 한번씩 실행하고 사용하지 않으면 문장 레벨 트리거가 되어 DML문장 당 한번만 실행된다. |
TRIGGER에서 OLD와 NEW
행 레벨 TRIGGER에서만 사용할 수 있는 예약어로 트리거 내에서 현재 처리되고 있는 행을 액세스할 수 있다. 즉 두개의 의사 레코드를 통하여 이 작업을 수행할 수 있다. :OLD는 INSERT문에 의해 정의되지 않고 :NEW는 DELETE에 대해 정의되지 않는다. 그러나 UPDATE는 :OLD와 :NEW를 모두 정의한다. 아래의 표는 OLD와 NEW값을 정의한 표이다.
|
문장 |
:OLD |
:NEW |
|
INSERT |
모든 필드는 NULL로 정의 |
문장이 완전할 때 삽입된 새로운 값 |
|
UPDATE |
갱신하기 전의 원래 값 |
문장이 완전할 때 갱신된 새로운 값 |
|
DELETE |
행이 삭제되기 전의 원래 값 |
모든 필드는 NULL이다. |
TRIGGER 술어 사용하기
트리거 내에서 오퍼레이션이 무엇인지를 결정하기 위해 사용할 수 있는 3가지 BOOLEAN함수가 있다.
|
술 어 |
설 명 |
|
INSERTING |
트리거링 문장이 INSERT이면 TRUE를 그렇지 않으면 FALSE를 RETURN |
|
UPDATING |
트리거링 문장이 UPDATE이면 TRUE를 그렇지 않으면 FALSE를 RETURN |
|
DELETING |
트리거링 문장이 DELETE이면 TRUE를 그렇지 않으면 FALSE를 RETURN |
TRIGGER 삭제와 억제하기
DROP TRIGGER명령어로 트리거를 삭제할 수 있고 TRIGGER를 잠시 disable할 수 있다.
|
DROP TRIGGER trigger_name;
ALTER TRIGGER trigger_name {DISABLE | ENABLE}; |
TRIGGER와 DATA DICTIONARY
TRIGGER가 생성될 때 소스 코드는 데이터 사전 VIEW인 user_triggers에 저장된다. 이 VIEW는 TRIGGER_BODY, WHERE절, 트리거링 테이블, TRIGGER 타입을 포함 한다.
|
SQL> SELECT trigger_type,table_name,triggering_event
2 FROM user_triggers;
TRIGGER_TYPE TABLE_NAME TRIGGERING_EVENT
---------------- ------------------------------ --------------------------
AFTER STATEMENT EMP INSERT OR UPDATE OR DELETE
BEFORE STATEMENT EMP INSERT OR UPDATE OR DELETE
BEFORE EACH ROW EMP UPDATE |
|
CREATE OR REPLACE TRIGGER emp_sal_chk
BEFORE UPDATE OF sal ON emp
FOR EACH ROW WHEN (NEW.sal < OLD.sal
OR NEW.sal > OLD.sal * 1.1)
BEGIN
raise_application_error(-20502,
'May not decrease salary. Increase must be < 10%');
END;
/
SQL> @emp_sal |
반응형
'ORACLE > SQL' 카테고리의 다른 글
| Oracle 날짜 관련 함수 (0) | 2008.10.29 |
|---|---|
| Oracle 날짜형 데이터의 연산 (0) | 2008.06.17 |
| PL/SQL (13) - 커서(cursor) (0) | 2008.02.21 |
| PL/SQL (19) - Collections (중첩테이블 - Nested Table) (0) | 2008.02.21 |
| <25가지 SQL작성법> (0) | 2008.02.19 |
