欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

Oracle存储过程及Java调用

程序员文章站 2022-05-28 18:50:14
...

说说我的需求,以便让大家更顺利的看懂我的代码,我的需求是:存储过程从Java端接收两个参数userid(用户)和topicid(话题),在

初次研究出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;