SQLSERVER DLL的基本操作和范例

/**
? *删除数据库中已经存在的表
?**/
if exists(select * from sys.objects where name='tb_grade')
drop table tb_grade--删除tb_grade表
go
if exists(select * from sys.objects where name='tb_student')
drop table tb_student--删除tb_grade表
go

/**
? *创建数据表
?**/
create table tb_student--创建tb_student
(
?? student_id int identity(1,1),--学生编号(主键,自动增长)
?? student_name nvarchar(30) not null,--学生姓名(不能为空)??
?? student_sex char(10) not null,--学生性别(不能为空)
?? student_age int default(18),--学生年龄(默认为18)
?? constraint pk_student_id primary key (student_id)
)
go
create table tb_grade--创建tb_grade
(
? grade_id int identity(1,1),--成绩编号(主键，自动增长1)
? student_id int,--外键(引用学生表student_id)
? english float,--英语成绩
? math float,--数学成绩
? constraint pk_grade_id primary key (grade_id),--为表tb_student创建主键
? constraint fk_student_id foreign key(student_id) references tb_student(student_id)--创建外键关系
)
?
select * from tb_student
select * from? tb_grade

/**
? *创建视图
?**/

/**
?*创建视图的语法
?*
?CREATE VIEW? view_name
?AS
?select_statement
*
*
*/

if exists (select * from sys.objects where name='vw_student')
drop view vw_student --删除已经存在的vw_student视图
go
create view vw_student--创建视图vw_student
as
select * from tb_student inner join tb_grade on tb_student.student_id=tb_grade.student_id
go

/**
? *创建索引
?**/

/**
?*创建索引的语法
?*
CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
??? ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ WITH < index_option > [ ,...n] ]
[ ON filegroup ]

< index_option > ::=
??? { PAD_INDEX |
??????? FILLFACTOR = fillfactor |
??????? IGNORE_DUP_KEY |
??????? DROP_EXISTING |
??? STATISTICS_NORECOMPUTE |
??? SORT_IN_TEMPDB?
}
*
*
*/

if exists (select * from sys.indexes where name='index_student_id')
drop index index_student_id on tb_student --删除tb_student中的索引student_id
--创建索引
create index index_student_id on tb_student(student_id)
go

/**
? *创建存储过程
?**/

/**
?*
?*创建存储过程的语法
?*
CREATE PROC [ EDURE ] procedure_name [ ; number ]
??? [ { @parameter data_type }
??????? [ VARYING ] [ = default ] [ OUTPUT ]
??? ] [ ,...n ]

[ WITH
??? { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS sql_statement [ ...n ]
*
*/
--不带参数的存储过程
if exists (select * from? sys.objects where name='find_student')
drop procedure find_student
go
create procedure find_student
as
select * from tb_student
go
---调用存储过程
exec find_student
go
--带输入参数的存储过程
if exists (select * from? sys.objects where name='add_student_grade')
drop procedure add_student_grade
go
create procedure add_student_grade
--定义输入参数
@studentName nvarchar(30),
@studentSex char(10),
@studentAge int=18,
@gradeEnglish float,
@gradeMath float
as
??? declare @studentId int
??? declare @n int
??? set @n=0
??? begin transaction tran_add --开启事务
??? insert into tb_student(student_name,student_sex,student_age) values(@studentName,@studentSex,@studentAge)
??? set @n=@@error???
?select @studentId=max(student_id) from tb_student
?set @n=@@error?
??? insert into tb_grade (student_id,english,math) values(@studentId,@gradeEnglish,@gradeMath)
?set @n=@@error?
??? if(@n<>0)
?????? begin
?????????? rollback transaction tran_add --回滚事务
?????? end
??? else
?????? begin
???? commit transaction tran_add? --提交事务
?????? end
go
---调用存储过程
exec add_student_grade '张三','男',20,80,88
go
--带输出参数的存储过程
if exists (select * from? sys.objects where name='getCount')
drop procedure getCount
go
create procedure getCount
@n int output
as
select @n=count(*) from tb_student
go
--调用带输出参数的存储过程
declare @n int
execute getCount @n output
select @n
??
/**
?*创建触发器
?**/
/**
?*
?*创建触发器的语法
?*
?*
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
??? { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
??????? [ WITH APPEND ]
??????? [ NOT FOR REPLICATION ]
??????? AS
??????? [ { IF UPDATE ( column )
??????????? [ { AND | OR } UPDATE ( column ) ]
??????????????? [ ...n ]
??????? | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
??????????????? { comparison_operator } column_bitmask [ ...n ]
??????? } ]
??????? sql_statement [ ...n ]
??? }
}
*
*
**/

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'reminder' AND type = 'TR')
DROP TRIGGER reminder --删除触发器reminder
GO
--创建触发器reminder(如果对表tb_student进行添加和更新信息时出发)
CREATE TRIGGER reminder
ON tb_student
FOR INSERT, UPDATE
AS
RAISERROR (50009, 16, 10)
GO
---创建DELETE触发器
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'sendemail' AND type = 'TR')
DROP TRIGGER sendemail--删除触发器sendemail
GO
--创建触发器
CREATE TRIGGER sendemail
ON tb_grade
FOR DELETE
AS
EXEC master..xp_sendmail'?MaBin',
'Don''t forget to print a report for the distributors.'
GO