oracle遷移mysql資料庫注意

標籤：

oracle轉mysql修改：

1. substr()
   substr( string , 0, 10) 這裡測試 必須從 第一位擷取 既是 substr(string , 1 , 10)
2. to_char()
   只能用做oracle的函數，相容oracle和mysql故 改為concat( … , ‘‘);
   這裡 使用了兩個， 一個 是將類似 int 轉為 string 
3. select * from (select * from table2)   這裡 要加別名
4. nvl函數
   被改為 case when length(str)>0 then ‘處理1‘ else ‘處理2‘ end
5. 使用了oracle  擷取當前日期 並格式化成yyyy-MM-dd 和前一天的日期
   解決，將擷取時間改成了 傳值， java 擷取目前時間和前一天日期傳給資料庫（mysql 有內建   的緩衝，如果使用了 函數，他不會緩衝，另外 為了相容 mysql 和 ora　　cle）
6. oracle  rownum
   select @rownum:[email protected]+1 rownum
   from (select @rownum:=0 from table)
7. to_char(to_date(t1.create_date,‘yyyy-MM-dd hh24:mi:ss‘),‘dateFormat’) 
   修改為了?STR_TO_DATE(t1.create_date, ‘%Y-%m-%d %h:%i:%s’)?DATE_FORMAT(NOW(),‘%Y-%m-%d %H:%i:%s‘
8. 日期直接加減的含義不同了
   比如Oracle中sysdate + 1 變成了sysdate() + interval 1 day（注意如果寫成sysdate() + 1 文法還是正確的，但含義是錯誤的）
   查詢select sysdate() + 1 from dual 在MySQL得到比如 20080223153234（= 20080223153233 + 1）的數
9. select ‘abc‘ || ‘d‘ from dual 兩個資料執行的結果不同（文法都能通過），MySQL要寫成select concat(‘abc‘ , ‘d‘)的形式
10. autocommit 注意 mysql 這個
11. 去掉別名
    delete from table1 t where substr(t.trade_date,1,4)=?
    這樣的 sql 在 oracle 可以 但是在 mysql 需要把別名去掉
    delete from table1  where substr(trade_date,1,4)=?
12. 預存程序
    oracle 帶參數聲明
    create or replace procedure THINKXDT.proc_update_seq(v_typename in varchar2,v_num out varchar2)
    mysql 帶參數聲明
    DROP PROCEDURE IF EXISTS `proc_update_seq`;
    CREATE DEFINER=`root`@`%` PROCEDURE `proc_update_seq`(IN v_typename VARCHAR(200), OUT v_num VARCHAR(200))
    
    oracle 參數為 (名稱 IN/OUT 類型)
    mysql  參數為 (IN/OUT 名稱 類型)    oracle 在預存程序聲明變數 如下： 名稱 類型;   （在 begin 之前 create procedure() as 之後聲明    mysql 在預存程序聲明變數 如下： declare 名稱 類型;  （在begin 和 end 中聲明）

    oracle 在 預存程序 入參 有 帶有 sys_refcursor 遊標的參數，這樣的mysql 不能用
    oracle 使用了  表.Investor%TYPE 這種聲明參數的時候 引用另外一個表的欄位類型，mysql 裡面最好直接 改為 那個類型

---------------------------------------------------------------------------
    oracle 使用了 %rowtype 這種 聲明一條結果集的 mysql不支援，解決(兩部)
         1.建立暫存資料表
         2.是吧這個裡面的結果集欄位 都單獨的聲明成一個 單獨的變數

            oracle 如下：
                   var_name    tablename%rowtype;   --聲明參數
                   -- 賦值
                   select o.* into tablename from oldtablename as o where rownum=1

                   -- 使用
                   tablename.a  直接就是 欄位a的值

            mysql 如下：
                  -- oralce 的聲明參數改為了 建立暫存資料表
                  CREATE TEMPORARY TABLE IF NOT EXISTS tablename (
                       SELECT * 
                       FROM oldtablename t
                       WHERE tag_type=p_tag_type 
                       ORDER BY create_date DESC, id DESC
                       limit 1
                  );
                  -- 表中的欄位都要建立一個 變數
                  declare a int default -1;
                  declare b int;
                  -- 賦值 
                  select t.a into a from tablename as t where limit 1;
                  select t.b into b from tablename as t where limit 1;
                  -- 使用 
                  -- 直接 使用 變數 a b 
                  -- 使用完後 需要 
                  -- 清除暫存資料表
                  TRUNCATE TABLE tmpTable;

---------------------------------------------------------------------------    

    判斷 字元是否 為空白 可以直接 LENGTH(字元) ,長度 <= 0 即為 空

    oracle 中 預存程序可以return
    mysql 需要 弄一個標記
          如下：
          begin 的 改為 label:begin 此時加了一個 label名字的標記
          oracle 中 return 改為 leave label; 即可
    
    oracle預存程序中給變數賦值 可以直接 賦值 var_name := 10;
    mysql 需要這種修改  set var_name := 10; (mysql 必須加上set)
  
    oracle 預存程序傳回值 需要在參數裡面加上 status_rc   out  sys_refcursor
           然後
               open status_rc for
               select ‘-10000‘ as rscode, ‘XXX不可為空‘ as rsmsg from dual;
               return;
    mysql 直接
               select ‘-10000‘ as rscode,
                  ‘操作方向格式不正確（只能為‘+’或‘-’）‘ as rsmsg
               from dual;
               leave label;

    mysql 預存程序中的異常處理 如下：

           -- 異常處理 begin 後 聲明變數的時候加上下面的

           declare _err int default 0;

           declare continue handler for sqlexception, sqlwarning, not found set _err=1;   

           -- 代碼結尾 加上

           if _err=1 then
              rollback;
              select ‘-10000‘ as rscode, ‘XXXX異常！‘ as rsmsg from dual;
              leave label;
           end if;

13. 注意值，有些只 預設不是為空白的 而是 為 null
14. to_char(sysdate, ‘yyyy-mm-dd‘) 
    改為：DATE_FORMAT(now(), ‘%Y-%m-%d‘)
15. 如果欄位類型是 varchar 但是 此欄位值是數字，想以此欄位排序，mysql 需要 查出來後 +0 就會自動轉為 int 類型了

 

oracle遷移mysql資料庫注意