Precautions for migrating a program from MySQL to Oracle

1. Automatically increasing data type Processing

MySQL has a data type that increases automatically. When you insert a record, you do not need to operate on this field. The data value is automatically obtained. Oracle does not have an auto-increasing data type. You need to create an auto-increasing serial number. When inserting a record, you need to assign the next value of the serial number to this field.

Create sequence serial number name (preferably table name + Serial number mark) increment by 1 start with 1 maxvalue 99999 cycle nocache;

The maximum value is determined by the length of the field. If the defined auto-increment serial number (6), the maximum value is 999999.

Insert statement insert this field value: name of the serial number. nextval

2. Single quotes

MySQL can use double quotes to enclose strings. Oracle can only use single quotes to enclose strings. You must replace single quotes before inserting and modifying strings: replace all the existing single quotes with two single quotes.

3. Processing of paging SQL statements

The SQL statement for MySQL to process paging is relatively simple. It uses limit to start the position and record the number. In PHP, you can also use seek to locate the result set. It is complicated for Oracle to process paging SQL statements. Each result set has only one rownum field to indicate its location, and only rownum <100, not rownum> 80 can be used.

The following two SQL statements (ID is the field name of the unique keyword) are better after analysis ):

Statement 1:

Select ID, [field_name,...] from table_name where ID in (select ID from (select rownum as numrow, ID from table_name where Condition 1 order by condition 2) Where numrow> 80 and numrow <100) order by Condition 3;

Statement 2:

Select * from (select rownum as numrow, C. * From (select [field_name,...] from table_name where Condition 1 order by condition 2) c) Where numrow> 80 and numrow <100) order by Condition 3;

PDM Chinese website www.pdmcn.com