//因为数据库中新增了一列basic_sort ,然后需要根据之前basic_datetime数据中的时间转为时间戳后存入新增的字段中,因为表中数据量有10万+,所以网上查了查写了个存储过程出来,测试通过,10万多条用时不到2分钟

 

我发现复制存储过程的时候有个坑,完整复制过去之后有的地方会报错,报错的位置照着手打一遍就可以了

1. 首先先创建一个存储过程

create procedure update_sort()

begin

end

 

2. 提示成功之后将下面的内容放入begin  和  end之中

DECLARE basic_id VARCHAR(32);

DECLARE basic_datetime VARCHAR(32);

# 设置变量s来进行while循环

DECLARE s int DEFAULT 0;

#设置consume游标,来接收查询的list的结果集

DECLARE consume  CURSOR FOR SELECT a.basic_id as basic_id,a.basic_datetime as basic_datetime from basic a ;

# 当程序发生异常时将s设置为1

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET s=1;

#打开consume游标进行程序的调用

 OPEN consume;

 #将consume的内容赋值给basic_id,basic_datetime;

    FETCH consume into basic_id,basic_datetime;

    while s <> 1 DO

              update basic b set b.basic_sort = UNIX_TIMESTAMP(basic_datetime) where b.basic_id = basic_id; 

    FETCH consume INTO basic_id,basic_datetime;

    end WHILE;

  CLOSE consume;

List<Object[]> result = new ArrayList<Object[]>();//接收值所用的列表

Connection conn = DAOFactory.getDAOFactory().getConnection();//根据项目情况打开连接（这里只是我项目的打开方式）
CallableStatement statement = conn.prepareCall("{call up_GetDept(?)}");//执行存储过程"?"为需传递的参数

statement.setString(1, productCode);//传递参数

ResultSet rs = statement.executeQuery();
//循环拿出返回的值
while (rs.next()) {
	Object[] object = new Object[2];
	object[0] = rs.getString("deptname");
	object[1] = rs.getString("deptcode");

	result.add(object);
}
	conn.commit();

在使用oracle进行批量插入的时，如果数据量较小可以使用insert all的语法进行批量插入。如果数据量较大，再使用insert all的语句插入就会发生错误，因为这个语法会受到限制，oracle不允许一次性插入的列数乘以行数>1000，这时就可以考虑使用存储过程批量插入了。 

        使用存储过程批量插入很显然要接收一个数组当参数，而且这个数组里的元素类型应该和java里自定义的数据模型对应。我项目里数据层使用的是MyBatis，就整理整理MyBatis的实现过程。当然不管是MyBatis还是Hibernatge、JDBC都是一个道理，关键的一步都是将java数据模型转换成数据库数据模型。 

        以user表为例，user表字段:id、username、age、phone、email，对user表进行批量插入。思考一下，java里有个user类，批量插入，那么传入数据类型应该是List,但是数据库可不认识这个数据模型，所以为了让数据库认识，需要将java模型转换成数据库模型。

创建数据库类型和java数据模型对应
create or replace type user_type as object(
       age number,
       username nvarchar2(20),
       phone nvarchar2(20),
       email nvarchar2(50)
)

注意这里字符串类型一定要定义为nvarchar，否则字符串类型在转换时会乱码无法插入数据库

2.数据模型有了，还得有个能存放此类型的数组



create or replace type user_type_arr as table of user_type

3.接着可以创建存储过程了



create or replace procedure user_insert_pro(userList in user_type_arr) as
begin

  for i in 1 .. userList.count loop 
    insert into users(
                       id,
                       username,
                       age,
                       phone,
                       email
                      )
                      values
                      (
                       user_seq.nextval,
                       userList(i).username,
                       userList(i).age,
                       userList(i).phone,
                       userList(i).email
                      );
  end loop;
end user_insert_pro;

 数据库的准备工作就完成了，下面是java代码的工作。我使用的是MyBatis，要在Mapper里配置一个parameterMap




<parameterMap type="map" id="user_map">
        <parameter property="userList" javaType="java.util.ArrayList" mode="IN" jdbcType="ARRAY"
        typeHandler="com.jy.typeHandler.UserListHandler"/>
    </parameterMap>

然后写mybatis里写存储过程调用时的参数类型就定义为这个parameterMap ，这样的意义就是配置的typeHandler会将java里传入的ArrayList转换成对应的数据库类型。UserListHandler代码如下

package com.jy.typeHandler;

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 org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import com.jy.model.User;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

@SuppressWarnings("rawtypes")
public class UserListHandler extends BaseTypeHandler
{

    @Override
    public Object getNullableResult(ResultSet arg0, String arg1) throws SQLException
    {
        return null;
    }

    @Override
    public Object getNullableResult(ResultSet arg0, int arg1) throws SQLException
    {
        return null;
    }

    @Override
    public Object getNullableResult(CallableStatement arg0, int arg1) throws SQLException
    {
        return null;
    }

    @SuppressWarnings("unchecked")
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Object obj, JdbcType arg3)
            throws SQLException
    {
        Connection conn = null;
        try
        {
            if (null != obj)
            {
                List<User> list = (ArrayList<User>) obj;
                conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "root", "root");
                // 数据库的type名必须大写
                ARRAY array = getArray(conn, "USER_TYPE", "USER_TYPE_ARR", list);
                preparedStatement.setArray(i, array);
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        } finally
        {
            if (null != conn)
            {
                conn.close();
            }
        }
    }

    private ARRAY getArray(Connection con, String OracleObj, String Oraclelist, List<User> data) throws Exception
    {
        ARRAY array = null;
        ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist, con);
        STRUCT[] structs = new STRUCT[data.size()];
        StructDescriptor structdesc = new StructDescriptor(OracleObj, con);
        for (int i = 0; i < data.size(); i++)
        {
            Object[] result = { 
                                data.get(i).getAge(), 
                                data.get(i).getUsername(), 
                                data.get(i).getPhone(),
                                data.get(i).getEmail() 
                            };
            structs[i] = new STRUCT(structdesc, con, result);
        }
        array = new ARRAY(desc, con, structs);
        return array;
    }

}


再再Mapper里定义存储过程调用的语句



<insert id="userInsert" useGeneratedKeys="false" parameterMap="user_map" statementType="CALLABLE">
        call user_insert_pro(?) 
    </insert>

最后可以在java代码里用MyBatis调用存储过程了，MyBatis会基于刚才的配置，将java模型转换成数据库模型。 

调用如下：



@org.junit.Test
    public void test()
    {
        SqlSession session=sqlSessionFactory.openSession();
        List<User> list=new ArrayList<User>();
        for(int i=0;i<1000;i++)
        {
            list.add(new User("jy", 25, "oracle.com", "110"));
        }
        HashMap<String, Object> params=new HashMap<String,Object>();
        params.put("userList", list);
        session.insert("com.jy.dao.UserDao.userInsert", params);
        session.close();
    }

全剧终！

不管什么框架，主要找到java模型和数据库模型转换的方式就行了

oracle存储过程接收自定义数组类型参数

2016年10月29日 09:53:23 ntuloser 阅读数：1092 标签： oraclejava存储mybatis 更多

个人分类： 数据库

版权声明：本文为博主原创文章，未经博主允许不得转载。 https://blog.csdn.net/ntuloser/article/details/52962988

在使用oracle进行批量插入的时，如果数据量较小可以使用insert all的语法进行批量插入。如果数据量较大，再使用insert all的语句插入就会发生错误，因为这个语法会受到限制，oracle不允许一次性插入的列数乘以行数>1000，这时就可以考虑使用存储过程批量插入了。 

使用存储过程批量插入很显然要接收一个数组当参数，而且这个数组里的元素类型应该和java里自定义的数据模型对应。我项目里数据层使用的是MyBatis，就整理整理MyBatis的实现过程。当然不管是MyBatis还是Hibernatge、JDBC都是一个道理，关键的一步都是将java数据模型转换成数据库数据模型。 

以user表为例，user表字段:id、username、age、phone、email，对user表进行批量插入。思考一下，java里有个user类，批量插入，那么传入数据类型应该是List,但是数据库可不认识这个数据模型，所以为了让数据库认识，需要将java模型转换成数据库模型。

创建数据库类型和java数据模型对应
create or replace type user_type as object(
       age number,
       username nvarchar2(20),
       phone nvarchar2(20),
       email nvarchar2(50)
)

注意这里字符串类型一定要定义为nvarchar，否则字符串类型在转换时会乱码无法插入数据库

2.数据模型有了，还得有个能存放此类型的数组

create or replace type user_type_arr as table of user_type

1
3.接着可以创建存储过程了

create or replace procedure user_insert_pro(userList in user_type_arr) as
begin

  for i in 1 .. userList.count loop 
    insert into users(
                       id,
                       username,
                       age,
                       phone,
                       email
                      )
                      values
                      (
                       user_seq.nextval,
                       userList(i).username,
                       userList(i).age,
                       userList(i).phone,
                       userList(i).email
                      );
  end loop;
end user_insert_pro;

 数据库的准备工作就完成了，下面是java代码的工作。我使用的是MyBatis，要在Mapper里配置一个parameterMap


1
<parameterMap type="map" id="user_map">
        <parameter property="userList" javaType="java.util.ArrayList" mode="IN" jdbcType="ARRAY"
        typeHandler="com.jy.typeHandler.UserListHandler"/>
    </parameterMap>

然后写mybatis里写存储过程调用时的参数类型就定义为这个parameterMap ，这样的意义就是配置的typeHandler会将java里传入的ArrayList转换成对应的数据库类型。UserListHandler代码如下

package com.jy.typeHandler;

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 org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import com.jy.model.User;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

@SuppressWarnings("rawtypes")
public class UserListHandler extends BaseTypeHandler
{

    @Override
    public Object getNullableResult(ResultSet arg0, String arg1) throws SQLException
    {
        return null;
    }

    @Override
    public Object getNullableResult(ResultSet arg0, int arg1) throws SQLException
    {
        return null;
    }

    @Override
    public Object getNullableResult(CallableStatement arg0, int arg1) throws SQLException
    {
        return null;
    }

    @SuppressWarnings("unchecked")
    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, Object obj, JdbcType arg3)
            throws SQLException
    {
        Connection conn = null;
        try
        {
            if (null != obj)
            {
                List<User> list = (ArrayList<User>) obj;
                conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe", "root", "root");
                // 数据库的type名必须大写
                ARRAY array = getArray(conn, "USER_TYPE", "USER_TYPE_ARR", list);
                preparedStatement.setArray(i, array);
            }
        } catch (Exception e)
        {
            e.printStackTrace();
        } finally
        {
            if (null != conn)
            {
                conn.close();
            }
        }
    }

    private ARRAY getArray(Connection con, String OracleObj, String Oraclelist, List<User> data) throws Exception
    {
        ARRAY array = null;
        ArrayDescriptor desc = ArrayDescriptor.createDescriptor(Oraclelist, con);
        STRUCT[] structs = new STRUCT[data.size()];
        StructDescriptor structdesc = new StructDescriptor(OracleObj, con);
        for (int i = 0; i < data.size(); i++)
        {
            Object[] result = { 
                                data.get(i).getAge(), 
                                data.get(i).getUsername(), 
                                data.get(i).getPhone(),
                                data.get(i).getEmail() 
                            };
            structs[i] = new STRUCT(structdesc, con, result);
        }
        array = new ARRAY(desc, con, structs);
        return array;
    }

}


再再Mapper里定义存储过程调用的语句

<insert id="userInsert" useGeneratedKeys="false" parameterMap="user_map" statementType="CALLABLE">
        call user_insert_pro(?) 
    </insert>

最后可以在java代码里用MyBatis调用存储过程了，MyBatis会基于刚才的配置，将java模型转换成数据库模型。 

调用如下：

@org.junit.Test
    public void test()
    {
        SqlSession session=sqlSessionFactory.openSession();
        List<User> list=new ArrayList<User>();
        for(int i=0;i<1000;i++)
        {
            list.add(new User("jy", 25, "oracle.com", "110"));
        }
        HashMap<String, Object> params=new HashMap<String,Object>();
        params.put("userList", list);
        session.insert("com.jy.dao.UserDao.userInsert", params);
        session.close();
    }

全剧终！

不管什么框架，主要找到java模型和数据库模型转换的方式就行了

https://blog.csdn.net/ntuloser/article/details/52962988