注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

北边的风

IT 钓鱼 养生

 
 
 

日志

 
 

sql 存储过程调用  

2015-10-16 13:06:05|  分类: IT心得 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

 12.3.3     执行存储过程

当需要执行存储过程时,可使用 EXECUTE  EXEC 语句。如果存储过程是批处理中 的第一条语句,那么不使用EXECUTE   EXEC 关键字也可以执行该存储过程。

如果存储过程编写为可以接受参数值,那么可以提供参数值。 任务 12.4

问题描述:执行任务 12.3 创建的存储过程 Insertproduct,向产品表中追加一行。 解决方案 1

EXEC Insertproduct 8,'Konbu', '2 kg box', 24,6.0000, 5,10,0

解决方案 2

EXEC Insertproduct 8,'Konbu', '2 kg box', 24,6.0000, 5,10

解决方案 3

EXEC Insertproduct default,'Konbu', '2 kg box', 24,6.0000, 5,10

解决方案 4

EXEC Insertproduct @ProductName='Konbu',@QuantityPerUnit='2 kg box',

@UnitsInStock=24,@UnitPrice=6.0000,@UnitsOnOrder=5,@ReorderLevel=10

解决方案 5

EXEC Insertproduct @ReorderLevel=10, @UnitsOnOrder=5,

@UnitsInStock=24,@UnitPrice=6.0000, @QuantityPerUnit='2 kg box',@ProductName='Konbu'

 

分析与讨论:

1.如果存储过程含有参数,则在执行存储过程时,必须为这些参数提供值(具有默认值 的参数可以不提供值而且提供的值必须是确定的值例如方案 1必须按照 CREATE PROCEDURE 语句中存储过程参数给出的顺序提供参数值,以下语句就会发生错误,因为 不是按照顺序提供参数值。

EXEC Insertproduct 'Konbu',8,'2 kg box', 24,6.0000, 5,10,0

2.如果存储过程含有默认值参数,则在执行存储过程时,可省略为默认值参数提供值, 但其它参数的值不能够省略,必须为这些参数提供值。例如方案 2,没有为@Discontinued 参数提供值,则在执行存储过程时该参数的值为其默认值 1.

3.虽然可以省略已提供默认值的参数,但只能截断参数列表。例如,如果一个存储过程 有八个参数可以省略第七个和第八个参数但不能跳过第七个参数而仍然包含第八个参数, 除非以 @parameter = value  形式提供参数(参见 4)。例如,方案 2中,@CategoryID 参数 尽管是有默认值的参数但由于它是第一个参数而存储过程第二个参数必须提供值因此 执行存储过程时要为@CategoryID 参数提供值不能省略如果要使用默认值作为参数的值, 则可以使用 DEFAULT 关键字,例如方案3

如果在存储过程中定义了参数的默认值,那么下列情况下将使用默认值:

1)执行存储过程时未指定参数值。

2)将 DEFAULT  关键字指定为参数值。

4.  执行存储过程时,既可以通过提供在 CREATE PROCEDURE  语句中给定的参数值

(如解决方案 1,解决方案 2),来向存储过程参数传递值。也可以通过显式指定参数名称 并分配适当的值(使用@parameter = value 的形式),来给存储过程参数传递值。

如果使用 @parameter = value 的形式,那么可以按任意顺序提供参数,还可以省略那 些已提供默认值的所有参数。注意,如果以 @parameter = value 形式提供了一个参数,就 必须按此种形式提供后面所有的参数。例如解决方案 和解决方案 5,执行存储过程时存储  @ProductName   数的值 'Konbu'  @QuantityPerUnit  参数的值 '2  kg  box',

@UnitsInStock  参数的值为 24,@UnitPrice  参数的值为 6.0000,@UnitsOnOrder  参数的值为


 

5,@ReorderLevel 参数的值为 10,没有提供@CategoryID @Discontinued 参数的值,它们 的值为其默认值。

执行存储过程时如果参数名称和存储过程的参数名称不匹配则这些参数都不会被接 受。例如以下代码参数名称@ReorderLevelA  和存储过程的参数名称不匹配, 则参数

@ReorderLevelA 不会被接受。

EXEC Insertproduct @ReorderLevelA=10, @UnitsOnOrder=5,

@UnitsInStock=24,@UnitPrice=6.0000, @QuantityPerUnit='2 kg box',@ProductName='Konbu' 执行存储过程时,服务器将拒绝所有未包含在存储过程创建期间的参数列表中的参数。

注意在执行存储过程时指定参数名称允许按任意顺序提供参数值如果未指定参数名 称则必须按照参数在存储过程中定义时的顺(从左至右来提供参数(按照位置递 参数值),并且,如果给某一参数提供了值,则该参数前面的所有参数必须为它们提供值, 即使这些参数有默认值(如方案 2)。

12.4.1     使用变量、IF 语句和 RETURN 语句

变量用来存储程序在运行过程中可以使用的值用户可以定义变量用户定义的变量称 为局部变量。

IF 语句根据表达式的值选择要执行的语句。IF 语句的一般表示形式为:

IF (表达式) BEGIN

语句块;

END

IF 语句的执行方式如下如果表达式的值为 TRUE则执行语句块否则语句块不会被 执行。语句块可为一条或多条语句,如果句块可为一条语句,可省略 BEGINEND

任务 12.6

问题描述: 创建一个存储过程,用参数指定的产品类别 ID 的值查询该类的所有产品, 并测试错误,如果存储过程成功执行,则存储过程返回 0,如果执行存储过程时,执行存储 过程中的 SQL 语句发生错误,则存储过程返回返回错误号。并创建代码测试该程序过程。

解决方案:

1)创建 ProductsByCategory1 存储过程

 

CREATE Procedure ProductsByCategory1 (

@CategoryID int

) AS

DECLARE @ErrorSave int; SET @ErrorSave = 0; SELECT         * FROM  Products

WHERE CategoryID = @CategoryID ORDER BY ProductName;

IF (@@ERROR <> 0) BEGIN

SET @ErrorSave = @@ERROR; END

RETURN @ErrorSave; GO

 

2)创建测试代码

 

DECLARE @ReturnStatus int;

EXECUTE @ReturnStatus =ProductsByCategory1 1; SELECT'Return code = ' + CAST(@ReturnStatus AS CHAR(10)); GO


 

分析与讨论:

1.声明变量。使用 DECLARE  语句声明变量,其格式如下:

DECLARE 变量名 数据类型和长度

变量名的第一个字符必须为一个 @。声明变量后变量的值初始化为 NULL 变量不能是 textntext  image  数据类型。

下面的语句创建了一个名为@ErrorSave 的变量,该变量的数据类型为 int,变量的值设 置为 NULL

DECLARE @ErrorSave int;

若要声明多个局部变量可在定义的第一个变量后使用一个逗号然后指定下一个变量 名称和 据类型 例如,DECLARE  语句创 了三个  @ProductName 

@QuantityPerUnit e @ProductID 的变量,并将每个变量都初始化为 NULL

DECLARE @ProductName nvarchar(30), @QuantityPerUnit nvarchar(20),

@ProductID int;

变量的作用域就是可以引用该变量的 SQL 语句的范围。变量的作用域从声明变量的地 方开始到声明变量的批处理或存储过程的结尾。

2.  变量赋值。可使用 SET  语句为变量赋值,例如,下面的语句将值  赋给变量

@ErrorSave。其中,“=”为赋值运算符。

SET @ErrorSave = 0;

3.输出变量的值。可以使用 SELECT  语句变量的值。例如以下示例首先声明变量

@ReturnStatus,然后执行存储过程,将存储过程 ProductsByCategory1 的返回值赋给

@ReturnStatus 变量,最后输出变量@ReturnStatus 的值:

DECLARE @ReturnStatus int;

EXECUTE @ReturnStatus =ProductsByCategory1 1; SELECT @ReturnStatus;

也可以使用 SELECT 语句输出表达式的值。例如:

SELECT'Return code = ' + CAST(@ReturnStatus AS CHAR(10));

如果使用 SELECT 输出多个变量或表达式的值,则变量或表达式之间用逗号分隔。例 如:

SELECT @ProductName, @QuantityPerUnit, @ProductID; SELECT 表达式 1, 表达式 2, 表达式 2;

 

4. 使用 @@ERROR@@ERROR 系统函数它用来检测它的上一条 SQ语句是否 执行成功,如果上一条 SQL 语句执行成功,则@@ERROR 系统函数返回 0;如果上一条 SQL 语句语句生成错误,@@ERROR  将返回错误号。

@@ERROR 通常用于检测存储过程是成功还是失败。整数变量(如@ErrorSave)被初 始化为 0。在每个 SQL 语句完成之后,立即测试 @@ERROR  是否为 0,如果不是 0,将

@@ERROR 保存到整数变量中。然后,存储过程将在 RETURN 语句中返回变量。如果过 程中的 SQL 语句都没有错误,变量将保持为 0。如果一个或多个语句生成错误,变量将包 含最后一个错误号。

5. IF  语句。如果 IF  语句中条件表达式的值为 TRUE  ,则执行 IF  语句后的语句或语 句块。如果 IF  语句中条件表达式的值为 FALSE  ,则跳过 IF  语句后的语句或语句块。

例如,在以上存储过程中,如果@@ERROR 的值为 0,则条件表达式@@ERROR <> 0 的值为 FALSE,这时不执行 SET @ErrorSave = @@ERROR 语句。如果@@ERROR 的值不  0,则条件表达式@@ERROR <> 0 的值 TRUE,这时执行 SET @ErrorSave = @@ERROR 语句。该语句将@@ERROR 的值赋给变量@ErrorSave

6. 使用 BEGIN...ENDBEGI和 END 用于定义语句块它们可以用于定义一系列一 起执行的 SQL 语句。BEGIN END 语句必须成对使用,任何一个均不能单独使用。 BEGIN 语句单独出现在一行中,后跟 SQL  语句块。最后,END  语句单独出现在一行中,


 

指示语句块的结束。

BEGIN 和 END  语句块必须至少包含一条 SQL  语句。

7. RETURN 语句。

RETURN  句无条件从查询存储过程或批处理中退出RETURN  语句后面的语句都 不执行。RETURN  语句的一般格式为:

RETURN integer_expression

其中 integer_expression  为返回的整数值或整型表达式。

当在存储过程中使用 RETURN  语句时此语句可以指定返回给调用程序批处理或过 程的整数值。如果 RETURN  未指定值,则存储过程返回 0

以上 ProductsByCategory1 存储过程中,当执行 RETURN  @ErrorSave 语句时,退出存 储过程,并将@ErrorSave 作为存储过程的值返回。

8. SQL  语句中可包含存储过程返回的值,但必须以下列格式输入:

EXECUTE @return_status <procedure_name> 例如:

EXECUTE @ReturnStatus =ProductsByCategory1 1;

该语句执行存储过程,并将存储过程的返回值赋给变量@ReturnStatus

  评论这张
 
阅读(173)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016