存储过程课件

存储过程

9.1存储过程的基本概念

在使用Transact-SQL语言编程的过程中，可以将某些多次调用以实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQLServer服务器通过过程名调用它们，这些过程就叫做存储过程。存储过程分为系统存储过程和用户自定义的存储过程。系统存储过程由

SQLServer2000提供，用户可以直接使用。用户自定义存储过程需要用户自己创建和维护。系统存储过程使用“sp_”作为前缀。

存储过程的优点：可以在单个存储过程中执行一系列

SQL语句。

可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

存储过程在创建时即在服务器上进行编译，所以执行起来比单个

SQL语句快。

9.2创建存储过程

在SQL-Server中，可以使用三种方法创建存储过程：使用向导创建存储过程。

使用SQL-Server企业管理器创建存储过程。

使用Transact-SQL语句中的CreateProcedure命令创建存储过程。

默认情况下，创建存储过程的许可权归属数据库的所有者，数据库的所有者可以授权给其他用户。9.2.1使用向导创建存储过程

（1）在企业管理器中选中某个SQL-Server服务器中的数据库，这里选中sales数据库。选择工具菜单中的“向导”菜单项，系统会弹出“选择向导”对话框。选中“创建存储过程向导”选项。（2）单击“确定”按钮，出现“欢迎使用创建存储过程向导”对话框。（3）单击“下一步”按钮，出现“选择数据库”对话框，如图9-3所示。该对话框用于选择创建存储过程中使用的数据库。

图9-3“选择数据库”对话框

（4）我们选择sales数据库，单击“下一步”按钮，出现“选择操作对象”对话框，如图9-4所示，在该对话框中，列出了所有可选择的表，以及可以对表进行的数据库操作，即插入删除和更新。这里我们对表Salers进行操作，因此选中表Salers对应的插入列、删除列和更新列下面的复选框。

图9-4“选择操作对象”对话框

（5）单击“下一步”按钮，出现确认存储过程信息对话框，如图9-5所示。图9-5“完成创建存储过程向导”对话框

9.2.2使用企业管理器创建存储过程

（1）在企业管理器中选中某个SQL-Server服务器中的数据库，这里选中sales数据库。右键单击数据库下的“存储过程”选项，弹出快捷菜单，如图9-8所示。

图9-8快捷菜单内容

（2）在快捷菜单中选择“新建存储过程”命令出现“新建存储过程”对话框，如图9-9所示。图9-9“新建存储过程”对话框

（3）在“新建存储过程”对话框的“文本”列表框中输入存储过程名称和程序语句，如图9-9所示。

CREATEPROCEDUREUP_SalersASSelect*fromSalers存储过程UP_Salers的完成的功能是从Salers表中查询全部数据。（4）单击“检查语法”按钮，执行语法正确性检验。（5）单击“确定”按钮，返回企业管理器窗口，可以看到所创建的存储过程。9.2.3使用Transact-SQL语句创建存储过程

CREATEPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement

procedure_name：新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。

@parameter：过程中的参数。

data_type：参数的数据类型。

Default：参数的默认值。

OUTPUT：表明参数是返回参数。

AS：指定过程要执行的操作。

sql_statement：过程中要包含的任意数目和类型的

Transact-SQL语句。

例9-1不带有参数的存储过程下面的存储过程从sales数据库的三个表的联接中返回订单的编号、客户名称、销售员和订单日期。该存储过程不使用任何参数。USEsalesGOCREATEPROCEDUREUP_OrderInfoASSELECTOrders.OrderID,Customers.CompanyName,Salers.Salename,Orders.OrderDateFROMOrdersINNERJOINSalersONOrders.SaleID=Salers.SaleIDINNERJOINCustomersONOrders.CustomerID=Customers.CustomerIDGO例9-2带有参数的存储过程下面的存储过程从sales数据库的三个表的联接中返回订单的编号、客户名称、销售员和订单日期。该存储过程接受日期区间参数：开始日期和结束日期。

USEsalesGOCREATEPROCEDUREUP_OrderInfoWithParam@StartDatedatetime,@EndDatedatetimeASSELECTOrders.OrderID,Customers.CompanyName,Salers.Salename,Orders.OrderDateFROMOrdersINNERJOINSalersONOrders.SaleID=Salers.SaleIDINNERJOINCustomersONOrders.CustomerID=Customers.CustomerIDWHERE(Orders.OrderDateBETWEEN@StartDateAND@EndDate)GO9.3执行存储过程

[[EXEC[UTE]]{[@return_status=]{procedure_name]}[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]][,...n][WITHRECOMPILE]EXEC[UTE]：为执行存储过程的关键字。如果所执行存储过程语句为批中的第一个语句时，则可以省略EXECUTE关键字。

@return_status：是一个可选的整型变量，保存存储过程的返回状态。

@parameter：是过程参数，在

CREATEPROCEDURE语句中定义。

Value：是过程中参数的值。

@variable：是用来保存参数或者返回参数的变量。OUTPUT：指定存储过程必须返回一个参数。

DEFAULT：根据过程的定义，提供参数的默认值。

1．不带参数的存储过程的调用：USEsalesEXECUTEUP_OrderInfo2．带参数的存储过程的调用：

USEsalesEXECUTEUP_OrderInfoWithParam'2005-1-1','2005-5-1'--OrEXECUTEUP_OrderInfoWithParam@StartDate='2005-1-1',@EndDate='2005-5-1'--OrEXECUTEUP_OrderInfoWithParam@EndDate='2005-5-1',@StartDate='2005-1-1'3．自动执行的存储过程可以通过调用一个系统存储过程来设置一个存储过程为自动执行的存储过程。这样的存储过程可以在SQLserver启动时自动执行。这个系统存储过程是sp_procoption,其调用语法格式为：sp_procoption[@ProcName=]'procedure',[@OptionName=]'option',[@OptionValue=]'value'

[@ProcName=]'procedure'：是要为其设置或查看选项的过程名。

[@OptionName=]'option'：要设置的选项的名称。option的唯一值是startup，该值设置存储过程的自动执行状态。设置为自动执行的存储过程会在每次MicrosoftSQLServer启动时运行。

[[@OptionValue=]'value']：表示选项是设置为开（true或on）还是关（false或off）。9.4管理存储过程

9.4.1查看存储过程

创建存储过程之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用企业管理器或系统存储过程查看用户创建的存储过程。

1．使用企业管理器查看用户创建的存储过程

在企业管理器中，打开指定的服务器和数据库项，这里打开HBSI服务器下的sales数据库，并单击sales中的“存储过程”项，此时在右边的窗口中显示出sales数据库中的所有存储过程。如图9-12所示。

图9-12存储过程显示窗口

右键单击要查看的存储过程，从弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性”选项。或者左键双击要查看的存储过程，也可以弹出“存储过程属性”选项。在此对话框中能够看到存储过程的源代码。

2．使用系统存储过程查看用户创建的存储过程

sp_help

sp_helptext

sp_depends

sp_stored_procedures9.4.2修改存储过程

1．使用企业管理器修改存储过程

2．使用Transact-SQL语句修改存储过程

ALTERPROC[EDURE]procedure_name[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement9.4.3重命名存储过程

1．使用企业管理器重命名存储过程名称在企业管理器中，右键单击要更名的存储过程，从弹出的快捷菜单中选择“重命名”选项，当存储过程名称变成可输入状态时，就可以直接修改该存储过程的名称了。

2．使用系统存储过程重命名存储过程名称

sp_renameold_name，new_name例如：USEsalesGOsp_renameP_OrderInfoWithParam,UP_OrderInfoDateGO9.4.4删除存储过程

1．使用企业管理器删除存储过程在企业管理器中，右键单击要更名的存储过程，从弹出的快捷菜单中选择“删除”选项，弹出“除去对象”对话框，如图9-14所示。在该对话框中，单击“全部