[DB] Procedure

● Procedure(Stored Procedure)

1) 정의 : 자주 사용하는 쿼리문을 미리 컴파일하여 필요할 때 호출

2) 문법

CREATE [OR REPLACE] PROCEDURE 프로시명[(파라미터,...)]

IS

BEGIN

코드

END;

3) PL/SQL에서의 프로그래밍 BLOCK

DECLARE

-- 선언부

BEGIN

-- 실행 및 처리

EXCEPTION

-- 예외처리

END;

Procedure TEST ▼

1) 사번이 7788인 사원의 급여를 3500로 수정하는 프로시저 작성

CREATE OR REPLACE PROCEDURE usp_sal

IS

BEGIN

UPDATE emp

SET sal=3500

WHERE empno=7788;

END;

/

exec usp_sal;

exec 명령어로 실행 후 확인해보니 SCOTT의 급여가 수정된 것을 볼수있다.

2) 위의 예제를 좀 더 개선하여 매개변수를 활용

CREATE OR REPLACE PROCEDURE usp_sal(p_empno in number, p_sal in number)

IS

BEGIN

UPDATE emp

SET sal=p_sal

WHERE empno=p_empno;

END;

/

exec usp_sal(7788, 3000);

3) 위의 예제를 이용해서 메시지 출력( 조건문 사용 )

sql%로 시작하면 전역변수이다.

dbms_output.put_line은 자바에서 Sysout이랑 같다.

오라클에서는 || 연산자가 자바에서 +와 같다.

CREATE OR REPLACE PROCEDURE usp_sal(p_empno in number, p_sal in number)

IS

BEGIN

UPDATE emp

SET sal=p_sal

WHERE empno=p_empno;

IF sql%notfound THEN

dbms_output.put_line('없는 사원번호입니다.');

ELSE

dbms_output.put_line(to_char(sql%rowcount) || '명의 자료가 수정됨');

END IF;

END;

set serveroutput on;

-> 메세지를 띄우기 위해 설정해준다.

exec usp_sal(10000,4000);

exec usp_sal(7788, 4000);

4) 특정한 수에 7%의 세금을 계산하여 그  결과를 돌려받을 수 있는 프로시저 작성

in 값을 넘겨 받을때, out 값을 바깥으로 전달 함(리턴)

:=은 오라클에서 대입연산다이다

CREATE OR REPLACE PROCEDURE usp_tax(p_num in number, p_result out number)

IS

BEGIN

p_result := p_num * 0.07;

END;

/

var g_result number;    -> p_result값을 받을 변수 선언

exec usp_tax(5000, :g_result);

print :g_result;    -> g_result 출력

5) 사원 등록 프로시저

사원의 이름, 업무, 직속상사, 급여를 입력받는다.

부서번호는 직속상사의 부서번호화 같다.

보너스는 SALESMAN일 경우 0, 그외에는 null로 처리.

-> 테이블.컬럼명%type 으로 데이터 타입을 대체해도 프로시저가 생성된다.

-> 조건을 IS부분에서 변수를 설정해서 값을 INTO로 대입해서 사용한다.

CREATE SEQUENCE seq_empno START WITH 8000;

CREATE OR REPLACE PROCEDURE usp_register(

p_ename in emp.ename%type, p_job emp.job%type, p_mgr in emp.mgr%type, p_sal in emp.sal%type)

IS

v_deptno emp.deptno%type;

v_comm emp.comm%type;

BEGIN

SELECT deptno INTO v_deptno FROM emp WHERE empno = p_mgr;

IF p_job='SALESMAN' THEN

v_comm :=0;

ELSE

v_comm :=null;

END IF;

INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) 

VALUES(seq_empno.nextVal, p_ename, p_job, p_mgr, sysdate, p_sal, v_comm,v_deptno);

END;

/

exec usp_register('임꺽정', 'CLERK', 7788, 1500); 

6) 위의 예제에 예외처리 추가

-> 예외처리는 EXCEPTION을 사용하는데 조건문을 사용할때 WHEN~THEN절을 사용한다.

-> v_job_err는 에러정보를 담기 위한 변수

CREATE OR REPLACE PROCEDURE usp_register(

p_ename in emp.ename%type, p_job emp.job%type, p_mgr in emp.mgr%type, p_sal in emp.sal%type)

IS

v_deptno emp.deptno%type;

v_comm emp.comm%type;

v_job_err EXCEPTION;

BEGIN

SELECT deptno INTO v_deptno FROM emp WHERE empno = p_mgr;

IF p_job NOT IN('CLERK', 'SALESMAN', 'PRESIDENT', 'MANAGER', 'ANALYST') THEN

RAISE v_job_err;

ELSIF p_job='SALESMAN' THEN

v_comm :=0;

ELSE

v_comm :=null;

END IF;

INSERT INTO emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) 

VALUES(seq_empno.nextVal, p_ename, p_job, p_mgr, sysdate, p_sal, v_comm,v_deptno);

EXCEPTION

WHEN NO_DATA_FOUND THEN

dbms_output.put_line('입력된 데이터 없음');

WHEN v_job_err THEN

dbms_output.put_line('잘못된 없무가 입력됨');

WHEN OTHERS THEN

dbms_output.put_line('입력오류');

END;

/

exec usp_register('김유신', 'AAA', 7788, 1500);

set serveroutput on;

exec usp_register('김유신', 'AAA', 7788, 1500);

7) 이름을 입력받아 그 직원의 부서명과 급여를 조회

CREATE OR REPLACE PROCEDURE usp_search(

p_ename in emp.ename%type,

p_dname out dept.dname%type,

p_sal out emp.sal%type

)

IS

v_deptno    emp.deptno%type;

BEGIN

SELECT sal, deptno INTO p_sal, v_deptno

FROM emp

WHERE ename=upper(p_ename);

SELECT dname INTO p_dname

FROM dept

WHERE deptno=v_deptno;

END;

/

var g_dname varchar2(14)

var g_sal number

exec usp_search('scott', :g_dname, :g_sal);

print :g_dname

print :g_sal

8) 숫자를 입력받아 전화번호 형식으로 리턴하는 프로시저

CREATE OR REPLACE PROCEDURE usp_tel(p_tel IN OUT varchar2)

IS
BEGIN

p_tel := substr(p_tel, 1, 3) || '-' || substr(p_tel, 4);

END;

var g_tel varchar2(20)

begin

:g_tel := 1234567;

end;

/

exec usp_tel(:g_tel)

print : g_tel