Chapter4 An Introduction to SQL

1. Chapter4An Introduction to SQL 福島 卓也 2004/2/27

2. 今回の内容 4.1 導入 4.2 概要 4.3 カタログ 4.4 ビュー 4.5 トランザクション 4.6 埋め込みSQL 4.7 動的SQLとSQL/CLI 4.8 SQLは完璧ではない 4.9 要約 An Introduction to SQL

3. 4.1 導入 4.1 導入 4.2 概要 4.3 カタログ 4.4 ビュー 4.5 トランザクション 4.6 埋め込みSQL 4.7 動的SQLとSQL/CLI 4.8 SQLは完璧ではない 4.9 要約 An Introduction to SQL

4. 本章の目的 • 本章の目的 • SQLの主な特徴について述べる • SQLとは？ • 関係型システムの標準言語 • 今日のほとんど全ての商用商品で使用 • 1970年代にIBMが開発 • ここでのSQLとはSQL:1999のことをさす An Introduction to SQL

5. SQLの特徴 • もともとはデータ副言語を意図 1996年にSQL Persistent Stored Modules (SQL/PSM)によって計算完備な言語に拡張 →CALL, RETURN, SET, CASE, IF, LOOP, LEAVE, WHILE, REPEATなど変数や例外を扱えるもの。 • SQLでは以下の表現を用いる • relation(relvar): 表(table) • tuple: 行(row) • attribute: 列(column) • SQLの仕様書は2000ページを超える • 完璧な関係言語ではない An Introduction to SQL

6. 4.2 概要 4.1 導入 4.2 概要 4.3 カタログ 4.4 ビュー 4.5 トランザクション 4.6 埋め込みSQL 4.7 動的SQLとSQL/CLI 4.8 SQLは完璧ではない 4.9 要約 An Introduction to SQL

7. データ副言語 • データ副言語(Data sublanguage) • データ定義言語　(DDL) • データ操作言語　（DML） • SQLではデータ定義、データ操作ともに含んでいる →本章ではこの2つの操作について述べる An Introduction to SQL

8. CREATE TYPEによって新たにデータ型を作成できる。 CREATE TABLEによって新たに関係(table)を作成できる。 INTEGER, CHAR型などもともと準備されているデータ型もある。 CREATE TYPE S# …; CREATE TYPE NAME …; …………… CREATE TABLE S ( S# S#, SNAME NAME, STATUS INTEGER, CITY CHAR(15), PRIMARY KEY ( S# ) ); データ定義 An Introduction to SQL

9. PRIMARY KEYで主キーを設定 FOREIGNKEYで外部キーを設定 CREATE TABLE SP ( S# S#, P# p#, QTY QTY, PRIMARY KEY (S#, P#), FOREIGN KEY (S#) REFERENCES S, FOREIGN KEY (P#) REFERENCES P ); データ定義2 An Introduction to SQL

10. データ操作 • 以下のような操作が可能 • SELECT • INSERT • DELETE • UPDATE • SELECTを使用することで次の操作が可能 • 制限 (restrict) • 射影 (project) • 結合 (join) An Introduction to SQL

11. 条件に当てはまる行だけを表示させる SQL文 SELECT * FROM SP WHERE QTY < 150; 制限 (restrict) An Introduction to SQL

12. 条件に当てはまる列だけ表示させる SQL文 SELECT S#, CITY FROM S; 射影 (project) An Introduction to SQL

13. 複数の表の列をひとまとめにして一つの表にする複数の表の列をひとまとめにして一つの表にする S# SNAME STATUS CITY S# P# QTY S1 P1 100 S1 Smith 20 London S1 P2 50 S2 Jones 10 Paris S2 P1 150 結合 (join) SELECT S#,SNAME, STATUS, CITY, P#, QTY FROM S, SP WHERE S.S# = SP.S#; An Introduction to SQL

14. 結合の結果 An Introduction to SQL

15. SELECT句以外の操作 • restrict, project, joinともにSELECT句による操作であった。 • SELECT句以外に次のような操作がある。 • INSERT • DELETE • UPDATE • 全て集合レベルの操作。 An Introduction to SQL

16. INSERT句 INSERT INTO TEMP ( P#, WEIGHT ) SELECT P#, WEIGHT FROM P WHERE COLOR = COLOR(‘Red’); • P#とWEIGHTを列に持つ表TEMPはあらかじめ作られている物とする。 • 表PでCOLORが‘Red’のもの全てをTEMPに挿入する操作を行っている。 An Introduction to SQL

17. DELETE句 DELETE FROM SP WHERE P# = P# (‘P2’); • SPからP#が‘P2’である行を全て削除する An Introduction to SQL

18. UPDATE句 UPDATE S SET STATUS = 2 * STATUS, CITY = ‘Rome’ WHERE CITY = ‘Paris’ ; • Parisにある供給者全てをRomeに移動しSTATUSを2倍にする。 An Introduction to SQL

19. 4.3 カタログ 4.1 導入 4.2 概要 4.3 カタログ 4.4 ビュー 4.5 トランザクション 4.6 埋め込みSQL 4.7 動的SQLとSQL/CLI 4.8 SQLは完璧ではない 4.9 要約 An Introduction to SQL

20. カタログ(Catalog)とスキーマ(Schema) • SQLは情報スキーマというカタログを備えている • SQLでは「カタログ」も「スキーマ」も両方用いられるがSQL特有の意味を持つ • カタログ:個々のデータベースに対する識別子 • スキーマ:ユーザ個人に属するデータベース（の一部分）に対する識別子 • 一つのデータベースにはカタログが複数ある • 一つのカタログは複数のスキーマに分割される An Introduction to SQL

21. 情報スキーマとは？ • SQLの表の集合 カタログ内の全ての他のスキーマの全ての定義を正確に反映 　＝　仮説的な｢定義スキーマ｣のビューの集合 • 実装の際、仮説的な定義スキーマはサポートする必要なく、以下の2つが重要 • ある種の定義スキーマ • 情報スキーマのビューに見える「定義スキーマ」のビュー An Introduction to SQL

22. 情報スキーマ • 市場の製品は一種の定義スキーマをサポートしている。 定義スキーマは製品によって大きく異なる 　（同社の製品でも異なることがある） • カタログ一つにつき情報スキーマは一つ ただし、ユーザに与えられる情報は一つのスキーマからではない(簡単のため一つであるかのようにいう） An Introduction to SQL

23. 情報スキーマ • 情報スキーマの内容までは詳しくは触れない • 情報スキーマのビューの例 ASSERTIONS TABLES CHECK_CONSTRAINTS TABLE_CONSTRAINTS COLUMNS TABLE_PRIVILEGES COLUMN_PRIVILEGES USAGE_PRIVILEGES COLUMN_UDT_USAGE USER_DEFINED_TYPES CONSTRAINT_COLUMN_USAGE UDT_PRIVILEGES CONSTRAINT_TABLE_USAGE VIEWS KEY_COLUMN_USAGE VIEW_COLUMN_USAGE REFERENTIAL_CONSTRAINTS VIEW_TABLE_USAGE SHEMATA • TABLEは表とビューに関する情報を含むが、VIEWはビューの情報のみを含む An Introduction to SQL

24. 4.4 ビュー 4.1 導入 4.2 概要 4.3 カタログ 4.4 ビュー 4.5 トランザクション 4.6 埋め込みSQL 4.7 動的SQLとSQL/CLI 4.8 SQLは完璧ではない 4.9 要約 An Introduction to SQL

25. ビュー(View) • ビュー定義 CREATE VIEW GOOD_SUPPLIER AS SELECT S#, STATUS, CITY FROM S WHERE STATUS > 15; • クエリは普通の表と区別なく同じようにできる。 SELECT S#, STATUS FROM GOOD_SUPPLIER WHERE CITY = ‘London’ ; An Introduction to SQL

26. ビューのクエリの例 • ビュー定義をビューの名前のところに代入 SELECT GOOD_SUPPIER.S#, GOOD_SUPPLIER.STATUS FROM ( SELECT S#, STATUS, CITY FROM S WHERE STATUS > 15 ) AS GOOD_SUPPLIER WHERE GOOD_SUPPLIER.CITY = ‘London’ ; • 実際に実行されるクエリ SELECT S#, STATUS FROM S WHERE STATUS > 15 AND CITY = ‘London’ ; An Introduction to SQL

27. ビューのクエリの例2 • DELETE文の場合 DELETE FROM GOOD_SUPPLIER WHERE CITY = ‘London’ ; • 実際に実行されるクエリ DELETE FROM S WHERE STATUS > 15 AND CITY = ‘London’ ; An Introduction to SQL

28. 4.5 トランザクション 4.1 導入 4.2 概要 4.3 カタログ 4.4 ビュー 4.5 トランザクション 4.6 埋め込みSQL 4.7 動的SQLとSQL/CLI 4.8 SQLは完璧ではない 4.9 要約 An Introduction to SQL

29. トランザクション(Transaction) • Chapter3では次のような記述があった。 • BEGIN TRANSACTION • COMMIT • ROLLBACK • SQLでは･･･ • START TRANSACTION • COMMIT WORK • ROLLBACK WORK WORKは省略可 An Introduction to SQL

30. 4.6 埋め込みSQL 4.1 導入 4.2 概要 4.3 カタログ 4.4 ビュー 4.5 トランザクション 4.6 埋め込みSQL 4.7 動的SQLとSQL/CLI 4.8 SQLは完璧ではない 4.9 要約 An Introduction to SQL

31. 埋め込みSQL • 多くのSQL商品は次の両方でSQLを実行できる。 • 直接(対話的に) • アプリケーションプログラムの一部として • SQL記述は親プログラムと混合されている(親プログラムに埋め込まれている)。 • SQL標準では以下の言語をサポート • Ada • C • COBOL • Fortran • Java • M • Pascl • PL/I An Introduction to SQL

32. 双対原理(Dual-mode principle) 双対原理 (Dual-mode principle) 対話的に使えるSQL記述はアプリケーションプログラム内でも使うことができる • 記述の詳細は多少異なる（特に検索） An Introduction to SQL

33. EXEC SQL BEGIN SECTION; DCL SQLSTATE CHAR(5); DCL P# CHAR(6); DCL WEIGHT FIXED DECIMAL(5,1); EXEC SQL END DECLARE SECTION; P# = ‘P2’; EXEC SQL SELECT P.WEIGHT INTO :WEIGHT FROM P WHERE P.P# = P# (:P#) ; IF SQLSTATE = ‘00000’ THEN ･･･; /* WEIGHTに値を代入 */ ELSE ･･･; /* 例外が発生 */ PL/Iでの埋め込みSQLのプログラム例 An Introduction to SQL

34. 埋め込みSQLの記述 • 埋め込みSQL記述はEXEC SQLという接頭辞を用いて、親言語と区別。 特別な終端記号（；など）をもちいる。 • 実行可能なSQL記述は親言語が記述できるところならばどこにでも記述可能。 埋め込みSQLには宣言的な記述（DECLARE CURSOR, WHENEVERなど）がある • 親変数はコロンを用いて区別 • INTO句は検索結果がどの値に代入されるのか、変数を特定するのに用いる。 An Introduction to SQL

35. 埋め込みSQLの記述２ • 親言語の変数は埋め込みSQLの定義部で定義されている必要がある。 BEGIN DECLARE SECTION, END ･･･ • 親変数にSQLSTATEという変数を定義してないといけない。 00000:実行成功 02000:データが見つからない • 全ての親変数はデータ型を定義されていないといけない。 • 親変数とSQLの列は同じ名前でもよい Ex.) WEIGHT, P# An Introduction to SQL

36. 埋め込みSQLの記述3 • WHENEVER句によってSQLSTATEごとに動作を記述するのが簡単にできる。 WHENEVER <状態><動作> NOT FOUND （SQLSTATE = 02xxx） SQLWARNING （SQLSTATE = 01xxx） SQLEXCEPTION （SQLSTATE = xxxxx） • SQLと親言語は疎結合 SQL部分と親言語部分を簡単に区別可能 An Introduction to SQL

37. 操作操作 • 記述はほとんどSQLと同じで少しの変更でよい。 • 特別な処理が必要 • SQLは複数の行を取り出すのに対して親言語は１行しか取り出せない。 • SQLは集合レベル検索、親言語は行レベル。 • カーソル（CURSOR）という論理的なポインタを使用することで解決。 An Introduction to SQL

38. カーソルを使用しない操作 • 以下の操作ではカーソルを使用しない • 単項SELECT • INSERT • DELETE(CURRENT以外) • UPDATE(CURRENT以外) An Introduction to SQL

39. 単項SELECTの例 EXEC SQL SELECT STATUS, CITY INTO :RANK, :TOWN FROM S WHERE S# = S# ( :GIVENS# ); • WHERE句の結果を満たすものが一つしかない • 結果が0個の場合SQLSTATEが02000になる • 結果が複数個の場合SQLSTATEからエラーメッセージが発生 An Introduction to SQL

40. INSERT句の例 EXEC SQLINSERT INTO P ( P#, PNAME, WEIGHT) VALUES ( :P#, :PNAME, :PWT) ; • テーブルPに新しい行を挿入する • 設定されていない属性の値（color, city）にはデフォルトの値が入力される An Introduction to SQL

41. DELETE句の例 EXEC SQL DELETE FROM SP WHERE :CITY = ( SELECT CITY FROM S WHERE S.S# = SP.S# ); • 親変数CITYの条件を満たす行を削除 • WHERE句を満たす行がなければSQLSTATEが02000になる An Introduction to SQL

42. UPDATE句の例 EXEC SQL UPDATE S SET STATUS = STATUS + :RAISE WHERE CITY = ‘London’ ; • CITYの値がLondonであるSTATUSにRAISEを加える • RAISEは親変数 • WHERE句を満たさない場合SQLSTATEが02000になる An Introduction to SQL

43. カーソルを使用する操作 • 集合レベルの操作 任意の行数を含む検索の集合 • カーソルによって集合の中の一行ごとにアクセスしていく An Introduction to SQL

44. カーソルを使用した操作の例 EXEC SQL DECLARE X CURSOR FOR SELECT S.S#, S.SNAME, S.STATUS FROM S WHERE S.CITY = :Y ORDER BY S# ASC; EXEC SQL OPEN X; DO for all S rows accessible via X; EXEC SQL FETCH X INTO :S#, :SNAME, :STATUS; ･･･ END; EXEC SQL CLOSE X; An Introduction to SQL

45. 検索の説明 • DECLARE X CURSORでカーソルＸを定義 この段階では定義されただけで評価されていない • OPEN Xによって評価される • FETCH Xはループ内に現れ、検索結果をINTO句の親変数に代入 結果の集合にまだ行がある限りループし続ける • ループを抜けるとCLOSE Xで終了する An Introduction to SQL

46. DECLARE CURSOR文 定義 EXEC SQL DECLARE <カーソル名> CURSOR FOR <テーブルの定義式> [<順序付け>] 順序付け ORDER BY <並び替えるもののコンマリスト> ASC 昇順 DESC 降順 An Introduction to SQL

47. OPEN句 EXEC SQL OPEN <カーソル名>; • 指定した特定のカーソルを開く • 実際はカーソルに関する表定義式が評価される • 評価した行の集合が使用可能(active)な集合になり、カーソルによってその集合中の位置を特定 →行の最初の前の位置を特定する An Introduction to SQL

48. FETCH句 EXEC SQL FETCH <カーソル名> INTO <親変数のコンマリスト>; • 開かれたカーソルをactiveな集合中の次の行に進め、i番目の値を親変数のi番目に参照された値に割り当てる • FETCHを実行して次に行がない場合、SQLSTATEが02000にセットされる An Introduction to SQL

49. CLOSE句 EXEC SQL CLOSE <カーソル名>; • 開かれている指定されたカーソルを閉じる • 再び開かれる可能性がある • オープンされた地点で評価するため、以前と同じとは限らない(特にカーソル宣言で参照される親変数が変更された場合) • カーソルが開いている間に親変数を変更しても現在activeな集合には影響しない An Introduction to SQL

50. CURRENT書式 • DELETEとUPDATEがある。以下はDELETEの例 EXEC SQL UPDATE S SET STATUS = STATUS + :RAISE WHERE CURRENT OF X; • 現在カーソルXのある行を削除、更新 • CREATE VIEW(Chapter10)によって更新不能なビューを定義しているときは許されない An Introduction to SQL