Oracle复合类型参数的存储过程以及JDBC调用
来源:互联网 发布:北京网络信息传媒公司 编辑:程序博客网 时间:2024/05/22 04:54
原文:http://blog.csdn.net/fk_baker/article/details/2451365
Oracle中PL/SQL的功能非常强大,可以实现很多功能,所以会将很多业务逻辑通过存储过程实现,
但是,存储过程的参数在JDBC调用时需要进行类型之间的对应,对于简单类型,就比较简单,本文主要是
阐述PL/SQL的复合类型参数在JDBC中如何调用的.
1、nest表组合成结果集,然后以游标变量的形式返回
--创建类型
create or replace package mytest is
-- Author : ADMINISTRATOR
-- Created : 2008-5-13 11:19:28
-- Purpose : test
TYPE record_type IS RECORD(
code VARCHAR2(18),
p_name VARCHAR2(16));TYPE ref_cur_type IS REF CURSOR;
end mytest;CREATE OR REPLACE TYPE table_type IS TABLE OF record_type;
--创建过程
CREATE OR REPLACE PROCEDURE testproc( v_ref_cur out mytest.ref_cur_type) IS
v_record record_type;
v_table table_type := table_type();
BEGIN
FOR i IN 1 .. 9 LOOP
v_record := NEW record_type('45212319830810435' || i, '侯廷文' || i);
v_table.EXTEND;
v_table(i) := v_record;
END LOOP;OPEN v_ref_cur FOR
SELECT * FROM TABLE(CAST(v_table AS table_type));END testproc;
--java代码:
OracleCallableStatement cstmt = (OracleCallableStatement) con
.prepareCall("{call testproc(?)}");
cstmt.registerOutParameter(1, OracleTypes.CURSOR);
cstmt.execute();
ResultSet rs = (ResultSet) cstmt.getObject(1);
while (rs.next()) {
System.out.print(rs.getString(1) + "");
System.out.print(rs.getString(2) + "");
System.out.println();
}
2、PL/SQL varray数组类型的形式返回结果
--创建类型
CREATE OR REPLACE TYPE myarr IS VARRAY(200) OF VARCHAR2(256);
--创建过程
CREATE OR REPLACE PROCEDURE testvarray(thearr OUT myarr) IS
BEGIN
thearr := myarr();
FOR i IN 1 .. 100 LOOP
thearr.EXTEND;
thearr(i) := '我是侯廷文'||i;
END LOOP;
END testvarray;--java代码:
OracleCallableStatement cstmt = (OracleCallableStatement) con
.prepareCall("{call testvarray(?)}");
cstmt.registerOutParameter(1, OracleTypes.ARRAY, "MYARR");
cstmt.execute();
ARRAY array = cstmt.getARRAY(1);
Datum[] datas = array.getOracleArray();
for (int i = 0; i < datas.length; i++) {
System.out.println(i + ":" + new String(datas[i].getBytes()));
}
3、PL/SQL 的Object类型返回,对应jdbc的Struct
--创建类型
CREATE OR REPLACE TYPE record_type as object(code VARCHAR2(18),
p_name VARCHAR2(16));--创建过程
create or replace procedure testStruct(theObj OUT record_type) is
begin
theObj:=record_type('452134198308104351','侯廷文');
end testStruct;--java代码:
OracleCallableStatement cstmt = (OracleCallableStatement) con
.prepareCall("{call testStruct(?)}");
cstmt.registerOutParameter(1, OracleTypes.STRUCT, "RECORD_TYPE");
cstmt.execute();
STRUCT struct = cstmt.getSTRUCT(1);
Datum[] datas = struct.getOracleAttributes();
for (int i = 0; i < datas.length; i++) {
System.out.print(i + ":" + new String(datas[i].shareBytes()));
System.out.println();
}
4、PL/SQL 的table类型返回,对应jdbc的ARRAY
--创建类型
create or replace type nest_table_type IS TABLE OF VARCHAR2(200);
--创建过程
CREATE OR REPLACE PROCEDURE testtable(thetable OUT nest_table_type) IS
BEGIN
thetable := nest_table_type();
FOR i IN 1 .. 100 LOOP
thetable.EXTEND;
thetable(i) := dbms_random.STRING('X', 3) || '侯廷文' || i;
END LOOP;
END testtable;--java代码:
OracleCallableStatement cstmt = (OracleCallableStatement) con
.prepareCall("{call testtable(?)}");
cstmt.registerOutParameter(1, OracleTypes.ARRAY, "NEST_TABLE_TYPE");
cstmt.execute();
ARRAY array = cstmt.getARRAY(1);
Datum[] datas=array.getOracleArray();
for (int i = 0; i < datas.length; i++) {
System.out.print(i + ":" + new String(datas[i].shareBytes()));
System.out.println();
}
5、PL/SQL 的Index table类型,注意该类型只能在
oci8方式连接下使用,所需要的动态连接库文件(ocijdbc9.dll)要在环境变量里进行设置
(例如:path=D:/oracle/ora90/BIN),在不同的环境下OCI驱动还可能出现类装载异常,
所以返回索引表尽管方便,但偶尔会出现意想不到的错误
--创建类型
CREATE OR REPLACE PACKAGE p_type IS
TYPE index_table_type IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
END;--创建过程
create or replace procedure testIndexTable(v_iTable OUT p_type.index_table_type) is
begin
FOR i IN 1 .. 100 LOOP
v_iTable(i):=dbms_random.string('X',5)||'侯廷文'||i;
END LOOP;
end testIndexTable;--java代码:
OracleCallableStatement cstmt = (OracleCallableStatement) con
.prepareCall("{call testindextable(?)}");
cstmt.registerIndexTableOutParameter(1, 200, OracleTypes.VARCHAR, 256);
cstmt.execute();
String[] strs = (String[]) cstmt.getPlsqlIndexTable(1);
for (int i = 0; i < strs.length; i++) {
System.out.println(i + ":" + strs[i]);
}
citiplanner 用的上6
6、nestTable里装的是Person Object
--创建类型
CREATE OR REPLACE TYPE Person AS OBJECT
(
p_code VARCHAR2(32),
p_name VARCHAR2(16),
p_age NUMBER,
p_birthday DATE
);CREATE OR REPLACE TYPE person_table_type IS TABLE OF Person;
--创建过程
CREATE OR REPLACE PROCEDURE testtableofobject(v_table OUT person_table_type) IS
BEGIN
v_table := person_table_type();
FOR i IN 1 .. 5 LOOP
v_table.EXTEND;
v_table(i) := NEW person('45212319880810435' || i,
'侯廷文' || i,
20 + i,
to_date('1985-08-1' || i, 'YYYY-MM-DD'));
END LOOP;
END testtableofobject;--java代码:
OracleCallableStatement cstmt = (OracleCallableStatement) con
.prepareCall("{call testtableofobject(?)}");
cstmt.registerOutParameter(1, OracleTypes.ARRAY, "person_table_type"
.toUpperCase());
cstmt.execute();
ARRAY array = cstmt.getARRAY(1);
Datum[] datas = array.getOracleArray();
for (int i = 0; i < datas.length; i++) {
System.out.print(i + ":");
Datum[] personAttributes = ((STRUCT) datas[i])
.getOracleAttributes();
System.out.print("p_code:"
+ new String(personAttributes[0].getBytes()) + "");
System.out.print("p_name:"
+ new String(personAttributes[1].getBytes()) + "");
System.out.print("p_age:" + personAttributes[2].intValue() + "");
System.out.print("p_birthday:" + personAttributes[3].dateValue()
+ "");
System.out.println();
}
7、输入参数为nestTable里装的是Person Object
注意要将nls_charset12.jar放入ClassPath中,否则报SQLException:
Non supported character set: oracle-character-set-852
--创建类型
CREATE OR REPLACE TYPE Person AS OBJECT
(
p_code VARCHAR2(32),
p_name VARCHAR2(16),
p_age NUMBER,
p_birthday DATE
);CREATE OR REPLACE TYPE person_table_type IS TABLE OF Person;
CREATE TABLE personTable OF person;
--创建过程
CREATE OR REPLACE PROCEDURE testInPersons(persons IN person_table_type) IS
BEGIN
FORALL i IN persons.first .. persons.count
INSERT INTO personTable VALUES(persons(i));
COMMIT;
END testInPersons;--java代码:
OracleCallableStatement cstmt = (OracleCallableStatement) con
.prepareCall("{call testInPersons(?)}");
StructDescriptor sd = new StructDescriptor("PERSON", con);
Object[] personAttrs1 = new Object[] { "This is code11", "侯廷文11",
new NUMBER(32), new Date(new java.util.Date().getTime()) };
STRUCT person1 = new STRUCT(sd, con, personAttrs1);
Object[] personAttrs2 = new Object[] { "This is code22", "侯廷文22",
new NUMBER(33), new Date(new java.util.Date().getTime()) };
STRUCT person2 = new STRUCT(sd, con, personAttrs2);
ArrayDescriptor ad = new ArrayDescriptor("person_table_type"
.toUpperCase(), con);
ARRAY persons = new ARRAY(ad, con, new STRUCT[] { person1, person2 });
cstmt.setARRAY(1, persons);
cstmt.execute();
}
- Oracle复合类型参数的存储过程以及JDBC调用
- Oracle复合类型参数的存储过程以及JDBC调用
- JDBC 调用 oracle 存数过程 返回复合类型数据
- jdbc中调用oracle 返回游标类型的存储过程
- jdbc中调用oracle 返回游标类型的存储过程
- Oracle 建立存储过程 以及jdbc调用
- Java调用Oracle集合类型输出参数的存储过程
- Java调用Oracle集合类型输出参数的存储过程
- 创建oracle的存储过程,以及通过JDBC调用该存储过程
- JDBC调用存储过程输出游标参数的存储过程
- JDBC调用存储过程,以及存储过程 事务的使用.....
- jdbc调用oracle 存储过程
- JDBC调用Oracle存储过程
- jdbc调用Oracle存储过程
- jdbc调用oracle存储过程
- 用JDBC调用输出参数为cursor的oracle存储过程
- hibernate里使用jdbc方式调用oracle带游标返回参数的简单分页存储过程
- spring jdbc调用oracle 存贮过程 返回游标类型的参数
- java jsp ssh 在线 管理 系统 毕业设计
- Unity手游之路<十一>资源打包Assetbundle
- Web前端.系统学习Web前端开发路线图
- Bash Shell let命令
- Linux Socket编程中的心跳机制
- Oracle复合类型参数的存储过程以及JDBC调用
- MySQL数据的导出和导入工具:mysqldump
- samba文件服务器配置
- glPixelStore
- cppTest-3.5:模板类
- vs2005调用动态链接库设置
- 素数判断 与筛法求素数
- 17.2、UINavigaionController(如何管理视图控制器1)-OC+UI
- ubuntu终端全屏