내 남자의 길~!

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문

(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 = ‘Seoul’

WHEN 02’ then set val1 = ‘Pusan’

WHEN 03’ then set val1 = ‘Inchen’

ELSE    set val1 = ‘지방’

END

From      customer; 

2)  CASE단독으로 사용

Case val1  WHEN ‘01’ then set val2 = ‘Seoul’

WHEN ‘02’ then set val2 = ‘Pusan’

WHEN ‘03’then set val1 = ‘Inchen’

ELSE set val2 = ‘지방’

END CASE;

(5-4) LOOP ~ END LOOP

반복처리를 위한 Loop문

예)

Tagloop:  Loop

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:

Loop

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;