@markdown
## PL/SQL 저장 프로시저
____
- 작성한 PL/SQL을 저장해 놓고 필요한 경우 호출하여 사용할 때
- 오라클은 사용자가 작성한 PL/SQL 문을 데이터베이스에 저장 할 수 있도록 저장프로시저 기능 제공
- 배치 작업, 복잡한 로직, 많은 데이터 활용해서 건드리는 작업에 용이
<pre><code class="sql" style="font-size:14px">CREATE [OR REPLACE ] PROCEDURE prcedure_name
( 변수 [mode] 자료형, 변수 [mode] 자료형 ... )
IS
지역변수 선언
BEGIN
...
END;
/
</code></pre>
### mode 종류
- `in` : 실행시 입력되는 것을 받는다
- `out` : 실행 후 호출한 곳으로 값을 return
- `in out` : 실행시 입력되는 것을 받는다. 실행 후 호출한곳으로 값을 return
- 실행 : execute 프로시저이름(파라미터1, 파라미터2 ...)
- execute 프로시저 or 함수이름(in변수, :out변수)
- 파라미터가 in이면 값 또는 변수가능, 값을 파라미터 전달
- 파라미터가 out이면 변수만 가능, 변수이름을 전달하면서 `:`을 사용하여 :변수이름 으로 사용
- `execute 프로시저 이름(:변수)`
- 파라미터가 `in out`이면 변수만 가능
## PL/SQL 저장 함수
____
- 저장 함수는 저장 프로시저와 용도는 비슷하지만, 실행결과를 되돌려 받을 수 있다
- 단순 로직에 필요한 부분만 함수로 정의해서 간단하게 사용할때 용이
- 함수 실행 방법
1. 함수 작성
2. 함수 생성
3. select 문에서 함수 사용
<pre><code class="sql" style="font-size:14px">CREATE [OR REPLACE ] PROCEDURE prcedure_name
( 변수 [mode] 자료형, 변수 [mode] 자료형 ... )
return number
IS
지역변수 선언
BEGIN
...
END;
/
SQL > EXECUTE :변수이름 := 함수이름(파라미터);
</code></pre>
- 직원들의 급여 보너스를 계산하는 함수 작성
<pre><code class="sql" style="font-size:14px">create or replace function func_bonus(v_empno in number, v_per in number)
return number
is
v_name emp.ename%type;
v_sal emp.sal%type;
begin
select ename, sal
into v_name, v_sal
from emp
where empno = v_empno;
return v_sal*v_per;
end;
/
</code></pre>
- 작성한 함수는 select문에서 컬럼이름을 파라미터 값으로 지정해 사용할 수 있다.
<pre><code class="sql" style="font-size:14px">select ename, sal, func_bonus(empno, 2), lower(ename)
from emp
</code></pre>
## PL/SQL 커서
____
- select 문장 수행 후 반환되는 여러개의 행을 처리하기 위해 사용한다.
- 커서에는 명시적 / 암시적 방법으로 선언할 수 있다.
- fetch문을통해 로우에 대한 결과값을 처리할 수 있다.
### 명시적 커서
____
<pre><code class="sql" style="font-size:14px">create or replace procedure 명시적_커서_프로시저
is
변수 선언;
--커서 선언
cursor 커서이름 is select empno, ename, sal from emp;
begin
--커서 실행
open 커서이름;
loop
--데이터 가져오는 부분 fetch문 : 행 단위로 데이터를 읽는다.
fecth 커서이름 into 전달받을 변수1, 변수2, 변수3
--반복문 탈출 조건
exit when 커서이름%notfound;
dbms_output.put_line(변수1 || 변수2 || 변수3);
end loop;
--커서 종료
close 커서이름;
end;
/
</code></pre>
### 암시적 커서
- for문을 이용하여 간단하게 암시적으로 커서를 생성하고, 반복문을 통해 데이터를 가져올 수 있다.
- for loop에서 각 반복마다 커서를 열고 행을 fetch한다.
- 모든 행이 처리되고 난 후 자동으로 커서가 닫히기 때문에 사용하기 편리하다.
<pre><code class="sql" style="font-size:14px">create or replace procedure 암시적_커서_프로시저
is
begin
--암시적인 커서
--레코드 변수 이름 지정 후 select 문 결과를 한 행씩 가져와 레코드에 저장한다.
for v_emp_rec in (select empno, ename, sal from emp)
loop
dbms_output.put_line('직원정보 : ' || v_emp_rec.empno || ' ' || v_emp_rec.ename || ' ' || v_emp_rec.sal);
end loop;
end;
/
</code></pre>
## PL/SQL 패키지
____
- 패키지 : 프로시저나 함수를 효율적으로 관리하기 위해 패키지 단위로 배포할 때 사용된다.
- 패키지는 패키지 선언(명세부)과 패키지 몸체 선언(몸체부) 두 가지 모두를 정의해야 합니다.
- 구성 : 선언부와 구현부로 나누어 패키지 등록을 한다.
- 선언부 : 프로시저 or 함수 헤더 부분만 작성
- 패키지 선언부
<pre><code class="sql" style="font-size:14px">create or replace package mypkg
is
procedure sp_emp7;
function func_tax(v_salary number) return number;
end;
/
</code></pre>
- 구현부 : 선언부에서 선언된 함수 헤더를 구현하는 부분을 작성
- 패키지 바디(구현부)
<pre><code class="sql" style="font-size:14px">create or replace package body mypkg
is
procedure sp_emp7
is
begin
--암시적인 커서
for v_emp_rec in (select empno, ename, sal from emp)
loop
dbms_output.put_line('직원정보 : ' || v_emp_rec.empno || ' ' || v_emp_rec.ename || ' ' || v_emp_rec.sal);
end loop;
end sp_emp7;
function func_tax(v_salary in number) return number
is
begin
return v_salary * 0.07;
end func_tax;
end;
/
</code></pre>
- 사용 : 패키지이름.프로시저 이름 or .함수이름
- 패키지 실행 : `execute mypkg.sp_emp7();`
- select문 패키지함수 호출 : `select ename, sal, mypkg.func_tax(sal) from emp;`
## PL/SQL 트리거
____
- 트리거는 특정 이벤트 전/후로 자동으로 실행되는 프로시저 or 함수이다.
- 자동 실행 프로시저 / 함수
- 트리거 사용
<pre><code class="sql" style="font-size:14px">CREATE TRIGGER 트리거이름 timing[BEFORE|AFTER] event[INSERT|UPDATE|DELETE]
ON 테이블이름
[FOR EACH ROW]
[WHEN conditions]
BEGIN
트리거 수행 문장
END
</code></pre>
### 트리거의 타이밍
- before : 테이블에 INSERT, UPDATE, DELETE 문이 실행될 때, 해당 문장이 실행되기 전에 트리거가 가지고 있는 BEGIN ~ END 사이의 문장을 실행한다.
- after : INSERT, UPDATE, DELETE 문이 실행되고 난 후에 트리거가 가지고 있는 BEGIN ~ END 사이의 문장을 실행한다.
### 트리거의 이벤트
- 어떤 DML문이 실행됐을때 트리거를 발생시킬 것인지 결정하는 옵션
### 트리거의 몸체
- 트리거 수행 문장을 BEGIN ~ END에 기술
### 트리거의 유형
- for each row에 의해 문장 레벨 트리거와 Row 레벨 트리거로 나눈다.
- 문장 레벨 트리거 : 테이블에 DML 문 실행되면, 트리거를 딱 한번 실행하는 것
- Row 레벨 트리거 : DML에 의해 여러 개의 행이 변경될때, 각 행이 변경될 때마다 트리거를 발생시키는 방법
<pre><code class="sql" style="font-size:14px">insert 문장 발생 후 콘솔에 출력하는 트리거 코드
CREATE OR REPLACE TRIGGER new_employee_trg
AFTER INSERT
ON EMP
BEGIN
DBMS_OUTPUT.PUT_LINE('신입사원 생성');
END;
/
</code></pre>
'PL SQL' 카테고리의 다른 글
| PL/SQL 연습 (0) | 2017.05.18 |
|---|---|
| PL/SQL - 기초(구조, 변수, 문법) (0) | 2017.05.18 |