So-net無料ブログ作成

MS Accessのパラメータクエリ [Accessクエリ]



本日は、2回目の投稿となる。MS Accessのパラメータクエリのお話である。

パラメータクエリ

Accessでのクエリは「VIEWみたいなものである」とSQLポケリに書いたと思う。
といい加減ではいけない気がするので、ちょっと調べた。

CREATE VIEWのところに、*Accessでは、ビューは「クエリ」として作成されます。
と書いてある。

1.6ビューのところに、ビューにはパラメータを付けることはできない、うんぬんという表記もある。

Accessにおいては、ビュー=クエリにパラメータを付けることができる。

この辺りが「Accessならでは」の事情になっており混乱している。

Oracleなどのデータベースはサーバーとして動作している。サーバへの命令はほとんどSQLだけで行う。
Accessは、データベースエンジンが内蔵されていて、ユーザ操作のGUIとセットになって動いている。データベース操作は、GUIのデザイナで行う。一部の操作はSQLでも可能。逆にSQL命令でなければ、記述できないクエリも存在する。
Accessのデータベースエンジンだけを使用して、SQL命令だけでAccessデータベースを扱うことも可能ではあるが、メインとなる操作方法ではない。

それは、さておき、パラメータクエリの作成方法について解説してみたいと思う。

パラメータクエリといっても、クエリの作成方法が異なるわけではない。普通にクエリを作成して、デザインビューの抽出条件のところに、[ ] で囲んで適当な文字列を記述すればよいだけ。

Accessパラメータクエリ.png

この状態で、クエリを実行すると、ダイアログボックスが表示され、[ ] の中に書かれた文字列が表示される。ユーザが、ダイアログボックスに数値を入力すると、その数値が抽出条件になる。といった寸法である。

Accessパラメータクエリ入力ダイアログ.png

テーブルから条件に一致するレコードのみを抽出したい、という要望は日常茶飯事的に発生するものである。クエリにパラメータを持たせることができるのは、非常に便利なわけである。

パラメータの型を指定しておくと、ダイアログボックスで入力した数値がエラーチェックされるようになるので、さらに便利になる。

Accessパラメータの型指定.png

また、>=[パラメータ]のように抽出条件を書いておけば、入力された数値以上であるレコードが抽出されるようにもできる。

Accessで、クエリを作成すると、データベースオブジェクトのビューのようなものが作成される。Oracleなどでは、ビューにパラメータを作成することはできないが、Accessではそれが可能となっている。
ビューの中に、パラメータがあると、Accessでは、数値を入力するようにダイアログを表示する。
Oracleなどでは、ビューにパラメータを付けることはできないが、仮にパラメータが作成できたとする。Oracleは別のサーバマシンで動作していることが多いであろう。サーバマシンでダイアログを表示しても、ユーザが数値を入力することはできないであろう。

つまり、Accessはデータベースエンジンとユーザ操作のGUIが一体化しているので、こういった芸当が可能なわけである。

置換変数

ちなみに、OracleのSQL Developerでは、「&&パラメータ」と書いておくと同じようなことが可能である。
しかし、これは、SQL DeveloperやSQL*Plusだけでの話。バインド変数ではなく、置換変数と呼ばれる機能。SQL Developerが変数の内容を展開してからクエリを実行する。ビューの中に置換変数を記述することはできない。

SQLDeveloper置換変数.png

AccessのパラメータクエリとOracleの置換変数を見てきた。クエリにパラメータを渡す方法は、各データベースでいろいろ。SQL標準を基本に考えれば「ビューにパラメータを付けることはできない」で正解である。
SQLポケリでは、ビューにパラメータを付けることができないので、そういう場合はストアドを作れ、となっている。Accessでは、クエリにパラメータを付けることができるので、それで代用可能では?と思われるが、そうもいかない。

なぜかというと、クエリからパラメータ付きの別クエリを結合なりで呼び出すことは可能であるが、パラメータを与えることができない。例えば、以下のようなSELECT命令にはできないのである。

SELECT * FROM foo CROSS JOIN パラメータクエリ(foo.a)


パラメータの値は、ダイアログボックスで入力するしかないのである。

テーブルを戻す関数を作成すれば、ビューに抽出用のパラメータを付けた感じにすることができる。Accessではできないが、SQL Serverでは以下のようにすれば可能である。

CREATE FUNCTION table_func(@argment int) RETURNS TABLE AS
 RETURN (SELECT * FROM foo WHERE a=@argment)

SELECT * FROM foo CROSS APPLY table_func(foo.a) AS S


table_funcがテーブルを返す関数。その引数にfooのa列の値を指定して渡している。
こういった芸当はAccessのパラメータクエリではできないのである。

CROSS APPLYの話はこちらからどうぞ





サイト内を検索


nice!(0)  コメント(0) 
共通テーマ:携帯コンテンツ

MS AccessでBEFOREトリガー [Accessクエリ]



本日も昨日に続き、MS Accessトリガの話題である。

MS AccessでINSERT、DELETE、UPDATEのAFTERトリガを作成することに成功した。SQL命令でトリガの中身を書くことができないので、なんとももどかしい感じである。

前の記事
MS Accessでトリガー

しかし、テキストで命令を書いていくプログラミングに不慣れな方にとっては、GUI画面でマクロを組んでいく、という方法が好まれていると思われる。

トリガーのBEFORE/AFTER

それは、さておき、先日紹介できなかった、BEFOREトリガについて解説しようと思う。
AFTERトリガは、レコードの追加、削除、更新のデータベース処理後に発生する。レコードの追加トリガは、INSERT命令が実行された後にイベントが上がり発火する。
DELETEトリガはDELETE命令の実行後、UPDATEトリガはUPDATE命令の実行後。

これらのAFTERトリガで処理すべき内容は、データの整合性を保持するようなものやログ的なものになるであろう。データの整合性は、外部参照整合制約(外部キー)を作成しておけばある程度は自動化できる。外部キーを使えないような特殊な場合とか、データベースアクセスの履歴を常に保存しておきたい、とか、データのバックアップを自動的にやりたいとか、そういった用途にAFTERトリガが使用される。

BEFOREトリガでは、これもまた、データの整合性を保持するような機能を実現できる。ログ的な用途で使用してもよいが、あまり使用されないと思う。
AFTERトリガでは、データベース操作をエラーで終了させることはできない。なぜなら、データベース操作処理が終わってしまってから呼び出されるものだから。
BEFOREトリガは、データベース操作処理の前に先立って呼び出されるので、そこでエラーチェックすることができる。不正値が与えられたら、データベース操作をエラーで終わらせることができるのである。

変更前トリガ

変更前トリガを以下のように作成したみた。

Access変更前トリガー.png

これで、fooテーブルのb列を、999、9999、99999の何れかの値で更新をかけようとするとエラーが発生するようになる。

Access変更前トリガでエラー.png

削除前トリガ

同じ要領で削除前トリガを作成。

Access削除前トリガ.png

これで、fooテーブルのb列が、1、2、3の何れかの値であるレコードを削除しようとするとエラーとなり、削除できなくなる。

Access削除前トリガでエラー.png

Accessでもトリガを作成できることがわかった。
しかしながら、SQLのCREATE TRIGGER命令は使用しないし、トリガの中身をSQLで記述することもできないので、Accessでは、SQLを使ってトリガを作成することはできない、ということにしておく。
SQLポケリは更新しないとですね。





サイト内を検索


nice!(0)  コメント(0) 
共通テーマ:携帯コンテンツ

AREarthroidPro ARで地球を表示するアプリ

Copyright Atsushi Asai Google+朝井淳
[改訂第4版]SQLポケットリファレンス

[改訂第4版]SQLポケットリファレンス

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2017/02/18
  • メディア: 単行本(ソフトカバー)

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

[データベースの気持ちがわかる]SQLはじめの一歩 (WEB+DB PRESS plus)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2015/03/03
  • メディア: 単行本(ソフトカバー)
C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

C言語 ポインタが理解できない理由 [改訂新版] (プログラミングの教科書)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2011/04/08
  • メディア: 単行本(ソフトカバー)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

かんたんAndroidアプリ作成入門 (プログラミングの教科書)

  • 作者: 朝井 淳
  • 出版社/メーカー: 技術評論社
  • 発売日: 2013/04/16
  • メディア: 単行本(ソフトカバー)