学习sqlserver的存储过程、触发器、函数

数据库存储结构学习

T-sql的一些语法

declare @arg char(8)   			声明 变量(变量以@开头) 类型
set @arg='123'        			给变量赋值
set @arg=(select ID from user)	给变量赋值

sqlserver的insert、update、delete注意事项

insert：插入一条数据时，会生成一个临时表inserted
update：更新一条数据时，没有临时表updated，有临时表inserted
delete：删除一条数据时，会生成一个临时表deleted

select中的条件语句示例：当性别用1，0表示时，查询结果中的性别使用【男】【女】显示

select 
case 
	when sex=1 then '男' 
	when sex=0 then '女' 
end, 
max(age),avg(age) 
from emp 
group by sex 
having sex=0

简单总结【存储结构】【触发器】【函数】的作用

1、存储结构：在数据库中定义好方法（由sql语句构成），在需要的地方直接 exec 调用。优点：sql语句复用，自定义查询方法逻辑
2、触发器：当执行【增删查改】时自动触发的sql语句
3、函数：执行一些自定义小功能，例如judge(age)函数，返回字符串【青年】还是【老年】，类似max、avg函数

存储过程

存储过程在【数据库】下的【可编程性】【存储过程】创建

作用：在数据库层面创建方法，执行SQL语句，实现自定义高级查询功能。例如：只写一条语句，当有参数【学生姓名】时，查单条；当没有参数【学生姓名】时，查所有。

调用：exec 【存储过程】【参数】

存储结构基础语法

-- 目的：调用存储结构，输入【姓名参数】查找学生的【ID，学生姓名，年龄】信息并返回
use library
drop PROCEDURE queryAll2
GO
CREATE PROCEDURE queryAll2 @name char(8)=NULL-- 添加方法参数并设默认值
	-- 添加全局参数
AS
BEGIN
	SET NOCOUNT ON;
	if @name is not NULL
    -- Insert statements for procedure here
		SELECT ID,学生姓名,年龄 from 学生表 where 学生姓名=@name
	else
		SELECT ID,学生姓名,年龄 from 学生表
END
GO
-- 调用存储结构
exec queryAll2 -- 或者
EXEC queryAll2'ls'

带输入输出参数的存储结构

-- 目的：调用存储结构，只传入一个【性别】参数，输出性别为【男】或【女】学生的【平均年龄】和【最大年龄】
use library
GO                      -- GO的作用：隔离sql语句，表示事务可以提交
drop PROCEDURE queryAll3
GO
CREATE PROCEDURE queryAll3
	@sex char(8),        -- 添加方法参数
	@AVGage float OUTPUT,
	@MAXage float OUTPUT
AS
BEGIN
	select @AVGage=AVG(年龄) from 学生表 where 性别=@sex
	select @MAXage=MAX(年龄) from 学生表 where 性别=@sex
END
GO
-- 执行使用存储结构
declare @sex char(8),@out_AVGage float,@out_MAXage float -- 连续声明三个变量
set @sex='男'                                            -- 给输入参数赋值
exec queryAll3 @sex,@out_AVGage output,@out_MAXage output -- 执行输入输出方法，有output
select @sex as 性别,@out_AVGage as 平均年龄,@out_MAXage as 最大年龄

目的：判断表、存储结构、触发器是否存在，如果存在则删除

GO
if exists(select * from sysObjects where name='queryAll3')
begin
    drop PROCEDURE queryAll3
    select'已删除'
end
GO

SQL日期运算

dateadd  在向指定日期加上天数，返回新的 datetime 值
select dateadd(day,2,'2004-10-15') 				 --返回：2004-10-17 00:00:00.000
datediff  返回两个日期天数差值
select datediff(day,'2004-09-01','2004-09-18')   --返回：17

触发器

触发器在表下面【触发器】创建

作用：当执行insert、update、delete操作时，触发事件执行额外操作，例如插入一条数据时，同时生成一条插入历史记录

调用：自动触发

执行时间和方式
例如：AFTER insert     在插入数据后执行
instead of delete     替代delete语句，执行delete操作时进入该事件

-- 目的：当有人借书添加记录时，判断书是否存在，并自动记录借书时间和还书时间
SET ANSI_NULLS ON -- select * from table where arg!=NULL查不出结果
GO
-- sql关键字允许通过加‘’的形式做变量
-- select 'name' 与 select name 的结果相同，off时，select 'name'显示不出数据
SET QUOTED_IDENTIFIER ON
GO
if exists(select * from sysObjects where name='borrowbook')
drop TRIGGER borrowbook
Go
CREATE TRIGGER addstudent
   ON borrow 
   AFTER insert
AS 
BEGIN
	SET NOCOUNT ON; -- 开启返回影响条数

	declare @BID int,@bookID int,@borrowDate date,@returnDate date
	set @BID=(select ID from inserted)
	set @bookID=(select bookID from inserted)
	set @borrowDate=GETDATE()
	set @returnDate=dateadd(day,90,@borrowDate)

	if exists(select * from book where ID=@bookID)
		begin
			update borrow set returnDate=@returnDate where ID=@BID
		end
	else
		begin
			rollback
			print('图书不存在')
		end

END
GO
-- 调用
insert into borrow(bookID,name) values(9,'zs') -- 插入借书数据，参数【书名】、【借书人】

用户自定义函数

作用：自定义类似sum、Max、avg、dateadd的函数

创建：通过【数据库】【可编程性】【函数】【标量值函数】创建

-- 基础模板
CREATE FUNCTION 函数名	           --创建标量函数
[({@形参名 数据类型}[,...n])] 		--括号内输入参数
RETURNS 数据类型	   --定义返回标量值的数据类型
[WITH 选项]
AS 
BEGIN
   T-SQL语句				--函数体
RETURN 标量表达式	
--返回RETURNS子句中定义的数据类型的单个数据值
END

示例（知识：1、不区分大小写2、begin end相当于{}隔离语句3、Go开启事务4、如何删除【函数】）

-- 目的：调用judge(age)函数，返回字符串'少年''青年''中年''老年'
if exists(select * from sys.objects where name = 'judge')
	drop FUNCTION dbo.judge          -- 删除定义的函数
use library
Go
-- 基础模板
create FUNCTION dbo.judge(@age int)  --创建标量函数
RETURNS nvarchar(50)	             --定义返回标量值的数据类型
AS 
BEGIN
	declare @result nvarchar(50)
	if @age > 17
		set @result='青年'
	else if @age > 30
		set @result='中年'
	else if @age > 60
		set @result='老年'
	else
		set @result='少年'
	RETURN @result	
END
GO
-- 使用函数
select name,age,sex,dbo.judge(age) from emp