Migration steps from oracle to mysql and various precautions

Oracle to mysql migration steps and various precautions recently a company project needs to migrate the database once, from oracle to mysql, there is little information on the Internet, I will summarize some of the problems I encountered in this migration process (mainly the migration of Stored Procedures). I hope to give myself a reference for the future. If I am lucky enough to help you better. -- Mysql does not have the package concept. Therefore, the stored procedure is named 'package name during migration. stored PROCEDURE name' format mysql Stored PROCEDURE format: DELIMITER $ -- DELIMITER -- create procedure ([[IN | OUT] parameter name data type...]), IN and OUT are written at the beginning, where IN can omit create procedure 'pkg _ ypgl. prc_ypsc '(prm_ypbm VARCHAR (20), OUT prm_AppCode VARCHAR (20), -- Program Execution Code OUT prm_ErrorMsg VARCHAR (100) -- program execution error message) BEGIN/* variable definition */DECLARE n_count DECIMAL (8); DECLARE done INT (10);/* set the cursor end flag */DECLARE CONTINUE HANDLER FOR NOT FO Und set done = 1; -- if not found does NOT get the value, the value of done is assigned to 1, and the program continues to execute SET done = 0;/* defines a block lavel_error, logical error handling */label_error: BEGIN/* define CURSOR */DECLARE cur_bdjl cursor for select ..... /* OPEN the cursor */OPEN cur_bdjl; repeat fetch cur_bdjl INTO v_aaz001 .... if not done THEN -- IF the done is 0, the loop continues ........ end if;/* END the loop, CLOSE the cursor */UNTIL done -- until not found end repeat; CLOSE cur_bdjl; SET prm_AppCode = 'noerror'; -- SET prm_AppCode to positive SET prm_ErrorMsg = ''; END $ DELIMITER; Data Type: Oracle: varchar2 Mysql: varchar (20) (Parameter Self-SET) Oracle: number () Mysql: decimal () Oracle: date Mysql: datetime defines the variable: Mysql needs to add DECLARE before each sentence to assign a value to the variable: Oracle: v_string: = 'asdas '; Mysql: SET string: = 'asdas '; (the colon Before the equal sign may or may not exist) EXCEPTION Handling: Oracle: exception when others then .... Mysql: DECLARE {EXIT | CONTINUE} HANDLERFOR {error-number | {SQLSTATE error-string} | condition} SQL statement; SQLWARNING indicates all error codes starting with 01 not found indicates all error codes starting with 02, and SQLEXCEPTION indicates all error codes except SQLWARNING and not found at the end of the cursor. declare exit handler forsqlexception, SQLWARNING, not found set a = 1; Note: a begin .... only one HANDLER can be declared in the end. EXIT indicates that SET a = 1 is executed when this exception is encountered, and then the stored procedure is ended. CONTINUE indicates that SET a = 1 when this exception is encountered, after the execution continues Stored Procedure jump: Oracle: GOTO label_error ;..... <Label_error> Mysql: Initialize the error code prm_AppCode to "error", define a block label_error, set prm_AppCode to 'noerror' at the end of the block, and set the intermediate trigger condition, change GOTO label_error to leave label_error. jump out of the block CURSOR: Mysql only has a static CURSOR and no dynamic CURSOR. Use the stored procedure instead of the statement defining the CURSOR as DECLAREcur_bdjl CURSOR ..... Mysql does not support the rec_curname.aaz001 statement. Therefore, all fields obtained by the cursor must be fetch into the variable for loop: Mysql has three cycles (1 ). WHILE loop WHILE expression DO statements end while; (2 ). LOOP statements end loop; (3 ). repeat until loop REPEAT statements UNTIL expression end repeat; sequence: Mysql has no sequence, which is replaced by the function + table method. TABLE creation statement: create table 'seq '('name' varchar (20) not null default ''comment 'serial number generator name', 'val 'bigint (20) unsigned not null comment 'serial number ', 'crem Ent 'int (4) DEFAULT '1' comment' sequence increment', 'Min' bigint (20) default null comment' sequence minimal', 'max 'bigint (20) default null comment 'maximum sequence', 'cycle' char (1) DEFAULT 'n' COMMENT 'whether to cycle', primary key ('name ')) ENGINE = InnoDB default charset = utf8 COMMENT = 'mysql simulates the serial number generator using the table' function as follows (self-written, may be wrong ): DELIMITER $ drop function if exists 'seq '$ create function 'seq' (seq_name VARCHAR (20) returns bigint (20) BEGINDECLARE v_val Ue BIGINT (20); DECLARE v_CYCLE CHAR; DECLARE v_MIN BIGINT (20); DECLARE v_MAX BIGINT (20); SELECT. val,. MIN,. MAX,. CYCLE sequence v_value, v_MIN, v_MAX, v_CYCLE FROM seq a where name = seq_name; IFv_CYCLE = 'y' AND v_value = v_MAX THEN -- the sequence is cyclic AND the current value is its maximum value UPDATE seq -- SET the current value to v_MIN SET val = v_MIN where name = seq_name; else update seq -- otherwise, SET the current value to val + increment SET val = val + increment where name = seq_name; End if; SELECT val INTO v_value FROM seqWHERE NAME = seq_name; RETURN v_value; END $ DELIMITER; UPDATE: Oracle: update table t set (A, B, C) = (select a, B, C FROM TABLE_2 K WHERE K. Y = T.Y) where t. X = V_X; Mysql: update table t, TABLE_2 k set t. A = K. a, T. B = K. b, T.C = K. c where k. Y = T.Y ANDT. X = V_X group by: mysql's group by statement can select fields not grouped, such as select id, name, age from A group by age, the row where name is located is the first row of data in each group. Call: Mysql: call procedure_name (all parameters); Skip loop: Oracle: EXIT; Mysql: Define the loop content as a block label_loop. leave label_loop when the loop needs to be jumped out; note: 1. # comment content 2. -- note the comment content -- add a space. 3. Use/* Comment content */table comment for block comment. Execute the following statement in oracle: select 'altertable' | table_name | 'comment' | ''' | COMMENTS | ''' | ';' fromUSER_TAB_COMMENTS where comments is not null; put the obtained result into mysql and execute it to add the comment table field annotation of the Table Name: execute the following statement in oracle: select distinct (data_type) FROM all_tab_columns Where owner = 'ydmis 'converts the data types involved in the query to the corresponding functions (parameters) in Mysql using the decode function, for example, if CHAR is converted to CHAR (20), the parameter value is obtained in DATA_LENGTH DATA_PRECISION DATA_SCALE of all_tab_columns. Execute the following statement in oracle. Note that the parameters in the decode function must be converted according to the query result in the previous step: select 'altertable' |. table_name | 'modify column '|. column_name | ''| decode (B. data_type, 'varchar2', 'varchar ('| B. DATA_LENGTH | ')', 'date', 'datetime', 'number', 'decimal ('| B. DATA_PRECISION | ',' | B. DATA_SCALE | ')', 'Char ', 'Char (' | B. DATA_LENGTH | ')', 'longraw', 'mediumblob ') | 'comment' | ''' | comments | ''' | '; 'From user_col_comments a, all_tab_columnsbwhe Re. comments is not nulland. table_name = B. table_nameand. column_name = B. column_nameand B. owner = 'ydmis '; put the obtained result into mysql and execute it to add the comment function of the table field: function: oracle mysql remarks eg. oracle eg. mysql connection string | concat () 'A' | 'B' | 'C' concat ('A', 'B', 'C ') convert other formats to the concat (x, '') string. Extract the string substr () substring () substr ('abcd', 1, 3) substring ('abcd', 1, 3) string to date to_date () str_to_date (str, format) to_date (aae036, 'yyyy-mm-dd hh2 4: mi: ss') str_to_date (aae036, '% Y-% m-% d % H: % I: % s') Get the current date sysdate now (), sysdate (), current_date now () returns the time when the program starts to run, sysdate () returns the real-time, generally use now () current_date indicates the specified date_format (date, type) % Y: Year % c: month % d: day % H: hour % I: minute % s: second date_format (now (), '% Y-% c-% d % h: % I: % s') add one day sysdate + 1 DATE_ADD (date, INTERVAL expr type) sysdate + 1 DATE_ADD (now (), INTERVAL 1 DAY) type conversion TO_CHAR TO_DATE TO_NUMBER cast (xxx as type) type: 2 Hexadecimal: BINARY numeric type, with parameters: CHAR () DATE: date time: time date and TIME type: DATETIME floating point number: decimal integer: signed unsigned integer: UNSIGNED to_char (33) cast (33 as char (2) precision conversion to_number (x, type) round (x, d) is retained to the d-digit after the decimal point, and the d-digit is retained to rounding. To retain the d to the left of the x decimal point, you can set d to a negative value to replace the null value NVL (string1, replace_with) ifnull (string1, replace_with) decode (expression, search_1, result_1, default) case expression when search_1 then result_1 else def