SQLServer 存储过程返回值总结
1. 存储过程没有返回值的情况 (即存储过程语句中没有 return 之类的语句用方法 int count = ExecuteNonQuery(..执行存储过程其返回值只有两种情况
(1假如通过查询分析器执行该存储过程,在显示栏中假如有影响的行数,则影响几行 count 就是几
(2假如通过查询分析器执行该存储过程, 在显示栏中假如显示 ' 命令已成功完成。 ' 则 count = -1;在显示栏中假如有查询结果,则 count = -1
总结:A.ExecuteNonQuery(该方法只返回影响的行数,假如没有影响行数,则该方法的返回值只能是 -1,不会为 0。
B.不论 ExecuteNonQuery(方法是按照
CommandType.StoredProcedure 或者 CommandType.Text 执行, 其效果和 A 一样。 ---------------------------------------------------------------------------------------------------------------------------------------------------
2. 获得存储过程的返回值 --通过查询分析器获得
(1不带任何参数的存储过程 (存储过程语句中含有 return
---创建存储过程
CREATE PROCEDURE testReturn
AS
return 145
GO
---执行存储过程
DECLARE @RC int
exec @RC=testReturn
select @RC
---说明
查询结果为 145
(2带输入参数的存储过程 (存储过程语句中含有 return ---创建存储过程
create procedure sp_add_table1
@in_name varchar(100,
@in_addr varchar(100,
@in_tel varchar(100
as
if(@in_name = '' or @in_name is null
return 1
else
begin
insert into table1(name,addr,tel
values(@in_name,@in_addr,@in_tel
return 0
end
---执行存储过程
<1>执行下列,返回 1
declare @count int exec @count = sp_add_table1 '','中三路 ','123456' select @count <2>执行下列,返回 0
declare @count int exec @count = sp_add_table1 '','中三路 ','123456' select @count ---说明
查询结果不是 0就是 1
(3带输出参数的存储过程 (存储过程中可以有 return 可以没有 return
例子 A :
---创建存储过程
create procedure sp_output
@output int output
as
set @output = 121
return 1
---执行存储过程
<1>执行下列,返回 121
declare @out int
exec sp_output @out output
select @out
<2>执行下列,返回 1
declare @out int
declare @count int
exec @count = sp_output @out output
select @count
---说明
有 return ,只要查询输出参数,则查询结果为输出参数在存储过程中最后变成的值;只要不查询输出参数,则查询结果为 return 返回的值
例子 B :
---创建存储过程
create procedure sp_output
@output int output
as
set @output = 121
---执行存储过程
<1>执行下列,返回 121
declare @out int
exec sp_output @out output
select @out
<2>执行下列,返回 0
declare @out int
declare @count int
exec @count = sp_output @out output
select @count
---说明
没有 return ,只要查询输出参数,则查询结果为输出参数在存储过程中最后变成的值;只要不查询输出参数,则查询结果为 0
总结:
(1存储过程共分为 3类:
A. 返回记录集的存储过程 ---------------------------其执行结果是一个记录集,例如:从数据库中检索出符合某一个或几个条件的记录
B. 返回数值的存储过程 (也可以称为标量存储过程 -----其执行完以后返回一个值,例如:在数据库中执行一个有返回值的函数或命令
C. 行为存储过程 -----------------------------------用来实现数据库的某个功能,而没有返回值,例如:在数据库中的更新和删除操作
(2含有 return 的存储过程其返回值为 return 返回的那个值
(3没有 return 的存储过程,不论执行结果有无记录集,其返回值是 0
(4带输出参数的存储过程:假如有 return 则返回 return 返回的那个值,假如要select 输出参数,则出现输出参数的值,于有无 return 无关
---------------------------------------------------------------------------------------------------------------------------------------------------
3. 获得存储过程的返回值 --通过程序获得
---------------------------------------------------------------------------------------------------------------------------------------------------
SqlParameter[] cmdParms = { .. ,new
SqlParameter("@return",SqlDbType.Int};
cmdParms[cmdParms.Length - 1].Direction = ParameterDirection.ReturnValue; 或者
cmdParms[cmdParms.Length - 1].Direction = ParameterDirection.Output或者
cmdParms[cmdParms.Length - 1].Direction = ParameterDirection.Input; 得到返回值 object bj = cmdParms[cmdParms.Length - 1].Value;
可能有不少朋友使用SQL SERVER做开发也已经有段日子，但还没有或者很少在项目中使用存储过程，或许有些朋友认为根本没有必要使用存储过程等等。其实当你一个项目做完到了维护阶段时，就会发现存储过程给我们带来了好处了，修改方便，不能去改我们的应用程序，只需要改存储过程的内容，而且还可以使我们的程序速度得到提高。 QUOTE: SQL SERVER 联机丛书中的定义： 存储过程是保存起来的可以接受和返回用户提供的参数的 Transact-SQL 语句的集合。 可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。 也可以创建在 Microsoft SQL Server 启动时自动运行的存储过程。 要使用存储过程，首先我们必需熟悉一些基本的T-SQL语句，因为存储过程是由于一组T-SQL语句构成的，并且，我们需要了解一些关于函数、过程的概念，因为我们需要在应用程序中调用存储过程，就像我们调用应用程序的函数一样，不过调用的方法有些不同。 下面我们来看一下存储过程的建立和使用方法。 一、创建存储过程 和数据表一样，在使用之前我们需要创建存储过程，它的简明语法是： QUOTE: CREATE PROC 存储过程名称 [参数列表（多个以“,”分隔）] AS SQL 语句 例： QUOTE: CREATE PROC upGetUserName @intUserId INT, @ostrUserName NVARCHAR(20) OUTPUT -- 要输出的参数 AS BEGIN -- 将uName的值赋给 @ostrUserName 变量，即要输出的参数 SELECT @ostrUserName=uName FROM uUser WHERE uId=@intUserId END 其中 CREATE PROC 语句（完整语句为CREATE PROCEDURE）的意思就是告诉SQL SERVER，
SQLServer 存储过程返回值总结 1. 存储过程没有返回值的情况 (即存储过程语句中没有 return 之类的语句用方法 int count = ExecuteNonQuery(..执行存储过程其返回值只有两种情况 (1假如通过查询分析器执行该存储过程,在显示栏中假如有影响的行数,则影响几行 count 就是几 (2假如通过查询分析器执行该存储过程, 在显示栏中假如显示 ' 命令已成功完成。 ' 则 count = -1;在显示栏中假如有查询结果,则 count = -1 总结:A.ExecuteNonQuery(该方法只返回影响的行数,假如没有影响行数,则该方法的返回值只能是 -1,不会为 0。 B.不论 ExecuteNonQuery(方法是按照 CommandType.StoredProcedure 或者 CommandType.Text 执行, 其效果和 A 一样。 --------------------------------------------------------------------------------------------------------------------------------------------------- 2. 获得存储过程的返回值 --通过查询分析器获得 (1不带任何参数的存储过程 (存储过程语句中含有 return ---创建存储过程 CREATE PROCEDURE testReturn AS return 145 GO ---执行存储过程
DECLARE @RC int exec @RC=testReturn select @RC ---说明 查询结果为 145 (2带输入参数的存储过程 (存储过程语句中含有 return ---创建存储过程 create procedure sp_add_table1 @in_name varchar(100, @in_addr varchar(100, @in_tel varchar(100 as if(@in_name = '' or @in_name is null return 1 else begin insert into table1(name,addr,tel values(@in_name,@in_addr,@in_tel return 0
C#调用存储过程简单完整例子https://www.doczj.com/doc/0f3159713.html,/itblog/article/details/752869 创建存储过程 Create Proc dbo.存储过程名 存储过程参数 AS 执行语句 RETURN 执行存储过程 GO DECLARE @iRet INT, @PKDisp VARCHAR(20) SET @iRet = '1' Select @iRet = CASE WHEN @PKDisp = '一' THEN 1 WHEN @PKDisp = '二' THEN 2 WHEN @PKDisp = '三' THEN 3 WHEN @PKDisp = '四' THEN 4 WHEN @PKDisp = '五' THEN 5 ELSE 100 END DECLARE @i INT SET @i = 1 WHILE @i<10 BEGIN set @i=@i+1 PRINT @i END DECLARE @d INT set @d = 1 IF @d = 1 BEGIN -- 打印 PRINT '正确' END ELSE BEGIN PRINT '错误' END
CREATE PROC P_TEST @Name VARCHAR(20), @Rowcount INT OUTPUT AS BEGIN SELECT * FROM T_Customer WHERE NAME=@Name SET @Rowcount=@@ROWCOUNT END GO ---------------------------------------------------------------------------------------- --存储过程调用如下: ---------------------------------------------------------------------------------------- DECLARE @i INT EXEC P_TEST 'A',@i OUTPUT SELECT @i --结果 /* Name Address Tel ---------- ---------- -------------------- A Address Telphone （所影响的行数为 1 行） ----------- 1 （所影响的行数为 1 行） */ ---------------------------------------------------------------------------------------- --DotNet 部分(C#) --WebConfig 文件: ---------------------------------------------------------------------------------------- ......
存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。 定义总是很抽象。存储过程其实就是能完成一定操作的一组SQL语句，只不过这组语句是放在数据库中的(这里我们只谈SQL Server)。如果我们通过创建存储过程以及在ASP中调用存储过程，就可以避免将SQL 语句同ASP代码混杂在一起。这样做的好处至少有三个： 第一、大大提高效率。存储过程本身的执行速度非常快，而且，调用存储过程可以大大减少同数据库的交互次数。 第二、提高安全性。假如将SQL语句混合在ASP代码中，一旦代码失密，同时也就意味着库结构失密。第三、有利于SQL语句的重用。 在ASP中，一般通过command对象调用存储过程，根据不同情况，本文也介绍其它调用方法。为了方便说明，根据存储过程的输入输出，作以下简单分类： 1. 只返回单一记录集的存储过程 假设有以下存储过程(本文的目的不在于讲述T-SQL语法，所以存储过程只给出代码，不作说明)： /*SP1*/ CREATE PROCEDURE dbo.getUserList as set nocount on begin select * from dbo.[userinfo] end go 以上存储过程取得userinfo表中的所有记录，返回一个记录集。通过command对象调用该存储过程的ASP 代码如下: '**通过Command对象调用存储过程** DIM MyComm,MyRst Set MyComm = Server.CreateObject("https://www.doczj.com/doc/0f3159713.html,mand") MyComm.ActiveConnection = MyConStr 'M yConStr是数据库连接字串 https://www.doczj.com/doc/0f3159713.html,mandText = "getUserList" '指定存储过程名 https://www.doczj.com/doc/0f3159713.html,mandType = 4 '表明这是一个存储过程 MyComm.Prepared = true '要求将SQL命令先行编译 Set MyRst = MyComm.Execute Set MyComm = Nothing 存储过程取得的记录集赋给MyRst，接下来，可以对MyRst进行操作。 在以上代码中，CommandType属性表明请求的类型，取值及说明如下： -1 表明CommandText参数的类型无法确定 1 表明CommandText是一般的命令类型 2 表明CommandText参数是一个存在的表名称 4 表明CommandText参数是一个存储过程的名称 还可以通过Connection对象或Recordset对象调用存储过程，方法分别如下：
存储过程的作用和意义 随着唐山公司开发部的成立，针对各项生产经营活动的系统支撑逐步到位，在开发过程中，数据库存储过程应用逐渐广泛，这里我来简要介绍下存储过程。 一、什么是存储过程： 存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。 二、为什么要用存储过程呢？ 存储过程真的那么重要吗，它到底有什么好处呢？存储过程说白了就是一堆SQL 的合并。中间加了点逻辑控制。 1.存储过程处理比较复杂的业务时比较实用。具体分为两个方面：(一)、响应时间上来说有优势：如果你在前台处理的话。可能会涉及到多次数据库连接。但如果你用存储过程的话，就只有一次。存储过程可以给我们带来运行效率提高的好处；(二)、从安全上使用了存储过程的系统更加稳定：程序容易出现BUG 不稳定，而存储过程，只要数据库不出现问题，基本上是不会出现什么问题的。 2.数据量小的项目不用存储过程也可以正常运作。 三、那么什么时候才需要用存储过程？ 存储过程不仅仅适用于大型项目，对于中小型项目，使用存储过程也是非常有必要的。其优势主要体现在： 1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。 3.存储过程可以重复使用,可减少数据库开发人员的工作量。 4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。 5.更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。 6.分布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。 一般来说，存储过程的编写比基本SQL语句复杂，编写存储过程需要更高的技能，更丰富的经验。 四、系统开发中存储过程使用的优势和劣势 优点如下： 1.执行效率高。 2.安全性能好。 3.对于一些场合非常容易实现需求。 缺点如下： 1.可维护性比较差。 2.可读性也差。
动态语句基本语法 1 :普通SQL语句可以用exec执行 Select * from tableName exec('select * from tableName') exec sp_executesqlN'select * from tableName' -- 请注意字符串前一定要加N 2:字段名，表名，数据库名之类作为变量时，必须用动态SQL declare @fnamevarchar(20) set @fname = 'FiledName' Select @fname from tableName -- 错误,不会提示错误，但结果为固定值FiledName,并非所要。exec('select ' + @fname + ' from tableName') -- 请注意加号前后的单引号的边上加空格 当然将字符串改成变量的形式也可 declare @fnamevarchar(20) set @fname = 'FiledName' --设置字段名 declare @s varchar(1000) set @s = 'select ' + @fname + ' from tableName' exec(@s) -- 成功 exec sp_executesql @s -- 此句会报错 declare @s Nvarchar(1000) -- 注意此处改为nvarchar(1000) set @s = 'select ' + @fname + ' from tableName' exec(@s) -- 成功 exec sp_executesql @s -- 此句正确 3. 输出参数 declare @numint, @sqlsnvarchar(4000) set @sqls='select count(*) from tableName' exec(@sqls) --如何将exec执行结果放入变量中？ declare @numint, @sqlsnvarchar(4000) set @sqls='select @a=count(*) from tableName ' execsp_executesql @sqls,N'@aint output',@num output select @num 1 :普通SQL语句可以用Exec执行例: Select * from tableName Exec('select * from tableName')
实验七存储过程及应用 1．实验目的 1、理解存储过程的概念。 2、掌握存储过程的使用方法。 2．实验要求 1．建立如下的存储过程（基于前面实验建立的表和插入的数据，并为每个存储过程设计返回的状态值）： （1）按要求设计完成如下功能的存储过程。 ①查询平均分数在x到y范围内的学生信息。 说明： 该存储过程有两个参数； 要求查询的学生信息包括学号、姓名、院系名称和平均分数。 ②更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩。 (2)自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求）。 （3）在客户端以存储过程和输入SQL语句的方式分别执行相同的查询或操作，比较使用和不使用存储过程的区别。 3、实验过程 （1）①查询平均分数在x到y范围内的学生信息 create procedure cc711@x smallint,@y smallint as select学生.学号,学生.姓名,院系.名称,学生.平均成绩 from学生join院系on学生.院系=院系.编号 where学生.平均成绩between@x and@y EXECUTE cc71160,90
②更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考试成绩，并返回该学生的平均成绩 create procedure mcc712@xh nchar(8),@kcbh nchar(8),@cj int as update选课set成绩=@cj where学号=@xh and课程编号=@kcbh declare@pjcj int select@pjcj=AVG(成绩)from选课where学号=@xh return@pjcj declare@avg int execute@avg=mcc7122,5,98 print'更新后平均成绩：'+str(@avg,6)
题目1 1、学校图书馆借书信息管理系统建立三个表： 学生信息表：student 图书表：book 借书信息表：borrow 请编写SQL语句完成以下的功能： 1)查询“计算机”专业学生在“2007-12-15”至“2008-1-8”时间段内借书的学生编号、 学生名称、图书编号、图书名称、借出日期；参考查询结果如下图所示： 2)查询所有借过图书的学生编号、学生名称、专业；参考查询结果如下图所示：
3)查询借过作者为“安意如”的图书的学生姓名、图书名称、借出日期、归还日期； 参考查询结果如下图所示： 4)查询目前借书但未归还图书的学生名称及未还图书数量；参考查询结果如下图所 示： 附加：建表语句:
标准答案:
题目2 程序员工资表：ProWage 创建一个存储过程，对程序员的工资进行分析，月薪1500到10000不等，如果有百分之五十的人薪水不到2000元，给所有人加薪，每次加100，再进行分析，直到有一半以上的人大于2000元为止，存储过程执行完后，最终加了多少钱？ 例如：如果有百分之五十的人薪水不到2000，给所有人加薪，每次加100元，直到有一半以上的人工资大于2000元，调用存储过程后的结果如图：
请编写T-SQL来实现如下功能： 1)创建存储过程，查询是否有一半程序员的工资在2200、3000、3500、4000、5000或6000 元之上，如果不到分别每次给每个程序员加薪100元，至之一半程序员的工资达到2200，3000，3500，4000，5000或6000元。 2)创建存储过程，查询程序员平均工资在4500元，如果不到则每个程序员每次加200元， 至到所有程序员平均工资达到4500元。 建表语句
2008-12-16 21:41 第一种方法: 使用output参数 USE AdventureWorks; GO IF OBJECT_ID ( 'https://www.doczj.com/doc/0f3159713.html,p_GetList', 'P' ) IS NOT NULL DROP PROCEDURE https://www.doczj.com/doc/0f3159713.html,p_GetList; GO CREATE PROCEDURE https://www.doczj.com/doc/0f3159713.html,p_GetList @product varchar(40) , @maxprice money , @compareprice money OUTPUT , @listprice money OUT AS SELECT https://www.doczj.com/doc/0f3159713.html, AS Product, p.ListPrice AS'List Price' FROM Production.Product p JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE https://www.doczj.com/doc/0f3159713.html, LIKE@product AND p.ListPrice <@maxprice; -- Populate the output variable @listprice. SET@listprice= (SELECT MAX(p.ListPrice) FROM Production.Product p JOIN Production.ProductSubcategory s ON p.ProductSubcategoryID = s.ProductSubcategoryID WHERE https://www.doczj.com/doc/0f3159713.html, LIKE@product AND p.ListPrice <@maxprice); -- Populate the output variable @compareprice. SET@compareprice=@maxprice; GO 另一个存储过程调用的时候: Create Proc Test as DECLARE@compareprice money, @cost money EXECUTE https://www.doczj.com/doc/0f3159713.html,p_GetList '%Bikes%', 700, @compareprice OUT, @cost OUTPUT IF@cost<=@compareprice BEGIN PRINT'These products can be purchased for less than $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.' END ELSE PRINT'The prices for all products in this category exceed $'+RTRIM(CAST(@compareprice AS varchar(20)))+'.'
存储过程如同一门程序设计语言，同样包含了数据类型、流程控制、输入和输出和它自己的函数库。 --------------------基本语法-------------------- 一.创建存储过程 create procedure sp_name() begin ......... end 二.调用存储过程 1.基本语法：call sp_name() 注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递 三.删除存储过程 1.基本语法： drop procedure sp_name// 2.注意事项 (1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程 四.其他常用命令 1.show procedure status 显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等2.show create procedure sp_name 显示某一个mysql存储过程的详细信息 --------------------数据类型及运算符-------------------- 一、基本数据类型： 略 二、变量： 自定义变量：DECLARE a INT ; SET a=100; 可用以下语句代替：DECLARE a INT DEFAULT 100; 变量分为用户变量和系统变量，系统变量又分为会话和全局级变量 用户变量：用户变量名一般以@开头，滥用用户变量会导致程序难以理解及管理 1、在mysql客户端使用用户变量 mysql> SELECT 'Hello World' into @x; mysql> SELECT @x; mysql> SET @y='Goodbye Cruel World'; mysql> select @y; mysql> SET @z=1+2+3; mysql> select @z; 2、在存储过程中使用用户变量 mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World'); mysql> SET @greeting='Hello'; mysql> CALL GreetWorld( );
C#执行存储过程代码实例，演示如何用C#调用并执行SQLSERVER的存储过程，是一个数据库的高级应用，存储过程相对专业些，但效率更高，而且使SQL的功能发挥的更强大，这仅是一个较简单的存储过程调用例子： 01using System; 02using System.Data; 03using System.Data.SqlClient; 04namespace ExecuteStorageProcess 05{ 06 public class ExecuteStorageProcess 07 { 08 public static void Main() 09 { 10 SqlConnection mySqlConnection = 11 new SqlConnection( 12 "server=localhost;database=Northwind;uid=sa;pwd=sa" 13 ); 14 mySqlConnection.Open(); 15 // 设置CommandText属性为EXECUTE语句 16 SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); 17 https://www.doczj.com/doc/0f3159713.html,mandText = 18 "EXECUTE AddProduct @MyProductID OUTPUT, @MyProductName, " + 19 "@MySupplierID, @MyCategoryID, @MyQuantityPerUnit, " + 20 "@MyUnitPrice, @MyUnitsInStock, @MyUnitsOnOrder, " + 21 "@MyReorderLevel, @MyDiscontinued"; 22 // 添加过程调用所要用到的参数 23 mySqlCommand.Parameters.Add("@MyProductID", SqlDbType.Int); 24 mySqlCommand.Parameters["@MyProductID"].Direction = 25 ParameterDirection.Output; 26 mySqlCommand.Parameters.Add( 27 "@MyProductName", SqlDbType.NVarChar, 40).Value = "Widget"; 28 mySqlCommand.Parameters.Add( 29 "@MySupplierID", SqlDbType.Int).Value = 1; 30 mySqlCommand.Parameters.Add( 31 "@MyCategoryID", SqlDbType.Int).Value = 1; 32 mySqlCommand.Parameters.Add( 33 "@MyQuantityPerUnit", SqlDbType.NVarChar, 20).Value = "1 per box"; 34 mySqlCommand.Parameters.Add( 35 "@MyUnitPrice", SqlDbType.Money).Value = 5.99; 36 mySqlCommand.Parameters.Add( 37 "@MyUnitsInStock", SqlDbType.SmallInt).Value = 10; 38 mySqlCommand.Parameters.Add( 39 "@MyUnitsOnOrder", SqlDbType.SmallInt).Value = 5;
--存储过程的创建和调用演示 ------------------------------------- --1.不带参数的存过过程的创建 CREATEPROCEDURE PRO_WITH_NO_PARAM AS BEGIN--BEGIN 可省略 SELECT*FROM C; END--END 可省略 --调用 EXEC PRO_WITH_NO_PARAM; ------------------------------------ --2.带参数的存储过程 CREATEPROCEDURE PRO_WHIT_PARAM ( @Sno char(5), @Cno char(1) ) AS BEGIN SELECT*FROM SC WHERE Sno=@Sno AND Cno=@Cno END --调用 EXEC PRO_WHIT_PARAM '09002','3' --------------------------------------- --3.带一个参数的多表查询存储过程 CREATEPROCEDURE PRO_QUERY_DOUBLE_TABLE ( @Sname_IN char(6) ) AS BEGIN SELECT Sname,Ssex,Sage,Sdept,Cno,Grade FROM S STU ,SC SCTU WHERE STU.Sno = SCTU.Sno AND Sname = @Sname_IN END --执行 EXEC PRO_QUERY_DOUBLE_TABLE '姑姑' ----------------------------------------- --4.创建一个插入表的存储过程 CREATEPROCEDURE PRO_INSERT ( @Sno_IN CHAR(5), @Cno_IN CHAR(1), @Grade_IN int )
SQL Server在存储过程中编写事务处理代码的三种方法 SQL Server中数据库事务处理是相当有用的，鉴于很多SQL初学者编写的事务处理代码存往往存在漏洞，本文我们介绍了三种不同的方法，举例说明了如何在存储过程事务处理中编写正确的代码。希望能够对您有所帮助。 在编写SQL Server 事务相关的存储过程代码时，经常看到下面这样的写法： begin tran update statement 1 ... update statement 2 ... delete statement 3 ... commit tran 这样编写的SQL存在很大隐患。请看下面的例子： create table demo(id int not null) go begin tran insert into demo values (null) insert into demo values (2) commit tran go 执行时会出现一个违反not null 约束的错误信息，但随后又提示(1 row(s) affected)。我们执行select * from demo 后发现insert into demo values(2) 却执行成功了。这是什么原因呢? 原来SQL Server在发生runtime 错误时，默认会rollback引起错误的语句，而继续执行后续语句。 如何避免这样的问题呢？ 有三种方法：
1. 在事务语句最前面加上set xact_abort on set xact_abort on begin tran update statement 1 ... update statement 2 ... delete statement 3 ... commit tran go 当xact_abort 选项为on 时，SQL Server在遇到错误时会终止执行并rollback 整个事务。 2. 在每个单独的DML语句执行后，立即判断执行状态，并做相应处理。 begin tran update statement 1 ... if@@error<>0 begin rollback tran goto labend end delete statement 2 ... if@@error<>0 begin rollback tran goto labend end commit tran labend: go 3. 在SQL Server 2005中，可利用try...catch 异常处理机制。
获取存储过程返回值及代码中获取返回值 1.OUPUT参数返回值 例：向Order表插入一条记录,返回其标识 CREATE PROCEDURE[dbo].[nb_order_insert]( @o_buyerid int , @o_id bigint OUTPUT ) AS BEGIN SET NOCOUNT ON; BEGIN INSERT INTO[Order](o_buyerid ) VALUES (@o_buyerid ) SET@o_id=@@IDENTITY END END 存储过程中获得方法： DECLARE@o_buyerid int DECLARE@o_id bigint EXEC[nb_order_insert]@o_buyerid ,o_id bigint 2.RETURN过程返回值 CREATE PROCEDURE[dbo].[nb_order_insert]( @o_buyerid int , @o_id bigint OUTPUT ) AS BEGIN SET NOCOUNT ON; IF(EXISTS(SELECT*FROM[Shop]WHERE[s_id]=@o_shopid)) BEGIN INSERT INTO[Order](o_buyerid ) VALUES (@o_buyerid ) SET@o_id=@@IDENTITY RETURN1—插入成功返回1 END ELSE RETURN0—插入失败返回0 END
存储过程中的获取方法 DECLARE@o_buyerid int DECLARE@o_id bigint DECLARE@result bit EXEC@result=[nb_order_insert]@o_buyerid ,o_id bigint 3.SELECT 数据集返回值 CREATE PROCEDURE[dbo].[nb_order_select]( @o_id int ) AS BEGIN SET NOCOUNT ON; SELECT o_id,o_buyerid FROM[Order] WHERE o_id =@o_id GO 存储过程中的获取方法 （1）、使用临时表的方法 CREATE TABLE[dbo].[Temp]( [o_id][bigint]IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, [o_buyerid][int]NOT NULL ) INSERT[Temp]EXEC[nb_order_select]@o_id –这时Temp就是EXEC执行SELECT 后的结果集 SELECT*FROM[Temp] DROP[Temp]—删除临时表 （2）、速度不怎么样.（不推荐） SELECT*from openrowset(’provider_name','Trusted_Connection=yes’,'exec nb _order_select’) 1.获取Return返回值 程序代码 //存储过程 //Create PROCEDURE MYSQL // @a int,
数据库实验七：存储过程及应用 一、实验目的与要求： 1．实验目的 1．理解存储过程的概念。 2．掌握存储过程的使用方法。 2．实验要求 1．建立如下的存储过程（基于前面实验建立的表和插入的数据，并为每个存储过程设计返回的状态值）： （1）按要求设计完成如下功能的存储过程。 ①查询平均分数在x到y范围内的学生信息。 说明： ●该存储过程有两个参数； ●要求查询的学生信息包括学号、姓名、院系名称和平均分数。 ②更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考 试成绩，并返回该学生的平均成绩。 ③更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考 试成绩，并返回该学生的平均成绩。 （2）自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求）。 （3）在客户端以存储过程和输入SQL语句的方式分别执行相同的查询或操作，比较使用和不使用存储过程的区别。 二、实验内容 1、实验原理 1．创建存储过程的SQL语句的一般格式是： CREATE PROC[edure] [schema_name].procedure-name [; number ] [@parameter data-type [VARYING] [= default ][OUT | OUTPUT],…] AS sql-statement 2．执行存储过程的语句是： [EXECute]
[@<返回状态码> =] <存储过程名> [[@<参数>=]｛<值>|@<变量>｝…] 或 EXECUTE [@return_status=] [schema_name].procedure-name [; number ] [@parameter =]{value | variable [ OUTPUT]}[,…n] 2、实验步骤与结果 （1）调出SQL Server2005软件的用户界面，进入SQL Server Management Studio。 （2）输入自己编好的程序。 （3）检查已输入的程序正确与否。 （4）运行程序，并分析运行结果是否合理和正确。在运行时要注意当输入不同的数据时所得到的结果是否正确。 （5）输出程序清单和运行结果。 实验程序： create procedure Stu @minmark smallint,@maxmark smallint AS select学生.学号,学生.姓名,院系.名称,平均成绩 from学生join院系 on学生.院系=院系.编号 where平均成绩>=@minmark and平均成绩<=@maxmark execute Stu80,100
Oracl e 存储过程学习 目录 Oracle 存储过程1 Oracle存储过程基础知识1 Oracle存储过程的基本语法2 关于Oracle存储过程的若干问题备忘4 1.在Oracle中，数据表别名不能加as。5 2.在存储过程中，select某一字段时，后面必须紧跟into，如果select整个记录，利用 游标的话就另当别论了。5 3.在利用select...into...语法时，必须先确保数据库中有该条记录，否则会报出"no data found"异常。5 4.在存储过程中，别名不能和字段名称相同，否则虽然编译可以通过，但在运行阶段 会报错5 5.在存储过程中，关于出现null的问题5 6.Hibernate调用Oracle存储过程6 用Java调用Oracle存储过程总结6 一、无返回值的存储过程6 二、有返回值的存储过程（非列表）8 三、返回列表10 在存储过程中做简单动态查询11 一、本地动态SQL12 二、使用DBMS_SQL包14 Oracle存储过程调用Java方法16 Oracle高效分页存储过程实例17 Oracle存储过程基础知识 商业规则和业务逻辑可以通过程序存储在Oracle中，这个程序就是存储过程。 存储过程是SQL, PL/SQL, Java 语句的组合，它使你能将执行商业规则的代码从你的应用程序中移动到数据库。这样的结果就是，代码存储一次但是能够被多个程序使用。 要创建一个过程对象（procedural object），必须有CREATE PROCEDURE 系统权限。如果这个过程对象需要被其他的用户schema 使用，那么你必须有CREATE ANY PROCEDURE 权限。执行procedure 的时候，可能需要excute权限。或者EXCUTE ANY PROCEDURE 权限。如果单独赋予权限，如下例所示： grant execute on MY_PROCEDURE to Jelly 调用一个存储过程的例子： execute MY_PROCEDURE( 'ONE PARAMETER'); 存储过程(PROCEDURE)和函数(FUNCTION)的区别。
用JDBC操作Oracle的存储过程返回值 Oracle的存储过程可以返回任何类型，包括一个ResultSet，JDBC自带的CallableStatement可以提供操作这些返回值得借口，其中我们可以通过registerOutParameter来注册需要返回的类型。CallableStatement是PrepareStatement的一个子类，但提供了返回和注册Out类型参数的功能。我们看一下例子，在Oracle里面我们定义了一个包如下： create or replace package GP_TESTP is type my_dCursor is ref cursor; procedure GP_Test(m_cursor2 OUT my_dCursor; end GP_TESTP; create or replace package body GP_TESTP is procedure GP_Test(m_cursor2 OUT my_dCursor is begin open m_cursor2 for select bom.material_no,bom.product_no from gp2_bom bom where bom.year=2006 ; end GP_Test; end GP_TESTP; 在JDBC里面我们可以通过如下的接口来得到返回的动态游标的内容 Global.start(; //初始化连接池 ConnectionWrapper wrapper=ConnectionPoolFactory.getPoolInstance(.borrowConnection(;//从连接池里面返回连接 Connection conn=wrapper.getConnection(; try { String callSql="{call GP_TESTP.GP_Test(?}"; CallableStatement cs = null; ResultSet rs=null; cs = conn.prepareCall(callSql; cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR; cs.execute(; rs = (ResultSetcs.getObject(2; while(rs.next({ //String s=rs.get(""; String component=rs.getString(2; String productNo=rs.getString(4; System.out.println("component="+component+"-------productNo="+productNo; } } catch (SQLException e { // TODO Auto-generated catch block e.printStackTrace(; } 注意兰颜色的代码是注册动态游标和返回结果集的关键代码。 ======================================= 2008年07月06日星期日 07:41 P.M.一：无返回值的存储过程存储过程为： CREATE OR REPLACE PROCEDURE TESTA(PARA1 IN VARCHAR2,PARA2 IN VARCHAR2 AS BEGIN INSERT INTO HYQ.B_ID (I_ID,I_NAME VALUES (PARA1, PARA2; END TESTA; 然后呢，在java里调用时就用下面的代码： package com.hyq.src; import java.sql.*; import java.sql.ResultSet; public class TestProcedureOne { public TestProcedureOne( { } public static void main(String[] args { String driver = "oracle.jdbc.driver.OracleDriver"; String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521: hyq "; Statement stmt = null;