MyBatis Project Actual Quickly Converts MySQL to Oracle Statements

1. Preface

Due to project requirements, the subordinate will migrate the project from mysql to oracle~

Data migration has already been completed ( https://zhengqing.blog.csdn.net/article/details/103694901)

You will now complete converting all MySQL statements in your project into Oracle statements based on MyBatis-Plus

Approximate implementation steps:
  1. Change the project to support dual-library configuration (because the project is based on mysql database and you don't want to replace it directly, add oracle library so that the whole project can support multiple databases, not multiple data sources here!)
  2. Creating Common Functions in Oracle
  3. Walk through the xxxMapper.xml file in the project, find the difference between mysql and oracle statements, and replace most of the SQL
  4. Finally, some special mysql statements are manually modified to oracle statements

2. MyBatis-Plus supports dual library configuration [mysql, oracle]

1. Configure the database-id of mybatis-plus in application.yml

# mybatis-plus configuration
mybatis-plus:
  configuration:
    jdbc-type-for-null: 'null' # Resolve the failure to convert error when oracle updates data to null
    database-id: oracle # Support multi-library configuration for mysql,oracle

2. MybatisPlus Core Profile - > Execute different SQL depending on the database vendor

@Configuration
@MapperScan("com.zhengqing.demo.modules.**.mapper*")
public class MybatisPlusConfig {

    /**
     * `xxxMapper.xml`The `databaseId'in the file automatically identifies the type of database used that corresponds here
     * Note: If `databaseId'is not specified, the SQL statement applies to all databases.
     * 
     * databaseIdProvider: Support multi-database vendors
	 * VendorDatabaseIdProvider: With the database vendor's identity (driving getDatabaseProductName()), mybatis can execute different SQLs based on the database vendor's identity;
	 * MySQL,Oracle,SQL Server,xxxx
     */
    @Bean
    public DatabaseIdProvider getDatabaseIdProvider(){
        DatabaseIdProvider databaseIdProvider = new VendorDatabaseIdProvider();
        Properties properties = new Properties();
        // Alias different database vendors
        properties.setProperty("MySQL","mysql");
        properties.setProperty("Oracle","oracle");
        databaseIdProvider.setProperties(properties);
        return databaseIdProvider;
    }

}

3. Specify the database type through databaseId in xxxMapper.xml

<select id="selectUserInfo" resultMap="UserVO" databaseId="mysql">
    SELECT * FROM Table Name LIMIT 1
</select>

<select id="selectUserInfo" resultMap="UserVO" databaseId="oracle">
    SELECT * FROM Table Name WHERE ROWNUM &lt;= 1
</select>

3. Creating Common Functions in Oracle

Here you can apply it to your personal project ~

1,ORACLE_TO_UNIX

Oracle Time Date type converted to Unix time stamp, equivalent to UNIX_TIMESTAMP in mysql

create or replace function ORACLE_TO_UNIX(in_date IN DATE) return number is  
begin
  return( ROUND( (in_date -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600, 0) );
end ORACLE_TO_UNIX;

2,FIND_IN_SET

CREATE OR REPLACE FUNCTION FIND_IN_SET(piv_str1 varchar2, piv_str2 varchar2, p_sep varchar2 := ',')
RETURN NUMBER IS      
  l_idx    number:=0; -- For calculation piv_str2 Position of separator in  
  str      varchar2(500);  -- Substring intercepted by separator  
  piv_str  varchar2(500) := piv_str2; -- take piv_str2 Assign to piv_str  
  res      number:=0; -- Return results  
  loopIndex number:=0;
BEGIN  
-- If piv_str No splitter, just judge piv_str1 and piv_str Is it equal, equal res=1  
IF instr(piv_str, p_sep, 1) = 0 THEN  
   IF piv_str = piv_str1 THEN   
      res:= 1;  
   END IF;  
ELSE  
-- Loop Intercept by Separator piv_str  
LOOP 
    l_idx := instr(piv_str,p_sep);  
     loopIndex:=loopIndex+1;
	  -- When piv_str When there is a separator in the  
      IF l_idx > 0 THEN  
   		 -- Truncate the field before the first separator str  
         str:= substr(piv_str,1,l_idx-1);  
   		 -- judge str and piv_str1 Is it equal, equal res=1 And end the loop  
         IF str = piv_str1 THEN   
           res:= loopIndex;  
           EXIT;  
         END IF;  
        piv_str := substr(piv_str,l_idx+length(p_sep));  
      ELSE
    	-- When intercepted piv_str Judgement when no divider exists piv_str and piv_str1 Is it equal, equal res=1  
        IF piv_str = piv_str1 THEN   
           res:= loopIndex;  
        END IF;  
        -- Jump out of the loop whether the end is equal or not  
        EXIT;  
      END IF;  
END LOOP;  
-- End cycle  
END IF;  
-- Return res  
RETURN res;  
END FIND_IN_SET; 

4. Tool class (MySQL statement to Oracle statement)

Replace steps:
  1. Add databaseId="mysql" to all sql statements in xxxMapper.xml
  2. Copy a copy of mysql's sql (oracle statement to be replaced)
  3. Add databaseId="oracle" to the copied sql
  4. Find out the difference between mysql and oracle statements and replace sql

Warm Tip: The tool class here is only for reference. Actual operation can be modified according to your own project. Before operation, it is recommended to back up your own project in order to prevent improper operation from losing code.

import org.apache.commons.lang3.StringUtils;
import org.junit.Test;
import java.io.*;
import java.util.*;

/**
 *  <p> mysql Migrate oracle test tool class </p>
 *
 * @description :  
 * @author : zhengqing  
 * @date : 2020/1/08 10:10
 */
public class MySQLToOracleTest {

    private final static String ORACLE_SQL = "    <!-- ====================================== ↓↓↓↓↓↓ oracle ↓↓↓↓↓↓  ====================================== -->";

    @Test // Replace sql statement in project
    public void testSQL() throws Exception {
        String path = System.getProperty("user.dir") + "\\src\\main\\java\\com\\zhengqing\\xxx"; // TODO Replace this with your own project path
        File file = new File(path);
        HashMap<Object, Object> fileMap = new HashMap<>();
        getAllFileByRecursion(fileMap, file);

        fileMap.forEach((key, value) -> {
            String fileXmlName = (String) key;
            File fileXml = (File) value;
            String filePath = fileXml.getPath();

            if (fileXmlName.equals("Test.xml")) {
                System.out.println(filePath);
                try {
                    // 1. Add databaseId="mysql"
                    addMysql(filePath);
                    // 2. Copy an oracle sql
                    if (!checkHasOracle(filePath)) {
                        copyMysqlToOracle(filePath);
                    }
                    // 3. Add databaseId="oracle"
                    addOracle(filePath);
                    // 4. Replace sql statements in mybatis `xxMapper.xml`
                    repalceSQL(filePath);
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }

        });
        System.out.println(fileMap);
    }

    /**
     * Replace sql statement in mybatis `xxMapper.xml`
     */
    private static void repalceSQL(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // Memory stream, as temporary stream
        CharArrayWriter tempStream = new CharArrayWriter();
        // replace
        String line = null;
        int row = 0;
        int rowOracle = 0;
        while ((line = bufIn.readLine()) != null) {
            row++;
            if (line.contains(ORACLE_SQL)) {
                rowOracle = row;
            }

            if (rowOracle != 0 && row > rowOracle) {
                // 1) Replace `LIMIT` -> ` AND ROWNUM < = 1`TODO [Note: Some contain `ORDER BY` keywords, which need to be handled separately]
                if (line.contains("limit") || line.contains("LIMIT")) {
                    System.out.println();
                    System.out.println(" ==============================↓↓↓↓↓↓  copy Paging Requirements (" + row + ")  ↓↓↓↓↓↓===================================== ");
                    System.out.println("SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM (");
                    System.out.println();
                    System.out.println(") TMP  WHERE ROWNUM &lt;=1) WHERE ROW_ID > 0");
                    System.out.println();
                }
                line = StringUtils.replace(line, "limit 1", "AND ROWNUM &lt;= 1");
                line = StringUtils.replace(line, "LIMIT 1", "AND ROWNUM &lt;= 1");
                line = StringUtils.replace(line, "limit 0,1", "AND ROWNUM &lt;= 1");
                line = StringUtils.replace(line, "LIMIT 0,1", "AND ROWNUM &lt;= 1");

                // (2)'`'symbol cannot be used in oracle
                line = StringUtils.replace(line, "`", "");

                // (3) CONCAT ('%', #{name},'%') ->'%'|#{name}|'%'(the concat function in Oracle can only take two parameters)
                if (line.contains("concat")) {
//                    String newLine = line.substring(line.indexOf("concat(") + 7, line.lastIndexOf("'%')") + 3);
                    line = line.replaceAll(",", " || ");
                    line = line.replaceAll("concat", "");
                }
                if (line.contains("CONCAT")) {
//                    String newLine = line.substring(line.indexOf("CONCAT(") + 7, line.lastIndexOf("'%')") + 3);
                    line = line.replaceAll(",", " || ");
                    line = line.replaceAll("CONCAT", "");
                }

                // (4) `UNIX_TIMESTAMP` -> `ORACLE_TO_UNIX` date type time-to-10 bit timestamp
                line = line.replaceAll("UNIX_TIMESTAMP", "ORACLE_TO_UNIX");

                // Some keywords need to be enclosed in double quotation marks TODO [Note: Field names are capitalized, and aliases mapped need to be saved in lower case!`level ->'LEVEL'` `user ->'USER' ` `number ->'NUMBER'` `desc ->'DESC'`
                List<String> keywordList = new ArrayList<>(Arrays.asList("level", "user", "number"));
                if (!line.contains("test=")) {
                    for (String e : keywordList) {
                        // StringUtils.swapCase(e): case-switching
                        line = line.replaceAll(" " + e + " ", " \"" + StringUtils.swapCase(e) + "\" ");
                        line = line.replaceAll("." + e + " ", "\\.\"" + StringUtils.swapCase(e) + "\" ");
                        if (line.endsWith(e) || line.endsWith(e + ",")) {
                            line = line.replaceAll(e, "\"" + StringUtils.swapCase(e) + "\"");
                        }
                    }
                }
                if (line.endsWith(" date") || line.endsWith(" date,") || line.endsWith(" 'date'") || line.endsWith(" 'DATE'") || line.endsWith("DATE")) {
                    line = line.replaceAll(" date", " \"date\"");
                    line = line.replaceAll(" date,", " \"date,\"");
                    line = line.replaceAll(" 'date'", " \"date\"");
                    line = line.replaceAll(" 'DATE'", " \"date\"");
                    line = line.replaceAll(" DATE", " \"date\"");
                }
                line = line.replaceAll(" date ", " \"date\" ");
                line = line.replaceAll(" DATE ", " \"date\" ");

                // ⑥ `IFNULL` -> `NVL`
                line = line.replaceAll("IFNULL", "NVL");
                line = line.replaceAll("ifnull", "NVL");

                // Time`str_to_date` -> `to_date` `date_format` -> `to_char`
                // `%Y-%m-%d`  -> `yyyy-MM-dd`    `%Y-%m` -> `yyyy-MM`
                line = line.replaceAll("str_to_date", "TO_DATE");
                line = line.replaceAll("STR_TO_DATE", "TO_DATE");
                line = line.replaceAll("date_format", "TO_CHAR");
                line = line.replaceAll("DATE_FORMAT", "TO_CHAR");

                // Note the replacement order here, the longest should be placed first!!!
                line = line.replaceAll("%Y-%m-%d %H:%i:%S", "yyyy-MM-dd HH24:mi:ss");
                line = line.replaceAll("%Y-%m-%d %H:%i:%s", "yyyy-MM-dd HH24:mi:ss");
                line = line.replaceAll("%Y-%m-%d %H:%i", "yyyy-MM-dd HH24:mi");
                line = line.replaceAll("%Y-%m-%d %H", "yyyy-MM-dd HH24");
                line = line.replaceAll("%Y-%m-%d %h", "yyyy-MM-dd HH");
                line = line.replaceAll("%Y-%m-%d", "yyyy-MM-dd");
                line = line.replaceAll("%Y-%m", "yyyy-MM");
                line = line.replaceAll("%Y", "yyyy");
                line = line.replaceAll("%H", "HH24");
                line = line.replaceAll("%k", "HH24");

                line = line.replaceAll("now\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");
                line = line.replaceAll("NOW\\(\\)", "(SELECT SYSDATE + 8/24 FROM DUAL)");

                // ⑧ ...

                // Manual SQL [group by | bulk insert |...]
            }
            // Write this line to memory
            tempStream.write(line);
            // add linefeeds
            tempStream.append(System.getProperty("line.separator"));
        }
        // Close Input Stream
        bufIn.close();
        // Write stream in memory to file
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * Add databaseId="mysql"
     */
    private static void addMysql(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // Memory stream, as temporary stream
        CharArrayWriter tempStream = new CharArrayWriter();
        // replace
        String line = null;
        while ((line = bufIn.readLine()) != null) {
            if ((line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete")) && !line.contains("databaseId")) {
                if (line.endsWith(">")) {
                    line = line.replaceAll(">", " databaseId=\"mysql\">");
                } else {
                    line = line + " databaseId=\"mysql\"";
                }
            }
            // Write this line to memory
            tempStream.write(line);
            // add linefeeds
            tempStream.append(System.getProperty("line.separator"));
        }
        // Close Input Stream
        bufIn.close();
        // Write stream in memory to file
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * Join databaseId="oracle"
     */
    private static void addOracle(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // Memory stream, as temporary stream
        CharArrayWriter tempStream = new CharArrayWriter();
        HashSet<String> lineSet = new HashSet<>();
        // replace
        String line = null;
        while ((line = bufIn.readLine()) != null) {
            if (line.contains("databaseId=\"mysql\"")) {
                if (lineSet.contains(line)) {
                    line = line.replaceAll("databaseId=\"mysql\"", "databaseId=\"oracle\"");
                }
                lineSet.add(line);
            }
            // Write this line to memory
            tempStream.write(line);
            // add linefeeds
            tempStream.append(System.getProperty("line.separator"));
        }
        // Close Input Stream
        bufIn.close();
        // Write stream in memory to file
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * Copy an oracle sql
     */
    private static void copyMysqlToOracle(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // Memory stream, as temporary stream
        CharArrayWriter tempStream = new CharArrayWriter();
        // replace
        String line = null;
        // Rows to be replaced
        List<String> lineList = new LinkedList<>();

        int row = 0;
        int firstRow = 0;

        while ((line = bufIn.readLine()) != null) {
            row++;
            if (line.contains("<select") || line.contains("<update") || line.contains("<insert") || line.contains("<delete")) {
                firstRow = row;
            }

            // Add Replacement
            if (firstRow != 0 && row >= firstRow && !line.contains("</mapper>")) {
                lineList.add(line);
            }

            // Query End Location
            if (line.contains("</mapper>")) {
                tempStream.append(System.getProperty("line.separator"));
                tempStream.write(ORACLE_SQL);
                tempStream.append(System.getProperty("line.separator"));
                tempStream.append(System.getProperty("line.separator"));

                lineList.forEach(lineValue -> {
                    // copy mysql statement to oracle
                    try {
                        tempStream.write(lineValue);
                        tempStream.append(System.getProperty("line.separator"));
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                });

                tempStream.append(System.getProperty("line.separator"));
            }
            // Write this line to memory
            tempStream.write(line);
            // add linefeeds
            tempStream.append(System.getProperty("line.separator"));
        }
        // Close Input Stream
        bufIn.close();
        // Write stream in memory to file
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
    }

    /**
     * Check if SQL has been copied
     */
    private static boolean checkHasOracle(String path) throws IOException {
        File file = new File(path);
        FileReader in = new FileReader(file);
        BufferedReader bufIn = new BufferedReader(in);
        // Memory stream, as temporary stream
        CharArrayWriter tempStream = new CharArrayWriter();
        // replace
        String line = null;
        boolean result = false;
        while ((line = bufIn.readLine()) != null) {
            if (line.contains(ORACLE_SQL)) {
                result = true;
            }
            // Write this line to memory
            tempStream.write(line);
            // add linefeeds
            tempStream.append(System.getProperty("line.separator"));
        }
        // Close Input Stream
        bufIn.close();
        // Write stream in memory to file
        FileWriter out = new FileWriter(file);
        tempStream.writeTo(out);
        out.close();
        return result;
    }

    /**
     * Recursive Folder - > Find all xml files
     */
    private static void getAllFileByRecursion(HashMap<Object, Object> fileMap, File file) {
        File[] fs = file.listFiles();
        for (File f : fs) {
            String fileName = f.getName();
            if (f.isDirectory()) {
                // If the directory is recursive, otherwise print the files in the directory
                getAllFileByRecursion(fileMap, f);
            }
            if (f.isFile() && fileName.endsWith(".xml")) {
                fileMap.put(fileName, f);
            }
        }
    }

}

V. Summary

Here's a brief description of the differences between SQL in MySQL and Oracle and an example of how MySQL statements translate into Oracle statements

1. Paging

  1. mysql: LIMIT 0,1
  2. oracle: ROWNUM <= 1
Scenario 1: mysql does not contain ORDER BY
-- mysql
SELECT * FROM Table Name LIMIT 1

-- oracle
SELECT * FROM Table Name  WHERE ROWNUM <= 1
Scenario 2: mysql contains ORDER BY
-- mysql
SELECT * FROM Table Name ORDER BY Field name DESC LIMIT 1 


-- oracle
SELECT * FROM ( 
	SELECT TMP.*, ROWNUM ROW_ID FROM ( 
		SELECT * FROM Table Name ORDER BY Field name DESC
	) TMP WHERE ROWNUM <= 1 ) 
WHERE ROW_ID > 0;
Warm tip: SQL SELECT statement execution order
  1. FROM clause assembling data from different data sources
  2. WHERE clause filters records based on specified conditions
  3. GROUP BY clause divides data into groups
  4. Aggregation function for calculation
  5. HAVING clause filtering grouping
  6. Calculate all expressions
  7. ORDER BY Sorts Results

2. Field names in oracle cannot use the symbol'`'to include

-- mysql
 SELECT `Field Name`FROM Table Name

-- oracle
 SELECT field name FROM table name

3. String Stitching

Note: CONCAT function in Oracle can only take two parameters, so change to ||stitching

  1. mysql: CONCAT('%', 'xxx' , '%')
  2. oracle: '%' || 'xxx' || '%'
-- mysql
 SELECT field name FROM table name WHERE field name LIKE CONCAT('%','helloworld','%')

-- oracle
 SELECT field name FROM table name WHERE field name LIKE ('%'|'helloworld' | |'%')

4. date type time to 10-bit timestamp

  1. mysql: UNIX_TIMESTAMP
  2. oracle: ORACLE_TO_UNIX (Note: This function was created manually in step 3, not by oracle itself!)

5. The field name is the Oracle keyword with double quotation marks

Warm Tip: Field names need to be capitalized. If the Java entity class corresponds to lower case fields, note that the aliases mapped need to be kept in lower case ~

For example:

  1. level -> "LEVEL"
  2. user -> "USER"
  3. number -> "NUMBER"
  4. desc -> "DESC"
  5. date -> DATE

6. Determine whether it is NULL: If x is NULL, return value, otherwise return x value itself

  1. mysql: IFNULL(x, value)
  2. oracle: NVL(x, value)

7. Date-Time Interchange

Before mysql, after oracle

  1. String type to time type: STR_TO_DATE -> TO_DATE
  2. Time type to specified string type: DATE_FORMAT -> TO_CHAR
  3. Get the current time of the system: NOW () -> SELECT SYSDATE FROM DUAL
-- Convert time type to specified string type
SELECT DATE_FORMAT( NOW(),'%Y-%m-%d %H:%i:%s');             -- mysql
SELECT TO_CHAR( SYSDATE,'yyyy-MM-dd HH24:mi:ss') FROM DUAL; -- oracle

-- String type to time type
SELECT STR_TO_DATE( NOW(), '%Y-%m-%d %H');             -- mysql
SELECT TO_DATE( '2020-01-09', 'yyyy-MM-dd') FROM DUAL; -- oracle [ Note: oracle The format of string time in the former should be the same as that in the latter.~ ]

-- Get the current time of the system
SELECT NOW(); -- mysql
SELECT SYSDATE + 8/24 FROM DUAL; -- oralce [Note: If the server time does not differ by 8 hours, you do not need to add`8/24`]

-- mysql
SELECT YEAR( NOW() );     -- Year finding
SELECT QUARTER( NOW() );  -- Search for quarter
SELECT MONTH( NOW() );    -- Calendar month

-- oracle
SELECT TO_CHAR(SYSDATE, 'Q') FROM DUAL; -- Search for quarter

In addition, the format of the time identifier used in the subtitle is given here.

-- Front: mysql             After: oracle
"%Y-%m-%d %H:%i:%S"     "yyyy-MM-dd HH24:mi:ss"
"%Y-%m-%d %H:%i:%s"     "yyyy-MM-dd HH24:mi:ss"
"%Y-%m-%d %H:%i"        "yyyy-MM-dd HH24:mi"
"%Y-%m-%d %H"           "yyyy-MM-dd HH24"
"%Y-%m-%d %h"           "yyyy-MM-dd HH"
"%Y-%m-%d"              "yyyy-MM-dd"
"%Y-%m"                 "yyyy-MM"
"%Y"                    "yyyy"
"%H"                    "HH24"
"%k"                    "HH24"

8. Left and right field types must be the same when judging

It is important to note here that different types of older versions may be queried under different oracle versions, but it is recommended to change to the same type of association to avoid problems with future database version upgrades!!!

Suggest small to large, such as number to string, and use CONCAT to modify the type, since both mysql and oracle support this function and will not have problems with special types~

-- ex: `JOIN` Association of two tables when associating tables`Field type`Must`identical`
SELECT a.*,b.*
FROM Table 1 a
LEFT JOIN Table 2 b on a.String Type Field = CONCAT(b.Number Type Field, '')

9. Bulk Insertion

-- mysql
<insert id="insertBatch" databaseId="mysql">
    INSERT INTO Table Name( `Field name 1`, `Field Name 2`, `field...`) VALUES
    <foreach collection="list" item="item" separator="," open="(" close=")">
        #{item.Field 1},#{item.Field 2},#{Each field name in the item...}
    </foreach>
</insert>

-- oracle
<insert id="insertBatch" databaseId="oracle">
    INSERT INTO Table Name(Field name 1,Field Name 2,xxx...)
    SELECT A.*
    FROM(
        <foreach collection="list" item="item" index="index" separator="UNION ALL" >
            SELECT
                #{item.Field 1},#{item.Field 2},#{Each field name in the item...}
            FROM DUAL
        </foreach>
    ) A
</insert>

10. Grouping GROUP BY

After GROUP BY grouping in oracle, all fields queried (except grouping fields) must be fields of aggregate function, otherwise errors will occur!

Solve:

  1. Change Query Field to Aggregate Function
  2. Use the following analysis functions OVER (Partition BY...) and windowing functions
-- mysql
SELECT Field name,xxx... FROM Table Name GROUP BY Grouping Fields

-- oracle
SELECT
    *
FROM (
    SELECT tb.*, ROW_NUMBER ( ) OVER ( PARTITION BY tb.Grouping Fields ORDER BY tb.sort field DESC ) AS result
    FROM (
        SELECT Field name,xxx... FROM Table Name -- Here is the query sql,Remove`GROUP BY`Grouping condition, add grouping field above [Note: This sql All of the query fields in the query field are aggregate function fields or none of them are!]
    ) tb
) WHERE result = 1

11. Table aliases in Oracle cannot be AS, column aliases can be AS

why?: To prevent conflicts with keyword AS in Oracle stored procedures

12. Note that Oracle is strict with data types!!!

MySQL differs from Oracle in more than the points mentioned in the small edition, but you also need to make comparisons based on actual projects and then modify them.

6. This article MySQL to Oracle statement tool source code

Warm Tip: If the later edition is idle, the test replacement tool class provided above will be modified and stored in the following github repository at that time...

https://github.com/zhengqingya/mysql-to-oracle

Tags: Programming Oracle MySQL SQL Database

Posted on Thu, 09 Jan 2020 22:52:43 -0500 by xatter