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

1.存储过程

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

优点

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

创建存储过程的语法：

create procedure <procedureName>

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

as

        <sql-statements>

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

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

例

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

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

as

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 ] ) ]

as

        <sql-statments>

例

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

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

as

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

as

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

                rollback

修改触发器

alter trigger <triggerName>

on <tableName>

for <insert | updata |delete>

as <sql-Statement>

<sql-Statement>跟上面创建触发器的用法一样

例

（书253页）

删除触发器

drop trigger <triggerName>