SQL Server 存储过程、触发器、游标

542505989

　　
存储过程
    1、存储过程是事先编好的、存储在数据库中的程序，这些程序用来完成对数据库的指定操作。
    2、系统存储过程: SQL Server本身提供了一些存储过程，用于管理有关数据库和用户的信息。
       用户存储过程: 用户也可以编写自己的存储过程，并把它存放在数据库中，供客户端调用。
    3、这样安排的主要目的就是要充分发挥数据库服务器的功能，尽量减少网络上的堵塞。
    4、系统存储过程
    概念：
    它的目的在于能够方便地从系统表中查询信息，或者完成与更新数据库表相关的管理任务或其它的系统管理任务。
    系统存储过程可以在任意一个数据库中执行。创建并存放于系统数据库master中，并且名称以sp_或者xp_开头。
    部分系统存储过程：
       sp_addtype：用于定义一个用户定义数据类型。
       sp_configure：用于管理服务器配置选项设置。
       xp_sendmail：用于发送电子邮件或寻呼信息。
       sp_stored_procedures：用于返回当前数据库中的存储过程的清单。
       sp_help：用于显示参数清单和其数据类型。
       sp_helptext：用于显示存储过程的定义文本。
       sp_rename：用于修改当前数据库中用户对象的名称。
       Sp_who:用于显示使用数据库的当前用户
       sp_help：用于显示参数清单和其数据类型。
       sp_depends：用于显示存储过程依据的对象或者依据存储过程的对象
       sp_helptext：用于显示存储过程的定义文本。
    5、用户自定义的存储过程
    定义格式：
       CREATE PROC[edure] procedure_name [ ; number ]
       [ @parameter data_type [ = default ][output], … uc1]
       AS sql_statement

       procedure_name：给出存储过程名；
       number：对同名的存储过程指定一个序号(允许同名)；
       @parameter：给出参数名；
       data_type：指出参数的数据类型；
        Output:返回值参数
       default：给出参数的默认值；
       sql_statement：存储过程所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。
    注意事项:
        存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，
        即在存储过程中一般不能含有以下语句：
       CREATE TABLE ； CREATE VIEW ； CREATE DEFAULT ；
        CREATE RULE ；CREATE TRIGGER ；CREATE PROCEDURE
    存储过程的返回值和状态信息:
        无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。
        如果存储过程执行成功，返回的结果码是0；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。
        我们在创建存储过程时，也可以定义自己的状态码和错误信息。

    执行存储过程：
    例：执行带参数的存储过程,查询大于岁的学生
       create proc show;3 ( @pno char(6) )
       as
       select * from person where Pno = @pno
       exec show;3 4
    例：CREATE Procedure sp_getstu;1
       AS
       SELECT * FROM 学生
    例：带参数的存储过程,查询大于指定年龄的学生
       CREATE proc sp_getstu;2 (@sage int)
       AS
       SELECT * FROM 学生WHERE年龄> @sage

    例: 带输出参数的存储过程,查询指定学生的年龄
       CREATE proc sp_getstu;3 ( @name char(10) , @age int output )
       AS
       SELECT @age=年龄 FROM学生WHERE姓名= @name

       Declare @sage int
       Exec sp_getstu;3 '张三',@sage
       Print @sage

    例：带参数和返回状态值的存储过程。
       CREATE PROCedure sp_getstu;3 (@sage int =NULL )
       AS
       IF @sage IS NULL
           BEGIN
              PRINT '必须提供一个数值作参数！'
              RETURN 13
           END
       IF NOT EXISTS (SELECT * FROM student WHERE sage > @sage)
           BEGIN
              PRINT '没有满足条件的记录！'
              RETURN -103
           END
           SELECT * FROM student WHERE sage > @sage
           RETURN 0
       DECLARE @status int
       EXECUTE @status=sp_getstu;3 22
       print @status


触发器
1、定义格式
    CREATE TRIGGER trigger_name
    ON table
    FOR { INSERT | UPDATE | DELETE }
    AS
    [IF UPDATE(column) [{AND | OR} UPDATE(column)…]]
    sql_statement
2、插入视图和删除视图
    为触发器运行而自动派生的两个视图：
    inserted——存放刚插入的新记录
    deleted——存放刚删除的旧记录
3、触发器分类
    插入类触发器…insert删除类触发器…delete更新类触发器…update
4、触发时机
    After|For触发器是在SQL Server服务器接到执行SQL语句请求之后，
    先建立临时的Inserted表和Deleted表，然后实际更改数据，最后才激活触发器的。

    Instead Of触发器在SQL Server服务器接到执行SQL语句请求后，先建立临时的Inserted表和Deleted表，
    然后就触发了Instead Of触发器，至于那个SQL语句是插入数据、更新数据还是删除数据，一概不管，
    把执行权全权交给了Instead Of触发器，由它去完成之后的操作。

例：建立一个简单的触发器。
    CREATE TRIGGER test_trigger
    ON student FOR  INSERT
    AS  PRINT '插入了一个元组'
例：定义一个触发器，使得当删除学生记录时，同时将所有该学生选课记录删除
    CREATE TRIGGER del_trigger
    ON STUDENT FOR DELETE
    AS
    DELETE FROM SC
    WHERE SNO = (SELECT SNO FROM deleted)
例：对学生表的插入操作定义一个触发器，使得当插入记录时，检查相应的学生年龄是否满足条件，如果不存在则显示错误信息。
    create trigger stu_tri
    on student instead of insert
    as
    if ( select age from inserted )>40
       print '不能插入大于岁的学生纪录'
    else insert into student select * from inserted

游标
1、需要游标的数据操作
    当select语句的结果中包含多个元组时，使用游标可以逐个存取这些元组
    活动集：select语句返回的元组的集合
    当前行：活动集中当前处理的那一行。游标即是指向当前行的指针。
2、游标分类
    滚动游标：游标的位置可以来回移动，可在活动集中取任意元组。
    非滚动游标：只能在活动集中顺序地取下一个元组。
    更新游标：数据库对游标指向的当前行加锁，当程序读下一行数据时，本行数据解锁，下一行数据加锁。
3、定义与使用游标的语句
    declare ：
        declare  游标名[scroll]  cursor  for select语句[for update [of列表名]]
        定义一个游标，使之对应一个select语句
       for update任选项，表示该游标可用于对当前行的修改与删除
    open
       打开一个游标，执行游标对应的查询，结果集合为该游标的活动集
       open  游标名
    fetch
       在活动集中将游标移到特定的行，并取出该行数据放到相应的变量中
       fetch [next | prior | first | last | current | relative n | absolute m] 游标名into  [变量表]
    close
       关闭游标，释放活动集及其所占资源。需要再使用该游标时，执行open语句
       close  游标名
    deallocate
       删除游标，以后不能再对该游标执行open语句
       deallocate游标名
    @@FETCH_STATUS
        返回被FETCH语句执行的最后游标的状态.
       0 fetch语句成功
        -1 fetch语句失败
        -2 被提取的行不存在

    例:查询电子商务系学生信息,性别为女输出为female,否则输出为male？
       declare c1 cursor for select sno,sname,ssex from student where sdept='ec'
       declare @sno char(10),@sname char(10),@ssex char(2)
       Open c1
       Fetch c1 into @sno,@sname,@ssex
       While @@fetch_status==0
       Begin
       if @ssex='女'
           begin  set @ssex='female' end
       else
           begin set @ssex='male' end
       Select @sno,@sname ,@ssex
           Fetch c1 into @sno,@sname,@ssex
       end