create or replace procedure p is(p_str varchar2) is begin execute immediate p_str; --执行成功 输出一些提示信息 dbms_output.put_line(p_str || 'is finished' || sysdate); end;
嗯 上面写的procedure处理dml操作 并没有提交 等你循环结束后再commit;
可以使用 begin --批量语句 end; 方式一起执行的
参考: begin insert into a values('a'); update a set fld='b'; commit; end;
CREATE OR REPLACE PROCEDURE DOPATCH AS l_compile_sql varchar2(4000); BEGIN if l_compile_sql is not null then begin execute immediate l_compile_sql; dbms_output.put_line('OK'|| sysdate); commit; exception when others then dbms_output.put_line('FAILED'|| sysdate); end; end if; END DOPATCH;这么写对不对呢.
//创建ArrayDescriptor,其中第1个参数是对应oracle中的嵌套表类型 ArrayDescriptor desc = ArrayDescriptor .createDescriptor("STRINGS", conn); //创建ARRAY对象,将java数组传入 ARRAY array = new ARRAY(desc, conn, arr); //设置存储过程参数 stmt.setARRAY(1, array); stmt.execute(); conn.commit(); conn.close(); stmt.close(); }} /* * create or replace type strings is table of varchar2(32767);--nested table create table proc_log_notes (sql_text varchar2(2000),done_date date); --procedure create or replace procedure batchDML(param in strings)--param is nested table as begin for i in param.first .. param.last loop execute immediate param(i); end loop; end batchDML; */这也不是什么批处理,测试后是这样: SQL> select * from proc_log_notes;
SQL_TEXT DONE_DATE -------------------------------------------------------------------------------- ----------- drop table test 2010/5/9 19 create table test(id number) 2010/5/9 19 insert into test values(1) 2010/5/9 19
SQL> create or replace type myvarray_list as varray(2) of varchar2(100); 2 /
Type created
SQL> SQL> CREATE OR REPLACE PROCEDURE 2 PRO(p_str IN myvarray_list) IS 3 begin 4 for i in 1..p_str.count loop 5 execute immediate p_str(i); 6 dbms_output.put_line(sysdate); 7 end loop; 8 end; 9 /
Procedure created
SQL> select * from t;
ID ----------
SQL> declare 2 a myvarray_list; 3 begin 4 a := myvarray_list(); 5 a.extend; 6 a(1):= 'insert into t values(1)'; 7 a.extend; 8 a(2):= 'insert into t values(2)'; 9 pro(a); 10 end; 11 /
2010-05-09 19:15:31 2010-05-09 19:15:31
PL/SQL procedure successfully completed
SQL> select * from t;
ID ---------- 1 2
create or replace procedure batchDML(param in strings)--param is nested table as begin for i in param.first .. param.last loop execute immediate param(i); --记录日记 insert into proc_log_notes values(param(i),sysdate); end loop; end batchDML;
这是我写的一个根据权限判断是不是有修改的权利游标写的 declare cursor cc is select id, name, quanid, jieguo from timubianyi; i cc%rowtype; aa varchar2(40); begin open cc; fetch cc into i; while cc%found loop if i.quanid=1 then dbms_output.put_line(i.name||' '||i.jieguo); dbms_output.put_line('你没有修改的权利');
else update timubianyi t1 set t1.name = '8*5', t1.quanid = 2, t1.jieguo = '40' where t1.id = 1; dbms_output.put_line('你执行了修改的语句');
每次调用一下procedure就可以呀
is
begin
execute immediate p_str;
--执行成功 输出一些提示信息
dbms_output.put_line(p_str || 'is finished' || sysdate);
end;
等你循环结束后再commit;
begin
--批量语句
end;
方式一起执行的
begin
insert into a values('a');
update a set fld='b';
commit;
end;
PROCEDURE DOPATCH
AS
l_compile_sql varchar2(4000);
BEGIN
if l_compile_sql is not null then
begin
execute immediate l_compile_sql;
dbms_output.put_line('OK'|| sysdate);
commit;
exception
when others then
dbms_output.put_line('FAILED'|| sysdate);
end;
end if;
END DOPATCH;这么写对不对呢.
你是通过java或者c来调用procedure的吧 等list遍历结束在java或者c里进行commit;
你可以一次传入关键数据通过存储过程来处理,
并不需要在外面构造好SQL语句。这样会增大网络传输量。
然后存储过程有个什么对象能接住,
然后这个对象就得到了这个list,知道list的count,
然后一条条执行的.我不怎么熟悉,也不知道是记忆错误,还是概念错误的.
要是可以这么干的话,能不能写个代码提示呢.谢谢啊.
没有很多吧,你别搞个几十万的数组,然后中间都不提交一下,效率很低的
好像java java.sql没有对应的api,但是oracle.jdbc是有的,我曾经帮人写过这样一个传数组的东西
难道java的Statement里面没有batch,不需要用存储过程的
如果非要存储过程,必须用和oracle.jdbc与存储过程对应的数组等会帮你写个
不过不要紧,
关键是oralce里面怎么写,刚查看了一下,说要定义一个自己的数组做为参数,我就说我在那里选择的时候只看见了,常规类型的参数,没有看见数组类型的吗.
import java.sql.DriverManager;import oracle.jdbc.driver.OracleCallableStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
/**
*
* @author dingjun
* 实现批量传SQL语句到Procedure中执行
*/
public class Batch2Prc { public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:ora", "username", "passwd");
conn.setAutoCommit(false);
// 创建String[]
String[] arr = { "drop table test", "create table test(id number)"
,"insert into test values(1)"};
//利用oracle API
OracleCallableStatement stmt = (OracleCallableStatement) conn
.prepareCall("{call batchDML(?)}");
//创建ArrayDescriptor,其中第1个参数是对应oracle中的嵌套表类型
ArrayDescriptor desc = ArrayDescriptor
.createDescriptor("STRINGS", conn);
//创建ARRAY对象,将java数组传入
ARRAY array = new ARRAY(desc, conn, arr);
//设置存储过程参数
stmt.setARRAY(1, array);
stmt.execute();
conn.commit();
conn.close();
stmt.close();
}}
/*
* create or replace type strings is table of varchar2(32767);--nested table
create table proc_log_notes (sql_text varchar2(2000),done_date date);
--procedure
create or replace procedure batchDML(param in strings)--param is nested table
as
begin
for i in param.first .. param.last loop
execute immediate param(i);
end loop;
end batchDML;
*/这也不是什么批处理,测试后是这样:
SQL> select * from proc_log_notes;
SQL_TEXT DONE_DATE
-------------------------------------------------------------------------------- -----------
drop table test 2010/5/9 19
create table test(id number) 2010/5/9 19
insert into test values(1) 2010/5/9 19
2 /
Type created
SQL>
SQL> CREATE OR REPLACE PROCEDURE
2 PRO(p_str IN myvarray_list) IS
3 begin
4 for i in 1..p_str.count loop
5 execute immediate p_str(i);
6 dbms_output.put_line(sysdate);
7 end loop;
8 end;
9 /
Procedure created
SQL> select * from t;
ID
----------
SQL> declare
2 a myvarray_list;
3 begin
4 a := myvarray_list();
5 a.extend;
6 a(1):= 'insert into t values(1)';
7 a.extend;
8 a(2):= 'insert into t values(2)';
9 pro(a);
10 end;
11 /
2010-05-09 19:15:31
2010-05-09 19:15:31
PL/SQL procedure successfully completed
SQL> select * from t;
ID
----------
1
2
as
begin
for i in param.first .. param.last loop
execute immediate param(i);
--记录日记
insert into proc_log_notes values(param(i),sysdate);
end loop;
end batchDML;
myvarray_list只定义含有两个元素 根据你的需求进行相应修改
外部程序的话 给procedure传个数组就可以
declare
cursor cc is select id, name, quanid, jieguo from timubianyi;
i cc%rowtype;
aa varchar2(40);
begin
open cc;
fetch cc into i;
while cc%found loop
if i.quanid=1 then
dbms_output.put_line(i.name||' '||i.jieguo);
dbms_output.put_line('你没有修改的权利');
else
update timubianyi t1
set
t1.name = '8*5',
t1.quanid = 2,
t1.jieguo = '40'
where t1.id = 1;
dbms_output.put_line('你执行了修改的语句');
end if;
fetch cc into i;
end loop;
close cc;
end;