存储过程重新捡起

相隔半年多没有接触存储过程和触发器了，有点陌生，找点资料重新捡回起来http://hi.baidu.com/xmlf/blog/item/b247b251f80e908e8d5430ea.html

1、SQL批处理语句:可以一次执行多个语句
select au_lname from authors
select au_fname from authors
如果两个语句中有一个出错，则都不会执行。
你可以使用:
select au_lname from authors
go
select au_fname from authors
如果第二个语句错误，第一个照样执行。
2、变量:全局变量，局部变量
全局变量:全局变量的个数是有限的，你不能自己创建全局变量。@@identity和@@rowcount
@@identity记录最后一次插入identity列的值
insert authors(au_lname) values ('xmlf')
select @@identity /*返回标识列的值*/
第二个重要的全局变量是@@ROWCOUNT,@@ROWCOUNT变量记录最近一个语句执行时所影响到的列的数目。
如:
update authors set au_lname='xmlf' where au_fname='xmlf'
select @@rowcount
返回au_fname是xmlf的记录的数目
select * from authors where 1=2
select @@rowcount
返回0，因为不可能有1=2
局部变量:你可以对局部变量定义，修改，赋值操作。
不过首先你要先声明才能使用:
declare @myvar int
select @myvar=3
select @myvar
最后一个返回变量@myvar的值
declare @firstname varchar(20),@lastname varchar(20)
select @firstname="xxit"
select @lastname="xmlf"
select @firstname=@lastname
select @firstname
你也可以把执行sql语句的结果赋给局部变量，例如:
declare @query varchar(20)
select @query=au_lname from authors where password=111
select @query
假如一个查询没有返回值，该变量保留它原先的值。当变量第一次定义时，该变量的值为NULL
declare @query int
select @query=12
select @query=author_id from authors where 1=2
select @query
返回的值是12
3、在屏幕上显示数据:print
declare @myvar int
select @myvar=12
print @myvar
4、注释符 /* */ 这个没什么好说的，和C语言一样
5、条件控制语句 if语句
if (select count(*) from authors)>10 print "more than 10 authors"
如果表authors中的记录数大于10，则打印出more than 10 authors这个语句
if datename(mm,getdate())='July'
begin
print "this is July"
print "happy July"
end


if datename(mm,getdate())='July'
begin
print "this is July"
print "happy July"
if datename(dd,getdate())=4
begin
print "this is 4th"
print "happy 4th of July"
end
end

if datename(dw,getdate())='Friday'
print "this is Friday"
else
print "this is not Friday"

关键字exists判断是否返回了结果
如:
if exists(select au_lname from authors where au_lname="xmlf")
print "xmlf is an author"
else
print "xmlf is not an auhtor"

6、选择语句:case 语句
select
(case
when siteurl like "%edu" then "Educational"
when siteurl like "%gov" then "Gonvment"
when siteurl like "%com" then "Commercial"
else "Other"
end
) "type",sitename "name",siteurl "url" from site_dir


select
(case sitename
when "yahoo" then "Internet Directory"
when "Micorsoft" then "Software Giant"
else "Other"
end
) "Type",siteurl "url" from site_dir

7、使用return语句返回结果退出批处理
如:
if datename(dw,getdate())='Sunday'
begin
print "this is Sunday"
return
end
print "this is other day"
分析:如果上面语句不加return语句，则每天(包括Sunday)都会执行最后一个语句,而加了一个return语句后，在Sunday那天，遇到return语句后，就退出批处理而返回结果了，不会执行最后一个语句.
8、把语句组加入到事务组中:意思是这组语句要么全部执行，要么全部不执行.
例如:
begin transaction
insert user(username,password) values ('xmlf',123456)
INSERT orders(username) VALUES (‘Andrew Jones’)
commit transaction
这组语句只有当执行完commit transaction后才起作用。
begin transaction
insert user(username,password) values ('xmlf',123456)
INSERT orders(username) VALUES (‘Andrew Jones’)
if datename(dw,getdate())='Friday'
rollback transaction
else
commit transaction
上面语句只有在不是星期五执行，如果是星期五则不会执行。

9、存储过程:把一组复杂的SQL语句存储在存储过程中。
存储过程相对于批处理优点：
处理速度快，因为批处理每次执行都要首先进行编译；而存储过程只需要在第一次执行的时候进行编译。可以非常灵活控制存储过程，可以对存储过程输入输出值。当使用批处理的时候你需要传递每一个语句，而使用存储过程，你只需要传递一个简单的语句；而且你还可以在一个存储过程中包含另外一个存储过程。这样可以创建很复杂的存储过程。
有了以上优点，你没有理由不使用存储过程。
创建存储过程:
create procedure retrieve_authors as select * from authors
这样就创建了一个存储过程，存储过程名retrieve_authors 存储过程中的SQL语句是AS后面的SQL语句，当执行这个存储过程，返回的是authors表中的所有记录。
执行一个存储过程:execute retrieve_authors
如果你想在B数据库中调用A数据库中的存储过程，可以使用:execute a..retrieve_authors
你还可以使用sp_helptext来查看存储过程中的SQL语句，例如:sp_helptext retrieve_authors
将显示:
select * from authors
有趣的是sp_helptext本身就是个系统存储过程，你可以使用sp_helptext sp_helptext来查看sp_helptext内容.
一旦你创建了个存储过程，你不能修改它，如果要修改它必须先破坏它，然后重建。
你可以使用Drop procedure 存储过程名 来删除存储过程。
例如:drop procedure retrieve_authors
你可以使用sp_help 来查看数据库中的所有存储过程列表，sp_help 存储过程名 查看指定的存储过程的信息。

给存储过程传值:
create procedure check (@firstname varchar(30),@lastname varchar(30))
as
if exists(select name from authors where name=@firstname or name=@lastname)
print "he is an auhtors"
else
print "he is not an authors"

当执行这个存储过程是赋值:
execute check @firstname="xmlf",@lastname="xxit"
这样就可以检测出xmlf或xxit中是否至少有一位是authors.

从存储过程中获得值:
create procedure check_philosophers (@philosophers varchar(30),@conculsion varchar(30) output)
as
if exists(select name from authors where name=@philosophers)
select @conculsion="a philosopher"
else
select @conculsion="not a philosopher"

执行这个存储过程之前，你必须先定义一个变量来接受变量@conclusion的值。
declare @proc_results varchar(30)
execute check_philosophers @philosophers="xmlf",@conculsion=@proc_results output
print @proc_results

注意在该EXECUTE语句中，参数的名称总是列在前面。你要使用@conclusion=@proc_results来接收参数@conclusion的值，而不是你可能期望的@proc_results=@conclusion。

在存储过程中使用Return语句:
create procedure check_table (@who varchar(30))
as
if exists(select name from teachers where name=@who)
begin
print "in the teachers table"
return
end
if exists(select name from authors where name=@who)
begin
print "in the authors table"
return
end
print "not in any table"
return

执行这个存储过程。
execute check_table @who="xmlf"

你还可以使用return语句返回状态值，例如:
create procedure check_table (@who varchar(30))
as
if exists(select name from teachers where name=@who)
begin
return(1)
end
if exits(select name from authors where name=@who)
begin
return(2)
end
return(3)
这样你就可以在ASP中通过这些状态值来判断所给的值存在在哪个表了。
执行这个存储过程并显示出返回的状态值:
declare @myvar int
execute @myvar=check_table @who="xmlf" /*将存储过程的返回值赋给了定义的变量@myvar*/
select @myvar /*显示出返回值*/

使用触发器：
建立触发器:create trigger user_insert on user for insert as execute master..xp_sendmail "administrator" "New user registered!"

create trigger 触发器名 on 表名 for 触发的动作 as 触发后执行的语句
触发的动作你也使用:insert,update,delete
在本例中，当有新的数据插入到表user中，会触发执行发送email信息.
记住:触发器和表是相关联的，当表被删除了，触发器也相应被删除。在一个表上，你只可以为每个动作建立一个触发器，不允许在多个触发器上使用相同的动作。
为一个表最多可以创建三个触发器，分别是insert,update,delete。
当你添加具有相同动作的第二个触发器时，会在没有任何通知的情况下删除第一个触发器。

你同样可以使用sp_help查看数据库中的所有存储过程，触发器和表。如果只想显示触发器的信息，使用sp_help 触发器名

创建与多个动作关联的触发器：
create trigger username_trigger on users for insert,delete,update as 所要执行的SQL语句

触发器只有在其FOR短语后面指定的动作发生时执行。你不能直接执行一个触发器。

删除触发器:drop trigger 触发器名

两个特殊的表:inserted和deleted
这两个表仅仅在触发器被执行的时候存在。
create trigger user_trigger on user for delete as insert user_log(activity) select username from deleted
假使你不小心执行delete user,这样会删除表user中的所有记录。这样这些记录会永久的丢失，可是上面的触发器会在记录被删除时触发，自动将被删除的username表中的记录拷贝到user_log表中。
Deleted表和有记录被删除的表的列结构一模一样。在前面的例子内，Deleted表具有和webusers表相同的结构。

同样，当在表user中插入一条新记录时并做备份，你可以使用:
create trigger user_trigger on user for insert as insert user_log(activity) select username from inserted
当表user中有新纪录插入时，会自动执行触发器user_trigger，并将新记录从inserted拷贝到user_log中。

你同样可以使用inserted和deleted来记录update对触发器所在的表的动作。当一个和触发器相关联的表中数据被修改时，deleted包含被修改前的值，inserted包含了被修改后的值。
记住:inserted和deleted表仅仅在触发器运行时存在，当触发器运行结束，这两个表中的记录也不复存在。

触发器和事务的结合:
create trigger user_trigger on users for insert,update,delete as
if datename(dw,getdate())="Sunday"
rollback transaction
该触发器阻止任何人在Sunday插入，修改或删除users表中的记录。

create trigger user_trigger on users for insert,update,delete as
if exists(select username from inserted where username="abc")
rollback transaction

该触发器阻止用户名为abc的人注册。当用户名为abc包含在某个insert,update,delete语句中时，就会被rollback回滚回去，阻止执行。

训练题目：

http://www.cnblogs.com/tenghoo/archive/2007/06/11/779240.html