Oracle存储过程迁移至mysql

一、创建存储过程源码

oracle源码

CREATE OR REPLACE PROCEDURE P_TRANSMITDATA
(
  nor_table in VARCHAR2,  --正式表
  delsql in VARCHAR2,
  Flag in  VARCHAR2,  --1，增量  2. 全量 3、明细
  TORQ in  VARCHAR2
)
as
sqlStr  VARCHAR2(500);
delsql1  VARCHAR2(500);
colname VARCHAR2(40);
tmp_table VARCHAR2(50);
begin
    tmp_table := 'TMP_'|| nor_table;
​
    if  nor_table ='B_TRANSLIST_DEF'
    then
        colname := 'transmitdate';
    elsif nor_table = 'BDM_CD_U_AP_EI_PERSON'
    then
        colname := 'CUST_ID';
    elsif nor_table = 'BDM_CD_U_AP_MI_CUST_CLS'
    then
        colname := 'CUST_COD';
    end if;
​
    -- 增量情况
    if  Flag = '1'
    then
        ---处理需要更新的数据
        --删除历史数据
        delsql1 :='delete from ' || nor_table || ' a where exists(select 1 from '||tmp_table||' b where a.'||colname||'=b.'||colname||') '; 
        sqlStr :='insert into ' || nor_table||' select a.*,'''||TORQ||''' from '||tmp_table || ' a';
        --insert into debug1 values('delsql1='||delsql1,sysdate);
        commit;
    
        Execute Immediate delsql1;
        
    elsif  Flag ='2'
    then
​
        sqlStr := 'truncate table '|| nor_table;
        --execute( sqlStr)
        Execute Immediate sqlStr;
        --sqlStr := 'insert into '|| nor_table||' select * from '|| tmp_table;
        sqlStr := 'insert /*+ append nologging */ into '|| nor_table||' select /*+ parallel(b, 4) */ * from '|| tmp_table;
    elsif  Flag ='3'
    then
        sqlStr:='delete from '|| nor_table|| ' where DATE_POST='|| TORQ;
        --execute ( sqlStr)
        Execute Immediate sqlStr;
    
        sqlStr :='insert inot '|| nor_table||' select *,'|| TORQ||' from '|| tmp_table;
    elsif Flag = '4'--全量数据且只有月末下发
    then
        delsql1 :='delete from ' || nor_table || ' a where exists(select 1 from '||tmp_table||' b ) ';
        --insert into debug1 values('delsql1='||delsql1,sysdate);
        commit;
        Execute Immediate delsql1;
        sqlStr := 'insert /*+ append nologging */ into '|| nor_table||' select /*+ parallel(b, 4) */ * from '|| tmp_table;
    end if;
​
    --execute ( sqlStr)
    --insert into debug1 values('sqlStr='||sqlStr,sysdate);
    commit;
    Execute Immediate sqlStr;
    
    sqlStr:='truncate table '|| tmp_table;
--   execute( sqlStr) --先不清临时表
  
   -- return 0;
    commit;
end;
/
​

mysql源码

DROP PROCEDURE IF EXISTS `P_TRANSMITDATA`;
​
DELIMITER $$
CREATE PROCEDURE P_TRANSMITDATA
(
    IN nor_table VARCHAR(50),  -- 正式表
    IN delsql VARCHAR(2000),
    IN Flag  VARCHAR(1),  -- 1，增量  2. 全量 3、明细
    IN TORQ  VARCHAR(8)
)
BEGIN
    DECLARE execSql  VARCHAR(2000);
    DECLARE sqlStr  VARCHAR(500);
    DECLARE delsql1  VARCHAR(500);
    DECLARE colname VARCHAR(40);
    DECLARE tmp_table VARCHAR(50);
    -- begin
    SET @tmp_table = CONCAT('TMP_',nor_table);
​
    --  增量情况
    if Flag = '1' 
    then
            -- -处理需要更新的数据
            -- 删除历史数据
            SET @delsql1 = CONCAT('delete from ',nor_table,' a where exists(select 1 from ',@tmp_table,' b where a.',@colname,'=b.',@colname,')  as a');
            SET @sqlStr = CONCAT('insert into ',nor_table,' select a.*,''',TORQ,''' from ',@tmp_table,' a');
    
        PREPARE execSql from @delsql1;
            Execute execSql;
    
    elseif Flag ='2' 
    then
​
            SET @sqlStr = CONCAT('truncate table ',nor_table);
            -- select @sqlStr;
        PREPARE execSql from @sqlStr;
            Execute execSql;
            -- @sqlStr := 'insert into '|| nor_table||' select * from '|| @tmp_table;
            SET @sqlStr = CONCAT('insert into ',nor_table,' select  * from ',@tmp_table);
    elseif Flag ='3'
    then
            SET @sqlStr = CONCAT('delete from ',nor_table,' where DATE_POST=',TORQ);
            PREPARE execSql from @sqlStr;
            Execute execSql;
​
            SET @sqlStr = CONCAT('insert inot ',nor_table,' select *,',TORQ,' from ',@tmp_table);
    elseif Flag = '4'-- 全量数据且只有月末下发
    then
            SET @delsql1 = CONCAT('delete from ',nor_table,' a where exists(select 1 from ',@tmp_table,' b ) ');
    
        PREPARE execSql from @delsql1;
            Execute execSql;
​
        SET @sqlStr = CONCAT('insert into ',nor_table,' select * from ',@tmp_table);
    end if;
​
    -- select @sqlStr;
​
    PREPARE execSql from @sqlStr;
        Execute execSql;
​
    -- 先不清临时表
    -- SET @sqlStr = CONCAT('truncate table ',@tmp_table);
    -- PREPARE execSql from @sqlStr;
        -- Execute execSql;
    
END$$
DELIMITER ;
​
​

二、差异部分

  • 创建时先删除旧的存储过程，Oracle 语句的意思是创建或覆盖该存储过程，功能一样
CREATE OR REPLACE PROCEDURE P_TRANSMITDATA                      -- oracle
DROP PROCEDURE IF EXISTS `P_TRANSMITDATA`;                      -- mysql
  • 设定传入参数属性时，Oracle和mysql的 in 的位置有所差异（Oracle在参数名和数据类型中间，mysql在字段名前面）；
  • Oracle中有varchar2的数据类型，mysql没有
  • 注释：Oracle用--可以直接注释，mysql在--后面要有空格才是正确的注释语法（用#注释，可以不用空格，批量注释时，mysql还可以用 /* */）
#oracle
(
  nor_table in VARCHAR2,  --正式表
  delsql in VARCHAR2,
  Flag in  VARCHAR2,  --1，增量  2. 全量 3、明细
  TORQ in  VARCHAR2
)
​
#mysql
(
    IN nor_table VARCHAR(50),  -- 正式表
    IN delsql VARCHAR(2000),
    IN Flag  VARCHAR(1),  -- 1，增量  2. 全量 3、明细
    IN TORQ  VARCHAR(8)
)
  • 声明变量格式，Oracle需在声明完参数格式后 用 as 并跟上变量定义，然后再begin开始编写存储过程主体；mysql在定义完参数类型后，直接begin然后再声明变量和编写过程主体，声明变量格式时，需加上declare；
#oracle
as
sqlStr  VARCHAR2(500);
delsql1  VARCHAR2(500);
colname VARCHAR2(40);
tmp_table VARCHAR2(50);
begin
​
#mysql
BEGIN
    DECLARE execSql  VARCHAR(2000);
    DECLARE sqlStr  VARCHAR(500);
    DECLARE delsql1  VARCHAR(500);
    DECLARE colname VARCHAR(40);
    DECLARE tmp_table VARCHAR(50);
  • 设定变量值：Oracle：var := values;mysql：set @var = values;(如果不是自定义变量，是系统变量的话，是可以不用加@的)
  • 连接参数或语句，组成一个新内容：Oracle：'val1' || 'val2' || 'val3'; mysql：CONCAT('val1','val2','val3'); (如果是组合的是变量或传入的参数，不用加引号）
  • sql中elif：Oracle：elsif MySQL：elseif
  • 直接执行给定语句： Oracle：Execute Immediate sql;
    • MySQL稍微复杂一些
    • # 提交准备执行语句并命名为execsql（可以自定义），语句如果来自变量的话需要加@，Oracle不用。execsql也可以提前声明数据格式。
      PREPARE execsql FROM @SET_BANK_FLAG;​
      # 执行语句
      EXECUTE execsql;
      # 解除语句
      DEALLOCATE PREPARE execsql;
#oracle
    if  Flag = '1'
    then
        delsql1 :='delete from ' || nor_table || ' a where exists(select 1 from '||tmp_table||' b where a.'||colname||'=b.'||colname||') '; 
        sqlStr :='insert into ' || nor_table||' select a.*,'''||TORQ||''' from '||tmp_table || ' a';
        commit; 
        
        Execute Immediate delsql1;
        
    elsif  Flag ='2'
    then
    
#mysql
    if Flag = '1' 
    then
            SET @delsql1 = CONCAT('delete from ',nor_table,' a where exists(select 1 from ',@tmp_table,' b where a.',@colname,'=b.',@colname,')  as a');
            SET @sqlStr = CONCAT('insert into ',nor_table,' select a.*,''',TORQ,''' from ',@tmp_table,' a');
    
        PREPARE execSql from @delsql1;
            Execute execSql;
    
    elseif Flag ='2' 
    then

三、其他

常见函数上的不同： 1、获取当前时间在mysql中使用NOW()函数，Oracle用sysdate 2、mysql不存在DECODE函数，使用IF函数或者case when来等价替换。 3、日期函数处理 to_date(UPDATEDATE,’yyyy-mm-dd hh24:mi:ss’)” 修改为对应的：date_format(UPDATEDATE, ‘%Y%m%d %H%i%s’) 时间相减使用TIMESTAMPDIFF函数，例如TIMESTAMPDIFF(SECOND,ENDDATE,NOW()) 4、nvl 修改为 IFNULL 5、chr() 修改 CHAR() 6、获取时间的毫秒数使用UNIX_TIMESTAMP，时间的加减要使用date_add和date_sub

在Oracle的内容输出，和报错信息输出的格式上也有些许不同

以上只是在这次修改存储过程中发现的不同，其他地方必然还有很多，需根据报错内容定位，并根据需要实现的功能参考mysql文档找到替代方法。