一个简单的java调用储存过程
package com.topband.testmain;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
public class t {
public static void main(String[] args ){
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@192.168.8.88:1521: testoa";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
System.out.println("输出值");
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "数据库名", "密码");
CallableStatement proc = null;
proc = conn.prepareCall("{ call prc_tbtest(?)}");
if(proc != null)
{
proc.registerOutParameter(1,Types.INTEGER);
proc.execute(); //提交存储过程
String testPrint = proc.getString(1);
System.out.println("输出值"+testPrint);
}
}
catch (SQLException ex2) {
ex2.printStackTrace();
}
catch (Exception ex2) {
ex2.printStackTrace();
}
finally{
try {
if(rs != null){
rs.close();
if(stmt!=null){
stmt.close();
}
if(conn!=null){
conn.close();
}
}
}
catch (SQLException ex1) {
}
}
}
}
create or replace procedure prc_tbtest(res out int) is num_count int; begin execute immediate ' select count(*) from whir$hrresume ' into num_count; if num_count >0 then res :=1; else res :=2; end if; END prc_tbtest;
批量储存
package com.topband.testmain;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import com.topband.zhugx.model.POItemBean;
public class CCGC1 {
public static void main(String arg[]) {
long t1 = System.currentTimeMillis();
List itemlist= new ArrayList();
//********取值方法未上传
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@192.168.8.88:1521: testoa";
Connection con = DriverManager.getConnection(url, "数据库","密码");
CallableStatement pstmt = null;
String sql = "{call po_itemone(?,?,?,?)}";
pstmt = con.prepareCall(sql);
Object[][] object1 = new Object[1][28];
for (int i = 0; i < 1; i++) {
POItemBean itembean = (POItemBean) itemlist.get(i);
// object1[i][0] = itembean.getEBELN();
// object1[i][1] = itembean.getEBELP();
// object1[i][2] = itembean.getMATNR();
// object1[i][3] = itembean.getMEINS();
// object1[i][4] = itembean.getNETPR();
// object1[i][5] = itembean.getZPEICE_TOTAL();
// object1[i][6] = itembean.getSTPRS();
// object1[i][7] = itembean.getLOEKZ();
// object1[i][8] = itembean.getELIKZ();
// object1[i][9] = itembean.getRETPO();
// object1[i][10] = itembean.getREMARK2();
// object1[i][11] = itembean.getBANFN();
// object1[i][12] = itembean.getLGORT();
// object1[i][13] = itembean.getWERKS();
// object1[i][14] = itembean.getZMAKTX();
// object1[i][15] = itembean.getMENGE_PR();
// object1[i][16] = itembean.getMENGE();
// object1[i][17] = itembean.getSLCY();
// object1[i][18] = itembean.getJGCY();
// object1[i][19] = itembean.getWAERS();
// object1[i][20] = itembean.getKBETR();
// object1[i][21] = itembean.getEINDT();
// object1[i][22] = itembean.getMATKL();
// object1[i][23] = itembean.getYJHSL();
// object1[i][24] = itembean.getWJHSL();
// object1[i][25] = itembean.getSjjh();
// object1[i][26] = itembean.getPodesc();
// object1[i][27] = itembean.getWj();
object1[i][0] = "88";
object1[i][1] = "10";
object1[i][2] = "10";
object1[i][3] = "10";
object1[i][4] = "10";
object1[i][5] = "10";
object1[i][6] = "10";
object1[i][7] = "10";
object1[i][8] = "10";
object1[i][9] = "10";
object1[i][10] = "10";
object1[i][11] = "10";
object1[i][12] = "10";
object1[i][13] = "10";;
object1[i][14] = "10";
object1[i][15] = "10";
object1[i][16] = "10";
object1[i][17] = "10";
object1[i][18] = "10";
object1[i][19] = "10";
object1[i][20] = "10";
object1[i][21] ="10";
object1[i][22] = "10";
object1[i][23] = "10";
object1[i][24] = "10";
object1[i][25] = "10";
object1[i][26] = "10";
object1[i][27] = "10";
}
String usid = "2222";
String org = "33333";
oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor("ARRAY_OBJECT2", con);
oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc, con, object1);
pstmt.setArray(1, array);
pstmt.setString(2, usid);
pstmt.setString(3, org);
pstmt.registerOutParameter(4, java.sql.Types.INTEGER);
pstmt.executeUpdate();
String testPrint = pstmt.getString(4);
System.out.println("输出值"+testPrint);
} catch (Exception e) {
e.printStackTrace();
}
//System.out.println(System.currentTimeMillis() - t1);
}
}
批量储存过程
---创建类型对应java list create or replace type srm_item as object( WHIR$SRMPOITEM_TBEBELN VARCHAR2(255), WHIR$SRMPOITEM_TBEBELP INTEGER, WHIR$SRMPOITEM_MATNR VARCHAR2(255), WHIR$SRMPOITEM_MEINS VARCHAR2(255), WHIR$SRMPOITEM_NETPR NUMBER, WHIR$SRMPOITEM_ZPEICE_TO NUMBER, WHIR$SRMPOITEM_STPRS NUMBER, WHIR$SRMPOITEM_LOEKZ VARCHAR2(255), WHIR$SRMPOITEM_ELIKZ VARCHAR2(255), WHIR$SRMPOITEM_RETPO VARCHAR2(255), WHIR$SRMPOITEM_REMARK2 VARCHAR2(255), WHIR$SRMPOITEM_BANFN VARCHAR2(255), WHIR$SRMPOITEM_LGORT VARCHAR2(255), WHIR$SRMPOITEM_WERKS VARCHAR2(255), WHIR$SRMPOITEM_ZMAKTX VARCHAR2(255), WHIR$SRMPOITEM_MENGE_PR INTEGER, WHIR$SRMPOITEM_MENGE INTEGER, WHIR$SRMPOITEM_SLCY INTEGER, WHIR$SRMPOITEM_JGCY NUMBER, WHIR$SRMPOITEM_WAERS VARCHAR2(255), WHIR$SRMPOITEM_KBETR VARCHAR2(255), WHIR$SRMPOITEM_EINDT VARCHAR2(255), WHIR$SRMPOITEM_MATKL VARCHAR2(255), WHIR$SRMPOITEM_YJHSL INTEGER, WHIR$SRMPOITEM_WJHSL INTEGER, WHIR$T3067_F3368 VARCHAR2(255), WHIR$T3067_PODESC VARCHAR2(500), WHIR$T3067_FOREIGNKEY NUMBER(20) ); --绑定类型 CREATE OR REPLACE TYPE ARRAY_object2 AS table OF srm_item create or replace procedure po_itemone( i_item in array_object2,res out int --item 列表 array_object2 在上述定义过 whir$SRMPOITEM_WJHSL = '||i_item(i).whir$SRMPOITEM_WJHSL||' - whir$SRMPOITEM_ZTSL, ) is num_count int; zero int; begin for i in 1..i_item.count loop execute immediate 'select count(*) from whir$t3067 where whir$SRMPOITEM_TBEBELN = '''|| i_item(i).WHIR$SRMPOITEM_TBEBELN||''' and whir$SRMPOITEM_TBEBELP = '|| i_item(i).WHIR$SRMPOITEM_TBEBELP into num_count; if num_count >0 then res :=1; execute immediate 'update whir$t3067 set whir$SRMPOITEM_MATNR = '''||i_item(i).whir$SRMPOITEM_MATNR||''', whir$SRMPOITEM_MEINS = '''||i_item(i).whir$SRMPOITEM_MEINS||''', whir$SRMPOITEM_NETPR = '||i_item(i).whir$SRMPOITEM_NETPR||', whir$SRMPOITEM_ZPEICE_TO = '||i_item(i).whir$SRMPOITEM_ZPEICE_TO||', whir$SRMPOITEM_STPRS = '||i_item(i).whir$SRMPOITEM_STPRS||', whir$SRMPOITEM_LOEKZ = '''||i_item(i).whir$SRMPOITEM_LOEKZ||''', whir$SRMPOITEM_ELIKZ = '''||i_item(i).whir$SRMPOITEM_ELIKZ||''', whir$SRMPOITEM_RETPO = '''||i_item(i).whir$SRMPOITEM_RETPO||''', whir$SRMPOITEM_REMARK2 = '''||i_item(i).whir$SRMPOITEM_REMARK2||''', whir$SRMPOITEM_BANFN = '''||i_item(i).whir$SRMPOITEM_BANFN||''', whir$SRMPOITEM_LGORT = '''||i_item(i).whir$SRMPOITEM_LGORT||''', whir$SRMPOITEM_WERKS = '''||i_item(i).whir$SRMPOITEM_WERKS||''', whir$SRMPOITEM_ZMAKTX = '''||i_item(i).whir$SRMPOITEM_ZMAKTX||''', whir$SRMPOITEM_MENGE_PR = '||i_item(i).whir$SRMPOITEM_MENGE_PR||', whir$SRMPOITEM_MENGE = '||i_item(i).whir$SRMPOITEM_MENGE||', whir$SRMPOITEM_SLCY = '||i_item(i).whir$SRMPOITEM_SLCY||', whir$SRMPOITEM_JGCY = '||i_item(i).whir$SRMPOITEM_JGCY||', whir$SRMPOITEM_WAERS = '''||i_item(i).whir$SRMPOITEM_WAERS||''', whir$SRMPOITEM_KBETR = '''||i_item(i).whir$SRMPOITEM_KBETR||''', whir$SRMPOITEM_EINDT = '''||i_item(i).whir$SRMPOITEM_EINDT||''', whir$SRMPOITEM_MATKL = '''||i_item(i).whir$SRMPOITEM_MATKL||''', whir$SRMPOITEM_YJHSL = '||i_item(i).whir$SRMPOITEM_YJHSL||', whir$SRMPOITEM_WJHSL = '||i_item(i).whir$SRMPOITEM_WJHSL||' - whir$SRMPOITEM_ZTSL, whir$t3067_f3368 = '''||i_item(i).whir$t3067_f3368||''', whir$t3067_PODESC = '''||i_item(i).whir$t3067_PODESC||''' where whir$SRMPOITEM_TBEBELN = '''||i_item(i).WHIR$SRMPOITEM_TBEBELN||''' and whir$SRMPOITEM_TBEBELP = '||i_item(i).WHIR$SRMPOITEM_TBEBELP; else -- select WF_SEQUENCE.nextVal into var_item_id from dual; --取索引下表 zero :=0; res :=2; execute immediate 'insert into whir$t3067(whir$t3067_ID, whir$t3067_OWNER, whir$t3067_ORG, whir$SRMPOITEM_TBEBELN, whir$SRMPOITEM_TBEBELP, whir$SRMPOITEM_MATNR, whir$SRMPOITEM_MEINS, whir$SRMPOITEM_NETPR, whir$SRMPOITEM_ZPEICE_TO, whir$SRMPOITEM_STPRS, whir$SRMPOITEM_LOEKZ, whir$SRMPOITEM_ELIKZ, whir$SRMPOITEM_RETPO, whir$SRMPOITEM_REMARK2, whir$SRMPOITEM_BANFN, whir$SRMPOITEM_LGORT, whir$SRMPOITEM_WERKS, whir$SRMPOITEM_ZMAKTX, whir$SRMPOITEM_MENGE_PR, whir$SRMPOITEM_MENGE, whir$SRMPOITEM_SLCY, whir$SRMPOITEM_JGCY, whir$SRMPOITEM_WAERS, whir$SRMPOITEM_KBETR, whir$SRMPOITEM_EINDT, whir$SRMPOITEM_MATKL, whir$SRMPOITEM_YJHSL, whir$SRMPOITEM_WJHSL, whir$SRMPOITEM_ZTSL, whir$t3067_PODESC, WHIR$T3067_FOREIGNKEY)values(hibernate_sequence.nextval,'||zero||','''||zero||''', '''||i_item(i).whir$SRMPOITEM_TBEBELN||''', '||i_item(i).whir$SRMPOITEM_TBEBELP||', '''||i_item(i).whir$SRMPOITEM_MATNR||''', '''||i_item(i).whir$SRMPOITEM_MEINS||''', '||i_item(i).whir$SRMPOITEM_NETPR||', '||i_item(i).whir$SRMPOITEM_ZPEICE_TO||', '||i_item(i).whir$SRMPOITEM_STPRS||', '''||i_item(i).whir$SRMPOITEM_LOEKZ||''', '''||i_item(i).whir$SRMPOITEM_ELIKZ||''', '''||i_item(i).whir$SRMPOITEM_RETPO||''', '''||i_item(i).whir$SRMPOITEM_REMARK2||''', '''||i_item(i).whir$SRMPOITEM_BANFN||''', '''||i_item(i).whir$SRMPOITEM_LGORT||''', '''||i_item(i).whir$SRMPOITEM_WERKS||''', '''||i_item(i).whir$SRMPOITEM_ZMAKTX||''', '||i_item(i).whir$SRMPOITEM_MENGE_PR||', '||i_item(i).whir$SRMPOITEM_MENGE||', '||i_item(i).whir$SRMPOITEM_SLCY||', '||i_item(i).whir$SRMPOITEM_JGCY||', '''||i_item(i).whir$SRMPOITEM_WAERS||''', '''||i_item(i).whir$SRMPOITEM_KBETR||''', '''||i_item(i).whir$SRMPOITEM_EINDT||''', '''||i_item(i).whir$SRMPOITEM_MATKL||''', '||zero||', '||i_item(i).whir$SRMPOITEM_WJHSL||', '||zero||', '''||i_item(i).whir$t3067_PODESC||''', '||i_item(i).WHIR$T3067_FOREIGNKEY||')'; end if; end loop; END po_itemone;