반응형
/********************************************************************************************
-- Title : [10g] 프로시저 IN/OUT/INOUT 파라미터
-- Reference : radiocom.kunsan.ac.kr
-- Key word : procedure proc parameter in out inout
********************************************************************************************/
/**********************************
-- IN 매개변수
**********************************/
-- 테이블 생성
-- drop table scott.emp2 purge;
create table scott.emp2
as
select *
from scott.emp;

-- 확인
select * from scott.emp2;

-- 프로시저 생성
-- drop procedure scott.hire_emp;
CREATE OR REPLACE PROCEDURE scott.up_hire_emp
( a_emp_name     IN scott.emp2.ename%type
, a_emp_job      IN scott.emp2.job%type
, a_mgr_no       IN scott.emp2.mgr%type
, a_emp_sal      IN scott.emp2.sal%type
)
IS
  i_emp_comm        scott.emp2.comm%type;
  i_dept_no         scott.emp2.deptno%type;
BEGIN
    IF a_emp_job = 'SALESMAN' THEN
       i_emp_comm := 0;     /* 0 for salesperson */
    ELSE
       i_emp_comm := NULL;  /* NULL for non-salesperson */
    END IF;

    INSERT INTO scott.emp2 (empno, ename, job, mgr, hiredate, sal, comm, deptno)
    VALUES(1111, a_emp_name, a_emp_job,
           a_mgr_no, SYSDATE, a_emp_sal, i_emp_comm, i_dept_no);
      
      
    COMMIT WORK;
END up_hire_emp;

-- 프로시저 수행
SQL> execute scott.up_hire_emp('dbrang','manager',111,111);
SQL> execute scott.up_hire_emp(a_emp_name => 'ttt', a_emp_job => 'worker', 222, 222);
 

/**********************************
-- OUT 매개변수
**********************************/
-- 프로시저 생성
CREATE OR REPLACE PROCEDURE scott.up_query_emp
( a_emp_no      IN   scott.emp2.empno%type
, a_emp_name    OUT  scott.emp2.ename%type
, a_emp_sal     OUT  scott.emp2.sal%type
, a_emp_comm    OUT  scott.emp2.comm%type
)
IS
BEGIN
    SELECT ename, sal, comm
    INTO a_emp_name, a_emp_sal, a_emp_comm
    FROM scott.emp2
    WHERE empno = a_emp_no;
END up_query_emp;

-- 프로시저 수행
SQL> variable emp_name  varchar2(15)
SQL> variable emp_sal   number
SQL> variable emp_comm  number
SQL> execute scott.up_query_emp(7902, :emp_name, :emp_sal, :emp_comm);
SQL> print emp_name;
SQL> print emp_sal;
SQL> select :emp_name, :emp_sal from dual;
 

/**********************************
-- INOUT 매개변수
**********************************/
-- 프로시저 생성
CREATE OR REPLACE PROCEDURE scott.up_add_one
( a_phone_no    IN OUT  VARCHAR2
)
IS
BEGIN
    a_phone_no := SUBSTR (a_phone_no, 1,1) || 1 || SUBSTR(a_phone_no, 2, length(a_phone_no));
END up_add_one;

-- 프로시저 실행
SQL> variable phone_num varchar2(15)
SQL> BEGIN :phone_num := '1234-121212';
     END;
     /
SQL> EXECUTE add_one(:phone_num);
SQL> PRINT phone_num;

-- 프로시저 생성
CREATE OR REPLACE PROCEDURE scott.up_test
( p_parm1 IN OUT NUMBER
, p_parm2 IN OUT NUMBER
)
AS
  test_temp NUMBER;
BEGIN
  test_temp := p_parm1;
  p_parm1 := p_parm2;
  p_parm2 := test_temp;
END up_test;
 
 
-- 프로시저 실행
SQL> declare
       l_num1 number := 100;
       l_num2 number := 101;
     begin
       scott.up_test(l_num1, l_num2);
       dbms_output.put_line( 'l_num1 = ' || l_num1 );
       dbms_output.put_line( 'l_num2 = ' || l_num2 );
     end;
     /
반응형

+ Recent posts