您當前位置：首頁 > php開源 > 綜合技術 > 《SQL必知必會》19-22章筆記

《SQL必知必會》19-22章筆記

來源：程序員人生發布時間：2016-07-09 13:07:59 閱讀次數：2998次

第19章使用存儲進程

為以后的使用而保存的1條或多條SQL語句的集合
可將其視為批文件，雖然它們的作用不但限于批處理

19.2為何要使用存儲進程

通過把處理封裝在容易使用的單元中，簡化復雜的操作
由于不要求反復建立1系列處理步驟，保證了數據的1致性（避免毛病）
簡化對變動的管理（保證安全性）
存儲進程通常以編譯過的情勢存儲，所以DBMS為處理命令所做的工作較少，提高性能
存在1些只能用在單個要求中的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');

履行1個名為AddNewProduct的存儲進程，將1個新產品添加到Products表中。AddNewProduct有4個參數，分別是：供應商ID、產品名、價格和描寫。這4個參數匹配存儲進程中的4個預期變量。此存儲進程將新行添加到Products表，并將傳入的屬性賦給相應的列。我們注意到，在Products表中還有另外1個需要值的列Prod_id，它是表的主鍵。為何不把這個值作為屬性傳遞給存儲進程？要保證恰當地生成此ID，最好是使生成此ID的進程自動化。
以下是存儲進程完成的工作：

驗證傳遞的數據，保證所有4個參數都有值
生成用作主鍵的唯1ID
將新產品插入到Products表，在適合的列總存儲生成的主鍵和傳遞的數據。

19.4創建存儲進程

1個例子，對郵件發送清單中具有郵件地址的顧客進行計數。

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操作要末完全履行，要末完全不履行
事務處理是1種機制，用來管理必須成批履行的SQL操作，以保證數據庫不包括不完全的操作結果（好比運行進程中突然斷電了，致使批處理意外終止）

事務：指1組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語句。下面是1個SQL Server的例子：

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

在這個例子中，從系統中完全刪除定單12345.由于設計刪除兩個數據庫表，用事務處理塊來保證定單不被部份刪除。最后COMMIT語句僅在不出錯時寫出更改。如果第1叫DELETE起作用，但第2條DELETE失敗，則DELETE不會提交。

20.2.3使用保存點

設置占位符，每一個點有唯1的名字。
完全的SQL Server例子

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沒法得到第1行、下1行或前10行。
常見的1些選項和特性：
- 能夠標記游標為只讀，數據不能更新和刪除
- 能控制可以履行的定向操作（向前、向后、第1、最后、絕對位置、相對位置等）
- 能標記某些列為可編輯的，某些列為不可編輯的
- 規定范圍，使游標對創建它的特定要求（如存儲進程）或對所有要求可訪問
- 唆使DBMS對檢索出的數據（而不是指出表中活動數據）做復制，使在游標打開和訪問期間數據不變化

21.2使用游標

步驟：

在能夠使用游標前，必須聲明（定義）它
1旦聲明后，必須打開游標以供使用（數據檢索出來）
對填有數據的游標，根據需要取出（檢索）各行
在結束游標使用時，必須關閉游標，而且可能的話，釋放游標

21.2.1創建游標

使用DECLARE語句創建游標。
例子，找出空缺的電子郵件地址

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

21.2.2使用游標

OPEN CURSOR CustCursor
現在可以用FETCH語句訪問游標數據了，FETCH指出要檢索哪些行，從何處檢索它們和將它們放于何處。
例子：從游標中檢索第1行

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行到最后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主鍵

主鍵是1種特殊的束縛，用來保證1列（或1組列）中的值是唯1的，而且永不改動。
那末主鍵需要甚么條件呢？

任意兩行的主鍵值都不相同
每行都具有1個主鍵值（即不允許NULL值）
包括主鍵的列從不修改或更新。
主鍵值不能重用。如果表中刪除某1行，其主鍵值不分配給新行。

在定義表的時候可以創建主鍵

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外鍵

外鍵是表中的1列，其值必須在另外一表的主鍵中。外鍵是保證援用完全性的及其重要的部份。
如何理解外鍵？

    Orders表將錄入系統的每一個定單作為1行包括其中。顧客信息存儲在Customers表中。Orders表中的定單通過顧客ID與Customers表中的特定的行進行關聯。顧客ID為Customers表的主鍵，每一個顧客都有唯1的ID。定單號為Orders的主鍵，每一個定單都有唯1的定單號。

    Orders表中的顧客ID列的值其實不1定是唯1的。如果某個顧客有多個定單，則有多個行有相同的顧客ID。同時，Orders表中的顧客ID列的合法值為Customers表中的顧客ID。

    在這個例子中，在Orders的顧客ID列上定義1個外鍵，因此只能接受CUstomers表的主鍵值

下面是定義這個外鍵的方法

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

其中表定義使用了REFERENCES關鍵字，它表示cust_id中的任何值都必須是Customers表中的cust_id的值。
相同的工作也能在ALTER TABLE與劇中使用CONSTRAINT語法來完成：

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

22.1.3唯1束縛

唯1束縛用來保證1列中的數據是唯1的。它們類似主鍵，但是又有1些區分。

表可以包括多個唯1束縛，但每一個表只允許包括1個主鍵
唯1束縛列可包括NULL值
唯1束縛列可修改或更新
唯1束縛列的值可重復使用
與主鍵不1樣，唯1束縛列不能用來定義外鍵

22.1.4檢查束縛

檢查束縛用來保證1列（或1組列）中的數據滿足1組指定的條件。檢查束縛的常見用處有以下幾點

檢查最小或最大值（例如避免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必須動態地更新索引
索引數據可能要占用大量的存儲空間
并不是所有數據都合適于索引。唯1性不好的數據從索引得到的好處不如具有更多可能值的數據從索引得到的好處多
索援用于數據過濾和數據排序，如果常常以某種特定的順序排序數據，則該數據多是索引的備選
可以在索引中定義多個列

在Products表的產品名列上創建1個簡單的索引

CREATE INDEX prod_name_ind
ON PRODUCTS (prod_name);

22.3觸發器

觸發器的常見用處
觸發器是特殊對存儲進程，它在特定的數據庫活動產生時自動履行。

保證數據1致（如大小寫自動轉換）
基于某個表的變動在其他表上履行活動
進行額外的驗證并根據需要回退數據
計算計算列的值或更新時間戳

1個簡單的例子，它對所有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;

1般束縛比觸發器塊，因此可能的時候，應當盡量使用束縛。

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
程序員人生

------分隔線----------------------------

上一篇 iOS 簡單實用的音樂播放器，少年，自己做個歌單吧。。。。。。

下一篇 添物零基礎到大型全棧架構師服務器架構及實戰（架構篇）- 概述

分享到: