PL関数とは、1つまたは複数のSQL文で構成されるサブルーチンであり、繰り返し実行可能です。関数は1つの変数しか返すことができず、有効なRETURNステートメントを含む必要があります。
適用対象
この内容はOceanBaseデータベースEnterprise Editionにのみ適用されます。OceanBaseデータベースCommunity EditionではMySQLモードのみが提供されています。
関数を作成するための基本構文は以下のとおりです：
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;
例：
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
パラメータの渡し方
関数にパラメータを渡すには、以下の3つの方法があります：
位置表記法。
パラメータの定義順に従って個別に渡します。構文は
argument_value1[,argument_value2 ...]例：
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名前表記法。
パラメータ名と渡す値を明示的に指定します。パラメータの定義順に従う必要はありません。構文は
argument1 => parameter1 [, argument2 => parameter2[, ...]]例：
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混合表記法。
位置表記法と名前表記法を同時に使用して関数にパラメータを渡します。このパラメータ渡し方法を採用する場合、位置表記法で渡されるパラメータは名前表記法で渡されるパラメータよりも前に配置する必要があります。つまり、関数にいくつのパラメータがあっても、そのうちのいずれか一つのパラメータが名前表記法を使用した場合、その後のすべてのパラメータも名前表記法を使用しなければなりません。
例：
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
パラメータのデフォルト値
CREATE OR REPLACE FUNCTION ステートメントで関数パラメータを宣言する際に、DEFAULT キーワードを使用して入力パラメータにデフォルト値を指定できます。例：
CREATE OR REPLACE FUNCTION demo_def_args(
name VARCHAR2,
age INTEGER,
-- genderに値が渡されない場合、デフォルトは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;
デフォルト値を持つ関数が作成された後、関数呼び出し時にデフォルト値を持つパラメータに実際のパラメータ値が提供されていない場合、関数はそのパラメータのデフォルト値を使用します。ただし、呼び出し元がデフォルトパラメータに実際のパラメータを提供した場合、関数は実際のパラメータ値を使用します。関数を作成する際には、入力パラメータにのみデフォルト値を設定でき、入力/出力パラメータにデフォルト値を設定することはできません。
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.