- 浏览: 146581 次
文章分类
最新评论
-
Alors:
Quartz这个类没有,报错
spring3.1和quatz2实现数据库持久化和动态加载 -
flyingcatjj:
...
Java中的break Label 和continue Label -
hehebaiy:
...
Maven安装、配置、使用
本文为摘录学习笔记.
一:无返回值的存储过程
存储过程为:
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;
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }");
proc.setString(1, "100");
proc.setString(2, "TestOne");
proc.execute();
}
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) {
}
}
}
}
当然了,这就先要求要建张表TESTTA,里面两个字段(I_ID,I_NAME)。
二:有返回值的存储过程(非列表)
存储过程为:
CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
END TESTB;
在java里调用时就用下面的代码:
package com.hyq.src;
public class TestProcedureTWO {
public TestProcedureTWO() {
}
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;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call HYQ.TESTB(?,?) }");
proc.setString(1, "100");
proc.registerOutParameter(2, Types.VARCHAR);
proc.execute();
String testPrint = proc.getString(2);
System.out.println("=testPrint=is="+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) {
}
}
}
}
}
注意,这里的proc.getString(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。
三:返回列表
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.所以要分两部分,
1, 建一个程序包。如下:
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
2,建立存储过程,存储过程为:
CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
END TESTC;
可以看到,它是把游标(可以理解为一个指针),作为一个out 参数来返回值的。
在java里调用时就用下面的代码:
package com.hyq.src;
import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;
public class TestProcedureTHREE {
public TestProcedureTHREE() {
}
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;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "hyq", "hyq");
CallableStatement proc = null;
proc = conn.prepareCall("{ call hyq.testc(?) }");
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet)proc.getObject(1);
while(rs.next())
{
System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");
}
}
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) {
}
}
}
}
在这里要注意,在执行前一定要先把oracle的驱动包放到class路径里,否则会报错的。
使用oracle存储过程分页的例子
1, 建一个程序包。如下:
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
2,建立存储过程,存储过程为:
create or replace procedure TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR,lowerNum in numeric,higherNum in numeric) is
begin
OPEN p_CURSOR FOR select * from ( select row_.*, rownum rownum_ from (select id,title,status from HYQTEST) row_ where rownum <= higherNum) where rownum_ >lowerNum;
end TESTC;
使用plsql测试:
declare
lowerNum integer;
higherNum integer;
id varchar2(10);
title varchar2(500);
status numeric;
c testpackage.Test_CURSOR;
rownum_ integer;
begin
lowerNum:=1;
higherNum:=10;
TESTC(c,lowerNum,higherNum);
LOOP
FETCH c INTO id,title,status,rownum_;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('=行号='||rownum_||'=='||id||'==='||title||'===='||status||'==');
END LOOP;
CLOSE c;
end;
实现 JAVA中的LIST输入参数
一:无返回值的存储过程
存储过程为:
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;
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call HYQ.TESTA(?,?) }");
proc.setString(1, "100");
proc.setString(2, "TestOne");
proc.execute();
}
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) {
}
}
}
}
当然了,这就先要求要建张表TESTTA,里面两个字段(I_ID,I_NAME)。
二:有返回值的存储过程(非列表)
存储过程为:
CREATE OR REPLACE PROCEDURE TESTB(PARA1 IN VARCHAR2,PARA2 OUT VARCHAR2) AS
BEGIN
SELECT INTO PARA2 FROM TESTTB WHERE I_ID= PARA1;
END TESTB;
在java里调用时就用下面的代码:
package com.hyq.src;
public class TestProcedureTWO {
public TestProcedureTWO() {
}
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;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, " hyq ", " hyq ");
CallableStatement proc = null;
proc = conn.prepareCall("{ call HYQ.TESTB(?,?) }");
proc.setString(1, "100");
proc.registerOutParameter(2, Types.VARCHAR);
proc.execute();
String testPrint = proc.getString(2);
System.out.println("=testPrint=is="+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) {
}
}
}
}
}
注意,这里的proc.getString(2)中的数值2并非任意的,而是和存储过程中的out列对应的,如果out是在第一个位置,那就是proc.getString(1),如果是第三个位置,就是proc.getString(3),当然也可以同时有多个返回值,那就是再多加几个out参数了。
三:返回列表
由于oracle存储过程没有返回值,它的所有返回值都是通过out参数来替代的,列表同样也不例外,但由于是集合,所以不能用一般的参数,必须要用pagkage了.所以要分两部分,
1, 建一个程序包。如下:
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
2,建立存储过程,存储过程为:
CREATE OR REPLACE PROCEDURE TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR) IS
BEGIN
OPEN p_CURSOR FOR SELECT * FROM HYQ.TESTTB;
END TESTC;
可以看到,它是把游标(可以理解为一个指针),作为一个out 参数来返回值的。
在java里调用时就用下面的代码:
package com.hyq.src;
import java.sql.*;
import java.io.OutputStream;
import java.io.Writer;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import oracle.jdbc.driver.*;
public class TestProcedureTHREE {
public TestProcedureTHREE() {
}
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;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "hyq", "hyq");
CallableStatement proc = null;
proc = conn.prepareCall("{ call hyq.testc(?) }");
proc.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet)proc.getObject(1);
while(rs.next())
{
System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"+rs.getString(2)+"</td></tr>");
}
}
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) {
}
}
}
}
在这里要注意,在执行前一定要先把oracle的驱动包放到class路径里,否则会报错的。
使用oracle存储过程分页的例子
1, 建一个程序包。如下:
CREATE OR REPLACE PACKAGE TESTPACKAGE AS
TYPE Test_CURSOR IS REF CURSOR;
end TESTPACKAGE;
2,建立存储过程,存储过程为:
create or replace procedure TESTC(p_CURSOR out TESTPACKAGE.Test_CURSOR,lowerNum in numeric,higherNum in numeric) is
begin
OPEN p_CURSOR FOR select * from ( select row_.*, rownum rownum_ from (select id,title,status from HYQTEST) row_ where rownum <= higherNum) where rownum_ >lowerNum;
end TESTC;
使用plsql测试:
declare
lowerNum integer;
higherNum integer;
id varchar2(10);
title varchar2(500);
status numeric;
c testpackage.Test_CURSOR;
rownum_ integer;
begin
lowerNum:=1;
higherNum:=10;
TESTC(c,lowerNum,higherNum);
LOOP
FETCH c INTO id,title,status,rownum_;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('=行号='||rownum_||'=='||id||'==='||title||'===='||status||'==');
END LOOP;
CLOSE c;
end;
1.插入,无返回值 oracle 的 procedure 为 create or replace procedure add_project(proid varchar2, proversion number, proname varchar2, customID varchar2 default null) is begin insert into T_LPROMIS_XMGL_GBBXMXX pro (xmid, xmbbh, xmmc,pro.khid) values (proid, proversion, proname,customID); end; 2.查询,有一个out varchar的返回值oracle 的 procedure 为 create or replace procedure query_project (proname in varchar2, proid out varchar2) is begin select pro.xmid into proid from T_LPROMIS_XMGL_GBBXMXX pro where pro.xmmc = proname; end; 3.查询,有一个out ref cursor的返回值oracle 的 procedure 为 create or replace package query_project_pck as type pro_ref_cursor_type is ref cursor; type pro_table is table of varchar(100); type pro_record is record(name varchar(100),id varchar(100)); procedure pro_procedure(name varchar,id varchar); end query_project_pck; -- define package create or replace procedure query_pro(pro_result out query_project_pck.pro_ref_cursor_type) is begin open pro_result for select pro.xmid, pro.xmmc,pro.updatetime from T_LPROMIS_XMGL_GBBXMXX pro return pro_record_type; end; --create procedure using ref cursor 下面就是jdbc call procedure 了: public static void test() throws ClassNotFoundException, SQLException{ System.out.println("=====Test.test====="); Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.32.227:1521:orcl","use","password"); //Connection conn = new Conn().getConnection(); CallableStatement cstmt = null; ResultSet rs = null; try{ // 过程调用,无返回值 cstmt = conn.prepareCall("call add_project(?,?,?)"); cstmt.setString(1,"testproject1"); cstmt.setInt(2, 0); cstmt.setString(3, "super man project"); cstmt.execute(); cstmt.setString(1, "testproject2"); cstmt.setInt(2, 0); cstmt.setString(3, "a worker project"); cstmt.execute(); cstmt.setString(1, "testproject3"); cstmt.setInt(2, 0); cstmt.setString(3, "a user project"); cstmt.execute(); System.out.println("插入成功"); //过程调用,普通类型的返回值,如第二个参数为字符串类型的返回值 cstmt = conn.prepareCall("call query_project(?,?)"); cstmt.registerOutParameter(2,Types.VARCHAR);//设置第二个参数为字符串类型返回值 cstmt.setString(1, "项目"); cstmt.execute(); System.out.println(cstmt.getString(2));//获取返回值 //过程调用,索引类型的返回值 cstmt = conn.prepareCall("call query_pro(?)"); cstmt.registerOutParameter(1, OracleTypes.CURSOR);//设置第一个参数为索引类型返回值 cstmt.execute(); rs = (ResultSet) cstmt.getObject(1);//获得此索引返回的集合 while(rs.next()) { System.out.println("id= "+rs.getString(1)); System.out.println("name="+rs.getString(2)); System.out.println("msg="+rs.getString(3)); } if(!rs.isClosed()) rs.close(); cstmt.close(); conn.close(); }catch(Exception e){ System.out.println("=====Test.test=====\n操作失败"); e.printStackTrace(); } }
package bing.oracleprocedure; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import java.sql.Types; import oracle.jdbc.OracleTypes; /** * 练习存储过程的调用 * @author bing * @version 2011-07-09 */ public class Test { public static void test(){ System.out.println("=====Test.test====="); Connection conn = new Conn().getConnection(); // 获得数据连接对象 CallableStatement cstmt = null; ResultSet rs = null; try{ /* -- 创建练习用的表t_test create table t_test( t_id number(4) not null, t_name varchar2(20), t_msg varchar2(100) ); alter table t_test add constraint pk_t_test primary key(t_id); -- 编写练习用的过程up_insert_test -- 插入数据到表t_test create or replace procedure up_insert_test(v_id in number, v_name in varchar2, v_msg in varchar2) is begin insert into t_test(t_id,t_name,t_msg) values(v_id,v_name,v_msg); end up_insert_test; / */ // 过程调用,无返回值 cstmt = conn.prepareCall("call up_insert_test(?,?,?)"); cstmt.setInt(1, 1); cstmt.setString(2, "bing"); cstmt.setString(3, "super man"); cstmt.execute(); cstmt.setInt(1, 2); cstmt.setString(2, "admin"); cstmt.setString(3, "a worker"); cstmt.execute(); cstmt.setInt(1, 3); cstmt.setString(2, "user"); cstmt.setString(3, "a user"); cstmt.execute(); System.out.println("插入成功"); /* -- 编写练习用的存储过程up_select_test_1 -- 输入id,输出name,查询t_test中是单条记录 create or replace procedure up_select_test_1 (v_id in number, v_name out varchar2) is begin select t_name into v_name from t_test where t_id = v_id; end up_select_test_1; / */ // 过程调用,返回单条记录 // oracle过程没有返回值,所有返回值都是通过out参数来替代的 cstmt = conn.prepareCall("call up_select_test_1(?,?)"); cstmt.setInt(1, 1); cstmt.registerOutParameter(2, Types.VARCHAR);// 注册out参数,注意序号对应过程的参数序号 cstmt.execute(); String name = cstmt.getString(2);// 获取out参数,注意序号对应过程的参数序号 System.out.println("查询成功"); System.out.println("name = " + name); /* -- 编写包upk_select_test,为存储过程up_select_test_2准备 -- create or replace package upk_select_test as type uc_test is ref cursor; end upk_select_test; / -- 编写存储过程up_select_test_2 -- 查询表t_test中的所有记录 create or replace procedure up_select_test_2 (uc_result out upk_select_test.uc_test) is begin open uc_result for select * from t_test; end up_select_test_2; / */ // 过程调用,返回多条记录 // 集合不能用一般的参数,必须要用pagkage,从上面的注释可以看到游标作为out参数,过程返回的是一个游标 cstmt = conn.prepareCall("call up_select_test_2(?)"); cstmt.registerOutParameter(1, OracleTypes.CURSOR); cstmt.execute(); rs = (ResultSet) cstmt.getObject(1); System.out.println("查询成功"); while(rs.next()){ System.out.println("id = " + rs.getString(1) + " name = " + rs.getString(2) + " msg = " + rs.getString(3)); } rs.close(); cstmt.close(); conn.close(); }catch(Exception e){ System.out.println("=====Test.test=====\n操作失败"); e.printStackTrace(); } } public static void main(String[] args) { new Test().test(); /* 控制台输出: =====Test.test===== 插入成功 查询成功 name = bing 查询成功 id = 3 name = user msg = a user id = 1 name = bing msg = super man id = 2 name = admin msg = a worker */ } }
实现 JAVA中的LIST输入参数
首先创建两个ORACLE数据类型,目的就是为了存储结果集数据(数组等): CREATE OR REPLACE TYPE CDW_OBJECT AS OBJECT( FISCAL_MONTH VARCHAR2(10), CUSTOMER_NUMBER VARCHAR2(10), CUSTOMER_NAME VARCHAR2(50) ); CREATE OR REPLACE TYPE CDW_TABLE AS TABLE OF CDW_OBJECT; 表结果及数据: SQL> DESC CDW_AR_SA_EXPOSURE_T; Name Type Nullable Default Comments --------------- ------------ -------- ------- -------- FISCAL_MONTH VARCHAR2(10) Y CUSTOMER_NUMBER VARCHAR2(10) Y CUSTOMER_NAME VARCHAR2(50) Y SQL> DESC CDW_AR_SA_EXPOSURE_FACT; Name Type Nullable Default Comments --------------- ------------ -------- ------- -------- FISCAL_MONTH VARCHAR2(10) Y CUSTOMER_NUMBER VARCHAR2(10) Y CUSTOMER_NAME VARCHAR2(50) Y SQL> 表结构及测试数据代码: CREATE TABLE CDW_AR_SA_EXPOSURE_FACT (FISCAL_MONTH VARCHAR2(10),CUSTOMER_NUMBER VARCHAR2(10),CUSTOMER_NAME VARCHAR2(50)); INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200708','1001','XIEFENG'); INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200705','1002','MANTISXF'); INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200706','2001','CHENGUOZHENG'); INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200808','3001','XIAOFANG'); INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200809','1056','ZIMING'); INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200810','7701','BOSHI'); INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200811','1821','BOGE'); INSERT INTO CDW_AR_SA_EXPOSURE_FACT VALUES('200903','2431','FEIZAI'); INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200811','3301','BOSHI_HAOREN'); INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200812','1921','BOGE_HAOREN'); INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES('200905','6666','FEIZAI_HAOREN'); COMMIT; 存储过程代码: CREATE OR REPLACE PROCEDURE SP_CARC_UPLOAD_FILE_TEST(OUT_CURSOR OUT SYS_REFCURSOR, V_ARRAY IN CDW_TABLE) AS -- DECLARE THE VARIABLE AND CURSOR TYPE V_STEP VARCHAR2(100); VCOUNTS NUMBER; BEGIN V_STEP := 'INSERT RECORDS FROM ARRAY'; FOR I IN 1..V_ARRAY.COUNT LOOP INSERT INTO CDW_AR_SA_EXPOSURE_T VALUES ( V_ARRAY(I).FISCAL_MONTH, V_ARRAY(I).CUSTOMER_NUMBER, V_ARRAY(I).CUSTOMER_NAME ); DBMS_OUTPUT.PUT_LINE('FISCAL_MONTH: '||V_ARRAY(I).FISCAL_MONTH); DBMS_OUTPUT.PUT_LINE('CUSTOMER_NUMBER: '||V_ARRAY(I).CUSTOMER_NUMBER); DBMS_OUTPUT.PUT_LINE('CUSTOMER_NAME: '||V_ARRAY(I).CUSTOMER_NAME); IF MOD(I,1000) = 0 THEN COMMIT; END IF; END LOOP; COMMIT; V_STEP := 'GET THE INVALID CUSTOMER COUNT'; SELECT COUNT(1) INTO VCOUNTS FROM CDW_AR_SA_EXPOSURE_T STG WHERE NOT EXISTS (SELECT 1 FROM CDW_AR_SA_EXPOSURE_FACT FACT WHERE STG.CUSTOMER_NUMBER = FACT.CUSTOMER_NUMBER AND STG.FISCAL_MONTH = FACT.FISCAL_MONTH); --FETCH CUR_MATCH_USERID INTO RT_CUR_MATCH_USERID; IF VCOUNTS = 0 THEN V_STEP := 'UPDATE USER_INFORMATION'; DBMS_OUTPUT.PUT_LINE('UPDATED SQL COUNT: ' || SQL%ROWCOUNT); OPEN OUT_CURSOR FOR SELECT DUMMY AS CUSTOMER_NUMBER FROM DUAL WHERE ROWNUM < 1; ELSE V_STEP := 'RETURN CURSOR WHICH STORED ALL INVILD CUSTOMER_DETAILS'; OPEN OUT_CURSOR FOR SELECT CUSTOMER_NUMBER FROM CDW_AR_SA_EXPOSURE_T STG WHERE NOT EXISTS (SELECT 1 FROM CDW_AR_SA_EXPOSURE_FACT FACT WHERE STG.CUSTOMER_NUMBER = FACT.CUSTOMER_NUMBER AND STG.FISCAL_MONTH = FACT.FISCAL_MONTH); END IF; V_STEP := 'DELETE DATA FROM TABLE CDW_AR_SA_EXPOSURE_T'; /*DELETE FROM CDW.CDW_AR_SA_EXPOSURE_T; COMMIT;*/ EXCEPTION WHEN OTHERS THEN BEGIN ROLLBACK; --EXECUTE IMMEDIATE 'TRUNCATE TABLE CDW_AR_SA_EXPOSURE_T REUSE STORAGE'; DBMS_OUTPUT.PUT_LINE('Error at :' || V_STEP); DBMS_OUTPUT.PUT_LINE(SQLCODE || ' : ' || SQLERRM); END; END SP_CARC_UPLOAD_FILE_TEST; JAVA 代码: import oracle.sql.*; import java.sql.*; import oracle.jdbc.driver.*; import java.util.*; public class ArrayTest { static public Connection conn; static public OracleCallableStatement stmt = null; public Connection getConnectionDB()throws SQLException, ClassNotFoundException{ Class.forName("oracle.jdbc.driver.OracleDriver"); // B. 创新新数据库连接 conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:Oraxf", "scott", "tiger"); DatabaseMetaData md = conn.getMetaData(); System.out.println("数据库版本:"); System.out.println("------------------------------------------------"); System.out.println(md.getDatabaseProductVersion()); System.out.println(); System.out.println("驱动程序名称与版本:"); System.out.println("------------------------------------------------"); System.out.print(md.getDriverName() + " " + md.getDriverVersion()); return conn; } public static void main(String[] my)throws SQLException, ClassNotFoundException{ try{ ArrayTest at = new ArrayTest(); at.getConnectionDB(); ArrayList myArray = new ArrayList(); String[][] values = { {"200811","3301","BOSHI_HAOREN"}, {"200812","1921","BOGE_HAOREN"}, {"200905","6666","FEIZAI_HAOREN"} }; myArray.add(values); ARRAY array = getArray("CDW_OBJECT","CDW_TABLE",myArray); stmt = (OracleCallableStatement)conn.prepareCall("begin SP_CARC_UPLOAD_FILE_TEST(?,?); end;"); stmt.registerOutParameter(1, OracleTypes.CURSOR,"OUT_CURSOR"); stmt.setArray(2, array); stmt.execute(); ResultSet rs = (ResultSet)stmt.getObject(1); while(rs.next()){ String CUSTOMER_NUMBER = rs.getString("CUSTOMER_NUMBER"); System.out.println("CUSTOMER_NUMBER: "+CUSTOMER_NUMBER); } at.closeConnectionDB(); } catch(Exception e){ e.printStackTrace(); } } public static ARRAY getArray(String OracleObj,String OracleTbl, ArrayList alist) throws Exception{ // my code ARRAY list = null; if (alist != null && alist.size() > 0){ StructDescriptor structdesc = new StructDescriptor(OracleObj,conn); //STRUCT[] structs = new STRUCT[alist.size()]; Object[] result = null; //String[][] myArray = new String[alist.size()][3]; //alist.toArray(myArray); /* for(int i=0;i<alist.size();i++){ String obj[] = (String [])alist.get(i); result = new Object[3]; result[0] = obj[0].toString(); result[1] = obj[1].toString(); result[2] = obj[2].toString(); structs[i] = new STRUCT(structdesc,conn,result); } for(int i=0;i<alist.size();i++){ ResultSet rs = (ResultSet)alist.get(i); for(int j=0;j<3;j++){ result = new Object[3]; result[0] = rs.getObject(1).toString(); result[1] = rs.getObject(2).toString(); result[2] = rs.getObject(3).toString(); } structs[i] = new STRUCT(structdesc,conn,result); }*/ Object[] o1 = new Object[]{"200811","3301","BOSHI_HAOREN"}; Object[] o2 = new Object[]{"200812","1921","BOGE_HAOREN"}; Object[] o3 = new Object[]{"200905","6666","FEIZAI_HAOREN"}; STRUCT s1 = new STRUCT(structdesc, conn, o1); STRUCT s2 = new STRUCT(structdesc, conn, o2); STRUCT s3 = new STRUCT(structdesc, conn, o3); STRUCT[] structs = {s1,s2,s3}; /* for(int i=0;i<alist.size();i++){ result = new Object[3]; result[0] = myArray[i][0].toString(); result[1] = myArray[i][1].toString(); result[2] = myArray[i][2].toString(); structs[i] = new STRUCT(structdesc,conn,result); }*/ ArrayDescriptor arraydesc = new ArrayDescriptor(OracleTbl,conn); list = new ARRAY(arraydesc,conn,structs); } return list; } public void closeConnectionDB()throws SQLException{ conn.close(); } } 输出结果: -- JAVA 控制台 数据库版本: ------------------------------------------------ Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production With the Partitioning, OLAP and Data Mining options 驱动程序名称与版本: ------------------------------------------------ Oracle JDBC driver 10.1.0.2.0CUSTOMER_NUMBER: 6666 CUSTOMER_NUMBER: 1921 CUSTOMER_NUMBER: 3301 -- 数据库SQLPLUS SQL> SELECT * FROM CDW_AR_SA_EXPOSURE_T; FISCAL_MONTH CUSTOMER_NUMBER CUSTOMER_NAME ------------ --------------- -------------------------------------------------- 200811 3301 BOSHI_HAOREN 200812 1921 BOGE_HAOREN 200905 6666 FEIZAI_HAOREN SQL> 在跑JAVA代码时可能会出现相关问题: java程序中使用Oracle的对象: StructDescriptor structdesc = new StructDescriptor(OracleObj,conn); 出现这个错误: java.sql.SQLException: Fail to construct descriptor: Invalid arguments java.sql.SQLException: 无法构造描述符: Invalid arguments 原因为数据库连接dbConn为空,可是这种Exception着实有点让人迷糊。切记:友好的提示信息对问题的定位和解决非常重要。
相关推荐
本文实例讲述了Python使用cx_Oracle调用Oracle存储过程的方法。分享给大家供大家参考,具体如下: 这里主要测试在Python中通过cx_Oracle调用PL/SQL。 首先,在数据库端创建简单的存储过程。 create or replace ...
oracle 存储过程实例 oracle存储过程实例
oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel oracle 存储过程导出excel
oracle 存储过程 unwrap 图形解密 工具
可以将SQL Server存储过程转为oracle存储过程的工具
ibatis调用oracle存储过程分页
帆软报表调用Oracle存储过程如果存储过程定义中没有参数,但是设计器中缺弹出一个storeParameter1参数的解决插件
4、开发指南:《Oracle存储过程入门指南&100+种真实业务场景存储过程实例.pdf》,可以帮助初学者系统学习。 5、资源包括“通用存储过程整理” 和 “真实业务存储过程整理” 两部分,通用适合各种系统,可以无缝隙...
使用java实现oracle存储过程。 共有3个小例子。实现的功能 1、无返回值的存储过程 如 insert 2、有返回值的存储过程(非列表)select id from tab 3、返回列表 如:select * from tab 顺便鄙视下csdn,作为一个it...
oracle存储过程学习经典入门 非常好的 初学者必看
oracle 存储过程 函数 dblink 绝对对工作和平时学习有价值的资料。针对个人具体情况做修改即可使用
Oracle存储过程中使用临时表 会话级临时表 事务级临时表
oracle 存储过程批量提交
oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记oracle存储过程笔记
行业内Oracle存储过程最基本的开法规范,适合oracle入门小白学习
oracle 存储过程实例 oracle 存储过程实例 oracle 存储过程实例 oracle 存储过程实例
linux系统中使用shell脚本对oracle存储过程进行编译
oracle存储过程+日期+定时任务Job oracle存储过程+日期+定时任务Job oracle存储过程+日期+定时任务Job
Oracle存储过程分页代码 Oracle存储过程分页代码 Oracle存储过程分页代码 Oracle存储过程分页代码 Oracle存储过程分页代码