pl/sql 서브 프로그램의 블록 구조<header>
IS / As
------------------------------------------> 여기까지서브 프로그램 명세(specification)
선언 부분
begin
실행 부분
exception (선택 사항)
exception 섹션
end;
------------------------------------------> 여기까지 서브 프로그램 몸체(body)
pl/sql 서브 프로그램 사용 방법
pl/sql 프로그램에서 한 번 이상 반복되느 코드를
서브 프로그램으로 작성하고 작성된 서브 프로그램을 여러 위치에서 호출하여 사용
isql*plus를 사용한 서브 프로그램 개발
①에디터 편집기를 통해 프로시져를 작성합니다.
②isql*plus에 스크립트를 로드해서
③실행합니다.
내장 스포시저 및 함수를 호출하는 방법
isql*plus 환경 --> execute log_execution
oracle forms developer와 같은 오라클 개발 툴 --> log_execution;
다른 프로시저 --> create or replace procedure leave_emp(p_id in employees.employee_id%type)
is
begin
declare from employees
where employee_id=p_id;
log_execution;
end
leave_emp;
프로시저란?
프로시저는 작업을 수행하는 일종의 서브 프로그램이며 반복 실행을 위해 데이터 베이스에 스키마
객체로 저장될 수 있다.
프로시저 작성 구문
create [or replace] procedure procedure_name
[(parameter1 [mode1] datatype1,
parameter2 [mode2] datatype2,
. . .)]
IS / AS
PL/SQL Block;
procedure_name : 프로시저 이름부여
[mode] : 인수 유형이며 IN ,OUT, IN OUT
datatype : 인수의 데이터 유형이며 모든 SQL/PLSQL데이터 유형을 사용할 수 있다.
여기서 주의 할점은 SIZE는 지정하지 않는다.
NOTE : 내장 프로시저는 PL/SQL 블록에서는 호스트 변수 또는 바인드 변수를 참조할 수
없습니다.
프로시저 개발
1. 편집기나 에디터를 통해 프로시저 코드를 작성합니다.
2. iSQL*PLUS를 통해 작성한 파일을 로드합니다.
3. 작성한 파일에 나와있는 소스 코드를 컴파일해서 p code 로 오라클 서버에 저장합니다.
호출시 이러한 pcode를 통해 실행을 합니다.
형식 매개 변수와 실제 매개변수
◇ 형식 매개변수 - 서브 프로그램 명세의 파라미터 목록에 선언된 변수로 서브 프로그램의 header에
선언된 파라미터
◇ 실제 매개변수 - 서브 프로그램 호출의 파라미터 목록에서 참조되는 변수 또는 표현식
예)raise_sal(v_id,2000) , raise_sal(v_id,raise+100)
프로 시저 파라미터 모드
in (기본값 ) : 상수 값을 호출 환경에서 프로시저로 전달합니다.
out : 값을 프로시저에서 호출 화경으로 전달합니다.
in out : 동일한 파라미터를 사용하여 호출 환경에서 프로시저로 값을 전달하고 프로시저에서 호출
환경으로 다른 값을 전달 할 수 있습니다.
IN 파라미터 : 예제
create or replace procedure raise_salary
(p_id in employees.employee_id%type)
is
begin
update employees
set salary=saalry+1.10
where employee_id=p_id;
end raise_salary;
/
execute raise_salary(176);
▷ 176 값을 받아서 procedure를 호출하여 employee_id가 입력받은 값 176과 같으면
봉급을 10%인상하라는 프로시져
OUT 파라미터 : 예제
create or replace procedure query_emp
(p_id in employees.employee_id%type,
p_name out employees.last_name%type,
p_salary out employees.salarytype,
p_comm out employees.commission_pct%type)
is
begin
select last_name,salary,commission_pct
into p_name,p_salary,p_comm
from employees
where employee_id=p_id;
end query_emp;
/
variable g_name varchar2(25);
variable g_sal number;
variable g_comm number;
execute query_emp(171,:g_name,:g_comm);
▷employee_id=176을 입력받아서 그에 따른 사원이름,봉급,commission_pct 정보를 호스트 변수
에 기록한다.
NOTE : out 변수에 상수 또는 표현식을 실제 매개변수로 할당하면 컴파일 오류가 발생한다.
IN OUT 파라미터 : 예제
기록한다. create or replace procedure format_phone
(p_phone_no in out varchar2)
is
begin
p_phone_no:='(' ||substr(p_phone_no,1,3)||')'||substr(p_phone_no,4,3)||'-'||
substr(p_phone_no,7);
end format_phone;
/
variable g_phone_no varchar2(15);
begin
:g_phone_no:='8006330575';
end;
/
print g_phone_no
execute format_phone(:g_phone_no);
print g_phone_no;
▷ 호스트 변수 g_phone_no 에 입력된 숫자 값 8006330575를 입력받아서 형태를
(800)633-0575 로 변환
파라미터 전달 방식
≫위치 적용 방식 : 실제 매개 변수를 형식 매개변수와 동일한 순서로 나열
≫이름 지정 방식 : 실제 매개 변수를 형식 매개변수와 이름으로 연결시켜 나열('=>' 기호 사용)
≫조합 방식 : 일부는 위치 적용 방식으로 일부는 이름 지정 방식으로 나열
->조합 방식에서 처음오는 매개변수와 두번째로 오는 매개변수에 따른 가능여부
( 위치적용 방식, 이름 적용 방식 ) -:> 가능
( 이름 적용 방식, 위치적용 방식) -:> 불가능
파라미터의 default 옵션
create or replace procedure add_dept
(p_name in departments.department_name%type default 'unknown',
p_loc in departments.location_id%type default 1700)
is
begin
insert into departments(department_id,department_name,location_id) values
(departments_seq.nextval,p_name,p_loc);
end add_dept;
/
begin
add_dept;
add_dept('training',1700);
add_dept(p_loc=>1700, p_name=>'education');
add_dept(p_loc=>1700);
end;
/
select department_id,department_name,location_id
from departments;
서브 프로그램 선언
create or replace procedure leave_emp2
(p_id in employees.employee_id%type)
is
----------------------------------------------------
procedure log_exec
is
begin
insert into log_table(user_id,log_date) ===>또 다른 서브 쿼리
values (user,sysdate);
end log_exec;
-----------------------------------------------------
begin
delete from employees
where employee_id=p_id;
log_exec;
end leave_emp2;
/
≫누가 언제 삭제 했는지를 log_table에 저장
note : 서브 프로그램의 선언은 사용하기 전에 먼저 선언 되있어야 함.
익명 pl/sql 블록에서 프로시저 호출
declare
v_id number:=163;
begin
raise_salary(v_id); ->이미 만들어져 있는 프로시저를 익명PL/SQL에서 호출
commit;
.....
end;
/
다른 프로시저에서 프로시저 호출
create or replace procedure process_emps
is
cursor emp_cursor is
select employee_id
from employees;
begin
for emp_rec in emp_cursor
loop
raise_salary(emp_rec.employee_id); =>이미 만들어져 있는 프로시저를 다른
프로시저에서 호출하여 사용
end loop;
commit;
end process_emp;
/
≫각 사원의 id를 raise_salary프로시저에 전달하여 회사 전체 급여를 10%인상합니다.
처리된 예외
▷호출 프로시저에 있는 다른 프로시저로 넘어간 상태에서 exception 이 발생한 경우
해당 프로시저에 exception 처리 부분이 없는 경우 원래 프로시저에 있는 exception으로 가서
처리 됩니다.
처리 되지 않은 예외
▷호출 프로시저에 있는 다른 프로시저로 넘어간 상태에서 exception 이 발생하였는데
예외 처리가 안될 경우 proc2 프로시저는 롤백되게 되고 proc1 프로시저로 넘어가서
예외를 처리한다. 만약 여기서도 처리가 안될경우 전체가 rollback된다.
프로시저 제거
drop procedure procedure_name <==제거하기위해 create procedure권한이 필요하다.
ex)
drop procedure raise_salary;
