实验14 存储过程和函数.docVIP

实验十四　存储过程与函数 【实验目的与要求】 熟练掌握存储过程的编写。 熟练掌握函数的编写与使用。 【实验内容与步骤】 基础知识 存储过程（Stored Procedure）和函数是一组编译好存储在服务器上的完成特定功能T-SQL代码，是某数据库的对象。客户端应用程序可以通过指定存储过程或函数的名字并给出参数（如果该存储过程带有参数）来执行存储过程。 创建用户存储过程 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中无销售日期数据） 存储过程的参数 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 --实参表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函数分为标量函数和表值函数。前者返回一个标量值结果，后者以表的形式返