Oracle存储过程及Java调用

初次研究出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.简单说tp_arr3 类型是指一条记录，tp_arr_tbl3是指多条记录；--点赞关系表
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相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址