存储过程和触发器

存储过程和触发器

存储过程是存储在服务器上的一组预编译的SQL语句（二进制代码）。它是一种封装重复任务操作的方法，支持用户提供的变量，具有强大的编程功能。存储过程非常类似DOS系统中的BAT文件。从性能上讲由于存储过程由于是在服务器上运行的，而服务器通常功能强大，所以存储过程的执行时间要比在工作站中执行的时间要短得多。
存储过程的优点：
a、提供了一个种安全机制，如果用户被授予执行存储过程的权限，那么即使该用户没有访问该存储过程所参考的表或者视图的权限，也可以执行存储过程。因此可以创建存储过程来完成所有的增加、删除等操作，并且可以通过编程的方式控制上面操作中对信息的访问，我们稍后在VB或JAVA中演示。
b、由于存储过程在第一次执行之后，其执行规划就驻守在高速缓冲存储器中，所以在以后的操作中，只需要从高速缓冲存储器中调用存储过程已编译好的二进制代码来执行，从而加速了存储过程的执行，提高了系统的性能。
c、减少网络传输所需的时间。这是使用存储过程的一条非常重要的原因。如果有一千条SQL语句，一条一条地通过网络在客户机和服务器之间传送，那么这种传输所耗费的时间是非常长的，即使是世界上最有耐心的人也无法忍受。但是，如果把这一千多条SQL语句的命令写成一条较为复杂的存储过程命令，在客户机和服务器之间进行的网络传输所需的时间就会大大减少。
在SQL Server中有5种类型的存储过程：系统的存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。

1、系统存储过程是有系统提供的存储过程，用来作为命令执行各种操作。它们存储于master数据库中，其前缀为sp_或xp_。系统的存储过程可以在任意一个数据库中执行。可以通过帮助找到它们各自的作用。
举一个例子。如sp_dropserver删除远程或者链接服务器列表中的一个服务器。

1、本地存储过程是指在每个用户自己的数据库中创建的存储过程。这种存储过程主要在应用程序中使用。

2、临时存储过程属于本地存储过程，如果本地存储过程的名称前面有一个‘#’，该存储过程就称为临时存储过程，这种存储过程只能在一个用户会话（一个用户连接，也就是一个查询分析器中）中使用；如果本地存储过程的名称前面有‘##’，该存储过程就称为全局临时存储过程，这种存储过程可以在所有用户会话中使用。
局部临时过程只能由创建该过程的连接使用。全局临时过程则可由所有连接使用。局部临时过程在当前会话结束时自动除去。只要创建者不被关闭，全局临时过程就存在，否则全局存储过程消失。
任何连接都可执行全局临时存储过程。只有创建该过程的用户所用的连接关闭，并且所有其它连接所用的该过程的当前执行版本运行完毕后，全局临时存储过程才不再存在。一旦用于创建该过程的连接关闭，将不再允许启动执行该全局临时存储过程。只允许那些已启动执行该存储过程的连接完成该过程的运行（没作完的作完，作完了就不能再用了）。

临时存储过程总是会自动的创建到tempdb数据库中，但要在所要访问的数据库里写临时存储过程，在任何数据库中都能访问tempdb里的临时存储过程。临时存储过程不能手工的删除（代码和菜单操作都不行）
局部临时过程：
use mybole
go
create procedure #myproc
as
select * from gst
go
--这里一定要加go，否则下面的语句exec #myproc也被编译成了一个存储过程了
exec #myproc

全局临时过程--全局临时过程会存在于所有连接的tempdb中，可以看到
use mybole
go
create procedure ##myproc
as
select * from gst
go
exec ##myproc

 

3、远程的存储过程是指从远程服务器上调用的存储过程，或者是从连接到另外一个服务器的客户机上调用的存储过程。远程存储过程就是非本地服务器上的存储过程

4、扩展存储过程：
在SQL Server环境外执行的动态链接库DLL称为扩展存储过程，其前缀是xp_。虽然这些动态链接库在SQL Server环境外，但是它们可以加载到SQL Server系统中，并且按照使用存储过程的方式执行。
扩展存储过程举例--执行操作系统的命令
exec xp_cmdshell 'mkdir hi' --建在了system32里

exec xp_cmdshell 'dir d:\*.exe'

DECLARE @cmd sysname, @var sysname
SET @var = 'dir /p'
SET @cmd = 'echo ' + @var + ' > dir_out.txt'
EXEC master..xp_cmdshell @cmd

exec xp_cmdshell "net send 127.0.0.1 hello"

5、存储过程的执行过程包括创建存储过程和执行存储过程。当第一次执行存储过程时，存储过程的执行规划放在过程高速缓冲存储区，过程高速缓冲存储区是一块内存缓冲区，SQL Server用这块缓冲区来存储已经编译的查询规划以便执行存储过程。
当存储过程创建之后，系统检查其中的语句是否正确。检查语法之后，系统将存储过程的名字存储在当前数据库的系统表sysobjects中，将存储过程的文本存储在当前数据库的系统表syscomment中。
存储过程的执行过程：
语法分析
|
优化
|
编译
|
执行
第一次执行存储过程之后，再次执行存储过程时其速度快于第一次的执行速度，这是系统使用了过程高速缓冲存储区中已经优化的查询规则。

6、创建存储过程有三种方法：
a、在查询分析器里使用Create Procedure语句
b、使用存储过程向导
c、直接在SQL Server的库的存储过程里新建存储过程

这里讲解主要是用Create Procedure创建存储过程

exec sp_stored_procedures返回当前数据库中存储过程清单
exec sp_help显示参数清单及其数据类型
exec sp_helptext myproc显示存储过程的定义文本

加密的存储过程，只能执行，不能查看存储过程的文本，知识产权保护。
create procedure my1 with encryption
as
select * from gst where gst_id=1

7、执行存储过程
只有具有存储过程的执行权限EXECUTE的许可，才可以执行存储过程。执行存储过程有两种方法：直接执行存储过程（EXECUTE）；在INSERT语句中执行存储过程，也就是把存储过程中的SELECT语句返回的数据加载到表中。

create proc my2
as
select * from infobak
go
exec my2
go
insert into infobak exec my2--infobak表不能有标识字段，因为标识字段是不允许编辑的。

8、带参数的存储过程：支持输入和输出的参数
@parameter date_type[=default]
存储过程的参数信息存放在系统表syscolumns中

使用输入参数
if object_id('my3') is not null
 drop proc my3
go
create proc my3
(
 @id integer=null,
 @username varchar(8)='aa'
)
as
if @id is null
begin
 print 'please input id'
 return--有return则不执行下面的select语句，反之执行。
end

select * from info where id=@id and username=@username

sql语句对大小写不敏感
如果想将某个参数省略就必须填为null,如exec my3 1,null,'aa'
如果指定参数时的参数顺序要与存储过程定义中的参数顺序不同，那么参数必须使用参数名称传递

根据参数名称来指定输入参数
exec my3 @id=2,@username='11'
根据参数位置来指定输入参数
exec my3 1,'aa'

使用输出参数返回值，注意输出参数的类型可以是除text和image类型之外的任意数据类型
例一：有乘法功能的存储过程
if object_id('my4') is not null
 drop proc my4
go
create proc my4
(
 @m1 smallint,
 @m2 smallint,
 @result smallint output
)
as
set @result=@m1*@m2

declare @answer smallint
exec my4 12,22,@answer output
print 'this result is:'+str(@answer)

例二：希望在插入数据后就能得到该数据的自增长的ID号
if object_id('my5') is not null
 drop proc my5
go
create proc my5
(
 @id int output,
 @username varchar(50),--千万要为该类型指定大小，否则默认是1个字节
 @gender varchar(50),
 @password varchar(50),
 @address varchar(50)
)
as
select @id=max(id) from info
if @id=null
 set @id=1
else
 set @id=@id+1
insert into info(username,gender,password,address) values(@username,@gender,@password,@address)

declare @iid int--定义为@id也可以
exec my5 @iid output ,'ab','cd','ef','gh'
print @iid

select * from info

9、修改存储过程
if object_id('my6') is not null
 drop proc my6
go
create proc my6
as
begin
select * from info
end
select * from info
注意这里的begin和end并不是存储过程命令的开始和结束，最下面的语句还属于存储过程的命令中，结果是返回两个记录集。begin和end用于a、WHILE 循环需要包含语句块。b、CASE 函数的元素需要包含语句块。c、IF 或 ELSE 子句需要包含语句块。像一对大括号的作用。所以上面的存储过程用Begin和End是没有任何意义的

alter procedure my6
as
select * from info where id=6

注意在create procedure语句中使用的选项（SQL Server的一些选项）也必须在ALTER PROCEDURE语句中使用。

SQL的注释：
单行注释  --
多行注释 /**/，注意多行注释不能跨一个批处理
错误的注释
/*if object_id('my6') is not null
 drop proc my6
go
create proc my6
as
select * from info*/

正确的注释，整个注释必须包含在一个批处理内
/*if object_id('my6') is not null
 drop proc my6
go*/
/*create proc my6
as
select * from info*/

触发器带有一触即发的意思，也就是只要满足一定的条件，它就可以被触发从而完成各种简单或复杂的任务。
触发器是一种特殊类型的存储过程。当有操作影响到触发器保护的数据时，触发器就自动触发执行。触发器是与表紧密联系在一起的，只要提及某个具体的触发器就是指某个表的触发器。当使用下面的一种或多种数据修改操作在指定表中对数据进行修改时，触发器就会自动触发执行：UPDATE、INSERT 或 DELETE。触发器是在操作有效之后执行，所以在修改中它们代表“最后的动作”。假如触发器不允许用户的某一操作，则SQL Server系统将拒绝更新数据，并且给那些处理--事务--的应用程序返回一个错误信息。
触发器与一般的存储过程的区别是：后者可以由用户直接调用执行，前者不能直接调用执行。
触发器与表是密不可分的，触发器不能离开表而存在，并且触发器能保护表中的数据。一般情况下，对表中数据的操作有3种基本类型，即插入、修改、删除。因此触发器也有3种类型，即INSERT、UPDATE、DELETE。当在某个表中插入数据时，如果该表有INSERT类型的触发器，那么该INSERT触发器就触发执行。虽然触发器只有3中类型，但一个表可以有许多个触发器，如一个表可以有5个INSERT类型的触发器。
触发器是在操作之后执行，而约束是在操作发生之前起作用。
就像用Create procedure创建存储过程一样，我们用Create trigger创建触发器
语法如下：
create trigger [owner_name].trigger_name
on [owner_name].table_name
for (insert,update,delete)
as
 trigger code

使用带有提醒消息的触发器
if object_id('info_instri1') is not null
 drop trigger info_instri1
go
create trigger info_instri1 on info for insert
as
   print '插入成功'

insert into info(username,gender,password,address) values('张三','男','123','456')

使用防止插入的触发器
if object_id('info_instri2') is not null
 drop trigger info_instri2
go
create trigger info_instri2 on info for insert
as
   if exists(select * from info where password='123')
   begin
 rollback transaction--这里用return没用，因为return只是返回了事务，而没有撤回前面的插入语句
 print '由于密码是123已经有人设置，所以本次插入数据失败'
   end

insert into info(username,gender,password,address) values('张三','男','123','456')

这时有两个插入的触发器，所以在插入数据的时候这两个触发器都会执行。

使用带有提醒电子邮件的触发器
当 titles 表更改时，下例将电子邮件发送给指定的人员 (MaryM)。

USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'reminder' AND type = 'TR')
   DROP TRIGGER reminder
GO
CREATE TRIGGER reminder
ON titles
FOR INSERT, UPDATE, DELETE
AS
   EXEC master..xp_sendmail 'MaryM',
      'Don''t forget to print a report for the distributors.'
GO

修改触发器
alter trigger info_instri1 on info for insert
as
   if exists(select * from info where password='121')
   begin
 rollback transaction
 print '由于密码是123已经有人设置，所以本次插入数据失败'
   end
就是将原来的Create改为alter

全局变量：我们在前面使用DECLARE语句创建变量（局部变量），SELECT更改变量，在表达式里原来放置文字值或者引用列的地方替换上变量。有一个特殊的变量集由数据库服务器自己维护，这些变量统称为全局变量。某些情况时可以设置这些变量，但不能创建它们。全局变量存储的值对所有用户都是可用的，它们不同于普通变量，因为它们名称前面是两个@。注意：用户可以创建以两个@@符号开始的变量，但不能使其成为全局变量，而只是用令人混淆的方式命名的局部变量。如：@@rowcount、@@version
例print @@version
在全局变量之间，有些是各个连接具有的特定的值，有些是每个连接都含有的相同的值。例如@@error含有当前用户执行的最近语句的返回码

if object_id('info_instri2') is not null
 drop trigger info_instri2
go
create trigger info_instri2 on info for insert
as
 select * from info--触发器最好不要返回结果集
 print '目前有'+str(@@rowcount)+'行'  

 

 

附info、infobak表的源码

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[info]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[info]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[infobak]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[infobak]
GO

CREATE TABLE [dbo].[info] (
 [id] [int] IDENTITY (1, 1) NOT NULL ,
 [username] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [gender] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [password] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[infobak] (
 [username] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [gender] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [password] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
 [address] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

注意还有一些网站服务商如中国万网使用的还是SQL Server7.0，所以如果在编写脚本的时候用2000直接编写的脚本在7.0运行会出错，要在设置格式里指定和7.0兼容。

遗留问题：
case语句
求每个季度的每一门课程的咨询人数
select TransourceCode,
       sum(case when datepart(mm,ZXDate) in (1,2,3) then 1 else 0 end)as 第一季度,
       sum(case when datepart(mm,ZXDate) in (4,5,6) then 1 else 0 end)as 第二季度,
       sum(case when datepart(mm,ZXDate) in (7,8,9) then 1 else 0 end)as 第三季度,
       sum(case when datepart(mm,ZXDate) in (10,11,12) then 1 else 0 end)as 第四季度
from students_transubject
group by TransourceCode

下面示例在查询结果集内显示每个作者所居住州的全名：
use pubs
SELECT au_fname, au_lname,
   CASE state
      WHEN 'CA' THEN 'California'
      WHEN 'KS' THEN 'Kansas'
      WHEN 'TN' THEN 'Tennessee'
      WHEN 'OR' THEN 'Oregon'
      WHEN 'MI' THEN 'Michigan'
      WHEN 'IN' THEN 'Indiana'
      WHEN 'MD' THEN 'Maryland'
      WHEN 'UT' THEN 'Utah'
        END AS StateName
FROM pubs.dbo.authors
ORDER BY au_lname

case语句类似标准的IF语句。计算条件列表并返回多个可能结果表达式之一。

while语句
declare @i int
select @i=0
while @i<5
begin
 
 if @i=3
 begin
  break;
  --select @i=@i+1
  --continue--跳出本次循环，执行下一次循环。
 end
 print @i
 select @i=@i+1
end

使用变量、设置变量
declare @username varchar(25)
select @username='aa'--用select语句设置变量。
select @username=username from info where id=25
print @username+convert(varchar(100),@@rowcount)--print语句只能打印类型是char或varchar的值。也不提供隐式类型转换，这就需要我们手工转换了。

事务:根据系统的设置，可以把事务分为两种类型：系统提供的事务和用户定义的事务。系统提供的事务是指在执行某些语句时，一条语句就是一个事务。在SQL Server里所有的SQL语句就是一个事务如
update titles set price=price*100,要么将titles表中的所有行都修改，要么一行都不修改
另外
update account set price=price+100 where id=3
update account set price=price-100 where id=2
这两条语句也包含了事务，全部执行或者一条都不执行
事务处理语句有三种：
Begin Transaction 开始一个工作单元
Commit Transaction 完成一个工作单元
Roolback Transaction 回滚一个工作单元

用户定义的事务
begin transaction
update info set username='张学有' where id=6
insert into info(username,password,gender,address) values('张满','111','222','333')
commit transaction

begin transaction
update info set username='张学' where id=6
insert into info(username,password,gender,address) values('张','111','222','333')
rollback transaction
查询分析器本身就内置了事务，在别的数据库客户端工具里可能就不会内置事务，就需要我们手工定义事务了
/*隐藏了begin transaction*/
update account set price=price+100 where id=3
update account set price=price-100 where id=2
/*隐藏了commit transaction*/

begin tran
save tran i1
update info set username1='xxx' where username='a'
save tran i2
update info set username='yyy' where username='e'
save tran i3
update info set username='zzz' where username='i'
if(@@error<>0)
begin
   Raiserror 5001 'have a error founded!'//这个在查询分析器里不起作用，是因为查询分析器已为你做了错误处理了
   Rollback tran i2--i2上面的没有被撤消，而是它下面的。
end
else
   Commit Tran

最好不要在事务里嵌套事务，会占大量的系统资源。

 

自定义函数的使用
系统内置函数如rtrim(),count()
if Object_ID('stuFun')is not null
   Drop Function stuFun
go
Create function stuFun(@username  varchar(10)) returns varchar(10)
begin
  Declare @password char(6)
  select top 1 @password=password from info where username like RTrim(@username)+'%'
  return @password
end

select * from info

declare @password varchar(10)
set @password=dbo.stuFun('张')
print @password

返回类型为一个表
if Object_ID('stuFun1')is not null
   Drop Function stuFun1
go
Create function stuFun1(@username  varchar(10)) returns table
as--不能有上例这里的两行语句了
  return(select password from info where username like RTrim(@username)+'%')

select * from dbo.stuFun1('张')

游标
执行Select语句或其他的修改记录的语句常常只能对一个整体进行处理，而不能对其中的部分进行单独的处理，如果想单独对某一条记录进行处理就要写各条记录对应SQL语句去修改相应的记录。而游标正是这样一种能够对结果集中部分进行处理的方法。
可以把游标看成一种指针，用于指向结果集中的任意位置。
SQL SERVER2000提供两种游标应用接口方法：
Transact SQL游标：由SQL SERVER服务器实现的游标，游标的控制和管理操作通过脚本程序、存储过程、或者触发器将Transact SQL语句传送给服务器实现。这是我们这里所要介绍的游标
API（数据库应用程序编程接口）游标：通过调用API游标函数，经ADO、OLEDB、ODBC实现游标功能。也就是我们在程序中所控制的游标
操作游标有5步：
a、用DECLARE语句创建游标，并定义游标类型和属性
b、调用OPEN语句打开和填充游标
c、执行FETCH语句在结果集中提取若干行数据，Transact SQL游标不支持多行读取操作
d、根据需要，使用UPDATE或DELETE语句在游标当前位置上进行操作
e、用CLOSE和DEALLOCATED语句关闭释放游标，用前者，虽然你不能再处理游标里的行了，但游标仍然为声明状态，而后者不仅关闭游标，而且还取消游标的声明

API的游标有四种类型：单进游标、静态游标、动态游标和键集驱动游标，这四个游标都是存在于客户端的
详情见SQL SERVER帮助（游标：动态等）

打开游标时游标位于结果集的首行
注意如果修改了本次连接的游标，它不会起作用，因为没有关闭它，游标存活于一个连接中，所以可以新建查询窗口

use mybole
DECLARE info_cursor CURSOR
   FOR SELECT * FROM info
OPEN info_cursor
FETCH NEXT FROM info_cursor
update info set password='1' where current of info_cursor--用游标更新
delete from info  where current of info_cursor--用游标删除
while(@@fetch_status=0)
 --FETCH info_cursor
 FETCH NEXT FROM info_cursor 
--close info_cursor
deallocate info_cursor