存儲(chǔ)過(guò)程、游標(biāo)及其它

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

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

[WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASsql_statement[...n]

明

使用ENCRYPTION選項(xiàng)創(chuàng)建的存儲(chǔ)過(guò)程不能使用sp_helptext查看。若要顯示有關(guān)過(guò)程引用的對(duì)象的報(bào)表，請(qǐng)使用sp_depends。若要為過(guò)程重命名，請(qǐng)使用sp_rename。準(zhǔn)備工作：1）為了每一次都可以執(zhí)行，在創(chuàng)建之前先寫上下面的一段SQL語(yǔ)句，檢測(cè)是不是已經(jīng)有了相同名稱的存儲(chǔ)過(guò)程，如果有就刪除。Proc_name是要建立的存儲(chǔ)過(guò)程名，sysobjects是一個(gè)系統(tǒng)表，存了所有的系統(tǒng)對(duì)象的信息。ifexists(selectnamefromsysobjectswherename='proc_name'andtype='P')dropprocedureproc_namego2）ifelse語(yǔ)法IFBoolean_expression {sql_statement|statement_block}[ELSE {sql_statement|statement_block}]在每一個(gè)段中都要用一對(duì)beginend進(jìn)行語(yǔ)句塊的標(biāo)定。不使用參數(shù)的存儲(chǔ)過(guò)程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加上所有者避免潛在權(quán)限問(wèn)題加上up_更清楚使用參數(shù)的存儲(chǔ)過(guò)程(注意缺省值)/*這里的參數(shù)是一個(gè)輸入存儲(chǔ)過(guò)程的參數(shù)，我們?yōu)樗O(shè)定了一個(gè)缺省值，以便在用戶忘記了輸入時(shí)也可以給出一個(gè)答案，不返回出錯(cuò)的信息*/usenorthwindgoifexists(selectnamefromsysobjectswherename='dbo.up_shipper'andtype='p')dropproceduredbo.up_shipperGocreateproceduredbo.up_shipper@ShipperNamechar(40)='UnitedPackage‘WithENCRYPTIONasselectrequireddate,shippeddate,panynamefromorders,shipperswhereshippeddate>requireddateandorders.shipvia=shippers.shipperidandpanyname=@shippername使用加密后，不能再看原代碼使用編輯菜單事件探查器也看不到代碼對(duì)存儲(chǔ)過(guò)程只能顯示名稱，加不加密一樣怎樣使用參數(shù)來(lái)調(diào)用一個(gè)存儲(chǔ)過(guò)程CREATEPROCEDURECustOrdersOrders@CustomerIDnchar(5)ASSELECTOrderID,OrderDate,RequiredDate,ShippedDateFROMOrdersWHERECustomerID=@CustomerIDORDERBYOrderIDexecCustOrdersOrders"VINET"怎樣使用參數(shù)來(lái)調(diào)用一個(gè)存儲(chǔ)過(guò)程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可以終止一個(gè)存儲(chǔ)過(guò)程且返回執(zhí)行的狀態(tài)。不加任何參數(shù)就是停止過(guò)程的運(yùn)行，下面的過(guò)程中如果發(fā)現(xiàn)參數(shù)沒(méi)有輸入就自動(dòng)終止。--dropprocShipperLate--gocreateprocedureShipperLate@ShipperNamechar(40)=nullasif@ShipperNameisnullBeginprint'請(qǐng)輸入要查詢的公司名'returnEndselectrequireddate,shippeddate,panynamefromorders,shipperswhereshippeddate>requireddateandorders.shipvia=shippers.shipperidandpanyname=@shippernamegoexecShipperLateReturn返回值時(shí)只能返回整數(shù)，下面建立一個(gè)存儲(chǔ)過(guò)程，查詢XX合同號(hào)，如果查到了就返回1，否則返回0。查詢XX合同號(hào)，查到返回1，否則返回0createproctry_return@idchar(6)asdeclare@valintifexists(select*fromorderswhereorderid=rtrim(@id))beginset@val=1return@valendelsebeginset@val=0return@valend調(diào)用的時(shí)候像這樣就可以了declare@kkintexec@kk=try_return'1123'print@kk用存儲(chǔ)過(guò)程組織表，用于打印或別的用途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實(shí)例：教育部項(xiàng)目中１）向Web_log中插入數(shù)據(jù)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中調(diào)用存儲(chǔ)過(guò)程PublicConnAsADODB.Connection‘建立一個(gè)連接DimSQLasstringSetConn=NewConnectionSetRS=NewRecordsetREM打開(kāi)數(shù)據(jù)源DimPassAsStringPass=“password”‘輸入數(shù)據(jù)源的密碼Conn.CursorLocation=adUseClientConn.Open"PROVIDER=MSDASQL;dsn=ld2000;uid=sa;pwd="&PassREM調(diào)用格式：Sql=”proc_nameparameters”‘用變量表取代parameters，字符型的變量用單引號(hào)定界就可以了，（如果SQL語(yǔ)句不是只有一行，就在存儲(chǔ)過(guò)程的名子前加上EXEC），如：SQL＝”ShipperLate‘UniteState’”Conn.executeSQL游標(biāo)游標(biāo)的使用關(guān)系數(shù)據(jù)庫(kù)中的操作會(huì)對(duì)整個(gè)行集產(chǎn)生影響。由SELECT語(yǔ)句返回的行集包括所有滿足該語(yǔ)句WHERE子句中條件的行。由語(yǔ)句所返回的這一完整的行集被稱為結(jié)果集。應(yīng)用程序，特別是交互式聯(lián)機(jī)應(yīng)用程序，并不總能將整個(gè)結(jié)果集作為一個(gè)單元來(lái)有效地處理。這些應(yīng)用程序需要一種機(jī)制以便每次處理一行或一部分行。游標(biāo)就是提供這種機(jī)制的結(jié)果集擴(kuò)展。游標(biāo)通過(guò)以下方式擴(kuò)展結(jié)果處理：

允許定位在結(jié)果集的特定行。

從結(jié)果集的當(dāng)前位置檢索一行或多行。

支持對(duì)結(jié)果集中當(dāng)前位置的行進(jìn)行數(shù)據(jù)修改。為由其他用戶對(duì)顯示在結(jié)果集中的數(shù)據(jù)庫(kù)數(shù)據(jù)所做的更改提供不同級(jí)別的可見(jiàn)性支持。

提供腳本、存儲(chǔ)過(guò)程和觸發(fā)器中使用的訪問(wèn)結(jié)果集中的數(shù)據(jù)的Transact-SQL語(yǔ)句。SQL-92語(yǔ)法DECLAREcursor_name[INSENSITIVE][SCROLL]CURSOR

FORselect_statement

[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]INSENSITIVE定義一個(gè)游標(biāo)，以創(chuàng)建將由該游標(biāo)使用的數(shù)據(jù)的臨時(shí)復(fù)本。對(duì)游標(biāo)的所有請(qǐng)求都從tempdb中的該臨時(shí)表中得到應(yīng)答；因此，在對(duì)該游標(biāo)進(jìn)行提取操作時(shí)返回的數(shù)據(jù)中不反映對(duì)基表所做的修改，并且該游標(biāo)不允許修改。使用SQL-92語(yǔ)法時(shí)，如果省略INSENSITIVE，（任何用戶）對(duì)基表提交的刪除和更新都反映在后面的提取中。SCROLL指定所有的提取選項(xiàng)（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果在SQL-92DECLARECURSOR中未指定SCROLL，則NEXT是唯一支持的提取選項(xiàng)。如果指定SCROLL，則不能也指定FAST_FORWARD。Transact-SQL擴(kuò)展語(yǔ)法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]]]參數(shù)意義LOCAL指定該游標(biāo)的作用域?qū)υ谄渲袆?chuàng)建它的批處理、存儲(chǔ)過(guò)程或觸發(fā)器是局部的。該游標(biāo)名稱僅在這個(gè)作用域內(nèi)有效。GLOBAL指定該游標(biāo)的作用域?qū)B接是全局的。在由連接執(zhí)行的任何存儲(chǔ)過(guò)程或批處理中，都可以引用該游標(biāo)名稱。STATIC對(duì)游標(biāo)的所有請(qǐng)求都從tempdb中的該臨時(shí)表中得到應(yīng)答；因此，在對(duì)該游標(biāo)進(jìn)行提取操作時(shí)返回的數(shù)據(jù)中不反映對(duì)基表所做的修改，并且該游標(biāo)不允許修改。使用簡(jiǎn)單游標(biāo)和語(yǔ)法打開(kāi)該游標(biāo)時(shí)所生成的結(jié)果集包括pubs數(shù)據(jù)庫(kù)的authors表中的所有行和列。可以更新該游標(biāo)，對(duì)該游標(biāo)所做的所有更新和刪除均在提取中表現(xiàn)出來(lái)。因?yàn)闆](méi)指定SCROLL選項(xiàng)，F(xiàn)ETCHNEXT是唯一可用的提取選項(xiàng)。DECLAREauthors_cursorCURSOR