`
deng131
  • 浏览: 672646 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
社区版块
存档分类
最新评论

MSSQL存储过程与函数区别

阅读更多
本质上没区别。只是函数有如:只能返回一个变量的限制。而存储过程可以返回多个。而函数是可以嵌入在sql中使用的,可以在select中调用,而存储过程不行。执行的本质都一样。
      函数限制比较多,比如不能用临时表,只能用表变量.还有一些函数都不可用等等.而存储过程的限制相对就比较少

       1.     一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。
       2.     对于存储过程来说可以返回参数,而函数只能返回值或者表对象。
       3.     存储过程一般是作为一个独立的部分来执行,而函数可以作为查询语句的一个部分来调用,由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。
       4.     当存储过程和函数被执行的时候,SQL Manager会到procedure cache中去取相应的查询语句,如果在procedure cache里没有相应的查询语句,SQL Manager就会对存储过程和函数进行编译。
       Procedure cache中保存的是执行计划 (execution plan) ,当编译好之后就执行procedure cache中的execution plan,之后SQL SERVER会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan,评判的标准一个是这个execution plan可能被使用的频率;其次是生成这个plan的代价,也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。

存储过程和用户自定义函数具体的区别:

存储过程
存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是 SQL 语句和可选控制流语句的预编译集合,以一个名称存储并作为一个单元处理。存储过程存储在数据库内,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其它强大的编程功能。

存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

可以出于任何使用 SQL 语句的目的来使用存储过程,它具有以下优点:

可以在单个存储过程中执行一系列 SQL 语句。

可以从自己的存储过程内引用其它存储过程,这可以简化一系列复杂语句。

存储过程在创建时即在服务器上进行编译,所以执行起来比单个 SQL 语句快。
用户定义函数
函数是由一个或多个 Transact-SQL 语句组成的子程序,可用于封装代码以便重新使用。Microsoft? SQL Server? 2000 并不将用户限制在定义为 Transact-SQL 语言一部分的内置函数上,而是允许用户创建自己的用户定义函数。

可使用 CREATE FUNCTION 语句创建、使用 ALTER FUNCTION 语句修改、以及使用 DROP FUNCTION 语句除去用户定义函数。每个完全合法的用户定义函数名 (database_name.owner_name.function_name) 必须唯一。

必须被授予 CREATE FUNCTION 权限才能创建、修改或除去用户定义函数。不是所有者的用户在 Transact-SQL 语句中使用某个函数之前,必须先给此用户授予该函数的适当权限。若要创建或更改在 CHECK 约束、DEFAULT 子句或计算列定义中引用用户定义函数的表,还必须具有函数的 REFERENCES 权限。

在函数中,区别处理导致删除语句并且继续在诸如触发器或存储过程等模式中的下一语句的 Transact-SQL 错误。在函数中,上述错误会导致停止执行函数。接下来该操作导致停止唤醒调用该函数的语句。

用户定义函数的类型
SQL Server 2000 支持三种用户定义函数:

标量函数

内嵌表值函数

多语句表值函数
用户定义函数采用零个或更多的输入参数并返回标量值或表。函数最多可以有 1024 个输入参数。当函数的参数有默认值时,调用该函数时必须指定默认 DEFAULT 关键字才能获取默认值。该行为不同于在存储过程中含有默认值的参数,而在这些存储过程中省略该函数也意味着省略默认值。用户定义函数不支持输出参数。

标量函数返回在 RETURNS 子句中定义的类型的单个数据值。可以使用所有标量数据类型,包括 bigint 和 sql_variant。不支持 timestamp 数据类型、用户定义数据类型和非标量类型(如 table 或 cursor)。在 BEGIN...END 块中定义的函数主体包含返回该值的 Transact-SQL 语句系列。返回类型可以是除 text、ntext、image、cursor 和 timestamp 之外的任何数据类型。

表值函数返回 table。对于内嵌表值函数,没有函数主体;表是单个 SELECT 语句的结果集。对于多语句表值函数,在 BEGIN...END 块中定义的函数主体包含 TRANSACT-SQL 语句,这些语句可生成行并将行插入将返回的表中。有关内嵌表值函数的更多信息,请参见内嵌用户定义函数。有关表值函数的更多信息,请参见返回 table 数据类型的用户定义函数。

BEGIN...END 块中的语句不能有任何副作用。函数副作用是指对具有函数外作用域(例如数据库表的修改)的资源状态的任何永久性更改。函数中的语句唯一能做的更改是对函数上的局部对象(如局部游标或局部变量)的更改。不能在函数中执行的操作包括:对数据库表的修改,对不在函数上的局部游标进行操作,发送电子邮件,尝试修改目录,以及生成返回至用户的结果集。

函数中的有效语句类型包括:

DECLARE 语句,该语句可用于定义函数局部的数据变量和游标。

为函数局部对象赋值,如使用 SET 给标量和表局部变量赋值。

游标操作,该操作引用在函数中声明、打开、关闭和释放的局部游标。不允许使用 FETCH 语句将数据返回到客户端。仅允许使用 FETCH 语句通过 INTO 子句给局部变量赋值。

控制流语句。

SELECT 语句,该语句包含带有表达式的选择列表,其中的表达式将值赋予函数的局部变量。

INSERT、UPDATE 和 DELETE 语句,这些语句修改函数的局部 table 变量。

EXECUTE 语句,该语句调用扩展存储过程。
在查询中指定的函数的实际执行次数在优化器生成的执行计划间可能不同。示例为 WHERE 子句中的子查询唤醒调用的函数。子查询及其函数执行的次数会因优化器选择的访问路径而异。

用户定义函数中不允许使用会对每个调用返回不同数据的内置函数。用户定义函数中不允许使用以下内置函数:

@@CONNECTIONS @@PACK_SENT GETDATE
@@CPU_BUSY @@PACKET_ERRORS GetUTCDate
@@IDLE @@TIMETICKS NEWID
@@IO_BUSY @@TOTAL_ERRORS RAND
@@MAX_CONNECTIONS @@TOTAL_READ TEXTPTR
@@PACK_RECEIVED @@TOTAL_WRITE


架构绑定函数
CREATE FUNCTION 支持 SCHEMABINDING 子句,后者可将函数绑定到它引用的任何对象(如表、视图和其它用户定义函数)的架构。尝试对架构绑定函数所引用的任何对象执行 ALTER 或 DROP 都将失败。

必须满足以下条件才能在 CREATE FUNCTION 中指定 SCHEMABINDING:

该函数所引用的所有视图和用户定义函数必须是绑定到架构的。

该函数所引用的所有对象必须与函数位于同一数据库中。必须使用由一部分或两部分构成的名称来引用对象。

必须具有对该函数中引用的所有对象(表、视图和用户定义函数)的 REFERENCES 权限。
可使用 ALTER FUNCTION 删除架构绑定。ALTER FUNCTION 语句将通过不带 WITH SCHEMABINDING 指定函数来重新定义函数。

调用用户定义函数
当调用标量用户定义函数时,必须提供至少由两部分组成的名称:

SELECT *, MyUser.MyScalarFunction()FROM MyTable可以使用一个部分构成的名称调用表值函数:

SELECT *FROM MyTableFunction()然而,当调用返回表的 SQL Server 内置函数时,必须将前缀 :: 添加至函数名:

SELECT * FROM ::fn_helpcollations()可在 Transact-SQL 语句中所允许的函数返回的相同数据类型表达式所在的任何位置引用标量函数,包括计算列和 CHECK 约束定义。例如,下面的语句创建一个返回 decimal 的简单函数:

CREATE FUNCTION CubicVolume-- Input dimensions in centimeters     (@CubeLength decimal(4,1), @CubeWidth decimal(4,1),      @CubeHeight decimal(4,1) )RETURNS decimal(12,3) -- Cubic Centimeters.ASBEGIN     RETURN ( @CubeLength * @CubeWidth * @CubeHeight )END然后可以在允许整型表达式的任何地方(如表的计算列中)使用该函数:

CREATE TABLE Bricks     (      BrickPartNmbr     int PRIMARY KEY,      BrickColor        nchar(20),      BrickHeight       decimal(4,1),      BrickLength       decimal(4,1),      BrickWidth        decimal(4,1),      BrickVolume AS                (                 dbo.CubicVolume(BrickHeight,                           BrickLength, BrickWidth)                )     )dbo.CubicVolume 是返回标量值的用户定义函数的一个示例。RETURNS 子句定义由该函数返回的值的标量数据类型。BEGIN...END 块包含一个或多个执行该函数的 Transact-SQL 语句。该函数中的每个 RETURN 语句都必须具有一个参数,可返回具有在 RETURNS 子句中指定的数据类型(或可隐性转换为 RETURNS 中指定类型的数据类型)的数据值。RETURN 参数的值是该函数返回的值
分享到:
评论

相关推荐

    第11章MySQL存储过程与函数.docx

    MySQL 存储过程与函数 MySQL 存储过程和函数是数据库中定义的一组用户定义的 SQL 语句集合。它们之间的区别在于: 1. 存储过程实现的功能要复杂一点,而函数实现的功能针对性比较强。 2. 存储过程可以返回参数,而...

    Mysql存储过程和函数

    ### MySQL存储过程与函数详解 #### 一、概述 MySQL是一种广泛使用的开源关系型数据库管理系统,在数据管理和存储方面提供了强大的支持。其中,存储过程和函数是MySQL提供的两种重要的编程特性,它们可以帮助开发者...

    MySQL实验报告5(存储过程与函数)(1)(1).pdf

    函数与存储过程类似,也是一种子程序。不同之处在于函数必须返回一个值。报告中并没有直接提及函数的创建,但是存储过程中的某些操作可以通过函数来实现,比如获取表中的记录数等。 3. 使用游标 游标允许逐行遍历...

    mysql存储过程和存储函数

    mysql存储过程,存储函数练习,里面有详细代码,供大家参考

    精通MySQL存储过程和函数

    ### 精通MySQL存储过程和函数 #### 1. 说明 ##### 1.1 手册适用范围 本手册适用于对MySQL存储过程和函数感兴趣的开发者和技术人员,特别是那些希望深入了解并掌握如何在实际项目中高效利用这些功能的专业人士。...

    mysql存储过程和存储函数的示例

    mysql 存储过程 ,存储函数的调用示例

    Mysql存储过程和函数区别介绍

    MySQL中的存储过程和函数是两种不同的数据库编程工具,它们各自有着独特的特性和用途。下面将详细阐述这两者之间的差异。 1. **定义与用途** - **存储过程** 是一组预编译的SQL语句,由用户定义并保存在数据库中,...

    MySql存储过程和函数

    函数与存储过程相似,但它们的主要区别在于函数必须返回一个值,而存储过程可以没有返回值。在MySQL中,创建函数使用`CREATE FUNCTION`语句: ```sql CREATE FUNCTION func_name(param1 datatype) RETURNS datatype...

    mysql存储过程之返回多个值的方法示例

    mysql存储函数只返回一个值。要开发返回多个值的存储过程,需要使用带有INOUT或OUT参数的存储过程。咱们先来看一个orders表它的结构: mysql> desc orders; +----------------+-------------+------+-----+--------...

    Mysql手册20-存储过程和函数

    MySQL存储过程和函数是数据库编程的重要组成部分,它们提供了在数据库服务器上执行特定任务的能力。本部分详细介绍了存储程序和函数的各个方面,包括它们的创建、语法、权限控制以及与性能优化相关的概念。 首先,...

    第11章MySQL存储过程与函数.pdf

    1. 存储过程与存储函数的区别: - 存储过程功能更全面,可以处理复杂的业务逻辑,而函数通常用于执行特定任务。 - 存储过程可以有输入和输出参数,而函数仅能返回一个值或表对象。 - 非确定性函数不能在函数中...

    MySQL存储过程的异常处理方法

    本实例展示了如何在MySQL存储过程中实现异常处理,以捕获并处理可能出现的错误。 首先,我们注意到在创建存储过程`myProc`时,使用了`delimiter $$`来改变MySQL客户端的语句分隔符,这是为了在存储过程中使用多个...

    mysql存储过程教程

    MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预编译一系列SQL语句并封装成一个可重复使用的单元,从而提高数据处理的效率和代码的复用性。本教程将深入探讨MySQL存储过程的创建、调用以及相关概念...

    Mysql 存储过程(输入,返回),函数,临时表

    Mysql 存储过程,函数,临时表 存储过程包括输入返回,可供项目中遇到难题的人解决一下,我曾为此费了好大精力,最后总结出来共大家分享

    JAVA通过MyBatis调用MySql存储过程和函数doc文档合集整理.zip

    这个文档合集应该详细讲解了这些步骤和最佳实践,帮助开发者熟练掌握在Java中利用MyBatis调用MySQL存储过程和函数的技巧,从而更高效地进行数据库操作。通过深入学习和实践,开发者可以更好地理解这两者之间的交互,...

    MySQL数据库函数,触发器,存储过程

    通过深入学习和掌握MySQL的函数、触发器和存储过程,开发者能够更加高效地管理和操作数据库,解决复杂的数据处理问题,提升应用程序的性能。在实际项目中,熟练运用这些特性是数据库设计和优化的关键。通过"mysql...

    mysql存储函数以及函数过程

    mysql+零基础+mysql存储函数以及函数过程知识点

    mysql存储过程和函数(超经典)个人总结

    mysql存储过程和函数(超经典)个人总结,本人总结了mysql存储过程极其用到的函数,实用!

Global site tag (gtag.js) - Google Analytics