《SQL必知必会》19-22章ノート

第19章ストレージプロセスの使用

以降の使用のために保存される1つまたは複数のSQL文のセット

は、バッチ

に限定するものではないが、バッチファイルとして扱うことができる.
19.2ストレージ・プロシージャを使用する理由

は、使いやすいユニットに処理を封入することにより、複雑な操作

を簡略化する.

一連の処理手順の繰り返し確立を要求することなく、データの整合性(エラー防止)

が保証される.

変動の管理を簡略化(安全性の保証)

の記憶プロセスは通常コンパイル形式で記憶されるため、DBMSがコマンドを処理するための作業は少ない、性能を向上させる

である.

には、単一のリクエストでのみ使用できるSQL要素と特性があります.ストレージ・プロシージャは、より強力で柔軟なコード

を記述するために使用できます.
簡単に言えば、シンプル、セキュリティ、高性能の3つの利点があります.
19.3ストレージプロセスの実行

EXECUTE AddNewProduct('JTS01','Stuffed Eiffel Tower',6.49,'Plush stuffed toy with the text La Tour Eiffel in red white and blue');

AddNewProductという名前のストレージ・プロシージャを実行し、新しい製品をProductsテーブルに追加します.AddNewProductには、ベンダーID、製品名、価格、説明の4つのパラメータがあります.この4つのパラメータは、ストレージ中の4つの予想変数に一致します.このストレージ・プロシージャは、新しいローをProductsテーブルに追加し、入力したプロパティを対応するカラムに割り当てます.Productsテーブルには、値が必要なカラムProd_がもう1つあることに気づきました.id、テーブルのプライマリ・キーです.なぜこの値をプロパティとしてストレージ・プロシージャに渡さないのですか?このIDを適切に生成することを保証するには、このIDを生成するプロセスを自動化することが望ましい.ストレージ・プロシージャが完了した作業は、次のとおりです.

転送されたデータを検証し、4つのパラメータすべてに

の値があることを保証します.

は、プライマリキーとしての一意のID

を生成する.

新製品をProductsテーブルに挿入し、生成されたプライマリ・キーと転送されたデータを適切なカラムに格納します.

19.4ストレージ・プロシージャの作成
一例として、メール送信リストにメールアドレスを有する顧客をカウントする.

CREATE PROCEDURE MailingListCount（ ListCount OUT INTEGER ） IS v_rows INTEGER;
BEGIN SELECT COUNT(*) INTO v_rows FROM Customers WHERE NOT cust_email IS NULL;
    ListCount :=v_rows;
END;

第20章管理事務処理
20.1取引
取引
トランザクションは、データベースの整合性を維持するために使用されます.これにより、バッチのSQL操作が完全に実行されるか、トランザクションが完全に実行されないかを保証するメカニズムです.バッチで実行する必要があるSQL操作を管理し、データベースに不完全な操作結果が含まれないことを保証します(たとえば、運転中に突然電源が切れ、バッチが予期せず終了します).
トランザクション:SQL文のセット
ロールバック:rollback、指定したSQL文を取り消すプロセスコミット:commit、保存されていないSQL文の結果をデータベーステーブル保持ポイントに書き込む:一時プレースホルダ
INSERT、UPDATE、DELETE文をロールバックできます
CREATE、DROP、SELECTは返品できません(SELECTを返品しても必要ありません)
20.2取引の制御
通常COMMITは変更を保存し、ROLLBACKは取り消しに使用します.
20.2.1 ROLLBACKを使う

DELETE FROM Orders； ROLLBACK；

20.2.2 COMMIT使用
トランザクション・ブロックでは、コミットは暗黙的に行われません.(ただし、異なるDBMSに関連し、暗黙的なコミット処理トランザクション側もある)明確なコミットを行い、COMMIT文を使用します.SQL Serverの例を次に示します.

BEGIN TRANSACTION DELETE OrderItems WHERE order_num = 12345 DELETE Orders WHERE order_num =12345 COMMIT TRANSACTION

この例では、注文12345がシステムから完全に削除する.2つのデータベース・テーブルを削除するように設計されているため、トランザクション・ブロックを使用して注文が部分的に削除されないことを保証します.最後にCOMMIT文は、エラーが発生しない場合にのみ変更を書きます.1つ目がDELETEと呼ばれているが、2つ目のDELETEが失敗した場合、DELETEはコミットされません.
20.2.3予約ポイントの使用
各ポイントに一意の名前を持つプレースホルダを設定します.完全なSQLサーバーの例

BEGIN TRANSACTION INSERT INTO Customers(cust_id,cust_name) VALUES('100000010','Toys Emporium');
SAVE TRANSACTION StartOrder;
INSERT INTO Orders(order_num ,order_date,cust_id) VALUES(20100,'2001/12/1','1000000010') IF @@ERROR <>0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num ,order_item,prod_id,quantity,item_price) VALUES(20100,1,'BR01',100,5.49) IF @@ERROR <>0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num ,order_item,prod_id,quantity,item_price) VALUES(20100,1,'BR03',100,10.99) IF @@ERROR <>0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION

ここでトランザクションブロックには4つのINSERT文が含まれており,変数が0に等しくないと判断すればエラーが発生し,ロールバックが可能である.
第21章カーソルの使用
21.1カーソル
結果セットを返すと、簡単なSELECTでは最初の行、次の行、または最初の10行が得られません.一般的ないくつかのオプションと特性:-カーソルを読み取り専用にマークでき、データの更新と削除ができません-実行可能な指向性操作(前後、第1、最後、絶対位置、相対位置など)を制御できます-一部の列を編集可能にマークでき、一部の列を編集不可にすることができます-カーソルが作成された特定の要求(ストレージ・プロシージャなど)に対してまたはすべてのリクエストにアクセス可能-DBMSが取得したデータ(テーブル内のアクティブなデータを示すのではなく)をコピーし、カーソルのオープンおよびアクセス中にデータが変化しないようにするように指示します.
21.2カーソルの使用
手順:

カーソルを使用できる前に、

を宣言(定義)する必要があります.

宣言後、

を使用(データ取得)するためにカーソルを開く必要があります.

データが記入カーソルに対して、必要に応じて各行

を取り出す(取得する).

カーソルの使用を終了するには、カーソルを閉じる必要があります.また、可能であれば、カーソル

を解放します.
21.2.1カーソルの作成
DECLARE文を使用してカーソルを作成します.たとえば、空席のEメールアドレスを見つけます.

DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers WHERE cust_email IS NULL;

21.2.2カーソルの使用
OPEN CURSOR CustCursorは、カーソルデータにFETCH文でアクセスできるようになりました.FETCHは、どのローを取得するか、どこから取得するか、どこに配置するかを示します.例:カーソルから最初のローを取得する

DECLARE TYPE CustCursor IS REF CURSOR
    RETURN Custmors%ROWTYPE;
DECLARE CustRecord Customers%POWTYPE    
BEGIN OPEN CustCursor FETCH CustCursor INTO CustRecord;
    CLOSE CustCursor;
END;

例2:1行目から最後の行まで検索したデータをループする

DECLARE TYPE CustCursor IS REF CURSOR
    RETURN Custmors%ROWTYPE;
DECLARE CustRecord Customers%POWTYPE    
BEGIN
    OPEN CustCursor
    LOOP
    FETCH CustCursor INTO CustRecord;
    EXIT WHEN CustCursor%NOTFOUND;
    ...
    END LOOP;
    CLOSE CustCursor;
END;

21.2.3カーソルを閉じる
CLOSE CustCursor
第22章高級SQL特性
22.1拘束
22.1.1プライマリ・キー
プライマリ・キーは、カラム(またはカラムのセット)の値が一意であり、変更されないことを保証する特殊な制約です.では、プライマリ・キーにはどのような条件が必要ですか?

任意の2行のプライマリ・キー値が異なる

各行に1つのプライマリ・キー値(すなわちNULL値は許可されない)

がある.

プライマリ・キーを含むカラムは、変更または更新されません.

プライマリ・キー値は再利用できません.テーブルからローが削除された場合、プライマリ・キー値は新しいローに割り当てられません.

テーブルを定義するときにプライマリ・キーを作成できます

CREATE TABLE Vendors ( vend_id CHAR(10) NOT NULL PRIMARY KEY, vend_name CHAR(50) NOT NULL, vend_address CHAR(50) NULL,

表を更新するとき

ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY (vend_id);

22.1.2外部キー
外部キーはテーブルの列で、その値は別のテーブルのプライマリ・キーにある必要があります.外部キーは、参照の整合性を保証する重要な部分です.外部キーの理解方法

    Orders                   。       Customers  。Orders         ID Customers           。  ID Customers    ，         ID。    Orders   ，            。

    Orders     ID           。           ，           ID。  ，Orders     ID      Customers     ID。

          ， Orders   ID        ，      CUstomers     

次は、この外部キーを定義する方法です.

CTEATE TABLE Orders
(.......
REFERENCES Customers(cust_id)
);

表定義には、cust_を表すREFERENCESキーワードが使用されます.idの値はすべてCustomersテーブルのcust_でなければなりませんidの値.同じ仕事もALTER TABLEと劇中でCONTRAINT文法を使って完成できます.

ALTER TABLE Orders ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

22.1.3一意の拘束
ユニークコンストレイントは、カラム内のデータがユニークであることを保証するために使用されます.プライマリ・キーに似ていますが、いくつかの違いがあります.

テーブルには複数の一意の制約を含めることができますが、各テーブルには1つのプライマリ・キー

のみを含めることができます.

一意制約列は、NULL値

を含むことができる

一意制約列は、

を変更または更新することができる.

一意制約カラムの値は、

を繰り返し使用することができる.

はプライマリ・キーとは異なり、一意の制約列は外部キー

を定義するために使用できません.
22.1.4制約の確認
チェックコンストレイントは、カラム(またはカラムのセット)内のデータが指定した条件のセットを満たすことを保証します.チェックコンストレイントの一般的な用途は、次の点です.

最小値または最大値(例えば0品目の注文防止)

をチェックする.

は範囲を指定します.たとえば、出荷日が今日の

以上であることを保証します.

は、特定の値のみを許可します.例えば、性別はMまたはFのみを許可する.

次の例はコンストレイントチェックです

CREATE TABLE OrderItems ( .......... quantity INTEGER NOT NULL CHECK (quantity>0), )

これにより、挿入または更新されたローがチェックされ、0より大きいことが保証されます.チェックgenderにはMまたはFのみが含まれています

ADD CONSTRAINT CHECK (gender LIKE '[MF]')

22.2インデックス
インデックスはなぜですか?データをソートして、検索とソート操作を高速化します.

インデックスは、検索操作のパフォーマンスを向上させるが、データの挿入、変更、削除のパフォーマンスを低下させる.これらの動作を実行する場合、DBMSはインデックス

を動的に更新する必要がある.

インデックスデータは、大量の記憶領域を占有する場合がある

.

は、すべてのデータがインデックスに適しているわけではありません.一意性の悪いデータがインデックスから得られるメリットは、より多くの可能な値を有するデータがインデックスから得られるメリットよりも

多い.

インデックスは、データフィルタリングおよびデータソートのために使用され、データが常に特定の順序でソートされる場合、インデックスの代替

である可能性がある.

は、複数のカラム

をインデックス内で定義することができる.
Productsテーブルの製品名に簡単なインデックスを作成する

CREATE INDEX prod_name_ind ON PRODUCTS (prod_name);

22.3トリガ
トリガの一般的な用途トリガは、特定のデータベース・アクティビティが発生したときに自動的に実行される特別なストレージ・プロシージャです.

データの一致を保証する(例えば大文字と小文字の自動変換)

あるテーブルの変動に基づいて他のテーブルでアクティビティ

を実行する.

は、追加の検証を行い、必要に応じてデータ

をロールバックする.

計算カラムの値または更新タイムスタンプ

簡単な例では、すべてのINSERTとUPDATE操作に対して、Customersテーブルのcust_state列を大文字に変換

CREATE TRIGGER customer_state ON Customers FOR INSERT,UPDATE AS UPDATE Customers SET cust_state = Upper(cust_state) WHERE Customers.cust_id = inserted.cust_id;

一般的なコンストレイントはトリガブロックよりも大きいので、可能な限りコンストレイントを使用する必要があります.