SQL入門經典：第八部分在實際工作中應用SQL知識_斯蒂芬森

第22章高級SQL主題

第23章 SQL擴展到企業、互聯網和內部網

第24章標準SQL的擴展

第22章高級SQL主題

本章的重點包括：

什麼是光標

使用存儲過程

什麼是觸發器

動態SQL基礎

使用SQL生成SQL

直接SQL與嵌入SQL

調用級接口

前面的章節介紹了SQL的一些基本操作，比如從數據庫查詢數據、創建數據庫結構、操作數據庫裡的數據，現在我們來介紹一些高級SQL主題，內容包括光標、存儲過程、觸發器、動態SQL、直接SQL與嵌入SQL、SQL生成SQL。很多SQL實現都支持這些高級特性，增強了SQL的功能。

注意：某些主題不屬於ANSI SQL

某些主題並不都屬於ANSI SQL，所以其實際語句和規則要取決於具體實現。本章會介紹一些主要廠商的語法以供比較。

22.1 光標

通常，數據庫操作被認為是以數據集為基礎的操作。這就意味著，大部分ANSI SQL命令是作用於一組數據的。但是，光標則被用於通過以記錄為單位的操作，來獲得數據庫中數據的子集。因此，程序可以依次對光標裡的每一行進行求值。光標一般用於過程化程序裡嵌入的SQL語句。有些光標是由數據庫服務程序自動隱含創建的，有些是由SQL程序員定義的。每個SQL實現裡對光標用法的定義是不同的。

下面介紹本書中一直在應用的 3個流行SQL實現的範例：MySQL、SQL Server和Oracle。

MySQL裡對光標的聲明語法如下所示：

SQL Server裡對光標的聲明語法如下所示：

Oracle的語法如下：

下面的光標包含了表EMPLOYEE_TBL全部記錄的子集：

根據ANSI標準，在光標被創建之後，可以使用如下操作對其進行訪問。

OPEN：打開定義的光標。

FETCH：從光標獲取記錄，賦予程序變量。

CLOSE：在對光標的操作完成之後，關閉光標。

22.1.1 打開光標

要使用光標，必須首先打開光標。當光標被打開時，指定光標的SELECT語句被執行，查詢的結果被保存在內存裡的特定區域。

在MySQL和Microsoft SQL Server中打開一個光標的語法如下：

在Oracle裡的語法如下：

下面的範例會打開光標EMP_CURSOR：

22.1.2 從光標獲取數據

在光標打開之後，我們可以使用FETCH語句獲取光標的內容（查詢的結果）。

在SQL Server裡，FETCH語句的語法如下所示：

在Oracle裡的語法如下：

MySQL裡的語法如下：

下面的FETCH語句把光標EMP_CURSOR裡的內容獲取到變量EMP_RECORD：

FETCH EMP_CURSOR INTO EMP_RECORD

在從光標獲得數據時，需要注意可能會到達光標末尾。不同的實現使用不同的方法來解決這個問題，從而避免用戶在關閉光標的時候產生錯誤。下面是一些偽代碼實例，顯示了MySQL、Microsoft SQL Server和Oracle如何處理這種情況，幫助讀者理解光標的處理過程。

MySQL中的語法如下：

Microsoft SQL Server中的語法如下：

Oracle中的語法如下：

22.1.3 關閉光標

光標可以打開，當然就可以關閉。在光標關閉之後，程序就不能再使用它了。關閉光標是相當簡單的。

下面是SQL Server裡關閉和釋放光標的語法：

在Oracle裡，當光標被關閉之後，不必使用DEALLOCATE語句就可以釋放資源和姓名。其語法如下：

MySQL的光標也是這樣，不必使用DEALLOCATE語句。其語法如下：

注意：高級特性在不同實現間的差別很大

從前面的範例可以看出，不同實現之間的差別很大，特別是高級特性和 SQL擴展（詳情請見第24章）。關於光標使用的詳細情況請參見具體實現的文檔。

22.2 存儲過程和函數

注意：釋放光標所佔據的資源

關閉光標並不一定意味著會釋放它所佔據的內存空間。在某些實現裡，光標佔用的內存必須使用DELLOCATE語句才能解除分配。當光標被解除分配時，相關聯的內存被釋放，而光標的名稱可以被再次使用。而在某些實現裡，當光標被關閉時，內存會被隱含地解除分配。當光標佔據的內容被釋放之後，它們可以用於其他操作，比如打開另一個光標。

存儲過程是一組相關聯的SQL語句，通常被稱為函數和子程序，能夠讓程序員更輕鬆和靈活地編程。這是因為存儲過程與一系列單個SQL語句相比更容易執行。存儲過程可以嵌套在另一個存儲過程裡，也就是說存儲過程可以調用其他存儲過程，後者又可以調用另外的存儲過程，依此類推。

利用存儲過程可以實現過程化編程。基本的SQL DDL（數據定義語言）、DML（數據管理語言）和 DQL（數據查詢語言）語句（CREATE TABLE、INSERT、UPDATE、SELECT等）只是告訴數據庫需要做什麼，而不是如何去做。而通過對存儲過程進行編程，我們就可以告訴數據庫發動機如何處理數據。

存儲過程是保存在數據庫裡的一組SQL語句或函數，它們被編譯，隨時可以被數據庫用戶使用。存儲函數與存儲過程是一樣的，但函數可以返回一個值。

函數由過程調用。當函數被過程調用時也可以傳遞參數，函數會進行所需要的計算，並且把一個值返回給調用它的過程。

當存儲過程被創建之後，組成它的各種子程序和函數都保存在數據庫裡。這些存儲過程經過了預編譯，可以隨時由用戶調用。

下面是MySQL創建存儲過程的語法：

下面是SQL Server創建存儲過程的語法：

Oracle的語法如下所示：

下面是一個很簡單的存儲過程，它在表PRODUCTS_TBL裡插入一行新記錄：

SQL Server裡執行存儲過程的語法如下：

下面是Oracle的語法：

下面是MySQL的語法：

注意：基本SQL命令往往是相同的

可以看出，不同 SQL 實現裡對過程進行編程的語法有很大的差別。在不同的SQL實現裡，基本的SQL命令應該是相同的，但編程概念（變量、條件語句、光標、循環）可能會有很大不同。

現在執行前面創建的過程：

與單個SQL語句相比，存儲過程具有一些明顯的優點，包括：

存儲過程的語句已經保存在數據庫裡了；

存儲過程的語句已經被解析過，以可執行格式存在；

存儲過程支持模塊化編程；

存儲過程可以調用其他存儲過程和函數；

存儲過程可以被其他類型的程序調用；

存儲過程通常具有更好的響應時間；

存儲過程提高了整體易用性。

22.3 觸發器

觸發器是數據庫裡編譯了的SQL過程，基於數據庫裡發生的其他行為來執行操作。它是存儲過程的一種，會在特定 DML 行為作用於表格時被執行。它可以在 INSERT、DELECT或UPDATE語句之前或之後執行，可以在這些語句之前檢查數據完整性，可以回退事務，可以修改一個表裡的數據，可以從另一個數據庫的表裡讀取數據。

在大多數情況下，觸發器都是很不錯的函數，但它們會導致更多的I/O開銷。如果使用存儲過程或程序能夠在較少開銷下完成同樣的工作，就應該盡量不使用觸發器。

22.3.1 CREATE TRIGGER語句

這個語句用於創建觸發器。

ANSI標準語法是：

MySQL裡使用觸發器的語法是：

SQL Server裡創建觸發器的語法是：

Oracle的基本語法是：

下面是使用Oracle語法編寫的一個觸發器範例：

前面的範例創建了一個名為EMP_PAY_TRIG的觸發器，每當表EMPLOYEE_PAY_TBL裡的記錄被更新時，它就會在表EMPLOYEE_PAY_HISTORY裡插入一條記錄。

注意：觸發器的內容不能修改

觸發器的內容是不能修改的。想要修改觸發器，我們就只能替換它或重新創建它。有些實現允許使用CREATE TRIGGER語句替換已經存在的同名觸發器。

22.3.2 DROP TRIGGER語句

這個語句可以刪除觸發器，其語法如下：

22.3.3 FOR EACH ROW語句

MySQL裡的觸發器還可以調整觸發條件。FOR EACH ROW語法可以讓過程在SQL語句影響每條記錄時都觸發，或是一條語句只觸發一次。其語法如下所示：

區別在於觸發器執行的次數。如果創建了一個普通觸發器，執行了一條會影響100行記錄的SQL語句時，觸發器只會執行一次。如果創建觸發器時使用了FOR EACH ROW語法，並且再次執行同樣的SQL語句，觸發器就會執行100次，也就是SQL語句影響的每條記錄都會觸發它。

22.4 動態SQL

動態SQL允許程序員或終端用戶在運行時創建SQL語句的具體代碼，並且把語句傳遞給數據庫。數據庫然後就把數據返回到綁定的程序變量裡。

為了更好地理解動態SQL，先要來複習一個靜態SQL。本書前面介紹的全部都是靜態SQL。靜態SQL是事先編寫好的，不準備進行改變的。雖然靜態SQL語句可以保存到文件裡以備以後使用，也可以作為存儲過程保存在數據庫裡，但其靈活性還是不能與動態 SQL相比。

使用靜態SQL語句的一個問題是，雖然我們可以為終端用戶提供大量的語句，但依然可能出現不能滿足所有用戶需要的情況。動態SQL通常被用於專門的查詢工具，允許用戶隨時創建SQL語句，從而滿足特定情況下的特定查詢需求。在語句根據用戶需要被生成之後，它們被送給數據庫，數據庫檢查語法正確性及所需的權限，對語句進行編譯。

使用調用級接口可以創建動態SQL，下一小節將介紹調用級接口。

注意：動態SQL的性能不一定好

雖然動態SQL為終端用戶提供了更好的靈活性，但其性能不能與存儲過程相比，因為後者已經被SQL優化器進行瞭解析。

22.5 調用級接口

調用級接口（CLI）用於把 SQL代碼嵌入到主機程序，比如ANSI C。程序員應該很熟悉調用級接口的概念，它是把SQL嵌入到不同的過程序編程語言的方法之一。在使用調用級接口時，我們只需要根據主機編程語言的規則把SQL語句的文本保存到一個變量裡，然後利用這個變量就可以在主機程序裡執行SQL語句。

EXEC SQL是一個常見的主機編程語言命令，可以在程序裡調用SQL語句。

下面是支持CLI的常見編程語言：

ANSI C；

C#；

VB.NET；

JAVA；

Pascal；

Fortran。

注意：調用級接口的語法因平台而異

使用調用級接口的具體語法請參考所用主機編程語言的文檔。調用級編程語言與平台有關。所以，Oracle與SQL Server的調用級接口互不兼容。

22.6 使用SQL生成SQL

使用SQL生成SQL是節省SQL語句編寫時間的一個好方法。假設數據庫裡已經有了100個用戶，我們創建一個新角色ENABLE，要授予給這100個用戶。這時不必手工創建100個GRANT語句，下面的SQL語句會生成所需的每一條語句：

這個範例使用了Oracle的系統目錄視圖（包含著關於用戶的信息）。

注意包圍GRANT ENABLE TO的單引號，它表示所包圍的內容（包括空格在內）要直義使用。還記得嗎，我們可以像從表裡選擇字段一樣選擇直義值。USERNAME 是系統目錄表 SYS.DBA_USERS 裡的字段，雙管道符號（||）用於連接字段，它把分號連接到用戶名之後，從而形成完整的語句。

這個SQL語句的結果是這樣的：

這些結果應該保存到文件裡，再發送給數據庫。然後數據庫執行文件裡的每條SQL語句，這樣我們就不必輸入很多的命令，從而節省了時間與精力。GRANT ENALBE TO USERNAME語句會對數據庫裡的每個用戶重複執行。

在需要編寫會重複多次的SQL語句時，我們應該發揮自己的想像力，讓SQL為我們完成工作。

22.7 直接SQL與嵌入SQL

直接SQL是指從某種形式的交互終端上執行的SQL語句，它的執行結果會直接返回到終端。本書的大部分內容是關於直接SQL的。直接SQL也被稱為交互調用或直接調用。

嵌入SQL是在其他程序裡使用的SQL代碼，這些程序包括Pascal、Fortran、COBOL和C。前面已經介紹過，SQL 代碼是通過調用級接口嵌入到主機編程語言裡的。在主機編程語言裡，嵌入 SQL語句通常以EXEC SQL開始，以分號結束。當然也有使用其他結束符的，比如END-EXEC和右圓括號。

下面是在主機程序（比如ANSI C）裡嵌入SQL的範例：

22.8 窗口表格函數

窗口表格函數可以對表格的一個窗口進行操作，並且基於這個窗口返回一個值。這樣就可以計算連續總和、分級和移動平均值等。窗口表格函數的語法如下所示：

幾乎所有匯總函數都可以作為窗口表格函數，另外還有5個新的窗口表格函數：

RANK OVER；

DENSE_RANK OVER；

PERCENT_RANK OVER；

CUME_DIST OVER；

ROW_NUMBER OVER。

一般來說，計算個人在一個收入年度裡的評分級別是比較困難的，而窗口表格函數可以讓這種工作容易一些，比如下面這個Microsoft SQL Server範例：

不是全部RDBMS實現都支持窗口表格函數，所以在使用這種函數之前請查看具體實現的文檔。

22.9 使用XML

2003版的ANSI標準裡有一個與XML相關功能的部分，從那之後，很多數據庫實現都努力至少支持其中的部分功能。舉例來說，ANSI標準裡有一部分是以XML格式輸出查詢的結果，SQL Server就通過語句FOR XML提供了這個功能，範例如下：

XML 功能集裡另一個重要特性是能夠從 XML 文檔或片斷裡獲取信息，MySQL 通過EXTRACTVALUE函數提供了這個功能，它有兩個參數，第一個是XML片斷，第二個是定位器，用於返回與字符串匹配標記的第一個值。其語法如下所示：

下面的範例使用這個函數從節點a裡提取值：

關於XML功能的詳細情況請參見具體實現的文檔。某些實現，例如SQL Server和Oracle，擁有特定的XML數據類型。例如，Oracle的XMLTYPE類型擁有特定的API來處理與XML有關的大部分功能，例如查找和提取數據。Microsoft SQL Server的XML類型允許使用模板來確保輸入到列的XML數據的完整性。

22.10 小結

本章介紹了一些高級SQL概念，雖然並沒有深入討論，但可以讓我們對這些概念有一個基本的瞭解。首先是光標，它可以把查詢的結果傳遞到內存裡的某個位置。當程序裡聲明了一個光標之後，在訪問之前要打開它，然後就可以把光標的內容獲取到一個變量裡，用於程序進行處理。光標的內容會保存在內存裡，直到光標被關閉且內存被重新分配。

接著介紹了存儲過程和觸發器。存儲過程就是保存在數據庫裡的SQL語句，這些語句（以及其他命令）在數據庫裡是經過編譯的，可以被用戶隨時執行。存儲過程通常比單個SQL語句具有更好的性能。

另外還介紹了動態SQL、用SQL生成SQL、直接SQL與嵌入SQL的不同。動態SQL是用戶在運行期間創建的SQL代碼，這是與靜態SQL的最大區別。

最後，我們還討論了窗口表格函數和XML，這些是相對比較新的特性，可能不是所有數據庫都支持，但還是值得瞭解一下。這裡介紹的一些高級主題可以用於解釋第23章中的企業級SQL應用。

22.11 問與答

問：存儲過程能夠調用另一個存儲過程嗎？

答：是的，被調用的存儲過程被稱為嵌套的。

問：如何執行一個光標？

答：只需要使用OPEN CURSOR語句，就會把光標的結果發送到特定存儲區域。

22.12 實踐

下面的內容包含一些測試問題和實戰練習。這些測試問題的目的在於檢驗對學習內容的理解程度。實戰練習有助於把學習的內容應用於實踐，並且鞏固對知識的掌握。在繼續學習之前請先完成測試與練習，答案請見附錄C。

22.12.1 測驗

1．觸發器能夠被修改嗎？

2．當光標被關閉之後，我們能夠重用它的名稱嗎？

3．當光標被打開之後，使用什麼命令獲取它的結果？

4．觸發器能夠在INSERT、DELECT或UPDATE語句之前或之後執行嗎？

5．在MySQL裡使用什麼語句從XML片斷裡獲取信息？

6．為什麼Oracle和MySQL不支持針對光標的DEALLOCATE語法？

7．為什麼光標不是基於數據集的操作？

22.12.2 練習

1．參考下面的MySQL命令，編寫SQL語句，來返回數據庫中所有表的描述信息：

2．編寫一個 SELECT 語句來生成 SQL 代碼，統計每個表裡的記錄數量。（提示：類似於練習1。）

3．編寫一組SQL命令來創建一個光標，返回所有用戶及其銷售數據。確保在用戶所使用的實現中，正確關閉光標並回收資源。

第23章 SQL擴展到企業、互聯網和內部網

本章的重點包括：

SQL與企業

前台和後台程序

訪問遠程數據庫

SQL與互聯網

SQL與內部網

前一章介紹了一些高級SQL概念，它們基於本書前面章節所介紹的內容，並且開始展示SQL的一些實際應用。本章著重於把SQL擴展到企業背後的概念，其中包括SQL應用程序和讓企業全部成員都能夠使用數據來完成日常工作。

23.1 SQL與企業

很多商業公司都為其他企業、顧客和銷售商提供數據，比如一個企業可能會向顧客提供關於產品的詳細信息，從而希望實現更好的銷售。企業僱員的需求也在考慮之列，比如提供關於僱員的特定信息，包括考勤登記、休假計劃、培訓計劃、公司政策等。在數據庫被創建之後，顧客和僱員應該可以通過SQL或某種互聯網語言訪問企業的數據。

23.1.1 後台程序

任何應用的核心都是後台程序，它們對於數據庫終端用戶是透明的，但卻是發生一切事情的幕後場所。後台程序包括實際的數據庫服務程序、數據源、把程序連接到Web或局域網上遠程數據庫的中間軟件。

確定所要使用的數據庫實現通常是移植任何程序的第一步，包括通過局域網（LAN）到企業、到企業自己的內部網，或是到互聯網。移植描述了在一個環境裡實現一個應用供用戶使用的過程。數據庫服務程序應該由數據庫管理員（DBA）創建，他理解公司的需求與程序的要求。

應用的中間件包括Web服務程序、能夠把Web服務程序連接到數據庫服務程序的工具。其主要目的是讓Web上的程序能夠與公司的數據庫進行通信。

23.1.2 前台程序

前台程序是應用的組成部分，終端用戶通過它進行交互。前台程序可以是現成的商業軟件，或是使用第三方工具自己開發的程序。商業軟件包括一些使用Web瀏覽器來展示內容的應用軟件。在Web環境下，類似FireFox和IE這樣的瀏覽器經常被用來訪問數據庫程序。這樣，用戶不必安裝特定軟件也可以訪問數據庫。

注意：應用具有很多不同的層

前台程序簡化了終端用戶對數據庫的操作。底層的數據庫、代碼和數據庫裡發生的事件對於用戶來說是透明的。前台程序使得用戶不必對系統本身非常瞭解，從而減少了他們的猜測與疑惑。新技術使得程序更加智能化，讓用戶能夠專注於真正與實際工作有關的部分，從而提高了整體的生產力。

目前可以使用的工具是用戶友好的、面向對象的，具有圖標、嚮導，支持鼠標的播放操作。用於把程序移植到Web的流行工具包括Borland公司的C++ Builder、IntraBuilder和微軟的Visual Studio。其他一些用於在局域網上開發公司級程序的工具還有Powersoft的PowerBuilder、Oracle公司的Oracle Designer和Oracle Forms、微軟的Visual Studio、Borland的Delphi。

圖23.1展示了數據庫應用裡的前台程序和後台程序。後台程序位於數據庫所在的主機服務器上。後台用戶包括開發人員、程序員、DBA、系統管理員和系統分析員。前台程序位於客戶計算機，通常就是每個用戶的個人電腦。前台用戶是前台程序的大量使用人員，包括數據輸入員、會計等。終端用戶能夠通過網絡連接（LAN或廣域網）訪問後台數據庫，這是由一些通過網絡為前台和後台程序提供連接的中間件（比如ODBC驅動程序）實現的。

圖23.1 數據庫應用

23.2 訪問遠程數據庫

有時要訪問的數據庫是個本地數據庫，也就是直接連接的。但在很多情況下，我們都會訪問某種形式的遠程數據庫。遠程數據庫是非本地的，或是說位於非直接連接的服務器上，這時我們必須使用網絡和網絡協議與數據庫進行交互。

訪問遠程數據庫的方式有多種。從廣義角度來說，我們是利用中間產品（ODBC和JDBC就是標準的中間件，在後續章節進行介紹）通過網絡或互聯網連接訪問遠程數據庫的。圖23.2展示了訪問遠程數據庫的3種情形。

圖23.2 訪問遠程數據庫

圖23.2展示了從本地數據庫服務器、本地前台程序和本地主機服務器訪問遠程服務器的情形。本地數據庫服務器和本地主機服務器經常是同一台機器，因為數據庫一般位於本地主機服務器上。但是，我們通常在沒有本地數據庫連接的情況下從本地服務器連接到遠程數據庫。對於終端用戶來說，前台程序是訪問遠程數據庫的最典型方式。所有的方法都必須把對數據庫的請求通過網絡進行路由。

23.2.1 ODBC

開放式數據庫連接（ODBC）可以通過一個庫驅動程序連接到遠程數據庫。前台程序利用ODBC驅動與後台數據庫進行交互。在連接到遠程數據庫時，可能還需要一個網絡驅動。程序調用ODBC函數，驅動管理程序加載ODBC驅動。ODBC驅動處理這個調用，提交SQL請求，從數據庫返回結果。

作為 ODBC 的一個組成部分，所有關係數據庫管理系統（RDBMS）廠商都提供了數據庫的應用編程接口（API）。

23.2.2 JDBC

JDBC是Java數據庫連接，它類似ODBC，通過一個Java庫驅動連接到遠程數據庫。前台的Java程序使用JDBC驅動與後台的數據庫進行交互。

23.2.3 OLE DB

OLE DB是微軟公司使用組件對像模型（Component Object Model）編寫的一組接口，用於代替 ODBC。OLE DB實現力圖拓展ODBC功能，不僅可以連接各種數據庫實現， 也可以連接非數據庫存儲的數據，例如電子錶格等。

除了 ODBC 驅動之外，很多廠商也提供了自己的產品，可以把用戶連接到遠程數據庫。這些廠商產品都是專門用於特定 SQL 實現的，一般不能移植到其他類型的數據庫服務程序。

23.2.4 廠商連接產品

除了驅動和 API 之外，很多廠商也提供了自己的產品，可以把用戶連接到遠程數據庫。這些廠商產品都是專門用於特定 SQL 實現的，一般不能移植到其他類型的數據庫服務程序。

Oracle公司有一個名為Oracle Fusion Middleware的中間件產品，既可以連接Oracle數據庫，也可以連接其他應用軟件。

Microsoft也有幾款產品與其數據庫配合使用，例如Microsoft SharePoint Server和 SQL Server Reporting Services。

23.2.5 通過Web接口訪問遠程數據庫

通過Web接口訪問遠程數據庫十分類似於通過局域網進行訪問，主要區別在於用戶的全部請求都經過Web服務程序進行了路由（如圖23.3所示）。

從圖23.3 中可以看出，一個終端用戶通過一個 Web 接口訪問數據庫，首先是調用一個Web瀏覽器，它用於連接到一個特定的URL（由Web服務程序的位置決定）。Web服務程序驗證用戶的訪問，把用戶請求（可能是一個查詢）發送給遠程數據庫（也可能對用戶的身份進行驗證）。數據庫服務程序然後把結果返回給Web服務程序，後者把結果顯示在用戶的Web瀏覽器上。使用防火牆可以控制對特定服務器的非授權訪問。

警告：注意互聯網信息安全問題

注意在Web上提供的信息。永遠要確保在全部恰當的級別都採取了應有的預防措施，其中包括Web服務器、主機服務器、遠程數據庫。涉及個人隱私的數據，比如個人的社會保險號碼，永遠都不應該公開在Web上。

圖23.3 遠程數據庫的Web接口

防火牆是一種安全機制，防止來自和針對服務器的非授權連接。在一台服務器上可以啟動一個或多個防火牆來監視對數據庫或服務器的訪問。

另外，一些數據庫實現允許我們根據IP地址限制對數據庫的訪問，這就提供了另一層保護，因為我們可以把對數據庫的訪問限制到充當應用層的Web服務器。

23.3 SQL與互聯網

SQL可以嵌入到或用於像C#和JAVA這樣的編程語言，還可以嵌入到互聯網編程語言，比如Java和ASP.NET。源自於HTML的文本可以被轉換為SQL，從Web前端遠程數據庫發送請求。在數據庫完成查詢操作之後，輸出結果被轉換回HTML，顯示在用戶的Web瀏覽器上。下面的小節將討論SQL在互聯網上的應用。

23.3.1 讓數據可以被全世界的顧客使用

隨著互聯網的出現，數據對全世界的顧客和廠商都開放了。一般來說，用戶利用前台工具以只讀方式訪問數據。

為顧客提供的數據包括一般的顧客信息、產品信息、發票信息、當前訂單、延期交貨單和其他相關信息。但其中不應該包括隱私信息，比如公司策略和僱員信息。

在互聯網上擁有自己的主頁已經成為公司競爭中不可缺少的組成部分，Web頁面可以僅用很小的代價就向瀏覽者展示公司的全面情況，包括它的服務、產品和其他信息。

23.3.2 向僱員和授權顧客提供數據

數據庫可以通過互聯網或公司的內部網向僱員或顧客提供訪問。互聯網是一個非常有價值的通信資源，可以用於向僱員提供公司政策、福利、培訓等信息。但是，在通過互聯網提供數據時一定要非常小心，公司機密和個人信息不應該能夠通過Web訪問。另外，在線提供的數據應該只是數據庫的一個子集或子集的副本。主要的實用數據庫應該全力保護。

警告：互聯網的安全性還不夠好

與互聯網的安全相比，數據庫安全更可靠一些，因為後者可以根據所包含的數據進行精細的調整。雖然在通過互聯網訪問數據時也可以使用一些安全措施，但通常是有限的，而且不像數據庫權限那樣容易修改。我們應該總是盡量使用數據庫服務器具有的安全特性。

23.4 SQL與內部網

IBM 最初創建 SQL 是要實現主機上的數據庫與使用客戶機的用戶之間的通信。用戶通過 LAN 連接到主機，SQL 被選作數據庫與用戶之間通信的標準語言。內部網基本上就是一個小型互聯網，主要區別是內部網是針對單個公司的應用，而互聯網是對公共福斯開放的。內部網上的用戶（客戶端）接口與客戶/服務器環境裡的是一樣的。SQL經過Web服務器和語言（比如HTML）的路由轉發到數據庫。內部網主要用於公司內部應用、文檔、表單、Web頁面和電子郵件。

通過互聯網進行的SQL請求必須特別注意性能問題。在這種情況下，不僅需要從數據庫獲取數據，還需要把數據顯示在用戶的瀏覽器上。這通常涉及把數據轉換為某種形式的HTML兼容代碼。另外，Web連接一般都比內部網連接的速度慢，因此數據來回傳遞的速度也慢。

連接入Web的數據庫實現必須重視安全性。這需要考慮很多問題，來確保數據處於安全保護之下。首先，如果數據暴露於公共網絡，必須確保這些數據不會被非法訪問。通常，數據會被轉換成明文形式，以便用戶閱讀。可以考慮使用SSL作為部分安全措施，來保護網絡交流。SSL使用證書來加密服務端和客戶端之間傳遞的消息，這種加密可以被用HTTPS開頭的網站所識別。

另一個需要考慮的問題是非法的數據輸入。用戶或應用程序可能會向錯誤的字段輸入了錯誤的數據類型，也可能會遇到更嚴重的SQL注入攻擊，黑客可能通過這種方式向數據庫注入並執行自己的SQL代碼。

預防上述問題的最好方法就是，嚴格約束應用軟件賬戶對數據庫的訪問。可以在需要訪問數據庫的時候，使用存儲過程和函數，這樣就可以對進出系統的數據有所控制。同時，還可以使用戶執行任何符合DBA要求的數據操作，以確保數據的一致性。

23.5 小結

本章介紹了在互聯網上應用SQL和數據庫程序背後的概念，這些概念對於公司在當今這個時代保持競爭力是非常重要的。事實已經證明，為了不被時代拋棄，在互聯網上佔據一席之地是很有好處的——甚至是必須的。為此，公司必須開發程序，甚至是從客戶/服務器系統上把程序移植到互聯網上的Web服務器。在提供任何類型及任何數量的公司數據時，最需要考慮的問題就是安全，並且應該始終嚴格堅持安全準則。

本章還討論了通過局域網和互聯網訪問遠程數據庫。任何訪問遠程數據庫的方式都需要使用網絡和協議適配器來轉換對數據庫的請求。在此，我們概要介紹了基於局域網、公司內部網和互聯網的SQL應用。在完成後面的測驗和練習之後，我們就要進入最後一章了。

23.6 問與答

問：為什麼說，瞭解數據是否通過互聯網的公共網絡被訪問，這一點很重要？

答：在客戶端和Web應用之間傳遞的數據往往是明文形式。這就意味著，任何人都可以攔截消息並看到其中的內容，例如社會保險號或銀行賬號。在可能的情況下，最好對數據進行加密。

問：針對Web應用的後台數據庫與針對客戶/服務器系統的後台數據庫有什麼不同嗎？

答：針對 Web 應用的後台數據庫本身不必與針對客戶/服務器系統的有什麼不同，但基於Web 的程序需要滿足其他一些要求。舉例來說，需要使用Web 服務程序訪問數據庫。在使用Web程序時，用戶通常不是直接連接到數據庫的。

23.7 實踐

23.7.1 測驗

1．一台服務器上的數據庫能夠被另一台服務器訪問嗎？

2．公司可以使用什麼方式向自己的僱員發佈信息？

3．提供數據庫連接的產品被稱為什麼？

4．SQL能夠嵌入到互聯網編程語言裡嗎？

5．如何通過Web程序訪問遠程數據庫？

23.7.2 練習

1．連接到互聯網，查看一些公司的主頁。如果你自己的公司有主頁，可以把它與競爭對手的主頁進行一下比較，回答以下這些問題。

頁面上有動態的內容嗎？

什麼樣的頁面或者頁面上的什麼區域，可能包含來自後端數據庫的數據？

Web頁面上有什麼安全機制嗎？在訪問保存在數據庫裡的數據時需要登錄嗎？

現在，大部分瀏覽器允許用戶查看返回頁面的源代碼。使用你的網頁瀏覽器查看源代碼。其中是否存在一些代碼，可以告訴你後端使用的數據庫是什麼？

如果在源代碼中發現了一些信息，例如服務器名稱或者數據庫用戶名，你認為這屬於安全漏洞嗎？

2．訪問下面的站點，瀏覽其中的內容、最新的技術和公司在Web上使用的數據（來自於數據庫的數據）。

www.amazon.com

www.informit.com

www.epinions.com

www.mysql.com

www.oracle.com

www.ebay.com

www.google.com

第24章標準SQL的擴展

本章的重點包括：

各種實現

不同實現之間的區別

遵循ANSI SQL

交互SQL語句

使用變量

使用參數

本章介紹對ANSI標準SQL的擴展。雖然大多數SQL實現遵循了這個標準，但有很多廠商會通過各種形式的改進對標準SQL進行擴展。

24.1 各種實現

多家廠商發佈了多種SQL實現，在此不可能列出全部的關係型數據庫廠商，只能討論一些主流實現，其中包括MySQL、Microsoft SQL Server和Oracle。其他一些比較流行的廠商還有Sybase、IBM、Informix、Progress、PostgreSQL等。

24.1.1 不同實現之間的區別

雖然這裡討論的各種實現都是關係型數據庫產品，但彼此之間還是有所區別的。這些區別源自於產品設計和數據庫發動機處理數據的方式，但本書著重介紹SQL方面的區別。所有的實現都根據ANSI的要求使用SQL作為與數據庫通信的語言，但很多實現都對SQL進行了某種形式的擴展。

注意：廠商有意擴展SQL標準

不同廠商會出於性能及易用性的考慮對ANSI SQL進行增強，努力提供其他廠商沒有的優勢，從而吸引顧客。

在瞭解了SQL之後，根據不同實現的區別對SQL進行調整應該沒有什麼問題。換句話說，如果我們可以在Sybase實現裡編寫SQL，就可以在Oracle裡編寫SQL。另外，瞭解不同廠商的SQL還可以增加我們的就業機會。

下面比較幾個主流廠商與ANSI標準的SELECT語句。

首先是ANSI標準：

下面是Microsoft SQL Server的語法：

Oracle的語法：

從這些語法的比較可以看出，它們基本上是相同的。它們都具有 SELECT、FORM、WHERE、GROUP BY、HAVING、UNION和ORDER BY子句，這些子句在工作概念上是一樣的，但有些具有額外的選項，這些選項就被稱為擴展。

24.1.2 遵循ANSI SQL

廠商們的確努力遵循ANSI SQL，但都沒有做到百分之百符合ANSI SQL標準。有些廠商添加了命令或函數，而且其中很多新命令或函數被吸收到ANSI SQL裡。對於廠商來說，遵循標準有很多好處，最明顯的是使用其產品易於學習，而且其使用的代碼也易於移植到其他實現。當數據庫從一個實現遷移到另一個實現時，可移植性是一個非常重要的考慮因素。

對於被認為遵循ANSI的數據庫來說，它只需要對應於ANSI標準的一個功能子集。ANSI標準是由多家數據庫廠商共同制定的。因此，雖然大多數SQL實現彼此之間有很大差別，但它們都被認為是遵循ANSI標準的。所以，把代碼限制到嚴格遵循ANSI標準的語句能夠提高可移植性，但數據庫性能可能不會達到最優。總之，我們要在可移植性與性能之間權衡。權衡的結果通常是放棄可移植性，從而充分利用用戶所用平台的性能。

24.1.3 SQL的擴展

實際上，全部主流廠商都對SQL有所擴展。對於特定實現來說，SQL擴展都是不同的，而且一般不便於移植。然而，流行的標準擴展已經得到了ANSI的關注，將來可能會成為新標準。

Oracle的PL/SQL、Sybase和Microsoft SQL Server使用的Transact-SQL是標準SQL擴展的兩個範例，後面的範例裡將更詳細地介紹它們。

24.2 擴展範例

PL/SQL和Transact-SQL都被認為是第4代編程語言，是過程化語言，但SQL是非過程化語言。我們還會簡要地討論一下MySQL。

非過程語言SQL包括如下語句：

INSERT；

UPDATE；

DELETE；

SELECT；

COMMIT；

ROLLBACK。

SQL擴展是一種過程語言，包括標準SQL裡全部語句、命令和函數，另外還包括：

變量聲明；

光標聲明；

條件語句；

循環；

錯誤處理；

變量累加；

日期轉換；

通配符；

觸發器；

存儲過程。

這些語句可以讓程序員在過程化語言裡更好地控制數據處理方式。

24.2.1 Transact-SQL

Transact-SQL是Microsoft SQL Server使用的一種過程語言，表示我們告訴數據庫如何、在何處獲取和操作數據。SQL 是非過程的，由數據庫決定如何、在何處選擇和操作數據。Transact-SQL 的幾個突出優點包括聲明本地和全局變量、光標、錯誤處理、觸發器、存儲過程、循環、通配符、日期轉換和匯總報告。

Transact-SQL語句的一個範例如下：

這是個很簡單的Transact-SQL語句，它表示如果表PRODUCTS_TBL裡的平均價格大於50，就顯示「LOWER ALL COSTS BY 10 PERCENT」，否則就顯示「COSTS ARE REASONABLE」。

其中使用了 IF...ELSE 語句計算條件的值，而 PRINT 命令也是個新命令。這些只是Transact-SQL強大功能的九牛一毛。

注意：SQL不是過程語言

標準 SQL 從根本上來說是非過程語言，表示我們把語句提交給數據庫服務程序，後者決定如何以最優方式執行語句。過程語言允許程序員請求要獲取或操作的數據，告訴數據庫服務程序如何準確地執行請求。

24.2.2 PL/SQL

PL/SQL是Oracle對SQL的擴展，也是一種過程語言，由代碼的邏輯塊構成。一個邏輯塊包含三個部分，其中兩個是可選的。第一部分是DECLARE部分，是可選的。它包含變量、光標和常數。第二個部分是 PROCEDURE，是必需的，包含條件命令和 SQL 語句，是邏輯塊的執行部分。第三部分是 EXCEPTION，是可選的，定義了程序如何處理錯誤和自定義異常。PL/SQL 的突出優點包括使用了變量、常數、光標、屬性、循環、處理異常、向程序員顯示輸出、事務控制、存儲過程、觸發器和軟件包。

PL/SQL語句的範例如下所示：

這個範例裡使用了三個部分裡的兩個：DECLARE和PROCEDURE。首先，用一個查詢定義了一個名為EMP_CURSOR的光標；然後聲明了一個變量EMP_REC，與光標裡每個字段的數據類型（%ROWTYPE）相同。PROCEDURE部分（在BEGIN之後）的第一步是打開光標，然後使用 LOOP命令遍歷光標裡每條記錄，結束於 END LOOP語句。光標裡的全部記錄都會更新到表EMPLOYEE_TBL。如果僱員的中間名是NULL，更新操作會把中間名設置為「X」。更新被提交到數據庫，最後光標被關閉。

24.2.3 MySQL

MySQL是個多用戶、多線程SQL數據庫客戶/服務器實現，它包含一個後台服務程序、一個終端監控客戶程序、幾個客戶程序和庫。MySQL的主要目標是速度、強健性和易用性，它最初的設計目的是對大型數據庫提供更快速的訪問。

MySQL被認為是一種比較符合ANSI標準的數據庫實現。從最開始，MySQL就是一個半開源的開發環境，以便嚴格遵守ANSI標準。從5.0版開始，MySQL推出了開源的社區版和閉源的企業版。2009年，MySQL隨同SUN公司一起被Oracle公司收購。

目前，MySQL還不像Oracle或Microsoft SQL Server那樣有大的改動，但根據其近期的表現來看，情況很快就會有變化了。用戶可以查看所用版本 MySQL 的說明書，以便瞭解哪些擴展可能會被開發。

24.3 交互SQL語句

交互SQL語句會在完全執行之前詢問用戶變量、參數或某種形式的數據。假設我們有一個SQL語句是交互的，用於在數據庫裡創建用戶。它會提示我們輸入一些信息，比如用戶ID、用戶名、電話號碼等。它可以創建一個或多個用戶，而且只需執行一次。否則，我們就需要用CREATE USER語句分別創建每個用戶。當然，這個SQL語句還能提示設置權限。並不是全部廠商都具有交互式SQL語句，詳細情況請參見具體實現的文檔。

交互式SQL語句的另一個優點是可以使用參數。參數是SQL裡的變量，位於程序之內。我們可以在運行時向SQL語句傳遞參數，讓用戶能夠以更靈活的方式執行語句。很多主流實現支持使用這些參數，下面的小節將展示在Oracle和SQL Server裡傳遞參數的範例。

Oracle裡可以把參數傳遞給靜態SQL語句，比如：

前面這個SQL語句會提示輸入EMP_ID，然後返回EMP_ID和對應的LAST_NAME、FIRST_NAME。下面的語句提示我們輸入城市和州，返回居住在指定城市和州里的僱員的全部數據。

在Microsoft SQL Server裡，我們可以把參數傳遞給存儲過程：

下面就執行這個存儲過程並傳遞參數：

24.4 小結

本章介紹了一些廠商對標準SQL的擴展以及它們遵循ANSI標準的情況。在學習了SQL之後，我們可以輕鬆地把這些知識（和代碼）應用到SQL的其他實現。SQL在不同廠商之間是可以移植的，大多數SQL代碼只需要很小的修改就可以在大多數SQL實現中使用。

最後一部分內容展示了三種實現使用的兩個擴展。Microsoft SQL Server和Sybase使用了Transact-SQL，而Oracle使用的是PL/SQL。從範例中可以看出這兩者之間的相似之處。它們都遵循ANSI標準，在此基礎上進行增強，提供更好的功能和效率。另外還介紹了MySQL，其設計目的是提高大型數據庫查詢的速度。本章的目標是讓用戶瞭解到存在著很多 SQL 擴展，而遵循ANSI SQL標準也是一件非常重要的事情。

如果可以掌握本書的內容並使用它（創建自己的代碼、進行測試、增長知識），我們就走上了掌握SQL的陽光大道。公司都要使用數據，沒有數據庫就很難正常運行。關係型數據庫遍佈四方，而SQL是與關係型數據庫進行通信和管理的標準語言，所以學習SQL是個非常好的選擇。祝你好運！

24.5 問與答

問：為什麼SQL有差異？

答：不同的SQL實現使用不同方式存儲數據，各個廠商都努力超越其他競爭對手，不斷出現的新概念，這些原因導致了SQL有差異。

問：在學習了基本SQL之後，我們是不是就可以在不同實現上使用SQL了？

答：是的，但是要記住不同實現之間存在的差異與變化，但大多數實現的SQL基本構架是一樣的。

24.6 實踐

24.6.1 測驗

1．SQL是過程語言還是非過程語言？

2．除了聲明光標之外，光標的3個基本操作是什麼？

3．過程或非過程：數據庫發動機在處理什麼語句時會決定對SQL語句進行估值和執行？

24.6.2 練習

研究一下不同廠商的SQL差異。訪問如下站點，研究常見的SQL實現：