Oracle and MySQL user-defined functions
The following sections will provide details on assessing, preparing, and running the migration of user-defined functions (UDFs) using Amazon DMS.
Feature compatibility | Amazon SCT / Amazon DMS automation level | Amazon SCT action code index | Key differences |
---|---|---|---|
|
|
Syntax and option differences. |
Oracle usage
You can create an Oracle UDF using PL/SQL, Java, or C. UDFs are useful for providing functionality not available in SQL or SQL built-in functions. They can appear in SQL statements wherever built-in SQL functions can appear.
You can use UDFs in the following cases:
-
To return a single value from a
SELECT
statement (scalar function). -
While performing DML operations.
-
In
WHERE
,GROUP BY
,ORDER BY
,HAVING
,CONNECT BY
, andSTART WITH
clauses.
Examples
Create a simple Oracle UDF with arguments for employee HIRE_DATE
and SALARY
as INPUT
parameters and calculate the overall salary over the employee’s years of service for the company.
CREATE OR REPLACE FUNCTION TOTAL_EMP_SAL_BY_YEARS (p_hire_date DATE, p_current_sal NUMBER) RETURN NUMBER AS v_years_of_service NUMBER; v_total_sal_by_years NUMBER; BEGIN SELECT EXTRACT(YEAR FROM SYSDATE) - EXTRACT(YEAR FROM to_date(p_hire_date)) INTO v_years_of_service FROM dual; v_total_sal_by_years:=p_current_sal*v_years_of_service; RETURN v_total_sal_by_years; END; / -- Verifying SELECT EMPLOYEE_ID, FIRST_NAME, TOTAL_EMP_SAL_BY_YEARS(HIRE_DATE, SALARY)AS TOTAL_ SALARY FROM EMPLOYEES; EMPLOYEE_ID FIRST_NAME TOTAL_SALARY 100 Steven 364000 101 Neena 204000 102 Lex 272000 103 Alexander 99000 104 Bruce 60000 105 David 57600 …
For more information, see CREATE FUNCTION
MySQL usage
Aurora MySQL supports user-defined scalar functions only. There is no support for table-valued functions.
Aurora MySQL doesn’t permit stored functions to contain explicit SQL transaction statements such as COMMIT
and ROLLBACK
.
In Aurora MySQL, you can explicitly specify several options with the CREATE FUNCTION
statement. These characteristics are saved with the function definition and are viewable with the SHOW CREATE FUNCTION
statement.
-
The
DETERMINISTIC
option must be explicitly stated. Otherwise, the engine assumes it is not deterministic.Note
MySQL doesn’t check the validity of the deterministic property declaration. If you wrongly specify a function as
DETERMINISTIC
when it is not, unexpected results and errors may occur. -
CONTAINS
SQL indicates the function code does not contain statements that read or modify data. -
READS SQL DATA
indicates the function code contains statements that read data such asSELECT
but not statements that modify data such asINSERT
,DELETE
, orUPDATE
. -
MODIFIES SQL DATA
indicates the function code contains statements that may modify data.Note
These options are advisory only. The server doesn’t constrain the function code based on the declaration. This feature is useful for code management.
Syntax
CREATE FUNCTION <Function Name> ([<Function Parameter>[,...]]) RETURNS <Returned Data Type> [characteristic ...] <Function Code Body> characteristic: COMMENT '<Comment>' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }
Migration considerations
For scalar functions, migration should be straightforward with respect to the function syntax. Rules in Aurora MySQL regarding functions are much more lenient than Oracle.
A function in Aurora MySQL may modify data and schema. Function determinism must be explicitly stated, unlike Oracle that infers it from the code. Additional properties can be stated for a function, but most are advisory only and have no functional impact.
The AS
keyword, which is mandatory in Oracle before the function’s code body, is not valid Aurora MySQL syntax and must be removed.
Examples
Create a scalar function to change the first character of a string to upper case.
CREATE FUNCTION UpperCaseFirstChar (String VARCHAR(20)) RETURNS VARCHAR(20) BEGIN RETURN CONCAT(UPPER(LEFT(String, 1)) , LOWER(SUBSTRING(String, 2, 19))); END SELECT UpperCaseFirstChar ('mIxEdCasE'); Mixedcase
Summary
The following table identifies similarities, differences, and key migration considerations.
Oracle | Aurora MySQL | Comment |
---|---|---|
Scalar UDF |
Scalar UDF |
Use |
Inline table-valued UDF |
N/A |
Use views and replace parameters with |
Multi-statement table-valued UDF |
N/A |
Use stored procedures to populate tables and read from the table directly. |
UDF determinism implicit |
Explicit declaration |
Use the |
UDF boundaries local only |
Can change data and schema |
UDF rules are more lenient, avoid unexpected changes from function invocation. |
For more information, see CREATE PROCEDURE and CREATE FUNCTION Statements