CREATE FUNCTION

説明

CREATE FUNCTION ステートメントは、ストアド関数を作成するために使用します。

権限要件

CREATE PROCEDURE ステートメントを使用するには、CREATE FUNCTION 権限が必要です。

基本的な例：

ユーザーに CREATE ROUTINE 権限を付与すると、そのユーザーは FUNCTION を作成できるようになります。

-- ユーザーの作成と権限の付与
obclient> CREATE USER 'mingye'@'localhost' IDENTIFIED BY 'password';
obclient> GRANT CREATE ROUTINE ON mydb.* TO 'mingye'@'localhost';
obclient> GRANT EXECUTE ON mydb.* TO 'mingye'@'localhost';

-- 対象データベースへの切り替え
obclient> USE mydb;

-- 関数の作成
obclient> DELIMITER //
obclient> CREATE FUNCTION add_numbers(a INT, b INT)
    RETURNS INT
    DETERMINISTIC
    BEGIN
        RETURN a + b;
    END //
obclient> DELIMITER ;

-- 関数のテスト
obclient> SELECT add_numbers(5, 3);

デフォルトでは、PROCEDURE を作成すると、OceanBaseデータベースは自動的に ALTER ROUTINE および EXECUTE 権限をルーチン（ストアドプロシージャおよび関数）の作成者に付与します。DEFINER 句が存在する場合、必要な権限は user の値によって決まります。

DEFINER句を使用した例：

-- 管理者ユーザーの作成
obclient> CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin_password';
obclient> GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost';

-- 通常ユーザーの作成
obclient> CREATE USER 'user1'@'localhost' IDENTIFIED BY 'user_password';
obclient> GRANT SELECT, EXECUTE ON mydb.* TO 'user1'@'localhost';

-- DEFINERを使用して関数を作成
obclient> DELIMITER //
obclient> CREATE DEFINER='admin'@'localhost' FUNCTION multiply_numbers(a INT, b INT)
    RETURNS INT
    DETERMINISTIC
    BEGIN
        RETURN a * b;
    END //
obclient> DELIMITER ;

-- 通常ユーザーとして関数をテスト
obclient> SELECT multiply_numbers(4, 6);

構文

CREATE
    [DEFINER = user]
    FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

func_parameter:
    param_name type

type:
    Any valid MySQL data type

characteristic: {
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
}

routine_body:
    Valid SQL routine statement

デフォルトでは、ストアド関数はデフォルトのデータベースに関連付けられます。ストアド関数を特定のデータベースに関連付けるには、database_name.sp_name で名前を指定します。

ストアド関数を呼び出すには、式の中でその関数を参照します。この関数は式の評価時に値を返します。

DEFINER および SQL SECURITY 句は、ルーチン実行時にアクセス権限をチェックするために使用されるセキュリティコンテキストを指定します。

IF NOT EXISTS は、既に存在する同名のルーチンの作成を防ぎます。ストアド名が組み込みSQL関数の名前と同じ場合、定義時または後からの呼び出し時に名前の後に括弧を使用し、その間にスペースを入れない限り、構文エラーが発生します。そのため、既存のSQL関数の名前を使用してユーザー自身のストアドルーチンを作成することは避けてください。

括弧内の proc_parameter パラメータリストは常に必要です。パラメータがない場合は、空のパラメータリストを表すために空の括弧 () を使用します。ここでのパラメータ名は大文字小文字を区別しません。ストアド関数の各パラメータは IN パラメータです。

RETURNS 句はストアド関数にのみ使用され、関数の戻り値の型を指定します。関数本体には RETURN value ステートメントを含める必要があります。RETURN ステートメントが異なる型の値を返す場合、その値は適切な型へ強制的に変換されます。例えば、関数が RETURNS 句で ENUM または SET 値を指定しているにもかかわらず、RETURN ステートメントが整数を返す場合、関数から返される値は SET メンバーセットにおける対応する ENUM メンバーの文字列となります。

パラメータ型と関数の戻り値型は任意の有効なデータ型を宣言できます。CHARACTER SET が先行する場合は COLLATE 属性を使用できます。

routine_body は有効なSQLステートメントで構成されます。SQLステートメントは単純なステートメント（例：SELECT または INSERT）の場合もあれば、BEGIN と END で囲まれた複合ステートメントを使用する場合もあります。複合ステートメントには、宣言、ループ、その他の制御構造ステートメントを含めることができます。実際、ストアド関数では複合ステートメントを使用する傾向がありますが、本体が単一の RETURN ステートメントで構成されている場合は例外です。

ストアドルーチン内で USE ステートメントの使用は許可されていません。ルーチンを呼び出す際、USE database_name が暗黙的に実行され（ルーチン終了時に取り消される）、ルーチンの実行にデフォルトのデータベースが指定されます。ルーチンのデフォルトデータベース以外のデータベースオブジェクトを参照する必要がある場合は、データベース名を使用して限定できます。

厳密SQLモードでルーチンを定義した後、非厳密モードで呼び出した場合、厳密モードではルーチンのパラメータに値が代入されません。厳密SQLモードでルーチンに渡される式を割り当てる場合は、厳密モードでルーチンを呼び出す必要があります。

COMMENT 機能はストアドルーチンの説明に使用でき、SHOW CREATE PROCEDURE および SHOW CREATE FUNCTION ステートメントを使用してコメント情報を表示できます。

LANGUAGE 機能は、ルーチンを記述する言語を示します。SQLルーチンにのみ適用され、サーバーはこの機能を無視します。

ルーチンが常に同じ入力パラメータに対して同じ結果を返す場合、それは「決定的」と見なされます。そうでない場合は「非決定的」と見なされます。ルーチンの定義に DETERMINISTIC でも NOT DETERMINISTIC でも指定されていない場合、デフォルトは NOT DETERMINISTIC です。関数を決定的であると宣言するには、DETERMINISTIC を明示的に指定する必要があります。

ルーチンがデータを使用する特性に関する情報は以下のとおりです：

• CONTAINS SQL は、ルーチンにデータの読み書きを行うステートメントが含まれていないことを示します。これがデフォルト値です。例えば、SET@x=1 や DO RELEASE_LOCK('abc') は実行されますが、データを読み書きすることはありません。

• NO SQL は、ルーチンにSQLステートメントが含まれていないことを示します。

• READS SQL DATA は、ルーチンにデータの読み取りを行うステートメント（例：SELECT）が含まれていることを示しますが、データの書き込みを行うステートメントは含まれていません。

• MODIFIES SQL DATA は、ルーチンにデータの書き込みを行う可能性のあるステートメント（例：INSERT または DELETE）が含まれていることを示します。

SQL SECURITY は DEFINER または INVOKER となり、ルーチンが指定されたアカウント（このアカウントはルーチンに関連付けられたデータベースへのアクセス権限を持っている必要があります）の権限で実行されるか、呼び出し元の権限で実行されるかを指定します。デフォルト値は DEFINER です。ルーチンを呼び出すユーザーは、そのルーチンに対する EXECUTE 権限を持っている必要があります。

DEFINER 句は、ルーチンのアクセス権限をチェックする際に使用されるアカウントを指定します。DEFINER 句が存在する場合、user 値は 'user_name'@'host_name' として指定されたアカウント（例：'admin'@'oblocalhost'）または CURRENT_USER() 関数が取得するアカウントである必要があります。DEFINER 句を省略した場合、デフォルトの定義者は CREATE FUNCTION ステートメントを実行したユーザーです。SQL SECURITY DEFINER 機能を使用して定義されたストアドルーチンの本体内で、CURRENT_USER 関数を使用してルーチンの DEFINER 値を返すことができます。

サーバーは、ルーチンのパラメータ、DECLARE で作成されたローカルルーチン変数、または関数の戻り値のデータ型を以下のように処理します：

• データ型の不整合やオーバーフローがないかチェックします。変換やオーバーフローの問題は、厳密SQLモードでは警告またはエラーを引き起こす原因となります。

• 標量値のみを指定できます。例えば SET val=(SELECT 1, 2) ステートメントは無効です。

• 文字データ型について、宣言に CHARACTER SET が含まれる場合、指定された文字セットとそのデフォルトの照合順序が使用されます。COLLATE 属性を使用した場合は、デフォルトの照合順序ではなく、指定された照合順序が使用されます。

  CHARACTER SET と COLLATE が存在しない場合、ルーチン作成時に適用されたデータベースの文字セットと照合順序が使用されます。サーバーがデータベースの文字セットと照合順序を使用するのを避けるには、パラメータに対して CHARACTER SET と COLLATE 機能を明示的に指定してください。

  データベースのデフォルトの文字セットや照合順序を変更するには、ストアドルーチンを削除して再作成し、新しいデータベースのデフォルト値を適用する必要があります。

  character_set_database と collation_database システム変数を使用して、データベースの文字セットと照合順序を指定できます。

関数作成の詳細な例については、ストアド関数を参照してください。