LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@EIJI_at_FujiwaaaLand(藤原 英司)

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

Last updated at Posted at 2024-12-29

ごあいさつ

こんにちは、「エイジ＠フジワーランド」です
フリーでパッケージシステムの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で呼び出す具体例を紹介します

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

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

顧客テーブル定義例

CREATE TABLE customers (
    customer_id NUMBER PRIMARY KEY,
    customer_name VARCHAR2(100),
    city VARCHAR2(50),
    created_date DATE
);
サンプルデータ
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;

ストアドプロシージャ

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

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を使用して参照カーソルの結果を取得する例です
この例では '大阪'を指定してます

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で結果が表示されます。
実行結果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を指定すると全件出力されます

実行結果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でストアドを呼び出して参照カーソルの結果を取得する例です

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#を使用してストアドを呼び出す例です

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を使用してストアドを呼び出す例です

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組み立てのなくなったコードは眺めていて気持ちがいいですよ

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

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

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?