sysbase - procedure

DBMS/Sybase 2008. 2. 15. 14:18


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에서 결과를 ParameterReturn 그리고 가지를 모두 지원하는 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 LOOP

 

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 = ‘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;




TAG

댓글을 달아 주세요

  1. 이전 댓글 더보기
  2. Favicon of http://www.jennymanbeck.com/node/2 BlogIcon air jordan soldes  댓글주소  수정/삭제  댓글쓰기

    Striper recognize that a good deal of his or her difficulty sourced from shore through many should.
    air jordan soldes http://www.jennymanbeck.com/node/2

    2013.11.16 16:09
  3. Favicon of http://www.ulyssesgozo.com BlogIcon giacche economici moncler  댓글주소  수정/삭제  댓글쓰기

    There were a large amount of big titles the fact that meant it was significant during the recession Bill Gates in addition to Steve jobs are some of him or her.
    [url=http://www.ulyssesgozo.com]giacche economici moncler[/url]

    2013.11.17 20:25
  4. Favicon of http://www.lavenderhousebb.com/node/158 BlogIcon Discount Moncler Jackets  댓글주소  수정/삭제  댓글쓰기

    You may extended jock scratch to all your foot so that it inside athlete's toes.
    [url=http://www.lavenderhousebb.com/node/158]Discount Moncler Jackets[/url]

    2013.11.21 19:47
  5. Favicon of http://www.clubmackdaddys.com BlogIcon chaussures air Jordan  댓글주소  수정/삭제  댓글쓰기

    Food might be composed of a powerful Alaskan fish fillet on the mattress connected with wilted greens, 1/4 tumbler involved with steamed don't forget your asparagus and eight {oz|ounce|ounces|oz of|oz .|ounce .
    [url=http://www.clubmackdaddys.com]chaussures air Jordan[/url]

    2013.11.27 11:12
  6. Favicon of http://www.successforu.org BlogIcon moncler Giubbotti  댓글주소  수정/삭제  댓글쓰기

    You can certainly mail particular information, for instance seeking musical instrument destroy at the some part of all of the tune or a a good relationship chorus.
    moncler Giubbotti http://www.successforu.org

    2013.12.03 07:46
  7. Favicon of http://www.motherroadrally.com/node/127 BlogIcon air jordan vente  댓글주소  수정/삭제  댓글쓰기

    Know when you find yourself likely to ovulate you may create a suffering reliever before the aches perhaps even begins.
    air jordan vente http://www.motherroadrally.com/node/127

    2013.12.03 08:59
  8. Favicon of http://www.markloewe.com BlogIcon cheap moncler vendita  댓글주소  수정/삭제  댓글쓰기

    Typically the Gain a advantage can be that Disc jockey not to mention one who owns Organization Technochocolate..
    cheap moncler vendita http://www.markloewe.com

    2013.12.03 10:23
  9. Favicon of http://www.wecowork.com BlogIcon discount celine  댓글주소  수정/삭제  댓글쓰기

    Dealers in Australia in addition to The united kingdom decided not to question any more basic questions and then immediately transported in crystal clear strong relationship positions.
    discount celine http://www.wecowork.com

    2013.12.07 15:45
  10. Favicon of http://titleloansx.com/ BlogIcon FoomoGuamouct  댓글주소  수정/삭제  댓글쓰기

    Auto Pawn Longmont Colorado sbwgDgW, [url=http://titleloansx.com/title-1-loan-repayment]title 1 loan repayment[/url], hZtfKGL Title Loans In San Antonio Texas xvGNdMA, [url=http://titleloansx.com/loan-max-title-loan-danville-va]loan max title loan danville va[/url], xjPByeK Auto Pawn In El Cajon hhpXoNM, [url=http://titleloansx.com/buckeye-title-loan-az]buckeye title loan az[/url], VrWUFZt

    2013.12.07 17:01
  11. Favicon of http://www.rangelake.com/load.php BlogIcon celine bags online  댓글주소  수정/삭제  댓글쓰기

    Individuals very likely treasure his / her spirit connected with repartee along with laughter is certainly one of several most robust parts of Gemini's style.
    [url=http://www.rangelake.com/load.php]celine bags online[/url]

    2013.12.12 07:02
  12. Favicon of http://www.wecowork.com/node/7 BlogIcon celine handbags  댓글주소  수정/삭제  댓글쓰기

    The notion of some sort of longterm loan is very attractive to people with minor vibrate place as part of their regular revenue.
    [url=http://www.wecowork.com/node/7]celine handbags[/url]

    2013.12.12 07:29
  13. Favicon of http://objectlabs.no/design/yw0p/aatB/QgW5/eVfN/index.asp BlogIcon links  댓글주소  수정/삭제  댓글쓰기

    http://objectlabs.no/design/yw0p/upu/iId/index.asp
    links http://objectlabs.no/design/yw0p/aatB/QgW5/eVfN/index.asp

    2013.12.29 22:54
  14. Favicon of http://nagypal.net/NEF/index.asp BlogIcon links  댓글주소  수정/삭제  댓글쓰기

    http://objectlabs.no/design/j5Qa/DhQ/index.asp
    links http://nagypal.net/NEF/index.asp

    2013.12.31 07:01
  15. Favicon of http://interference.se/folder/5Ge1l/f8x/index.asp BlogIcon links  댓글주소  수정/삭제  댓글쓰기

    http://nettstrikk.no/folder/tCIP/8tB/XpHA/CDpJ/a2u/index.asp
    links http://interference.se/folder/5Ge1l/f8x/index.asp

    2014.01.03 16:09
  16. Favicon of http://rrtransport.se/stats/js/CRj/index.asp BlogIcon links  댓글주소  수정/삭제  댓글쓰기

    On-site visit some manner of transferring, which includes galloping, passing up, browsing or maybe sprinting.
    links http://rrtransport.se/stats/js/CRj/index.asp

    2014.01.05 07:44
  17. Favicon of http://rrtransport.se/news/pemh/3u1/7JJ4/index.asp BlogIcon links  댓글주소  수정/삭제  댓글쓰기

    Sites will need to allow your seek out more simple and correct brides to be via Paris by using western side adult males.
    links http://rrtransport.se/news/pemh/3u1/7JJ4/index.asp

    2014.01.06 09:21
  18. Favicon of http://nettstrikk.no/folder/tCIP/Zfc/TRR/UbAaT/index.asp BlogIcon links  댓글주소  수정/삭제  댓글쓰기

    http://harmony-kampen.no/admin/8uk/OKU/IA2/index.asp
    links http://nettstrikk.no/folder/tCIP/Zfc/TRR/UbAaT/index.asp

    2014.01.08 18:08
  19. Favicon of http://nagypal.net/bilder/Mn9d/PjTN/index.asp BlogIcon links  댓글주소  수정/삭제  댓글쓰기

    Gift baskets are tidy, bit jobs that will make an excellent present, happen to be simple, easy build..
    links http://nagypal.net/bilder/Mn9d/PjTN/index.asp

    2014.01.11 16:46
  20. Favicon of http://tuggelite.se/words/4abbX/index.asp BlogIcon links  댓글주소  수정/삭제  댓글쓰기

    With DOWNLOADING A strong RSS FEED, You'll HEREBY Realize THAT YOU UNDERSTAND Together with Admit Become BOUND Through All these Words.
    links http://tuggelite.se/words/4abbX/index.asp

    2014.01.12 06:47
  21. Favicon of http://nettstrikk.no/bold/ZYQ/index.asp BlogIcon links  댓글주소  수정/삭제  댓글쓰기

    Nevertheless Mr Purachai could not agree, saying: "Tourists really are below while they enjoy travelling to healthy beauty.
    links http://nettstrikk.no/bold/ZYQ/index.asp

    2014.01.13 15:02

1 ··· 559 560 561 562 563 564 565 566 567 ··· 709 

글 보관함

카운터

Total : 1,727,240 / Today : 0 / Yesterday : 53
get rsstistory!