A PL function is a subprogram composed of one or more SQL statements that can be executed repeatedly. A function can return only one variable and must contain a valid RETURN statement.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL mode.
The basic syntax for creating a function is as follows:
CREATE [OR REPLACE] FUNCTION function_name
[ (argment [ { IN | IN OUT }] Type,
argment [ { IN | OUT | IN OUT } ] Type ]
[ AUTHID DEFINER | CURRENT_USER ]
RETURN return_type
{ IS | AS }
delarification_block
BEGIN
FUNCTION_body
EXCEPTION
exception_handler
END;
Here is an example:
obclient> CREATE TABLE employees(
empno NUMBER(4,0),
empname VARCHAR(10),
job VARCHAR(10),
deptno NUMBER(2,0),
salary NUMERIC
);
Query OK, 0 rows affected
obclient> INSERT INTO employees VALUES (200,'Jennifer','AD_ASST',1,15000),
(202,'Pat','MK_REP',3,12000),(119,'Karen','PU_CLERK', 4,10000),(201,'Michael','MK_MAN',3,9000);
Query OK, 4 rows affected )
Records: 4 Duplicates: 0 Warnings: 0
obclient> CREATE OR REPLACE FUNCTION get_salary_by_dept(
v_in_dept_id NUMBER,
v_out_emp_count OUT NUMBER)
RETURN NUMBER
IS
v_sum NUMBER;
BEGIN
SELECT SUM(salary), count(*) INTO v_sum, v_out_emp_count
FROM employees WHERE deptno=v_in_dept_id;
RETURN v_sum;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Dept id '||v_in_dept_id||' not found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERR: '||SQLCODE||': '||SQLERRM);
END get_salary_by_dept;
/
Query OK, 0 rows affected
Parameter passing
You can pass parameters to a function by using any of the following three methods:
- Positional notation.
You can pass parameters in the order they are defined in the function definition. The syntax is argument_value1[,argument_value2 ...]
Here is an example:
obclient> DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum :=get_salary_by_dept(3, v_num);
DBMS_OUTPUT.PUT_LINE('Total salary: '||v_sum);
DBMS_OUTPUT.PUT_LINE('Total amount of person: '||v_num);
END;
/
Query OK, 0 rows affected
Total salary: 21000
Total amount of person: 2
- Named notation.
You can explicitly specify the parameter name and the value to be passed. You do not have to pass parameters in the order they are defined in the function definition. The syntax is argument1 => parameter1 [, argument2 => parameter2[, ...]]
Here is an example:
obclient> DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum :=get_salary_by_dept(v_out_emp_count => v_num, v_in_dept_id => 3 );
DBMS_OUTPUT.PUT_LINE('Total salary: '||v_sum);
DBMS_OUTPUT.PUT_LINE('Total amount of person: '||v_num);
END;
/
Query OK, 0 rows affected
Total salary: 21000
Total amount of person: 2
- Mixed notation.
You can pass parameters to a function by using both positional and named notations. When you use mixed notation, the parameters passed by using positional notation must be placed before the parameters passed by using named notation. In other words, if any parameter is passed by using named notation, all subsequent parameters must also be passed by using named notation.
Here is an example:
obclient> DECLARE
v_num NUMBER;
v_sum NUMBER;
BEGIN
v_sum :=get_salary_by_dept(3, v_out_emp_count => v_num );
DBMS_OUTPUT.PUT_LINE('Total salary: '||v_sum);
DBMS_OUTPUT.PUT_LINE('Total amount of person: '||v_num);
END;
/
Query OK, 0 rows affected
Total salary: 21000
Total amount of person: 2
Default parameter values
You can use the DEFAULT keyword in the CREATE OR REPLACE FUNCTION statement to specify a default value for an input parameter. Here is an example:
CREATE OR REPLACE FUNCTION demo_def_args(
name VARCHAR2,
age INTEGER,
-- If you do not specify the gender parameter, the default value is 'Male'.
gender VARCHAR2 DEFAULT 'Male')
RETURN VARCHAR2
AS
V_var VARCHAR2(32);
BEGIN
V_var := name||', '||gender||', '||TO_CHAR(age)||' years old.';
RETURN v_var;
END;
```
After a function with default values is created, if you do not specify a value for a parameter with a default value when you call the function, the function uses the default value. However, if you specify a value for a default parameter, the function uses the specified value. You can only specify default values for input parameters when you create a function. You cannot specify default values for input/output parameters.
```javascript
obclient> DECLARE
Var VARCHAR(32);
BEGIN
Var := demo_def_args('Roger', 30);
DBMS_OUTPUT.PUT_LINE(var);
Var := demo_def_args('Allen', age => 40);
DBMS_OUTPUT.PUT_LINE(var);
Var := demo_def_args('Tracy', gender => 'Female', age => 20);
DBMS_OUTPUT.PUT_LINE(var);
END;
/
Query OK, 0 rows affected
Roger, Male, 30 years old.
Allen, Male, 40 years old.
Tracy, Female, 20 years old.