SQL程式设计技术

1、第 9 章 SQL程式設計技術 2 學習重點 使用宣示敘述指定一般的限制 SQL中的視界 資料庫程式設計 嵌入式SQL 函式呼叫與SQL/CLI 預儲程序和SQL/PSM 3 使用宣示敘述指定一般的限制 一般性的限制：那些不屬於基本SQL分類裡 的限制 機制：CREAT ASSERTION 元件包括： 限制名稱 之後的是CHECK關鍵字 緊接著是一個條件 4 宣示敘述範例 指定員工的薪資不得超過其工作部門 經理的薪資這個限制 CREAT ASSERTION SALARY_CONSTRAINT CHECK (NOT EXISTS (SELECT * FROM EMPLOYEE E, EMPLOY

2、EE M, DEPARTMENT D WHERE E.SALARY M.SALARY AND E.DNO=D.NUMBER AND D.MGRSSN=M.SSN) 限制名稱, CHECK, 條件 5 使用一般的宣示敘述 指定查詢選出那些會違反要求條件的值組，並 在查詢中加入NOT EXISTS子句 查詢的結果必須是空集合 假如查詢結果不是空集合，那麼就代表違反宣 示敘述 6 SQL觸發敘述 目標：監控資料庫並在條件符合時採取行動 觸發敘述的語法類似宣示敘述，也包括下列幾 部分： 事件 如發生一個插入、刪除或更新運算 條件 行動 條件符合時所要採取的行動 7 SQL觸發敘述範例 用觸發敘述在插入

3、或更新運算時檢查，員工的 薪資是否超過其主管的薪資： CREATE TRIGGER INFORM_SUPERVISOR BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN ON EMPLOYEE FOR EACH ROW WHEN (NEW.SALARY (SELECT SALARY FROM EMPLOYEE WHERE SSN=NEW.SUPERVISOR_SSN) INFORM_SUPERVISOR (NEW.SUPERVISOR_SSN,NEW.SSN); 8 SQL中的視界 視界 (view) 是指一個從其他表格所衍生出來的 虛擬表格(

4、virtual table) 這會限制可能應用在視界上的更新運算動作 因為這個表格可能在實際上並不存在 在視界上進行查詢時並不會有任何限制 方便用來表達某些運算 9 視界的規格 SQL命令：CREATE VIEW 一個表格 (視界) 名稱 屬性名稱清單和 (例如視界的屬性全都不是由函 數或算術運算子產生的，就不需要指定視界的 屬性名稱，它們將會與定義表格裡的屬性名稱 相同) 指定視界內容的查詢 10 SQL視界的範例 指定另一個不同的WORKS_ON表格 CREATE VIEW WORKS_ON_NEW AS SELECT FNAME, LNAME, PNAME, HOURS FROM EMP

5、LOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER GROUP BY PNAME; 11 使用虛擬表格 我們可以在新建立的視界上指定SQL查詢： SELECT FNAME, LNAME FROM WORKS_ON_NEW WHERE PNAME=Seena; 等到不需要它的時候，再刪除該視界： DROP WORKS_ON_NEW; 12 有效率的視界查詢 查詢修改： 將視界查詢修改為針對基底表格的查詢 缺點： 對於複雜的視界執行時會變得很沒有效率 特別是假如在一段很短的時間內，有很多個查詢同 時在使用此視界時 13 有效率的視界查詢

6、(續) 視界具體化 (view materialization)： 在實體上真正建立一個暫存的視界表格並維護 它 假設： 視界的其他查詢也會跟著使用和維護這個暫存 表格 注意： 在基底表格更新時，必須維護基底表格與視界 之間的一致 策略： 遞增更新 14 視界的更新 假如視界是定義在單一表格上，而且不含有聚 合函數： 則它的更新動作可以直接對應成基底表格的更 新動作 如果視界是合併的： 更新動作可能對映成基底關聯的更新動作 但不一定可以 15 無法更新的視界 有定義使用群組化或聚合函數的視界不能執行 更新動作 假如視界是定義在使用合併的多個表格上，通 常不能執行更新動作 WITH CHECK

7、OPTION：假如視界是需要被更 新的，必須在視界定義的結尾加上它 這樣系統就會去檢查視界是否可進行更新，並 且去規劃出視界更新時的執行策略 16 資料庫程式設計 目標： 如何從應用程式來存取資料庫 (與互動式介面相 反) 為什麼？ 互動式的操作介面雖然方便但是不夠 大部分的資料庫運算還是要透過應用程式 (目前很 多是透過網頁程式) 17 資料庫程式設計的方式 嵌入指令： 在一般的程式語言中嵌入資料庫指令 資料庫函式的程式庫： 讓主機語言 (host language) 可以用來呼叫資料 庫；也就是所謂的API API 是Application Program Interface的縮寫 設計一

8、個全新的語言 盡量減少不相容的情況 18 不相容的問題 形容因為不同資料庫模型和程式語言之間所發 生的問題，例如 因為資料型態不相容，因此必須為每種語言進 行繫結 (binding) 集合 vs. 一次一筆記錄的處理方式 需要特殊的反覆子 (iterator) 變數，以迴圈方式 來處理查詢的結果 19 資料庫程式設計典型的步驟 首先用戶端程式要開啟 (open) 一個連結 (connection) 到資料庫伺服器 用戶端程式將查詢送出 (submit) 並更新資料 庫 當程式不再需要存取資料庫時將連結中斷 (terminate) 20 嵌入式SQL 大多數的SQL敘述都可以嵌入在主機語言的程

9、式裡，如COBOL、C、Java 嵌入式SQL敘述與主機語言敘述的區隔是藉由 一對 EXEC SQL或EXEC SQL BEGIN與相對 應的END-EXEC 或EXEC SQL END (或分號) 在不同語言中語法可能不同 共享變數 (在兩邊的語言中都會用到) 在SQL中 通常前面會有個冒號 (:) 21 範例：在C語言中的變數宣告 在DECLARE中宣告的變數是共享變數，可以出現在SQL敘 述中 (前面加上冒號) SQLCODE是用來處理連結錯誤以及其他在程式與資料庫之 間發生的例外狀況 int loop; EXEC SQL BEGIN DECLARE SECTION; varchar d

10、name16, fname16, ; char ssn10, bdate11, ; int dno, dnumber, SQLCODE, ; EXEC SQL END DECLARE SECTION; 22 連結資料庫的SQL命令 連結 (同時可以有多個連結存在，但是同時只 有一個連結是使用中) CONNECT TO server-name AS connection- name AUTHORIZATION user-account-info; 變更目前使用中的連結 SET CONNECTION connection-name; 中斷連結 DISCONNECT connection-name;

11、 23 在C程式中嵌入SQL的範例 loop = 1; while (loop) prompt (“Enter SSN: “, ssn); EXEC SQL select FNAME, LNAME, ADDRESS, SALARY into :fname, :lname, :address, :salary from EMPLOYEE where SSN = :ssn; if (SQLCODE = 0) printf(fname, ); else printf(“SSN does not exist: “, ssn); prompt(“More SSN? (1=yes, 0=no): “, l

12、oop); END-EXEC 24 在C程式中嵌入SQL的範例 在處理多筆值組時需要用到指標 (cursor)，也 就是反覆子 (iterator) FETCH命令會將指標移到下一筆值組 CLOSE CURSOR是代表查詢結果的處理已經完 成 25 動態SQL 目標： 在執行期間 (run-time) 執行新的 (之前尚未編 譯過的) SQL敘述 能讓使用者在執行期間從鍵盤上輸入SQL敘述的 程式 讓使用者以滑鼠點選的方式來產生SQL查詢 動態更新比較簡單；動態查詢可能較複雜 因為在編譯期間不知道將會擷取到幾個屬性 26 動態SQL範例 EXEC SQL BEGIN DECLARE SECTI

13、ON; varchar sqlupdatestring256; EXEC SQL END DECLARE SECTION; prompt (“Enter update command:“, sqlupdatestring); EXEC SQL PREPARE sqlcommand FROM :sqlupdatestring; EXEC SQL EXECUTE sqlcommand; 27 在JAVA裡嵌入SQL SQLJ：一種將SQL敘述嵌入在JAVA語言中 的標準 SQLJ翻譯器 (translator) 會將SQL敘述翻譯 成Java程式碼 準備透過JDBC介面來執行 必須匯入指定的類別

14、如java.sql 28 Java資料庫的連結 JDBC： 一組SQL連結函式呼叫，讓Java程式可以存取 資料庫 有JDBC函式的Java程式，可以存取任何有 JDBC驅動程式的關聯式DBMS JDBC能讓程式連結到多個資料庫 (也就是所謂 的資料來源，data source) 29 JDBC資料庫存取的步驟 先匯入 (import)JDBC程式庫(java.sql.*) 載入JDBC驅動程式： Class.forname(“oracle.jdbc.driver.OracleDrive r”) 定義適當的變數 建立一個連結物件 (使用getConnection) 使用Statement類別建立一個敘述物件： 1. PreparedStatment2. CallableStatement 指定敘述參數 (以問號指定) 將參數繫結 (bound) 到某個程式變數 藉由JDBC的executeQuery來執行物件所參考的SQL敘 述 處理查詢結果 (放在型態為Resu