在應(yīng)用中使用SQL-課件

1、第7章 在應(yīng)用中使用SQL7.1 嵌入式SQL7.2 存儲過程7.3 ODBC簡介7.4 JDBC簡介7.5 觸發(fā)器7.6 小結(jié)7.1 嵌入式SQL 在高級程序設(shè)計語言中使用SQL語句操縱數(shù)據(jù)庫需要解決兩個問題：采用某種語法形式使得編譯程序可以區(qū)分SQL語句和宿主語言的語句；提供一種機(jī)制使得SQL語言和宿主語言之間可以交換數(shù)據(jù)和執(zhí)行狀態(tài)。7.1 嵌入式SQL 7.1.1嵌入式SQL的一般形式 在嵌入式SQL中，為了能夠區(qū)分SQL語句與宿主語言語句，所有SQL語句都必須加前綴EXEC SQL。例如， EXEC SQL DROP TABLE SC; EXEC SQL SELECT * FROM S

2、tudent; EXEC SQL GRANT UPDATE ON Student TO User1;7.1 嵌入式SQL 7.1.1嵌入式SQL的一般形式 為了不修改宿主語言的編譯器，DBMS提供一個預(yù)編譯器，預(yù)編譯器識別嵌入式SQL語句，將它們換成SQL函數(shù)庫中的函數(shù)調(diào)用，將最初的宿主語言和嵌入式SQL的混合體轉(zhuǎn)換成純宿主語言的代碼，然后由編譯器進(jìn)行通常的編譯和連接操作，最終生成可執(zhí)行代碼，完成過程控制和數(shù)據(jù)庫操作，具體過程如圖7.1所示。7.1 嵌入式SQLHost language+Embedded SQLPreprocessorHost language+Function callsC

3、ompilerObject-codeProgramSQL library圖7.1 嵌入式SQL的處理過程7.1 嵌入式SQL 7.1.2 嵌入式SQL語句與宿主語言的通信 將SQL嵌入到高級語言中混合編程，SQL語句負(fù)責(zé)操縱數(shù)據(jù)庫，高級語言語句負(fù)責(zé)控制程序流程。這時程序中含有兩種不同計算模型的語句，一種是描述性的面向集合的SQL語句，一種是過程性的高級語言語句，SQL標(biāo)準(zhǔn)主要使用宿主變量在它們之間互相交換數(shù)據(jù)，進(jìn)行通信。7.1 嵌入式SQL 宿主變量使用聲明節(jié)（declare section）定義宿主變量，格式如下： EXEC SQL BEGIN DECLARE SECTION 按照宿主語言的

4、語法定義的變量 EXEC SQL END DECLARE SECTION7.1 嵌入式SQL 7.1.2 嵌入式SQL語句與宿主語言之間的通信例如：EXEC SQL BEGIN DECLARE SECTIONcharSno8;charSname9;charSsex3;shortSage;charSdept21;charSQLSTATE6;EXEC SQL END DECLARE SECTION7.1 嵌入式SQL7.1.3 查詢結(jié)果為單個記錄的SELECT語句 在嵌入式SQL中，查詢結(jié)果為單個記錄的SELECT語句使用INTO子句把查詢結(jié)果傳送到宿主變量，供宿主語言繼續(xù)處理。該語句的一般格式為

5、： 7.1 嵌入式SQLEXEC SQL SELECT ALL|DISTINCT , INTO , FROM , WHERE GROUP BY HAVING ORDER BY ASC|DESC;7.1 嵌入式SQL7.1.3 查詢結(jié)果為單個記錄的SELECT語句 1.INTO子句、WHERE子句的條件表達(dá)式、HAVING短語的的條件表達(dá)式中均可以使用宿主變量。 2.查詢返回的記錄中，可能某些列為空值。如果INTO子句中宿主變量后面跟有指示變量，則當(dāng)查詢得出的某個數(shù)據(jù)項為空值時，系統(tǒng)會自動將相應(yīng)宿主變量后面的指示變量置為負(fù)值，不再向宿主變量賦值。 7.1 嵌入式SQL7.1.3 查詢結(jié)果為單個記

6、錄的SELECT語句 3.如果查詢結(jié)果實際上并不是單條記錄，而是多條記錄，則程序出錯，DBMS將SQLSTATE的值設(shè)置為21000。7.1 嵌入式SQL例 1 查詢某個學(xué)生的信息，這個學(xué)生的學(xué)號存放在宿主變量Sno中，并且將查詢得到的學(xué)生信息存放到上一小節(jié)定義的變量中。EXEC SQL SELECT Sname, Ssex, Sage, SdeptINTO :Sname, :Ssex, :Sage, :SdeptFROM StudentWHERE Sno= :Sno;7.1 嵌入式SQL例 2 查詢某個學(xué)生選修某門課程的成績。由于學(xué)生的成績可能是空值，使用了指示變量gradenullflag

7、。EXEC SQL SELECT GradeINTO :grade :gradenullfalgFROM SCWHERE Sno=:Sno AND Cno=:Cno;7.1 嵌入式SQL7.1.4 游標(biāo) SQL語言與宿主語言有不同的數(shù)據(jù)處理方式。SQL語言是面向集合的，一條SQL語句將產(chǎn)生或處理多條記錄。而宿主語言是面向記錄的，一組宿主變量一次只能存放一條記錄。所以僅使用宿主變量并不能完全滿足SQL語句向應(yīng)用程序輸出數(shù)據(jù)的要求，為此SQL引入了游標(biāo)的概念，用游標(biāo)來協(xié)調(diào)這兩種不同的處理方式。7.1 嵌入式SQL 游標(biāo)（Cursor）是系統(tǒng)開設(shè)的一個數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果。游標(biāo)有一個

8、名字，可以通過游標(biāo)逐一獲取記錄，并賦予宿主語言的宿主變量，交由宿主語言進(jìn)一步處理。如圖7.2。7.1 嵌入式SQL記錄1記錄2記錄n游標(biāo)圖7.2游標(biāo)示意圖游標(biāo)包括以下兩個部分：1、游標(biāo)結(jié)果集（cursor result set） 由定義游標(biāo)的SELECT語句返回的行的集合。2、游標(biāo)的位置（cursor position） 指向這個集合中某一行的指針。7.1 嵌入式SQL一、使用游標(biāo)讀取數(shù)據(jù)使用游標(biāo)處理數(shù)據(jù)的流程如圖7.3。是否使用DECLARE CURSOR語句聲明游標(biāo)使用OPEN語句打開游標(biāo)使用FETCH INTO語句從游標(biāo)中提取數(shù)據(jù)使用CLOSE語句關(guān)閉游標(biāo)最后一個元組？圖7.3使用游標(biāo)的

9、一般過程7.1 嵌入式SQL1、聲明游標(biāo)格式：EXEC SQL DECLARE cursor-name INSENSITIVE SCROLL CURSORFOR SELECT statement FOR READ ONLY SELECT語句定義了游標(biāo)結(jié)果集，游標(biāo)名用于對游標(biāo)的各種操作。使用INSENSITIVE通知DBMS在游標(biāo)存續(xù)期間，不允許其它的事務(wù)修改游標(biāo)結(jié)果集中的數(shù)據(jù)。7.1 嵌入式SQL 如果游標(biāo)不修改數(shù)據(jù)庫中的數(shù)據(jù)，使用FOR READ ONLY告知DBMS，DBMS將允許這樣的游標(biāo)和INSENSITIVE類型的游標(biāo)并發(fā)執(zhí)行。 默認(rèn)情況下，游標(biāo)采用順序處理的方式，依次處理結(jié)果集中的

10、記錄。根據(jù)需要，也可以將游標(biāo)定義為滾動（SCROLL）游標(biāo)。對滾動游標(biāo)，SQL提供了若干命令將游標(biāo)移動到希望的位置。7.1 嵌入式SQLnext 把游標(biāo)移動到當(dāng)前游標(biāo)所指下一個記錄prior 把游標(biāo)移動到當(dāng)前游標(biāo)所指上一個記錄first 把游標(biāo)移動到第一個記錄last 把游標(biāo)移動到最后一個記錄7.1 嵌入式SQLabsolute n 如果n是一個正整數(shù)，把游標(biāo)移動到從前往后計數(shù)的第n個記錄；如果n是一個負(fù)整數(shù)，把游標(biāo)移動到從后數(shù)的第n個記錄。relative n 如果n是一個正整數(shù)，把游標(biāo)移動相對于當(dāng)前游標(biāo)所指記錄之后的第n個記錄；如果n是一個負(fù)整數(shù)，把游標(biāo)移動相對于當(dāng)前游標(biāo)所指記錄之前的第n

11、個記錄。7.1 嵌入式SQL例 3 聲明存取計算機(jī)系全體學(xué)生的游標(biāo)。EXEC SQL DECLARE dept_computer CURSORFOR SELECT *FROM StudentWHERE Sdept= 計算機(jī)FOR READ ONLY;7.1 嵌入式SQL2、打開游標(biāo)格式：EXEC SQL OPEN ; 打開游標(biāo)后，DBMS執(zhí)行與游標(biāo)相關(guān)聯(lián)的SELECT語句，并把查詢結(jié)果存放到游標(biāo)中，游標(biāo)指向第1個記錄。例如，EXEC SQL OPEN dept_computer;圖7.4 游標(biāo)dept_computer的內(nèi)容2000012,王林,男,19,計算機(jī)游標(biāo)2000014,葛波,女,1

12、8,計算機(jī)7.1 嵌入式SQL3、存取游標(biāo) 如果游標(biāo)不是滾動游標(biāo)，使用FETCH語句讀取當(dāng)前游標(biāo)所指的記錄到宿主變量中，然后，游標(biāo)自動移向下一個記錄：EXEC SQL FETCH FROM cursor_name INTO variable-list; 7.1 嵌入式SQL 如果游標(biāo)是滾動游標(biāo)，先將游標(biāo)移動到所指定的記錄，然后將游標(biāo)所指的記錄存放到宿主變量中： EXEC SQL FETCH NEXT | PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE nFROM cursor_nameINTO variable-list;7.1 嵌入式SQL4、關(guān)閉游標(biāo) 游標(biāo)使用完畢

13、后，要釋放其所占的系統(tǒng)資源。使用下面的語句：EXEC SQL CLOSE cursor_name; 游標(biāo)指向了結(jié)果集最后一個記錄之后時，SQLSTATE的值被設(shè)置為02000，可以用這個條件作為循環(huán)的結(jié)束條件。7.1 嵌入式SQL例 4 通過游標(biāo)dept_computer讀取每個學(xué)生的信息，并顯示。 EXEC SQL BEGIN DECLARE SECTION charSno8;charSname9;charSsex3;shortSage;charSdept21;charSQLSTATE6;7.1 嵌入式SQLEXEC SQL END DECLARE SECTION-聲明游標(biāo)EXEC SQL

14、DECLARE dept_computer CURSORFOR SELECT *FROM StudentWHERE Sdept= 計算機(jī)FOR READ ONLY;-打開游標(biāo)EXEC SQL OPEN dept_computer;7.1 嵌入式SQL-讀取第1條記錄EXEC SQL FETCH dept_computer INTO :Sno, :Sname,:Ssex,:Sage,:Sdept;while(strcmp(SQLSTATE, 02000) != 0)/輸出 EXEC SQL FETCH dept_computer INTO :Sno, :Sname,:Ssex,:Sage,:Sd

15、ept;-關(guān)閉游標(biāo)EXEC SQL CLOSE dept_computer;7.1 嵌入式SQL二、使用游標(biāo)修改數(shù)據(jù) 上面介紹了通過游標(biāo)把數(shù)據(jù)庫的批量數(shù)據(jù)傳送到宿主變量中做進(jìn)一步處理。游標(biāo)的另一個作用是可以通過游標(biāo)修改數(shù)據(jù)庫中的數(shù)據(jù)。 使用游標(biāo)修改數(shù)據(jù)要注意兩件事： 1.聲明游標(biāo)時沒有加FOR READ ONLY關(guān)鍵字； 2.一般不要修改基于多表的游標(biāo)。7.1 嵌入式SQL1、UPDATE語句格式：UPDATE table-name SET column- name = expressionWHERE CURRENT OF cursor_name功能：修改數(shù)據(jù)庫中與當(dāng)前游標(biāo)所指向的記錄相對應(yīng)的

16、元組的列值。7.1 嵌入式SQL2、DELETED語句DELETE FROM table-nameWHERE CURRENT OF cursor_name功能：刪除數(shù)據(jù)庫中與當(dāng)前游標(biāo)所指向的記錄相對應(yīng)的元組。7.1 嵌入式SQL例 5 通過游標(biāo)dept_computer給每個學(xué)生年齡增加1歲EXEC SQL BEGIN DECLARE SECTIONcharSQLSTATE6;EXEC SQL END DECLARE SECTION-聲明游標(biāo)EXEC SQL DECLARE dept_computer CURSORFOR SELECT *FROM StudentWHERE Sdept= 計算機(jī)

17、FOR UPDATE OF Sage -只允許修改列Sage的值;7.1 嵌入式SQL-打開游標(biāo)EXEC SQL OPEN dept_computer;-讀取第1條記錄EXEC SQL FETCH dept_computer;while(strcmp(SQLSTATE, 02000) != 0)EXEC SQL UPDATE Student SET Sage = Sage + 1WHERE CURRENT OF dept_computer;FETCH dept_computer;-讀下一條記錄;-關(guān)閉游標(biāo)EXEC SQL CLOSE dept_computer;7.1 嵌入式SQL7.1.5

18、動態(tài)SQL簡介動態(tài)SQL方法允許在程序運行過程中臨時“組裝”SQL語句，主要有三種形式： 1.語句可變：允許用戶在程序運行時輸入完整的SQL語句。 2.條件可變。 3.數(shù)據(jù)庫對象、查詢條件均可變。7.1 嵌入式SQL一、PREPARE PREPARE把存放在宿主變量中的一個字符串“準(zhǔn)備”為一個SQL語句，所謂“準(zhǔn)備”就是通過和DBMS的通信，對SQL語句進(jìn)行分析和生成執(zhí)行計劃。具體格式為：PREPARE stmt_name FROM :host-variable 其中，stmt_name為待準(zhǔn)備的SQL語句命名，供其它動態(tài)SQL命令引用，stmt_name在一個程序模塊必需是唯一的。7.1 嵌

19、入式SQL二、EXECUTE EXECUTE執(zhí)行由PREPARE準(zhǔn)備的SQL語句：EXECUTE prepared_stmt_name USING : host-variabel ,.; prepared_stmt_name是由某個PREPARE準(zhǔn)備的SQL語句，USING后面的宿主變量用于替換SQL語句的？參數(shù)，有幾個?參數(shù)就必須有幾個宿主變量，數(shù)據(jù)類型必須兼容，并且按照位置對應(yīng)的原則進(jìn)行替換。7.1 嵌入式SQL二、EXECUTE 例 6 生成一個向SC表插入任意元組的SQL語句EXEC SQL BEGIN DECLARE SECTION;char prep = INSERT INTO s

20、c VALUES(?,?,?);char sno8;char cno5;short grade;EXEC SQL END DECLARE SECTION;7.1 嵌入式SQL二、EXECUTEEXEC SQL PREPARE prep_stat FROM :prep;while (strcmp(SQLSTATE, 00000) = = 0) scanf(%s , sno); scanf(%s , cno); scabf(%d , grade); EXEC SQL EXECUTE prep_stat USING :sno, :cno, :grade;7.1 嵌入式SQL三、EXECUTE IMM

21、EDIATE EXECUTE IMMEDIATE語句結(jié)合了PREPARE和EXECUTE的功能，準(zhǔn)備一個SQL語句并且立即執(zhí)行它。因為準(zhǔn)備一個SQL語句需要和DBMS通信，開銷比較大，所以，采用PREPARE和EXECUTE方式適合于準(zhǔn)備的語句要多次執(zhí)行的方式，而 EXECUTE IMMEDIATE適用于只執(zhí)行一次的情形。語句格式為：EXECUTE IMMEDIATE : :host-variable7.2 存儲過程 存儲過程是SQL語句和可選控制流語句的預(yù)編譯集合。它以一個名稱存儲并作為一個單元處理。存儲過程存儲在數(shù)據(jù)庫內(nèi)，由應(yīng)用程序調(diào)用執(zhí)行。存儲過程包含流程控制以及對數(shù)據(jù)庫的查詢，可接受參

22、數(shù)、輸出參數(shù)、返回單個或多個結(jié)果集以及執(zhí)行狀態(tài)，而且允許定義變量、條件執(zhí)行、循環(huán)等編程功能。7.2 存儲過程 存儲過程有以下特點： 1.確保數(shù)據(jù)訪問和操作的一致性，提高應(yīng)用程序的可維護(hù)性； 2.提高系統(tǒng)的執(zhí)行效率； 3.提供一種安全機(jī)制； 4.減少了網(wǎng)絡(luò)的流量負(fù)載； 5.若要改變業(yè)務(wù)規(guī)則或策略，只需改變存儲過程和參數(shù)，不必修改應(yīng)用程序。7.2 存儲過程7.2.1 SQL/PSM 編寫存儲過程以及后面介紹的觸發(fā)器需要一種通用的程序設(shè)計語言。SQL/PSM（persistent stored modules）標(biāo)準(zhǔn)制定了一個程序設(shè)計語言，這個語言可以被DBMS解釋執(zhí)行。 SQL/PSM提供了通用程序

23、設(shè)計語言的定義變量、流程控制和存儲過程定義以及調(diào)用語句。在PSM標(biāo)準(zhǔn)出現(xiàn)之前，一些DBMS廠商就提供了自己的編程語言。 7.2 存儲過程一、存儲過程和函數(shù)的定義和調(diào)用 SQL/PSM中的存儲過程和函數(shù)與PASCAL語言中的過程和函數(shù)十分相似，但是語法比較復(fù)雜，下面給出一個簡化版本。CREATE PROCEDURE (IN | OUT ,.)routine-body;CREATE FUCTION ( variable-name datatype,.) RETURN datatyperoutine-body;7.2 存儲過程 在定義存儲過程和函數(shù)時，首先要賦予一個在數(shù)據(jù)庫范圍內(nèi)唯一的名字，然后給出

24、參數(shù)。對于存儲過程，要進(jìn)一步指出每個參數(shù)是輸入?yún)?shù)還是輸出參數(shù)。對于函數(shù)，在函數(shù)體內(nèi)要使用RETURN 語句返回一個值。過程體和函數(shù)體使用SQL/PSM提供的過程控制語句和SQL語句編寫。7.2 存儲過程 SQL/PSM定義的函數(shù)可以出現(xiàn)在SQL語句中可以出現(xiàn)常數(shù)的地方。存儲過程序要使用CALL語句調(diào)用。在過程體或函數(shù)體內(nèi)直接使用CALL (argument list)，在嵌入式SQL中要在CALL語句之前加上EXEC SQL。例如，下面的過程完成一個學(xué)生的轉(zhuǎn)系任務(wù)，學(xué)生的學(xué)號和要轉(zhuǎn)到的系名稱作為過程的輸入?yún)?shù)。7.2 存儲過程CREATE PROCEDURE transform-dept(I

25、N sno char(7), IN dept char(20) UPDATE student； SET Sdept = dept； WHERE Sno = sno;7.2 存儲過程二、變量的聲明和賦值 DECLARE語句為一個變量指定變量名稱和數(shù)據(jù)類型，分配存儲空間。變量的作用范圍是存儲過程，DECLARE語句應(yīng)該出現(xiàn)在其它可執(zhí)行語句之前。DECLARE ; SET語句為變量賦值，先計算出等號右邊的表達(dá)式的值，然后把得到的值賦于變量，表達(dá)式的構(gòu)成與其它程序設(shè)計語言相同。SET = ;7.2 存儲過程三、分支語句 SQL/PSM的分支語句的功能同C語言這樣的高級程序設(shè)計語言，但在語法上和邏輯條件

26、的構(gòu)成上略有不同。IF THEN ELSEIF THEN ELSIFELSE END IF7.2 存儲過程四、循環(huán)語句 SQL/PSM的循環(huán)語句多用于處理游標(biāo)，有多種形式，最基本的語句形式為：LOOPEND LOOP; 一般在LOOP前加上一個語句標(biāo)號，語句標(biāo)號的形式是一個名字后面緊跟一個冒號（:），一般在循環(huán)體中要對循環(huán)條件進(jìn)行測試，當(dāng)條件滿足時使用下面的語句結(jié)束循環(huán)，開始執(zhí)行END LOOP后面的語句。LEAVE ;7.2 存儲過程四、循環(huán)語句 例 7 給出選修某門課程的成績最好的學(xué)生的姓名和所在系，如果有多個這樣的學(xué)生，則任意給出一個。CREATE PROCEDURE course_li

27、st(INvcno char(4),OUT vsname char(8),OUT vsdept char(2)DECLAREmax_grade int;DECLAREvgradeint;DECLAREvsnochar(7);7.2 存儲過程四、循環(huán)語句DECLARE cur_sc CURSOR FOR SELECT Sno, Grade FROM SC WHERE Cno = vcno;BEGINSELECT max(grade) INTO max_grade FROM SC WHERE Cno = vcno;OPEN cur_sccur_loop: LOOPFETCH cur_sc INTO

28、 vsno, vgrade;IF vgrade = max_grade THENSELECT Sname, Sdept INTO vsname, vsdeptFROM Student WHERE Sno = vsno;LEAVE cur_loop;END IF;END LOOP;CLOSE cur_sc;END;7.2 存儲過程四、循環(huán)語句 例 8 使用FOR語句改寫上例。CREATE PROCEDURE course_list(INvcnochar(4),OUTvsnamechar(8),OUTvsdeptchar(20)DECLAREmax_gradeint;DECLAREvgradein

29、t;DECLAREvsnochar(7);7.2 存儲過程四、循環(huán)語句 BEGINSELECT max(grade) INTO max_grade FROM SC WHERE Cno = vcno;FOR cur_loop AS cur_sc CURSOR FORSELECT Sno, Grade FROM SC WHERE Cno = vcnoDOIF vgrade = max_grade THENSELECT Sname, Sdept INTO vsname, vsdeptFROM Student WHERE Sno = vsno;LEAVE cur_loop;END IF;END FOR

30、;END;7.2 存儲過程五、異常處理 DBMS在執(zhí)行SQL語句時，如果遇到錯誤則將變量SQLSTATE的值設(shè)置為不等于00000 的長度為5的字符串。 SQL/PSM提供了異常處理句柄(exception handler)功能，異常處理句柄被封裝在一個由BEGIN和END界定的語句塊中，當(dāng)語句塊中的某個語句發(fā)生了異常處理句柄所捕獲的錯誤，則執(zhí)行異常處理句柄預(yù)先定義的代碼。7.2 存儲過程異常處理句柄的格式為：DECLARE HANDLER FOR condition list是用逗號分隔開的一組條件，條件可以用DECLARE語句定義，或直接用SQLSTATE所規(guī)定的代碼。7.2 存儲過程 n

31、ext step約定執(zhí)行完statement后需要做的工作，有三種情形： 1.CONTINUE 繼續(xù)執(zhí)行引起錯誤的語句的下一條語句 2.EXIT 跳出異常處理句柄所在的語句塊，繼續(xù)執(zhí)行END后面的語句 3.UNDO 首先撤銷引起錯誤的語句對數(shù)據(jù)庫所作的所有修改，以后的操作同EXIT。7.2 存儲過程例 9 查詢學(xué)生王林所在的系，如果有多個學(xué)生叫王林或者沒有叫王林的學(xué)生，則返回一串星號。CREATE PROCEDURE course_list(OUT vsdept ar(20)DECLARE Not_Found CONDITION FOR SQLSTATE 02000;DECLARE Too_M

32、any CONDITION FOR SQLSTATE 21000;BEGINDECLARE EXIT HANDLER FOR Not_Found, Too_ManySET vsdept = *;SELECT Sdept INTO vsdept FROM Student WHERE Sname = 王林;END;7.2 存儲過程7.2.2 PL/SQL PL/SQL是Oracle的編程語言，具有支持內(nèi)存變量、條件結(jié)構(gòu)和循環(huán)結(jié)構(gòu)、過程和函數(shù)以及執(zhí)行SQL語句的能力。本節(jié)簡單介紹PL/SQL的有關(guān)概念。一、PL/SQL的塊結(jié)構(gòu) PL/SQL程序的基本結(jié)構(gòu)是塊。所有的PL/SQL程序都是由塊組成的，這

33、些塊之間還可以互相嵌套，每個塊完成一個邏輯操作。基本塊由定義部分、執(zhí)行部分和異常處理部分組成。除執(zhí)行部分是必須的以外，其它的部分都是可選的。7.2 存儲過程例 10 向關(guān)系Student中插入10個學(xué)生,學(xué)生編號從1001到1010。DECLAREsnoNUMBER;/*定義部分*/BEGIN/*執(zhí)行部分*/sno := 1001;LOOP INSERT INTO Student(Sno) VALUES(to_char(:sno, 9999); sno := sno +1; EXIT WHEN sno 1010; END LOOP;EXCEPTION/*異常處理*/WHEN others TH

34、EN/*處理異常情況others*/dbms_output.put_line(Error);END;7.2 存儲過程二、變量和常量的定義1、PL/SQL中定義變量的語法形式是：變量名 數(shù)據(jù)類型 NOT NULL := 初值表達(dá)式變量名由字母、數(shù)字、下劃線（_）、美圓符號($)和英鎊符號(#)組成，以字母開頭，長度不超過30個字符。保留字不能做變量名。除此之外，PL/SQL還提供了構(gòu)造數(shù)據(jù)類型記錄和表以及類型轉(zhuǎn)換函數(shù)。7.2 存儲過程2、常量的定義類似于變量的定義：常量名稱 數(shù)據(jù)類型 CONSTANT := 常量表達(dá)式常量必須要給一個值，并且其值在存在期間或常量的作用域內(nèi)不能改變。如果試圖修改它

35、，PL/SQL將返回一個異常。3、賦值語句變量名 := 表達(dá)式7.2 存儲過程三、控制結(jié)構(gòu)1、條件控制語句形式一：IF條件 THEN語句序列1;ELSIF條件2 THEN語句序列2；. ELSE最后的語句序列；END IF;7.2 存儲過程2、循環(huán)控制語句 PL/SQL 提供了三種不同的循環(huán)結(jié)構(gòu)。每種循環(huán)結(jié)構(gòu)都能夠重復(fù)地執(zhí)行一組PL/SQL語句，并可根據(jù)條件終止循環(huán)的執(zhí)行。形式一：LOOP語句序列;END LOOP;跳出循環(huán)語句：形式一：EXIT形式二：EXIT WHEN條件7.2 存儲過程2、循環(huán)控制語句 例 11 如果選修課程1156的學(xué)生人數(shù)不超過30人時則可以加入選課的學(xué)生。DECLA

36、REp_total NUMBER;BEGINSELECT COUNT(*) INTO p_total FROM SC WHERE Cno = 1156;IF (p_total =90 AND score = 80) THENgrade := B;ELSIF (score = 70) THENgrade := C;ELSIF (score = 60) THENgrade := D;ELSEgrade := E;END IF;END;7.2 存儲過程形式二：WHILE 條件 LOOP語句序列;END LOOP;形式三：FOR 循環(huán)變量 IN REVERSE 下限.上限 LOOP語句序列；END L

37、OOP;7.2 存儲過程例 13 求1到10的和用LOOP實現(xiàn)DECLAREp_sumNUMBER :=0;/*p_sum的初始值為0*/p_incNUMBER;BEGINp_inc := 1;LOOPp_sum := p_sum + p_inc;p_inc := p_inc + 1;EXIT WHEN (p_inc 10);END LOOP;dbms_output.put_line(p_sum);/*Oracle包提供的輸出函數(shù)*/END;7.2 存儲過程用WHILE實現(xiàn)DECLAREp_sumNUMBER :=0;p_incNUMBER;BEGINp_inc := 1;WHILE (p_i

38、nc = 0 THEN INSERT INTO SC VALUES(sno,cno,grade);ELSERAISE must_be_positive_grade;END IF;EXCEPTIONWHEN must_be_positive_grade THENdbms_output.put_line(Grade Must Be Positive | grade);END;7.2 存儲過程例 16 異常處理后控制不再返回異常語句所在的基本塊。DECLARE ratio NUMBER(3,1); t1 NUMBER; t2 NUMBER;BEGIN INSERT INTO Course(1234,

39、 Data Structure, 1210,4); SELECT COUNT(*) INTO t1 FROM Student; SELECT COUNT(*) INTO t2 FROM SC WHERE Cno = 1234;ratio := t1 / t2 *100;INSERT INTO Course(1235, Data MIning, 1210,4);EXCEPTIONWHEN ZERO_DIVIDE THEN.END;7.2 存儲過程五、存儲過程和函數(shù) PL/SQL塊主要有兩種類型，即命名塊和匿名塊。前面介紹的都是匿名塊，匿名塊每次使用時都要進(jìn)行編譯，它不能被存儲到數(shù)據(jù)庫種，也不能在

40、其它的PL/SQL塊中調(diào)用。過程和函數(shù)是命名塊，被編譯后保存在數(shù)據(jù)庫中，可以被反復(fù)調(diào)用，它們的運行速度較快。7.2 存儲過程1、存儲過程存儲過程是利用PL/SQL編寫的一組規(guī)定的操作。此過程存儲在數(shù)據(jù)庫中，這就是將它稱為存儲過程的原因。語法形式為：CREATE OR REPLACE PROCEDURE 過程名（參數(shù)1, 參數(shù)2, .） AS | IS變量聲明;BEGIN語句組；END;7.2 存儲過程例 17 計算某個員工的應(yīng)發(fā)獎金CREATE OR REPLACE PROCEDURE calc_bonus(emp_id IN INTEGER) IShire_dateDATE;/*進(jìn)某單位工作

41、的日期*/bonusREAL;BEGIN/*獎金為工資的10%*/SELECT sal * 0.10, hiredate INTO bonus,hire_date FROM emp WHERE empno = emp_id;IF (bonus IS NULL) THEN INSERT INTO emp_bonus(empno,bonus) VALUES(emp_id,NULL);7.2 存儲過程例 17 計算某個員工的應(yīng)發(fā)獎金ELSE IFMONTHS_BETWEEN(SYSDATE,hire_date) 60 THEN /工作時間超過5年bonus := bonus + 500;獎金增加50

42、0元*/END IF;INSERT INTO emp_bonus(empno,bonus) VALUES(emp_id,bonus); END IF;END;7.2 存儲過程2、函數(shù)函數(shù)類似于存儲過程。其語法是：CREATE OR REPLACE FUNCTION 函數(shù)名（參數(shù)1, 參數(shù)2, .） RETURN DataType AS | IS變量聲明;BEGIN語句組；END;7.2 存儲過程例 18 計算某個員工的應(yīng)發(fā)獎金CREATE OR REPLACE FUNCTION calc_bonus_func(emp_id INTEGER) RETURN REAL IShire_dateDAT

43、E;bonusREAL;BEGINSELECT sal * 0.10, hiredate INTO bonus,hire_date FROM emp WHERE empno = emp_id;7.2 存儲過程例 18 計算某個員工的應(yīng)發(fā)獎金IF (bonus IS NULL) THENRETURN ( -1 );ELSEIF MONTHS_BETWEEN(SYSDATE,hire_date) 60 THENbonus := bonus + 500;END IF;RETURN ( bonus ); END IF;END;7.2 存儲過程3、存儲過程和函數(shù)的區(qū)別 過程和函數(shù)都是經(jīng)過編譯以后保存在數(shù)

44、據(jù)庫中的。 通過將參數(shù)的模式設(shè)置為OUT，則過程也可以返回值，函數(shù)也可以返回多于一個值（但不提倡）。參數(shù)的模式有IN,OUT和IN OUT，默認(rèn)的參數(shù)模式是IN。IN表示參數(shù)是向過程（函數(shù)）傳送值的，該參數(shù)的值在過程（函數(shù)）體中不能被改變。OUT表示該參數(shù)的值是由過程（函數(shù)）返回的。IN OUT具有IN和OUT的雙重意義。7.2 存儲過程 過程和函數(shù)都可以有聲明部分、執(zhí)行部分和異常處理部分。 在使用過程和函數(shù)時，過程要作為一個語句，而函數(shù)必須出現(xiàn)在SQL語句中（所有可以出現(xiàn)數(shù)值的地方） 過程中可以包含INSERT、UPDATE和DELETE語句，而函數(shù)中不能出現(xiàn)這些語句。7.2 存儲過程 例

45、19 計算empno從1到10的員工的獎金，并把計算結(jié)果存放到關(guān)系emp_bonus中。利用存儲過程來實現(xiàn)DECLAREp_empnoNUMBER;BEGINFOR p_empno IN 1.10LOOPcalc_bonus(p_empno);/*存儲過程作為一個語句*/END LOOP;END;7.2 存儲過程 例 19 計算empno從1到10的員工的獎金，并把計算結(jié)果存放到關(guān)系emp_bonus中。利用函數(shù)來實現(xiàn)DECLAREp_empnoNUMBER;p_bonusREAL;BEGINFOR p_empno IN 1.10LOOPSELECT calc_bonus_func(p_emp

46、no) INTO p_bonus FROM DUAL;INSERT INTO emp_bonus(p_empno,p_bonus);END LOOP;END;7.2 存儲過程4、刪除過程和函數(shù) 當(dāng)一個存儲過程或函數(shù)不再需要時，可以用DROP語句將它們刪除掉。例 20刪除calc_bonus和calc_bonus_funcDROP PROCEDURE calc_bonus;DROP FUNCTIONcalc_bonus_func;7.2 存儲過程六、游標(biāo) 如果SELECT語句只返回一行，則可以將該結(jié)果存放到變量中，例如語句SELECT COUNT(*) INTO p_total FROM Stu

47、dent可以正確執(zhí)行。而語句SELECT sno INTO p_sno FROM Student WHERE Sname = 王林就可能出現(xiàn)錯誤，觸發(fā)TOO_MANY_ROWS異常，因為可能有多個人叫王林。要處理這種情況就必須使用游標(biāo)，游標(biāo)的概念已經(jīng)在3.11節(jié)中介紹過了。7.2 存儲過程7.2.3 Transact-SQL Transact-SQL是Microsoft的一種編程語言，具有所有語言所具有的要素，如：數(shù)據(jù)類型、常量和變量、函數(shù)、表達(dá)式和流程控制語句。不僅提供對SQL標(biāo)準(zhǔn)的支持，而且包含了Microsoft對SQL的一系列擴(kuò)展。7.2 存儲過程一、常量 常量是在程序運行中值不發(fā)生變

48、化的量，常量的格式取決于它所表示的數(shù)據(jù)類型。根據(jù)常見的數(shù)據(jù)類型，分為字符串常量、整型常量、實型常量、貨幣常量和日期時間常量。下面舉例說明各類常量的使用方法。7.2 存儲過程1、字符串常量 字符串常量分為ASCII字符串常量和Unicode字符串常量。ASCII字符串常量是用單引號括起來，由ASCII字符構(gòu)成的字符串；Unicode字符串常量前面有一個N，代表國際語言。每個ASCII字符由一個字節(jié)存儲，而每個Unicode字符需要兩個字節(jié)的存儲空間。7.2 存儲過程ASCII字符串常量Have a nice day! 馬翔 2563 如果單引號是字符串的一部分，則需要用兩個單引號表示。Jones

49、 homeUnicode字符串常量NHave a nice day! N馬翔7.2 存儲過程2、整型常量 整型常量由數(shù)字0-9以及+/-號組成的有意義的串。如：123、-9811、+893、實型常量 實型常量有定點和浮點兩種表示形式。定點實型常量 由數(shù)字0-9、小數(shù)點和正負(fù)號組成有意義的串。如：1.0、3467.584。浮點實型常量 由數(shù)字0-9、小數(shù)點、正負(fù)號和字母E組成有意義的串。如：0.68E+3、1E10、-23E2。7.2 存儲過程4、貨幣常量 貨幣常量是以$符號為前綴的整型或?qū)嵭统Ａ?，用來表示貨幣值，精度?位小數(shù)。如$100、-$52、$16.37。5、日期時間常量 日期常量是用

50、單引號括起來的有效的日期和時間組成的字符串。常見的日期格式September 1,2007、 2007年9月1號、 2007-09-01、 09/01/2007、 20070901常見的時間格式 14:12:00、 02:12PM7.2 存儲過程二、變量 變量就是在程序運行中值會發(fā)生變化的量。變量可用于存放輸入的值以及保存計算結(jié)果等。SQL Server有兩類變量： 1.全局變量。 2.局部變量。7.2 存儲過程1、全局變量 全局變量是SQL Server管理的變量，用戶不能建立全局變量，可以查看全局變量的值，但不能改變?nèi)肿兞康闹怠?全局變量分為兩類，一類反映SQL Server系統(tǒng)的全局變

51、量，另一類反映與一個連接有關(guān)的全局變量。 全局變量的特征是變量名前2個符號必須是。SQL Server提供了大約30多個全局變量。7.2 存儲過程2、局部變量 局部變量是在一定范圍內(nèi)有意義的變量，由用戶建立和使用。局部變量的作用范圍一般為批處理、存儲過程、觸發(fā)器。 局部變量的命名要求第一個字符必須是，由字母、數(shù)字、下劃線等字符組成，不能與SQL Server的保留字、全局變量、存儲過程、表等其它數(shù)據(jù)庫對象重名。 局部變量要用DECLARE語句聲明。 可以用SET或SELECT語句給局部變量賦值。 局部變量可以出現(xiàn)在SQL語句中。7.2 存儲過程三、運算符 運算符完成運算功能。SQL Serve

52、r提供了豐富的運算符，絕大部分的含義同一般的編程語言，這里只是將它們羅列出來，以便于對照學(xué)習(xí)。1、算術(shù)運算符 算術(shù)運算符用于對兩個數(shù)字型的量進(jìn)行數(shù)學(xué)運算。算術(shù)運算符有：+（加）、-（減）、*（乘）、/（除）、%（取余）。其中，+和-還可以作為一元運算符，也可以對datatime和smalldatatime類型的數(shù)據(jù)進(jìn)行運算。7.2 存儲過程三、運算符2、比較運算符 比較運算符用于比較兩個表達(dá)式的值之間的關(guān)系，結(jié)果為布爾值，分為TRUE（真）、FALSE（假）、UNKNOWN（未知）。 比較運算符有：=（等于）、（小于）、（大于）、=（大于等于）、（不等于，另外一個寫法是！=）、?。ú淮笥冢?、！

53、、=、=、 、!=、!、!位運算符：、&、|邏輯運算符：NOT、AND、OR7.2 存儲過程四、函數(shù) 在Transact-SQL語言中，函數(shù)被用來執(zhí)行一些特殊的運算以支持SQL Server的標(biāo)準(zhǔn)命令。SQL Server支持兩種函數(shù)類型：內(nèi)置函數(shù)和用戶定義函數(shù)。其內(nèi)置函數(shù)十分豐富，具體的請參考其它書籍。7.2 存儲過程五、流程控制語句語句功能BEGINEND定義語句塊。IFELSE條件選擇語句，條件成立執(zhí)行IF后語句（第一個分支），否則執(zhí)行ELSE后語句。CASE語句分支處理語句，表達(dá)式可根據(jù)條件返回不同值。WHILE循環(huán)語句，重復(fù)執(zhí)行命令行或程序塊。WAITFOR設(shè)置語句執(zhí)行的延遲時間。B

54、REAK循環(huán)跳出語句。CONTINUE重新啟動循環(huán)語句。跳過CONTINUE后語句，回到WHILE循環(huán)的第一行命令。GOTO無條件轉(zhuǎn)移語句。RETURN無條件返回語句。表7.2 流程控制語句及功能7.2 存儲過程1、語句塊 BEGINEND語句能夠?qū)⒍鄠€Transact-SQL語句組合成一個語句塊，將其視為一個單元處理。在條件語句和循環(huán)等控制流程語句中，當(dāng)符合特定條件便要執(zhí)行兩個或者多個語句時，就需要使用BEGINEND語句。格式：BEGIN 語句1 語句2 語句n END7.2 存儲過程2、IF ELSE語句 IFELSE語句是條件判斷語句，用來判斷當(dāng)某一條件成立時執(zhí)行某段程序，條件不成立時

55、執(zhí)行另一段程序。允許嵌套。格式：IF ELSE 條件表達(dá)式 7.2 存儲過程例 21 從SC數(shù)據(jù)表中求出學(xué)號為2007012同學(xué)的平均成績，如果此平均成績大于或等于60分，則輸出pass信息。if (SELECT AVG(Grade) FROM SC WHERE Sno=2007012)=60)beginprint passend7.2 存儲過程例 22 判斷數(shù)值大小，打印運算結(jié)果。declare q int,r int,s intselect q = 4,r = 5,s = 6 -為三個變量賦值if q rprint qrelse if r sprint rs elseprint sr7.2

56、 存儲過程3、CASE語句 CASE語句可以計算多個條件式，并返回其中一個符合條件的表達(dá)式的結(jié)果。按照使用形式的不同，可以分為簡單CASE語句和搜索CASE語句。7.2 存儲過程1.簡單CASE語句格式： CASE WHEN THEN WHEN THEN ELSE END7.2 存儲過程2.搜索CASE語句格式：CASEWHEN THEN WHEN THEN ELSE END7.2 存儲過程例 23 輸出選修1156號課程的成績（分為優(yōu)、良、中、及格、不及格五個等級）。select Sno, 成績=casewhen Grade=60 and Grade=70 and Grade=80 and

57、Grade=90 then 優(yōu)endfrom SCwhere Cno=11567.2 存儲過程4、WHILE語句 WHILE語句通過布爾表達(dá)式設(shè)置重復(fù)執(zhí)行SQL語句或語句塊的循環(huán)條件。WHILE命令在設(shè)定的條件成立時會重復(fù)執(zhí)行SQL語句或程序塊?？梢允褂肂REAK和CONTINUE語句在循環(huán)內(nèi)部控制WHILE循環(huán)中語句的執(zhí)行。格式：WHILE BEGIN BREAK CONTINUE END7.2 存儲過程例 24 計算從1加到100的值。declare s int,i intset i=0set s=0while i=100beginset s=s+iset i=i+1endprint 1+

58、2+100= + cast(s as char(25)7.2 存儲過程5、RETURN語句 RETURN語句用于在任何時候從過程、批處理或語句塊中結(jié)束當(dāng)前程序、無條件退出，而不執(zhí)行位于RETURN之后的語句，返回到上一個調(diào)用它的程序，在括號內(nèi)可指定一個返回值。格式：RETURN整數(shù)值7.2 存儲過程6、WAITFOR語句 WAITFOR語句用于暫時停止執(zhí)行SQL語句、語句塊或者存儲過程等，直到所設(shè)定的時間已過或者所設(shè)定的時間已到才繼續(xù)執(zhí)行。格式：WAITFOR DELAY | TIME | ERROREXIT | PROCESSEXIT | MIRROREXIT7.2 存儲過程例 25 等待1

59、 小時2 分零3 秒后才執(zhí)行SELECT 語句。waitfor delay 01:02:03select * from SC等到11點12分才執(zhí)行select語句waitfor time 11:12:00select * from SC7.2 存儲過程7、批處理 一個批處理是一組語句，以GO作為結(jié)束標(biāo)志。SQL Server將批處理作為一個工作單位，批處理中的所有語句作為一個整體進(jìn)行詞法分析、語法分析，進(jìn)行編譯和執(zhí)行。在分析階段，若某一條語句有錯誤，則不會執(zhí)行批處理中的任何語句。在執(zhí)行批處理時，某條語句在執(zhí)行中出錯，不會影響到其它語句的執(zhí)行。SQL Server規(guī)定有些語句必須是批處理中的唯一

60、一條語句或者是第一條語句，如，一條DDL語句必須作為一個批處理。7.2 存儲過程7、批處理 例 26 下面的批處理由兩條語句組成select *from studentinsert student(Sno,Sname)values(2007002,王亮)go7.2 存儲過程7、批處理 例 27 局部變量的作用范圍是批處理為變量studentName賦值，然后在SELECT語句中引用，要求賦值和引用在一個批處理中。DECLARE studentName varchar(12) -聲明變量SET studentName = 馬 + 翔 -為變量賦值SELECT *FROM StudentWHERE