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 ]
[ DETERMINISTIC ]
[ PARALLEL_ENABLE ]
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混合指定法
位置指定法と名前指定法を同時に使用して、関数にパラメータを渡します。この方法を採用する場合、位置指定法で渡されるパラメータは、名前指定法で渡されるパラメータよりも前に記述する必要があります。つまり、関数のパラメータ数に関係なく、名前指定法を使用するパラメータが1つある場合、その後のすべてのパラメータも名前指定法で指定する必要があります。
例：
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
関数の特性
DETERMINISTIC
DETERMINISTIC 機能は、関数が決定的であることを示します。つまり、同じ入力パラメータに対して常に同じ結果を返すことを意味します。これはクエリ性能の最適化において重要です。
PARALLEL_ENABLE
PARALLEL_ENABLE 機能は、その関数が並列クエリで安全に実行できることを示します。この機能を指定する場合、関数は以下の条件を満たしている必要があります：
- 関数は決定的でなければなりません (
DETERMINISTIC) - 関数はデータベースの状態を変更してはなりません。
- 関数はセッションの状態やグローバル変数に依存してはなりません。
注意
PARALLEL_ENABLE 機能を持つ関数は、並列実行環境では複数のスレッドから同時に呼び出される可能性があるため、関数はスレッドセーフである必要があります。
PARALLEL_ENABLE の使用例：
CREATE OR REPLACE FUNCTION sub_query_result(input VARCHAR)
RETURN VARCHAR
DETERMINISTIC
PARALLEL_ENABLE
IS
result tp_sys_para.para_value%type;
BEGIN
SELECT tp.para_value INTO result
FROM tp_sys_para tp
WHERE tp.para_name_e = input;
RETURN result;
END;
/
パラメータのデフォルト値
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
v_var VARCHAR(32);
BEGIN
v_var := demo_def_args('Roger', 30);
DBMS_OUTPUT.PUT_LINE(v_var);
v_var := demo_def_args('Allen', age => 40);
DBMS_OUTPUT.PUT_LINE(v_var);
v_var := demo_def_args('Tracy', gender => 'Female', age => 20);
DBMS_OUTPUT.PUT_LINE(v_var);
END;
/
Query OK, 0 rows affected
Roger, Male, 30 years old.
Allen, Male, 40 years old.
Tracy, Female, 20 years old.