一、Oracle部分
1、在Oracle新建自定义类型,对应要传入oracle的字段,类型不一定一致,因为时间类型不好处理,为了处理方便将date/number类型均对应为varchar2类型
CREATE OR REPLACE TYPE alarm_content_type AS OBJECT
(
con_addr varchar2 (10) ,
acq_no varchar2 (5) ,
mea_no varchar2 (5) ,
alarm_day varchar2 (20) ,
alarm_value varchar2 (5) ,
data_type varchar2 (5) ,
alarm_data varchar2 (15)
)
其中acq_no对应为number,alarm_day对应为date类型
2、在Oracle新建对应的自定义类型数组
CREATE OR REPLACE TYPE alarm_content_array AS TABLE OF alarm_content_type
3、在Oracle建立package及 package bodies
CREATE OR REPLACE PACKAGE alarm_content_pck AS
PROCEDURE insert_object(d alarm_content_array, tbname varchar2);
END alarm_content_pck;
CREATE OR REPLACE PACKAGE BODY alarm_content_pck AS
PROCEDURE insert_object (d alarm_content_array, tbname varchar2) AS
SqlText varchar2 (500);
BEGIN
FOR i IN d.FIRST..d.LAST
LOOP
SqlText := 'INSERT INTO ' || tbname
|| ' VALUES(''' || d(i).con_addr
|| ''', ' || d(i).acq_no || ', ' || d(i).mea_no
|| ', decode(' || d(i).alarm_day || ', null , null, to_date(''' ||d(i).alarm_day || ''', ''yyyy-mm-dd''' || '))'
|| ', ''' || d(i).alarm_value || ''', ''' || d(i).data_type
|| ''', ' || d(i).alarm_data
|| ')';
EXECUTE IMMEDIATE SqlText;
END LOOP;
END insert_object;
END alarm_content_pck;
其中参数d 为传入的数据, tbname 为插入Oracle的表名
二、Java部分
1、Oracle的链接、关闭等
- package com.techen.database;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- import java.util.ArrayList;
- import java.util.List;
- import oracle.jdbc.OracleCallableStatement;
- import oracle.sql.ARRAY;
- import oracle.sql.ArrayDescriptor;
- import oracle.sql.STRUCT;
- import oracle.sql.StructDescriptor;
- import com.techen.database.DataDb;
- import com.techen.database.ProcedureParam;
- /**
- * Oracle接口类
- *
- */
- public class OracleDb
- {
- public static Connection connection = null; // 连接到数据库的对象
- private static Statement state = null; // Statement对象
- private static String URL = null;
- private static String userName;
- private static String passWord;
- /**
- * 建立到内存数据库的连接
- *
- * @return 成功与否
- */
- public OracleDb(String aURL, String aUserName, String aPassWord)
- {
- URL = aURL;
- userName = aUserName;
- passWord = aPassWord;
- }
- /**
- * 建立与Oracle数据库的连接
- *
- * @return 成功与否
- */
- public boolean createConnection()
- {
- boolean bFlag = false;
- if (connection != null)
- closeConnection(); <