1027_저장 프로시저

<저장 프로시저>

 

<생성>

 

형식)

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