RDBMS毎の違い
1
Copyright 2016 G1Systems Inc.
RDBMS毎の違い
RDBMS
プリコンパイル
関数・プロシージャ
特徴
Oracle
されている
関数
(依存関係が分かる) (プロシージャも可)
パッケージにできるがくどい
細かい制御が可能
V$SQLは便利
SQLServer
されている
プロシージャ
(依存関係が分かる) (関数も可)
Begin End はくどい
RECOMPILEヒントがある
DB2
されている
プロシージャ
(依存関係が分かる)
パッケージにできる？
Begin End はくどい
REOPTオプションを利用する ※1
PostgreSQL
されない
関数
関数内でコミットできない
大きなバッチには不向き
MySQL
されない
プロシージャ
いろいろと貧弱
（特にエラー処理）
※1 https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_10.5.0/com.ibm.db2.luw.admin.perf.doc/doc/c0055082.html
2
Copyright 2016 G1Systems Inc.
依存関係を調べる
 プリコンパイルされているタイプのRDBMSでは、依存
関係を調べることができる。（以下はOracle）
SELECT * FROM
(SELECT
REFERENCED_OWNER || '.' || REFERENCED_NAME AS REF_OBJ
, TYPE
, OWNER || '.' || NAME AS OBJECT
, LEVEL AS OBJ_LEVEL
, SUBSTR(SYS_CONNECT_BY_PATH(REFERENCED_NAME,'←'),2) || '←' || NAME AS LIST
FROM
ALL_DEPENDENCIES
START WITH REFERENCED_NAME
IN ('DMP_SEC', 'DBMS_COMPARISON') -- 依存関係を調べたいオブジェクトの名前
CONNECT BY
PRIOR OWNER = REFERENCED_OWNER
AND PRIOR NAME = REFERENCED_NAME
AND PRIOR TYPE = REFERENCED_TYPE) a
ORDER BY
REF_OBJ
, OBJ_LEVEL;
3
Copyright 2016 G1Systems Inc.
Oracle の特徴１
 呼び出し方
SELECT * FROM TABLE(pkg.func(?, ?))
 BIND PEEK と ADAPTIVE CURSOR SHARING
で何回か失敗すれば、実行計画を作り直してくれる。
(Oracle11g以降)
 失敗が許容できない場合、動的SQLになるため依存関係
が分からなくなる。
4
Copyright 2016 G1Systems Inc.
ストアドプロシージャで動的生成すると……
vSQL := vSQL || 'WHERE Col1 = ''' || Para1 || '''';
IF Para2 NOT IS NULL THEN
vSQL := vSQL || ' AND Col2 = ''' || Para2 || '''';
END IF;
IF Para3 NOT IS NULL THEN
vSQL := vSQL || ' AND Col3 = ''' || Para3 ';
END IF;
IF Para4 NOT IS NULL THEN
vSQL := vSQL || ' OR Col4 = ''' || Para4 ';
END IF;
5
Copyright 2016 G1Systems Inc.
Oracle の特徴２－ プレースホルダにするなら
vSQL := vSQL || 'WHERE Col1 = :Para1';
vPrt := BIN_TO_NUM(0, 0, 0);
-- ビット演算 （ゼロでもいいけれど……）
IF Para2 NOT IS NULL THEN
vSQL := vSQL || ' AND Col2 = :Para2 '; パラメータごとにビットの位
vPrt := vPrt + BIN_TO_NUM(0, 0, 1);
置を決める。
END IF;
IF Para3 NOT IS NULL THEN
vSQL := vSQL || ' AND Col3 = :Para3 ';
ビットが立ってるパラメータ
vPrt := vPrt + BIN_TO_NUM(0, 1, 0);
をバインドする
END IF;
-- ……
CASE vPrt
WHEN BIN_TO_NUM(0,0,0) THEN EXECUTE IMMEDIATE vSQL USING Para1;
WHEN BIN_TO_NUM(0,0,1) THEN EXECUTE IMMEDIATE vSQL USING Para1, Para2;
WHEN BIN_TO_NUM(0,1,0) THEN EXECUTE IMMEDIATE vSQL USING Para1, Para3;
WHEN BIN_TO_NUM(0,1,1) THEN EXECUTE IMMEDIATE vSQL USING Para1, Para2, Para3;
-- ……
-- 任意のパラメータのビット数個(10個なら1024個必要)
-- Excelマクロで出力できなくないけれど……。
END CASE;
6
Copyright 2016 G1Systems Inc.
SQLServer の特徴１
 呼び出し方
EXEC proc ?, ?
SELECT * FROM func(?, ?)
 ストアドプロシージャにしても Linq と親和性が高い
 Linux対応が楽しみw
 依存関係は以下を参照のこと。
https://technet.microsoft.com/jajp/library/bb677168%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396
7
Copyright 2016 G1Systems Inc.
SQLServer の特徴２－ ストアドプロシージャ
CREATE PROCEDURE TEST_PROC(
@p_name NVARCHAR(20) -- 顧客名(一部一致、任意条件)
, @p_zip NVARCHAR(10)
-- 郵便番号(全一致、任意条件)
, @p_address NVARCHAR(100)
-- 住所(一部一致、任意条件)
)
AS
SET NOCOUNT ON;
SELECT
id, 顧客名, 郵便番号, 住所
FROM 顧客マスタ
WHERE
(顧客名 LIKE @p_name OR @p_name IS NULL)
AND (郵便番号 = @p_zip OR @p_zip IS NULL)
AND (住所 LIKE @p_address OR @p_address IS NULL)
OPTION(RECOMPILE);
GO
8
Copyright 2016 G1Systems Inc.
DB2 の特徴１
 呼び出し方
CALL proc(?, ?)
 REOPTオプション で、SQLServer の RECOMPILEと同様
のことが可能（なはず）です。
 依存関係は以下を参照のこと。
https://www.ibm.com/support/knowledgecenter/ja/SSEPGG_10.5.
0/com.ibm.db2.luw.sql.ref.doc/doc/r0001052.html
9
Copyright 2016 G1Systems Inc.
DB2 の特徴２－ ストアドプロシージャ
CREATE PROCEDURE DB2ADMIN.TEST_PROC(
IN p_name VARCHAR(20) -- 顧客名(一部一致、任意条件)
, IN p_zip VARCHAR(10)
-- 郵便番号(全一致、任意条件)
, IN p_address VARCHAR(100)
-- 住所(一部一致、任意条件)
)
DYNAMIC RESULT SETS 1
LANGUAGE SQL
P1: BEGIN
DECLARE CURSOR_ CURSOR WITH RETURN FOR
SELECT
id, 顧客名, 郵便番号, 住所
FROM 顧客マスタ
WHERE
(顧客名 LIKE @p_name OR @p_name IS NULL)
AND (郵便番号 = @p_zip OR @p_zip IS NULL)
AND (住所 LIKE @p_address OR @p_address IS NULL);
OPEN CURSOR_;
END P1
10
Copyright 2016 G1Systems Inc.
PostgreSQL の特徴１
 呼び出し方
SELECT * FROM func(?, ?)
 Functionしか作れず、 Function内でコミットできない
ため、大きなバッチには不向きです。
 戻りの型を宣言するのが少し面倒ですが、マクロで吸
収できます。
 依存関係は調べられません。
11
Copyright 2016 G1Systems Inc.
PostgreSQL の特徴２－ ストアドファンクション
CREATE OR REPLACE FUNCTION TEST_PROC(
IN p_name CHARACTER VARYING
-- 顧客名(一部一致、任意条件)
, IN p_zip CHARACTER VARYING
-- 郵便番号(全一致、任意条件)
, IN p_address CHARACTER VARYING
-- 住所(一部一致、任意条件)
)
RETURNS TABLE( -- 戻りの型を宣言するのが少し面倒
id INT, 顧客名 character varying, 郵便番号 character varying, 住所 character varying
) AS
$BODY$
DECLARE
BEGIN
RETURN QUERY
SELECT
id, 顧客名, 郵便番号, 住所
FROM 顧客マスタ
WHERE
(顧客名 LIKE p_name OR p_name IS NULL)
AND (郵便番号 = p_zip OR p_zip IS NULL)
AND (住所 LIKE p_address OR p_address IS NULL) ;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;
12
Copyright 2016 G1Systems Inc.
MySQL の特徴１
 呼び出し方
CALL proc(?, ?)
 エラー処理などが貧弱で使えなくない。というレベル
です。
13
Copyright 2016 G1Systems Inc.
MySQL の特徴１
 呼び出し方
CALL proc(?, ?)
 かなりシンプルに書けますからお手軽に利用可能です。
ただし、エラー処理が非常に貧弱なので、凝った処理
には不向きかもしれません。
 依存関係は調べられません。
14
Copyright 2016 G1Systems Inc.
MySQL の特徴２－ ストアドプロシージャ
DELIMITER $$
CREATE PROCEDURE TEST_PROC(
p_name TEXT
-- 顧客名(一部一致、任意条件)
, p_zip TEXT
-- 郵便番号(全一致、任意条件)
, p_address TEXT
-- 住所(一部一致、任意条件)
)
BEGIN
SELECT
id, 顧客名, 郵便番号, 住所
FROM 顧客マスタ
WHERE
(顧客名 LIKE p_name OR p_name IS NULL)
AND (郵便番号 = p_zip OR p_zip IS NULL)
AND (住所 LIKE p_address OR p_address IS NULL)
;
End
$$
DELIMITER ;
15
Copyright 2016 G1Systems Inc.

PDF:217KB