存储过程、游标及其它

存储过程与游标什么是存储过程？存储过程是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