sql学习总结（7）——存储过程和函数技术

存储过程是一组完成特定功能的T_SQL语句集合，经编译后存储在SQL Server服务端数据库中，可以加速SQL语句的执行。

7.1创建存储过程

语法格式：

create proc 过程名

@parameter 参数类型

...

@parameter 参数类型 output

...

as

begin

命令行或命令块

end

执行存储过程语法格式：

execute 过程名[参数值,...][output]

7.1.1不带参数的存储过程

create procedure pro_mygz1

as

select 姓名,工资,(select avg(工资) from 职工) as 平均工资from 职工 where 姓名 not like ‘%平%’

execute pro_mygz1

7.1.2带参数的存储过程

根据输入的城市、最小工资和最大工资来显示职工姓名、工资、所在仓库的面积及金额信息：

create procedure pro_mypro2

@city varchar(50),

@mingz int,

@maxgz int

as

select 姓名,工资,面积,金额 from 职工,仓库,订购单 where 仓库.仓库号=职工.仓库号 and 职工.职工号=订购单.职工号 and 城市=@city and 工资>=@mingz and 工资<=@maxgz

显示城市在“青岛”、最低工资为1200、最高工资为2400的职工姓名、工资、所在仓库的面积及金额信息：

execute pro_mypro2 ‘青岛’,1200,2400

创建带有输出参数的存储过程

create procedure pro_mypro3

@min int output,

@max int output,

@avg int output

as

begin

select @min=min(工资) from 职工

select @max=max(工资) from 职工

select @avg=avg(工资) from 职工

end

执行：

declare @x1 int,@x2 int,@x3 int

execute pro_mypro3 @x1 output,@x2 output,@x3 output

select @x1 as 最小工资,@x2 as 最大工资,@x3 as 平均工资

7.2修改、查看和删除存储过程

7.2.1修改存储过程

只需把关键字create改成alter即可

7.2..2重命名

execute    sp_rename 原存储过程名,新存储过程名

7.2.3查看存储过程代码

execute    sp_helptext     pro_mypro

7.2.4查看存储过程所使用的数据对象信息

execute    sp_depends     pro_mypro

7.2.5查看存储过程的属性信息

execute    sp_help   pro_mypro

7.2.6删除存储过程

drop proc 过程名,[...]

7.3存储过程的自动执行与监控

利用sp_procoption可以自动执行master数据库中的存储过程，利用sp_monitor可以监控指定数据库的存储过程。

自动执行存储过程语法结构：

sp_procoption [@procName=] ‘procedure’,

[@ optionName=] ‘option’,

[@optionValue=] ‘value’

各参数的意义如下：

[@procName=] ‘procedure’ 表示要自动执行的存储过程

[@ optionName=] ‘option’其值是startup,即自动执行存储过程

[@optionValue=] ‘value’表示自动执行是开（true）或是关（false）

利用sp_monitor监控存储过程，其返回集的各参数意义如下：

last_run：上次运行的时间

current_run：本次运行的时间

seconds：自动执行存储过程以来所经过的时间

cpu_busy：计算机CPU处理该存储过程所使用的时间

io_busy：在输入和输出操作上花费的时间

idle：SQL Server已空闲的时间

packets_received：SQL Server读取的输入数据包数

packets_sent：SQL Server写入的输出数据包数

packets_errors：SQL Server在写入和读取数据包时遇到的错误数

total_read：SQL Server读取的次数

total_write：SQL Server写入的次数

total_errors：SQL Server在写入和读取时遇到的错误数

connections：登录或尝试登录SQL Server的次数

首先，在系统数据库master中创建一个存储过程：

user master

go create procedure masterproc

as

select * from spt_values

其次，利用sp_procoption系统函数设置存储过程masterproc为自动执行：

user master

go

sp_procoption @procName=’masterproc’,

@ optionName= ‘startup’,

@optionValue=]=‘true’

利用系统函数sp_monitor监控指定数据库中的存储过程：

use db_company

execute sp_monitor

创建函数

语法格式：

creat       e      function  函数名(@parameter      变量类型[,@parameter  变量类型])

returns 变量   as

begin

命令行或程序块

end

注意：在自定义函数时，必须有返回值，可以是int、char等类型，还可以是表型。

创建：

create function db_fun1(@x int)

returns int

as

begin

declare @y int

set @y=@x*@x

return @y

end

调用：

print dbo.db_fun1(4)

7.4修改、查看和删除函数

7.4.1修改存储过程

只需把关键字create改成alter即可

7.4..2重命名

execute    sp_rename 原函数名,新函数名

7.4.3查看函数代码

execute    sp_helptext     pro_myfun

7.4.4查看函数所使用的数据对象信息

execute    sp_depends     pro_myfun

7.4.5查看函数的属性信息

execute    sp_help   pro_myfun

7.4.6删除函数

drop function 过程名,[...]

7.5日期时间函数

1.getdate()

2.year(date_expression) 

3.month(date_expression)

4.day(date_expression)

5.datename(datepart,date_expression)

6.dateadd(datepart,number,date)

datediff(datepart,startpart,endpart)