<저장 프로시저>
<생성>
형식)
create or replace procedure 프로시져명(매개변수)
is or as
변수 정의
begin
SQL
출력구문
조건문 , 반복문
end;
/
예시)
create or replace procedure emp01_print
is
vempno number(10);
vename varchar2(10);
begin
vempno := 1111;
vename := 'Hong';
dbms_output.put_line(vempno || ' ' || vename);
end;
/
<실행>
형식)
execute 프로시저 이름;
예시)
set serveroutput on;
execute pro_noparam;
- 이미 만들어져 있는 프로시저 실행
begin
프로시저이름;
end;
select * from emp01;
create or replace procedure del_ename(vename emp01.ename%type)
is
begin
delete from emp01
where ename = vename;
end;
exec del_ename('SCOTT');
<파라미터 사용>
파라미터 모드 | 설명 |
IN | 미지정 시 기본값으로 프로시저를 호출할 때 값을 입력받음 |
OUT | 호출할 때 값을 반환 |
IN OUT | 호출할 때 값을 입력받은 후 실행결과 값을 반환 |
- in : 값을 전달받는 용도
- out : 프로시져 내부의 실행 결과를 실행을 요청한 쪽으로 값을 전달
- int out : in + out
<IN>
생성)
CREATE OR REPLACE PROCEDURE PRO_PARAM_IN
(
param1 IN NUMBER,
param2 IN NUMBER,
param3 NUMBER := 3,
param4 NUMBER DEFAULT 4
)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('param1 : ' || param1);
DBMS_OUTPUT.PUT_LINE('param2 : ' || param2);
DBMS_OUTPUT.PUT_LINE('param3 : ' || param3);
DBMS_OUTPUT.PUT_LINE('param4 : ' || param4);
END;
/
실행)
EXCUTE pro_param_in(1,2,9,8)
- param3, param4 는 기본값이 지정되어 있는 상태이므로 호출할 때 두 개 값만 지정해도 된다
<OUT>
생성)
CREATE OR REPLACE POCEDURE pro_param_out
(
in_empno IN EMP.EMPNO%TYPE,
out_ename OUT EMP.ENAME%TYPE,
out_sal OUT EMP.SAL%TYPE
)
IS
BEGIN
SELECT ENAME, SAL INTO out_ename, out_sal
FROM EMP
WHERE EMPNO = in_empno;
END pro_param_out;
/
실행)
DECLARE
v_ename EMP.ENAME%TYPE;
v_sal EMP.SAL%TYPE;
BEGIN
pro_params_out(7788,v_ename, v_sal);
DBMS_OUT.PUT_LINE('ENAME : ' || v_ename);
END;
/
<IN OUT>
값을 입력받을 때와 프로시저 수행 후 결과 값 반환시
생성)
CREATE OR REPLACE pro_param_inout
(
inout_no IN OUT NUMBER
)
IS
BEGIN
inout_no := inout_no*2;
END;
/
실행)
DECLARE
no NUMBER;
BEGIN
no := 5;
pro_param_inout(no);
DBMS_OUTPUT.PUT_LINE('no : ' || no);
END;
/
<에러 확인>
SHOW ERRORS;
create or replace procedure sel_empno(
vempno in emp.empno%type,
vename emp.ename%type,
vsal out emp.sal%type,
vjob out emp.job%type
)
is
begin
select ename,sal,job
into
from emp
where empno = vempno;
end;
/
--바인드 변수
variable var_ename varchar2(15);
variable var_sal number;
variable var_job varchar2(9);
--exec sel_empno(7499, 바인드 변수1, 바인드 변수2, 바인드 변수3)
exec sel_empno(7499,:var_ename,:var_sal,:var_job);
print var_name;
print var_sal;
print var_job;
--사원 정보를 저장하는 저장 프로시져 만드세요
-- 사번, 이름, 직책, 매니져, 부서
--사원 정보는 매개변수 사용해서 받아온다.
create table emp02
as
select empno,ename, job,mgr,deptno
from emp
where 1 != 1;
create or replace procedure insert_sawon
(
vempno in emp02.empno%type,
vename in emp02.ename%type,
vjob in emp02.vjob%type,
vmgr in emp02.vmgr%type,
vdeptno in emp02.vdeptno%type
)
is
begin
insert into emp02
values(vempno,vename,vjob,vmgr,vdeptno);
end;
exec insert_sawon(1111,'hong','sales',2222,10);
select * from emp02;
------
is
변수정의
begin
sql 구문
출력함수
조건문, 반복문
return 리턴값; --세미콜론 사용
end;
create or replace function cal_bonus(vempno emp.empno%type)
return number
is
vsal number(7,2);
begin
select sal
into vsal
from emp
where empno = vempno;
return sal * 200;
end;
variable var_res number;
execute :var_res := cal_bonus(7788) --exec 뒤쪽에 바인드 변수를 선언해야 한다. \
print :var_res;
drop procedure insert_sawon;
drop function cal_bonus;
-- 커서
declare
begin
select *
into 변수
from emp
--where 조건식
end;
/
declare
-- 커서 : select 구문이 실행하는 결과를 가리킨다.
--cursor 커서명 is sql구문(select); --커서선언
vemp emp%rowtype;
begin
open 커서명;
loop
fectch 커서명 into vemp ;--테이블로부터 가져와서 변수에 저장하는 역할
exit when 커서명%notfound;
end loop;
close 커서명;
end;
01:50:36
declare
-- 커서 : select 구문이 실행하는 결과를 가리킨다.
--cursor 커서명 is sql구문(select); --커서선언
cursor c1 is select * form emp;
vemp emp%rowtype;
begin
open c1;
loop
fectch c1 into vemp ;--테이블로부터 가져와서 변수에 저장하는 역할
exit when 커서명%notfound;
end loop;
close 커서명;
end;
http://todduf.blogspot.com/2012/03/in-sql-out-program-inout-sql-program.html
'oracle' 카테고리의 다른 글
커서 (0) | 2022.11.02 |
---|---|
1021_데이터 사전 (0) | 2022.10.31 |
1026_PL/SQL(변수, 조건문, 반복문) (0) | 2022.10.27 |
1026_시퀀스 (0) | 2022.10.26 |
1024_제약조건(foreign key, check, default) (0) | 2022.10.26 |