🍒
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で呼び出す具体例を紹介します
ストアドプロシージャの例
次の例では、顧客テーブルから条件に応じてデータを取得するストアドを作成します
顧客テーブル定義例
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
実行手順
- SQL*Plusを起動し、データベースに接続します。
- 上記のスクリプトを実行します。
-
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組み立てのなくなったコードは眺めていて気持ちがいいですよ
よければこの記事を参考にして参照カーソルを積極的に活用してみてください
みなさんの作業効率が少しでもあがることを願います
最後まで読んできただきありがとうございました
Discussion