更改和訪問數據
更改數據
當設計一個數據庫應用程序時，數據更改是需要理解的一個關鍵過程。它取決於以下幾個因素：
數據模型和元數據（必須處理什麼樣的編目數據、類型、限制和檢查？）
業務需求（需要如何識別和修改數據庫中的數據？）
用戶、表和列級別上的權限和安全性（特定的更改是否被允許？）
訪問數據的接口（如何與更改的數據進行交互？）
在應用程序的設計中，應該使用 DB2 的哪些功能？用戶是不能修改系統編目數據的。編目表和視圖存儲關於數據的邏輯和物理定義的元數據。SYSIBM 模式中包含一些表，而這些表的視圖則屬於 SYSCAT 模式。通過查詢編目，可以獲得有用的信息。為了做出恰當的選擇，需要同時考慮應用程序的數據庫設計和目標環境。例如，可以選擇在數據庫設計中實施某些業務規則，而不是在應用程序中加入相應的邏輯。
所使用的功能以及對這些功能的使用程度會有很大的不同。需要考慮的功能包括：
訪問數據時使用：
嵌入式 SQL，包括嵌入式 SQL for Java (SQLJ)
DB2 Call Level Interface (DB2 CLI)、Open Database Connectivity (ODBC) 和 JDBC
Microsoft 規范
Perl DBI
查詢產品
控制數據值時使用：
數據類型（內置或用戶定義）
表檢查約束
參照完整性約束
使用 CHECK OPTION 的視圖
應用程序邏輯和變量類型
控制數據值之間的關系時使用：
參照完整性約束
觸發器
應用程序邏輯
執行程序時使用：
存儲過程
用戶定義函數
觸發器
將以數據為焦點的邏輯從應用程序轉移到數據庫的關鍵優勢是，應用程序變得更加獨立於數據。圍繞著數據的邏輯集中在一個地方，即數據庫。這意味著您只需更改一次數據或數據邏輯，就可以立即影響依賴於該數據的所有應用程序。
雖然後一個優點非常強大，但是必須考慮到，放入數據庫中的數據邏輯會同等地影響數據的所有用戶。您必須考慮施加在數據上的規則和約束是適用於數據的所有用戶，還是只適用於單個應用程序的用戶。
應用程序的需求也可能有助於決定在數據庫中還是在應用程序中施加規則。例如，可能需要在一個特定訂單的數據輸入階段處理有效性錯誤。通常，這種類型的數據驗證應該在應用程序編程階段進行。另外，還應該考慮應用程序所在的計算環境。您需要考慮在客戶端機器上執行邏輯與在數據庫服務器上運行邏輯之間的不同，在數據庫服務器上運行邏輯時要使用存儲過程，或者用戶定義函數 （UDF），或者結合使用兩者，這種方式一般來說要更強大一些。在某些情況下，正確的方法是在應用程序（也許是由於特定於應用程序的需求）和數據庫（也許是由於應用程序之外的其他交互用途）中都加入規則。
訪問數據
在關系數據庫中，必須使用 SQL 來訪問所需的數據。不過，可以選擇將 SQL 集成到應用程序中所采用的方式。您可以從以下接口以及它們所支持的語言中做出選擇：
嵌入式 SQL
C/C++
COBOL
FORTRAN
Java® 語言（通過 SQLJ 或 JDBC）
REXX
DB2 CLI 和 ODBC
Microsoft 規范，包括 ADO.Net 和 OLE DB
Visual Basic、Visual C++ 和 .Net 語言
Perl DBI
Perl
PHP
諸如 Lotus Approach、IBM Query Management Facility、Microsoft Access 或 Microsoft Excel 之類的查詢產品
在運行任何可執行 SQL 語句之前，程序必須建立到目標數據庫服務器的連接。該連接標識運行程序的用戶的授權 ID 和程序所使用的數據庫服務器的名稱。通常，應用程序進程一次只能連接到一個數據庫。這個服務器被稱作當前服務器。不過，在多站點更新環境中，應用程序可以連接到多個數據庫服務器。在這種情況下，只有一個服務器能作為當前服務器。
程序在建立與一個數據庫服務器的連接時，可以顯式地使用一條連接語句，也可以隱式地連接到缺省的數據庫服務器。Java 應用程序還可以通過一個 Connection 實例建立連接。
跨多個表查詢數據庫
可以使用 SELECT 語句從一個或多個表中查詢數據。為了訪問所查詢的數據，您需要適當的權限。查詢返回的數據被稱為結果集。
SELECT 語句只指定結果集所需獲取的數據應滿足的標准。它並不指定 DB2 返回數據的方式。DB2 優化器會根據來自系統編目表的當前數據庫統計信息，以及需要考慮使用的計劃類型，構建一個訪問計劃，從而決定采用何種方式返回數據。
現在來看一些 SELECT 語句的樣例。下面的語句從 store 和 product 表中選擇所有商店名和產品名：
SELECT A.STORE_NAME, B.PRODUCT_NAME FROM STORE A, PRODUCT B
Store_name 是 store 表中的一列。Product_name 是 product 表中的一列。
現在看看另一個例子。在 employee 表中，我們將選擇最高薪水少於所有其他部門平均薪水的部門的部門編號（WORKDEPT）和部門最高薪水（SALARY）：
SELECT WORKDEPT, MAX(SALARY)
FROM EMPLOYEE EMP_COR
GROUP BY WORKDEPT
HAVING MAX(SALARY) < (SELECT AVG(SALARY)
FROM EMPLOYEE
WHERE NOT WORKDEPT = EMP_COR.WORKDEPT)
使用 MQT
MQT 的定義基於查詢的結果。MQT 可以顯著提高查詢的性能。本教程將介紹 MQT、總結表（summary table）和 staging 表，並通過一些實用的例子展示如何創建和使用物化查詢表。
MQT 是基於查詢的結果定義的一個表。MQT 中包含的數據來自 MQT 定義所基於的一個或多個表。總結表（也稱自動總結表[AST]）對於 IBM DB2 for Linux, UNIX, and Windows 的用戶來說應該感到比較熟悉，它們可以看作是特殊的 MQT。fullselect 是總結表定義的一部分，它包含一個 GROUP BY 子句，該子句總結 fullselect 中所引用表中的數據。
您可以將 MQT 看作一種物化的視圖。視圖和 MQT 都是基於一個查詢來定義的。每當視圖被引用時，視圖所基於的查詢便會運行。但是，MQT 實際上會將查詢結果存儲為數據，您可以使用 MQT 中的這些數據，而不是使用底層表中的數據。MQT 可以顯著提高查詢的性能，尤其是提高復雜查詢的性能。如果優化器確定查詢或查詢的一部分可以用一個 MQT 來解決，那麼查詢就可以被重寫以便利用 MQT。MQT 可以在創建表時定義，可以定義為由系統維護，也可以定義為由用戶維護。
這種 MQT 中的數據是由系統維護的。當創建這種類型的 MQT 時，可以指定表數據是 REFRESH IMMEDIATE 還是 REFRESH DEFERRED。通過 REFRESH 關鍵字可以指定如何維護數據。DEFERRED 的意思是，表中的數據可以在任何時候通過 REFRESH TABLE 語句來刷新。系統維護的 MQT，不管是 REFRESH DEFERRED 類型的還是 REFRESH IMMEDIATE 類型的，對它們的插入、更新或刪除操作都是不允許的。但是，對於 REFRESH IMMEDIATE 類型的系統維護的 MQT，可以通過 對底層表的更改（即插入、更新或刪除操作）來更新。
下面的小節將展示一個創建 REFRESH IMMEDIATE 類型的系統維護的 MQT 的例子。這個表名為 EMP，它基於 SAMPLE 數據庫中的底層表 EMPLOYEE 和 DEPARTMENT。由於 REFRESH IMMEDIATE MQT 要求來自查詢內引用的每個表至少有一個惟一鍵要出現在 select 列表中，所以我們首先在 EMPLOYEE 表的 EMPNO 列上定義一個惟一性約束，另外還要在 DEPARTMENT 表的 DEPTNO 列上定義一個惟一性約束。DATA INITIALLY DEFERRED 子句的意思是，在執行 CREATE TABLE 語句的時候，並不將數據插入到表中。MQT 被創建好之後，就會處於檢查暫掛（check pending）狀態，在對它執行 SET INTEGRITY 語句之前，不能查詢它。IMMEDIATE CHECKED 子句指定，必須根據用於定義該 MQT 的查詢對數據進行檢查，並刷新數據。NOT INCREMENTAL 子句指定對整個表進行完整性檢查。
CONNECT TO SAMPLE
...
ALTER TABLE EMPLOYEE ADD UNIQUE (EMPNO)
ALTER TABLE DEPARTMENT ADD UNIQUE (DEPTNO)
CREATE TABLE EMP AS (SELECT E.EMPNO, E.FIRSTNME, E.LASTNAME, E.PHONENO, D.DEPTNO,
SUBSTR(D.DEPTNAME, 1, 12) AS DEPARTMENT, D.MGRNO FROM EMPLOYEE E, DEPARTMENT D
WHERE E.WORKDEPT = D.DEPTNO)
DATA INITIALLY DEFERRED REFRESH IMMEDIATE
SET INTEGRITY FOR EMP IMMEDIATE CHECKED NOT INCREMENTAL
函數和表達式
什麼是 DB2 SQL 函數？
數據庫函數 是一組輸入數據值與一組結果值之間的一種關系。有兩種類型的函數：內置（built-in）函數和用戶定義（user-defined）函數。
內置 SQL 函數是數據庫管理器提供的。它們提供單一的結果值，被標識為 SYSIBM 模式的一部分。內置 SQL 函數的例子包括 AVG 之類的列函數、+ 之類的操作符函數、DECIMAL 之類的類型覆蓋函數以及其他函數，比如 SUBSTR。
用戶定義函數（UDF）是在 SYSCAT.FUNCTIONS 中注冊到數據庫的函數（使用 CREATE FUNCTION 語句。UDF 決不不是 SYSIBM 模式的一部分。數據庫管理器在一個名為 SYSFUN 的模式中提供了那樣一組函數。
DB2 允許用戶和應用程序開發人員將他們自己的函數定義應用於數據庫引擎中，以擴展數據庫系統的功能。與從數據庫中檢索行，然後在所檢索到的數據上應用那些函數的應用程序相比，基於 UDF 的應用程序有更好的性能。通過擴展數據庫函數還可以讓數據庫在應用程序所使用的引擎中使用相同的函數，從而加強應用程序與數據庫之間的協作。函數的使用有助於提高應用程序開發人員的生產率，因為這樣更接近面向對象的思想。例如，您可能以美元為單位存儲一個產品的價格，但是又希望某個應用程序以英鎊為單位引用該價格。那麼可以使用一個函數來解決這個問題：
SELECT UNIT_PRICE, CURRENCY('UK',UNIT_PRICE) FROM PRODUCT WHERE PRODUCT_ID = ?
FENCED 和 NOT-FENCED 模式
可以用 C/C++、Java 語言或 OLE 創建函數。函數可以以 FENCED 或 NOT-FENCED 模式運行。在遷移到 NOT-FENCED 模式之前，應該用 FENCED 模式開發函數。NOT-FENCED 進程更快一些，因為它使用 DB2 代理內存，而 FENCED 進程在它自己的 db2udf 進程中運行。FENCED 進程使用共享內存與調用代理通信。FENCED 函數存儲在 sqllib/function 中，而 NOT-FENCED 則存儲在 sqllib/unfenced 中。
DB2 提供的 SQL 函數
現在來看一些 SQL 函數的例子。第一個例子從一個表中選擇所有書的標題和價格。如果該書的價格為 NULL，則將其價格顯示為 0.00。
SELECT TITLE, COALESCE(PRICE, 0.00) AS PRICE
FROM TITLES;
接下來的例子返回的結果是公司的名稱以及公司名稱中所含的字符數：
SELECT COMPANYNAME, LENGTH(COMPANYNAME)
FROM CUSTOMERS
現在看看如何返回每個作者的名字（first name）最右邊的 5 個字符：
SELECT RIGHT(AU_FNAME, 5)
FROM AUTHORS
下一個例子使用 project 表，將宿主變量 AVERAGE (decimal(5,2)) 設置為名為 D11 的部門（DEPTNO）中項目的平均工資水平（PRSTAFF）。
SELECT AVG(PRSTAFF)
INTO :AVERAGE
FROM PROJECT
WHERE DEPTNO = 'D11'
DB2 SQL Reference 手冊中還定義了很多其他的 SQL 函數。如果 DB2 沒有提供適當的函數，您總是可以編寫自己的 SQL 函數。
使用公共表表達式
公共表表達式 是一個本地臨時表，可以在一條 SQL 語句中引用多次。這個臨時表只能存在於定義它的 SQL 語句的生命周期內。每次公共表表達式被引用時，其結果都是相同的。臨時表是在 SQL 語句中用 WITH 子句定義的。下面是具體的語法：
WITH <COMMON NAME1> AS ( <SELECT EXPRESSION>), <COMMON NAME2>
AS (<SELECT EXPRESSION), & SELECT <COLUMN> FROM <TABLE_NAME> <WHERE_CLAUSE>
<table_name> 是數據庫中的一個表，也可以是由一個包括 WITH 子句的 SQL 語句定義的 <Common name>。 下面是一個例子：
WITH PROD_QUANTITY AS
(SELECT PRODUCT_ID, SUM (QUANTITY) AS QUANTITY
FROM CUSTOMER_ORDER_ITEM
GROUP BY PRODUCT_ID),
TOTALS AS
(SELECT -1 AS PRODUCT_ID, SUM(QUANTITY) AS TOTAL)
SELECT PRODUCT_ID, QUANTITY
FROM PROD_QUANTITY
UNION
SELECT PRODUCT_ID, TOTALS
FROM TOTALS
ORDER BY 1 DESC
在上述例子中，prod_quantity 被定義為一個公共表表達式。它與一個名為 totals 的公共表表達式一起使用。最終的 SELECT 語句將從兩個公共表表達式中進行選擇。
下面看另一個例子：
WITH
PAYLEVEL AS
(SELECT EMPNO, EDLEVEL, YEAR(HIREDATE) AS HIREYEAR,
SALARY+BONUS+COMM AS TOTAL_PAY
FROM EMPLOYEE
WHERE EDLEVEL > 16),
PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS
(SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)
FROM PAYLEVEL
GROUP BY EDLEVEL, HIREYEAR)
SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, DECIMAL(AVG_TOTAL_PAY,7,2)
FROM PAYLEVEL, PAYBYED
WHERE EDLEVEL = EDUC_LEVEL
AND HIREYEAR= YEAR_OF_HIRE
AND TOTAL_PAY < AVG_TOTAL_PAY
這個公共表表達式包含了 PAYLEVEL。這個結果表包括員工編號、該員工被雇年份、該員工的總工資以及該員工的受教育程度。只需要包括受教育程度大於 16 的那些員工的記錄。
該列表還包括一個名為 PAYBYED（“pay by education” 的縮寫）的公共表表達式。它通過使用 PAYLEVEL 表來確定受教育程度、被雇年份和同年被雇的具有同等受教育程度的員工的平均工資。由這個表中得到的列的列名（例如 EDUC_LEVEL）與選擇列表中使用的列名不同。
最後，我們得到能夠產生所需結果的實際查詢。該查詢連接兩個表（PAYLEVEL、PAYBYED），確定工資低於同年雇用的所有員工平均工資的員工。注意，PAYBYED 是基於 PAYLEVEL 的，所以在整個語句中，PAYLEVEL 實際上被訪問了兩次。在這兩次中，計算查詢時都用到了同一組行。
在定義一個公共表表達式之後，就可以像使用其他表一樣 SQL 語句中使用它。可以任意次地使用公共表表達式。甚至可以在之前創建的公共表表達式的基礎上，再創建一個公共表表達式。
在 SQL 程序中何時使用游標
什麼是游標？何時需要游標？
游標（cursor）是用於操縱 DB2 查詢返回的多行結果集的一種機制。 在以下兩種情況下，需要使用游標：
查詢返回不止一行。
含 INTO 子句的 SELECT 語句很容易編寫，但是這種語句只允許返回一行。在一條 SELECT 語句中不存在允許直接處理多個行的子句，所以必須使用游標。
當您想要更新或刪除一個或多個行，但是首先需要檢查它們的內容時，需要使用游標。
更新或刪除行的最簡單的方法是使用像下面這樣的語句：
UPDATE staff SET salary = salary * 1.10 WHERE id = 100
或者
DELETE FROM staff WHERE id = 100
然而，這些叫做 searched 更新或刪除的語句，在更新或刪除之前不允許程序檢查行的內容。這時可以考慮使用一個游標，結合使用一條 positioned 更新或刪除語句。後一種方法也稱 Update Where Current Of 和 Delete Where Current Of，其中 Current Of 是指游標當前所處在的行。
要使用游標，首先必須聲明它，然後打開它，接著從中獲取行，每次只能獲取一行，再（可選地）更新或刪除游標所處在的那一行，最後關閉該游標。在本節和後面的小節中，可以看到更多細節和例子。
游標支持如何隨 DB2 接口而變
游標支持和術語會因 DB2 編程接口的不同而不同。讓我們簡略地看一下這些不同點。然後，在 操縱游標 小節中，還可以看到嵌入式 SQL 的一些例子。
對游標的基本支持是由 SQL 語言本身通過 DECLARE CURSOR、OPEN、FETCH 和 CLOSE 語句提供的。
通過以下語法，可以執行 positioned 更新和刪除：
UPDATE [OR DELETE] ... WHERE CURRENT OF <CURSOR NAME>
不同的接口以不同的方式建立 SQL 游標支持。傳統編程語言，例如 C、C++ 和 COBOL，支持在靜態和動態嵌入式 SQL 中顯式地聲明和使用游標。在這些語言中，能單向地一次一行地對行進行處理。
SQL 過程語言對游標的支持非常類似於 C，不過它增加了 WITH RETURN 子句，以支持將結果集返回給存儲過程的調用者。
在 DB2 CLI 中，游標不是顯式地聲明的，當調用 SQLExecute() 或 SQLExecDirect() 函數時，CLI 自動創建游標。在游標支持的基礎上，CLI 提供了附加的功能，例如向後滾動的功能，一次檢索一組行的功能，以及按多行向前移動的功能。
在 JDBC 中，游標是在創建 ResultSet 對象時自動創建的。JDBC 也提供了類似於 CLI 的附加功能。
SQLJ 的游標支持實際上是 JDBC 和 SQL 中游標支持的混合體，但是在 SQLJ 中，游標的等同物被稱作迭代器（iterator）。
一些交互式工具，例如 DB2 Command Line Processor (CLP) 和 Control Center，不允許直接使用游標。但是，這些工具本身會使用游標。當通過 CLP 執行一條 SELECT 語句時，或者在 Control Center 中向一個表請求樣本內容時，都要使用游標來返回行。
一個簡單的例子：游標在靜態嵌入式 SQL 中的使用
在更深入地討論游標之前，首先看看靜態嵌入式 SQL（一個 C 程序）中的一個非常簡單的游標。
EXEC SQL DECLARE C0 CURSOR FOR SELECT DEPTNUMB, DEPTNAME FROM ORG;
EXEC SQL OPEN C0;
EXEC SQL FETCH C0 INTO :DEPTNUMB, :DEPTNAME;
WHILE (SQLCA.SQLCODE != 100) /* CONTINUE UNTIL THE END OF THE RESULT SET */
{
PRINTF(" %8D %-14SN", DEPTNUMB, DEPTNAME);
EXEC SQL FETCH C0 INTO :DEPTNUMB, :DEPTNAME;
}
EXEC SQL CLOSE C0;
這段代碼打印 org 表中每個部門的編號和名稱。DECLARE CURSOR 語句提供所使用的查詢，OPEN 語句准備查詢的結果集。FETCH 語句重復地用於將結果集中各列的值移到程序變量中，一次移一行，直到到達結果集的結尾處 (SQLCODE = +100)，此時游標關閉。
識別游標的類型
游標的特征
游標有三個主要特征：
游標的類型（type）：只讀、可更新或模糊（ambiguous）
游標的方向（direction）：前向或可滾動
游標的作用域（scope）
這些特征將在接下來的幾個小節中詳細討論。
游標的類型
DB2 對三種游標類型的處理有所不同，這些不同主要體現在性能方面。我們來看看每種類型。
