Springboot Mybatis调用Oracle的存储过程实例及经验

Start

在网上找了许多，大部分没有试成功，好不容易给整成功了，记录一下。

1 存储过程

需要注意的是存储过程中，不可以使用truncate之类的DDL语句，也不要在存储过程做 commit 之类的操作 。

• in 代表的入参
• out 代表返回的参数
  这两个参数，与Mybatis传参保持一致即可。

存储过程如下：

create or replace procedure jphone_list_generate(
       v_result OUT VARCHAR2,
       v_createid IN VARCHAR2,
       v_department_group_code IN VARCHAR2, 
       v_year IN VARCHAR2, 
       v_month IN VARCHAR2, 
       v_staffcode IN VARCHAR2)
 IS 
  v_count NUMBER;
BEGIN
  
DELETE FROM jphone_data_center_tmp;
INSERT INTO jphone_data_center_tmp nologging
 SELECT a.year,a.month,a.department_group_code, a.channel, COUNT(*) call_count
FROM newcpic.jphone_data_center a
GROUP BY a.year,a.month, a.department_group_code, a.channel;

MERGE INTO newcpic.jphone_data_center a
   USING ( SELECT * FROM jphone_import_call a
             WHERE a.department_group_code = v_department_group_code 
              AND v_month >= to_char(a.vehicle_license + 15,'mm') 
              AND a.data_falg = '1' ) b
ON (a.vehicle_vin = b.vehicle_vin )
WHEN NOT MATCHED THEN
  INSERT (
     a.import_uuid,
        a.owner_name,
        a.idcard,
        a.mobilephone,
        a.address,
        a.vehicle_type,
        a.vehicle_model,
        a.vehicle_weight,
        a.vehicle_seat,
        a.vehicle_vin,
        a.vehicle_engine,
        a.vehicle_purchanse_date,
        a.vehicle_license,
        a.inception_date,
        a.planned_end_date,
        a.department_group_code,
        a.channel,
        a.staff_code,
        a.team_code,
        a.label_id,
        a.data_falg,
        a.if_recovery,
        a.year