stored procedure내에서 정의한 매개변수

http://blog.naver.com/nikeruga?Redirect=Log&logNo=60127805956

stored procedure내에서 정의한 매개변수

[출처] stored procedure내에서 정의한 매개변수 |작성자 세상바다

 

 

stored procedure내에서 정의한 매개변수

(IN, OUT, INOUT)

---

IN	운영체제상에서 정의한 값을 프로시져 내부로 전달할 때, 사용하는 IN 모드 매개변수임, 매개변수 이름과 함께 모드, 데이터 타입만 선언부에서 선언하면 됨
OUT	프로시져 내에서 산출된 값을 운영체제상으로 전달할 때 사용하는 OUT 모드 매개변수임 매개변수 이름과 함께 모드, 데이터 타입만 선언부에서 선언하면 됨
IN OUT	매개변수 용도가 IN과 OUT 모드의 2가지 모드로 사용될 경우에 정의됨

EXECUTE 문	매개변수 매칭	프로시져 문
SQL> variable c number; SQL> EXECUTE test(1234, 100, :c); SQL> print c;	1234 -----> a IN number 100 <----> b IN OUT number :c <----- c OUT number	create procedure test (a IN number; b IN OUT number; c OUT number; BEGIN c:= 1234; EXCEPTION ..... END;

 
【예제】IN 매개변수
SQL> show user;
USER은 "SCOTT"입니다
SQL> CREATE SEQUENCE s_emp_id START WITH 25;

시퀀스가 생성되었습니다.

SQL> exit 

$ vi hire_emp.sql

CREATE OR REPLACE PROCEDURE hire_emp
(v_emp_name     IN emp.ename%type,
 v_emp_job      IN emp.job%type,
 v_mgr_no       IN emp.mgr%type,
 v_emp_sal      IN emp.sal%type)
IS
 v_emp_comm        emp.comm%type;
 v_dept_no         emp.deptno%type;
BEGIN
 IF v_emp_job = 'SALESMAN' THEN
    v_emp_comm := 0;     /* 0 for salesperson */
 ELSE
    v_emp_comm := NULL;  /* NULL for non-salesperson */
 END IF;
SELECT deptno INTO v_dept_no
FROM emp 
WHERE empno = v_mgr_no;
INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
  values(s_emp_id.NEXTVAL, v_emp_name, v_emp_job,
        v_mgr_no, SYSDATE, v_emp_sal, v_emp_comm, v_dept_no);
COMMIT WORK;
END hire_emp;
/
 

$ sqlplus scott/tiger

세션이 변경되었습니다.

SQL> start hire_emp.sql

프로시저가 생성되었습니다.

SQL> execute hire_emp('GREEN','CLERK',7902,1000)

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> select * from emp where ename='GREEN';

 EMPNO ENAME   JOB        MGR HIREDATE         SAL    COMM DEPTNO
------ ------- ------ ------- ------------ ------- ------- --------
    25 GREEN   CLERK     7902 14-1월 -09      1000         20


SQL>

【예제】OUT 매개변수
$ vi query_emp.sql

create or replace procedure query_emp
 (v_emp_no      IN   emp.empno%type,
  v_emp_name    OUT  emp.ename%type,
  v_emp_sal     OUT  emp.sal%type,
  v_emp_comm    OUT  emp.comm%type)
IS
BEGIN
select ename, sal, comm
INTO v_emp_name, v_emp_sal, v_emp_comm
FROM emp
WHERE empno=v_emp_no;
END query_emp;
/


SQL> start query_emp.sql

프로시저가 생성되었습니다.

SQL> variable emp_name  varchar2(15)
SQL> variable emp_sal   number
SQL> variable emp_comm  number
SQL> execute query_emp(7902, :emp_name, :emp_sal, :emp_comm);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> print emp_name;

EMP_NAME
-----------------
FORD

SQL>

【예제】INOUT 매개변수
$ vi add_one.sql

create or replace procedure add_one
 (v_phone_no    IN OUT  varchar2)
IS
BEGIN
v_phone_no :=SUBSTR (v_phone_no, 1,1) ¦¦ 1 ¦¦
  SUBSTR(v_phone_no, 2, length(v_phone_no));
END add_one;
/


$ sqlplus scott/tiger
SQL> start add_one.sql

프로시저가 생성되었습니다.

SQL> variable phone_num varchar2(15)
SQL> begin :phone_num := '1234-121212';
  2  END;
  3  /

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> execute add_one(:phone_num);

PL/SQL 처리가 정상적으로 완료되었습니다.

SQL> print phone_num

PHONE_NUM
-----------------
11234-121212

SQL>

프로시저의 매개변수(Parameter)
프로시저의 호출자가 프로시저에 매개변수를 통해 값을 전달할 수 있다.
IN, OUT, IN OUT의 세 가지 모드가 있다. 
IN 매개변수 
호출자에 의해 프로시저로 전달되는 매개변수이며, 
'읽기' 전용의 값으로 프로시저는 이 매개변수의 값을 변경할 수 없다.(디폴트 모드) 
OUT 매개변수 
프로시저에서 값을 변경할 수 있고, 
'쓰기' 기능으로 프로시저가 정보를 호출자에게 돌려주는 기능이다. 
OUT 매개변수는 디폴트 값을 지정할 수 없다. 
IN OUT 매개변수 
프로시저가 읽고 쓰는 작업을 동시에 할 수 있는 매개변수이다. 
 
【예제】 
SQL> create table test (n number); 
  
Table created. 
  
SQL> create procedure insert_into_test(p_parm in number) is 
  2  begin 
  3    insert into test values (p_parm); 
  4  end insert_into_test; 
  5  / 
  
Procedure created. 
  
SQL> select * from test; 
  
no rows selected 
  
SQL> execute insert_into_test(p_parm => 100); 
  
PL/SQL procedure successfully completed. 
  
SQL> select * from test; 
  
         N 
---------- 
       100 
  
SQL> 
 
【예제】 
SQL> connect scott/tiger 
Connected. 
SQL> create procedure emp_lookup( 
  2    p_empno  in  number, 
  3    o_ename  out emp.ename%type, 
  4    o_sal    out emp.sal%type ) as 
  5  begin 
  6    select ename, sal 
  7      into o_ename, o_sal 
  8      from emp 
  9      where empno = p_empno; 
 10  exception 
 11    when NO_DATA_FOUND then 
 12      o_ename := 'NULL'; 
 13      o_sal := -1; 
 14  end emp_lookup; 
 15  / 
  
Procedure created. 
  
SQL> variable name varchar2(10); 
SQL> variable sal number; 
SQL> execute emp_lookup('7782', :name, :sal); 
  
PL/SQL procedure successfully completed. 
  
SQL> select :name, :sal from dual; 
  
:NAME                                  :SAL 
-------------------------------- ---------- 
CLARK                                  2450 
  
SQL> 
 
【예제】 
SQL> create procedure test( 
  2    p_parm1 in out number, 
  3    p_parm2 in out number) as 
  4    test_temp number; 
  5  begin 
  6    test_temp := p_parm1; 
  7    p_parm1 := p_parm2; 
  8    p_parm2 := test_temp; 
  9  end test; 
 10  / 
  
Procedure created. 
  
SQL> set serveroutput on 
SQL> declare 
  2    l_num1 number := 100; 
  3    l_num2 number := 101; 
  4  begin 
  5    test(l_num1, l_num2); 
  6    dbms_output.put_line( 'l_num1 = ' || l_num1 ); 
  7    dbms_output.put_line( 'l_num2 = ' || l_num2 ); 
  8  end; 
  9  / 
l_num1 = 101           // 값이 서로 바뀜을 확인함 
l_num2 = 100 
  
PL/SQL procedure successfully completed. 
  
SQL>  
 
 


매개변수의 전달 방법 
[출처] stored procedure내에서 정의한 매개변수 |작성자 세상바다