sqlserver数据库原理及应用第八章-存储过程和触发器

第8章：存储过程和触发器8.1存储过程的概念存储过程是SQL语句和可选控制流语句的预编译集合，它以一个名字存储并作为一个单元处理。8.2存储过程的分类在SQLServer中存储过程分为两类，即系统提供的存储过程和用户自定义的存储过程。第8章：存储过程和触发器1)系统存储过程：由系统自动创建，主要存储在master数据库中，一般以sp_为前缀。系统存储过程完成的功能主要是从系统表中获取信息。可以在其它数据库中调用系统存储过程。当创建一个新的数据库时，一些系统存储过程会在新数据库中被自动创建。第8章：存储过程和触发器2)用户自定义存储过程：由用户创建并能完成某一特定功能的存储过程。第8章：存储过程和触发器CREATEPROCP1ASSelectsname,cname,degreefromstudent,score,courseWherestudent.sno=score.snoandscore.cno=course.cno例如：第8章：存储过程和触发器3.使用存储过程的优点1)实现模块化编程2)使用存储过程可以加快程序的运行速度一个存储过程可以被多个用户共享和重用。存储过程在创建时即在服务器上进行编译，所以执行起来比单个sql语句快。第8章：存储过程和触发器3)使用存储过程可以减少网络流量存储过程存储在数据库内，由应用程序通过一个调用语句就可以执行它，不需要将大量T-SQL语句传送到服务器端。4)使用存储过程可以提高数据库的安全性用户可以调用存储过程，实现对表中数据的有限操作，但可以不赋予其直接修改数据表的权限，这样就提高了表中数据的安全性。第8章：存储过程和触发器4.存储过程创建1)使用企业管理器创建存储过程2)使用T-SQL创建存储过程第8章：存储过程和触发器3)创建存储过程应注意的事项：存储过程是数据库对象，其名称必须遵守标识符规则。不能将CREATEPROCEDURE语句与其他SQL语句组合到单个批处理中。只能在当前数据库中创建存储过程。创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。第8章：存储过程和触发器5.执行存储过程1)可以使用EXECUTE命令执行存储过程USEschoolEXECp12)或直接写存储过程的名称(如果存储过程是批处理的第一条语句)：USEschoolGO--批处理以GO结束P1……GO第8章：存储过程和触发器6.修改存储过程1)使用企业管理器修改存储过程(1)重命名(2)修改定义第8章：存储过程和触发器2)使用T-SQL语句修改存储过程(1)重命名sp_rename‘原名称’,‘新名称','object'(2)修改定义ALTERPROCEDUREauthorAS……第8章：存储过程和触发器7.删除存储过程1)使用企业管理器修改存储过程2)使用T-SQL语句修改存储过程DROPPROC[PROCEDUTE]sproc_name第8章：存储过程和触发器8.存储过程参数化(重点、难点)存储过程为我们提供了执行某种过程的能力，但是，如果它不能接受让其进行操作的某种数据，那么在大多数环境下来就没有用处。例如，建立一个删除表中数据的存储过程，要知道删除满足什么条件的记录。同样，有时候我们也想让存储过程输出一些信息，例如，我们建立一个更新表中数据的存储过程，一般情况需要知道到底更新了多少条记录，等等。要想实现上面的功能，就需要建立带有参数的存储过程。第8章：存储过程和触发器1)创建带有输入参数的存储过程例题1：创建一个向表student中输入数据的存储过程。第8章：存储过程和触发器UseschoolGOCREATEPROCspinsert@snochar(5),@snamechar(8),@ssexchar(2),@sbirthdaydatetime,@classchar(5)ASINSERTINTOstudent(sno,sname,ssex,sbirthday,class)VALUES(@sno,@sname,@ssex,@sbirthday,@class)执行存储过程:execspinsert‘111’,’张三’,’男’,’1980-1-1’,’95031’上面例题创建的存储过程spinsert一共需要5个参数，并且因为没有给这些参数提供默认值，所以为了成功运行该存储过程，必须提供这些参数值。如果执行如下语句：execspinsert‘112’,‘李四’,‘男’,‘1985-1-2’其中少了一个参数，尽管在基本表中该字段允许为空，但是此proc也不能被成功执行。第8章：存储过程和触发器例题2：2)创建带有输入参数的存储过程，同时给参数提供默认值。第8章：存储过程和触发器UseschoolGOCREATEPROCspinsert@snochar(5),@snamechar(8),@ssexchar(2),@sbirthdaydatetime,@classchar(5)=‘95031’--(或者@classchar(5)=null)ASINSERTINTOstudent(sno,sname,ssex,sbirthday,class)VALUES(@sno,@sname,@ssex,@sbirthday,@class)execspinsert‘112’,‘李四’,‘男’,‘1985-1-2’执行存储过程:此时，该proc执行成功!3)创建带有输出参数的存储过程第8章：存储过程和触发器UseschoolGOCREATEPROCaverage@st_noint,@st_namechar(8)output,@st_avgfloatoutputASSelect@st_name=student.sname,@st_avg=avg(score.degree)Fromstudent,scorewherestudent.sno=score.snoGroupbystudent.sno,student.snameHavingstudent.sno=@st_noDeclare@st_namechar(8)Declare@st_avgfloatExecaverage108,@st_nameoutput,@st_avgoutputSelect‘姓名’=@st_name,’平均分’=@st_avgGo执行存储过程:第8章：存储过程和触发器Declare@st_namechar(8)Declare@st_avgfloatExecaverage108,@st_nameoutput,@st_avgoutputPrint@st_nameprint@st_avgGo或者:第8章：存储过程和触发器Declare@st_namechar(8)Declare@st_avgfloatExecaverage108,@st_nameoutput,@st_avgoutputPrint@st_name+cast(@st_avgaschar(4))Go或者:第8章：存储过程和触发器9.存储过程的返回值例题1：1)创建存储过程createprocspreturnasdeclare@a1char(30)set@a1=’下面是存储过程的返回值：’print@a1第8章：存储过程和触发器declare@bintexec@b=spreturnprint@b可以看到运行结果是：下面是存储过程的返回值：0存储过程在执行后都会返回一个整型值。如果执行成功，返回0；否则返回-1到-99之间的数值。0是存储过程的返回值。第8章：存储过程和触发器2)运行存储过程例如：执行前面创建的存储过程spinsertdeclare@bintexec@b=spinsert'112','李四','男','1985-1-2','95031'print@b执行两次上面的语句，第二次就不能成功执行了。所以返回值是个非0值。第8章：存储过程和触发器格式：return[integervaluetoreturn]注意：返回值必须是整数。Return语句的最大特点是：一旦执行了return语句，那么系统就无条件的从过程中退出。也就是说，无论在过程的哪个位置执行了return语句，就再也不会执行该过程中的语句了。例如在第六章讲到的例题：第8章：存储过程和触发器declare@a1intset@a1=1print@a1returnset@a1=2print@a1return我们知道过程运行结果是1，这就说明了没有执行第一个return后面的语句。如果把第一个return删除再运行过程，会得到1和2。证明这次过程中的语句都被执行了。第8章：存储过程和触发器Return怎么用在存储过程中呢？第8章：存储过程和触发器修改例题1如下：例题2：1)创建存储过程createprocspreturnasdeclare@a1char(30)set@a1=’下面是存储过程的返回值：’print@a1return100--return默认返回0第8章：存储过程和触发器2)运行存储过程declare@bintexec@b=spreturnprint@b通过例题可以看到，可以给return语句指定返回值。但例题1没有指定返回值，结果仍然有返回值0。这是因为系统本身有返回值，如果过程成功执行，返回0。因此，例题1默认的返回了0。但是，我们也可以让过程返回我们希望得到的值，例如例题2。一般情况下，我们都给return指定返回值。第8章：存储过程和触发器说明：返回值(return实现)与输出参数不是一回事。但有的情况，两者可以完成相同的功能。例如：创建一个proc，功能是：求1-N的和。要求：使用输出参数和返回值两种方式来实现。第8章：存储过程和触发器执行：declare@aintexecp15,@aoutputprint@acreateprocp1@nint,@sumintoutputasdeclare@iintset@sum=0set@i=1while(@i=@n)beginset@sum=@sum+@iset@i=@i+1endcreateprocp1@nintasdeclare@sumint,@iintset@sum=0set@i=1while(@i=@n)beginset@sum=@sum+@iset@i=@i+1Endreturn@sumdeclare@aintexec@a=P15print@a小练习第8章：存储过程和触发器练习1:创建一个proc，功能是输入学号和课程号，能够显示相应的成绩?selectdegreefromscorewheresno=‘101’andcno=‘2-124’用查询语句，显示学号为‘101’，课程号为‘2-124’的学生的成绩。？第8章：存储过程和触发器第一步：selectdegreefromscorewheresno=‘101’andcno=‘2-124’创建一个proc，功能是显示学号为‘101’，课程号为‘2-124’的学生的成绩。？CreateprocP1as第8章：存储过程和触发器第二步：创建一个proc，功能是输入学号和课程号，能够显示相应的成绩?第8章：存储过程和触发器第三步：createprocp1@x1int,@x2int,@xintoutputasselect@x=degreefromscorewheresno=@x1andcno=@x2执行proc：方式一declare@xintexecp1'0301','0001',@xoutputprint@x或者声明其他名字的变量，例如：@a第8章：存储过程和触发器或者是：方式二declare@xintexecp1@x1='0301',@x2='0001',@x=@xoutputprint@x如果创建存储过程时，把参数的顺序改一下，即createprocp1@xintoutput，@x1int,@x2intas…….然后按方式一的语句执行，发现出现了问题。如果用方式二的语句执行，就可以了，执行语句如下：第8章：存储过程和触发器declare@aintexecp1@x1='0301',@x2='000