반응형
/********************************************************************************************
-- 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;
-- 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;
/
반응형