前台创建一个数组存SQL语句,底层写一个多SQL语句的执行方法,循环执行就OK了。 我是.net开发着给你个底层方法/// <summary> /// 执行多条SQL语句,实现数据库事务。 /// </summary> /// <param name="SQLStringList">多条SQL语句</param> public static void ExecuteSqlTran(ArrayList SQLStringList) { using (OracleConnection connection = new OracleConnection(connectionString)) { connection.Open(); OracleCommand cmd = new OracleCommand(); cmd.Connection = connection; OracleTransaction tx = connection.BeginTransaction(); cmd.Transaction = tx; try { for (int n = 0; n < SQLStringList.Count; n++) { string strsql = SQLStringList[n].ToString(); if (strsql.Trim().Length > 1) { cmd.CommandText = strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch (System.Data.OracleClient.OracleException E) { tx.Rollback(); throw new Exception(E.Message); } finally { cmd.Dispose(); connection.Close(); System.GC.Collect(); } } }
简单例子,各种没考虑--测试表 CREATE TABLE FOO( FOO NUMBER(10) ); --搞几条数据 BEGIN FOR I IN 1..10 LOOP INSERT INTO FOO VALUES(I); END LOOP; END; /--过程 主要是数组和更新 DECLARE TYPE T_ARRAY IS ARRAY(10) OF INTEGER; NUM_ARRAY T_ARRAY :=T_ARRAY(10,20); BEGIN FOR I IN 1 .. 2 LOOP UPDATE FOO SET FOO.FOO = NUM_ARRAY(I); END LOOP; END; --结果应该都为20
我是.net开发着给你个底层方法/// <summary>
/// 执行多条SQL语句,实现数据库事务。
/// </summary>
/// <param name="SQLStringList">多条SQL语句</param>
public static void ExecuteSqlTran(ArrayList SQLStringList)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
connection.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = connection;
OracleTransaction tx = connection.BeginTransaction();
cmd.Transaction = tx;
try
{
for (int n = 0; n < SQLStringList.Count; n++)
{
string strsql = SQLStringList[n].ToString();
if (strsql.Trim().Length > 1)
{
cmd.CommandText = strsql;
cmd.ExecuteNonQuery();
}
}
tx.Commit();
}
catch (System.Data.OracleClient.OracleException E)
{
tx.Rollback();
throw new Exception(E.Message);
}
finally
{
cmd.Dispose();
connection.Close(); System.GC.Collect();
}
}
}
CREATE TABLE FOO(
FOO NUMBER(10)
);
--搞几条数据
BEGIN
FOR I IN 1..10 LOOP
INSERT INTO FOO VALUES(I);
END LOOP;
END;
/--过程 主要是数组和更新
DECLARE
TYPE T_ARRAY IS ARRAY(10) OF INTEGER;
NUM_ARRAY T_ARRAY :=T_ARRAY(10,20);
BEGIN
FOR I IN 1 .. 2 LOOP
UPDATE FOO SET FOO.FOO = NUM_ARRAY(I);
END LOOP;
END;
--结果应该都为20
如果是任意SQL,可以考虑写个动态执行SQL的过程 (使用 execute immediate 'SQL';),将要执行的SQL通过长字符串,或者临时表的方式传递给过程