存儲過程與游標什么是存儲過程？存儲過程是SQLServer編譯到單一執行計劃的T－SQL的集合。第一次執行存儲過程時，該過程被存入內存中的高速過程緩沖區，下一次再調用時就不必重新編譯，一個存儲過程可以向多個進程提供服務，對每一個服務提供了一個單一的訪問點，可以更好地保證規則的與數據完整性。和其它編程語言中的過程一樣，它也可以接受參數，可以返回值以表示執行的成功與失敗。使用存儲過程的好處系統存儲過程的屬性—權限管理查看系統存儲過程原代碼系統存儲過程存在系統表Master--syscomments系統存儲過程存在這個系統表的TEXT列。如果不知道怎么辦呢？可以用下面的方法來實現：在查詢分析器中可以看到Execsp_helptextXXXXX什么是擴展存儲過程看不到源碼了--列出服務器上固定驅動器,以及每個驅動器的可用空間executemaster..xp_fixeddrives--列出當前錯誤日志的具體內容EXEC[master].[dbo].[xp_readerrorlog]存儲過程的創建語法CREATEPROC[EDURE]procedure_name[;number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

[WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASsql_statement[...n]

明

使用ENCRYPTION選項創建的存儲過程不能使用sp_helptext查看。若要顯示有關過程引用的對象的報表，請使用sp_depends。若要為過程重命名，請使用sp_rename。準備工作：1）為了每一次都可以執行，在創建之前先寫上下面的一段SQL語句，檢測是不是已經有了相同名稱的存儲過程，如果有就刪除。Proc_name是要建立的存儲過程名，sysobjects是一個系統表，存了所有的系統對象的信息。ifexists(selectnamefromsysobjectswherename='proc_name'andtype='P')dropprocedureproc_namego2）ifelse語法IFBoolean_expression {sql_statement|statement_block}[ELSE {sql_statement|statement_block}]在每一個段中都要用一對beginend進行語句塊的標定。不使用參數的存儲過程goifexists(selectnamefromsysobjectswherename='dbo.up_bian'andtype='p')dropproceduredbo.up_bianGocreateproceduredbo.up_bianasdeclare@idintset@id=1while(@id<6)beginselect*fromEmployeeswhereEmployeeID=str(@id)set@id=@id+1EndGodbo.up_bian加上所有者避免潛在權限問題加上up_更清楚使用參數的存儲過程(注意缺省值)/*這里的參數是一個輸入存儲過程的參數，我們為它設定了一個缺省值，以便在用戶忘記了輸入時也可以給出一個答案，不返回出錯的信息*/usenorthwindgoifexists(selectnamefromsysobjectswherename='dbo.up_shipper'andtype='p')dropproceduredbo.up_shipperGocreateproceduredbo.up_shipper@ShipperNamechar(40)='UnitedPackage‘WithENCRYPTIONasselectrequireddate,shippeddate,panynamefromorders,shipperswhereshippeddate>requireddateandorders.shipvia=shippers.shipperidandpanyname=@shippername使用加密后，不能再看原代碼使用編輯菜單事件探查器也看不到代碼對存儲過程只能顯示名稱，加不加密一樣怎樣使用參數來調用一個存儲過程CREATEPROCEDURECustOrdersOrders@CustomerIDnchar(5)ASSELECTOrderID,OrderDate,RequiredDate,ShippedDateFROMOrdersWHERECustomerID=@CustomerIDORDERBYOrderIDexecCustOrdersOrders"VINET"怎樣使用參數來調用一個存儲過程CREATEPROCEDUREdbo.up_CustOrdersDetail@OrderIDintASSELECTProductName,UnitPrice=ROUND(Od.UnitPrice,2),Quantity,Discount=CONVERT(int,Discount*100),ExtendedPrice=ROUND(CONVERT(money,Quantity*(1-Discount)*Od.UnitPrice),2)FROMProductsP,[OrderDetails]OdWHEREOd.ProductID=P.ProductIDandOd.OrderID=@OrderID返回值ReturnReturn可以終止一個存儲過程且返回執行的狀態。不加任何參數就是停止過程的運行，下面的過程中如果發現參數沒有輸入就自動終止。--dropprocShipperLate--gocreateprocedureShipperLate@ShipperNamechar(40)=nullasif@ShipperNameisnullBeginprint'請輸入要查詢的公司名'returnEndselectrequireddate,shippeddate,panynamefromorders,shipperswhereshippeddate>requireddateandorders.shipvia=shippers.shipperidandpanyname=@shippernamegoexecShipperLateReturn返回值時只能返回整數，下面建立一個存儲過程，查詢XX合同號，如果查到了就返回1，否則返回0。查詢XX合同號，查到返回1，否則返回0createproctry_return@idchar(6)asdeclare@valintifexists(select*fromorderswhereorderid=rtrim(@id))beginset@val=1return@valendelsebeginset@val=0return@valend調用的時候像這樣就可以了declare@kkintexec@kk=try_return'1123'print@kk用存儲過程組織表，用于打印或別的用途createprocedure"EmployeeSalesbyCountry"@Beginning_DateDateTime,@Ending_DateDateTimeASSELECTEmployees.Country,Employees.LastName,Employees.FirstName,Orders.ShippedDate,Orders.OrderID,"OrderSubtotals".SubtotalASSaleAmountinto#tempTableFROMEmployeesINNERJOIN (OrdersINNERJOIN"OrderSubtotals"ONOrders.OrderID="OrderSubtotals".OrderID) ONEmployees.EmployeeID=Orders.EmployeeIDWHEREOrders.ShippedDateBetween@Beginning_DateAnd@Ending_Date實例：教育部項目中１）向Web_log中插入數據IFEXISTS(SELECTnameFROMsysobjectsWHEREname='Insert_Weblog'ANDtype='P')DROPPROCEDUREInsert_WeblogGOCREATEPROCEDUREInsert_WeblogASinsertweb_log(id,ip,log_date,log_time,method,url,proxy)selectid,c_ip,log_date,log_time,cs_method,cs_uri_stem,user_agentfromalogfileGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname='data_clean'ANDtype='P')DROPPROCEDUREdata_cleanGOCREATEPROCEDUREdata_cleanASUPDATE[web_log]SET[usepage]=(selecttop1urlfromweb_logbwhereb.ip=[web_log].ipandb.id<[web_log].idorderbyb.iddesc)這樣完成后做一次只用３５秒，以前１５分鐘也不一定行。在VB中調用存儲過程PublicConnAsADODB.Connection‘建立一個連接DimSQLasstringSetConn=NewConnectionSetRS=NewRecordsetREM打開數據源DimPassAsStringPass=“password”‘輸入數據源的密碼Conn.CursorLocation=adUseClientConn.Open"PROVIDER=MSDASQL;dsn=ld2000;uid=sa;pwd="&PassREM調用格式：Sql=”proc_nameparameters”‘用變量表取代parameters，字符型的變量用單引號定界就可以了，（如果SQL語句不是只有一行，就在存儲過程的名子前加上EXEC），如：SQL＝”ShipperLate‘UniteState’”Conn.executeSQL游標游標的使用關系數據庫中的操作會對整個行集產生影響。由SELECT語句返回的行集包括所有滿足該語句WHERE子句中條件的行。由語句所返回的這一完整的行集被稱為結果集。應用程序，特別是交互式聯機應用程序，并不總能將整個結果集作為一個單元來有效地處理。這些應用程序需要一種機制以便每次處理一行或一部分行。游標就是提供這種機制的結果集擴展。游標通過以下方式擴展結果處理：

允許定位在結果集的特定行。

從結果集的當前位置檢索一行或多行。

支持對結果集中當前位置的行進行數據修改。為由其他用戶對顯示在結果集中的數據庫數據所做的更改提供不同級別的可見性支持。

提供腳本、存儲過程和觸發器中使用的訪問結果集中的數據的Transact-SQL語句。SQL-92語法DECLAREcursor_name[INSENSITIVE][SCROLL]CURSOR

FORselect_statement

[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]INSENSITIVE定義一個游標，以創建將由該游標使用的數據的臨時復本。對游標的所有請求都從tempdb中的該臨時表中得到應答；因此，在對該游標進行提取操作時返回的數據中不反映對基表所做的修改，并且該游標不允許修改。使用SQL-92語法時，如果省略INSENSITIVE，（任何用戶）對基表提交的刪除和更新都反映在后面的提取中。SCROLL指定所有的提取選項（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果在SQL-92DECLARECURSOR中未指定SCROLL，則NEXT是唯一支持的提取選項。如果指定SCROLL，則不能也指定FAST_FORWARD。Transact-SQL擴展語法DECLAREcursor_nameCURSOR

[LOCAL|GLOBAL]

[FORWARD_ONLY|SCROLL]

[STATIC|KEYSET|DYNAMIC|FAST_FORWARD]

[READ_ONLY|SCROLL_LOCKS|OPTIMISTIC]

[TYPE_WARNING]

FORselect_statement

[FORUPDATE[OFcolumn_name[,...n]]]參數意義LOCAL指定該游標的作用域對在其中創建它的批處理、存儲過程或觸發器是局部的。該游標名稱僅在這個作用域內有效。GLOBAL指定該游標的作用域對連接是全局的。在由連接執行的任何存儲過程或批處理中，都可以引用該游標名稱。STATIC對游標的所有請求都從tempdb中的該臨時表中得到應答；因此，在對該游標進行提取操作時返回的數據中不反映對基表所做的修改，并且該游標不允許修改。使用簡單游標和語法打開該游標時所生成的結果集包括pubs數據庫的authors表中的所有行和列。可以更新該游標，對該游標所做的所有更新和刪除均在提取中表現出來。因為沒指定SCROLL選項，FETCHNEXT是唯一可用的提取選項。DECLAREauthors_cursorCURSOR