T-SQL教程三

    存储过程（procedure）类似于C语言中的函数用来执行管理任务或应用复杂的业务规则存储过程可以带参数，也可以返回结果存储过程可以包含数据操纵语句、变量、逻辑 控制语句等

存储过程的分类
    系统存储过程
    由系统定义，存放在master数据库中类似C语言中的系统函数系统存储过程的名称都以“sp_”开头或”xp_”开头
    用户自定义存储过程
        由用户在自己的数据库中创建的存储过程类似C语言中的用户自定义函数

常用的系统存储过程

EXEC sp_databases
EXEC  sp_renamedb 'Northwind','Northwind1'
USE stuDB
GO
EXEC sp_tables
EXEC sp_columns stuInfo 
EXEC sp_help stuInfo
EXEC sp_helpconstraint stuInfo
EXEC sp_helpindex stuMarks
EXEC sp_helptext 'view_stuInfo_stuMarks'
EXEC sp_stored_procedures 

常用的扩展存储过程：xp_cmdshell

可以执行DOS命令下的一些的操作
以文本行方式返回任何输出

调用语法：
EXEC xp_cmdshell DOS命令 [NO_OUTPUT]

创建存储过程

定义存储过程的语法

CREATE  PROC[EDURE]  存储过程名
      @参数1  数据类型 = 默认值 OUTPUT,
       …… ,
      @参数n  数据类型 = 默认值 OUTPUT
    AS
    SQL语句
GO
和C语言的函数一样，参数可选
参数分为输入参数、输出参数
输入参数允许有默认值

实例：

CREATE PROCEDURE proc_stu
  AS
DECLARE @writtenAvg float,@labAvg float
    SELECT @writtenAvg=AVG(writtenExam), @labAvg=AVG(labExam) 
    FROM stuMarks
    print '笔试平均分：'+convert(varchar(5),@writtenAvg)
    print '机试平均分：'+convert(varchar(5),@labAvg)

    IF(@writtenAvg>70 AND @labAvg>70)
    print '本班考试成绩：优秀'
    ELSE
    print '本班考试成绩：较差'
    print '-----------------------------------------------'
    print '       参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,labExam
    FROM stuInfo INNER JOIN stuMarks
    ON
    stuInfo.stuNo=stuMaks.stuNo
    WHERE writtenExam<60 OR labExam<60
GO

调用存储过程

EXECUTE（执行）语句用来调用存储过程

调用的语法
    EXEC  过程名  [参数]
    EXEC proc_stu

创建带参数的存储过程
    存储过程的参数分两种
    输入参数
    用于向存储过程传入值，类似C语言的按值传递;
    输出参数
    用于在调用存储过程后，返回结果，类似C语言的按引用传递;

 
CREATE PROCEDURE proc_stu
  @writtenPass int 60, 
  @labPass int   60
  AS
    print '--------------------------------------------------'
    print '           参加本次考试没有通过的学员：'
    SELECT stuName,stuInfo.stuNo,writtenExam,
       labExam  FROM  stuInfo
  INNER JOIN stuMarks ON              
     stuInfo.stuNo=stuMarks.stuNo
WHERE writtenExam<@writtenPass
  OR labExam<@labPass
GO
EXEC proc_stu   --都采用默认值
EXEC proc_stu 64  --机试采用默认值
EXEC proc_stu 60,55   --都不采用默认值
如果希望调用存储过程后，返回一个或多个值，这时就需要使用输出（OUTPUT）参数了
CREATE PROCEDURE proc_stu
  @notpassSum int OUTPUT,
  @writtenPass int=60,  
  @labPass int=60 
  AS
    ……
     SELECT stuName,stuInfo.stuNo,writtenExam,
labExam FROM  stuInfo   INNER JOIN stuMarks
  ON stuInfo.stuNo=stuMarks.stuNo
    WHERE writtenExam<@writtenPass
      OR labExam<@labPass
    SELECT @notpassSum=COUNT(stuNo)
       FROM stuMarks  WHERE writtenExam<@writtenPass
   OR labExam<@labPass
GO

处理存储过程中的错误

    可以使用PRINT语句显示错误信息，但这些信息是临时的，只能显示给用户
    RAISERROR 显示用户定义的错误信息时paiserror可指定严重级别、设置系统变量@@ERROR、记录所发生的错误等。

RAISERROR语句的用法如下：
RAISERROR (msg_id | msg_str,severity,state WITH option[,...n]])
msg_id：在sysmessages系统表中指定用户定义错误信息
msg_str：用户定义的特定信息，最长255个字符
severity：定义严重性级别。用户可使用的级别为0–18级
state：表示错误的状态，1至127之间的值
option：指示是否将错误记录到服务器错误日志中
CREATE PROCEDURE proc_stu
  @notpassSum int OUTPUT, --输出参数
  @writtenPass int=60,  --默认参数放后
  @labPass int=60       --默认参数放后
  AS
    IF (NOT @writtenPass BETWEEN 0 AND 100)
     OR (NOT @labPass BETWEEN 0 AND 100)
       BEGIN
RAISERROR (‘及格线错误，请指定0－100之间的分
     数，统计中断退出',16,1)
RETURN  ---立即返回，退出存储过程
       END
    …..其他语句同上例，略
GO
---------------------------------------
/*---调用存储过程，测试RAISERROR语句----*/
DECLARE @sum int,  @t int
EXEC proc_stu @sum OUTPUT ,604  
SET @t=@@ERROR
print  '错误号：'+convert(varchar(5),@t )
IF @t<>0 
   RETURN  --退出批处理，后续语句不再执行
print '--------------------------------------------------'
IF @sum>=3
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,超过60%,及格分数线还应下调'
ELSE
  print '未通过人数：'+convert(varchar(5),@sum)+ '人,已控制在60%以下，及格分数线适中'
GO

触发器

为什么需要触发器

    为什需要触发器(TRIGGER)呢？典型的应用就是银行的取款机系统 它是一种特殊的存储过程，也具备事务的功能，它能在多表之间执行特殊的业务规则。

    触发器通常用于强制业务规则，触发器是在对表进行插入、更新或删除操作时自动执行的存储过程。触发器是一种高级约束，可以定义比用CHECK 约束更为复杂的约束，可执行复杂的SQL语句（if/while/case），可引用其它表中的列。

    触发器定义在特定的表上，与表相关自动触发执行不能直接调用是一个事务（可回滚）。

触发器的类型

    DELETE 触发器
    INSERT 触发器
    UPDATE 触发器

触发器触发时：

    系统自动在内存中创建deleted表或inserted表只读，不允许修改；触发器执行完成后，自动删除inserted 表。临时保存了插入或更新后的记录行，可以从inserted表中检查插入的数据是否满足业务需求如果不满足，则向用户报告错误消息，并回滚插入操作deleted 表；临时保存了删除或更新前的记录行，可以从deleted表中检查被删除的数据是否满足业务需求如果不满足，则向用户报告错误消息，并回滚插入操作。

创建触发器

CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
  FOR [DELETE, INSERT, UPDATE]
AS
  T-SQL语句
GO
---------WITH ENCRYPTION表示加密触发器定义的SQL文本
---------DELETE, INSERT, UPDATE指定触发器的类型

INSERT 触发器示例

-------关键代码------
CREATE TRIGGER trig_transInfo
ON transInfo
  FOR INSERT
   AS
   DECLARE @type char(4),@outMoney MONEY
   DECLARE @myCardID char(10),@balance MONEY
   SELECT @type=transType,@outMoney=transMoney，
@myCardID=cardID FROM inserted
     IF (@type='支取')
UPDATE bank SET currentMoney=currentMoney-@outMoney
     WHERE cardID=@myCardID
    ELSE
UPDATE bank SET currentMoney=currentMoney+@outMoney
     WHERE cardID=@myCardID
    …..
GO

DELETE触发器示例

-------关键代码------
CREATE TRIGGER trig_delete_transInfo
ON transInfo
  FOR DELETE
   AS
      print '开始备份数据，请稍后......'
      IF NOT EXISTS(SELECT * FROM sysobjects
   WHERE name='backupTable')
  SELECT * INTO backupTable FROM deleted
     ELSE
INSERT INTO backupTable SELECT * FROM deleted
      print '备份数据成功，备份表中的数据为:'
      SELECT * FROM backupTable
GO

UPDATE触发器示例

-------关键代码------
CREATE TRIGGER rig_update_bank
ON bank
  FOR UPDATE
   AS
      DECLARE @beforeMoney MONEY,@afterMoney MONEY 
      SELECT @beforeMoney=currentMoney FROM deleted  
      SELECT @afterMoney=currentMoney FROM inserted   
      IF ABS(@afterMoney-@beforeMoney)>20000
BEGIN
    print '交易金额:'+convert(varchar(8),
ABS(@afterMoney-@beforeMoney))
    RAISERROR ('每笔交易不能超过2万元，交易失败',16,1)
    ROLLBACK TRANSACTION
END
GO