sqlserver 存储过程总结

*****************************************************

*** author：Susan

*** date: 2005 / 08 / 05

*** expliation:如何寫存儲過程的格式及例子，有游標的用法！

*** 本版:SQL SERVER 版！

******************************************************/

在存儲過程中的格式規格：

CREATE PROCEDURE XXX

/*

列舉傳入參數

1：名稱，2：類型，包括長度

Eg:@strUNIT_CODE varCHAR(3)

*/

參數1，

參數2……………

As

/*

定義內部參數

1：名稱，2：類型，包括長度

Eg:@strUNIT_CODE varCHAR(3)

*/

Declare

參數1，

參數2……………

/*

初始化內部參數

Eg:SET @strUNIT_CODE=’’

*/

Set參數1的初始值

Set參數2的初始值…………

/*

過程的主內容區

Trascation：這裡起到的作用是，如果他中間的任何一個執行錯誤，就全部執行都返回，這裡sql sever 7.0以前一定要寫入，以後的就可以省略

Return：結束這支sp

*/

Begin trascation

/*

1:可以取得需要的值以存在內部參數中

Eg:SELECT @strUNIT_CODE=UNIT_CODE FROM UNIT WHERE …….

2:可以用取到的或傳入的參數進行判斷，來進行update,insert,delete 等等操作

eg: IF @strUNIT_CODE=’’

BEGIN

//具體的操作

End

Else

Begin

//具體的操作

End

3：有關游標的問題

Eg:

declare db cursor for //聲明一個游標(db為其名稱)

SELECT UNIT_NAME FROM UNIT WHERE LEFT(UNIT_CODE,2)=LEFT(@strTO,2)//記錄集

open db //打開游標

fetch next from db into @strUNIT_NAME //將第一個值放入一個參數中

while @@fetch_status = 0 ---存在本筆值向下循環

(0：順利執行;-1：失敗，或資料列超出結果集;-2：擷取的資料列已遺漏)

BEGIN ----開始循環

//個體操作

End ----結束循環

Close db ---關閉游標

deallocate db //移除資料指標參考

*/

Commit trascation

Return



下面是一個例子

CREATE PROCEDURE TEST_2

@strTO VARCHAR ( 3 )

AS

DECLARE

@strUNIT_NAME VARCHAR ( 800 ),

@strSQL VARCHAR ( 8000 ),

@Link VARCHAR ( 1 ),

@Link1 VARCHAR ( 1 )



SET @strUNIT_NAME = ''

SET @strSQL = ''

SET @Link = ''

SET @Link1 = ''



/*

處理update 的部分

EXEC TEST_2 '011'

EXEC TEST_2 ''

SELECT UNIT_NAME FROM UNIT WHERE UNIT_CODE='011'

*/

BEGIN TRANSACTION

IF @strTO <> ''

BEGIN

UPDATE UNIT SET UNIT_NAME = REPLACE (UNIT_NAME, ' * ' , '' ) WHERE UNIT_CODE = @strTO

END

ELSE

BEGIN

UPDATE UNIT SET UNIT_NAME = UNIT_NAME + ' * ' WHERE UNIT_CODE = ' 011 '

END

/*

EXEC TEST_2 '011'

功能說明：本sp用於處理cursor問題

*/

IF @strTO <> ''

BEGIN

declare db cursor for -- 必需聲明在查詢的前面

SELECT UNIT_NAME FROM UNIT WHERE LEFT (UNIT_CODE, 2 ) = LEFT ( @strTO , 2 ) -- -取到相關信息

END

ELSE

BEGIN

declare db cursor for -- 必需聲明在查詢的前面

SELECT UNIT_NAME FROM UNIT WHERE LEFT (UNIT_CODE, 2 ) = LEFT ( ' 011 ' , 2 ) -- -取到相關信息

END

open db -- -開起取到的信息

fetch next from db into @strUNIT_NAME -- -把第一筆放入@strUNIT_NAME中

while @@fetch_status = 0 -- -表示存在本筆資料

BEGIN -- --開始循環

set @strSQL = @strSQL + @Link1 + @Link + @strUNIT_NAME -- --設定保存的值

fetch next from db into @strUNIT_NAME -- --進行下次循環

SET @Link = CHAR ( 13 ) + CHAR ( 10 )

SET @Link1 = ' , '

END -- --結束循環

close db -- -關閉信息

deallocate db -- -移除資料指標參考

SELECT @strSQL

COMMIT TRANSACTION

RETURN如果循环insert的例子