SQL PL/SQL 프로시저 3_ 저장 프로시저의 IN,OUT파라미터

저장프로시저

--서버 on으로 열어주기--
set serveroutput on;
show serveroutput;

--저장프로시저--
create procedure myproc1
is
v_empno number(4) := 7788;
v_ename varchar2(10);
begin
v_ename := 'SCOTT';
dbms_output.put_line('v_empno' || v_empno);
dbms_output.put_line('v_ename' || v_ename);
end myproc1;
/

--저장 프로시저 실행--

--execute 실행법--
execute myproc1;

--익명프로시저 실행법--
begin
myproc1;
end;
/

프로시저를 저장하여 사용한다는 것은, 자주사용하는 비즈니스 로직을 만들어놓고 반복하여 재사용한다는 뜻이다. 익명프로시저와는 다르게 재사용할 '로직을 만들어 저장'하는 과정이기때문에 create procedure 프로시저명의 형태의 명령문으로 프로시저를 만들어야한다.

익명프로시저의 경우 사용할 변수가 있다면 declare절에 선언했다면 저장프로시저의 경우 is또는 as절에 변수를 저장해야한다.

(is나 as는 아무거나 사용해도 프로시저가 정상실행된다.) 그외에는 똑같이 begin절에 출력문을 적고 end로끝내주는데, 저장프로시저의 경우 end 저장된 프로시저명의 형태로 끝내야한다.

저장프로시저를 실행할 경우에는 2가지 방법이 있는데 프로시저 실행문인 excute로 실행하는 방법과 익명프로시저를 사용하는 경우이다. 첫번째의 경우에는 execute 프로시저명; 으로 실행해야하며, 익명프로시저로 실행하는 경우에는 익명프로시저의 실행문인 begin절에 저장된 프로시저명을 적어 실행시킨며 두 방법모두 결과는 같다.

 

파라미터

파리미터에는 in과 out모드 두가지가 있다. 파라미터를 사용한 프로시저는 in만 사용한 읽기전용 프로시저, out만 사용한 프로시저, in과 out둘다 있는 프로시저 이렇게 3가지의 형태가 있다.

 

 

IN 파라미터

--파라미터--

--in파라미터 저장 프로시저--
create or replace procedure mypro2(--파라미터 사용시 괄호사용--
p_p1 in number, --in파라미터 설정, 반드시 인수값있어야함--
p_p2 number, --in생략되어도 in모드로 설정. 인수값 필수--
p_p3 number := 2024, --in모드 생략. 2024라는 값이 디폴트로 들어감--
p_p4 number default 2034 --in모드 생락. 디폴트로 2034값 들어감--
)
as
begin
dbms_output.put_line('p_p1: '||p_p1);
dbms_output.put_line('p_p2: '||p_p2);
dbms_output.put_line('p_p3: '||p_p3);
dbms_output.put_line('p_p4: '||p_p4);
end;
/

파라미터는 create procedure바로 밑에 정의하며, 정해진 필수 규칙은 아니지만 보통 p_의 형태로 파라미터의 이름을 정의 한다. in파라미터를 적을때는 파라미터명 in 자료형으로 적는데, in을 생략한 파라미터는 디폴트값으로 전부 in파라미터의 값이 된다. 

이렇게 정의한 파라미터는 실행문인begin절에서 출력되도록 정의한다. 또한 in파라미터를 정의할때 디폴트값을 정의할 수 있는데 디폴트값으로 정의된 파라미터는 프로시저를 호출할때 사용된다. 또한 파라미터를 이용한 프로시저의 경우 파라미터를 묶어줄 괄호를 반드시 사용해야하며, 같은 파라미터명은 사용할 수 없다. 또한 저장프로시저와는 다르게 그냥 end만 끝내야한다.

--in파라미터 호출--
execute mypro2(1,2);
execute mypro2(1,2,10,20); --디폴트값에도 다른 값 넣기 가능--
execute mypro2(p_p1 => 1, p_p2 => 100); --기호 넣기 가능--

execute mypro2(1,2);

execute mypro2(1,2,10,20);

in파라미터를 호출할때는 execute를 사용하는데, in파라미터의 경우 입력값을 적는다. 이 경우 프로시저를 만들때 디폴트 값으로 정의된 파라미터의 값은 in값으로 값을 따로 적지않아도 오류가 나지 않으며 저장된 디폴트값으로 출력된다. 디폴트값으로 정의된 파라미터는 말 그대로 기본값이기때문에 디폴트값으로 정의된 값말고도 다른 값을 줄 수도 있으며, in값을 적을때 파라미터명=>값의 형태로도 적을 수 있다.

 

 

 

OUT 파라미터

--out파라미터 저장프로시저--
create or replace procedure mypro3(
p_p1 in number,
p_p2 in number,
p_p3 out number
)
as
begin
dbms_output.put_line('p_p1: '||p_p1);
dbms_output.put_line('p_p2: '||p_p2);
p_p3 := p_p1+p_p2;
end;
/

out파라미터의 경우 in파라미터와 함께 쓰이는게 대부분이다. 원래 프로시저는 반환값이 없는데 out파라미터를사용하면 마치 함수처럼 파라미터에서 반환값을 받을 수 있다는것이 특징이다. in파라미터로 값을 전달받아 out파라미터로 반환시킬 수 있도록 해주어야한다. out 파라미터의 경우 파라미터를 정의한후에 begin절에서 어떻게 값을 반환받을건지 따로 적어주어야한다. 

--out 파라미터 호출--
declare
v_value number ;
begin
mypro3(2,3,v_value);
dbms_output.put_line('v_vlaue: '|| v_value);
end;
/

out파라미터의 경우에는 호출할때 익명프로시저를 활용해서 출력해야하는데, 이 경우 반드시 declare절에서 out파라미터의 값이 담길 변수를 선언하여 출력해야한다.

 

 

IN, OUT 파라미터 실습

--in파라미터 실습--
--in파라미터로 밴드 정보 출력 폼 만들기--
create or replace procedure band_name(
p_p1 in varchar2,
p_p2 in varchar2,
p_p3 in varchar2
)
as
begin
dbms_output.put_line('밴드 명: ' || p_p1 );
dbms_output.put_line('밴드 리더: '|| p_p2);
dbms_output.put_line('대표 곡: '|| p_p3);
end;
/

--in파라미터 호출--
execute band_name('유다빈 밴드','유다빈','항해');

execute band_name('유다빈 밴드','유다빈','항해');

in파라미터 코드의 내용은 음악방송에서 출연하는 밴드의 정보를 빠르게 적을 수 있도록 하는 로직을 개설한다는 가정하에 적은 쿼리문이다. 방송에 밴드가 출연결정서를 보내면 밴드명과 밴드리더, 밴드를 소개할 수 있는 밴드 대표곡의 형태를 한번에 출력할 수 있는 폼을 만든건데, in파라미터를 이용하였고, 출력문에서 ||문자열 연결자를 사용하여 프로시저를 호출할때 값만 적어도 폼이 출력될 수 있도록했다.

 

--out파라미터 실습--
--out파라미터로 직원평가 점수 폼 만들기--
create or replace procedure emp_scoure(
p_p1 in varchar2,
p_p2 in number,
p_p3 in number,
p_p4 out number
)
as
begin
dbms_output.put_line('이름: ' || p_p1);
dbms_output.put_line('부서평가 점수(100점 만점): '|| p_p2);
dbms_output.put_line('동기평가 점수(100점 만점): '|| p_p3);
p_p4 := p_p2+p_p3;
end;
/

--out파라미터 호출--
declare
v_total number;
begin
emp_scoure('홍길동',78,90,v_total);
dbms_output.put_line('최종점수: '|| v_total);
end;
/

out파라미터는 한 기업에서 직원들의 직원 평가점수를 각각 기록하여합산하는 프로시저를 만든다는 가정하에 적은 쿼리문이다. in파라미터로 직원의 이름, 각각 분야의 점수를 설계했고 out파라미터로는 in파라미터로 받은 점수2개를 총합하여 반환해 출력하도록 했다.

---

 

 

 

학습일기

in,out파라미터의 경우 파이썬의 사용자 지정함수와 비슷하다는 생각이 들었다. 처음 파라미터를 배웠을때는 너무 헷갈렸는데 파이썬을 학습하고 난 후 다시 복습하니, 확실히 더 이해가 잘 되고 쿼리문도 쉽게쓰였다. 컴퓨터 언어는 각각 사용법은 다르지만 그 기반에 근거하는 형태의 로직은 약간씩 비슷한것같아 재미있다. 프로시저는 공부할때마다 느끼는것이지만 확실히 어렵긴해도 코드에 체계가 바로바로 보여서 그런지 비즈니스로직을 구성하고있다는 느낌이 확확 든다. 나중에는 대용량의 데이터를 가지고 프로시저를 활용할 수 있도록 해야겠다..!