文档章节

一个使用MyBatis调用Oracle数据库存储过程的例子

北风其凉
 北风其凉
发布于 2016/03/26 00:57
字数 1925
阅读 248
收藏 8

我的电脑操作系统版本为Win7旗舰版(ServicePack1),Oracle版本为Oracle11g

程序使用的jar包有:mybatis-3.2.2.jar、ojdbc14-10.2.0.2.0.jar

本例中使用的配置文件mybatis-config.xml,可以参见我的另一篇Blog《一个简单的MyBatis连接Oracle数据库的例子》(http://my.oschina.net/Tsybius2014/blog/626206


先说下本文中描述的场景。

现有一些产品,每个产品都有一些相关联的岗位,这些岗位可能由不同的人担任。人员信息表(person_info)表中记录了每个人的代码(person_code)和名称(person_name),人员任职表(time_on_duty)记录了每个产品的产品代码(item_code)、岗位类型(duty_type)、相关人(person_code&person_name)、在岗情况(is_on_duty),对于已离职的人,还需要记录离职的日期和时间。产品相关人员的变动、增减是从另一个系统推送来的,每次推送的数据,只有产品代码(item_code)、岗位1任职人、岗位2任职人、岗位3任职人……等等。

建表语句(init_data.sql)如下所示,time_on_duty的主键(id)使用序列(seq_time_on_duty)来赋值:

-- 人员信息表
create TABLE PERSON_INFO
(
    id number(12,0) PRIMARY KEY,
    person_code varchar2(20),
    person_name varchar2(50)
);

-- 插入人员信息基础数据
insert into person_info (id, person_code, person_name) values (1, '10001', 'Tsybius');
insert into person_info (id, person_code, person_name) values (2, '10002', 'Galatea');
insert into person_info (id, person_code, person_name) values (3, '10003', 'Gaius');
insert into person_info (id, person_code, person_name) values (4, '10004', 'Quintus');
insert into person_info (id, person_code, person_name) values (5, '10005', 'Atia');
commit;

-- 人员任职表
create TABLE TIME_ON_DUTY
(
    id number(12,0) PRIMARY KEY,
    item_code varchar2(20),
    person_code varchar2(20),
    person_name varchar2(50),
    duty_type varchar2(20),
    is_on_duty varchar2(5),
    exit_date number(10,0) DEFAULT 0,
    exit_time number(10,0) DEFAULT 0,
    input_date number(10,0) DEFAULT to_number(to_char(sysdate,'yyyymmdd')),
    input_time number(10,0) DEFAULT to_number(to_char(sysdate,'hh24miss')),
    update_date number(10,0) DEFAULT to_number(to_char(sysdate,'yyyymmdd')),
    update_time number(10,0) DEFAULT to_number(to_char(sysdate,'hh24miss'))
);

-- 创建人员信息表序列
CREATE SEQUENCE SEQ_TIME_ON_DUTY
INCREMENT BY 1
START WITH 1
MAXVALUE 999999999999999999999999999
CYCLE 
CACHE 20;

建立存储过程,对于每一个岗位的轮替,伪代码如下:

if (对应岗位人员 != null)
{
    bool 是否已更新 = false
    for (人员 : 当前产品同一岗位中所有的历史任职人员)
    {
        if (老数据当前已离职 && 老数据的人 == 新数据的人)
        {
            老人员重新上岗
            是否已更新 = true
        } 
        else if (老数据当前有效&&老数据的人 != 新数据的人)
        {
            老人下岗,更新离职时间
        }
        else if (老数据当前有效&&老数据的人 == 新数据的人)
        {
            更新下更新日期
            是否已更新 = true
        }
    }
    if (!是否已更新)
    {
        新人上岗
        是否已更新 = true
    }
}
else 
{
    当前产品对应岗位所有的历史任职人员,如还在职,全部标为离职,同时更新离职时间
}

写出的Oracle存储过程(refresh_new_data.sql)代码如下:

  -- 建立存储过程 - 更新项目相关人
  CREATE OR REPLACE PROCEDURE refresh_new_data(p_item_code     IN VARCHAR2,
                                               p_duty_1        IN VARCHAR2,
                                               --p_duty_2        IN VARCHAR2,
                                               --p_duty_3        IN VARCHAR2,  -- 如有更多相关人在此继续加入
                                               p_error_no      OUT NUMBER,   -- 错误号
                                               p_error_info    OUT VARCHAR2, -- 错误提示
                                               p_error_id      OUT NUMBER,   -- 错误序号
                                               p_error_sysinfo OUT VARCHAR2  -- 系统错误信息
                                               ) AS
  c_duty_type_1 CONSTANT VARCHAR2(2) := '1';
  --c_duty_type_2 CONSTANT VARCHAR2(2) := '2';
  --c_duty_type_3 CONSTANT VARCHAR2(2) := '3'; -- 如有更多相关人在此继续加入
  c_exit    CONSTANT VARCHAR2(3) := '0';
  c_on_duty CONSTANT VARCHAR2(3) := '1';
  v_duty_1_is_refresh VARCHAR2(5);
  --v_duty_2_is_refresh VARCHAR2(5);
  --v_duty_3_is_refresh VARCHAR2(5); -- 如有更多相关人在此继续加入
  v_id_tmp          time_on_duty.id % TYPE;
  v_item_code_tmp   time_on_duty.item_code % TYPE;
  v_person_code_tmp time_on_duty.person_code % TYPE;
  v_duty_type_tmp   time_on_duty.duty_type % TYPE;
  v_is_on_duty_tmp  time_on_duty.is_on_duty % TYPE;
  CURSOR c_duty_1 IS
    SELECT id, item_code, person_code, duty_type, is_on_duty
    FROM   time_on_duty
    WHERE  item_code = p_item_code AND
           duty_type = c_duty_type_1;
  --CURSOR c_duty_2 IS
  --  SELECT id, item_code, person_code, duty_type, is_on_duty
  --  FROM   time_on_duty
  --  WHERE  item_code = p_item_code AND
  --         duty_type = c_duty_type_2;
  --CURSOR c_duty_3 IS
  --  SELECT id, item_code, person_code, duty_type, is_on_duty
  --  FROM   time_on_duty
  --  WHERE  item_code = p_item_code AND
  --         duty_type = c_duty_type_3; -- 如有更多相关人在此继续加入
  -- 当前日期&当前时间
  v_curr_date NUMBER(10, 0) := TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmdd'));
  v_curr_time NUMBER(10, 0) := TO_NUMBER(TO_CHAR(SYSDATE, 'hh24miss'));
BEGIN
  dbms_output.put_line('----------- PROCUDURE START -----------');
  -- ITEM CODE 不能为空
  IF p_item_code IS NULL
  THEN
    BEGIN
      dbms_output.put_line('ITEM CODE IS NULL');
      p_error_no      := 101;
      p_error_info    := 'ITEM CODE IS NULL';
      p_error_id      := SQLCODE;
      p_error_sysinfo := 'CUSTOM DEFECT TYPE';
      RETURN;
    END;
  END IF;
  -- 相关人更新 START --
  --
  -- 更新 duty_1
  --
  IF p_duty_1 IS NOT NULL
  THEN
    BEGIN
      -- 如果传入的新人不为空,需要做进一步判断
      dbms_output.put_line('CHECK DUTY_1');
      v_duty_1_is_refresh := 'no';
      -- 使用游标遍历相同产品相同职务的人
      OPEN c_duty_1;
      LOOP
        FETCH c_duty_1
          INTO v_id_tmp, v_item_code_tmp, v_person_code_tmp, v_duty_type_tmp, v_is_on_duty_tmp;
        EXIT WHEN c_duty_1 % NOTFOUND;
        dbms_output.put_line('==ITEM FOUND==');
        dbms_output.put_line('id          : ' || v_id_tmp);
        dbms_output.put_line('item_code   : ' || v_item_code_tmp);
        dbms_output.put_line('person_code : ' || v_person_code_tmp);
        dbms_output.put_line('duty_type   : ' || v_duty_type_tmp);
        dbms_output.put_line('is_on_duty  : ' || v_is_on_duty_tmp);
        IF v_is_on_duty_tmp = c_exit AND
           p_duty_1 = v_person_code_tmp
        THEN
          -- 老人当前失效且老人与新人是同一人的情况:重新启用老人
          UPDATE time_on_duty
          SET    is_on_duty = c_duty_type_1, update_date = v_curr_date, update_time = v_curr_time
          WHERE  id = v_id_tmp;
          v_duty_1_is_refresh := 'yes';
        ELSIF v_is_on_duty_tmp = c_on_duty AND
              p_duty_1 <> v_person_code_tmp
        THEN
          -- 老人当前有效且老人与新人不是同一人的情况:老人离职
          UPDATE time_on_duty
          SET    is_on_duty  = c_exit,
                 exit_date   = v_curr_date,
                 exit_time   = v_curr_time,
                 update_date = v_curr_date,
                 update_time = v_curr_time
          WHERE  id = v_id_tmp;
        ELSIF v_is_on_duty_tmp = c_exit AND
              p_duty_1 = v_person_code_tmp
        THEN
          -- 老人当前有效且老人与新人是同一人的情况:更新下更新时间字段即可
          UPDATE time_on_duty
          SET    update_date = v_curr_date, update_time = v_curr_time
          WHERE  id = v_id_tmp;
          v_duty_1_is_refresh := 'yes';
        END IF;
      END LOOP;
      CLOSE c_duty_1;
      -- 游标使用结束,关闭游标
      -- 如新人以前未担任过本产品的对应职务则插入一条新记录
      IF v_duty_1_is_refresh = 'no'
      THEN
        INSERT INTO time_on_duty
          (id,
           item_code,
           person_code,
           person_name,
           duty_type,
           is_on_duty,
           exit_date,
           exit_time,
           input_date,
           input_time,
           update_date,
           update_time)
          SELECT seq_time_on_duty.NEXTVAL,
                 a.item_code,
                 a.person_code,
                 b.person_name,
                 a.duty_type,
                 a.is_on_duty,
                 a.exit_date,
                 a.exit_time,
                 a.input_date,
                 a.input_time,
                 a.update_date,
                 a.update_time
          FROM   (SELECT p_item_code   AS item_code,
                         p_duty_1      AS person_code,
                         c_duty_type_1 AS duty_type,
                         c_on_duty     AS is_on_duty,
                         0             AS exit_date,
                         0             AS exit_time,
                         v_curr_date   AS input_date,
                         v_curr_time   AS input_time,
                         v_curr_date   AS update_date,
                         v_curr_time   AS update_time
                  FROM   dual) a
          LEFT   JOIN (SELECT person_code, person_name
                       FROM   person_info) b ON a.person_code = b.person_code;
        v_duty_1_is_refresh := 'yes';
      END IF;
    END;
  ELSE
    -- 如果传入的新人为空,则认为老人离职,无人补缺
    UPDATE time_on_duty
    SET    is_on_duty  = c_exit,
           exit_date   = v_curr_date,
           exit_time   = v_curr_time,
           update_date = v_curr_date,
           update_time = v_curr_time
    WHERE  is_on_duty = c_on_duty AND
           item_code = p_item_code AND
           duty_type = c_duty_type_1;
  END IF;
  -- 如有更多相关人在此继续加入
  -- 相关人更新 FINISH --
  dbms_output.put_line('----------- PROCUDURE END -----------');
  p_error_no      := 0;
  p_error_info    := 'EXECUTE SUCCESS';
  p_error_id      := SQLCODE;
  p_error_sysinfo := SQLERRM;
EXCEPTION
  WHEN OTHERS THEN
    p_error_no      := 999;
    p_error_info    := '存储过程执行错误';
    p_error_id      := SQLCODE;
    p_error_sysinfo := SQLERRM;
END refresh_new_data;
/

在PL/SQL中使用命令窗口建立存储过程时,如果存储过程写得有问题,就会报错:

这种情况下,可以通过下面的方式查看存在哪里:

1、找到PL/SQL中的“浏览器”窗口,在存储过程中找到我们新建立的存储过程(上面标记了红色的×,说明脚本执行出错了),在右键菜单中找到“查看”

2、在“查看”界面中,出错的行会用黄色标记,下面有详细的报错信息

准备工作完成后,建立文件MyBatisTestMapper.java:

import java.util.Map;

public interface MyBatisTestMapper {
    void refreshNewData(Map<String, Object> paramMap);
}

建立对应的Mapper文件MyBatisTestMapper.xml,调用存储过程:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="MyBatisTestMapper">

    <parameterMap id="paramMap4NewData" type="java.util.Map">
       <parameter property="itemCode" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>
       <parameter property="duty1" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/>
       <!-- 如有更多相关人在此继续加入 -->
       <!-- <parameter property="duty2" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/> -->
       <!-- <parameter property="duty3" javaType="java.lang.String" jdbcType="VARCHAR" mode="IN"/> -->
       <parameter property="errorNo" javaType="java.lang.Long" jdbcType="NUMERIC" mode="OUT"/>
       <parameter property="errorInfo" javaType="java.lang.String" jdbcType="VARCHAR" mode="OUT"/>
       <parameter property="errorId" javaType="java.lang.Long" jdbcType="NUMERIC" mode="OUT"/>
       <parameter property="errorSysInfo" javaType="java.lang.String" jdbcType="VARCHAR" mode="OUT"/>
    </parameterMap>

    <select id="refreshNewData" statementType="CALLABLE" parameterMap="paramMap4NewData">
       { call refresh_new_data(?, ?, ?, ?, ?, ?) }
    </select>

</mapper>

main函数的实现如下:

import java.io.InputStream;
import java.text.MessageFormat;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

/**
 * MyBatis使用测试
 * @author Tsybius2014
 * @date 2016年3月26日
 * @time 上午12:29:10
 * @remark
 *
 */
public class MyBatisTest {
    
    public static void main(String[] args) {
        try {
            String resource = "mybatis-config.xml";
            InputStream inputStream = Resources.getResourceAsStream(resource);
            SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
            SqlSession session = sqlSessionFactory.openSession();
            try {
                MyBatisTestMapper mapper = session.getMapper(MyBatisTestMapper.class);
                Map<String, Object> paramMap = new HashMap<String, Object>();
                paramMap.put("itemCode", "item00X");
                paramMap.put("duty1", "10001");
                paramMap.put("duty2", "10001");
                paramMap.put("duty3", "10002");
                mapper.refreshNewData(paramMap);
                
                long errorNo = (Long) paramMap.get("errorNo");
                String errorInfo = (String) paramMap.get("errorInfo");
                long errorId = (Long) paramMap.get("errorId");
                String errorSysInfo = (String) paramMap.get("errorSysInfo");
                System.out.println(errorNo);
                System.out.println(errorInfo);
                System.out.println(errorId);
                System.out.println(errorSysInfo);
                
            } finally {
                session.commit();
                session.close();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}

输出结果如下,存储过程执行成功:

0
EXECUTE SUCCESS
0
ORA-0000: normal, successful completion

END

© 著作权归作者所有

北风其凉

北风其凉

粉丝 119
博文 498
码字总数 463468
作品 4
朝阳
程序员
私信 提问
Oracle + Mybatis实现批量插入、更新和删除示例代码

前言 Mybatis是web工程开发中非常常用的数据持久化的框架,通过该框架,我们非常容易的进行数据库的增删改查。数据库连接进行事务提交的时候,需要耗费的资源比较多,如果需要插入更新的数据...

一看就喷亏的小猿
2018/11/22
0
0
mysql插入数据后返回自增ID的方法,last_insert_id(),selectkey

mysql插入数据后返回自增ID的方法 mysql和oracle插入的时候有一个很大的区别是,oracle支持序列做id,mysql本身有一个列可以做自增长字段,mysql在插入一条数据后,如何能获得到这个自增id的...

飞翼
2016/12/28
27
0
Mybatis要如何捕捉存储过程执行异常的信息?

最近写的程序中发现存在如下的问题,当我用mybatis调用存储过程(数据库使用的是ORACLE),发现当过程执行存在异常,但是我事务任然提交了,目前的处理方法是根据过程中定义的返回标志来判断的,...

HansonReal
2017/02/27
461
4
MyBatis练习:查询表中单个数据列中用分隔符隔开的数据

我的电脑操作系统版本为Win7旗舰版(ServicePack1),Oracle版本为Oracle11g,PL/SQL版本为7.0.1.1066 程序使用的jar包有:mybatis-3.2.2.jar、ojdbc14-10.2.0.2.0.jar 本例中使用的配置文件...

北风其凉
2016/05/21
341
0
MyBatis 示例之存储过程

存储过程在数据库中比较常见,虽然大多数存储过程比较复杂,但是使用 MyBatis 调用时,用法都一样,因此我们这一节使用一个简单的存储过程来了解 MyBatis 中存储过程的使用方法。 基本准备 ...

JAVA群450936584
2017/12/17
0
0

没有更多内容

加载失败,请刷新页面

加载更多

Tedis:基于 TiKV 构建的 NoSQL 数据库

作者介绍: 陈东明,饿了么北京技术中心架构组负责人,负责饿了么的产品线架构设计以及饿了么基础架构研发工作。曾任百度架构师,负责百度即时通讯产品的架构设计。具有丰富的大规模系统构 ...

TiDB
17分钟前
0
0
linux命令

ls命令是linux下最常用的命令。ls命令就是list的缩写,缺省下ls用来打印出当前目录的清单。如果ls指定其他目录,那么就会显示指定目录里的文件及文件夹清单。 通过ls 命令不仅可以查看linux文件...

WinkJie
24分钟前
0
0
你需要的物流运输类报表,这里都有

你需要的物流运输类报表,都在这里 葡萄城报表模板库是一款免费的报表制作、学习和参考工具,包含了超过 200 张高质量报表模板,涵盖了 16 大行业和 50 多种报表类型,为 30 余万报表开发者提...

葡萄城技术团队
31分钟前
1
0
像Java SE一样编写Java EE(ddd探索)

今天主要改写昨天的组合模式成Web系统。 容器接口为 public interface TreeProduct { /** * 展示所有产品 * @return */ List<TreeProduct> allProducts();...

算法之名
32分钟前
0
0
Django Model 模型建立

Django Model 模型 Django Model层是Django的数据模型层,每一个Model类就是数据库中的一张表; 我们需要注意下面几点: model一般都是定义在不同的APP的models.py模块文件中,可以是一个,也...

彩色泡泡糖
40分钟前
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部