CREATE TABLE SFZ_TEST_MANAGER_XG(
yxgh VARCHAR2(100),
ygxm VARCHAR2(100),
position_name VARCHAR2(100)
);
CREATE TABLE SFZ_TEST_MANAGER_XG AS select T.CCUST_ID as yxgh,T.ACCS_NBR as ygxm,T.CCUST_NAME as position_name from HEMS_FILTER_CUST_LIST t where t.ccust_id is not null and t.accs_nbr is not null and t.ccust_name is not null;
DROP TABLE SFZ_TEST_MANAGER_XG;
select * from SFZ_TEST_MANAGER_XG;
select count(*) from SFZ_TEST_MANAGER_XG;
create or replace TYPE SFZ_MANAG_M_OBJ_TYPE AS OBJECT(
yxgh VARCHAR2(100),
ygxm VARCHAR2(100),
position_name VARCHAR2(100));
DROP TYPE SFZ_MANAG_M_OBJ_TYPE;
select * from user_objects t where t.object_name not like '%SFZ_MANAG_M_OBJ_TYPE%' and t.object_type = 'TYPE';
CREATE OR REPLACE TYPE SFZ_MANAG_M_TAB_TYPE IS TABLE OF SFZ_MANAG_M_OBJ_TYPE;
DROP TYPE SFZ_MANAG_M_TAB_TYPE;
create or replace procedure proc_sfz_proc_test(user_yxgh IN VARCHAR2,v_table out SFZ_MANAG_M_TAB_TYPE)
is
cursor cursor_cur_type is select * from SFZ_TEST_MANAGER_XG; --可以增加条件
i NUMBER(8) :=1;
BEGIN
v_table:=SFZ_MANAG_M_TAB_TYPE();
for cursor_record in cursor_cur_type loop
v_table.EXTEND;
v_table(i) := NEW SFZ_MANAG_M_OBJ_TYPE(cursor_record.yxgh,cursor_record.ygxm,cursor_record.position_name);
--v_table(i) := NEW SFZ_MANAG_M_OBJ_TYPE('123','124','125');
i:=i+1;
end loop;
end proc_sfz_proc_test;
DROP PROCEDURE proc_sfz_proc_test;
package com.zjhcsoft.test.utl;
import java.sql.Connection;
import java.sql.DriverManager;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.Datum;
import oracle.sql.STRUCT;
public class TestOracleProc {
/**
* @param args
*/
public static void main(String[] args) {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (Exception e) {
e.printStackTrace();
}
Connection conn = null;
String DBurl = "jdbc:oracle:thin:@134.224.40.73:1521:orcl";
try {
conn = DriverManager.getConnection(DBurl, "hems", "hems");
System.out.println("Getting Connection...");
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
try {
conn = DriverManager.getConnection(DBurl, "hems", "hems");
long start = System.currentTimeMillis();
OracleCallableStatement cs = (OracleCallableStatement)conn.prepareCall("{call proc_sfz_proc_test(?,?)}");
cs.setString(1, "fruitking");
cs.registerOutParameter(2, OracleTypes.ARRAY,"SFZ_MANAG_M_TAB_TYPE");
cs.execute();
long end = System.currentTimeMillis();
System.out.println("this procedure consumes "+((end-start)/1000)+" excute time.");
start = System.currentTimeMillis();
ARRAY array = (ARRAY)cs.getArray(2);
Datum[] datas = array.getOracleArray();
if(datas.length>0){
System.out.println("this procedure has "+datas.length+" data.");
for (int i = 0; i < datas.length; i++){
if(datas[i]!=null&&((STRUCT) datas[i])!=null){
Datum[] personAttributes = ((STRUCT) datas[i]).getOracleAttributes();
System.out.println("column"+(i+1)+":"+new String(personAttributes[0].getBytes())+", "+new String(personAttributes[1].getBytes())+", "+new String(personAttributes[2].getBytes()));
}else{
System.out.println("datas["+i+"] is null.");
}
}
}else{
System.out.println("this procedure is not result data...");
}
end = System.currentTimeMillis();
System.out.println("show this procedure data consumes "+((end-start)/1000)+" excute time.");
cs.close();
conn.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
分享到:
相关推荐
在有状态SessionBean中,用累加器,以对话状态存储起来,创建EJB对象,并将当前的计数器初始化,调用每一个EJB对象的count()方法,保证Bean正常被激活和钝化,EJB对象是用完毕,从内存中清除…… Java Socket 聊天...
在有状态SessionBean中,用累加器,以对话状态存储起来,创建EJB对象,并将当前的计数器初始化,调用每一个EJB对象的count()方法,保证Bean正常被激活和钝化,EJB对象是用完毕,从内存中清除…… Java Socket 聊天...
3. 关系结构模型:关系式数据结构把一些复杂的数据结构归结为简单的二元关系(即二维表格形式)。常见的有Oracle、mssql、mysql等 二、 主流数据库 数据库名 公司 特点 工作环境 mssql 微软 只能能运行在windows平台,...
实例043 将二维数组中的行列互换 53 实例044 利用数组随机抽取幸运观众 54 实例045 用数组设置JTable表格的列名与列宽 55 3.2 数组操作 57 实例046 数组的下标界限 57 实例047 按钮控件数组实现计数器界面 58 实例...
发布Oracle存储过程包c#代码生成工具(CodeRobot) New Folder XCodeFactory3.0完全攻略--序 XCodeFactory3.0完全攻略--基本思想 XCodeFactory3.0完全攻略--简单示例 XCodeFactory3.0完全攻略--IDBAccesser ...
IVD:超过20/20微观数据维数或变量等级文件 IVP:超过20/20的用户子集配置文件 IVT:超过20/20表或集合数据文件 IVX:超过20/20微数据目录文件 IW:Idlewild屏幕保护程序 IWC:Install Watch文档 J J62:...