MSSQL之九存储过程与函数

作为数据库开发人员，你可能需要一起执行一系列SQL语句，SQL Sever允许你创建能一起执行的多个语句的批处理，批处理中可以包含控制流语句以及在执行语句之前检查条件的条件逻辑。 当你需要在不同时间重复的执行批处理时，可以把批处理保存为存储过程和函数的

作为数据库开发人员，你可能需要一起执行一系列SQL语句，SQL Sever允许你创建能一起执行的多个语句的批处理，批处理中可以包含控制流语句以及在执行语句之前检查条件的条件逻辑。 当你需要在不同时间重复的执行批处理时，可以把批处理保存为存储过程和函数的数据库对象。这些数据库包含一个预编译的批处理，它可以不需要再编译而执行很多次。 本章解释如何创建批处理以执行多个SQL语句以及如何在SQL Sever2008实现存储过程和存储函数。 重点 ? 实现批处理 ? 实现存储过程 ? 实现函数 预习功课 ? 创建批处理 ? 创建存储过程 ? 创建带参数的存储过程 ? 从存储过程返回值 ? 创建标量、表值、内联表值函数

创建批处理

批处理是一组一起提交给SQL Sever执行的SQL语句。当执行批处理时SQL Sever将批处理的语句编译到一个称为执行计划的可执行单元。这样可以节省执行时间。

为了创建批处理，你可以写出多个SQL语句，后面在结尾跟着关键字Go,Go是一个命令，它指定批处理的结束。

使用变量

创建批处理的时候,你需要在执行的时候保存一些临时值,为存储这些临时值你可以声明变量并且为它们指定值.

定义变量

declare 变量名称 数据类型

给变量赋值

(1) 直接赋值

set 变量名称=值

(2) 从表中获得值

select 变量名称=值 from 表名

使用结构

If…else条件选择结构

If <条件表达式>

else

CASE结构

CASE <表达式>

WHEN <表达式> THEN <表达式>

[[WHEN <表达式> THEN <表达式>] […]]

[ELSE <表达式>]

END

BEGIN…END语句块

BEGIN

END

WHILE循环结构

WHILE <布尔表达式>

BEGIN

[BREAK]

[CONTNUE]

[SQL语句或程序块]

END

创建存储过程

什么是存储过程

存储过程是为完成特定的功能而汇集在一起的一组SQL程序语句，经编译后存储在数据库中的SQL程序。

.

当创建存储过程时，需要确定存储过程的三个组成部分：

（1）所有的输入参数以及传给调用者的输出参数。

（2）被执行的针对数据库的操作语句，包括调用其他存储过程的语句。

（3）返回给调用者的状态值，以指明调用是成功还是失败。

常用的系统存储过程

? 定义不带参数存储过程的语法

CREATE PROCEDURE 存储过程名

AS --注释:表示后面是存储过程要执行的语句

SQL语句

GO

? 定义带参数存储过程的语法

CREATE PROCEDURE 存储过程名

@参数1 数据类型[ = 默认值 OUTPUT],

…… ,

@参数n 数据类型[ = 默认值 OUTPUT]

AS --注释:表示后面是存储过程要执行的语句

SQL语句

GO

OUTPUT:指定存储过程必须返回一个参数.该存储过程的匹配参数也必须由关键字OUTPUT创建.使用游标变量作为参数时使用该关键字.

【例9-1】创一个存储过程，以简化对sc表的数据添加工作，使得在执行该存储过程时，其参数值作为数据添加到表中。

程序清单如下：

CREATE PROCEDURE [dbo].[ pr1_sc_ins]

@Param1 char(10),@Param2char(2),@Param3 real

AS

BEGIN

insert into sc(sno,cno,score) values(@Param1,@Param2,@Param3)

END

【例9-2】创建一个带有参数的简单存储过程，从视图中返回指定的雇员（提供名和姓）及其职务和部门名称，该存储过程接受与传递的参数精确匹配的值

程序清单如下。

USE AdventureWorks;

GO

CREATE PROCEDURE GetEmployees

@lastname varchar(40),

@firstname varchar(20)

AS

SELECT LastName, FirstName, JobTitle,Department

FROM HumanResources.vEmployeeDepartment

WHERE FirstName = @firstname AND LastName =@lastname;

GO

? 调用存储过程语法

EXEC PROCEDURE 存储过程名

【例9-3】执行存储过程au_infor_all。

au_infor_all 存储过程可以通过以下方法执行：

EXECUTE（EXEC） au_infor_all

【例9-4】使用 EXECUTE 命令传递参数，执行例9-1定义的存储过程pr1_sc_ins。

sc_ins存储过程可以通过以下方法执行：

EXEC pr1_sc_ins ‘3130040101’,’c1’,85

当然，在执行过程中变量可以显式命名：

EXEC sc_ins @Param1=’ 3130040101’,@Param2=’c1’,@Param3=85

【例9-5】 执行例9-2定义的存储过程GetEmployees。

GetEmployees存储过程可以通过以下方法执行：

EXECUTE（EXEC） GetEmployees'Dull', 'Ann' 或者

EXECUTE（EXEC） GetEmployees@lastname = 'Dull', @firstname = 'Ann' 或者

EXECUTE（EXEC） GetEmployees@firstname = 'Ann', @lastname = 'Dull'

修改存储过程

重命名存储过程

例: 把存储名称p_name修改为 p-address

EXEC sp_rename ‘p_name’,’p_address’

删除存储过程

例: 删除存储过程p_name

DROP PROCEDURE p_name

实现函数

你可以创建函数来永久存储一系列SQL语句,根据函数返回值形式的不同,用户定义的函数有标量函数和表值函数.

? 创建函数的语法:

CreateFunction[schema_name] function_name

([{@parameter_name[AS][type_schema_name.]

parameter_data_type

[=default] }

[,…n ]

]

)

Returnsreturn_data_type

[WIIH[,…n] ]

[AS]

BEGIN

function_body

return expression

END

@parameter_name 是在函数中的参数.可以有一个或多个被声明的参数.

[typr_schema_name] parameter_data_type 是参数的数据类型,和可选的它的所属的模式.

[=default] 是参数的默认值.

return_data_type 是一个标量的用户定义函数的返回值.

function body 指定一系列T-SQL语句.

? 创建标量函数

标量函数接受一个参数并且返回在RETURNS从句中指定的类型的一个数据值。标量函数可以返回除了文本、ntext、图片、光标和时间戳之外的任何数据类型。有些标量函数，例如current_timestamp，不需要任何参数。

语法：

create function 函数名(@变量名1 数据类型)

returns 返回值的数据类型

as

begin

declare @变量名2 数据类型

select @变量名2=sum(列名) from 表名1 where 主键名=@变量2

return @变量名2

end

select 函数名(主键名) from 表名2

例如:

CREATE FUNCTION HumanResources.MonthlySal (@PayRate float)

RETURNS float

AS

BEGIN

RETURN (@PayRate * 8 * 30)

END

例如：

DECLARE @PayRate float

SET @PauRate = HumanResources.MonthlySal(12.25)

PRINT @PauRate

注释：上述代码中，@PayRate是一个变量，它将存储MonthlySal函数返回的值。

创建表值函数

内联表值函数从一个SELECT语句的结果集返回一个表数据的变量。内联函数不再BEGIN和END语句中包含函数体。

例子1：

Create function fx_Department_name(@grnamenavarchar(20))

Return table

As

Return(

Select *

FromHumanResources.Department

WhereGroupName=@grname

)

GO

这里的内联表，接收一组名称作为参数并且饭回来自Department表属于组的部门的详情。

使用 SELECT*FROM fx_Department_name 可以查看上述代码的输出。

多语句表值函数

多语句表值函数使用多个语句来创建表，它被返回给调用语句。函数体包含BEGIN.。。。END块，它保存一系列T-SQL语句以创建和插入行盗临时表。临时表被在结果集中返回，并且基于函数中提到的规范创建。

语法:

create function 函数名(@变量名1 数据类型)

returns @变量名2 table

(

和创建表中的内容一样

)

as

begin

insert @变量名2 select表中的列名 from 表名 ----指把表中的内容加到新创建的函数表中

where 表中的另一个列名>@变量名1

insert @变量名2values(.......)

end

select * from 函数名(表中的另一个列中的内容)

例如：

CREATE FUNCTION PayRate (@rate money)

RETURNS @table TABLE

(EmployeeID int NOT NULL,

RateChangeDate datetime NOT NULL,

Ratemoney NOT NULL,

PayFrequency tinyint NOT NULL,

ModifiedDate datetime NOTNULL）

AS

BEGIN

INSERT@table

SELECT *

FROMHumanResources.EmployeePayHistory

WHERE Rate> @rate

RETURN

END

语句：

SELECT *FROM PayRate(45)

注释：

函数以在函数内创建的临时表@table，的形式返回一个结果集。以上的语句执行函数。

实践问题

1、批处理的用途是什么？

2、在批处理中本地变量的范围是什么？

3、存储过程如何返回值？

4、下面的哪个结构被使用，当你需要重复执行一系列T-SQL语句的时候？

A、try – catch块

B、while语句

C、if-else语句

D、case语句

小结

1、批处理是一系列一起提交到服务器执行的SQL语句。

2、你可以使用变量存储一个临时值。

3、你可以使用print语句来在屏幕上显示一个变量的内容。

4、你可以在批处理中使用注释给代码写注释。

5、你可以使用 if –else语句从条件执行SQL语句。

6、CASE语句求一系列条件的值并且返回各种可能结果中的一个。

7、你可以在批处理中使用WHILE语句以允许一系列T-SQL语句重复执行，只要给定条件为真。

8、BREAK语句导致从WHILE循环中退出。

9、存储过程是各种T-SQL语句的集合，它被存储在一个名字下，并且作为一个单元执行。

10、存储过程可以使用CREATE PROCEDURE语句创建。

11、存储过程允许你声明参数、变量和使用T-SQL语句并且编程逻辑。

12、存储过程提供更好的性能、安全性和准确性并且减少网络拥塞

13、存储过程通过输入参数接受数据。

14、存储过程通过输出参数或返回语句返回数据。

15、存储过程可以使用EXECUTE语句执行。

16、存储过程使用alter procedute语句执行修改

17、用户定函数是一个数据库对象，它包含一系列T-SQL语句。

18、用户定义函数可以返回一个单一标量值或结果集。