# 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 AWS DMS.
| Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
| --- | --- | --- | --- |
| ![\[Three star feature compatibility\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-mysql-migration-playbook/images/pb-compatibility-3.png) | ![\[Four star automation level\]](http://docs.aws.amazon.com/dms/latest/oracle-to-aurora-mysql-migration-playbook/images/pb-automation-4.png) | [Stored Procedures](chap-oracle-aurora-mysql.tools.actioncode.md#chap-oracle-aurora-mysql.tools.actioncode.storedprocedures) | 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`, and `START 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](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-FUNCTION.html#GUID-156AEDAC-ADD0-4E46-AA56-6D1F7CA63306) in the *Oracle documentation*.
## 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 as `SELECT` but not statements that modify data such as `INSERT`, `DELETE`, or `UPDATE`.
+ `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 ([[,...]])
RETURNS [characteristic ...]
characteristic:
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 `CREATE FUNCTION` with similar syntax, remove the `AS` keyword. |
| Inline table-valued UDF | N/A | Use views and replace parameters with `WHERE` filter predicates. |
| 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 `DETERMINISTIC` characteristic explicitly to denote a deterministic function, which enables engine optimizations. |
| 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](https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html) in the *MySQL documentation*.