SQLエラーにもう悩まない！参照カーソル利用で非効率なプログラム内での文字列組み立てから卒業しよう

## ごあいさつ
こんにちは、「エイジ＠フジワーランド」です
フリーでパッケージシステムのOEM供給やってます、最近暇なので時給でSESやってます
年末年始でSESはお休みですが、フリーはいつでも休みでいつでも仕事です(深い意味は少しあり)
さて今回はOracle参照カーソルの話です

## プログラム内でSQL文字列を組み立てるのはもうやめよう
プログラム内でSQL文を文字列結合して組み立てるのは柔軟性が高いように思えますが、以下のような問題点があります
### 1. 実行時しかSQLエラーがわからない
- 実行するまでエラーがわからないのは効率悪いです、WHERE条件を変更したりSELECT項目を変更したりと文字列なら思い通りのSQLが状況に合わせて書けますが、すべてのパターンが正しいSQLかどうかはIDEはチェックしてくれないです
### 2. 保守性が悪い
- 例えばソート順を変えるだけの変更でもプログラムに埋め込まれているSQL文字列を変更しないといけないですね…変更したSQLはほんとにエラーなく実行できるでしょうか？再ビルドしたら予期せぬ変更もリリースされてしまうとかないですか？
### 3. パフォーマンスの低下
- SQLを文字列でDBに渡すとSQL解析と実行計画の作業時間がかかります…キャッシュに全く同じSQLがあれば少しは速くなりますが、運任せはシステム屋としてはあまりよくないです
### 4. SQLインジェクションのリスク
- AIに聞いたらこれも教えてくれました…まあそのとおりですね

これらの問題を解決するために、Orcleのストアドプロシージャ（以下、ストアド）で参照カーソルを活用する方法を提案します

## 参照カーソルとは
ストアドのパラメータをレコードセットとして返してもらうことです
プログラムではレコードセットとして使用できますが、前方のみ移動可能で更新はできません
参照しているときはセッション接続したままなので、必要に応じて非接続型で参照したほうがいいかもです（非接続型参照については別記事で公開予定です）

## この記事でわかること
本記事では、参照カーソルのストアドの作成例と、SQL*PLUS、VBA/VB6、C#（ODP.NET）、Javaで呼び出す具体例を紹介します

---

## ストアドプロシージャの例

次の例では、顧客テーブルから条件に応じてデータを取得するストアドを作成します

### 顧客テーブル定義例

```sql
CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_name VARCHAR2(100),
    city VARCHAR2(50),
    created_date DATE
);
```

```sql:サンプルデータ
INSERT INTO customers VALUES (1, '山田 太郎', '東京', TO_DATE('2023-01-15', 'YYYY-MM-DD'));
INSERT INTO customers VALUES (2, '佐藤 花子', '大阪', TO_DATE('2023-02-10', 'YYYY-MM-DD'));
INSERT INTO customers VALUES (3, '鈴木 一郎', '名古屋', TO_DATE('2023-03-05', 'YYYY-MM-DD'));
INSERT INTO customers VALUES (4, '高橋 真美', '東京', TO_DATE('2023-04-20', 'YYYY-MM-DD'));
INSERT INTO customers VALUES (5, '伊藤 健太', '京都', TO_DATE('2023-05-15', 'YYYY-MM-DD'));
INSERT INTO customers VALUES (6, '中村 美咲', '広島', TO_DATE('2023-06-30', 'YYYY-MM-DD'));
INSERT INTO customers VALUES (7, '小林 涼介', '大阪', TO_DATE('2023-07-25', 'YYYY-MM-DD'));
INSERT INTO customers VALUES (8, '加藤 優子', '東京', TO_DATE('2023-08-10', 'YYYY-MM-DD'));
INSERT INTO customers VALUES (9, '田中 翔太', '名古屋', TO_DATE('2023-09-15', 'YYYY-MM-DD'));
INSERT INTO customers VALUES (10, '松本 絵里', '福岡', TO_DATE('2023-10-05', 'YYYY-MM-DD'));
commit;
```

### ストアドプロシージャ

以下は参照カーソルを使用して顧客データを取得するストアドの例です

```sql
CREATE OR REPLACE PACKAGE PKG_TEST
IS
	TYPE TYP_CURSOR IS REF CURSOR ;
    PROCEDURE get_customers_by_city (
        p_city IN VARCHAR2,
        p_cursor OUT SYS_REFCURSOR
    );
END;
/
CREATE OR REPLACE PACKAGE BODY PKG_TEST
IS
    PROCEDURE get_customers_by_city (
        p_city IN VARCHAR2,
        p_cursor OUT SYS_REFCURSOR
    )
    IS
    BEGIN
        OPEN p_cursor FOR
            SELECT customer_id AS "顧客id",
                   SUBSTR(customer_name,1,10) AS "顧客名",
                   SUBSTR(city,1,5) AS "都市"
            FROM customers
            WHERE city = NVL(p_city, city)
        ;
    END;
END;
/
```

このストアドは、入力パラメータ`p_city`で指定された都市の顧客データを返します
`p_city`が`NULL`の場合、すべての都市のデータを返します

SQLはすでに構文チェックも終えてDBに登録されているので実行時にSQL構文エラーが発生することはありません…この安心感はとてもメンタルにいいですね


---
## SQL*Plusからの呼び出し例

SQL*Plusを使用して参照カーソルの結果を取得する例です
この例では `'大阪'`を指定してます
```sql
SET SERVEROUTPUT ON
VAR p_cursor REFCURSOR

BEGIN
    PKG_TEST.get_customers_by_city('大阪', :p_cursor);
END;
/

PRINT p_cursor
```

### 実行手順
1. SQL*Plusを起動し、データベースに接続します。
2. 上記のスクリプトを実行します。
3. `PRINT p_cursor`で結果が表示されます。

```sql:実行結果1
SQL> SET SERVEROUTPUT ON
SQL> VAR p_cursor REFCURSOR
SQL>
SQL> BEGIN
  2      PKG_TEST.get_customers_by_city('大阪', :p_cursor);
  3  END;
  4  /

PL/SQLプロシージャが正常に完了しました。

SQL>
SQL> PRINT p_cursor

    顧客id 顧客名               地域
---------- -------------------- ----------
         2 佐藤 花子            大阪
         7 小林 涼介            大阪

SQL>
```

`PKG_TEST.get_customers_by_city`の 第1パラメータに `null`を指定すると全件出力されます
```sql:実行結果2
SQL> SET SERVEROUTPUT ON
SQL> VAR p_cursor REFCURSOR
SQL>
SQL> BEGIN
  2      PKG_TEST.get_customers_by_city(NULL, :p_cursor);
  3  END;
  4  /

PL/SQLプロシージャが正常に完了しました。

SQL>
SQL> PRINT p_cursor

    顧客id 顧客名               地域
---------- -------------------- ----------
         1 山田 太郎            東京
         2 佐藤 花子            大阪
         3 鈴木 一郎            名古屋
         4 高橋 真美            東京
         5 伊藤 健太            京都
         6 中村 美咲            広島
         7 小林 涼介            大阪
         8 加藤 優子            東京
         9 田中 翔太            名古屋
        10 松本 絵里            福岡

10行が選択されました。

SQL>
```

---

## VBA/VB6からの呼び出し例

VBA/VB6でストアドを呼び出して参照カーソルの結果を取得する例です

```vb
Sub CallStoredProcedure()
    Dim conn As Object
    Dim cmd As Object
    Dim rs As Object

    ' 接続オブジェクトの作成
    Set conn = CreateObject("ADODB.Connection")
    conn.Open "Provider=OraOLEDB.Oracle;Data Source=YOUR_DB;User Id=YOUR_USER;Password=YOUR_PASSWORD;"

    ' コマンドオブジェクトの作成
    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    cmd.CommandText = "BEGIN PKG_TEST.get_customers_by_city(:p_city, :p_cursor); END;"
    cmd.CommandType = 4 ' adCmdText

    ' パラメータ設定
    cmd.Parameters.Append cmd.CreateParameter("p_city", 200, 1, 50, "大阪") ' adVarChar, adParamInput
    cmd.Parameters.Append cmd.CreateParameter("p_cursor", 201, 2) ' adVariant, adParamOutput

    ' ストアド呼び出し
    cmd.Execute

    ' 結果セット取得
    Set rs = cmd.Parameters("p_cursor").Value
    While Not rs.EOF
        Debug.Print rs.Fields("顧客id"), rs.Fields("顧客名"), rs.Fields("都市")
        rs.MoveNext
    Wend

    ' 終了処理
    rs.Close
    conn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set conn = Nothing
End Sub
```

---

## C#（ODP.NET）からの呼び出し例

C#を使用してストアドを呼び出す例です

```csharp
using System;
using Oracle.ManagedDataAccess.Client;

class Program
{
    static void Main()
    {
        string connectionString = "User Id=YOUR_USER;Password=YOUR_PASSWORD;Data Source=YOUR_DB";

        using (var conn = new OracleConnection(connectionString))
        {
            conn.Open();

            using (var cmd = new OracleCommand("PKG_TEST.get_customers_by_city", conn))
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;

                cmd.Parameters.Add("p_city", OracleDbType.Varchar2).Value = "大阪";
                cmd.Parameters.Add("p_cursor", OracleDbType.RefCursor).Direction = System.Data.ParameterDirection.Output;

                using (var reader = ((OracleRefCursor)cmd.Parameters["p_cursor"].Value).GetDataReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"{reader["顧客id"]}, {reader["顧客名"]}, {reader["都市"]}");
                    }
                }
            }
        }
    }
}
```

---

## Javaからの呼び出し例

Javaを使用してストアドを呼び出す例です

```java
import java.sql.*;

public class CallStoredProcedure {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@YOUR_DB";
        String user = "YOUR_USER";
        String password = "YOUR_PASSWORD";

        try (Connection conn = DriverManager.getConnection(url, user, password);
             CallableStatement cstmt = conn.prepareCall("{ call PKG_TEST.get_customers_by_city(?, ?) }");) {

            cstmt.setString(1, "大阪"); // p_city
            cstmt.registerOutParameter(2, OracleTypes.CURSOR); // p_cursor

            cstmt.execute();

            try (ResultSet rs = (ResultSet) cstmt.getObject(2)) {
                while (rs.next()) {
                    System.out.printf("%d, %s, %s\n",
                        rs.getInt("顧客id"),
                        rs.getString("顧客名"),
                        rs.getString("都市"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
```

---

## おわりに

まえまえからプログラム内での文字列結合によるSQL組み立ては美しくないな、と思っていたのですが、ストアドにSQLを置くことによってすっきりしました
書く時点で構文チェックがされてるので安心、他のプログラムへの再利用も簡単、たぶんパフォーマンスもいい…といい事ずくめです
なんせ、SQL組み立てのなくなったコードは眺めていて気持ちがいいですよ

よければこの記事を参考にして参照カーソルを積極的に活用してみてください
みなさんの作業効率が少しでもあがることを願います

最後まで読んできただきありがとうございました