数据库原理与应用教程―SQLServer存储过程、触发器和游标存储过程、触发器和游标存储过程触发器游标存储过程、触发器和游标1存储过程SQLServer2005的存储过程(StoredProcedure)包含一些T-SQL语句并以特定的名称存储在数据库中。可以在存储过程中声明变量、有条件地执行以及其他各项强大的程序设计功能。存储过程、触发器和游标1.1存储过程概述一种数据库对象存储过程可以接受输入参数、输出参数,返回单个或多个结果集以及返回值,由应用程序通过调用执行。SQLServer支持存储过程和系统过程。存储过程是独立存在于表之外的数据对象。可以由客户调用,也可以从另一个过程或触发器调用,参数可以被传递和返回,出错代码也可以被检验。存储过程、触发器和游标目标:了解存储过程的优点掌握如何创建存储过程(参数、返回值)掌握如何调用存储过程了解为什么需要触发器理解触发器的工作原理掌握如何使用inserted表和deleted表掌握如何创建:INSERT触发器UPDATE触发器DELETE触发器游标的基本更新理解游标的使用步骤存储过程、触发器和游标1.1存储过程概述在性能方面,存储过程有如下优点:(1)预编译:存储过程预先编译好放在数据库内,减少编译语句所花的时间。(2)缓存:编译好的存储过程会进入缓存,所以对于经常执行的存储过程,除了第一次执行外,其他次执行的速度会有明显提高。(3)减少网络传输:特别对于处理一些数据的存储过程,不必像直接用T-SQL语句实现那样多次传送数据到客户端。(4)更好的利用服务器内存:特别对于处理中间数据量不大的情况,存储过程中可以利用存放在内存的表变量。存储过程、触发器和游标1.1存储过程概述一般来讲,应使用SQLServer中的存储过程而不使用存储在客户计算机本地的T-SQL程序,其优势主要表现在:(1)允许模块化程序设计。(2)允许更快速地执行。(3)减少网络流量。(4)可作为安全机制使用。存储过程、触发器和游标1.2存储过程的类型1.系统存储过程主要存储在master数据库中并以sp_为前缀,并且系统存储过程主要是从系统表中获取信息,从而为数据库系统管理员管理SQLServer提供支持。通过系统存储过程,SQLServer中的许多管理性或信息性的活动(如获取数据库和数据库对象的信息)都可以被顺利有效地完成。存储过程、触发器和游标1.2存储过程的类型2.本地存储过程本地存储过程也就是用户自行创建并存储在用户数据库中的存储过程,一般所说的存储过程指的就是本地存储过程。用户创建的存储过程是由用户创建并能完成某一特定功能(如查询用户所需的数据信息)的存储过程。存储过程、触发器和游标1.2存储过程的类型3.临时存储过程临时存储过程可分为以下两种:(1)本地临时存储过程不论哪一个数据库是当前数据库,如果在创建存储过程时,其名称以“#”号开头,则该存储过程将成为一个存放在tempdb数据库中的本地临时存储过程。(2)全局临时存储过程不论哪一个数据库是当前数据库,只要所创建的存储过程名称是以两个“#”号开头,则该存储过程将成为一个存储在tempdb数据库中的全局临时存储过程。存储过程、触发器和游标1.3创建存储过程1.在SQLServerManagementStudio中创建存储过程步骤如下:(1)打开SQLServerManagementStudio,展开要创建存储过程的数据库,展开“可编程性”选项,可以看到存储过程列表中系统自动为数据库创建的系统存储过程。右键单击“存储过程”选项,选“新建存储过程”命令。(2)出现创建存储过程的T-SQL命令,编辑相关的命令即可。(3)命令编辑成功后,进行语法检查,然后单击“确定”按钮,至此一个新的存储过程建立成功。存储过程、触发器和游标1.3创建存储过程2.利用T-SQL语句创建存储过程CREATEPROCEDURE创建存储过程,语法格式如下:CREATE{PROC|PROCEDURE}procedure_name[;number][{@parameterdata_type}[VARYING][=default][[OUT[PUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}[,...n]][FORREPLICATION]ASsql_statement[...n]存储过程、触发器和游标1.3创建存储过程在创建存储过程时,应当注意以下几点。(1)存储过程最大不能超过128MB。(2)用户定义的存储过程只能在当前数据库中创建,但是临时存储过程通常是在tempdb数据库中创建的。(3)在一条T-SQL语句中CREATEPROCEDURE不能与其他T-SQL句一起使用。(4)SQLServer允许在存储过程创建时引用一个不存在的对象,在创建的时候,系统只检查创建存储过程的语法。存储过程、触发器和游标1.3创建存储过程[例11-1]在教学库创建无参存储过程,查询每个同学各门功课的平均成绩。CREATEPROCEDUREstudent_avgASSELECT学生号,avg(成绩)as'平均分'FROM选课GROUPBY学生号存储过程、触发器和游标1.3创建存储过程[例11-2]在教学库创建带参数的存储过程,查询某个同学的基本信息。CREATEPROCEDUREGetStudent@numberchar(7)ASSELECT*FROM学生WHERE学生号=@number存储过程、触发器和游标1.3创建存储过程[例11-3]在教学库创建带有参数和默认值(通配符)的存储过程,从学生表中返回指定的学生(提供姓名)的信息。该存储过程对传递的参数进行模式匹配,如果没有提供参数,则返回所有学生的信息。CREATEPROCEDUREStudent_Name@namevarchar(40)='%'ASSELECT*FROM学生WHERE姓名LIKE@name存储过程、触发器和游标1.3创建存储过程[例11-4]在仓库库存数据库创建带OUTPUT参数的存储过程,用于计算指定的商品的平均价格,存储过程中使用一个输入参数(商品名)和一个输出参数(平均价格)。CREATEPROCEDUREPname@p_nvarchar(20),@aveageintOUTPUTASSELECT@aveage=avg(单价)FROM商品WHERE商品名称=@p_n存储过程、触发器和游标1.4执行存储过程EXECUTE命令。如果被调用的存储过程需要参数输入时,在存储过程名后逐一给定,每一个参数用逗号隔开,不必使用括号。如果没有使用@参数名=default这种方式传入值,则参数的排列必须和建立存储过程所定义的次序对应。用来接受输出值的参数则必须加上OUTPUT。存储过程、触发器和游标1.4执行存储过程EXECUTE语句的语法格式为:[[EXECUTE[UTE]][@return_status=]procedure_name[;number]{[[@parameter=]value|[@parameter=]@variable[OUTPUT]]}[WITHRECOMPILE][例11-5]执行存储过程student_avg。EXECUTEstudent_avg存储过程、触发器和游标1.4执行存储过程[例11-6]执行带参数的存储过程GetStudent,查询学号为‘01010001’的学生的基本信息。EXECUTEGetStudent‘01010001’[例11-7]执行带有参数和默认值(通配符)的存储过程Student_Name。(1)显示所有学生的信息:EXECUTEStudent_Name(2)显示“王小明”所有学生的信息:EXECUTEStudent_Name‘王小明’[例11-8]执行带有输入和输出参数的存储过程Pname。Declare@avgageintEXECUTEPname'冰箱',@avgageOUTPUTPrint'冰箱的平均价格:'+str(@avgage).NET中调用存储过程功能:返回销售额和插入的记录SN字段值SqlConnectionconn=newSqlConnection();conn.ConnectionString=@DataSource=.;InitialCatalog=CPXS;IntegratedSecurity=True;if(conn.State==ConnectionState.Closed)conn.Open();SqlCommandcmd=newSqlCommand();cmd.Connection=conn;cmd.CommandText=销售InsertOne;cmd.CommandType=CommandType.StoredProcedure;//cmd.CommandText=INSERTINTO[CPXS].[dbo].[销售]([产品编号],[客户编号],[销售日期],[数量],[销售额])+//VALUES(@产品编号,@客户编号,@销售日期,@数量,@销售额);//cmd.CommandType=CommandType.Text;cmd.Parameters.AddWithValue(@产品编号,DropDownList1.SelectedValue);cmd.Parameters.AddWithValue(@客户编号,DropDownList2.SelectedValue);cmd.Parameters.AddWithValue(@销售日期,Convert.ToDateTime(TextBox1.Text.Trim()));cmd.Parameters.AddWithValue(@数量,Convert.ToInt32(TextBox2.Text.Trim()));cmd.Parameters.AddWithValue(@销售额,0);cmd.Parameters[@销售额].Direction=ParameterDirection.InputOutput;//SqlParameterret=cmd.Parameters.Add(@ret,SqlDbType.Int);//ret.Direction=ParameterDirection.ReturnValue;cmd.Parameters.AddWithValue(@ret,0);//返回值类型参数的名称可以是存储过程参数中定义的参数cmd.Parameters[@ret].Direction=ParameterDirection.ReturnValue;cmd.ExecuteNonQuery();intretvalue=Convert.ToInt32(cmd.Parameters[@ret].Value);decimalxse=Convert.ToDecimal(cmd.Parameters[@销售额].Value);GridView1.DataBind();CREATEPROCEDURE销售InsertOne@产品编号CHAR(6),@客户编号CHAR(6),@销售日期DATETIME=NULL,@数量INT=NULL,@销售额DECIMAL(18,0)=NULLOUTPUTASSETNOCOUNTONSELECT@销售额=@数量*价格FROMdbo.产品WHERE产品编号=@产品编号INSERT销售(产品编号,客户编号,销售日期,数量,销售额)VALUES(@产品编号,@客户编号,@销售日期,@数量,@销售额)RETURN@@IDENTITYSETNOCOUNTOFFGO存储过程、触发器和游标1.5查看、修改和删除存储过程1.查看存储过程可以执行系统存储过程sp_h