SQL server Base III (存储过程 和 触发器)
继整理c++方面资料后，blog好久都没有更新了，最近准备抽空把关于DotNET的学习记录整理上传。此篇为SQL Server存储过程和触发器相关资料。
如果觉得文章内容对您有用欢迎任意转载。但请注明出处http://www.cnblogs.com/nniixl/。
THE8
系统存储过程的名称都以“sp_”开头或”xp_”开头
常用节选
系统存储过程 说明
|
sp_databases |
列出服务器上的所有数据库。 |
|
sp_helpdb |
报告有关指定数据库或所有数据库的信息 |
|
sp_renamedb |
更改数据库的名称 |
|
sp_tables |
返回当前环境下可查询的对象的列表 |
|
sp_columns |
回某个表列的信息 |
|
sp_help |
查看某个表的所有信息 |
|
sp_helpconstraint |
查看某个表的约束 |
|
sp_helpindex |
查看某个表的索引 |
|
sp_stored_procedures |
列出当前环境中的所有存储过程。 |
|
sp_password |
添加或修改登录帐户的密码。 |
|
sp_helptext |
显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。 |
注：过程由EXEC指定打头执行
常用的扩展存储过程：xp_cmdshell
可以执行DOS命令下的一些的操作
以文本行方式返回任何输出
调用语法：
EXEC xp_cmdshell DOS命令 [NO_OUTPUT]
定义存储过程的语法
CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 = 默认值 OUTPUT,
…… ,
@参数n 数据类型 = 默认值 OUTPUT
AS
SQL语句
GO
和C语言的函数一样，参数可选
参数分为输入参数、输出参数
输入参数允许有默认值
EXP:
设计1（无默认值 无输出型）
CREATE PROCEDURE proc_stu
@writtenPass int, --笔试及格线
@labPass int --上机及格线
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
调用
方法1： EXEC proc_stu 60, 55
方法2： EXEC proc_stu @labPass = 55, @writtenPass = 60
设计2（有默认值 有输出型）
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
调用
/*---调用存储过程----*/
DECLARE @sum int
EXEC proc_stu @sum OUTPUT ,64 --要指定输出变量名和OUTPUT参数
print '--------------------------------------------------'
IF @sum>=3
print '未通过人数：'+convert(varchar(5),@sum)+ '人, 超过60%,及格分数线还应下调'
ELSE
print '未通过人数：'+convert(varchar(5),@sum)+ '人, 已控制在60%以下，及格分数线适中'
GO
自定义错误RAISERROR语句
RAISERROR (msg_id | msg_str, severity, state WITH option [,...n]])
msg_id：在sysmessages系统表中指定用户定义错误信息
msg_str：用户定义的特定信息，最长255个字符
severity：定义严重性级别。用户可使用的级别为0–18级
state：表示错误的状态，1至127之间的值
option：指示是否将错误记录到服务器错误日志中
EXP:
设计
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
THE9
ON 表名
FOR INSERT、UPDATE 或 DELETE
AS
...
CREATE TRIGGER Trigger_name
ON Table_name
[with encrypyion] –加密防止复制
FOR {[DELETE INSERT UPDATE]}
AS
SQL语句
Inserted 表
插入命令 更新命令的插入步骤
Deleted 表
删除命令 更新命令的删除步骤
Exp：
USE MyDB
GO
IF EXISTS (SELECT name
FROM sysobjects WHERE name = ‘trigger_test’)
DROP TRIGGER
trigger_test
CREATE TRIGGER
trigger_test
ON table_test
FOR update
AS
DECLARE @afterNum,
@afterNum
SELECT @beforeNum =currNum FROM
deleted
SELECT @ afterNum=currNum FROM
inserted
IF ABS(@afterNum - @afterNum) > 1000
BEGIN
RAISERROR(‘该测试数据不可大于1000’)
ROLLBACK
TRASACTION
END
触发器 检查修改某列
