MySQL migration to Oracle Simple records

Due to the expansion of the business and the requirements of the customer, the company needs to migrate the data originally running on MySQL to Oracle.

The first few years of work have been short-lived, but infrequently used, and the initial impressions are running out.

This article mainly records in the database migration process, encountered the related problem solution. The main focus is on the choice of data types, the substitution of partial functions, and the differences between other databases.

For those of you who have the same needs, I would be glad to have a little help:)

1. Selection of data types.

Numeric type:

MySQL has two types of numbers: integers (whole number) and real numbers.

The storage integer can be selected: Tinyint,smallint,mediumint,int,bigint, corresponding to 8,16,24,32,64 bit storage space respectively. They can store ranges from 2 N-1 to 2 N-1-1, where n is the number of bits of storage space.

An integer type has an optional unsigned property, which means that negative values are not allowed, which can roughly increase the upper limit of a positive number by a factor. For example tinyint unsigned can store a range of 0~255, while the tinyint storage range is-128-127.

A real number is one with a fractional part. The decimal type is used to store exact decimals. Because of the extra computation and overhead, it should be used only when the decimal is accurately calculated. Consider using bigint instead of decimal when the amount of data is large.

In comparison, Oracle has a number,int,float,binary_float,binary_double,numeric type

The number type occupies 0~22 bytes of storage space in Oracle, is a variable-length data type, uses Oracle internal algorithm, is a soft data type, therefore has the good data accuracy, the versatility and the portability is strong

The following types are mapped from the number type.

NUMERIC (p,s): Fully mapped to number (p,s). If p is not specified, the default is 38.

DECIMAL (p,s) or Dec (p,s): Fully mapped to number (p,s). If p is specified, the default is 38

Integer or int: fully mapped to number (38) type.

SMALLINT: Fully mapped to number (38) type

Because of the differences in the storage structure, binary_float and binary_double can store a larger range of data than number, but the accuracy is not as number. If you are storing financial data, it is recommended that you use number.

For scientific operations, binary_float and Binary_double are recommended, since floating-point data uses hardware calculations, which are computationally efficient.

For various types of int in MySQL, it is recommended that the unification corresponds to the number type of Oracle. (Number (P,s) is also fully mapped to number type, so you do not need to set the precision)

Character type:

MySQL uses both varchar and char two types.

VARCHAR is a variable-length type

Char is a fixed-length type

Char, a fixed-length type, removes all trailing spaces, and some behaviors are difficult to understand when storing and comparing data. So the normal choice of varchar as well.

A char in Oracle is a fixed-length type that is populated with spaces.

VARCHAR2 uses a variable-length way to store data, which saves space. The storage efficiency is comparable to char.

In addition, the char type also has a trailing space problem.

For Oracle types, the VARCHAR2 should be chosen as much as possible due to working habits and storage requirements.

The role of delimiter in 2.Mysql

The keyword is to tell the interpreter whether the command has ended and whether MySQL can execute subsequent scripts.

DELIMITER;D rop PROCEDURE IF EXISTS p_contract; --The statement can be executed immediately delimiter $$--not terminated with a semicolon, and subsequent statements await execution when the $$ is encountered.    CREATE PROCEDURE p_contract () BEGIN ..... END $$-Executes the intermediate statement. DELIMITER;

3.DECLARE CONTINUE HANDLER for not FOUND

If no data is returned, the program continues, and the variable is_found is set to 0, which occurs when the select XX to XXX from TableName.

4.UNIX timestamp and date conversion to each other

MySQL date and time functions, recommended you can read this article: http://www.cnblogs.com/redfox241/archive/2009/07/23/1529092.html

Oracle gets UTC Time:

Select To_char (SYS_EXTRACT_UTC (Systimestamp), ' Yyyy-mm-dd Hh24:mi:ss ') from dual; --UTC time

Unix timestamp-to-date conversion between MySQL

Unix timestamp converted to date function: From_unixtime ()

Select From_unixtime (1410318106);

Date conversion to UNIX timestamp function: Unix_timestamp ()

Select Unix_timestamp (' 2014-09-10 11:01:46 ');

where Date_format (From_unixtime (' 1410318106 ', '%y-%m-%d%h:%m:%s '), '%y-%m-%d%h:%m:%s ')

Oracle does not have this kind of conversion function, it needs to write itself (the following writing is also from the network)

--oracle time Date type conversion to UNIX timestamp Create or Replace function Bill_query.oracle_to_unix (in_date date) return number Isbegin RET Urn ((in_date-to_date (' 19700101 ', ' YYYYMMDD ')) *86400-to_number (SUBSTR (Tz_offset (Sessiontimezone), 1, 3)) *3600); END Oracle_to_unix;/--unix timestamp converted to Oracle time Create or Replace function bill_query.unix_to_oracle (in_number number) return Date Isbegin return (to_date (' 19700101 ', ' yyyymmdd ') + in_number/86400 +to_number (SUBSTR (tz_offset), 1 , 3))/24); END unix_to_oracle;/

The indexes and constraints in 5.Mysql correspond to Oracle

PRIMARY KEY---> PRIMARY key

Index---> Index

Unique key–> Unique Index

Key---> Index

7.auto_increment Property

There is a auto_increment attribute in the field in Mysql and a sequence substitution is required in Oracle.

When this field is used, an explicit call is required to insert the Sequence_name.nextval

8.Mysql you can query the table for presence and deletion by using a statement when creating a table (drop database if exists table_name)

Oracle can be implemented with custom functions. (When a DDL statement exists in a function, it cannot be called by SELECT, only by assigning a value.) This can be considered easier to implement and invoke in a stored procedure. )

Create or replace function hytpdtnmdb.fun_obj_ifexists (v_obj_name in   VARCHAR2)  return number  is    num_tab   number;     num_seq    number;begin      select  Count (1)  into num_tab from all_objects where  owner| | | Object_name = upper (v_obj_name)  and object_type= ' TABLE ';       if  num_tab = 1 then          execute immediate  ' drop table   ' | | V_obj_name;         return (Num_tab);       end if;      select count (1)  into num_seq  from all_objects where  owner| | '. ' | | Object_name = upper (V_obj_name) &NBSp;and object_type= ' SEQUENCE ';        if num_seq = 1  THEN         EXECUTE IMMEDIATE  ' drop  sequence   ' | | v_obj_name;         return num_seq;       END IF;      return  -1;end fun_obj_ifexists;

9. About Oracle stored procedure execution permissions issues

In the case of a user, an update, delete, drop table, or CREATE table B user object is required.
Even if the user is given DBA authority, in the anonymous stored procedure or in the Command window, you can perform normally, there will be insufficient permissions in the stored procedure.

Through the online access to information, by adding Authid Current_User. Creating a stored procedure in the same way as create OR REPLACE procedure DEMO (ID in number) AUTHID Current_User as can be resolved.
Article Link: http://blog.csdn.net/gavinloo/article/details/6869234

Occasionally, however, there is still a problem with insufficient permissions, which can be resolved by grant explicit authorization.

10. Differences in some functions

• Concat can connect multiple strings in MySQL. Only two characters can be connected in Oracle. If you need more than one string of connections, you still want to use ' | | '

• Numtoyminterval (n, ' char_expr ')
  CHAR_EXPR: Date description, which can be year and month;

• Numtodsinterval (n, ' char_expr ')

  CHAR_EXPR: Time description, can be day, hour, minute, second;
  
  Interval can only be followed by numbers

• Interval can be used under normal circumstances, but when the value to be increased or decreased is a variable, only the

Numtodsinterval and Numtoyminterval substitution.

This article is from the "Libydwei" blog, make sure to keep this source http://libydwei.blog.51cto.com/37541/1772506

MySQL migration to Oracle Simple records