通用存儲過程的編寫

2024-07-21 02:07:13

字體：大中小

供稿：網(wǎng)友

通用存儲過程的編寫

對數(shù)據(jù)庫的操作基本上就四種：insert、update、delete和select，而update和insert兩種操作又可以作簡單的合并，這樣下來，基本上一個數(shù)據(jù)表對應(yīng)三個存儲過程便可以完成絕大多數(shù)的數(shù)據(jù)庫操作。存儲過程命名規(guī)則：operate_tablename。比如表order_info對應(yīng)三個存儲過程：addedit_order_info、delete_order_info、search_order_info，下面先列出相關(guān)代碼，然后作總體分析。

一、addedit_order_info

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

** name      :    addedit_order_info

** creater        :    ppcoder designed by ppcode studio(pptech.net)

** create date    :    2004-9-6 8:30:17

** modifer        :   rexsp

** modify date    :    2004-9-6 8:30:17

** description : addedit information for order_info

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

alter procedure dbo.addedit_order_info

(

     @orderstateid int = -1,

     @orderstateid_min int = -1,

     @orderstateid_max int = -1,

     @orderuserid int = -1,

     @orderuserid_min int = -1,

     @orderuserid_max int = -1,

     @orderid int = -1,

     @orderid_min int = -1,

     @orderid_max int = -1,

     @productid int = -1,

     @productid_min int = -1,

     @productid_max int = -1,

     @customizeid int = -1,

     @customizeid_min int = -1,

     @customizeid_max int = -1,

     @outid int = 0 output

)

as

if @orderid=-1

     begin

         insert into [order_info] (

                                          [orderstateid],

                                          [orderuserid],

                                          [productid],

                                          [customizeid]

                                     )

                                     values(

                                          @orderstateid,

                                          @orderuserid,

                                          @productid,

                                          @customizeid

                                     )

         set @outid = @@identity

     end

else

     begin

         declare @strsql nvarchar(1000)

         set @strsql = 'update [order_info] set @tmporderid = @tmporderid'

         if @orderstateid <> -1

              begin

                   set @strsql = @strsql + ', [orderstateid] = @tmporderstateid'

              end

         if @orderuserid <> -1

              begin

                   set @strsql = @strsql + ', [orderuserid] = @tmporderuserid'

              end

         if @productid <> -1

              begin

                   set @strsql = @strsql + ', [productid] = @tmpproductid'

              end

         if @customizeid <> -1

              begin

                   set @strsql = @strsql + ', [customizeid] = @tmpcustomizeid'

              end

         set @strsql = @strsql + ' where [orderid] = @tmporderid'

         begin tran

         execute sp_executesql @strsql, n'

                       @tmporderstateid int,

                       @tmporderuserid int,

                       @tmporderid int,

                       @tmpproductid int,

                       @tmpcustomizeid int',

                       @[email protected],

                       @[email protected],

                       @[email protected],

                       @[email protected],

                       @[email protected]

         set @outid = @orderid

         if @@error!=0

              begin

                   rollback

              end

         else

              begin

                   commit

              end

     end

return

二、delete_order_info

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

** name      :    delete_order_info

** creater        :    ppcoder designed by ppcode studio(pptech.net)

** create date    :    2004-9-6 8:30:17

** modifer        :   rexsp

** modify date    :    2004-9-6 8:30:17

** description : delete information for order_info

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

alter procedure dbo.delete_order_info

(

     @orderstateid int = -1,

     @orderstateid_min int = -1,

     @orderstateid_max int = -1,

     @orderuserid int = -1,

     @orderuserid_min int = -1,

     @orderuserid_max int = -1,

     @orderid int = -1,

     @orderid_min int = -1,

     @orderid_max int = -1,

     @productid int = -1,

     @productid_min int = -1,

     @productid_max int = -1,

     @customizeid int = -1,

     @customizeid_min int = -1,

     @customizeid_max int = -1,

     @outid int = 0 output

)

as

declare @strsql nvarchar(1000)

set @strsql = 'delete from [order_info] where @tmporderid = @tmporderid '

if @orderstateid<>-1

     begin

         set @strsql = @strsql + ' and orderstateid = @tmporderstateid'

     end

if @orderstateid_min<>-1

     begin

         set @strsql = @strsql + ' and orderstateid_min = @tmporderstateid_min'

     end

if @orderstateid_max<>-1

     begin

         set @strsql = @strsql + ' and orderstateid_max = @tmporderstateid_max'

     end

if @orderuserid<>-1

     begin

         set @strsql = @strsql + ' and orderuserid = @tmporderuserid'

     end

if @orderuserid_min<>-1

     begin

         set @strsql = @strsql + ' and orderuserid_min = @tmporderuserid_min'

     end

if @orderuserid_max<>-1

     begin

         set @strsql = @strsql + ' and orderuserid_max = @tmporderuserid_max'

     end

if @orderid<>-1

     begin

         set @strsql = @strsql + ' and orderid = @tmporderid'

     end

if @orderid_min<>-1

     begin

         set @strsql = @strsql + ' and orderid_min = @tmporderid_min'

     end

if @orderid_max<>-1

     begin

         set @strsql = @strsql + ' and orderid_max = @tmporderid_max'

     end

if @productid<>-1

     begin

         set @strsql = @strsql + ' and productid = @tmpproductid'

     end

if @productid_min<>-1

     begin

         set @strsql = @strsql + ' and productid_min = @tmpproductid_min'

     end

if @productid_max<>-1

     begin

         set @strsql = @strsql + ' and productid_max = @tmpproductid_max'

     end

if @customizeid<>-1

     begin

         set @strsql = @strsql + ' and customizeid = @tmpcustomizeid'

     end

if @customizeid_min<>-1

     begin

         set @strsql = @strsql + ' and customizeid_min = @tmpcustomizeid_min'

     end

if @customizeid_max<>-1

     begin

         set @strsql = @strsql + ' and customizeid_max = @tmpcustomizeid_max'

     end

         begin tran

         execute sp_executesql @strsql, n'

                       @tmporderstateid int,

                       @tmporderuserid int,

                       @tmporderid int,

                       @tmpproductid int,

                       @tmpcustomizeid int',

                       @[email protected],

                       @[email protected],

                       @[email protected],

                       @[email protected],

                       @[email protected]

         set @outid = @orderid

         if @@error!=0

              begin

                   rollback

              end

         else

              begin

                   commit

              end

return

三、 search_order_info

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

** name      :    search_order_info

** creater        :    ppcoder designed by ppcode studio(pptech.net)

** create date    :    2004-9-6 8:30:17

** modifer        :   rexsp

** modify date    :    2004-9-6 8:30:17

** description : search information for order_info

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

alter procedure dbo.search_order_info

(

     @orderstateid int = -1,

     @orderstateid_min int = -1,

     @orderstateid_max int = -1,

     @orderuserid int = -1,

     @orderuserid_min int = -1,

     @orderuserid_max int = -1,

     @orderid int = -1,

     @orderid_min int = -1,

     @orderid_max int = -1,

     @productid int = -1,

     @productid_min int = -1,

     @productid_max int = -1,

     @customizeid int = -1,

     @customizeid_min int = -1,

     @customizeid_max int = -1,

     @returncount int=-1,

     @outid int = 0 output

)

as

declare @strsql nvarchar(1000)

if @returncount<>-1

     begin

     set @strsql = 'select top '[email protected]+' * from [order_info] where @tmporderid = @tmporderid '

     end

else

     begin

     set @strsql = 'select * from [order_info] where @tmporderid = @tmporderid '

     end

if @orderstateid<>-1

     begin

         set @strsql = @strsql + ' and orderstateid = @tmporderstateid'

     end

if @orderstateid_min<>-1

     begin

         set @strsql = @strsql + ' and orderstateid_min = @tmporderstateid_min'

     end

if @orderstateid_max<>-1

     begin

         set @strsql = @strsql + ' and orderstateid_max = @tmporderstateid_max'

     end

if @orderuserid<>-1

     begin

         set @strsql = @strsql + ' and orderuserid = @tmporderuserid'

     end

if @orderuserid_min<>-1

     begin

         set @strsql = @strsql + ' and orderuserid_min = @tmporderuserid_min'

     end

if @orderuserid_max<>-1

     begin

         set @strsql = @strsql + ' and orderuserid_max = @tmporderuserid_max'

     end

if @orderid<>-1

     begin

         set @strsql = @strsql + ' and orderid = @tmporderid'

     end

if @orderid_min<>-1

     begin

         set @strsql = @strsql + ' and orderid_min = @tmporderid_min'

     end

if @orderid_max<>-1

     begin

         set @strsql = @strsql + ' and orderid_max = @tmporderid_max'

     end

if @productid<>-1

     begin

         set @strsql = @strsql + ' and productid = @tmpproductid'

     end

if @productid_min<>-1

     begin

         set @strsql = @strsql + ' and productid_min = @tmpproductid_min'

     end

if @productid_max<>-1

     begin

         set @strsql = @strsql + ' and productid_max = @tmpproductid_max'

     end

if @customizeid<>-1

     begin

         set @strsql = @strsql + ' and customizeid = @tmpcustomizeid'

     end

if @customizeid_min<>-1

     begin

         set @strsql = @strsql + ' and customizeid_min = @tmpcustomizeid_min'

     end

if @customizeid_max<>-1

     begin

         set @strsql = @strsql + ' and customizeid_max = @tmpcustomizeid_max'

     end

         begin tran

         execute sp_executesql @strsql, n'

                       @tmporderstateid int,

                       @tmporderuserid int,

                       @tmporderid int,

                       @tmpproductid int,

                       @tmpcustomizeid int',

                       @[email protected],

                       @[email protected],

                       @[email protected],

                       @[email protected],

                       @[email protected]

         set @outid = @orderid

         if @@error!=0

              begin

                   rollback

              end

         else

              begin

                   commit

              end

分析：

１、              三個存儲過程的入?yún)⒒旧舷嗤挥衧earch_order_info多了一個＠returncount用來控制搜索信息的條數(shù)的。入?yún)⒑苡刑攸c(diǎn)：與數(shù)據(jù)表字段的擴(kuò)展對應(yīng)。擴(kuò)展方式有三種：數(shù)字型和日期型擴(kuò)展出“極小”和“極大”兩個屬性，例如數(shù)字型的orderstateid對應(yīng)的參數(shù)有三個@orderstateid、@orderstateid_min 、@orderstateid_max ，時間型的addtime對應(yīng)@addtime、@addtime_rof、@addtime_eof ；如果是字符型的，則會擴(kuò)展出一個用來進(jìn)行模糊搜索的屬性，例如title對應(yīng)@title、@title_like。之所以這樣設(shè)計(jì)，是為了組合出更具適應(yīng)性的條件語句。三個存儲過程都有一個出參，就是表的唯一標(biāo)識id。這個主要在“添加和更新”操作中使用。當(dāng)然搜索的時候也可以當(dāng)唯一鍵返回。這個唯一標(biāo)識id也是來判斷是insert或update的標(biāo)識。

２、              入?yún)⒍加匈x初值，然后動態(tài)構(gòu)建sql語句的時候，會判斷各入?yún)⑹欠竦扔诔踔担绻坏扔诒硎臼峭饷鎮(zhèn)鬟M(jìn)來的傳，便參與sql語句的構(gòu)建。這種靈活性是程序適應(yīng)性的保證。這樣，我們就可以在程序員通過控制是否給入?yún)髦祦砼袛嗍欠褚M(jìn)行某一欄位進(jìn)行更新或是否要把某一欄位的信息參與條件語句的構(gòu)成。

３、              用系統(tǒng)存儲過程sp_executesql來執(zhí)行sql語句，完全數(shù)據(jù)庫操作。用系統(tǒng)存儲過程來執(zhí)行sql語句有一個好處，就是可以實(shí)現(xiàn)特殊字符的自動轉(zhuǎn)義。

４、              三個存儲過程都有統(tǒng)一的構(gòu)建規(guī)律，所以可以使用自動化工具依據(jù)表結(jié)構(gòu)直接生成。

收集最實(shí)用的網(wǎng)頁特效代碼！

上一篇：來自AskTom的關(guān)于Soft Parse的解釋

下一篇：本blog不再更新，新的blog如下