Create a function

Step 1: Enter a function name

In OceanBase Developer Center (ODC), click a connection name to open the connection. In the navigation pane on the left, click the Functions tab to view the list of functions. To create a function, click + in the upper-right corner of the function list, or click New in the top navigation bar of ODC.

Step 2: Select a data type for the return value

For more information about data types, see OceanBase Database Reference Guide.

Step 3: Add parameters

1. Parameters are the information passed to a function when it is called. You need to specify the following parameter information:

   • In Oracle mode, specify the Name, Mode, Type, and Default Value for each parameter.

   • In MySQL mode, specify the Name, Type, and Length for each parameter.

2. The following three operations are available for adding a function:

   Feature	Description
   Pop-up guide menu	Use the pop-up guide menu to add, delete, or move parameters up or down.
   Click the row number	Click a row number to select the entire row. A context menu appears with options to delete or move the row. Click and select a row number to drag the entire row to a new position.
   Right-click operations	Drag to select an entire row. Then, right-click to copy the row or move it down. Select a cell and right-click to copy it.

3. The Mode that you specify under Parameters defines the parameter type.

   MySQL mode does not support setting parameter modes. Oracle mode supports three parameter modes: IN (input), OUT (output), and INOUT (input/output).

   Parameter	Type
   IN	An input parameter. When the function is called, the value of the parameter is passed into the function for use during execution.
   OUT	An output parameter. When the function is called, its initial value is ignored. The function can modify the parameter, and the new value is returned to the caller.
   INOUT	An input/output parameter. It has the features of both an input parameter and an output parameter.

   Note

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

Step 4: Confirm the function creation

Click OK. The SQL creation confirmation page appears.

Step 5: Edit the function statement

On the SQL creation confirmation page, edit the function statement.

The toolbar on the editing page provides the following features:

On the SQL creation confirmation page, you can edit the function statement. The syntax is as follows:

CREATE FUNCTION <function_name> ([ <parameter1> <type1> [ , <parameter2> <type2>] ] …) 
RETURNS <type> 
<function_body>

The following table describes the parameters:

Example:

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

-- Function body begins
BEGIN

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

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

-- Return value
RETURN a;

-- Function body ends

END

Step 6: Finish creating the function

SELECT <function_name> ([<parameter> [,...]])

Example:

SELECT function_emp(2);

• MySQL mode

  

• Oracle mode

  

  Important

  • In Oracle mode, if a function includes an `OUT` parameter, you must call it using the CALL statement.

  • A yellow icon in the function list indicates an error or a warning.