SQL Sever 教案第13章 存储过程及自定义函数由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“sql自定义函数例子”。
什么是存储过程，在存储在服务器上的T-SQL语句的命名集合，是封装性任务的方法，支持变量及条件的编程。
SQL Server的存储过程与其他编程语言中的过程（包括函数）类似，可以包含数据库操作（调用其他过程）的编程语句，可以接受参数，可以返回状态值以表明成功或失败，以输出参数的形式将多个值返回至调用过程
SQL Server支持五种类型的存储过程：
系统存储过程（sp_）：存储在master数据库中。
本地存储过程：在单独的用户数据库中。
临时存储过程：局部的以#开头，全局的以##开头。
远程存储过程：分布式查询支持此功能。
扩展存储过程：在SQL Server环境外执行。
存储过程的优点 封装商务逻辑，若规则或策略改变只需修改存储过程就可以直接使用，屏蔽数据库的详细资料，用户不需要访问底层数据库和数据库对象。提供安全机制，只需要提供存储过程的权限而不需要提供整个数据库中数据的一个权限。另外，存储过程能够通过预编译的语句来确定执行哪一部分而不是都执行。在传输过程中传输的存储过程而不是数据，减少了通信量，能够实现一个较快的执行速度。
create proc liuhaoran as select price from titles where price>15 select title from titles where price
在存储过程里可以包含任何数目和类型的T-SQL语句，但不能包含create proc、create trigger、create view 执行创建存储过程的用户必须是sysadmin、db_owner 或 db_ddladmin角色的成员，或必须拥有 CREATE PROCEDURE 权限 存储过程有大小的限制，最大为128M
存储过程可以传递参数，创建存储过程，定义两个浮点型的参数，无返回值 CREATE PROCEDURE titlespro @Beginningprice float,@Endingprice float AS IF @Beginningprice IS NULL OR @Endingprice IS NULL BEGIN
print 'no price is exits'
RETURN END SELECT price FROM titles WHERE price BETWEEN @Beginningprice AND @Endingprice GO
/*执行语句,输入两个价格值作为参数值*/ exec titlespro 10,19
指定参数的依据和指导原则
所有的输入参数值都应该在存储过程开始的时候进行检查，以尽早捕获缺失值和非法值
应该为参数提供合适的默认值，可以未指定参数值的基础上执行存储过程
一个存储过程最多可以有1024个参数
不同存储过程可以使用相同的参数名 使用参数的指导原则
可以使用@参数=值的格式来指定参数，此方法可以按任意顺序来传递参数
对于有默认值的参数在调用存储过程的过程中可以不指定参数值
存储过程可以使用输出参数
--创建存储过程输入两个输入参数，定义一个输出参数 CREATE PROCEDURE Mathadd @m1 int, @m2 int, @result int OUTPUT--定义输出参数 AS SET @result=@m1+@m2 GO
调用过程如下：
declare @resultvalues int
exec mathadd 12,16,@resultvalues output--输出参数的值赋给变量 print 'The result is: '+convert(char,@resultvalues)
存储过程通过输出参数向调用它的存储过程或客户端返回信息，通过输出参数，存储过程的运行结果可以保留到程序运行结束。输出参数接受需要注意：
调用语句必须包含一个变量名，以接受返回值。
可以在T-SQL语句中使用返回变量 输出参数可以是任意类型，除了text和image 输出参数可以是游标
对于已经创建好的存储过程，如果存储过程需要修改，可以显式地重新编译，但应尽量少做。
在创建存储过程之前通常会检查此存储过程是否存在，如果存在可以选择删除后重建，或改变名字
IF EXISTS(SELECT name FROM sysobjects WHERE name='author_infor' AND type='p')DROP PROCEDURE author_infor GO
修改存储过程使用关键字alter
alter procedure pro_titles @values money--修改存储过程pro_titles as select price,pub_id from titles where price=@values go
删除存储过程使用关键字drop proc，因为存储过程属于数据库对象
drop procedure pro_titles
对于数据库中的所有存储过程或者触发器，如果需要将其全部重新编译，那么可以使用以下语句，EXEC sp_recompile titles--重新编译所有的存储过程或者触发器
在程序中通常会出现因为运行错误而出现的各种错误提示，其大部分都不能被用户容易读懂，因此可以自定义错误信息，来提示用户错误的具体原因。--自定义错误信息
EXEC sp_addmeage
@msgnum = 50010,---错误编号 @severity = 10,----严重级别
@msgtext = 'Customer cannot be deleted.',--错误文本信息 @with_log='true', @lang='us_english'
创建好自定义错误信息之后，就可以在程序中使用错误信息
--调用自定义错误信息
EXEC sp_addmeage 50010,10,'CustomerID not found.',@replace='replace' USE Northwind GO CREATE PROC UpdateCustomerPhone @CustomerID nchar(5)=NULL, @Phone nvarchar(24)=NULL AS IF @CustomerID IS NULL BEGIN
PRINT 'You must supply a valid CustomerID.'
RETURN END /*确认提供了合法的CustomerID */ IF NOT EXISTS(SELECT * FROM Customers WHERE CustomerID=@CustomerID)
BEGIN
RAISERROR(50010,10,1)--该客户不存在。
RETURN
END BEGIN TRANSACTION UPDATE Customers SET Phone =@Phone WHERE CustomerID=@CustomerID /*显示CompanyName的电话号码已更新的消息*/ SELECT 'The phone number for'+@CustomerID +'has been updated to'+ @Phone COMMIT TRANSACTION GO
创建存储过程的方法上面已经介绍结束，如果想要查看存储过程的代码，就需要使用到以下的关键字
--查看存储过程
EXEC sp_helptext UpdateCustomerPhone
而对于以存在的存储过程，在调用的过程由于某些原因需要重新命名的化，使用以下语句
--重命名存储过程
EXEC sp_rename reptq1, newproc
用户自定义函数部分
函数和存储过程同样都有输入和输出参数，单数输入和输出参数的声明方式有些区别--创建函数 USE pubs go CREATE FUNCTION WorkYearWage(@hiredate datetime,--hiredate 表示雇佣日期
@today datetime, @per_wage money)--today 表示当前的日期per_wage 表示每一年工龄应得的工资额 RETURNS money--返回值类型 AS BEGIN DECLARE @WorkYearWage money SET @WorkYearWage =(year(@today)-year(@hiredate))*@per_wage RETURN(@WorkYearWage)--返回的变量 END--结束函数定义 GO-
使用函数参数，当使用命名函数的时候，参数的次序不必按照在函数中声明的次序，但必须包含所有参数，不能省略任何参数
使用函数的过程同以前学习过的系统函数的使用方法想类似，在输出或查询语句中使用函数名及相应的床底若干参数即可。
--使用函数
SELECT pubs.dbo.workyearwage('1991-7-1',getdate(),15)--传参并输出结果
As work_year_wage
在函数的返回值方面可以直接声明返回参数，或者声明返回值类型--计算立方体函数
CREATE FUNCTION CubicVolume(@CubeLength decimal(4,1), @CubeWidth decimal(4,1), @CubeHeight decimal(4,1))--定义输入参数 RETURNS decimal(12,3)--定义返回值类型 AS BEGIN RETURN(@CubeLength * @CubeWidth * @CubeHeight)--返回结果 END
除特定类型的返回值之外，还可以返回表类型的结果集
--内联表值型用户自定义函数
CREATE FUNCTION orderfirms(@productid INT)--定义一个整型参数 RETURNS TABLE--返回值为表的结果集 AS RETURN(SELECT * FROM jobs WHERE min_lvl>@productid)--返回查询结果 GO
或者返回值是一张临时表
--多声明表值型用户自定义函数 USE Northwind go CREATE FUNCTION my_function(@regionParameter varchar(25))--定义一个函数的参数
returns @my_table TABLE--函数返回值为表的结果集，并定义表的结果集如下(city varchar(15)null, companyName varchar(40)not null, contactName varchar(30)null)AS BEGIN INSERT @my_table--从表customers中查询数据插入到@my_table中 SELECT city,companyName,contactName FROM Customers WHERE city=@regionParameter RETURN--返回结果集 END
表值函数：
函数体内只允许如下语句：
赋值语句
流程控制语句
用于定义函数局部数据变量和游标的declare语句
Select语句，将其后的表达式将赋予函数的局部变量
游标操作，仅允许使用fetch语句通过into子句给局部变量赋值，不允许使用fetch语句将数据返回到客户端
针对上面的例子（内嵌表值函数）需要注意的是：
return子句在括号中包含单个select语句，select语句的结果集构成函数所返回的表 函数体不由begin和end分隔
Return指定table作为返回的数据类型
不必定义返回变量的格式，因为它由return子句中的select语句的结果集的格式设置
查看创建用户自定义函数的方式有两种 查看特定用户自定义函数
--查看用户自定义函数 USE pubs go EXEC sp_helptext my_function--调用存储过程 GO
查看所有用户自定义函数
--通过系统表查看用户自定义函数 use pubs go select name, type, crdate from sysobjects where type='FN'--go
当创建的用户自定义函数需要修改时，使用如下语句
--修改函数cubicvolume函数
ALTER FUNCTION CubicVolume--这句表示声明修改用户自定义函数，其他语句不变
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1))--定义输入参数 RETURNS decimal(12,3)--定义返回值类型 AS BEGIN RETURN(@CubeLength * @CubeWidth)--返回结果 END 基本语法和创建的类型，只需要将create更改为alter即可
删除存储过程，由于存储过程为数据库对象因此删除使用drop drop function cubicvolume
