⼀个使⽤MyBatis调⽤Oracle数据库存储过程的例⼦
我的电脑操作系统版本为Win7旗舰版（ServicePack1），Oracle版本为Oracle11g
程序使⽤的jar包有：mybatis-3.2.2.jar、ojdbc14-10.2.0.2.0.jar
先说下本⽂中描述的场景。
现有⼀些产品，每个产品都有⼀些相关联的岗位，这些岗位可能由不同的⼈担任。⼈员信息表（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
-- 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;
oracle游标的使用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,
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.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,
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 {
发表评论