SQLSERVER存儲過程的寫法格式規格

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的例子

DECLARE   @strLoginID   VARCHAR ( 16 )
BEGIN
declare  db  cursor   for
SELECT  LoginID  FROM  dbo.s_Users  WHERE   len (UnitCoding)  in ( 9 , 12 )
END
open  db
fetch   next   from  db  into   @strLoginID
while   @@fetch_status   =   0   BEGIN
insert   into  s_P_User
select   @strLoginID ,LevelID  from  s_P_User  where  LoginID  =   ' aa '
fetch   next   from  db  into   @strLoginID
END
close  db
deallocate  db