数据库——SQL Server存储过程&存储函数

一、定义

• 存储过程或存储函数也是一类数据库的对象，需要有创建、删除等语句。这里的存储函数指自定义函数。类似于高级语言程序，过程化SQL程序也可以被命名和编译，并保存在数据库中，称为存储过程（stored procedure）或存储函数（stored function）。
• 可以将存储过程理解为一组预先编译好的sql语句的集合，即批处理语句。

二、优缺点

1、存储过程

优点：

• ①提高代码的重用性
• ②简化操作
• ③减少了编译次数， 减少了和数据库服务器的连接次数，提高了效率

缺点：

• 移植性差，如需兼容多种类型的数据库，尽量少用存储过程

2、存储函数

• 存储函数和存储过程意义和好处都是一样的，都是持久性存储模块。
• 存储过程可以没有返回值，也可以有多个返回值，适合做批量插入数据、批量更新等。
• 存储函数必须指定返回的类型，必须有返回值，而且只能有1个，适合做处理数据后返回1个结果。

三、存储过程

1、创建存储过程

if OBJECT_ID(N'procName',N'P') is not null
 drop proc procName
go
/*存储过程首部,以下示例为带参存储过程，如无参，则去掉括号及括号里的内容*/
 create procedure procName(
/*参数模式默认IN，在被调用时需要指定参数值，OUT在被调用时不用指定，而是作为返回值返回。
INOUT则既需要指定参数值，也会返回操作的最终值*/
 [IN|OUT|INOUT] @paramName1 paramType1,
 [IN|OUT|INOUT] @paramName2 paramType2,...)
 as
    T-SQL程序块
go

详解：

①object_id()

功能为判断资源是否已存在，以确保不会重复命名资源，可接受两个参数，第一个参数代表资源的名称，第二个参数表示该资源的类型，下列表格描述了常用资源类型及说明：

常用资源类型

type	说明
u	用户创建的表，区别于系统表(USER_TABLE)
s	系统表(SYSTEM_TABLE)
v	视图(VIEW)
p	存储过程(SQL_STORED_PROCEDURE)

ps:参数前面的N表示N'procName' 表示procName是个Unicode字符串

②drop proc  存储过程名

drop表示删除定义

proc表示存储过程（即资源）

存储过程名称（即资源名称）

③CREATE PROCEDURE 存储过程名(
[IN|OUT|INOUT] @参数1 参数类型,
[IN|OUT|INOUT] @参数2 参数类型,...)

上述为存储过程首部,示例为带参存储过程，如无参，则去掉括号及括号里的内容。参数也可以定义输入参数（IN）、输出参数（OUT）或输入/输出（INOUT）参数，默认为输入参数即IN。

1. IN在被调用时需要指定参数值
2. OUT在被调用时不用指定，而是作为返回值返回
3. INOUT则既需要指定参数值，也会返回操作的最终值

④T-SQL程序块

是一个<过程化SQL块>，包括声明部分和可执行语句部分

示例：创建存储过程，实现通过系编号获取该系的学生信息

--创建存储过程，实现通过系编号获取该系的学生信息
CREATE PROCEDURE P_getStudentByStept@Stept nvarchar(50) 
AS
BEGIN
	select * from student where stept=@Stept
END

2、执行存储过程

语法：

     EXEC 存储过程名称([参数1,参数2,...])；

1. 使用EXEC方式激活存储过程的执行
2. sql server数据库支持在存储过程体中调用其他存储过程

示例1：

执行存储过程，查询Cs系的全体学生

--执行存储过程，查询Cs系的全体学生         
exec P_getStudentByStept 'cs'

执行结果1： 

 示例2:

if OBJECT_ID(N'p_getStudentInfo',N'P') is not null
drop proc p_getStudentInfo
go
create procedure p_getStudentInfo
@sno char(12), @flag char(50) output
as
begin  
   if(dbo.f_getavg(@sno) >80)
      set @flag = '优秀'
   else if (dbo.f_getavg(@sno) >60)
      set @flag = '合格'
   else 
      set  @flag = '努力'
end

declare @theResult char(50)
exec  p_getStudentInfo '18210120303',@theResult output
select @theResult

3、修改存储过程

--重命名存储过程
ALTER  PROCEDURE  过程名称1 RENAME TO 过程名称2;
--重新编译存储过程
ALTER  PROCEDURE  过程名称 COMPILE;

4、删除存储过程

DROP PROCEDUCE  存储过程名称

四、存储函数（用户自定义函数）

1、创建函数

 ①标量函数--即返回的结果只是一个标量，通俗的讲返回的结果就是一种类型的一个值。

注意：RETURNS后面跟的是返回值类型，RETURN后面跟的是具体的值，比如变量

语法：

CREATE FUNCTION funName(@paramName paramType)
RETURNS returnType   ---函数必须而且只能有一个返回值
AS
BEGIN 
     T-SQL
     RETURN '返回值'     --程序中必须有return返回语句
END
GO

举例：编写自定义函数，根据学号，获取该同学所有科目的平均分

--编写自定义函数，根据学号，获取该同学所有科目的平均分
if OBJECT_ID(N'f_getavg',N'F') is not null
 DROP FUNCTION f_getavg
--上述代码判断函数是否已存在，若存在则删除
go
CREATE FUNCTION f_getavg(@sno char(12))   ---函数名、函数参数
RETURNS numeric(4,1)                      ---函数返回值必须指定类型而且只能有一个返回值
AS
BEGIN 
 DECLARE @avg numeric(4,1)
 select @avg=avg(scgrade) from sc where sno=@sno
 RETURN @avg                              ---函数必须return返回一个值
END

运行结果：（通过查询语句查询返回的结果值）

 

②内联表值函数--即返回的是一张表数据。

语法：

CREATE FUNCTION funName(@paramName paramType)
RETURNS TABLE
AS
RETURN (select * from Table)
go

示例：编写自定义函数，返回所有同学所有科目的平均分

--编写自定义函数，返回所有同学所有科目的平均分
if OBJECT_ID(N'f_allavg',N'F') is not null
DROP FUNCTION f_allavg
go
CREATE FUNCTION f_allavg()
RETURNS TABLE   --返回值类型为表对象
AS
RETURN (select sno,avg(scgrade) avggrade from sc group by sno)
go

运行结果：

③多语句表值函数--即通过多条语句来创建Table类型的数据，自行指定具体的Table类型的结构

if OBJECT_ID(N'f_allavgtable',N'F') is not null
 DROP FUNCTION f_allavgtable
 go
 create function f_allavgtable()
 returns @result table( 
    sno char(12),
    avggrade numeric(4,1)
    )
 as
 begin
     insert into @result select sno,avg(scgrade) avggrade from sc group by sno
     return 
 end
 go
 ---调用和上面的一样
select * from f_allavgtable()

2、调用函数

 调用方法和调用系统函数一样

3、删除函数

drop function  自定义函数名;

4、查看自定义函数的信息  

exec sp_helptext '自定义函数名';