Or16SubProgram
/*****************************
파일명 : Or16SubProgram.sql
서브프로그램
설명 : 저장프로시저, 함수 그리고 프로시저의 일종인 트리거를 학습한다.
*****************************/
/*
서브프로그램(Sub Program)
-PL/SQL에는 프로시저와 함수라는 두가지 유형의 SubProgram이 있다.
Select구문 뿐만 아니라 기타 DML문 등을 이용하거나 혹은 프로그래밍적인
요소등을 사용하여, 처리하기 복잡한 여러가지 작업들을 처리할 수 있도록
만들어진 데이터베이스 객체이다.
-'함수(Function)' , '프로시저(Procedure)' 그리고 프로시저의 일종인 '트리거(Trigger)'가 있다.
1. 프로시저]
-프로시저는 Return문이 없는 대신 out 파라메터를 통해 값을 반환한다.
-저장 프로시저의 장점
1. 매우좋은 성능을 가짐
2. 보안성을 높일수 있음
3. 다양한 처리가 가능함
4. 네트워크의 부하를 줄일 수 있음
- 프로시저 구문
사용법]
Create [Or Replace] Procedure 프로시저명
[{
매개변수 In 자료형, 매개변수 Out 자료형, 매개변수 InOut 자료형
]}
Is [변수선언]
Begin
실행문장
End;
-프로시저의 실행
Execute 프로시저명(매개변수1, 매개변수2....)]
-프로시저에서 매개변수의 역할
: 프로그램과 같이 값을 주고 받는 역할을 한다.
in : 실행환경에서 프로시저로 값을 전달한다.
out : 프로시저에서 실행환경으로 값을 전달한다.주로 결과값을 리턴하기
위해 사용된다.
Inout : 양쪽으로 값을 전달한다.
*/
--사원의 급여를 가져와서 출력하는 프로시저 생성하기
create or replace procedure pcd_emp_salary
is
v_salary employees.salary%type;
begin
select salary
into v_salary
from employees
where employee_id = 100;
dbms_output.put_line('사원번호100의 급여는:'|| v_salary ||'입니다.');
end;
/
--생성된 프로시저를 데이터사전에서 확인하기
select * from user_source where name like upper('%pcd_emp_salary%');
--프로시져 실행하기
execute pcd_emp_salary;
--프로시져 삭제하기
drop procedure pcd_emp_salary;
select * from user_source where name like upper('%pcd_emp_salary%');
/*
in 매개변수 사용하여 프로시져 생성과 실행하기
예시] 사원의 이름을 매개변수로 받아서 사원테이블에서 레코드를 조회후
해당 사원의 급여를 출력하는 프로시저를 생성후 실행하시오.
*/
create or replace procedure pcd_in_param_salary (param_name in employees.last_name%type)
is
v_salary employees.salary%type;
begin
select salary
into v_salary
from employees
where last_name = param_name;
dbms_output.put_line(param_name || '의 급여는' || v_salary ||'입니다');
end;
/
--데이터사전에서 확인
select * from user_source where name like upper('%pcd_in_par%');
--프로시져 실행
execute pcd_in_param_salary('Chen');
/*
out 매개변수를 사용하여 값 출력하기
예시] 위 문제와 동일하게 이름을 매개변수로 전달받아서 레코드를 조회하는
프로시저를 생성하시오. 단, 급여는 out 매개변수를 사용하여 반환하시오.
*/
create or replace procedure pcd_out_param_salary
(
param_name in employees.last_name%type,
param_salary out employees.salary%type
)
is
--변수선언이 필요없는 경우 생략가능
begin
select salary
into param_salary
from employees where last_name = param_name;
end;
/
--프로시저의 실행을 위해서 호스트호나경에서 바인드 변수를 생성
variable v_salary varchar2(30);
--바인드변수를 사용하여 프로시저 실행
execute pcd_out_param_salary('Chen', :v_salary);
--프로시저 실행후 반환된값(out 매개변수)을 출력
print v_salary;
/*
예시] 사원번호와 급여를 매개변수로 전달받아 해당사원의 급여를 수정하고 실제 수정된
행의 갯수를 받아서 출력하는 프로시저를 작성하시오.
*/
create or replace procedure pcd_update_salary(
p_empid in employees.employee_id%type,
p_sal in employees.salary%type,
rCount out number
)
is
begin
update employees
set salary=p_sal
where employee_id=p_empid;
/*
SQL%NotFound : 쿼리실행후 적용된 행이 없을경우 true반환
SQL%RowCount : 쿼리실행후 실제 적용된 행의 갯수를 반환
*/
if SQL%notfound then
dbms_output.put_line(p_empid||'은(는) 없는 사원번호입니다.');
else
dbms_output.put_line(SQL%rowcount||'명의 자료가 수정되었습니다.');
rCount := SQL%rowcount;
end if;
/*
쿼리실행후(insert/update/delete) 반드시 commit해야 실제 테이블에
적용되며 Oracle외부에서 확인할 수 있다.
*/
commit;
end;
/
select employee_id, first_name, last_name, salary
from employees;
--현재상태 : 107 Diana Lorentz 4200
--프로시져 실행을 위한 바인드변수 생성
variable r_count number;
--프로시저 실행
execute pcd_update_salary(107, 6200, :r_count);
--반환된값을 바인드변수를 통해 확인
print r_count;
--실제 레코드 확인
select employee_id first_name, last_name, salary
from employees where employee_id=107;
/*
2. 함수]
- 사용자가 PL/SQL문을 사용하여 오라클에서 제공하는 내장함수와 같은 기능을
정의한 것
- 함수는 in파라메터만 사용할 수 있으며, 반드시 반환될 값의 데이터타입을
Return문에 선언해야 한다.
-프로시저는 여러개의 결과값을 얻어올 수 있지만, 함수는 반드시 하나의 값을
반환받는다.
- 함수구문
사용법]
Create [Or Replace] Function 함수명
[(
매개변수1 [in] 자료형,
매개변수2 in 자료형
])
Return 자료형
is
[변수선언]
Begin
함수 실행 문장......;
End;
[함수와 프로시저 비교]
프로시저
1. PL/SQL문으로 실행한다.
2. Return 문이 없다.
3. 값을 반환할 때는 Out파라메타를 통해서 한다.
함수
1. 문장의 일부로서 사용한다.
2. Return문은 반드시 정의해야 한다.
3. 그러므로 값을 반환하는 것은 필수사항이다.
*/
/*
예시] 2개의 정수를 전달받아서 두 정수사이의 모든 수를 더해서 반환하는 함수를
정의해보시오.
2, 7 -> 2+3+4+......+7 = ??
*/
create or replace function getSumBetween(
num1 in number, num2 number)
return number
is
hapNum number;
begin
hapNum := 0;
for i in num1 .. num2 loop
hapNum := hapNum + i;
end loop;
return hapNum;
end;
/
select * from user_source where name like upper('%getSumBetween%');
--실행방법 1
select getSumBetween(1, 10) from dual;
--실행방법2
variable hapTxt varchar2(30);
execute :hapTxt := getSumBetween(1,100);
print hapTxt;
/*
예시] 주민번호를 전달받아서 성별을 판단하여 반환하는 함수를 정의하시오.
751024-1234567 -> 남자
120403-4123456 -> 여자
*/
create or replace function getGender (juminNum varchar2)
return nchar
is
returnValue nchar(4);
genderTxt varchar2(1);
begin
genderTxt := substr(trim(juminNum), 8, 1);
if genderTxt = 1 then
returnValue := '남자';
elsif genderTxt = 2 then
returnValue := '여자';
elsif genderTxt = 3 then
returnValue := '남자';
elsif genderTxt = 4 then
returnValue := '여자';
else
returnValue := '판단불가';
end if;
return returnValue;
end;
/
select getGender('751024-5234567') from dual;
/*
문제] 사원이름(first_name)을 매개변수로 전달받아서 급여를 반환하는 함수를
작성하시오.
함수명 : func_salary
*/
/*
create or replace function func_salary (first_name in varchar2)
return number
is
returnSal number(10);
begin
select salary into returnSal
from employees
where first_name = param_name;
return returnSal;
end;
/
*/
create or replace function func_salary (param_name in varchar2)
return number
is
returnSal number(10);
begin
select salary into returnSal
from employees
where first_name = param_name;
return returnSal;
end;
/
--실행
select func_salary ('Nancy') from dual;
--실행2
variable b_sal number;
execute :b_sal := func_salary('Bruce');
print b_sal;
--확인
select first_name, last_name, salary from employees
where first_name = 'Bruce';
/*
3. 트리거]
-자동으로 실행되는 프로시저의 한 종류로서 직접 실행은 불가능하다.
-하나의 테이블에 최대 3개의 트리거 적용가능함. 단, 트리거가 많을수록
성능저하를 초래할 가능성이 높아지므로 주의해야 한다.
-트리거 몸체안에서는 commit; rollback; 이 불가능하다.
-:new(변경후), :old(변경전) 과 같은 임시테이블은 행단위 트리거에서만 사용이 가능하다.
- 트리거종류
1. 트리거몸체(Declare - Begin End;절)의 실행횟수에 따른 분류
문장단위 트리거 : 한번만 실행
행단위 트리거 : 행단위로 변경된 횟수만큼 실행됨.
2. 타이밍에 따른 분류
Before 트리거 : 이벤트(Insert/delete/update) 발생전 트리거 몸체가 발생됨.
After 트리거 : 이벤트(Insert/delete/update) 발생후 트리거 몸체가 실행됨.
--트리거생성
사용법]
Create Trigger 트리거명
타이밍[Before | After] 이벤트[Insert | Update | Delete]
On 트리거_적용할_테이블명
[For Each Row] --행단위 트리거로 정의할 경우 입력함
--생략시 문장단위 트리거로 정의됨.
[When 트리거조건]
Declare
변수선언
Begin
트리거몸체
End;
--이벤트발생에 대한 키워드
Inserting / Deleting / Updating : insert/update/delete가 되었을 때
발생하는 이벤트명으로 해당 이벤트가 발생하면 True를 반환한다.
- 트리거삭제
Drop Trigger 트리거명;
-특정 트리거 활성/비활성화
Alter Trigger 트리거명 [Enable / Disable]
-테이블에 건 모든 트리거 활성/ 비활성화
Alter Table 테이블명 Enable/Disable All Triggers
-데이터사전 : user_triggers
*/
/*
예시] 테이블에 새로운 데이터가 입력되면 해당 데이터를 백업데이터에
저장하는 트리거를 작성해보자.
*/
--부서테이블 스키마만 복사하기
create table trg_dept_original
as
select * from departments where 1=0;
create table trg_dept_backup
as
select * from departments where 1=0;
--트리거 생성
create or replace trigger trg_backup
after insert on trg_dept_original
for each row
begin
if Inserting then
dbms_output.put_line('Insert Trigger 발생');
insert into trg_dept_backup
values (
:new.department_id,
:new.department_name,
:new.manager_id,
:new.location_id
);
end if;
end;
/
--데이터사전 확인하기
select * from user_triggers
where trigger_name like upper('%trg_back%');
--트리거 동작 테스트하기
insert into trg_dept_original values
(100, 'KOSMO31기', 10, 999);
--데이터 확인
select * from trg_dept_original;
select * from trg_dept_backup;
--데이터 삭제
delete from trg_dept_original where department_id =100;
/*
예시] 테이블에서 데이터가 삭제되면 백업테이블의 데이터도 같이 삭제되는
트리거를 작성해보자.
*/
create or replace trigger trg_delete
after delete on trg_dept_original
for each row
begin
dbms_output.put_line('Delete Trigger 발생');
if deleting then
delete from trg_dept_backup
where department_id = :old.department_id;
end if;
end;
/
--데이터사전 확인
select * from user_triggers where trigger_name like upper('%_delete%');
insert into trg_dept_original values(200, '코스모자바반', 777, 999);
--데이터확인
select * from trg_dept_original;
select * from trg_dept_backup;
--데이터삭제
delete from trg_dept_original where department_id =200;
