ストアドプロシージャでできないと思うのはOracleのせい
ストアドプロシージャでできない。できるわけがない。というのは、Oracleのストアドプロシージャ(ファンクション)でSELECT系の処理がどうしても難しいからです。
ストアドプロシージャで弊社で使っているスタブとしてのストアドプロシージャ(ファンクション)は以下のようになります。特にOracleは長くなりますが、本番時に書き換えるのは赤い部分だけです。
Oracleの場合
CREATE OR REPLACE PACKAGE TEST_PKG AS /* * テスト作成 */ -- TEST_FUNC 用のオブジェクトタイプを作成 TYPE TEST_FUNC_ROW IS RECORD ( ID NUMBER -- 主キーです , NAME VARCHAR2(40) -- 名前 , Bdate DATE -- 誕生日 , ADDRESS VARCHAR2(255) -- 住所 ); -- TEST_FUNC 用のレコードセットを定義 TYPE TEST_FUNC_RSET IS TABLE OF TEST_FUNC_ROW; /* ■□■□ TEST_FUNC * テスト * 得意先を検索する */ FUNCTION TEST_FUNC ( PARM1 IN NUMBER -- 1つ目のパラメータ , PARM2 IN VARCHAR2 -- 2つ目のパラメータ ) RETURN TEST_FUNC_RSET PIPELINED; END; / CREATE OR REPLACE PACKAGE BODY TEST_PKG AS /* * テスト作成 */ /* ■□■□ TEST_FUNC * テスト * 得意先を検索する */ FUNCTION TEST_FUNC ( PARM1 IN NUMBER -- 1つ目のパラメータ , PARM2 IN VARCHAR2 -- 2つ目のパラメータ ) RETURN TEST_FUNC_RSET PIPELINED IS CURSOR Cur_Main IS
-- 本番時は以下のSQLを修正し、
-- このコメントを削除する。
SELECT *
FROM xTEST_FUNC_VIEW
WHERE
1 = 1
AND ID = PARM1
AND NAME LIKE PARM2
;
Row_Main Cur_Main%ROWTYPE; ReturnRow TEST_FUNC_ROW; BEGIN OPEN Cur_Main; Loop FETCH Cur_Main INTO Row_Main; EXIT WHEN Cur_Main%NOTFOUND; ReturnRow.ID := Row_Main.ID; -- 主キーです ReturnRow.NAME := Row_Main.NAME; -- 名前 ReturnRow.Bdate := Row_Main.Bdate; -- 誕生日 ReturnRow.ADDRESS := Row_Main.ADDRESS;-- 住所 PIPE ROW(ReturnRow); -- データを出力する。 END Loop; CLOSE Cur_Main; RETURN; END TEST_FUNC; END;
SQL Serverの場合
/* * テスト作成 */ IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TEST_FUNC]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[TEST_FUNC] GO /* ■□■□ TEST_FUNC * テスト */ CREATE PROCEDURE TEST_FUNC ( @PARM1 INT -- 1つ目のパラメータ , @PARM2 VARCHAR(40) -- 2つ目のパラメータ ) AS SET NOCOUNT ON;
-- 本番時は以下のSQLを修正し、
-- このコメントを削除する。
SELECT *
FROM xTEST_FUNC_VIEW
WHERE
1 = 1
AND ID = @PARM1
AND NAME LIKE @PARM2
;
GO
ストアドプロシージャはバッチ専用ではない
ユーザインターフェースを作成するために、パラメータと戻りの型を仕様書としてエクセルに書き起こせば、マクロで上の通りジェネレートし、ダミーデータも同時に作ることができます。
ストアドプロシージャをインターフェースとすることにより、データベースと完全に疎結合にした状態でユーザインターフェース側のコーディングが可能になります。IDE(統合開発環境)やO/Rマッパも含めたフレームワークは洗練されてきているので、処理済みのデータを単純に出力するだけなら、プログラミングの7〜8割はマウス操作で可能になるでしょう。
つまり、現実に動くユーザインターフェースを先に納品できますから、顧客が抱く最終成果物とイメージのギャップを最小限に抑え、初期の段階で(ユーザインタフェース側の詳細設計を書くために)データベース設計を無理に行う必要もないので、開発の手戻りを抑えることができるため大幅な工数削減が可能になります。
赤い部分を書き直せば開発は終了です。ストアドプロシージャはバッチ処理をするのではなくSQLをラップするために利用します。もちろん、最終的に一文のSQLにならず、ストアドプロシージャ内でワークテーブルなどを利用してバッチ的な処理をすることも可能です。
バッチ的な処理が、オブジェクト指向言語側でループ処理していれば、大変遅い処理になっています。
一文のSQLで一括処理できても、できなくてもパフォーマンス的にもメリットがあり、ユーザインターフェースを開発している時点では、内部処理、データベース構造を全く考慮してないのにもかかわらず、十分なパフォーマンスを出すことが可能になります。
SQLServerとOracleを見比べて分かるように、Oracleは非常に書きにくいです。エクセルでジェネレートしなかったらちょっと使えない。実際、OracleのPL/SQL表(パイプライン表)をこのような使い方をしているプロジェクトはほとんどないでしょう。
ですから、SQL文はすべてストアドプロシージャに入れて、生のSQLを書かないでシステムを構築する。そんなことができるわけがない。と感じる人は、残念ながら大変大勢います。そんな風に感覚的に理解できないのは、Oracleが植え付けた「ストアドプロシージャはバッチ処理のためにある」という間違った文化のせいでしょう。
これは迷信みたいなものです。
しかし、技術者が根拠のない迷信を信じてはいけません。既成概念を打ち破っていかないと技術者ではなく作業者です。