sql server 存储过程,触发器,事务

尽管sql server 提供了使用方便的图形化用户界面,但是各种功能的实现基础是 transact-sql 语言.
transact-sql语言直接来源于sql语言,因此也具有sql语言的几个特点:
- 一体化的特点 transacpt-sql 语言集数据定义语言,数据操纵语言,数据控制语言和附加语言为一体,其中附加语言元素不是标准的sql语言的内容,但是它增强了用户对数据库操作的灵活性和便捷性.
- 两种使用方式,统一的语法结构.即联机交互式,和嵌入高级语言使用方式
- 高度非过程化,transact-sql 语言一次处理一个记录,对数据提供自动导航,允许用户在高层的数据结构上工作,可操作记录集而不是对单个记录进行操作.所有的sql语句接受集合作为输入,返回集合作为输出,并允许一条sql语句的结果作为另一条sql语句的输入.
- 类似于人的思维习惯,容易理解和掌握.
接下来的是 transact-sql 附加语言,这些语言元素不是sql语言的标准内容,而是为了编程方便而增加的语言元素,包括,变量,运算符,函数,流程控制语句,注释等.

为了以下语句演示能够进行,先建立三个表作为参考:

CREATE DATABASE TEST;

USE TEST
----------------建------------表------------------------
CREATE TABLE student
 ( 
 sname CHAR (20)PRIMARY KEY ,
 age INT 
 )

CREATE TABLE course
 (
 cname CHAR(20) PRIMARY KEY ,
 gpa INT 
 )

CREATE TABLE sc
 (
 sname CHAR (20) FOREIGN KEY REFERENCES student(sname),
 cname CHAR(20) FOREIGN KEY REFERENCES course(cname),
 grade INT ,
PRIMARY KEY (sname,cname)
 )
----------------数------------据----------------------
INSERT INTO student VALUES('赵日天',19)
INSERT INTO student VALUES('龙傲天',20)
INSERT INTO student VALUES('叶良辰',18)
INSERT INTO student VALUES('花美景',17)
SELECT * FROM student 

INSERT INTO course VALUES('计算机导论',2)
INSERT INTO course VALUES('数字逻辑',3)
INSERT INTO course VALUES('数据结构',5)
INSERT INTO course VALUES('数据库',5)
INSERT INTO course VALUES('C语言',6)
SELECT * FROM course

INSERT INTO sc VALUES ('赵日天','计算机导论',85)
INSERT INTO sc VALUES ('赵日天','数字逻辑',84)
INSERT INTO sc VALUES ('叶良辰','计算机导论',88)
INSERT INTO sc VALUES ('叶良辰','数据结构',89)
INSERT INTO sc VALUES ('花美景','计算机导论',92)
INSERT INTO sc VALUES ('花美景','数据库',85)
INSERT INTO sc VALUES ('龙傲天','计算机导论',89)
INSERT INTO sc VALUES ('龙傲天','C语言',82)
SELECT * FROM sc
----------------------------------------------------

transact-sql 语言结构

变量

    --局部变量，声明局部变量，必选先声明再使用，且首字母必须为@
        DECLARE @parm INT 
SET @parm =1
 PRINT @parm

DECLARE @parm INT
SELECT @parm=age --如果是一个结果集，@parm为最后一组的值
FROM student --如果无返回值，则保持原来值
 PRINT @parm

 --全局变量是sqlserver预先声明并负责维护的变量，用户不能随意建立全局变量，也不能
 --用set语句修改全局变量值，全局变量的变量名以@@开头，

SELECT @@ROWCOUNT --上次命令处理的行数
SELECT @@ERROR --上次sql命令出现错误的信息行数

** IF ELSE ,BEGIN ,END语句

--IF ELSE 语句




/*
 IF<条件表达式>
 <命令行或程序块>
 ELSE
 <命令行或程序块>
*/

    IF 2>1
        PRINT '2>1'
    ELSE
        PRINT '2<=1'


--BEGIN END 语句 ，相当于 花括号

/*
BEGIN
 <命令行或程序块>
END
*/

    IF 2>0
BEGIN 
 PRINT 'YES'
 PRINT '2>0'
END
ELSE PRINT 'NO'

CASE语句

/*
形式一：
 CASE<运算式>
 WHEN<运算式>THEN<运算式>
 ...
 WHEN<运算式>THEN<运算式>
 [ELSE<运算式>]
 END
 */
/*
 形式二:
 CASE
 WHEN<条件表达式>THEN<运算式>
 ..
 WHEN<条件表达式>THEN<运算式>
 [ELSE<运算式>]
 END
 */

SELECT sname,
CASE age
WHEN 17 THEN '很小'
WHEN 18 THEN '小'
WHEN 19 THEN '中'
WHEN 20 THEN '大'
ELSE '不知'
END AS '年龄' --END '年龄' 也可以
FROM student

SELECT sname ,
CASE --'年龄'=CASE 也可以
WHEN age<17 THEN '特小'
WHEN age>=18 AND age<20 THEN '正常'
ELSE '不知' 
END '年龄'
FROM student

-- WHILE .CONTINUE. BREAK 语句

DECLARE @i INT
SET @i=1
 WHILE @i<=10
BEGIN
SET @i=@i+1
IF @i>5
 BREAK--跳过本次循环
ELSE
BEGIN
IF @i%2=0
 PRINT @i
ELSE
CONTINUE--跳过后面语句，回到WHILE循环最上面
END
END

* WHILE 语句*

/*
WHILE<条件表达式>
 BEGIN 
 <命令行或程序块>
 [BREAK]
 [CONTINUE]
 <命令行或程序块>
 END
*/

数据类型
略..

内置函数

聚集函数
略…

字符串函数

--ASCII函数

SELECT ASCII ('abc')--返回字符表达式最左端字符ACII代码值
SELECT CHAR(65) --返回ASCII代码抓换成的字符
SELECT LOWER('AB') --小写
SELECT UPPER('ab') --大写

SELECT STR(123) --数据转换为字符型
SELECT LTRIM(' he') --去头部空格
SELECT RTRIM('he ') --去尾部空格

SELECT LEFT('0123',2) --从左开始的两个字符的子串
SELECT RIGHT('0123',2) --从右开始的两个字符的子串
SELECT SUBSTRING('0123',1,2)--子串，字符串下标从1开始

SELECT CHARINDEX('ac','xac')--查找前面字符串在后面位置，没有返回0
SELECT CHARINDEX('ac','xac',1)--从位置开始查询
SELECT PATINDEX('%a%','dax')--带通配符的查找

SELECT REVERSE ('abc')--反转
SELECT REPLACE ('aac','a','A')--用第三个参数替换在第一个参数中出现的所有第二个参数
SELECT SPACE(5)--返回指定长度的空白字符串

日期和时间函数，日期应该在1753年到9999年之间

    SELECT YEAR('2015/10/23'),MONTH('2015/10/23'),DAY('2015/10/23')--年月日

SELECT DATEADD (dd,1,'2015/1/1')--添加一天
 --类似 年:yy 月:mm 天:dd 星期:ww 小时:hh 分钟:mi 秒:ss 毫秒:ms

SELECT DATEDIFF (dd,'2015/1/1','2015/3/1')
 --指定部分的时间间隔

SELECT DATEPART(dd,'2015/1/1')--返回指定日期指定部分
SELECT DATENAME (ww,'2015/1/1')--提取日期名。。。

SELECT GETDATE()--当前时间

存储过程

存储过程是一组编辑在一起的，存储在服务器上的，执行某种功能的预编译sql语句，它是一种封装重复任务操作的方法，支持用户提供的参数变量，具有强大的编程能力。

存储过程的好处：
- 加快程序的执行速度，由于存储过程在第一次被执行后，其执行规划就存储在高速缓存中，以后的操作，只要从高速缓存中调用编译好的存储过程的二进制代码即可执行，因此可以加快存储过程的执行，提高程序的性能。
- 减少网络的数据流量，不适用存储过程，如果有1000条的sal语句的命令将会让这些部分在网络传递过程中慢很多，而使用存储过程只需要一条执行过程代码的单独指令
- 提供安全机制，如果用户被授予了执行某个存储过程的权限，那么不管他有没有其中的表的权限，它都可以执行它
- 允许模块后程序设计

存储过程分类
- 系统存储过程系统存储过程定义在系统数据库maste 中,前缀为sp_
- 本地存储过程
- 临时存储过程带有前缀# 如果带有前缀## 则为全局临时存储过程临时存储过程只能在一个用户会话中使用
- 远程存储过程
- 扩展存储过程

建立存储过程考虑因素：
- 存储过程可以参考表，视图，临时表中的数据
- 如果存储过程中创建了临时表，那么临时表只能用户改存储过程，存储过程执行完毕，临时表自动删除’
- 在一个批处理张，CREATE PROCEDURE 语句不能与其他的SQL语句混用
- CREATE PROCEDURE 语句中不能有以下对象的创建语句： DEFAULT ,PROCEDURE,RULE,TRIGGER,VIEW

-输入参数，默认值
CREATE PROCEDURE prt(@name CHAR(20),@age INT =15 )
AS
BEGIN
 PRINT @name
 PRINT @age
END

--执行存储过程
EXECUTE prt 'D',2 --指定值
EXECUTE prt 'd' --省略，使用默认值
EXECUTE prt 'x',DEFAULT --指定使用默认值


--输出参数，返回值
CREATE PROC pabs(@num INT ,@return INT OUTPUT )
AS
IF @num>0
SET @return=@num
ELSE 
SET @return=@num*-1
 --通过 @return 返回 @num 的绝对值

DECLARE @answer INT 
EXECUTE pabs -5,@answer OUTPUT
SELECT @answer

--修改存储过程，在 CREATE 中使用的选项，必须也要在 ALTER 中
ALTER PROCEDURE pabs(@num INT ,@return INT OUTPUT)
AS
SET @return =ABS(@num)

DROP PROCEDURE pabs

--存储过程优化

--使用 WITH RECOMPILE 语句，每次调用存储过程，查询优化器会新建一个查询方案
--这样就保证了每次执行存储过程所使用的查询方案都是根据最新数据输入参数选定
CREATE PROCEDURE x(@num INT )
WITH RECOMPILE
AS
SELECT * FROM student
WHERE age>@num

--用户自定义数据类型
--使用系统存储过程 sp_addtype ，实际上只是对系统数据类型的另一种描述，
--并不能算新的数据类型
EXEC sp_addtype telephone ,'CHAR(20)','not null'
EXEC sp_addtype tele ,'CHAR(10)'

EXEC sp_droptype tele --删除数据类型

存储过程应用,级联删除

--删除学生信息，并且将相应的选课信息也删除
INSERT INTO student VALUES('东东',15)
INSERT INTO sc VALUES('东东','数据库',88)


CREATE PROCEDURE del_student (@name CHAR(20))
AS
DELETE sc
WHERE sname=@name

DELETE student
WHERE sname=@name

EXECUTE del_student '东东'

SELECT * FROM student
SELECT * FROM sc

触发器

在进行数据库的管理和维护的时候，如何保证数据库中的数据的完整性是一项重要的课题，为此，微软在sql srever中引入了触发器技术。触发器是一种实施复杂的完整性约束的特殊存储过程，在sql server进行某个特定表的修改时，右sql server自动执行。触发器一般用于加强数据库的某些使用规则。当普通的约束不足以加强数据的完整性时，就可以考虑使用触发器。

从技术角度上看，触发器本质上是一种特殊类型的存储过程，当 ISNERT ，UPDATE ，DELETE 命令对触发器保护的数据进行修改时，它能被系统自动激活，用于防止对数据进行不正确，未授权或不一致的修改，触发器不像一般的存储过程，不可以使用 EXECUTE 语句来调用或执行，当用户对指定的数据进行修改时，sql server将自动执行在相应触发器中的sql语句。
一般以下情况可以考虑使用触发器：
- 强制比CHECK约束复杂的数据完整性。
- 使用自定义的错误信息
- 实现数据库中多张表的级联修改
- 比较修改数据库修改前后的状态
- 维护非规范数据

创建触发器，只有表的所有者和数据库所有者可以创建触发器

/*CREATE TRIGGER trigger_name
 ON { TABLE | VIEW }
 {FOR | AFTER | INSTEAD OF } {INSERT | UPDATE | DELETE }
AS
 IF UPDATE (name)

IF UPDATE (name) 指定对某个字段进行插入或修改触发器才起作用
*/

如果在一段批处理命令中使用CREATE TIRGGER 命令，则该命令必须是这段批处理命令的第一条
在表中如果既有约束又有触发器，则在执行前，约束优先于触发器，如果在触发器中与约束发生冲突，则触发器将不执行
不允许在触发器中出现下列语句
- 任何数据对象创建语句 CREATE TABEL,DATABASE,INDEX…
- 任何数据对象删除语句 DROP DATABASE,TABLE,PROCEDURE..
- 所有对象修改语句 ALTE TABLE,DATABASE…
- TRIMCATE TABLE 语句
- SELECT INTO 语句
- 权限命令
- UPDATE STATISTICS 语句
- SELECT INTO 语句
- RECONFIGURE 语句
- LOAD,RESTORE DATABASE,LOG
- 所有的磁盘操作指令
  sql CREATE TRIGGER tr ON student FOR UPDATE AS PRINT 1 PRINT 2 DROP TRIGGER tr --删除触发器
  触发器在执行过程中，sql server 会根据触发器类型，创建临时表
INSERT 创建 inserted 表
UPDATE 创建 inserted , deleted 表
DELETE 创建 deleted 表

触发器应用

CREATE TRIGGER tri_st
ON student FOR UPDATE ,INSERT 
AS
DECLARE @age INT 
SELECT @age=MAX(age)
FROM inserted 

IF (@age>150)
BEGIN 
 PRINT '年龄太大'
ROLLBACK TRANSACTION 
END
ELSE
 PRINT '操作成功'

INSERT INTO student VALUES('a',1901)

DROP TRIGGER tri_st

--级联删除
CREATE TRIGGER tri_del_st
ON student INSTEAD OF DELETE
AS
DELETE sc
WHERE sc.sname IN (
SELECT sname FROM deleted )

DELETE student
WHERE sname IN(
SELECT sname FROM deleted)

DELETE student
WHERE sname='赵日天'

事务和锁

在sql server中，系统的并发性是通过事物和锁的机制来实现的，事物就是一个逻辑工作单元，其中包括一系列的操作。这些操作要么全部成功，要么全部失败，事务机制确保了多个数据的同步修改。如果在事务执行的过程中出现错误，那么服务器会取消事务做的所有修改，而将数据库自动恢复到执行该事务前的状态，这种自动恢复的机制称为回滚，一个事务的操作必须具备以下四个属性，即ACID：
- 原子性：事务必须为工作的最小单位，其所作的操作要么全部执行，要么全都不执行
- 一致性：事务完成后，所有的数据必须保持其合法性，即所有数据必须遵守数据库的约束和规则
- 隔离性：一个事务所作的修改必须和其他事务所作的修改隔离，一个事务所用的数据必须是另一个并发事务完成前或者完成后的数据，而不能是另一事务执行过程中的数据
- 持久性：事务执行完成后，对数据库的修改将永久保持。

显式事务
- BEGIN TRANSACTION :标识显式事务的起始点
- COMMIT TRANSACTION 或者 COMMIT WORK : 标识事务的结束，数据修改永久保存，事务占用资源将会释放
- ROLLBACK TRANSACTION : 回滚语句，回滚到事务开始前的状态

隐式事务
隐式事务是指当前事务提交后者回滚后，sql server自动启动的事务，所以在隐式事务中。不需要使用BEGIN TRANSACTION 用户可以执行以下的命令进入让 sql server 进入或者退出隐式事务状态
- SET IMPLICT TRANSACTION ON 使系统进入隐式事务模式
- SET IMPLICT TRANSACTION OFF 使系统退出隐式事务模式
当处于隐式事务模式，执行以下的任何命令多会启动一个隐私事务
- 所有的CREATE 语句
- 所有的DROP 语句
- ALTER DATABASE
- INSERT
- DELETE
- GRANT
- REVOKE
- SELECT
- UPDATE
- FETCH
- TRUNCATE TABLE

--启动，结束事务

BEGIN TRANSACTION [transaction_name ] 
COMMIT TRANSACTION [transaction_name ]
ROLLBACK TRANSACTION [transaction_name]


 --保存点，建立一个储存点，使用户回到某个储存点状态而不是简单的回滚整个事务

 SAVE TRANSACTION savepoint_name

 --事务编程举例，结合存储过程

DROP PROCEDURE delst
CREATE PROCEDURE delst(@name CHAR(20) )
AS
BEGIN TRANSACTION 
DELETE sc
WHERE sname=@name

DELETE student 
WHERE sname=@name 

IF @@ROWCOUNT>1
BEGIN 
 PRINT '不能删除多组数据'
ROLLBACK TRANSACTION 
END
ELSE
BEGIN
 PRINT '删除成功，事务提交'
COMMIT TRANSACTION 
END

秒客网