| Symfoware Server SQLビギナーズガイド - FUJITSU - |
|
目次
索引
|
| 第4章 カーソルを使用するデータ操作の方法 |
カーソルを使用してデータ操作を行うには、カーソルを宣言しておくことが必要です。カーソル宣言は非実行文です。カーソル宣言の指定位置は、そのカーソルを使用するSQL文よりも、アプリケーションの記述の順序で前にあることが必要です。実行順序とは関係ありません。
カーソル宣言の例を以下に示します。
例1
発注表から、取引先、取引製品、発注数量を求めるためのカーソルを宣言します。
DECLARE 発注表カーソル CURSOR FOR (1) SELECT 取引先, 取引製品, 発注数量 FROM 在庫管理.発注表 (2)(1) カーソル名
(2) 問合せ式
このカーソル宣言は、発注表から取引先、取引製品、および発注数量の3つの列の値を、すべての行について取り出すためのカーソルを宣言しています。
カーソルを使用して操作する表やデータの取り出し方は、カーソル宣言の問合せ式で指定します。例1の問合せ式で行うデータ操作は単純なものです。このカーソルを使用して表からデータを取り出し、アプリケーションの論理でいろいろな処理を行うことができます。一方、問合せ式では、表をグループ化して操作したり、複数の表の集合和からデータを取り出すことができます。問合せ式をうまく利用すれば、アプリケーションの処理を大幅に削減することが可能になります。
問合せ式には、1つの問合せ指定を指定する方法と、2つ以上の問合せ指定を“UNION”で結合して指定する方法があります。UNIONの指定方法については、“複数の表から行の集合和を求める”で説明します。
問合せ指定は、単一行SELECT文からINTO句を取り除いたのと同じ形式です。問合せ指定により表を導出しますが、表の導出方法は単一行SELECT文の場合と同じです。すなわち、FROM句によって導出される表から、WHERE句の条件を満たす行について、選択リストで指定する列の値を取り出します。なお、単一行SELECT文では、取り出す行が2行以上あるとエラーになりますが、問合せ指定では、導出する行は何行あってもかまいません。
また、複数行を対象とするようなグループ処理ができます。詳しくは、“表をグループ化してデータを操作する”で説明します。ここでは、問合せ指定にGROUP BY句を使用したカーソル宣言について説明します。指定例を以下に示します。
在庫表と発注表から、製品番号ごとの、製品名、発注している取引先の件数、および在庫数量と発注数量の合計を求めるためのカーソルを宣言します。
DECLARE 製品番号ごとに処理するカーソル CURSOR FOR SELECT 製品番号, 製品名, COUNT(取引先), 在庫数量 + SUM(発注数量) FROM 在庫管理.在庫表, 在庫管理.発注表 WHERE 製品番号 = 取引製品 GROUP BY 製品番号, 製品名, 在庫数量
注1) COUNT(取引先)の結果から作成される列です。
注2) 在庫数量 + SUM(発注数量)の結果から作成される列です。
備考. 演算式や集合関数の結果の列には名前がありません。
これまでに説明していないことで、1つ重要なことがあります。それは、カーソルによって取り出される行の順序に決まりはないということです。すなわち、カーソル表に含まれる行は問合せ式によって決定しますが、それらの行の順序についてはなにも決まっていません。このことは、1つの表からすべての行を取り出すような単純なカーソルでも同じです。“図:カーソル表の導出”で示したカーソル表の行は、たまたま製品番号の順番に並んでいますが、必ずしもこの順番になるとは限りません。実際にアプリケーションを実行してみると、以下のような順序になるかもしれません。
注1） COUNT（取引先）の結果から作成される列です。
注2） 在庫数量 + SUM（発注数量）の結果から作成される列です。
備考．演算式や集合関数の結果の列には名前がありません。
カーソル宣言では、カーソル表に含まれる行を並べ替えることができます。カーソル表の行を並べ替えるには、問合せ式の後にORDER BY句を指定します。なお、行を並べ替えることを“ソート”と呼びます。ORDER BY句の指定例を以下に示します。
例3
例2のカーソル宣言で、カーソルによってデータを取り出す順序を製品番号の小さい順とします。
DECLARE 製品番号ごとに処理するカーソル CURSOR FOR SELECT 製品番号, 製品名, COUNT(取引先), 在庫数量 + SUM(発注数量) FROM 在庫管理.在庫表, 在庫管理.発注表 WHERE 製品番号 = 取引製品 GROUP BY 製品番号, 製品名, 在庫数量 ORDER BY 製品番号 ASC (A) (1)(1) ソート指定
(A)〜(1) ORDER BY句
ORDER BY句には、ソートするための列名、集合関数または値式と、ソートの方法を指定します。ソートするための列を“ソートキー”と呼びます。ソートの方法には以下のどちらかが指定できます。なお、ソートの方法は省略することができ、その場合は“ASC”を指定したのと同じになります。
NULL値は、最大の値として処理します。そのため、昇順では最後に、降順では最初に扱われます。
例3のように指定することにより、カーソル表に含まれる行の順序は“図:カーソル表の導出”になることが決まります。なお、“図:カーソル表の導出”では製品番号の値は行ごとに異なっているので、カーソル表の行の順序は一意に決まりました。次に、製品番号の値が同じ行が複数あった場合について説明します。
同じソートキーの値が複数行ある場合には、それらの行間での順序に決まりはありません。ソートキーの値が異なる行間では、ソート指定に従った順序となります。ソートキーの値が同じ行についても並べ替えるには、複数のソート指定をコンマ(,)で区切って指定します。ORDER BY句に指定された複数のソートキーは、左から順番に“第1ソートキー,第2ソートキー,…”と呼ばれます。カーソル表の行の順序は、まず第1ソートキーの値で並べ替えられ、その値が同じ行に対して第2ソートキーの値で並べ替えられます。すべてのソートキーについて同じ値を持つ行の順序には決まりはありません。指定例を以下に示します。
例4
在庫表から、倉庫番号の値の小さい順にデータを取り出すカーソルを宣言します。倉庫番号の値が同じ行については、在庫数量の値が大きい順とします。
DECLARE 在庫表カーソル CURSOR FOR SELECT * FROM 在庫管理.在庫表 ORDER BY 倉庫番号 ASC, 在庫数量 DESC (C) (1) (A) (2) (B)(1) 第1ソートキー
(2) 第2ソートキー
(1)〜(A)および(2)〜(B) ソート指定
(C)〜(B) ORDER BY句
これまでは、カーソル表に含まれる列を使用して行の順序を並べ替える例について説明しましたが、カーソル表に含まれない列を使用してソートすることもできます。指定例を以下に示します。
例5
在庫表から、在庫数量が30未満の製品の製品番号と製品名を、在庫数量の値が大きい順にソートして取り出すカーソルを宣言します。
DECLARE 在庫数量カーソル CURSOR FOR SELECT 製品番号, 製品名 FROM 在庫管理.在庫表 WHERE 在庫数量 < 30 ORDER BY 在庫数量 DESC
一方、問合せ指定の選択リストに演算式や、集合関数を指定した結果の列には名前がありません。名前のない列をソートキーにするには、ソート指定で列名の代わりに、列の番号を指定します。列の番号は、カーソル表の列を左から数えた順番です。指定例を以下に示します。
例6
例2のカーソル宣言で、カーソルによってデータを取り出す順序を、第1ソートキーを取引先の数で降順に、第2ソートキーを在庫数量と発注数量の合計で昇順にソートします。
DECLARE 製品番号ごとに処理するカーソル CURSOR FOR SELECT 製品番号, 製品名, COUNT(取引先), 在庫数量 + SUM(発注数量) FROM 在庫管理.在庫表, 在庫管理.発注表 WHERE 製品番号 = 取引製品 GROUP BY 製品番号, 製品名, 在庫数量 ORDER BY 3 DESC, 4 ASC (1) (2)(1) 第1ソートキー
(2) 第2ソートキー
注1） COUNT（取引先）の結果から作成される列です。
注2） 在庫数量 + SUM（発注数量）の結果から作成される列です。
備考．演算式や集合関数の結果の列には名前がありません。
カーソルには、更新可能カーソルと読込み専用カーソルの2種類があります。カーソルを使用するUPDATE文やDELETE文は更新可能カーソルだけが使用できます。FETCH文は両方が使用できます。カーソルがどちらになるかは、カーソル宣言の指定の内容によって決まります。
読込み専用カーソルとなるカーソル宣言の条件を以下に示します。この例で使用する表TBL1および表TBL2のスキーマ名はSCMとします。
|
読込み専用カーソルとなる |
例 |
|---|---|
|
a) DISTINCTを指定している。 |
DECLARE CSR CURSOR FOR SELECT DISTINCT COLA, COLB FROM SCM.TBL1 |
|
b) 選択リストに演算式を指定している。 |
DECLARE CSR CURSOR FOR SELECT COLC, COLA + COLB FROM SCM.TBL1 |
|
c) 選択リストに集合関数を指定している。 |
DECLARE CSR CURSOR FOR SELECT SUM(COLB) FROM SCM.TBL1 |
|
d) 選択リストに同じ列を指定している。 |
DECLARE CSR CURSOR FOR SELECT COLA, COLB, COLA, COLC FROM SCM.TBL1 |
|
e) 選択リストに定数またはホスト変数を指定している。 |
DECLARE CSR CURSOR FOR SELECT COLA, 100 FROM SCM.TBL1 |
|
f) 選択リストにデータ列値関数を指定している。 |
DECLARE CSR CURSOR FOR SELECT SUBSTRING(COL1 FROM 1 FOR 3) FROM SCM.TBL1 |
|
g) 選択リストに数値関数を指定している。 |
DECLARE CSR CURSOR FOR SELECT ABS(COL1) FROM SCM.TBL1 |
|
h) 選択リストに日時値関数を指定している。 |
DECLARE CSR CURSOR FOR SELECT TRUNC_DATE(COL1,'MONTH') FROM SCM.TBL1 |
|
i) 選択リストにCASE式を指定している。 |
DECLARE CSR CURSOR FOR SELECT CASE WHEN COL1=1 THEN 'ONE' END FROM SCM.TBL1 |
|
j) 選択リストにCAST指定を指定している。 |
DECLARE CSR CURSOR FOR SELECT CAST(COL1 AS CHAR(6)) FROM SCM.TBL1 |
|
k) 選択リストに連結演算子を指定している。 |
DECLARE CSR CURSOR FOR SELECT COL1 || COL2 FROM SCM.TBL1 |
|
l) 選択リストにファンクションルーチンを指定している。 |
DECLARE CSR CURSOR FOR SELECT USERFUNC1(C1,C2) FROM SCM.TBL1 |
|
m) 選択リストに順序を指定している。 |
DECLARE CSR CURSOR FOR SELECT SCM.順序1.NEXTVAL FROM SCM.TBL1 |
|
n) FROM句に2つ以上の表または結合表を指定している。 |
DECLARE CSR CURSOR FOR SELECT COLA, COLZ FROM SCM.TBL1, SCM.TBL2 |
|
o) GROUP BY句またはHAVING句を指定している。 |
DECLARE CSR CURSOR FOR SELECT COLA FROM SCM.TBL1 GROUP BY COLA |
|
p) WHERE句に副問合せを含んでいる。 |
DECLARE CSR CURSOR FOR SELECT * FROM SCM.TBL1 WHERE COLA IN (SELECT COLZ FROM SCM.TBL2 WHERE COLY > 2000) |
|
q) UNIONを指定している。 |
DECLARE CSR CURSOR FOR SELECT COLC FROM SCM.TBL1 UNION SELECT COLX FROM SCM.TBL2 |
|
r) 更新可能性句にFOR READ ONLYを指定している。 |
DECLARE CSR CURSOR FOR SELECT COLA, COLB FROM SCM.TBL1 FOR READ ONLY |
|
s) ORDER BY句を指定し、更新可能性句を指定していない。 |
DECLARE CSR CURSOR FOR SELECT COLA, COLB FROM SCM.TBL1 ORDER BY COLA |
|
t) SCROLLを指定し、更新可能性句を指定していない。 |
DECLARE CSR SCROLL CURSOR FOR SELECT COLA, COLB FROM SCM.TBL1 |
カーソル宣言の形式が、a)からt)のいずれかに該当する場合、読込み専用カーソルになります。いずれにも当てはまらない場合は、更新可能カーソルになります。
a)からp)は、問合せ指定で指定する要素です。また、q)は問合せ式で指定する要素です。r)からt)は、カーソル宣言で指定する要素です。r)とs)は、更新可能性句にFOR UPDATEを指定したときのみ更新可能カーソルとなります。なお、p)の副問合せについては“いろいろな探索条件を指定する”を、q)のUNIONについては“複数の表から行の集合和を求める”をそれぞれ参照してください。
|
目次
索引
|
