实验14 存储过程与函数

实验十四存储过程与函数

【实验目的与要求】

1．熟练掌握存储过程的编写。

2．熟练掌握函数的编写与使用。

【实验内容与步骤】

14.1.基础知识

存储过程（Stored Procedure）和函数是一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。客户端应用程序可以通过指定存储过程或函数的名字并给出参数（如果该存储过程带有参数）来执行存储过程。

14.2.创建用户存储过程

1. 使用存储过程模板创建存储过程

在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“存储过程”，选择“新建存储过程”命令，如图所示：

在右侧查询编辑器中出现存储过程的模板，可以在此基础上编辑存储过程，单击“执行”按钮，即可创建该存储过程。

实验14-1：创建一个简单的存储过程，实现从CP表中读取Mp3产品相关信息USE CPXS

GO

CREATE PROCEDURE get_mp3 --此为无参存储过程

AS

SELECT产品编号,产品名称,价格,库存量

FROM CP

WHERE产品名称='mp3'

实验14-2：执行存储过程

执行存储过程可用下列方法之一：

（1）使用存储过程名字如：get_mp3

（2) 使用Exec命令：如：EXEC get_mp3

执行上面创建的存储过程，并给出执行结果：

2. 使用T-SQL语句创建存储过程

在查询分析器里使用T-SQL可直接创建存储过程格式：

CREATE PROC 过程名

@形参名类型

@变参名类型OUTPUT

AS

SQL语句

实验14-3：创建一个多表查询的存储过程。

问题：查询在2009年9月18日有销售的产品名称（1）请给出相应的代码

create proc get_cp

as

select产品名称

from cp,cpxsb

where销售日期='2009-9-18'

（2）执行存储过程,并给出执行结果：

（cpxsb中无销售日期为20090918的数据）14.3.存储过程的参数

1. 输入参数（值参）

实验14-4：输入参数为某产品的名字。

USE CPXS

GO

CREATE PROCEDURE P_CPXS2

@Product_name char(30)--形式参数

As

SELECT产品编号,产品名称,价格,库存量

FROM CP

WHERE 产品名称= @ Product_name

GO

执行存储过程：

（1）直接传值：

EXEC P_CPXS2 '冰箱'--实参表

请给出执行结果：

（2）变量传值：

DECLARE @temp char(30)

SET @temp='洗衣机'

EXEC P_CPXS2 @temp --实参表

请给出执行结果：

实验14-5：使用默认参数

阅读以下程序段，理解参数传递过程

USE CPXS

GO

CREATE PROCEDURE P_CPXS3

@name varchar(10)=NULL--默认参数AS

IF @name IS NULL

SELECT 产品编号,产品名称,价格,库存量

FROM CP

ELSE

SELECT 产品编号,产品名称,价格,库存量

FROM CP

WHERE 产品名称=@name

GO

执行存储过程：

(1) 不带参数时

EXEC P_CPXS3

请给出执行结果：

(2) 不带参数时

EXEC P_CPXS3 ‘彩电’

请给出执行结果：

2. 输出参数（变参）

实验14-6：利用输出参数计算阶乘。

USE CPXS

–判断系统中是否有名为factorial的存储过程，若有，则删除之

IF EXISTS(SELECT name FROM sysobjects

WHERE name='factorial'AND type='P')

DROP PROCEDURE factorial

GO --前面这段仅是准备工作，真正的工作在之后

CREATE PROCEDURE factorial --创建存储过程

@in float,--输入形式参数

@out float OUTPUT--输出形式参数

AS

DECLARE @i int

DECLARE @s float

SET @i=1

SET @s=1

WHILE @i<=@in

BEGIN

SET @s=@s*@i

SET @i=@i+1

END

SET @out=@s --给输出参数赋值

调用存储过程：

DECLARE @ou float–定义变量，用于存储结果值

EXEC factorial 5,@ou OUT --实参表

PRINT @ou

请给出执行结果：

14.4.创建用户自定义函数

1. 使用存储过程模板创建存储过程

在【对象资源管理器】窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，再展开选择“可编程性”节点，右击“函数”，选择“新建”命令，在下一级菜单中选择适合的选项，如图所示：

在右侧查询编辑器中出现函数的模板，可以在此基础上编辑函数，单击“执行”按钮，即可创建该函数。

2. 使用T-SQL语句创建函数

函数分为标量函数和表值函数。前者返回一个标量值结果，在创建函数时，应在Returns 语句后指明标量类型(如：int )；后者以表的形式返回结果，在创建函数时，应在Returns 语句后用关键词Table指时其反回类型。

在查询分析器里可使用T-SQL可直接创建函数

格式：

CREATE FUNCTION 函数名(

@形参名类型

@变参名类型

)

[RETURNS 类型]

AS

SQL语句

3. 使用标量函数

标量函数接受0个或多个输入参数，并返回一个标量值。因为标量函数只返回一个值，所以通常在一个select语句的列列表中使用它们，也可以在where子句中使用它们。

实验14-7：基于CP表编写函数getStock( )，根据传进的参数“产品编号”，查询并返回相应产品的“库存量”。

请阅读以下程序，理解其基本结构和实现思想，给出运行结果。

调用函数：

函数一般在Select语句或Where子句中被调用，以下是一函数调用实例：

请给出执行结果：

4. 使用表值函数

表值函数遵守与标量函数相同的规则，区别在于表值函数返回一个表作为输出。因此，一般在select语句的from子句中使用它们，并可能与其他表或视图进行联接。

实验14-8：创建一个自定义函数fun_cpInfo()，根据产品编号返回该产品的名称、价格和库存量。

create function fun_cpInfo(@product_No char(6))

returns table --表值函数，返回查询结果集(即表)

as

return( select 产品名称,价格,库存量

from CP

where 产品编号=@product_No)

调用函数：

函数创建后，可在SQL语句中调用。调用函数fun_cpInfo()，可在查询分析器中执行如下Select语句：

请给出执行结果：

思考：请比较标量函数和表值函数，理解其在编写和使用上的差别。

14.5.实验练习

14.5.1 存储过程

对于CPXS数据库，完成以下存储过程。

1. 无参存储过程

编写一无参存储过程用于查询每个客户购买产品的情况（包括客户编号、产品编号、客户名称、产品名称、价格、购买日期、购买数量），然后调用该存储过程。

请给出程序源码：

create proc kh_cp_info

as

select cpxsb.客户编号,cpxsb.产品编号,客户名称,产品名称,价格

,销售日期,数量

from cp,xss,cpxsb

where cp.产品编号=cpxsb.产品编号

and xss.客户编号=cpxsb.客户编号

请给出执行测试结果：

2. 带有参数的存储过程

编写一加密存储过程，查询指定客户购买产品的情况。并调用该存储过程查询客户编号为“000002”的客户购买情况。

请给出程序源码：

create proc kh_info

@khbh char(6)

with encryption

as

select cpxsb.客户编号,cpxsb.产品编号,客户名称,产品名称,价格

,销售日期,数量

from cp,xss,cpxsb

where xss.客户编号=@khbh

and cp.产品编号=cpxsb.产品编号

and xss.客户编号=cpxsb.客户编号

请给出执行测试结果：

3. 带有通配符参数的存储过程

编写一存储过程，查询指定产品的销售情况。如果没有提供参数，则查询产品名称中包含有“冰箱”的产品销售情况。

请给出程序源码：

create proc cp_infor

@mc varchar(20)='%冰箱%'

as

select cpxsb.客户编号,cpxsb.产品编号,客户名称,产品名称,价格

,销售日期,数量

from cp,xss,cpxsb

where

cpxsb.产品编号in

(select产品编号from cp where产品名称like @mc)

and cp.产品编号=cpxsb.产品编号

and xss.客户编号=cpxsb.客户编号

请给出执行测试结果：

4. 带有OUTPUT参数的存储过程

编写一存储过程，查询指定客户在指定时间段内购买指定产品的数量，存储过程中使用了输入和输出参数。并调用该存储过程查询名称为“家电市场”的客户在2004年购买“洗衣机”的数量。

请给出程序源码：

请给出执行测试结果：

5. 带有OUTPUT游标参数的存储过程

编写一带有OUTPUT游标参数的存储过程，游标结果集为客户信息，并通过调用该存储过程，实现依次读取游标CUR2中各行数据。

请给出程序源码：

请给出执行测试结果：

6. 创建一个多表查询的存储过程。

问题：查询在2009年9月18日有销售的产品名称(若无此数据，请先添加之，以便于测试)。

（1）请给出相应的代码

create function cp_name(@cp_name char(10))

returns table

as

return

(select产品名称

from cp,cpxsb

where销售日期=@time and cp.产品编号=cpxsb.产品编号

)

（2）执行存储过程,并给出执行结果：

14.5.2 函数

对于CPXS数据库，定义完成如下功能的函数。

1.据产品名称，查询该产品的相关信息。（函数名为：FU_CP）

请给出程序源码：

create function fu_cp(@cp_name char(10))

returns table

as

return

(select*from CP

where产品名称=@cp_name)

请给出执行测试结果：查询产品名称为“mp3”的产品情况

2.按某年某季度统计给定产品名称的销售数量及销售金额。(函数名为：FU1_CPXS)

请给出程序源码：

create function FU1_CPXS (@cp_name char(20),@year int,@quarter int) returns table

as

return

(select sum(数量)as销售数量,

sum(销售额)as销售总额

from cp,xss,cpxsb

where cp.产品编号=cpxsb.产品编号

and xss.客户编号=cpxsb.客户编号

and datepart(year,销售日期)=@year

and datepart(quarter,销售日期)=@quarter

and产品名称=@cp_name

group by产品名称)

请给出执行测试结果：查询2004年第3季度彩色电视机的销售数量和销售金额

(数据库中无第三季度的数据，改为查询第一季度)

3.根据销售商名称，统计其在某年某季度内销售商品名称、数量及金额。（函数名为FU2_CPXS）

请给出程序源码：

Create function FU2_CPXS (@kh_name char(20),@year int,@quarter int)

returns table

as

return

(select产品名称,sum(数量)as销售数量,

sum(销售额)as销售总额

from cp,xss,cpxsb

where cp.产品编号=cpxsb.产品编号

and xss.客户编号=cpxsb.客户编号

and datepart(year,销售日期)=@year

and datepart(quarter,销售日期)=@quarter

and客户名称=@kh_name

group by产品名称)

请给出执行测试结果：查询广电公司2004年第1季度销售的产品名称、销售数量和销售金额。