Oracle Databaseのファンクション(関数)を極める！

Oracle Databaseのファンクション(関数)は、業務を効率化し、コードをシンプルでメンテナンスしやすくするための強力なツールです。本記事では、初心者がつまずきやすいポイントを丁寧に解説し、実務で即役立つ活用例を多数紹介します。

この記事のゴール：

ファンクションの基本を理解する
組み込み関数とユーザー定義関数の違いを把握する
実務で役立つ応用例とベストプラクティスを学ぶ

リンク

ファンクション(関数)とは？

ファンクション(関数)とは、特定の処理を行い、結果を返す再利用可能なコードの単位です。以下の特徴があります：

入力(引数) を受け取り、計算や操作を実行
結果(戻り値) を返す
SQL文やPL/SQL内で簡単に呼び出せる

例えば、文字列の長さを計算する場合、Oracleの組み込み関数 LENGTH を使えば次のように簡潔に書けます：

SELECT LENGTH('Oracle Database') AS result FROM dual;
-- 出力: 15

1. 組み込み関数：初心者でも簡単に使える標準機能

Oracleの組み込み関数は大きく以下のカテゴリに分けられます。

カテゴリ	主な関数例	用途
文字列操作	`LENGTH`, `SUBSTR`, `UPPER`	文字列の長さや部分抽出、大文字変換
数値操作	`ROUND`, `MOD`, `ABS`	四捨五入や絶対値計算
日付操作	`SYSDATE`, `ADD_MONTHS`	現在の日付や日付計算
変換関数	`TO_CHAR`, `TO_DATE`	データ型の変換

以下に、主要な組み込み関数の具体例を示します。

文字列操作の例

LENGTH：文字列の長さを取得

SELECT LENGTH('Oracle Database') AS length_result FROM dual;
-- 出力: 15

SUBSTR：部分文字列を抽出

SELECT SUBSTR('Hello, Oracle!', 8, 6) AS substring_result FROM dual;
-- 出力: Oracle

UPPER：文字列を大文字に変換

SELECT UPPER('hello world') AS upper_result FROM dual;
-- 出力: HELLO WORLD

日付操作の例

SYSDATE：現在の日付と時刻

SELECT SYSDATE AS current_date FROM dual;
-- 出力: 現在の日付と時刻

MONTHS_BETWEEN：月数の差を計算

SELECT MONTHS_BETWEEN(TO_DATE('2024-12-31', 'YYYY-MM-DD'), SYSDATE) AS months_diff FROM dual;
-- 出力: 現在の日付との月数差

応用例：指定日までの日数を計算

SELECT TRUNC(TO_DATE('2024-12-31', 'YYYY-MM-DD') - SYSDATE) AS days_left FROM dual;
-- 出力: 残りの日数

2. ユーザー定義関数：実務で役立つカスタムロジック

組み込み関数だけでは対応できない場合、PL/SQLを使って独自のユーザー定義関数を作成します。

基本的なファンクションの作成

以下は商品の税込み価格を計算する関数の例です。

ファンクションの定義

CREATE OR REPLACE FUNCTION calc_taxed_price(
    price NUMBER,           -- 商品価格
    tax_rate NUMBER DEFAULT 0.1 -- 消費税率 (デフォルト10%)
) RETURN NUMBER IS
BEGIN
    RETURN price * (1 + tax_rate);
END;
/

使用例

SELECT calc_taxed_price(1000) AS taxed_price FROM dual;
-- 出力: 1100

応用例：税率を変更

SELECT calc_taxed_price(1000, 0.08) AS taxed_price FROM dual;
-- 出力: 1080

実務での応用例

割引価格を計算する関数

CREATE OR REPLACE FUNCTION calc_discounted_price(
    price NUMBER,
    discount_rate NUMBER DEFAULT 0.2 -- 割引率 (デフォルト20%)
) RETURN NUMBER IS
BEGIN
    RETURN price * (1 - discount_rate);
END;
/

条件付き価格計算

CREATE OR REPLACE FUNCTION calc_final_price(
    price NUMBER,
    discount_rate NUMBER DEFAULT 0.2,
    tax_rate NUMBER DEFAULT 0.1
) RETURN NUMBER IS
BEGIN
    RETURN (price * (1 - discount_rate)) * (1 + tax_rate);
END;
/

実行例

SELECT calc_final_price(1000, 0.15, 0.08) AS final_price FROM dual;
-- 出力: 918

3. 注意点とベストプラクティス

パフォーマンスに注意

大量データのSQLに注意: SQL内でファンクションを多用すると、パフォーマンスが低下します。
インデックスの活用: 関数を使う列にインデックスが効かない場合があります。FUNCTION-BASED INDEX を検討してください。

セキュリティの確保

権限管理: 必要最小限の権限で実行されるように設計しましょう。
入力データの検証: SQLインジェクションを防ぐため、入力データのチェックを忘れずに。

エラーハンドリング

適切な例外処理を実装することで、意図しないエラーを防ぎます。

CREATE OR REPLACE FUNCTION safe_division(
    numerator NUMBER,
    denominator NUMBER
) RETURN NUMBER IS
BEGIN
    IF denominator = 0 THEN
        RETURN NULL; -- ゼロ除算を防止
    ELSE
        RETURN numerator / denominator;
    END IF;
END;
/

4. 実務でのファンクション活用アイデア

監査ログの記録
ユーザー操作を記録するファンクションを作成し、自動的にログテーブルに挿入します。
動的SQLを組み込んだ処理
動的にSQLを生成して実行する柔軟なロジックを実装します。
複数データベースの統合
データベースリンクを活用して、リモートデータの取得を行うファンクションを構築します。

まとめ

Oracle Databaseのファンクションは、データ操作を効率化し、ビジネスロジックを簡潔に表現するための必須スキルです。

組み込み関数を活用して、基本操作を効率化する
ユーザー定義関数を作成して、独自のロジックを実現する
パフォーマンスやセキュリティに配慮しながら、実務に役立てる

実際にファンクションを作成して活用することで、データベース操作が大幅に改善されます。次のステップとして、自分の業務に合ったファンクションを作成し、活用してみてください！

[参考]
Oracle Database SQL言語リファレンス 19c