> For AI agents: the complete documentation index is available at /resource/ucidm/docs/on-premise/llms.txt, the full documentation bundle is available at /resource/ucidm/docs/on-premise/llms-full.txt.

# ストアドプロシージャ／ファンクション

## 手続き型 SQL とは \{#procedural-sql}

ストアドプロシージャは、データベースサーバー上に名前付きオブジェクトとして永続化され、クライアントからの呼び出しによってサーバープロセス内で実行される、手続き型ロジックと SQL 文の集合です。SQL 文と制御構文 (分岐・ループ・例外処理) を組み合わせて記述します。

ストアドプロシージャは [SQL/PSM (Persistent Stored Modules)](https://en.wikipedia.org/wiki/SQL/PSM) として標準化されています。しかし、歴史的経緯により、Sybase SQL Server (現 SAP Adaptive Server Enterprise) が最初にストアドプロシージャを実装し、その後、Oracle がサーバー側に手続きロジックを置くという実務上の慣行を PL/SQL という独自言語で広めました。SQL/PSM が標準化されるよりも前にそれぞれのデータベース製品が独自実装を進めた結果、多くのデータベースが SQL/PSM に準拠していないという現状があります。そのため、ここではストアドプロシージャ／ファンクションなどの総称を **「手続き型 SQL」** と呼びます。

それぞれのデータベース製品は次の手続き型言語を提供しています。

- Oracle => [PL/SQL](https://www.oracle.com/jp/database/technologies/appdev/plsql.html)
- SQL Server / Sybase => T-SQL (Transact-SQL)
- PostgreSQL => [PL/pgSQL](https://www.postgresql.jp/document/current/html/plpgsql.html)
- MySQL / MariaDB => [ストアドルーチン](https://dev.mysql.com/doc/refman/8.0/ja/stored-routines.html)
- IBM Db2 => SQL PL

これらの手続き型言語を使い、文と式を次の機能で提供します。

- **ストアドプロシージャ (stored procedure)**
  - CALL / EXECUTE で明示的に呼び出すルーチン
  - 値を返さない (または OUT パラメータで返す) のが基本
- **ストアドファンクション / ユーザー定義関数 (stored function, UDF)**
  - 値を返し、SQL 式の中で使えるルーチン

SQL/PSM 標準ではこれらの2つをまとめて SQL-invoked routine と呼びます。

## UCIDM が呼び出すオブジェクト \{#ucidm-objects}

[RDBMS 外部連携](/resource/ucidm/docs/on-premise/administration/admin-ui/destination/rdbms.md#rdbms-destination-module) の反映方式に storedProcedure を選ぶと、UCIDM は決められた名前の6つのオブジェクトを呼び出してデータを反映します。この6つを連携先のスキーマにあわせて実装するのはシステム管理者の作業です。

| 管理画面の設定項目     | オブジェクト名              | 種別      | 呼び出す場面                |
| :------------ | :------------------- | :------ | :-------------------- |
| ユーザー反映プロシージャ  | `ucidm_apply_user`   | プロシージャ  | ユーザーの追加／更新／パスワード更新／削除 |
| ユーザー取得ファンクション | `ucidm_get_user`     | ファンクション | ユーザーの存在確認と変更前の値の取得    |
| グループ反映プロシージャ  | `ucidm_apply_group`  | プロシージャ  | グループの追加／更新／削除         |
| グループ取得ファンクション | `ucidm_get_group`    | ファンクション | グループの存在確認と変更前の値の取得    |
| メンバー反映プロシージャ  | `ucidm_apply_member` | プロシージャ  | グループメンバーの追加／削除        |
| メンバー取得ファンクション | `ucidm_get_member`   | ファンクション | グループメンバーの存在確認         |

オブジェクト名は UCIDM 側で固定されています。登録する CREATE 文にこの名前が含まれていないと、外部連携設定の保存時にエラーになります。

6つすべてに定義が必要です。ユーザーだけを連携する場合でも、グループとメンバーの4つを空欄にしたままでは設定を保存できません。[実施しない操作の設定](/resource/ucidm/docs/on-premise/administration/admin-ui/destination/rdbms.md#skip-operations) で操作を無効にしても、定義そのものは求められます。使わないオブジェクトには、何もしないプロシージャと NULL を返すファンクションを登録しておいてください。

### 引数と戻り値の型 \{#signature}

呼び出しの形はどの製品でも同じです。反映プロシージャは JSON を表す値1つを引数に取り、値を返しません。取得ファンクションは JSON を表す値1つを引数に取り、JSON オブジェクトを1つ返します。パッケージやオーバーロードは使えず、引数はちょうど1つです。

UCIDM が渡すのは JSON の文字列ですが、宣言する型は製品ごとに異なります。

| データベース製品        | 引数の型       | 取得ファンクションの戻り値の型 |
| :-------------- | :--------- | :-------------- |
| Oracle          | `VARCHAR2` | `VARCHAR2`      |
| PostgreSQL      | `json`     | `json`          |
| MySQL / MariaDB | `JSON`     | `JSON`          |

PostgreSQL では特に注意してください。UCIDM が発行する DROP 文が引数の型で修飾されるため、`json` 以外の型で作成すると定義を変更したときに古いオブジェクトが残ります。

### オブジェクトのインストール \{#install}

管理画面の各項目に登録するのは CREATE 文そのものです。UCIDM は外部連携設定から連携先を組み立てるときに、6つのオブジェクトを DROP してから CREATE します。定義が前回インストールしたものと同じであれば、そのオブジェクトの再作成をスキップします。

このスキップの判定は、UCIDM のプロセス内に保持している定義のハッシュとの比較です。データベース側でオブジェクトを直接書き換えても、管理画面の定義が同じであれば UCIDM は再作成しません。逆に UCIDM を再起動すると、定義が同じでも一度は再作成します。

インストールはドライランモードでも実行されます。連携履歴の差分表示に取得ファンクションが要るためです。ドライランモードが抑止するのは反映プロシージャの呼び出しだけで、6つのオブジェクトの DROP と CREATE は行われます。

連携先に同じ名前のオブジェクトがすでにあると、UCIDM がそれを削除します。既存のオブジェクトと名前が衝突しないことを確認してください。

### 接続ユーザーに要る権限 \{#privileges}

UCIDM は、外部連携設定に指定した接続ユーザー1つで、オブジェクトのインストールとデータの反映の両方を実行します。このユーザーには次の3つの権限が要ります。

- **作成**: 6つのオブジェクトを作成する権限
- **削除**: インストールのたびに実行する DROP のための権限
- **呼び出し**: 反映プロシージャを呼び出し、取得ファンクションを実行する権限

権限が足りないと、外部連携設定の保存時のインストールが失敗します。ドライランモードでもインストールは実行されるため、作成と削除の権限は連携を試す前から要ります。

これに加えて、プロシージャとファンクションが読み書きするテーブルへの権限も要ります。テーブルへのアクセスをオブジェクトの所有者の権限で検査する製品と、呼び出したユーザーの権限で検査する製品がありますが、UCIDM では作成するのも呼び出すのも同じ接続ユーザーなので、どちらの製品でも接続ユーザーにテーブルへの権限を与えることになります。ただし、権限をロール経由で与えたときに有効かどうかは製品によって異なります。

権限の与え方と `GRANT` 文の例は、データベース製品ごとのページにまとめています。

- [Oracle の接続ユーザーの権限](/resource/ucidm/docs/on-premise/tech/storedProcedure/oracle.md#privileges)
- [PostgreSQL の接続ユーザーの権限](/resource/ucidm/docs/on-premise/tech/storedProcedure/postgresql.md#privileges)
- [MySQL / MariaDB の接続ユーザーの権限](/resource/ucidm/docs/on-premise/tech/storedProcedure/mysql.md#privileges)

### 反映プロシージャが受け取る JSON \{#apply-payload}

ユーザーとグループの反映プロシージャは、次の形の JSON を受け取ります。

```json
{
  "opCode": "I",
  "primaryID": "u123",
  "enabled": true,
  "attrs": { "cn": "山田太郎", "mail": "taro@example.com", "empNo": "1042" }
}
```

- `opCode`: 実行する操作の種別です
- `primaryID`: [PrimaryID マッピング](/resource/ucidm/docs/on-premise/administration/admin-ui/primaryid-mapping.md) の結果です。連携先でエントリを一意に識別する値になります
- `enabled`: ユーザーの有効無効です。真偽値で、解決できたときだけ含まれます。グループには付きません
- `attrs`: [属性マッピング](/resource/ucidm/docs/on-premise/administration/admin-ui/mappings.md) の結果です。キーは「連携先の属性名」になります

`opCode` によって、含まれるキーと期待される処理が変わります。

| opCode | 操作      | enabled   | attrs         |
| :----- | :------ | :-------- | :------------ |
| `I`    | 追加      | 解決できたときのみ | 属性マッピングの結果    |
| `U`    | 更新      | 解決できたときのみ | 今回の操作で届いた属性のみ |
| `P`    | パスワード更新 | 付かない      | パスワードマッピングの結果 |
| `D`    | 削除      | 付かない      | 付かない          |

`attrs` の扱いには3つの制約があります。

まず、値はすべて文字列です。数値、日付、真偽値のカラムに格納するには、プロシージャ側で型変換します。次に、多値属性は先頭の1つだけが渡ります。最後に、`U` では今回の操作で届いた属性だけが `attrs` に入ります。渡されなかった属性を上書きして消さないように書く必要があります。

`P` の `attrs` のキーは、パスワードマッピングの設定で決まります。パスワードマッピングを設定していない場合は `password` というキーで平文が渡ります。

メンバー反映プロシージャが受け取る JSON は形が異なり、`opCode` も `I` と `D` だけです。

```json
{ "opCode": "I", "groupPrimaryID": "g1", "memberPrimaryID": "u123" }
```

### 取得ファンクションが返す JSON \{#get-payload}

取得ファンクションが受け取る JSON は、対象を識別する値だけを持ちます。ユーザーとグループは `{"primaryID": "u123"}`、メンバーは `{"groupPrimaryID": "g1", "memberPrimaryID": "u123"}` です。

戻り値は JSON オブジェクトの文字列、または SQL の NULL です。**対象が存在しないときは NULL を返してください**。この戻り値には2つの用途があります。

1つ目は存在確認です。UCIDM は取得ファンクションの結果から `opCode` を決めます。

| 場面              | 取得ファンクションの結果 | UCIDM の動作          |
| :-------------- | :----------- | :----------------- |
| ユーザー／グループの追加／更新 | NULL         | `opCode` を `I` にする |
| ユーザー／グループの追加／更新 | JSON         | `opCode` を `U` にする |
| ユーザー／グループの削除    | NULL         | 何もせずスキップする         |
| パスワード更新         | NULL         | エラーにする             |
| メンバーの追加／削除      | グループが NULL   | エラーにする             |
| メンバーの追加         | メンバーが JSON   | 登録済みとしてスキップする      |
| メンバーの削除         | メンバーが NULL   | 何もせずスキップする         |

メンバーの追加と削除では、UCIDM はメンバー取得ファンクションより先にグループ取得ファンクションを呼びます。グループが見つからなければエラーになり、メンバー反映プロシージャは呼ばれません。

2つ目は連携履歴の「変更前の値」の表示です。返した JSON のキーが属性マッピングの「連携先の属性名」と一致したときに、そのキーの値が変更前の値として履歴に表示されます。キー名が一致しなければ表示されないだけで、連携そのものは成功します。メンバー取得ファンクションは存在確認にしか使われないため、JSON の中身は問いません。

### エラーの扱い \{#error}

プロシージャやファンクションが送出した例外は、そのまま UCIDM の連携エラーになります。連携履歴には失敗として記録され、データベースが返したメッセージも残ります。連携先の都合で反映できないデータを検出したい場合は、プロシージャ内で明示的に例外を送出してください。

## データベース製品ごとの実装 \{#implementation}

- [Oracle PL/SQL の開発](/resource/ucidm/docs/on-premise/tech/storedProcedure/oracle.md)
- [PostgreSQL PL/pgSQL の開発](/resource/ucidm/docs/on-premise/tech/storedProcedure/postgresql.md)
- [MySQL / MariaDB ストアドルーチンの開発](/resource/ucidm/docs/on-premise/tech/storedProcedure/mysql.md)