实验10： 存储过程和存储函数

实验十函数一、实验目的与要求⏹理解什么是标量函数、内嵌表值函数及多语句表值函数。

⏹熟练掌握标量函数、内嵌表值函数、多语句表值函数的定义和调用。

⏹基表如下所示：⏹二、实验内容和步骤1、函数的定义对于CPXS数据库，定义完成如下功能的函数：（1）据产品名称，查询该产品的相关信息；（函数名为FU_CP）use cpxs1gocreate function FU_CP(@productname char(30))returns tableasreturn(select * from CPXSB right join XSS on CPXSB.客户编号=XSS.客户编号right join CP on CPXSB.产品编号=CP.产品编号where 产品名称=@productname)Gouse cpxs1goselect * from FU_CP('彩色电视机')go（2）按某年某季度统计给定产品名称的销售数量及销售金额；分别用名为FU1_CPXS内嵌表值函数和名为FU2_CPXS的多语句表值函数。

use cpxs1gocreate function FU12_CPXS(@year int ,@quarter int, @cpmc char(10))returns tableasreturn(select 产品名称, sum(数量) as 销售数量, sum(销售额) as 销售总额from cpxsb,cpwhere cpxsb.产品编号=cp.产品编号and 产品名称=@cpmcand datepart(year ,销售日期)=@yearand datepart(quarter,销售日期)=@quarter)Gouse cpxs1gocreate function FU2_SPXS(@year int ,@quarter int ,@cpmc char(10)) returns @xsqk table(产品名称char (10),销售数量int ,销售金额int)asbegin insert into @xsqkselect 产品名称,sum(销售额)as 销售总额,sum(数量)as 销售数量from cpxsb,cpwhere cpxsb.产品编号=cp.产品编号and 产品名称=@cpmcand datepart (year ,销售日期)=@yearand datepart (quarter, 销售日期)=@quartergroup by 产品名称returnendgo（3）根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。

存储过程与函数的构建与使用存储过程和函数是数据库中常用的两种程序化对象，它们都可以用来封装一定的复杂业务逻辑，在数据库中进行复用，提高数据库的性能和可维护性。

1. 存储过程的构建和使用存储过程是一种预编译的数据库对象，可以用来执行一些具体的操作。

在构建存储过程时，需要用到以下的语法结构：CREATE PROCEDURE procedure_name@parameter datatype(size) = default_value,ASBEGINSQL statementsEND1. 创建存储过程的语法是“CREATE PROCEDURE 存储过程名”。

其中，存储过程名是自己定义的，应该符合命名规范。

2. 存储过程可以包含输入输出参数，所以需要在存储过程中定义参数的数据类型和默认值。

3. SQL语句块始终包含在BEGIN和END语句之间，并以AS语句开头。

构建完存储过程后，就可以使用以下的语句来调用存储过程：EXEC procedure_name parameter1, parameter2, ...其中，parameter1、parameter2等是存储过程中定义的参数。

执行上述语句后，存储过程会按照自己的逻辑进行处理。

2. 函数的构建和使用函数是一种特殊的存储过程，它返回一个值，常用于数据处理过程中。

在构建函数时，需要用到以下的语法结构：CREATE FUNCTION function_name (@parameter datatype(size)) RETURNS datatype(size)ASBEGINSQL statementsEND1. 函数的创建语法是“CREATE FUNCTION 函数名”。

函数名应该符合命名规范。

2. 函数返回一个值，因此需要在函数中定义返回值的数据类型。

3. SQL语句块始终包含在BEGIN和END语句之间，并以AS语句开头。

构建完函数后，就可以使用以下的语句来调用存储过程：SELECT dbo.function_name(parameter)其中，parameter是函数中定义的参数。

数据库中函数和存储过程的区别先看⼀下存储过程和函数的定义。

存储过程:存储过程可以使得对数据库的管理、以及显⽰关于数据库及其⽤户信息的⼯作容易得多。

存储过程是 SQL 语句和可选控制流语句的预编译集合，以⼀个名称存储并作为⼀个单元处理。

存储过程存储在数据库内，可由应⽤程序通过⼀个调⽤执⾏，⽽且允许⽤户声明变量、有条件执⾏以及其它强⼤的编程功能。

存储过程可包含程序流、逻辑以及对数据库的查询。

它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

可以出于任何使⽤ SQL 语句的⽬的来使⽤存储过程，它具有以下优点：1、可以在单个存储过程中执⾏⼀系列 SQL 语句。

2、可以从⾃⼰的存储过程内引⽤其它存储过程，这可以简化⼀系列复杂语句。

3、存储过程在创建时即在服务器上进⾏编译，所以执⾏起来⽐单个 SQL 语句快。

⽤户定义函数：Microsoft SQL Server 2000 允许创建⽤户定义函数。

与任何函数⼀样，⽤户定义函数是可返回值的例程。

根据所返回值的类型，每个⽤户定义函数可分成以下三个类别：1、返回可更新数据表的函数如果⽤户定义函数包含单个 SELECT 语句且该语句可更新，则该函数返回的表格格式结果也可以更新。

2、返回不可更新数据表的函数如果⽤户定义函数包含不⽌⼀个 SELECT 语句，或包含⼀个不可更新的 SELECT 语句，则该函数返回的表格格式结果也不可更新。

3、返回标量值的函数⽤户定义函数可以返回标量值。

区别：1.⼀般来说，存储过程实现的功能要复杂⼀点，⽽函数的实现的功能针对性⽐较强。

2.对于存储过程来说可以返回参数，⽽函数只能返回值或者表对象。

3.存储过程⼀般是作为⼀个独⽴的部分来执⾏，⽽函数可以作为查询语句的⼀个部分来调⽤，由于函数可以返回⼀个表对象，因此它可以在查询语句中位于FROM关键字的后⾯。

4.当存储过程和函数被执⾏的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在procedure cache⾥没有相应的查询语句，SQL Manager就会对存储过程和函数进⾏编译。

第10章存储过程存储过程是SQL语句和可选控制流语句的预编译集合，它以一个名称存储并作为一个单元处理。

本章介绍存储过程的创建、执行、修改和删除等。

10.1 概述存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其他强大的编程功能。

存储过程可以使对数据库的管理，以及显示关于数据库及其用户信息的工作容易得多。

存储过程可包含程序流、逻辑以及对数据库的查询。

它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

可以出于任何使用SQL语句的目的来使用存储过程，它具有以下优点：可以在单个存储过程中执行一系列SQL语句。

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

存储过程在创建时即在服务器上进行编译，所以执行起来比单个SQL语句快，且能减少网络通信的负担。

10.2创建存储过程要使用存储过程，首先要创建一个存储过程。

可以使用Transact-SQL 语言的CREATE PROCEDURE语句，也可以使用企业管理器或者存储过程创建向导来完成。

1．使用CREATE PROCEDURE语句创建存储过程CREATE PROCEDURE语句的语法格式为：CREATE PROC[EDURE ] procedure_name [; number][ {@parameter data_type}[VARYING ][ = default][OUTPUT]][,…n][WITH{RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION}][FOR REPLICATION]AS sql_statement […n ]其中各参数含义如下：procedure_name新存储过程的名称。

number 是可选的整数，用来对同名的过程分组，以便用一条DROP PROCEDURE语句即可将同组的过程一起除去。

例如，名为orders的应用程序使用的过程可以命名为orderproc;1、orderproc;2等。