A PL function is a subprogram that consists of one or more SQL statements and 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
Passing parameters
You can pass parameters to a function by using any of the following methods:
- Positional notation.
You can pass parameters in the order in which they are defined. 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 names and values. You do not need to pass parameters in the order in which they are defined. 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 use both positional and named notations to pass parameters to a function. 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 the following parameters must 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 values
You can use the DEFAULT keyword to specify default values for input parameters in the CREATE OR REPLACE FUNCTION statement. Here is an example:
CREATE OR REPLACE FUNCTION demo_def_args(
name VARCHAR2,
age INTEGER,
-- If you do not specify a value for gender, 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 actual values for parameters with default values when you call the function, the function uses the default values. However, if you specify actual values for default parameters when you call the function, the function uses the actual values. When you create a function, you can specify default values only for input parameters, not 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.