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

最新推荐文章于 2021-10-25 21:17:14 发布

chenzai1946

最新推荐文章于 2021-10-25 21:17:14 发布

阅读量315

点赞数

文章标签：数据库 java 操作系统

原文链接：https://my.oschina.net/Tsybius2014/blog/647594

版权

我的电脑操作系统版本为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

转载于:https://my.oschina.net/Tsybius2014/blog/647594

chenzai1946

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
一个使用MyBatis调用Oracle数据库存储过程的例子

我的电脑操作系统版本为Win7旗舰版（ServicePack1），Oracle版本为Oracle11g 程序使用的jar包有：mybatis-3.2.2.jar、ojdbc14-10.2.0.2.0.jar 本例中使用的配置文件mybatis-config.xml，可以参见我的另一篇Blo...
复制链接

扫一扫