一、存储过程中使用事务的简单语法
在存储过程中使用事务时非常重要的，使用数据可以贺销保持数据的关联完整性，在Sql server存储过程中使用事务也很简单，用一个例子来说明它的语法格式：
Create Procere MyProcere
( @Param1 nvarchar(10),
@param2 nvarchar(10)
)
AS
Begin
Set NOCOUNT ON;
Set XACT_ABORT ON;
Begin Tran
Delete from table1 where name=’abc’;
Insert into table2 values(value1,value2,value3);
Commit Tran
End
说明：
1 、使用存储过程执行事物，需要开启XACT_ABORT参数(默认值为Off)，将该参数设置为On，表示当执行事务时，如果出错,会将transcation设置为uncommittable状态，那么在语句块批处理结束后将回滚所有操作；如果该参数设置为Off，表示当执行事务时，如果出错，出错的语句将不会执行，其他正确的操作继续执行。
2、当SET NOCOUNT 为 ON 时，不返回计数（计数表示受 Transact-SQL 语句影响的行数，例如在Sql server查询分析器中执行一个delete操作后，下方窗口会提示（3）Rows Affected）。当 SET NOCOUNT 为 OFF 时，返回计数,我们应该在存储过程的头部加上SET NOCOUNT ON 这样的话，在退出存储过程的时候加上 SET NOCOUNT OFF这样的话，以达到优化存储过程的目的。
二、事务内设置保存点
用户可以在事务内设置保存点或标记。保存点定义如果有条件地取消事务的一部分，事务可以返回的位置。如果将事务回滚到保存点，则必须（如果需要，使用更多的 Transact-SQL 语句和 COMMIT TRANSACTION 语句）继续完成事务，或者必须（通过将事务回滚到其起始点）完全取消事务。若要取消整个事务，请使用 ROLLBACK TRANSACTION transaction_name 格式。这将撤消事务的所有语句和过程。如：
代码
Create Procere MyProcere
AS
Begin
Set NOCOUNT ON;
Set XACT_ABORT ON;
begin tran ok --开始一个事务OK
delete from rxqz where qz= 'rx015 ' --删除数据
save tran bcd --保存一个事务点命名凯肆为bcd
update sz set name='李丽s' where name= '李丽'--修改数据
if @@error<>0 --判断修改数据有没有出错
begin --如果出错
rollback tran bcd -- 回滚事务到BCD 的还原点
commit tran ok --提交事务
end
else --没有出错
commit tran ok --提交事务
End
说明：1、@@error判断是否有错误，为0表示没有盯拍轿错误，但是对那种重大错误无法捕捉，而且@@error只能前一句sql语句生效。
三、存储过程使用try…catch捕获错误
在存储过程中可以使用try…catch语句来捕获错误，如下：
Create Procere MyProcere
( @Param1 nvarchar(10),
@param2 nvarchar(10)
)
AS
Begin
Set NOCOUNT ON;
Begin try
Delete from table1 where name=’abc’;
Insert into table2 values(value1,value2,value3);
End try
Begin Catch
SELECT ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
End Catch
End
说明：1、捕获错误的函数有很多，如下：
ERROR_NUMBER() 返回错误号。
ERROR_SEVERITY() 返回严重性。
ERROR_STATE() 返回错误状态号。
ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称。
ERROR_LINE() 返回导致错误的例程中的行号。
ERROR_MESSAGE() 返回错误消息的完整文本。该文本可包括任何可替换参数所提供的值，如长度、对象名或时间。
2、有些错误，如sql语句中的表名称输入错误，这是数据库引擎无法解析这个表名称时，所发生的错误在当前的try…catch语句中无法捕获，必须由外层调用该存储过程的地方使用 try…catch来进行捕获。
四、存储过程中事务和try…catch联合使用
在存储过程中使用事务时，如果没有try…catch语句，那么当set xact_abort on时，如果有错误发生，在批处理语句结束后，系统会自动回滚所有的sql操作。当set xact_abort off时，如果有错误发生，在批处理语句结束后，系统会执行所有没有发生错误的语句，发生错误的语句将不会被执行。
在存储过程中使用事务时，如果存在try…catch语句块，那么当捕获到错误时，需要在catch语句块中手动进行Rollback操作，否则系统会给客户端传递一条错误信息。如果在存储过程开始处将set xact_abort on，那么当有错误发生时，系统会将当前事务置为不可提交状态，即会将xact_state()置为-1，此时只可以对事务进行Rollback操作，不可进行提交(commit)操作，那么我们在catch语句块中就可以根据xact_state()的值来判断是否有事务处于不可提交状态，如果有则可以进行rollback操作了。如果在存储过程开始处将set xact_abort off，那么当有错误发生时，系统不会讲xact_state()置为-1，那么我们在catch块中就不可以根据该函数值来判断是否需要进行rollback了，但是我们可以根据@@Trancount全局变量来判断，如果在catch块中判断出@@Trancount数值大于0，代表还有未提交的事务，既然进入catch语句块了，那么还存在未提交的事务，该事务应该是需要rollback的，但是这种方法在某些情况下可能判断的不准确。推荐的方法还是将set xact_abort on，然后在catch中判断xact_state()的值来判断是否需要Rollback操作。
下面我们来看看两个例子：
一．使用Set xact_abort on
Create proc myProcere
As
begin
set xact_abort on;
begin try
begin tran
insert into TestStu values('Terry','boy',23);
insert into TestStu values('Mary','girl',21);
commit tran
end try
begin catch
--在此可以使用xact_state()来判断是否有不可提交的事务,不可提交的事务
--表示在事务内部发生错误了。Xact_state()有三种值：-1.事务不可提交；
--1.事务可提交；0.表示没有事务，此时commit或者rollback会报错。
if xact_state()=-1
rollback tran;
end catch
end
二.使用Set xact_abort off
Create proc myProcere
As
begin
set xact_abort off;
begin try
begin tran
insert into TestStu values('Terry','boy',23);
insert into TestStu values('Mary','girl',21);
commit tran
end try
begin catch
--在此不可以使用xact_state来判断是否有不可提交的事务
--只可以使用@@Trancount来判断是否有还未提交的事务，未提交的事务未必
--就是不可提交的事务，所以使用@@TranCount>0后就RollBack是不准确的
if @@TranCount>0
rollback tran;
end catch
end
另外，对于@@Trancount需要说明的是，begin tran 语句将 @@Trancount加 1。Rollback tran将 @@Trancount递减到 0，但 Rollback tran savepoint_name 除外，它不影响 @@Trancount。Commit tran 或 Commit work 将 @@Trancount 递减 1。
② 如何判断java的存储过程是否成功执行
这个分两种情况：
由于存储过程是数据库自身的东西，JAVA只是调用
所以，如果是语歼纯法上的错误 sql直接不能执行 ， JAVA确实能接收到异常
但是如果语法没有错误， 只是你写的陪拍条件错了， sql能执行，氏乱咐只是没有得到你想要的效果， 这个情况， 是判断不了的， 都会视为成功执行
如果 sql = "select from aaa where id > 10" 这直接是语法 错误，肯定报错
但 如果 sql = "update aaa set name = 'aaa' where id='123' "; 没有语法 错误 ，能执行， 只是没有id = 123的数据， 你要改124的，错写成123了， 就修改不了， 但也不会报任何错
③ Sqlserver 8.0.2039 存储过程怎样写try catch
sql2005 以下版本没有 try catch语句辩迟块;
通常滑灶轮信信采用 @@error 来判断;
code:
declare @error int
set @error = 0
begin tran
--your sql here and exceut
set @error = @@error + @error
if(@error <> 0 ) --has error
begin
rollback ;
return;
end
--another sql code and excut it ..
set @error = @@error + @error
if(@error <> 0 ) --has error
begin
rollback ;
return;
end
commit
④ 如何将datatable传入存储过程中
一、测试环境
1、Windows Server 2008 R2 DataCenter
2、Visual Studio 2008 Team System With SP1
3、SQL Server 2008 Enterprise Edition With SP1
由于是SQL Server 2008新特性，所以只能用2008。
二、测试概述
测试项目很简单，就是添加新用户
三、准凳敏备数据
1、建立数据库、表、类型、存储过程
IF NOT EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('Users') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE dbo.Users
(
UserID INT IDENTITY(-1, -1) NOT NULL,
UserName VARCHAR(20) NOT NULL,
UserPass VARCHAR(20) NOT NULL,
Sex BIT NULL,
Age SMALLINT NULL,
CONSTRAINT PK_Users_UserID PRIMARY KEY(UserID)
)
END
IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'UserTable' AND is_user_defined = 1)
BEGIN
CREATE TYPE UserTable AS TABLE
(
UserName VARCHAR(20) NOT NULL,
UserPass VARCHAR(20) NOT NULL,
Sex BIT NULL,
Age SMALLINT NULL
)
END
GO
IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID('sp_InsertSingleUser') AND OBJECTPROPERTY(id, N'IsProcere') = 1)
BEGIN
DROP PROCEDURE dbo.sp_InsertSingleUser
END
GO
CREATE PROCEDURE dbo.sp_InsertSingleUser
(
@User UserTable READONLY
)
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
INSERT INTO dbo.Users(UserName, UserPass, Sex, Age)
SELECT UserName, UserPass, Sex, Age FROM @User
COMMIT TRANSACTION
SET XACT_ABORT OFF
GO
前台搭建好表单，后台主要是一个函数：
publicvoidfnInsertSingleUser(DataTablev_dt)
{
try
{
SqlConnectioncn=newSqlConnection(CONN);
SqlCommandcmd=cn.CreateCommand();
cmd.CommandType=亮晌CommandType.StoredProcere;
cmd.CommandText=@"sp_InsertSingleUser";
SqlParameterp=cmd.Parameters.AddWithValue("@User",v_dt);
DataSetds=newDataSet();
SqlDataAdapterda=newSqlDataAdapter(cmd);
da.Fill(ds);
}敬粗锋
catch(Exceptionex)
{
throwex;
}
}
⑤ SqlDataReader无法从数据库中读取多条记录
while(dr.Read())
{
...
}
dr.Close();
这样是没有问题橡渗的,可以读取渣轮多条数据.
dr.Read()方法读取下一条数据成功就返回true,失败,即读取完毕时返回false.
至于只能读取一条数据,可能是这个存储过程只返回了一条数梁梁脊据,或者其他什么原因,我没有用过SQLServerHelper,不是很清楚.
可以在数据库里调用那个存储过程试试,看看返回了多少数据.
⑥ JDBC调用Sybase存储过程，结果集总是无法返回，该怎么处理
JDBC调用Sybase存储过程，结果集总是无法返回！
java代码部分如下：
String sproc = "{ call zhouxiaobotest2(?,?,?,?)} ";
CommonDAO = null;
try {
= new CommonDAO(jndi);
Connection connect = .getConn();
//得到总数
rowNum = PageDiv.getCount(countSql.toString(), );
//设置分页信息
pageBean.setPageInfo(rowNum, pageSize);
// 获取CallableStatement语句:
CallableStatement mStatement = connect.prepareCall(sproc,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
// 传入输入参数和注册输出参数
mStatement.setString(1,sql.toString());
mStatement.setInt(2, 10);
mStatement.setInt(3, 10);
mStatement.setInt(4, 5000);
// 执行存储过程
ResultSet rs = null;
mStatement.execute();
rs = mStatement.getResultSet();
catch (Exception ex) {
ex.printStackTrace();
logger.warn(sql.toString());
} finally {
.destroy();
= null;
sproc = null;
}
存储过程如下：
create procere zhouxiaobotest @qry varchar(16384),@ipage int, @num int,@maxpages int
as
/*@qry SQL语句, @ipage 页数, @num 每页记录条数, @maxpages 最大查薯昌询页数 */
begin
declare @execsql varchar(16384)
set @execsql = " select USER_NAME from TBL_USER "
execute (@execsql)
end
create procere zhouxiaobotest2 @qry varchar(16384),@ipage int, @num int,@maxpages int
as
/*@qry SQL语句, @ipage 页数, @num 每页记录条数, @maxpages 最大查询页橡手液数 */
begin
select USER_NAME from TBL_USER
end
现在我的问题是调用存梁物储过程zhouxiaobotest，mStatement.execute()始终是false；而调用zhouxiaobotest2 mStatement.execute()就是true，能得到结果集。我想知道如何修改程序使我能够调用zhouxiaobotest取得结果集（之前调试程序运行没有抛出任何异常，而且控制台运行存储过程也都正确）
⑦ 对象不能从 DBNull 转换为其他类型。
prmSid.Value 返回的档燃值是 DBNull，也就是说相当于数据岩蠢掘库单元格中什么值也没有。
你需要事先判断这种粗核情况。
⑧ 如何把sql语句查询到的值赋值给变量
//多行
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("server=.;database=你的数据库的名字;uid=sa;pwd=sa的密码;");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = conn;
cmd.CommandText="select 字段A from 表B where years=2000 and months=2";
conn.Open();
System.Data.SqlDataAdapter sda=new System.Data.SqlDataAdapter(cmd);
DataSet ds=new DataSet();
sda.Fill(ds,"dtResult");
conn.Close();
//结果在ds的dtResult中。
foreach(DataRow dr in ds.Tables["dtResult"])
{
Response.Write(dr["字段A"].ToString()+"<br>");
}
-------------------------------------------------------------
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection("server=.;database=你的数据库的名字;uid=sa;pwd=sa的密码;");
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
cmd.Connection = conn;
cmd.CommandText="select 字段A from 表B where years=2000 and months=2";
conn.Open();
int i=Convert.ToInt32(cmd.ExecuteScalar().ToString());
conn.Close();
⑨ 如何在JDBC里调用PLSQL创建的存储过程
import oracle.jdbc.*;
import oracle.sql.*;
import java.sql.*;
public class ReturnIndexTable
{
Connection ociconn=null;
OracleCallableStatement stmt =null;
public String[] getTable(String in_param)
{
String[] reAry=null;
try
{
OracleDriver S_Driver=null;
if(S_Driver==null)
S_Driver=new oracle.jdbc.OracleDriver();
DriverManager.registerDriver(S_Driver);
String url="jdbc:oracle:oci8:@test";
String user="user";
String password=" password";
ociconn= DriverManager.getConnection(url,user,password);
stmt =(OracleCallableStatement)ociconn.prepareCall("begin yejf_count(?,?); end;");
// 返回的索引表最大长度（可以大于索引表实际长度）
int maxLen =31;
// 索引表元素类型
int elemSqlType = OracleTypes.VARCHAR;
//索引表元素长度（CHAR, VARCHAR or RAW），其它元素类型可忽略该项值，但该参数仍须定义
int elemMaxLen=50;
stmt.setString(1,in_param);
// 注册返回参数
stmt.(2,maxLen,elemSqlType,elemMaxLen);
stmt.execute();
// 返回数组类型
reAry=(String[])stmt.getPlsqlIndexTable(2);
}
catch (Exception e)
{e.printStackTrace();
}
finally
{return reAry;
}
}
//关闭连接.............
}
⑩ C#代码中要执行2个存储过程，那么是应该写2个try2个catch还是1个try,1个catch
看你要怎么执行了
第一种方式,如果存储过程晌李1失败或谨圆了, 不会影衫塌响到存储过程2
第二种方式, 存储过程2执行的前提,必须是存储过程1执行成功