Create a function

ODC supports visualized function creation. This topic describes how to create a function with ODC.

Overview

A function is a subprogram defined in a database. You can call functions by using built-in SQL statements. If the built-in functions cannot meet your business requirements, OceanBase Developer Center (ODC) allows you to create functions. User-defined functions not only help you perform some calculations and special operations, but also reduce coding redundancy to improve the program readability.

A function is a procedural object that is similar to a stored procedure in a database. Like a stored procedure, a function is a code snippet of SQL statements and procedural statements, and can be called by applications and other SQL statements.

Differences between a user-defined function and a stored procedure:

• A function returns only one result and is suitable for data processing that generates one result. A stored procedure may return zero or multiple results and is suitable for batch insertion and batch update.

• You can call a function by using a SELECT statement. You can call a stored procedure by using a CALL statement.

As shown in the preceding figure, you can create a function in the following six steps:

1. Specify the function name.

2. Specify the data type of the return value.

3. Specify parameters.

4. Verify the parameters of the new function.

5. Edit the function.

6. Complete the function creation.

Procedure

In the following example, a function named function_emp is created in the ODC console to obtain the employee name from an employee table based on the employee ID. The function_emp function contains an INT type id parameter. Procedure:

Step 1: Specify the function name.

Log in to the ODC console and click the name of a connection to go to the corresponding connection management page. You can click Function in the left-side navigation pane to get a list of functions. To create a function, click + in the upper-right corner of the function list or click Create in the top navigation bar.

Step 2: Specify the data type of the return value.

Step 3: Specify parameters.

1. Parameters specify the information passed to a function when the function is called.

   • In Oracle compatible mode, specify the following fields: Name, Mode, Type, and Default Value.

   • In MySQL compatible mode, specify the following fields: Name, Type, and Length.

2. You can specify parameters by using one of the following three methods.

   Method	Description
   Use the quick access toolbar	In the quick access toolbar, you can add, delete, and move up and down parameters.
   Click the row number	- You can click a row number to select a row and display the quick access toolbar that allows you to delete the row or move the row up or down. - You can click a row number to select the row, and then drag the row to adjust its order.
   Right-click	Right-click the mouse to select the entire row, copy row or move down the row.

3. You need to specify the mode for the parameters.

   The parameter mode setting is unavailable in MySQL compatible mode. The Oracle compatible mode supports the following parameter modes: IN, OUT, and INOUT.

   Parameter	Type
   IN	Indicates an input parameter. When a function is called, the input parameter is passed to the function and used in the execution of the function.
   OUT	Indicates an output parameter. When a function is called, the value of the output parameter is ignored and an empty value is passed to the function. The output parameter in the function body can be modified. The modified result is returned and passed to the argument that the output parameter represents.
   INOUT	Indicates an inout parameter. An inout parameter is both an input parameter and an output parameter.

   Note

   In Oracle compatible mode, both functions and stored procedures support IN, OUT, and INOUT modes. In MySQL compatible mode, functions support only IN mode, and stored procedures support IN, OUT, and INOUT modes.

Step 4: Verify the parameters of the new function.

Click OK to go to the Create Function page.

Step 5: Edit the function.

Edit the function statement on the Create Function page. You can edit the SQL statements of the created function on the Create Function page. Syntax:

CREATE FUNCTION < function name > ([ <parameter 1> <type 1> [ , <parameter 2> <type 2>] ] …)
RETURNS < type >
< function body >

Parameters:

Example:

CREATE FUNCTION `function_emp` ( `id` int(45)) RETURNS VARCHAR(300)

-- The start of the function body.
BEGIN

-- Declare a variable.
DECLARE
  a VARCHAR(300);

-- Assign a value to the variable.
SELECT
  name INTO a
FROM
  employee
WHERE
  emp_no = id;

-- The return value.
RETURN a;

-- The end of the function body.
END

Step 6: Complete the function creation.

Click Create in the upper-right corner to create the function. After a user-defined function is created, you can use the SELECT statement to call it, just like how you call a built-in function.

Syntax:

SELECT <function name> ([<parameter> [,...]])

Example:

SELECT function_emp(2);

• MySQL compatible mode

  

• Oracle compatible mode

  

  Notice

  • In Oracle compatible mode, if a user-defined function contains an OUT parameter, you must call the function by using the `CALL` statement.
  • A yellow icon in the function list indicates an error or alert.

References

• Create a table
• Create a view
• Create a stored procedure
• Create a sequence
• Create a program package
• Create a trigger
• Create a type
• Create a synonym