数据库存储过程总结

最新推荐文章于 2024-06-17 15:00:11 发布

fsclc

最新推荐文章于 2024-06-17 15:00:11 发布

阅读量1.7w

点赞数 3

分类专栏：数据库文章标签：存储数据库 sql server encryption output microsoft

数据库专栏收录该内容

3 篇文章 0 订阅

订阅专栏

在大型数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL 语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程。存储过程在运算时生成执行方式，所以，以后对其再运行时其执行速度很快。SQL Server 2000 不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。

存储过程的概念
存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库。中用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。

    在SQL Server 的系列版本中存储过程分为两类：系统提供的存储过程和用户自定义存储过程。系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。通过系统存储过程，MS SQL Server 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。尽管这些系统存储过程被放在master 数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在本章中所涉及到的存储过程主要是指用户自定义存储过程。

存储过程的优点
    当利用MS SQL Server 创建一个应用程序时，Transaction-SQL 是一种主要的编程语言。若运用Transaction-SQL 来进行编程，有两种方法。其一是，在本地存储Transaction-SQL 程序，并创建应用程序向SQL Server 发送命令来对结果进行处理。其二是，可以把部分用Transaction-SQL 编写的程序作为存储过程存储在SQL Server 中，并创建应用程序来调用存储过程，对数据结果进行处理存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定；返回状态值给调用者，指明调用是成功或是失败；包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。

    我们通常更偏爱于使用第二种方法，即在SQL Server 中使用存储过程而不是在客户计算机上调用Transaction-SQL 编写的一段程序，原因在于存储过程具有以下优点：

（1）存储过程允许标准组件式编程
存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。

（2）存储过程能够实现较快的执行速度
如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的Transaction-SQL 语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。

（3）存储过程能够减少网络流量
对于同一个针对数据数据库对象的操作（如查询、修改），如果这一操作所涉及到的Transaction-SQL 语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，否则将是多条SQL 语句，从而大大增加了网络流量，降低网络负载。

（4）存储过程可被作为一种安全机制来充分利用
系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全

注意：存储过程虽然既有参数又有返回值，但是它与函数不同。存储过程的返回值只是指明执行是否成功，并且它不能像函数那样被直接调用，也就是在调用存储过程时，在存储过程名字前一定要有EXEC保留字

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

一、先介绍一下什么是存储过程
存储过程是利用SQL Server所提供的Tranact-SQL语言所编写的程序。Tranact-SQL语言是SQL Server提供专为设计数据库应用程序的语言，它是应用程序和SQL Server数据库间的主要程序式设计界面。它好比Oracle数据库系统中的Pro-SQL和Informix的数据库系统能够中的Informix-4GL语言一样。这类语言主要提供以下功能，让用户可以设计出符合引用需求的程序：
1)、变量说明
2)、ANSI兼容的SQL命令(如Select,Update….)
3)、一般流程控制命令(if…else…、while….)
4)、内部函数

二、存储过程的书写格

CREATE PROCEDURE [拥有者.]存储过程名[;程序编号]
[(参数#1,…参数#1024)]
[WITH
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
]
[FOR REPLICATION]
AS 程序行

其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数
(SQL Server 7.0以上版本),参数的使用方法如下:

@参数名数据类型 [VARYING] [=内定值] [OUTPUT]

每个参数名前要有一个“@”符号,每一个存储过程的参数仅为该程序内部使用,参数的类型除了IMAGE外，其他SQL Server所支持的数据类型都可使用。
[=内定值]相当于我们在建立数据库时设定一个字段的默认值，这里是为这个参数设定默认值。[OUTPUT]是用来指定该参数是既有输入又有输出值的，也就是在调用了这个存储过程时，如果所指定的参数值是我们需要输入的参数，同时也需要在结果中输出的，则该项必须为OUTPUT，而如果只是做输出参数用，可以用CURSOR，同时在使用该参数时，必须指定VARYING和OUTPUT这两个语句。

例子:
CREATE PROCEDURE order_tot_amt @o_id int,@p_tot int output AS
SELECT @p_tot = sum(Unitprice*Quantity)
FROM orderdetails
WHERE ordered=@o_id

例子说明:
该例子是建立一个简单的存储过程order_tot_amt,这个存储过程根据用户输入的定单ID号码(@o_id),由定单明细表(orderdetails)中计算该定单销售总额[单价(Unitprice)*数量(Quantity)],这一金额通过@p_tot这一参数输出给调用这一存储过程的程序

三、在SQL Server中执行存储过程

在SQL Server的查询分析器中，输入以下代码:
declare @tot_amt int
execute order_tot_amt 1,@tot_amt output
select @tot_amt

以上代码是执行order_tot_amt这一存储过程，以计算出定单编号为1的定单销售金额，我们定义@tot_amt为输出参数，用来承接我们所要的结果

四、在ASP中调用存储过程



<%
dim objCnn
dim objCmd
dim Rs
const o_id=112

’-----建立Connection对象----------
set objCnn=Server.CreateObject("Adodb.connection")
objCnn.Open "driver={sql server};server=localhost;uid=sa;pwd=cncanet;database=check;"
’-----建立Command对象-----------
set objCmd=Server.CreateObject("Adodb.Command")
objCmd.ActiveConnection=objCnn
objCmd.CommandText="order_tot_amt" ’指定存储过程名称
objCmd.CommandType=adCmdStoredProc ’其为Stored Procedure
’-----准备stored procedure 的参数-------
objCmd.Parameters.Append _
objCmd.CreateParameter("o_id",adInteger,adParamInput,,o_id)
objCmd.Parameters.Append _
objCmd.CreateParameter("p_tot",adBigInt,adParamOutput,,0)
’-----执行存储过程----------------------
objCmd.Execute

’-----输出参数以及处理结果--------------
for each parm in objCmd.Parameters
Response.Write parm.name &"="& trim(parm) &"<br>"
next
%>

ASP调用存储过程的方法：

---- 　　调用存储过程的方法需要使用ADO的Command对象。Command对象是对一个数据源运行特定命令的定义（例如，一个SQL查询或一个SQL存储过程）。下面以MS SQL SERVER6.5为后台数据库，介绍ASP调用存储过程的方法。

---- 　　例如，在表User中保存了用户名和密码。现在建立一个检查密码的存储过程sp_CheckPass来检查用户是否输入了合法的密码。建立存储过程，可以从Microsoft SQL Sever程序组中启动ISQL/w。然后，在查询窗口中输入存储过程：

/*存储过程例程*/

CREATE PROCEDURE sp_CheckPass

@CHKName VARCHAR(30),@CHKPass VARCHAR(30),@ISValid CHAR(8) OUTPUT

AS

IF EXISTS(SELECT UserName FROM UsersWHERE UserName=@CHKName AND UserPwd=@CHKPass)

SELECT @ISVaid="pass"

ELSE

SELECT @ISValid="Invalid"

---- 　　存储过程接收两个输入参数。输入参数@CHKaName向存储过程传递一个用户名。@CHKPass向存储过程传递一个密码。如果有用户拥有指定的密码，输出参数将返回“pass"，否则，返回“Invalid"。

---- 　　现在我们可以在CheckPass.asp中调用存储过程sp_CheckPass 了。

CheckPass.asp

< !--#INCLUDE VIRTUAL="ADOVBS.inc"-- >  ’包含VBSCRIPT的常量定义文件

< %Set DataConn=Sever.CreateObject("ADODB.Connection")  '建立连接对象

Dataconn.Open"DSN=user;SERVER=APP_SERVER;UID=sa;PWD=;APP=Microsoft(R) Developer Studio;WSID=APP_SERVER;Regional=Yes"

Set cmdTemp=Sever.CreateObject("ADODB.Command")  '建立命令对象

Set cmdTemp.ActiveConnection=DataConn

cmdTemp.CommandType=adCMdStoredProc  '指定要执行的是一个存储过程

cmdTemp.CommandText="sp_CheckPass"  '存储过程名称

Set　tmpFirstParam=cmdTemp.CreateParameter("UserName",adVarChar,adParamIntput,30) '创建输入参数对象

cmdTemp.Parameters.Append.tmpFirstParam  '把参数加到参数集合

Set tmpSecondParam=cmdTemp.CreateParameter("UserPwd",adVarChar,adParamInput,30)

cmdTemp.ParaMeters.Append tmpSecondParam

Set tmpThirdParam=cmdTemp.CreateParameter("RetValue",adChar,adParamOutput,8)    '创建返回参数对象

cmdTemp.Parameters.Append tmpThirdParamcmdTemp("UserName")=Request.FORM("UserName") ’取得输入参数

cmdTemp("UserPwd")=Request.FORM("UserPwd")

cmdTemp.Execute  ’执行存储过程% >

The Check Result is < %=cmdTemp("RetValue")% >  '输出返回值

< %DataConn.Close  '关闭数据库连接% >//用户输入界面

< HTML  >< HEAD  >< TITLE  >Registration Page< /TITLE  >< /HEAD  >

< H2  >请输入用户名和密码：< /H2  >  < FORM METHOD="POST" ACTION="CheckPass.asp"  >

< p  >用户名:     < input name="UserName" type="TEXT"  >  < /p >

< p > 密　码:     < input name="UserPwd" type="PASSWORD"  >

< input type="submit" name="submit" value="确定" >  < /p >

< /FORM > < /BODY > < /HTML >

---- 　　在这个例子中，首先创建了命令对象的一个实例。接着，ActiveConnection属性把命令和一个打开的连接联系在一起。CommandText属性指定要执行的存储过程名。CommandType属性为adCMDStoredProc属性，指明该命令是一个存储过程的定义。而为了得到存储过程的返回状态值必须用命令对象的CreateParameter()方法建立参数。本例中CreateParameter()方法有四个参数：

---- 第一个参数为新参数指定一个名字；

---- 第二个参数指定数据类型；

---- 第三个参数指定新参数的类型。在此例中，常量adParamReturnValue指明该参数是一个返

---- 回参数。常量adParamInput则指明该参数是一个输入参数；

---- 第四个参数指定了每个参数的最大长度，特别是当建立的参数是变长度数据类型，如

---- VARCHAR型时，就必须指定一个最大长度。

---- 本例中用CreateParameter()方法建立了三个参数对象。其中两个输入参数，一个输出参数。建立了任何新参数之后，都必须把它添加到命令对象的Parameters集合中。Append方法用来把新参数添加到这个集合中。最后，调用Execute方法执行存储过程。用户输入的用户名和密码被传递给存储过程。如果表中存在这个名字－密码组合，则报告该密码为Pass,否则报告该密码为Invalid。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
        存储过程的种类：
          1.系统存储过程：以sp_开头,用来进行系统的各项设定.取得信息.相关管理工作,
          如   sp_help就是取得指定对象的相关信息
          2.扩展存储过程   以XP_开头,用来调用操作系统提供的功能
          exec   master..xp_cmdshell   'ping   10.8.16.1'
          3.用户自定义的存储过程,这是我们所指的存储过程
          常用格式
          Create   procedure   procedue_name
          [@parameter   data_type][output]
          [with]{recompile|encryption}
          as
          sql_statement
        解释:
        output：表示此参数是可传回的
        with   {recompile|encryption}
        recompile:表示每次执行此存储过程时都重新编译一次
        encryption:所创建的存储过程的内容会被加密
        如:
          表book的内容如下
          编号   书名   价格
          001   C语言入门   $30
          002   PowerBuilder报表开发   $52
          实例1:查询表Book的内容的存储过程
          create   proc   query_book
          as
          select   *   from   book
          go
          exec   query_book
          实例2:加入一笔记录到表book,并查询此表中所有书籍的总金额
          Create   proc   insert_book
          @param1   char(10),@param2   varchar(20),@param3   money,@param4   money   output
          with   encryption   ---------加密
          as
          insert   book(编号,书名，价格）   Values(@param1,@param2,@param3)
          select   @param4=sum(价格)   from   book
          go
          执行例子:
          declare   @total_price   money
          exec   insert_book   '003','Delphi   控件开发指南',$100,@total_price
          print   '总金额为'+convert(varchar,@total_price)
          go
        存储过程的3种传回值:
          1.以Return传回整数
          2.以output格式传回参数
          3.Recordset
        传回值的区别:
          output和return都可在批次程式中用变量接收,而recordset则传回到执行批次的客户端中
        实例3：设有两个表为Product,Order,其表内容如下：
          Product
          产品编号   产品名称   客户订数
          001   钢笔   30
          002   毛笔   50
          003   铅笔   100
          Order
          产品编号   客户名   客户订金
          001   南山区   $30
          002   罗湖区   $50
          003   宝安区   $4
        请实现按编号为连接条件,将两个表连接成一个临时表,该表只含编号.产品名.客户名.订金.总金额,
        总金额=订金*订数,临时表放在存储过程中
        代码如下:
          Create   proc   temp_sale
          as
          select   a.产品编号,a.产品名称,b.客户名,b.客户订金,a.客户订数*   b.客户订金   as总金额
          into   #temptable   from   Product   a   inner   join   Order   b   on   a.产品编号=b.产品编号
          if   @@error=0
          print   'Good'
          else
        &n bsp; print   'Fail'
          go