数据库编程_3(存储过程，触发器)

如果存储过程的参数作为一个集合，则该输出参数不在存储过程的参数中声明，而是在存储过程中创建一个临时表来存储该集合值。该存储过程可以直接在服务器端运行，也可以在客户端远程调用运行，远程调用时存储过程还是在服务器运行。//逐行显示同学的姓名，课程名，成绩。本例中使用游标是因为，学生的成绩不止一门，所以需要游标了遍历输出，如果只有一个单一的数据，可以不使用有游标，直接用变量代替即可。输入某同学的学号，使

DNLi973

812人浏览 · 2023-07-02 22:27:58

DNLi973 · 2023-07-02 22:27:58 发布

1.存储过程

存储过程是为了完成特定功能汇成而命令成的sql语句集合，该集合编译后存放在数据库中，可以根据实际情况重新编译。该存储过程可以直接在服务器端运行，也可以在客户端远程调用运行，远程调用时存储过程还是在服务器运行。

优点

将业务操作封装：可以为复杂的业务操作编写存储过程，放在数据库中。用户可以调用存储过程执行，而业务操作对用户是不可见的。若存储过程仅修改了执行体，而没有修改接口，则用户程序不需要修改，到达了业务封装的效果
便于事务管理：事务控制可以用在存储过程中，程序员可以依据业务的性质定义事务，并对事务进行相应级别的操作
实现一定程度的安全性保护：由于存储过程是在存放在数据库中的，且在服务器端运行，因此对于那些不允许用户直接操作的表或视图，如果用户又需要对这些表或视图进行操作，则可以通过调用存储过程来间接的访问这些表或视图，从而到达一定程度的安全性。对存储过程只需要授予执行权限，不需要授予表或视图的操作权限
特别适合统计和查询操作
减少网络通信：存储过程仅在服务器端执行，客户端只接收结果。由于存储过程与数据一般在一个服务器中，因此可以减少大量的网络通信

创建存储过程的语法：

create procedure <procedureName>

[ ( <@parameterName> <datatype> [ = <defaultValue> ] [output ] ) ]

<sql-statements>

<procedureName>:过程名，必须符合标识符规则，且在数据库中必须是唯一的
<parameterName>:参数名，存储过程中可以不带参数，参数可以是变量，常量和表达式
output：输出参数，被调用者获得使用

如果存储过程的参数作为一个集合，则该输出参数不在存储过程的参数中声明，而是在存储过程中创建一个临时表来存储该集合值。临时表的表明前需要加一个#符号，如#myTemp。在存储过程的尾部，使用语句select * from #myTemp。存储过程结束之后，临时表将会被自动删除。

例

输入某同学的学号，使用游标统计该同学的平均分，并返回平均分，同时逐行的显示该同学的姓名，选课名称和选课成绩

create procedure proStudentByNo (@ sNo char(7),@ avg numeric(6,2) output)

begin

declare @sName varchar(20) , @cName varchar(20)

declar @score tinyint , @sum int , @count tinyint

select @sum=0 ,@count = 0

//定义游标

declare curScore cursor for

select studentName ,courseName , socre

from Score a ,Student b,Course c

where b.studentNo = @sNo and a.studentNo=b.studentNo and a.courseNo = c.courseNo

//打开游标

open curScore

//获取当前游标值，并下移一个元组

fetch curScore into @sName,@cName,@score

//判断是否将当前游标的值取出

while（@@fetch_status==0）

begin

//逐行显示同学的姓名，课程名，成绩。

select @sName ,@cName,@score

set @sum = @sum+@score

set @count = @count+1

//继续获取游标的值

fetch curScore into @sName,@cName,@score

end

close curScore

deallocate curScore

if @count = 0

select @avg = 0

else

select @avg = @sum/@count

end

本例中使用游标是因为，学生的成绩不止一门，所以需要游标了遍历输出，如果只有一个单一的数据，可以不使用有游标，直接用变量代替即可

执行存储过程

在执行存储过程时，必须执行命令execute，否则该存储过程仅可以供创建者执行。

execute <procedurName>

[ { [ <@paramererName> = ] <expr>}]

[ { [ <@paramererName> = ] <@variableName> [ output ]}]

execute的参数必须与对应的procedure的参数相匹配

例

执行存储过程proStudentByNo2

declare @avg numeric(6,2)

execute proStudentByNo2 '1600001' , @avg output

select @ avg

1.修改存储过程

alter procedure <procedureName>

[ ( <@parameterName> <datatype> [ = <defaultValue> ] [output ] ) ]

<sql-statments>

例

修改上面proStudentByNo2，将显示结果的语句删除

alter procedure proStudentByNo2 (@ sNo char(7),@ avg numeric(6,2) output)

begin

... //和创建一样

while（@@fetch_status==0）

begin

//逐行显示同学的姓名，课程名，成绩。select @sName ,@cName,@score，这里不写

set @sum = @sum+@score

set @count = @count+1

//继续获取游标的值

fetch curScore into @sName,@cName,@score

end

..... //和创建一样

end

修改就是将创建里面的<sql-statements>重写

删除存储过程

drop procedure <procedureName>

例

删除存储过程proStudentByNo2

drop procedure proStudentByNo2

2.触发器

触发器是用户定义在关系表上的一类由事件驱动的存储过程，由服务器自动激活。

触发器是一种特殊的存储过程，他的优点是不管什么原因造成的数据变化都能自动响应，对于每条sql语句，触发器仅执行一次，事务可用于触发器中。触发器常用于保证完整性，并在一定程度上实现安全性。

以sql server介绍触发器

1.deleted表。存储insert和updata语句，执行时所影响的行的复制，在delete和updata语句执行前被作用的行转移到deleted表中，即将被删除的元组或修改前的元组值存入该表中。

2.inserted表。存储insert和update语句执行时所影响的行的复制，在insert和update语句执行期间，新行被同时加到inserted表和触发器作用的表中，即将被插入的元组或修改后的元组值存入该表中，同时也更新触发器作用的基本表

updata命令是删除后紧跟着插入，旧行首先复制到deleted表中，新行同时复制到inserted表中和触发器作用的基本表中

创建触发器

语法

create trigger <triggerName>

on <tableName>

for <insert | updata | delete>

as <sql-statement>

<triggerName>:触发器的名称，由于触发器是数据库的对象，因此在数据库中必须唯一
<tableName>:触发器作用的基本表
< insert | updata | delete >触发器事件，触发器的事件可以是插入insert，修改updata，或删除delete事件，也可以是这几个事件的总和。
<sql-statement>：触发动作的执行体，即一段完整的sql语句块

例

创建触发器，保证学生表中的性别仅能取男或女

create trigger sexUptIns

on Student

for insert,updata

if exists ( select * from inserted where sex not in (‘男’，‘女’))

rollback

修改触发器

alter trigger <triggerName>

on <tableName>

for <insert | updata |delete>

as <sql-Statement>