Microsoft SQL Server 存储过程

---

TRIGGER

DDL触发器：主要用于防止对数据库架构、视图、表、存储过程等进行的某些修改；DDL事件是指对数据库CREATE,ALTER,DROP审核及规范数据库操作，创建用来监视并响应该数据库或服务器实例中的活动的事件通知时，可以指定相应事件类型或事件组，数据库（DataBase）级别和服务器（Server）级别；

DML触发器：DML事件是指对表或视图的INSERT,UPDATE,DELETE更新级联操作等；

查看触发器

--查看服务器级别DDL触发器及事件： select * from sys.server_triggers select * from sys.server_trigger_events --查看数据库级别DDL触发器及事件 SELECT * FROM sys.triggers SELECT * FROM sys.trigger_events --查看数据库中DML类型触发器信息 select * from sysobjects where xtype='TR' SELECT * FROM sys.all_objects WHERE type='TR' --查看触发器一般信息 EXECUTE SP_HELP TRI_NAME --查看触发器代码信息 EXECUTE SP_HELPTEXT TRI_NAME

创建触发器

--创建服务器级别的DDL触发器 if exists(select * from sys.server_triggers where name='tri_ddl_db') --删除服务器级别的触发器 drop trigger tri_ddl_db on all server go create trigger tri_ddl_db on all server for create_database,drop_database,alter_database as declare @EventData xml set @EventData = EVENTDATA(); -- 当触发器执行时，该函数会返回触发器的事件信息。 select EVENTDATA(); select '触发器tri_ddl_db禁止创建、修改、删除数据库操作' select @EventData.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)') as EventType, --事件类型 @EventData.value('(/EVENT_INSTANCE/PostTime)[1]','nvarchar(max)') as PostTime, --事件触发的时间 @EventData.value('(/EVENT_INSTANCE/ServerName)[1]','nvarchar(max)') as ServerName, --计算机名 @EventData.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(max)') as DatabaseName, --数据库名 @EventData.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)') as CommandText --操作命令文本 rollback go --判断数据库级别的触发器是否存在 IF EXISTS(SELECT * FROM sys.triggers WHERE parent_class = 0 AND name='tri_ddl_tab') --删除数据库级别的触发器 DROP TRIGGER tri_ddl_tab ON DATABASE GO --创建DDL触发器 CREATE TRIGGER tri_ddl_tab ON DATABASE --指定触发器事件 FOR create_table,alter_table,drop_table AS print 'DDL触发器已禁止对表进行操作' --对操作进行回滚 rollback GO --创建触发器禁止更改表内容 CREATE TRIGGER TRI_DATABASENAME_TABLENAME ON [DATABASENAME].[dbo].[TABLENAME] FOR INSERT,UPDATE,DELETE AS rollback print'更新数据不成功!' GO --触发器创建时系统自动创建Inserted和Delete2个表放在内存中触发器执行完成后表也会同时删除。 --Insert表：存放由执行insert或update语句要向表中插入的所有行 --Delete表：存放由执行delete或update语句要向表中删除的所有行 CREATE TRIGGER tri_test_t1 ON [dbo].[t1] for insert,delete,update AS begin --插入及更新数据 SELECT * into aa FROM inserted --更新及删除数据 SELECT * into bb from deleted end Go   --raiserror函数 CREATE TRIGGER TRI_NAME ON TABLE_NAME FOR UPDATE AS if update(id) begin raiserror('不能修改！',10,1) rollback end GO --利用instead of 替代触发器实现工资的自动计算。 create table salary_table ( id int identity primary key, salary money, [percent] numeric(2,1), realsalary money ) Go create trigger salary_trig on salary_table instead of insert as declare @salary money,@percent numeric(2,1) select @salary=salary,@percent=[percent]from inserted insert into salary_table(salary,[percent],realsalary) values(@salary,@percent,@salary*(1-@percent)) go insert into salary_table(salary,[percent]) values(1500,0.2)

修改触发器

ALTER TRIGGER TRI_NAME

启用或禁用触发器

--启用和禁用服务器级别DDL触发器： enable trigger tri_ddl_db on all server; disable trigger tri_ddl_db on all server; --启用和禁用数据库级别DDL触发器 enable trigger tri_ddl_view on database; disable trigger tri_ddl_view on database;

 

删除触发器

--删除服务器级别的DDL触发器 DROP TRIGGER tri_ddl_db ON ALL SERVER --删除数据库级别的DDL触发器 DROP TRIGGER tri_ddl_tab ON DATABASE GO DROP TRIGGER TRI_NAME

存储过程

查看存储过程

--通过系统存储过程sp_helptext可显示规则、默认值、没有加密的存储过程，用户定义的函数、触发器或视图的文本。 exec sp_helptext pro_name exec sp_depends pro_name exec sp_help pro_name

2.2创建存储过程

--创建没有参数的存储过程 create procedure student_info as select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and course.cno=sc.cno go --执行存储过程 exec student_info --创建带有输入参数的存储过程 create procedure student_info2 @xm varchar(8),@kcm varchar(50) as select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and course.cno=sc.cno and sname=@xm and cname=@kcm go --存储过程调用形式 -- 按位置传送参数值 exec student_info2 '李明','高等数学' --使用参数名传送 exec student_info2 @xm='李明',@kcm='高等数学' exec student_info2 @kcm='高等数学',@xm='李明' create procedure stud_info3 @startdate date,@enddate date,@sex char(2)='女' as if (@startdate is null or @enddate is null or @sex is null) begin raiserror('null value are invalid',5,5) return end select * from student where sbirth between @startdate and @enddate and ssex=@sex order by sbirth go exec stud_info3 '1991-1-1','1992-12-31' --创建带有通配符参数的存储过程 if exists (select name from sysobjects where name='student_info3' and type='P') drop procedure student_info3 go create procedure student_info3 @xm varchar(8)='刘%' as select student.sno,sname,cname,grade from student join sc join course on course.cno=sc.cno on student.sno=sc.sno where sname like @xm go execute student_info3 '[王张]%' --创建带有output参数的存储过程 if exists (select name from sysobjects where name='student_info4' and type='p') drop procedure student_info4 go create procedure student_info4 @xm varchar(8),@pjf tinyint output as select @pjf=AVG(grade) from student join sc on student.sno=sc.sno where sname=@xm go declare @pjf tinyint exec student_info4 '邓立新',@pjf output select '邓立新',@pjf create procedure stud_info @startdate date,@enddate date, @recordcount int output as if (@startdate is null or @enddate is null) begin raiserror('null value are invalid',5,5) return end select * from student where sbirth between @startdate and @enddate order by sbirth select @recordcount=@@ROWCOUNT Go --其中，＠＠ROWCOUNT是SQL Server用来返回受上一语句影响的行数的系统变量，在这里用它来返回符合条件记录数。 declare @recordcount int exec stud_info '1991-1-1','1992-12-31' ,@recordcount output select @recordcount as '人数' go --使用with encryption选项对用户隐藏存储过程 create procedure encrypt_this with encryption as select * from student Go exec sp_helptext encrypt_this --创建用户自己定义的存储过程 --创建一个过程，显示表名以xs开头的所有表及其索引。如果没有指定参数，此过程返回表名以kc开头的所有表及对应索引 create procedure sp_showtable @table varchar(30)='xs%' as select sysobjects.name as table_name, sysindexes.name as index_name from sysindexes join sysobjects on sysindexes.id=sysobjects.id where sysobjects.name like @table go exec sp_showtable 'xs%' go

2.3修改存储过程

--重命名存储过程 sp_rename old_procedure_name, new_procedure_name

 

ALTER PROCEDURE teacher_proc1 WITH RECOMPILE, ENCRYPTION AS SELECT teacher_id,name,tech_title,telephone FROM teacher_info WHERE gender = '女' GO --在创建一个按照性别统计人数的存储过程stud_proc3，要求输入性别的值后， --返回对应性别的学生人数，但需保证其在每次被执行时都被重编译处理。 create procedure stud_proc (@sex char(2),@man_count int output) with recompile as select @man_count=COUNT(*) from student where ssex=@sex go declare @man_count int exec stud_proc '女',@man_count output select @man_count as '人数' go

2.4删除存储过程

DROP PROCEDURE teacher_info_proc1

 

 

 

转载于:https://www.cnblogs.com/Aha-Best/p/10870119.html