SQL SERVER存储过程和触发器

存储过程

1、存储过程：

     独立存在于表之外的数据库对象，主要用于控制访问权限， 为数据库表中的活动创建审计追踪、将关系到数据库机器所有相关应用程序的数据定义语句和数据操作语句分隔开。

2、存储过程的优点

1. 提高了处理复杂任务的能力
2. 增强了代码的复用率和共享性
3. 减少了网络中数据的流量。一个需要数百行Transact-SQL代码的操作可以通过一条执行过程代码语句来执行，而不需要在网络中发送数百行代码。
4. 存储过程挂在服务器注册，加快了过程的运行速度。
5. 加强了系统的安全性

   3、用户定义的存储过程：存储过程是指封装了可重用代码的模块或历程。
   注意事项：
   （1）储存过程最大不超过128M
   （2）用户定义的存储过程，只能在当前数据库创建

   （3）存储过程可以嵌套

  4、 常见的存储过程：

   sp_helpdb:用于查看数据库名称及大小
   sp_helptext:用于显示规则、默认符、未加密的存储过程、用户定义函数、触发器或视图的文本。
   sp_renamedb:用于重命名数据库
   sp_rename:用于更改当前数据库中用户创建的对象（如表）
   sp_helplogins:查看所有数据库用户登录信息
   sp_helpsvrolemember:用于以查看所有数据库用户所属的角色信息

eg.创建存储过程  

  create proc sel/*创建存储过程名*/
  @rownum int ,/*@rownum作为输入参数*/
  @num int   output/*output表明参数是返回参数*/
  as   
 select @num=num   from
 (select * ,row_number() over (order by tim)as rownum
 from  [stock].[dbo].[tab_20150929SH600028]) as t

 where rownum=@rownum

 declare @nu int
 exec sel  @rownum=1,@num=@nu output /*执行存储过程*/
 select @nu

 drop proc sel /*删除存储过程*/

使用jdbc中callableStatement调用存储过程。

触发器

1、触发器是一种响应数据操作语言事件或数据定义语言而执行的特殊类型的存储过程，

是在用户对某一表中的数据进行UPDATE\INSERT\DELETE操作时被触发执行的一段程序。

2、作用：

级联操作、实现比Check约束更为复杂的限制、强制表的修改要合乎业务规则

createtrigger语句必须是批处理中的第一句，而且只能用于一个表或视图。

/*创建触发器*/

create trigger update_tim
on  tab_20150929SH600276
after update  /*after指定触发器只有在触发sql语句中指定的所有操作都执行后才激发*/
as
if update(tim)
begin
raiserror('不能修改时间',16,2)---用来抛出一个错误
rollback
end
update tab_20150929SH600276 set tim ='09:25:06'where tim='09:25:03'

create trigger delete_tim
on  tab_20150929SH600276
instead of delete /*instead of指定执行触发器而不是执行触发SQL 语句，从而替代触发语句中的操作*/
as
if exists(select * from tab_20150929SH600276 where tim='09:25:03')
begin
raiserror('不能删除',16,2)
rollback
end

delete from tab_20150929SH600276 where tim='09:25:03'

删除触发器

drop trigger delete_tim

禁用触发器

create table 表明

disable trigger delete_tim

启用触发器

alter table 表名

enable trigger delete_tim

ps：

1、批量删除以test为开头的表
   declare @name varchar(20)
   while(exists(select * from sysobjects where name like 'test%'))
   begin
   select @name=name from sysobjects where name like 'test%'
   exec ('drop table '+@name)
   end

2、查询数据库中的所有表
   select * from sysobjects where xtype='U'

3、游标
  当用户需要访问一个结果集中的某条记录的时候，就需要使用游标功能。
  cursor 表示游标
  使用关键字global声明全局游标：可以在任何位置上访问
  使用关键字local声明局部有标：只能在声明和创建的函数或者存储过程中对他进行访问

使用游标的步骤：
  (1)声明游标：declare cursor
  (2)打开游标:open,打开一个游标意味着在游标中输入了相关的记录信息
  (3)获取记录值fetch into：如果需要获取某一条记录的信息，还需要使用FETCH语句
  来获取该记录的值，一条FETCH语句会执行两步操作：首先将游标当前指向的记录保存到一个局部变量中，
  然后游标将自动移向下一条记录。
  (4)关闭游标：close
  (5)释放游标：deallocate
*游标就是指向内存中结果集的指针，可以实现对内存中的结果集进行各项操作。


 /****用游标遍历表，不建议使用****/
  declare @tim time(0), @price smallmoney,@buy int, @num int
  declare my_cursor scroll cursor for
  select * from [stock].[dbo].[tab_20150929SH600000];

  open my_cursor;

  fetch my_cursor into @tim,@price,@buy,@num;
  while(@@fetch_status=0)
  begin
  FETCH next from my_cursor into @tim,@price,@buy,@num;
  print @tim;print @price;print @buy;print @num ;
  end
  close my_cursor
  deallocate my_cursor

4、查寻第二行的值(tim为列名，tabName为表名)
  select * from
  (select * ,row_number() over(order  by tim)as rownum
   from tabName)
   as t
   where rownum=2

5、计算两个时间之间的差值
   select datediff(MINUTE ,'09:25:02','09:37:32')