oracle 的存储过程在java怎么调用
发布网友
发布时间:2022-04-20 11:16
我来回答
共3个回答
懂视网
时间:2022-04-30 00:56
初次研究出bug的地方非常多,所以注意点非常多,花了我三天时间除尽所有bug,我会把注意点都列出来,可能有落下的地方,还请多指正,相互探讨。
首先上最终测试成功版存储过程代码:(里面代码可能不尽对你都有用,借鉴参考吧,我全贴出来也是为了我以后好查)
说一下jar包用的是ojdbc14.jar,至于什么class12.jar、ojdbc6.jar啊应该都可以,只要一种就可以了。
说说我的需求,以便让大家更顺利的看懂我的代码,我的需求是:存储过程从Java端接收两个参数userid(用户)和topicid(话题),在存储过程进行循环查询当前用户对当前话题的点赞记录,如果有记录,则record为设置1,没有则为0,最后返回一个结果集,是反应 用户=>话题=>record相互对应的关系表。
好了不废话了,上代码吧,学习阶段,所以难免情绪波动和啰嗦,也是希望以最直白能懂的方式叙述出来。
------------在数据库建立一个type,对应JAVA端要传入的对象结构 :
create or replace type tp_arr3 as Object
(
userid nvarchar2(40), --这里从varchar2改成nvarchar2类型才能跟Java的String匹配上
topicid nvarchar2(40),
record nvarchar2(4)
)
---多次测试创建可能会出现“ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型”这个错误,这时只要换一个类型名字再创建就可以了
----------
CREATE OR REPLACE TYPE tp_arr_tbl3 AS TABLE OF tp_arr3
--------------创建包 ,创建一个游标类型用来放输出参数
create or replace package testpkg as
type testcur is ref cursor;
end testpkg;
------创建存储过程 ,定义两个参数,一个入参,是一个对象类型数组(这种类型应该可以满足大部分复杂需求了),一个出参,是用游标存放查询值
CREATE OR REPLACE procedure findRecord(type_obj IN tp_arr_tbl3,result out testpkg.testcur)
as
t tp_arr3;
sql2 varchar2(500);
sql3 varchar2(500);
v_count varchar2(4); --临时中间变量,用来存放对应的record
BEGIN
sql2 :='drop table tb_temp';
sql3 :='CREATE TABLE tb_temp( userid varchar2(40),topicid varchar2(40) primary key, record varchar2(4))';
execute immediate sql2;
execute immediate sql3;
FOR i IN type_obj.first()..type_obj.last()
LOOP
t:= type_obj(i);
select count(*) into v_count from scott.tb_praise_rel where userid=t.userid and topicid=t.topicid;
dbms_output.put_line( t.userid || '=>'||t.topicid ||'=>' || v_count);
insert into tb_temp values (t.userid,t.topicid, v_count);
END LOOP;
COMMIT;
open result for select * from tb_temp;
END;
--------------执行存储过程
declare
ta tp_arr_tbl3:=tp_arr_tbl3(); --对象的声明
t tp_arr3:=tp_arr3('0','0','0'); --声明及赋初值(必要步骤)
begin
for i in 1..12 loop
ta.extend;
t.userid:='1';
t.topicid:=i;
t.record:='0';
ta(i):=t;
findRecord(ta);
end loop;
end findRecord;
----------------------表查询测试部分
select * from tb_temp;
select * from scott.tb_praise_rel;
select userid from scott.tb_praise_rel where userid='1' and topicid='1';
-----------------------游标测试,后来没用,可以略过
cursor testcur is select userid,topicid from scott.tb_praise_rel;
cur testcur%rowtype;
open testcur;
loop
fetch testcur into cur;
exit when testcur%notfound;
dbms_output.put_line( 'userid:' || cur.userid || ',topicid:' || cur.topicid );
update tb_temp set record='1' where userid=cur.userid and topicid=cur.topicid;
end loop;
dbms_output.put_line('----------------------');
close testcur;
COMMIT;
注意点:1.测试用户起初用的Scott,发现没有执行权限,对其进行赋予dba权限还是不行,遂后来用的system;
2.多次测试创建可能会出现“ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型”这个错误,这时只要换一个类型名字再创建就可以了;
3.由于我的tb_temp表有唯一字段约束,所以存储过程每次进来先删表,再建表,再插入数据;
4.记得该打分号的地方不要漏,不该打的地方不要多;
5.执行存储过程的时候,要先声明并赋初值,不然也会报错;
6.注意pl/sql里执行存储过程测试赋值时候ta.extend不能少;
6.自定义类型要注意的地方很多,比如nvarchar2和JavaString类型的定义;
7.简单说<span style="font-family: Arial, Helvetica, sans-serif;">tp_arr3 类型是指一条记录,</span><span style="font-family: Arial, Helvetica, sans-serif;">tp_arr_tbl3是指多条记录;</span>
--点赞关系表
create table tb_praise_rel(
id varchar2(40) primary key,
userid varchar2(40), --用户id
topicid varchar2(40), --话题id
remarks1 varchar2(3000), --备用字段
remarks2 varchar2(3000),
remarks3 varchar2(3000)
);
再上Java调用代码:
package com.lofter.svntesr;
import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import oracle.jdbc.OracleTypes;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
import com.lofter.bean.ProcedureBean;
public class ProcedureTest3 {
/**
* @param args
*/
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:orcl";
//网上很多卡在获取con这个地方的,我最初也是,说是什么jar包问题,删掉class12啊,什么oracle与apache连接池冲突啊,但是我其实是存储过程没写对,最后绕了一圈回来还是用的这种方法测试通过,并没有加((org.apache.commons.dbcp.PoolableConnection) conn).getInnermostDelegate()
Connection con = DriverManager.getConnection(url, "system", "a");
// PreparedStatement pstmt = null;
CallableStatement cs = null;
ResultSet rs=null;
List<ProcedureBean> list = new ArrayList<ProcedureBean>();
for (int i = 1; i <= 12; i++) {
String r = i + "";
list.add(new ProcedureBean("1", r, "0"));
}
// list.add(new ProcedureBean("1","5f60b0f0-03d9-4671-b945-936fe821fe19", "0"));
//如果存储过程是用我这种对象数组as object类型,则java调用这一步必不可少,这是对之前在pl/sql中声明的tp_arr3 类型的映射,表示在pl/sql中去匹配你自定义的类型
//还有注意要大写,不然可能会报“无效名称模式”
StructDescriptor recDesc = StructDescriptor.createDescriptor(
"TP_ARR3", con);
//这一步是将你自定义的类型转化成oracle自己的类型,即STRUCT,相当于一个Object类,因为oracle的开发人员也不知道你会定义一个什么名字的类型,反正只用提供一个规则,最后大家都照着这个规则来转化就是了
ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();
for (ProcedureBean pb : list) {
System.out.println(pb);
Object[] objs = new Object[3];
objs[0] = pb.getUserid();
objs[1] = pb.getTopicid();
objs[2] = pb.getRecord();
STRUCT item = new STRUCT(recDesc, con, objs);
pstruct.add(item);
}
//这是第二步映射,映射我在oracle中自定义的tp_arr_tbl3类型,注意也要大写,网上也有说要加包名,不是同一个用户要加用户前缀什么的,我没有加,测试也通过,可能不是极端情况吧
oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor("TP_ARR_TBL3", con);
oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc, con, pstruct.toArray());
//也有说调用的时候要加包名的
cs = con.prepareCall("{call findRecord(?,?)}");
//设置参数这里,1、2分别对应存储过程findRecord(?,?)中参数的位置,注意位置不要错了
cs.setArray(1, array);
cs.registerOutParameter(2, OracleTypes.CURSOR);
cs.execute();
rs=(ResultSet) cs.getObject(2); //取数据也是根据对应参数位置来的
while( rs.next() ){
System.out.println("result : " + rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3));
}
con.commit();
} catch (Exception e) {
e.printStackTrace();
}
}
}
Java调用注意:基本上注意事项都以注释的方式写在代码里了,也有可能没想起来,很多bug信息由于测试通过心切,没能及时复制下来。注意不要导错包
2.错误信息“Message file ‘oracle.jdbc.driver.Messages‘ is missing.”,可能是你写错了或类型与oracle中不匹配,不要去找什么jar包啊什么的,网上信息也不多,我在这绕了好久,多检查一下上面提到的加包名、大小写、转类型什么的;
还有其他没想起来或没碰到的bug只有亲们多结合错误信息猜测,多动手测测,相信就会迎刃而解了。
测试的javaBean:
package com.lofter.bean;
import java.io.Serializable;
public class ProcedureBean implements Serializable {
private static final long serialVersionUID = 809894604693791308L;
private String userid;
private String topicid;
private String record;
public ProcedureBean() {
super();
}
public ProcedureBean(String userid, String topicid, String record) {
super();
this.userid = userid;
this.topicid = topicid;
this.record = record;
}
public String getUserid() {
return userid;
}
public void setUserid(String userid) {
this.userid = userid;
}
public String getTopicid() {
return topicid;
}
public void setTopicid(String topicid) {
this.topicid = topicid;
}
public String getRecord() {
return record;
}
public void setRecord(String record) {
this.record = record;
}
@Override
public String toString() {
return "ProcedureBean [userid=" + userid + ", topicid=" + topicid
+ ", record=" + record + "]";
}
}
折腾了我好几天研究这个东西,主要是要研究对象数组类型的,从语法都不清楚,只能参照着能看懂大概的别人代码揣测着写,到最后测试通过,一把辛酸泪啊,两天研究到凌晨四点,敲了不知多少遍回车键,点了不知多少次运行(其实也没多少,可能也是对之前调试所有bug过程的一次发泄),因为复杂类型的参数网上很多没讲清楚,也有很多bug,所以没办法就用。测试期间bug不断,一直百度,也翻了下平时都没认真看过的教材,最后终于打通从PL/SQL调用到Java调用,其实bug出最多在Java调用上,各种类型不匹配,只能说搜索引擎真强大,互联网真强大,大数据真强大。最后我想说的是:“人就怕认真”。
oracle存储过程及Java调用
标签:oracle 存储过程 java
热心网友
时间:2022-04-29 22:04
java下实现调用oracle的存储过程和函数
在oracle下创建一个test的账户,然后按一下步骤执行:
1.创建表:STOCK_PRICES
--创建表格
CREATE TABLE STOCK_PRICES(
RIC VARCHAR(6) PRIMARY KEY,
PRICE NUMBER(7,2),
UPDATED DATE );
2.插入测试数据:
--插入数据
INSERT INTO stock_prices values('1111',1.0,SYSDATE);
INSERT INTO stock_prices values('1112',2.0,SYSDATE);
INSERT INTO stock_prices values('1113',3.0,SYSDATE);
INSERT INTO stock_prices values('1114',4.0,SYSDATE);
3.建立一个返回游标: PKG_PUB_UTILS
--建立一个返回游标
CREATE OR REPLACE PACKAGE PKG_PUB_UTILS IS
--动态游标
TYPE REFCURSOR IS REF CURSOR;
END PKG_PUB_UTILS;
4.创建和存储过程:P_GET_PRICE
--创建存储过程
CREATE OR REPLACE PROCEDURE P_GET_PRICE
(
AN_O_RET_CODE OUT NUMBER,
AC_O_RET_MSG OUT VARCHAR2,
CUR_RET OUT PKG_PUB_UTILS.REFCURSOR,
AN_I_PRICE IN NUMBER
)
IS
BEGIN
AN_O_RET_CODE := 0;
AC_O_RET_MSG := '操作成功';
OPEN CUR_RET FOR
SELECT * FROM STOCK_PRICES WHERE PRICE<AN_I_PRICE;
EXCEPTION
WHEN OTHERS THEN
AN_O_RET_CODE := -1;
AC_O_RET_MSG := '错误代码:' || SQLCODE || CHR(13) || '错误信息:' || SQLERRM;
END P_GET_PRICE;
5.创建函数:
--创建函数:F_GET_PRICE
CREATE OR REPLACE FUNCTION F_GET_PRICE(v_price IN NUMBER)
RETURN PKG_PUB_UTILS.REFCURSOR
AS
stock_cursor PKG_PUB_UTILS.REFCURSOR;
BEGIN
OPEN stock_cursor FOR
SELECT * FROM stock_prices WHERE price < v_price;
RETURN stock_cursor;
END;
6.JAVA调用存储过程返回结果集
代码示例:JDBCoracle10G_INVOKEPROCEDURE.java
import java.sql.*;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
/* 本例是通过调用oracle的存储过程来返回结果集:
* oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip
*/
public class JDBCoracle10G_INVOKEPROCEDURE {
Connection conn = null;
Statement statement = null;
ResultSet rs = null;
CallableStatement stmt = null;
String driver;
String url;
String user;
String pwd;
String sql;
String in_price;
public JDBCoracle10G_INVOKEPROCEDURE()
{
driver = "oracle.jdbc.driver.OracleDriver";
url = "jdbc:oracle:thin:@localhost:1521:ORCL";
// oracle 用户
user = "test";
// oracle 密码
pwd = "test";
init();
// mysid:必须为要连接机器的sid名称,否则会包以下错:
// java.sql.SQLException: Io 异常: Connection
// refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
// 参考连接方式:
// Class.forName( "oracle.jdbc.driver.OracleDriver" );
// cn = DriverManager.getConnection(
// "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );
}
public void init() {
System.out.println("oracle jdbc test");
try {
Class.forName(driver);
System.out.println("driver is ok");
conn = DriverManager.getConnection(url, user, pwd);
System.out.println("conection is ok");
statement = conn.createStatement();
// conn.setAutoCommit(false);
// 输入参数
in_price = "3.0";
// 调用函数
stmt = conn.prepareCall("call P_GET_PRICE(?,?,?,?)");
stmt.registerOutParameter(1, java.sql.Types.FLOAT);
stmt.registerOutParameter(2, java.sql.Types.CHAR);
stmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
stmt.setString(4, in_price);
stmt.executeUpdate();
int retCode = stmt.getInt(1);
String retMsg = stmt.getString(2);
if (retCode == -1) { // 如果出错时,返回错误信息
System.out.println("报错!");
} else {
// 取的结果集的方式一:
rs = ((OracleCallableStatement) stmt).getCursor(3);
// 取的结果集的方式二:
// rs = (ResultSet) stmt.getObject(3);
String ric;
String price;
String updated;
// 对结果进行输出
while (rs.next()) {
ric = rs.getString(1);
price = rs.getString(2);
updated = rs.getString(3);
System.out.println("ric:" + ric + ";-- price:" + price
+ "; --" + updated + "; ");
}
}
} catch (Exception e) {
e.printStackTrace();
} finally {
System.out.println("close ");
}
}
public static void main(String args[])// 自己替换[]
{
new JDBCoracle10G_INVOKEPROCEDURE();
}
}
7.开发JAVA调用函数返回结果集
代码示例:JDBCoracle10G_INVOKEFUNCTION.java
import java.sql.*;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
/*
/* 本例是通过调用oracle的函数来返回结果集:
* oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip
*/
public class JDBCoracle10G_INVOKEFUNCTION {
Connection conn = null;
Statement statement = null;
ResultSet rs = null;
CallableStatement stmt = null;
String driver;
String url;
String user;
String pwd;
String sql;
String in_price;
public JDBCoracle10G_INVOKEFUNCTION()
{
driver = "oracle.jdbc.driver.OracleDriver";
url = "jdbc:oracle:thin:@localhost:1521:ORCL";
// oracle 用户
user = "test";
// oracle 密码
pwd = "test";
init();
// mysid:必须为要连接机器的sid名称,否则会包以下错:
// java.sql.SQLException: Io 异常: Connection
// refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
// 参考连接方式:
// Class.forName( "oracle.jdbc.driver.OracleDriver" );
// cn = DriverManager.getConnection(
// "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );
}
public void init() {
System.out.println("oracle jdbc test");
try {
Class.forName(driver);
System.out.println("driver is ok");
conn = DriverManager.getConnection(url, user, pwd);
System.out.println("conection is ok");
statement = conn.createStatement();
// conn.setAutoCommit(false);
// 输入参数
in_price = "5.0";
// 调用函数
stmt = conn.prepareCall("{? = call F_GET_PRICE(?)}");
// stmt.registerOutParameter(1, java.sql.Types.FLOAT);
// stmt.registerOutParameter(2, java.sql.Types.CHAR);
stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
stmt.setString(2, in_price);
stmt.executeUpdate();
// 取的结果集的方式一:
rs = ((OracleCallableStatement) stmt).getCursor(1);
// 取的结果集的方式二:
// rs = (ResultSet) stmt.getObject(1);
String ric;
String price;
String updated;
while (rs.next()) {
ric = rs.getString(1);
price = rs.getString(2);
updated = rs.getString(3);
System.out.println("ric:" + ric + ";-- price:" + price + "; --"
+ updated + "; ");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
System.out.println("close ");
}
}
public static void main(String args[])// 自己替换[]
{
new JDBCoracle10G_INVOKEFUNCTION();
}
}
热心网友
时间:2022-04-29 23:22
以前有人在百度知道上做过类似的回答,我就是转发一下。你可以参考下面的链接。
http://zhidao.baidu.com/question/326714348332776205.html?qbl=relate_question_0&word=java%20callablestatement
求java调用oracle存储过程
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 TestProc...
java调用oracle存储过程
//创建callablestatement调用存储过程 cs=ct.prepareCall("{call pro_fenye(?,?,?,?,?,?)}");//给前三个in输入 ?赋值,后三个为out返回参数 cs.setString(1, "emp");cs.setInt(2, 5);cs.setInt(3, 1);//注册 cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);cs.regist...
如何在ORACLE中使用JAVA存储过程
Java调用Oracle的存储过程 try { Class.forName(driverName);conn = DriverManager.getConnection(url, username, password);stat = conn.prepareCall(sql);// 一个输入参数和三个输出参数 stat.setInt(1, 7566);stat.registerOutParameter(2, OracleTypes.VARCHAR);stat.registerOutParameter(3, OracleTypes...
在JAVA中怎么调用带参数的存储过程啊??
在Java里面调用存储过程,写法那是相当的固定:Class.forName(...Connection conn = DriverManager.getConnection(.../ p是要调用的存储过程的名字,存储过程的4个参数,用4个?号占位符代替 其余地方写法固定 / CallableStatement cstmt = conn.prepareCall("{call p(?,?,?,?)}");/ 告诉JDBC,这些...
Java怎么调用Oracle输入参数为一个对象的存储过程(是输入参数,不是输出...
使用 OUTPUT 参数 OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程 (titles_sum),并使用一个可选的输入参数和一个输出参数。首先,创建过程:USE pubs GO IF EXISTS(SELECT name FROM sysobjects WHERE name = 'titles_sum' AND...
java调用oracle存储过程无法获得正确的返回值,每次都是0
你通过JDBC这种方式调用存储过程,应该使用 CallableStatement 类, CallableStatement cs=conn.prepareCall(str);补充一句,避免你在执行有错,cs.excuse();这个方法为执行,然后在获取输出参数。另外写输出参数的类型时,直Types.类型即可,不用带包。
oracle 存储过程返回结果集怎么实现?用java如何调用这个存储过程?比如...
1:首先你需要创建一个包,并定义你返回的游标的类型、存储过程 create or replace package TEST_PKG is type cur_emp is REF CURSOR;procedure test_proc (emps out cur_emp);end TEST_PKG;2:然后你再创建包体 create or replace package body TEST_PKG is procedure test_proc (emps out cur_...
java如何获取oracle存储过程里的dbms_output.put_line()内容; 现在...
1.通过存储过程的出参,将“当前用户:user_id”连接串返回;比如:假设你的过程叫,prc_test,在参数列表中增加出参。create or replace procedure prc_test(入参 in 类型, 出参 out 类型) is begin -- 逻辑实现 end;出参就是你要把内容放进去,用来传值的变量。2.在过程中保存一张临时表,...
JAVA调用存储过程,Oracle自定义类型作参数怎么写法
(1) 在Oracle中定义数组类型idArray (2) 在java构造数组并转换成Oracle中定义的数组类型,调用存储过程 /** * 当要删除图书时,检查是否仍然有图书复本处于借出状态 */ public boolean checkBookStatus(String[] bookIds) throws DataAccessException { boolean flag = false; Connection conn = ...
java程序在调用oracle的存储过程时 能不能传入一个数组
import oracle.sql.ArrayDescriptor;import oracle.sql.Datum;/ Java获取Oracle存储过程返回自定义类型 author lucky star / public class OracleTypeResultTest { / param args / public static void main(String[] args) { Connection con = null;OracleCallableStatement ocs = null;String sql = "{...