7.1.3 사용자Procedure
사용자가 필요에 의해 작성한 Logic을 처리하는 Procedure
※ row단위 반복 작업은 Performance가 좋지 않으므로 특히Cursor 사용을 금하는 것이 좋다.
1) Syntax
CREATE PROCEDURE proc_name (parameter,…)
BEGIN
-- Business_logic
END
2) Parameter
Procedure에 Parameter를 전달하거나 또는 Procedure에서 결과를 Parameter로Return 그리고 두 가지를 모두 지원하는 Parameter유형이 있다
1) 예제) Create Procedure test_prc(in cust_id char(4)
,Inout cname char(20) default NULL
,out sqty decimal(12))
2) cust_id는 input parameter로 procedure call할 때 반드시 입력
3) cname은 in-out parameter로 procedure call한 caller에 입력을 받을 뿐 아니라 Return도 한다. 그리고 default를 선언하면 입력이 없을 때default값으로 대치 한다. 위의 예에서는 Null로 대치 .
4) sqty는 procedure가 내부적인 Logic을 처리하고 최종적으로 할당된 값을 Caller 에게Return한다.
3) Business Logic
업무에 맞는 Logic를 구현하기 위해 기본적으로 데이터를 처리하는 SQL, 변수처리문, Control문이 필요하다.
4) 변수 처리문
Declare=> 변수 선언
Set=> 변수에 값을 할당
예제)
Declare val1 decimal(12);
Set val1= 234567;
5) Control문
|
BEGIN END |
IF … ELSE IF ELSE END IF |
CASE … WHEN … WHEN … ELSE … END CASE |
LOOP … END WHILE … |
FOR … END FOR |
LEAVE … CALL … |
(5-1) BEGIN ~ END
여러 문장을 하나의 Batch로 묶는 Command로 Procedure에는
시작과 끝을 나타냄
예)
Begin
Declare val1 integer;
Select ~~~
~~~
END ;
(5-2) IF ~ END IF
조건에 따라 분기 하거나 다른 Logic을 구현할 때 이용
예)
IF val1 = 1 Then
set val2 = 100;
Call prc1();
Elseif val1 = 2 Then
set val2 = 200;
Call prc2()
Else
Set val2 = 0;
End if;
(5-3) CASE ~ END CASE
1) 경우에 따라 값을 달리 처리하고자 할 때 이용 SELECT문 에서 사용
select CASE cust_reg WHEN 01’ then set val1 = ‘
WHEN 02’ then set val1 = ‘
WHEN 03’ then set val1 = ‘Inchen’
ELSE set val1 = ‘지방’
END
From customer;
2) CASE단독으로 사용
Case val1 WHEN ‘01’ then set val2 = ‘
WHEN ‘02’ then set val2 = ‘
WHEN ‘03’then set val1 = ‘Inchen’
ELSE set val2 = ‘지방’
END CASE;
(5-4) LOOP ~ END
반복처리를 위한 Loop문
예)
Tagloop:
Fetch next test_cur into reg, cyear, sqty;
If sqlstate = err_notfound
Leave curloop;
End if;
End loop Tagloop;
(5-5) WHILE
반복처리를 위한 While문 (dbisqlc로 실행 안됨)
예)
Begin
Declare @id int;
Select @id = 1;
While (@id < 4)
Begin
Insert aaa values(@id);
Select @id = @id +1;
End
End ;
(5-6) FOR ~ END FOR
Cursor를 처리할 때 반복 문으로 이용
예)
For curfor As test_cur
Cursor For
Select cust_reg, order_year, sum(order_qty) oqty
From customer a, order_detail b
Where a.cust_id = b.cust_id
Group by cust_reg, order_year
For read only
DO
Set reg = cust_reg;
Set cyear = order_year;
Set sqty = oqty;
End For curfor;
(5-7) LEAVE , CALL
LEAVE는 Loop문을 빠져 나올 때 이용
CALL은 Procedure를 수행 시킬 때 이용
6) Stored Procedure예
Create procedure Test_proc(in zip char(6))
Begin
Declare err_notfound Exception for sqlstate ‘02000’;
Declare reg char(20);
Declare cyear char(4);
Declare sqty decimal(12);
Declare test_cur cursor For
Select cust_reg, order_year, sum(order_qty)
From customer a, order_detail b
Where a.cust_id = b.cust_id And cust_zip = zip
Group by cust_reg, order_year
For read only;
Open test_cur;
Curloop:
Fetch next test_cur into reg, cyear, sqty;
If sqlstate = err_notfound
Leave curloop;
End if;
End loop Curloop;
Close test_cur;
End
7) Error 처리 EXCEPTION
Error발생 시 EXCEPTION 루틴을 수행하고 PROCEDURE를 종료한다.
예)
Create proc SP_Test( out SUB_RETURN_SQL numeric(5),
out SUB_JOB_STATUS char(1),
out SUB_STATUS_MSG char(50))
Begin
Select count(*) from test;
Set SUB_STATUS_MSG=’ERROR’
EXCEPTION
When others then
Set SUB_RETURN_SQL=SQLCODE;
Set SUB_JOB_STATUS=’1’;
Rollback work;
Message ‘ERROR_MSG :‘,SUB_STATUS_MSG type info to client;
Message ‘SQL_CODE :‘,SUB_RETURN_SQL type info to client;
Return;
End;