- Data type processing for automatic growth
MySQL There are auto growing data types , You do not need to operate this field when inserting records , The data value will be obtained automatically .Oracle There is no automatic growth data type , You need to create an auto growing serial number , When inserting a record, assign the next value of the sequence number to this field .
CREATE SEQUENCE The name of the serial number ( It's better to have a 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 autogrow sequence number NUMBER(6) , The maximum value is 999999 INSERT Statement inserts the field value as : The name of the serial number .NEXTVAL
- Handling of single quotes
MySQL Double quotation marks can be used in string ,Oracle You can only wrap strings in single quotation marks . Single quotation marks must be replaced before inserting and modifying strings ： Replace all occurrences of a single quotation mark with two single quotation marks .
- Paginated SQL Statement processing
MySQL Dealing with flipping SQL The sentence is simple , use
LIMIT.Oracle Paginated SQL The sentence is more complicated . Each result set has only one ROWNUM Field indicates its location , And only use ROWNUM<100, Out-of-service ROWNUM>80.
SELECT ID, [FIELD_NAME,...] FROM TABLE_NAME WHERE ID IN ( SELECT ID FROM ( SELECT ROWNUM AS NUMROW, ID FROM TABLE_NAME WHERE Conditions 1 ORDER BY Conditions 2) WHERE NUMROW > 80 AND NUMROW < 100 ) ORDER BY Conditions 3;
- Long string processing
Long string processing Oracle There's something special about it .INSERT and UPDATE The maximum operable string length is less than or equal to 4000 A single byte , If you want to insert a longer string , Please consider the fields with CLOB type , Method borrowing ORACLE It comes with DBMS_LOB Package . Before inserting a modified record, you must make a non null and length judgment , Field values that cannot be empty and field values that exceed the length should be warned , Return to last operation .
- Date field processing
MySQL The date field is divided into DATE and TIME Two kinds of ,Oracle The date field is only DATE, Contains the information of year, month, day, hour, minute and second , The system time of the current database is SYSDATE, Accurate to seconds , Or use a string to convert it into a date type function
The mathematical formula of date field is very different .MySQL Find the time away from the current time 7 Day use DATE_FIELD_NAME > SUBDATE（NOW（）,INTERVAL 7 DAY）ORACLE Find the time away from the current time 7 Day use DATE_FIELD_NAME >SYSDATE - 7;
The processing of empty characters
MySQL The non empty field of the also has empty content ,Oracle If a non empty field is defined in, it is not allowed to have empty content . Press MySQL Of NOT NULL To define Oracle Table structure , There are errors when importing data . Therefore, it is necessary to judge the null character when importing data , If NULL Or empty characters , You need to change it to a string of spaces .
Fuzzy comparison of strings
MySQL In the use Field name like '% character string %',Oracle You can also use Field name like '% character string %' But this method doesn't use indexes , Not fast , Compare functions with strings instr( Field name ,' character string ')>0 You'll get more accurate results .
In programs and functions , After the operation of the database, please pay attention to the release of result set and pointer .