數(shù)據(jù)庫原理第8章數(shù)據(jù)庫編程.ppt

第八章 數(shù)據(jù)庫編程,西南民族大學(xué)計(jì)算機(jī)學(xué)院,2019/7/13,第8章 數(shù)據(jù)庫編程,2,引 言,標(biāo)準(zhǔn)SQL是非過程化的查詢語言 優(yōu)點(diǎn)：操作統(tǒng)一、面向集合、功能豐富、使用簡單等。 缺點(diǎn)：流程控制能力欠缺 解決：SQL 編程技術(shù) 應(yīng)用系統(tǒng)中使用SQL編程訪問和管理數(shù)據(jù)庫數(shù)據(jù)的方式主要有： 嵌入式SQL PL/SQL ODBC、JDBC、OLEDB、ADO等,2019/7/13,第8章 數(shù)據(jù)庫編程,3,目 錄,8.1 嵌入式SQL 8.2 存儲過程 8.3 ODBC編程,2019/7/13,第8章 數(shù)據(jù)庫編程,4,8.1 嵌 入 式 SQL,SQL語言提供了兩種不同的使用方式： 交互式 嵌入式 為什么要引入嵌入式SQL SQL語言是非過程性語言 事務(wù)處理應(yīng)用需要高級語言 這兩種方式細(xì)節(jié)上有差別，在程序設(shè)計(jì)的環(huán)境下，SQL語句要做某些必要的擴(kuò)充,2019/7/13,第8章 數(shù)據(jù)庫編程,5,主 要 內(nèi) 容,8.1.1 嵌入式SQL的一般形式 8.1.2 嵌入式SQL語句與主語言之間的通信 8.1.3 不用游標(biāo)的SQL語句 8.1.4 使用游標(biāo)的SQL語句 8.1.5 動(dòng)態(tài)SQL簡介 8.1 小結(jié),BACK！,2019/7/13,第8章 數(shù)據(jù)庫編程,6,8.1.1嵌入式SQL的一般形式,為了區(qū)分SQL語句與主語言語句，需要: 前綴：EXEC SQL 結(jié)束標(biāo)志：隨主語言的不同而不同 以C為主語言的嵌入式SQL語句的一般形式 EXEC SQL ; 例：EXEC SQL DROP TABLE Student;,2019/7/13,第8章 數(shù)據(jù)庫編程,7,DBMS處理宿主型數(shù)據(jù)庫語言SQL 的方法,ESQL基本處理過程（見圖8.1 P238） 1由RDBMS的預(yù)處理程序?qū)υ闯绦蜻M(jìn)行掃描，識別出SQL語句 2把它們轉(zhuǎn)換成主語言調(diào)用語句，以使主語言編譯程序能識別它 3最后由主語言的編譯程序?qū)⒄麄€(gè)源程序編譯成目標(biāo)碼。,2019/7/13,第8章 數(shù)據(jù)庫編程,8,嵌入SQL語句,說明性語句 嵌入SQL語句 數(shù)據(jù)定義 可執(zhí)行語句 數(shù)據(jù)控制 數(shù)據(jù)操縱 允許出現(xiàn)可執(zhí)行的高級語言語句的地方，都可以寫可執(zhí)行SQL語句 允許出現(xiàn)說明語句的地方，都可以寫說明性SQL語句,2019/7/13,第8章 數(shù)據(jù)庫編程,9,8.1.2 嵌入式SQL語句與主語言之間的通信,將SQL嵌入到高級語言中混合編程，程序中會含 有兩種不同計(jì)算模型的語句 SQL語句 負(fù)責(zé)操縱數(shù)據(jù)庫 高級語言語句 負(fù)責(zé)控制程序流程,2019/7/13,第8章 數(shù)據(jù)庫編程,10,數(shù)據(jù)庫工作單元與 源程序工作單元之間的通信方式,1. SQL通信區(qū) 向主語言傳遞SQL語句的執(zhí)行狀態(tài)信息，主語言能夠據(jù)此控制程序流程 2. 主變量 主語言向SQL語句提供參數(shù) 將SQL語句查詢數(shù)據(jù)庫的結(jié)果交主語言進(jìn)一步處理 3. 游標(biāo) 解決集合性操作語言與過程性操作語言的不匹配,2019/7/13,第8章 數(shù)據(jù)庫編程,11,1. SQL通信區(qū),SQLCA： SQL Communication Area SQLCA是一個(gè)數(shù)據(jù)結(jié)構(gòu) SQLCA的用途 SQL語句執(zhí)行后，DBMS反饋給應(yīng)用程序信息 描述系統(tǒng)當(dāng)前工作狀態(tài) 描述運(yùn)行環(huán)境 這些信息將送到SQL通信區(qū)SQLCA中 應(yīng)用程序從SQLCA中取出這些狀態(tài)信息，據(jù)此決定接下來執(zhí)行的語句,2019/7/13,第8章 數(shù)據(jù)庫編程,12,SQLCA的內(nèi)容,與所執(zhí)行的SQL語句有關(guān) 與該SQL語句的執(zhí)行情況有關(guān) 例：在執(zhí)行刪除語句DELETE后，不同的執(zhí)行情況，SQLCA中有不同的信息： 違反數(shù)據(jù)保護(hù)規(guī)則，操作拒絕 沒有滿足條件的行，一行也沒有刪除 成功刪除，并有刪除的行數(shù) 無條件刪除警告信息 由于各種原因，執(zhí)行出錯(cuò),2019/7/13,第8章 數(shù)據(jù)庫編程,13,SQLCA的使用方法,定義SQLCA 用EXEC SQL INCLUDE SQLCA加以定義 使用SQLCA SQLCA中有一個(gè)存放每次執(zhí)行SQL語句后返回代碼的變量SQLCODE 如果SQLCODE等于預(yù)定義的常量SUCCESS，則表示SQL語句成功，否則表示出錯(cuò) 應(yīng)用程序每執(zhí)行完一條SQL 語句之后都應(yīng)該測試一下SQLCODE的值，以了解該SQL語句執(zhí)行情況并做相應(yīng)處理,2019/7/13,第8章 數(shù)據(jù)庫編程,14,2. 主變量,什么是主變量 在SQL語句中使用的主語言程序變量簡稱為主變量（Host Variable） 作用 嵌入式SQL語句中用主變量來輸入或輸出數(shù)據(jù),2019/7/13,第8章 數(shù)據(jù)庫編程,15,主變量（續(xù)）,主變量的類型 輸入主變量 由應(yīng)用程序?qū)ζ滟x值，SQL語句引用 輸出主變量 由SQL語句賦值或設(shè)置狀態(tài)信息，返回給應(yīng)用程序 一個(gè)主變量有可能既是輸入主變量又是輸出主變量,2019/7/13,第8章 數(shù)據(jù)庫編程,16,主變量（續(xù)）,主變量的用途 輸入主變量 指定向數(shù)據(jù)庫中插入的數(shù)據(jù) 將數(shù)據(jù)庫中的數(shù)據(jù)修改為指定值 指定執(zhí)行的操作 指定WHERE子句或HAVING子句中的條件 輸出主變量 獲取SQL語句的結(jié)果數(shù)據(jù) 獲取SQL語句的執(zhí)行狀態(tài),2019/7/13,第8章 數(shù)據(jù)庫編程,17,主變量（續(xù)）,指示變量 一個(gè)主變量可以附帶一個(gè)指示變量（Indicator Variable） 什么是指示變量 用來“指示”所指主變量的值或條件的一個(gè)整型變量 指示變量的用途 輸入主變量可以利用指示變量賦空值 輸出主變量可以利用指示變量檢測出是否空值，值是否被截?cái)?2019/7/13,第8章 數(shù)據(jù)庫編程,18,主變量（續(xù)）,在SQL語句中使用主變量和指示變量的方法 1) 說明主變量和指示變量 BEGIN DECLARE SECTION . . (說明主變量和指示變量) . END DECLARE SECTION,2019/7/13,第8章 數(shù)據(jù)庫編程,19,主變量（續(xù)）,2) 使用主變量 說明之后的主變量可以在SQL語句中任何一個(gè)能夠使用表達(dá)式的地方出現(xiàn) 為了與數(shù)據(jù)庫對象名（表名、視圖名、列名等）區(qū)別，SQL語句中的主變量名前要加冒號（:）作為標(biāo)志 如：:xname,2019/7/13,第8章 數(shù)據(jù)庫編程,20,主變量（續(xù)）,3) 使用指示變量 指示變量前也必須加冒號標(biāo)志 必須緊跟在所指主變量之后 如：:Hgrade:Gradeid,2019/7/13,第8章 數(shù)據(jù)庫編程,21,主變量（續(xù)）,在SQL語句之外(主語言語句中)使用主變量和指示變量的方法 可以直接引用，不必加冒號,2019/7/13,第8章 數(shù)據(jù)庫編程,22,3. 游標(biāo)（cursor）,為什么要使用游標(biāo) SQL語言與主語言具有不同數(shù)據(jù)處理方式，引入游標(biāo)的概念就是為了用來協(xié)調(diào)這兩種不同的處理方式 SQL語言是面向集合的，一條SQL語句原則上可以產(chǎn)生或處理多條記錄 主語言是面向記錄的，一組主變量一次只能存放一條記錄 問題：僅使用主變量并不能完全滿足SQL語句向應(yīng)用程序輸出數(shù)據(jù)的要求,2019/7/13,第8章 數(shù)據(jù)庫編程,23,游標(biāo)（續(xù)）,什么是游標(biāo) 游標(biāo)是系統(tǒng)為用戶開設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語句的執(zhí)行結(jié)果 每個(gè)游標(biāo)區(qū)都有一個(gè)名字 用戶可以用SQL語句逐一從游標(biāo)中獲取記錄，并賦給主變量，交由主語言進(jìn)一步處理,2019/7/13,第8章 數(shù)據(jù)庫編程,24,4、建立和關(guān)閉數(shù)據(jù)庫連接,建立數(shù)據(jù)庫連接 建立連接的ESQL語句 EXEC SQL CONNECT TO 目標(biāo)服務(wù)器 AS 連接名 USER 用戶名； 修改當(dāng)前連接的ESQL語句 EXEC SQL SET CONNECTION 連接名|DEFAULT； 關(guān)閉連接 關(guān)閉連接 的ESQL語句 EXEC SQL DISCONNECT 已建立的數(shù)據(jù)庫連接；,2019/7/13,第8章 數(shù)據(jù)庫編程,25,嵌入式SQL語句與主語言之間的通信(續(xù)),小結(jié) 在嵌入式SQL中，SQL語句與主語言語句分工非常明確 SQL語句：直接與數(shù)據(jù)庫打交道 主語言語句 1. 控制程序流程 2. 對SQL語句的執(zhí)行結(jié)果做進(jìn)一步 加工處理,2019/7/13,第8章 數(shù)據(jù)庫編程,26,嵌入式SQL語句與主語言之間的通信(續(xù)),SQL語句用主變量從主語言中接收執(zhí)行參數(shù)，操縱數(shù)據(jù)庫 SQL語句的執(zhí)行狀態(tài)由DBMS送至SQLCA中 主語言程序從SQLCA中取出狀態(tài)信息，據(jù)此決定下一步操作 如果SQL語句從數(shù)據(jù)庫中成功地檢索出數(shù)據(jù)，則通過主變量傳給主語言做進(jìn)一步處理 SQL語言和主語言的不同數(shù)據(jù)處理方式通過游標(biāo)來協(xié)調(diào),2019/7/13,第8章 數(shù)據(jù)庫編程,27,嵌入式SQL語句與主語言之間的通信(續(xù)),例：帶有嵌入式SQL的一小段C程序 EXEC SQL INCLUDE SQLCA; /* (1) 定義SQL通信區(qū) */ EXEC SQL BEGIN DECLARE SECTION; /* (2) 說明主變量 */ CHAR title_id7; CHAR title81; INT royalty; EXEC SQL END DECLARE SECTION;,2019/7/13,第8章 數(shù)據(jù)庫編程,28,嵌入式SQL語句與主語言之間的通信(續(xù)),main() EXEC SQL DECLARE C1 CURSOR FOR SELECT tit_id, tit, roy FROM titles; /* (3) 游標(biāo)操作（定義游標(biāo)）*/ /* 從titles表中查詢 tit_id, tit, roy */ EXEC SQL OPEN C1; /* (4) 游標(biāo)操作（打開游標(biāo)）*/,2019/7/13,第8章 數(shù)據(jù)庫編程,29,嵌入式SQL語句與主語言之間的通信(續(xù)),for(;) EXEC SQL FETCH C1 INTO :title_id, :title, :royalty; /* (5) 游標(biāo)操作（將當(dāng)前數(shù)據(jù)放入主變量并推進(jìn)游標(biāo)指針）*/ if (sqlca.sqlcode SUCCESS) /* (6) 利用SQLCA中的狀態(tài)信息決定何時(shí)退出循環(huán) */ break; printf(“Title ID: %s, Royalty: %d“, :title_id, :royalty); printf(“Title: %s“, :title); /* 打印查詢結(jié)果 */ EXEC SQL CLOSE C1; /* (7) 游標(biāo)操作（關(guān)閉游標(biāo)）*/ ,2019/7/13,第8章 數(shù)據(jù)庫編程,30,8.1.3 不用游標(biāo)的SQL語句,不用游標(biāo)的SQL語句的種類 說明性語句 數(shù)據(jù)定義語句 數(shù)據(jù)控制語句 查詢結(jié)果為單記錄的SELECT語句 非CURRENT形式的UPDATE語句 非CURRENT形式的DELETE語句 INSERT語句,2019/7/13,第8章 數(shù)據(jù)庫編程,31,一、說明性語句,說明性語句是專為在嵌入式SQL中說明主變量、SQLCA等而設(shè)置的 說明主變量 1. EXEC SQL BEGIN DECLARE SECTION; 2. EXEC SQL END DECLARE SECTION; 這兩條語句必須配對出現(xiàn)，相當(dāng)于一個(gè)括號，兩條語句中間是主變量的說明 說明SQLCA 3. EXEC SQL INCLUDE SQLCA,2019/7/13,第8章 數(shù)據(jù)庫編程,32,二、數(shù)據(jù)定義語句,例1 建立一個(gè)“學(xué)生”表Student EXEC SQL CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE, Sname CHAR(20), Ssex CHAR(1), Sage INT, Sdept CHAR(15);,2019/7/13,第8章 數(shù)據(jù)庫編程,33,數(shù)據(jù)定義語句（續(xù)）,數(shù)據(jù)定義語句中不允許使用主變量 例：下列語句是錯(cuò)誤的 EXEC SQL DROP TABLE :table_name;,2019/7/13,第8章 數(shù)據(jù)庫編程,34,三、數(shù)據(jù)控制語句,例2 把查詢Student表權(quán)限授給用戶U1 EXEC SQL GRANT SELECT ON TABLE Student TO U1;,2019/7/13,第8章 數(shù)據(jù)庫編程,35,四、查詢結(jié)果為單記錄的SELECT語句,語句格式 EXEC SQL SELECT ALL|DISTINCT ,. INTO ,. FROM , . WHERE GROUP BY HAVING ORDER BY ASC|DESC;,2019/7/13,第8章 數(shù)據(jù)庫編程,36,查詢結(jié)果為單記錄的SELECT語句（續(xù)）,對交互式SELECT語句的擴(kuò)充就是多了一個(gè)INTO子句 把從數(shù)據(jù)庫中找到的符合條件的記錄，放到INTO子句指出的主變量中去。,2019/7/13,第8章 數(shù)據(jù)庫編程,37,查詢結(jié)果為單記錄的SELECT語句（續(xù)）,使用注意事項(xiàng) 1. 使用主變量 INTO子句 WHERE子句的條件表達(dá)式 HAVING短語的條件表達(dá)式,2019/7/13,第8章 數(shù)據(jù)庫編程,38,查詢結(jié)果為單記錄的SELECT語句（續(xù)）,2. 使用指示變量 指示變量只能用于INTO子句中 如果INTO子句中主變量后面跟有指示變量，則當(dāng)查詢得出的某個(gè)數(shù)據(jù)項(xiàng)為空值時(shí)，系統(tǒng)會自動(dòng)將相應(yīng)主變量后面的指示變量置為負(fù)值，但不向該主變量執(zhí)行賦值操作，即主變量值仍保持執(zhí)行SQL語句之前的值 當(dāng)發(fā)現(xiàn)指示變量值為負(fù)值時(shí)，不管主變量為何值，均應(yīng)認(rèn)為主變量值為NULL,2019/7/13,第8章 數(shù)據(jù)庫編程,39,查詢結(jié)果為單記錄的SELECT語句（續(xù)）,3. 查詢結(jié)果為空集 如果數(shù)據(jù)庫中沒有滿足條件的記錄，即查詢結(jié)果為空，則DBMS將SQLCODE的值置為100 4. 查詢結(jié)果為多條記錄 程序出錯(cuò)，DBMS會在SQLCA中返回錯(cuò)誤信息,2019/7/13,第8章 數(shù)據(jù)庫編程,40,查詢結(jié)果為單記錄的SELECT語句（續(xù)）,例3 根據(jù)學(xué)生號碼查詢學(xué)生信息。 假設(shè)Hsno, Hname, Hsex, Hage, Hdept和givensno均是主變量，并均已在前面的程序中說明，并已將要查詢的學(xué)生的學(xué)號賦給了主變量givensno EXEC SQL SELECT Sno, Sname, Ssex, Sage, Sdept INTO :Hsno, :Hname, :Hsex, :Hage, :Hdept FROM Student WHERE Sno=:givensno;,2019/7/13,第8章 數(shù)據(jù)庫編程,41,查詢結(jié)果為單記錄的SELECT語句（續(xù)）,例4 查詢某個(gè)學(xué)生選修某門課程的成績。 假設(shè)已將要查詢的學(xué)生的學(xué)號賦給了主變量givensno，將課程號賦給了主變量givencno。 EXEC SQL SELECT Sno, Cno, Grade INTO :Hsno, :Hcno, :Hgrade:Gradeid FROM SC WHERE Sno=:givensno AND Cno=:givencno;,2019/7/13,第8章 數(shù)據(jù)庫編程,42,查詢結(jié)果為單記錄的SELECT語句（續(xù)）,從提高應(yīng)用程序的數(shù)據(jù)獨(dú)立性角度考慮，SELECT語句在任何情況下都應(yīng)該使用游標(biāo) 對于僅返回一行結(jié)果數(shù)據(jù)的SELECT語句雖然可以不使用游標(biāo) 但如果以后數(shù)據(jù)庫改變了，該SELECT語句可能會返回多行數(shù)據(jù)，這時(shí)該語句就會出錯(cuò),2019/7/13,第8章 數(shù)據(jù)庫編程,43,五、非CURRENT形式的UPDATE語句,非CURRENT形式的UPDATE語句 使用主變量 SET子句 WHERE子句 使用指示變量 SET子句 非CURRENT形式的UPDATE語句可以操作多條元組,2019/7/13,第8章 數(shù)據(jù)庫編程,44,非CURRENT形式的UPDATE語句（續(xù)）,例5 將全體學(xué)生1號課程的考試成績增加若干分。 假設(shè)增加的分?jǐn)?shù)已賦給主變量Raise EXEC SQL UPDATE SC SET Grade=Grade+:Raise WHERE Cno=1;,2019/7/13,第8章 數(shù)據(jù)庫編程,45,非CURRENT形式的UPDATE語句（續(xù)）,例6 修改某個(gè)學(xué)生1號課程的成績。 假設(shè)該學(xué)生的學(xué)號已賦給主變量givensno， 修改后的成績已賦給主變量newgrade。 EXEC SQL UPDATE SC SET Grade=:newgrade WHERE Sno=:givensno and Cno=1;,2019/7/13,第8章 數(shù)據(jù)庫編程,46,非CURRENT形式的UPDATE語句（續(xù)）,例7 將計(jì)算機(jī)系全體學(xué)生年齡置NULL值 Sageid=-1; EXEC SQL UPDATE Student SET Sage=:Raise:Sageid WHERE Sdept=CS;,2019/7/13,第8章 數(shù)據(jù)庫編程,47,非CURRENT形式的UPDATE語句（續(xù)）,將指示變量Sageid賦一個(gè)負(fù)值后，無論主變量Raise為何值，DBMS都會將CS系所有記錄的年齡屬性置空值。它等價(jià)于： EXEC SQL UPDATE Student SET Sage=NULL WHERE Sdept=CS;,2019/7/13,第8章 數(shù)據(jù)庫編程,48,六、非CURRENT形式的DELETE語句,非CURRENT形式的DELETE語句 使用主變量 WHERE子句 非CURRENT形式的DELETE語句可以操作多條元組,2019/7/13,第8章 數(shù)據(jù)庫編程,49,非CURRENT形式的DELETE語句（續(xù)）,例8 某個(gè)學(xué)生退學(xué)了，現(xiàn)要將有關(guān)他的所有選課記錄刪除掉。 假設(shè)該學(xué)生的姓名已賦給主變量stdname EXEC SQL DELETE FROM SC WHERE Sno= (SELECT Sno FROM Student WHERE Sname=:stdname);,2019/7/13,第8章 數(shù)據(jù)庫編程,50,七、INSERT語句,非CURRENT形式的INSERT語句 使用主變量 VALUES子句 使用指示變量 VALUES子句 非CURRENT形式的INSERT語句一次只能輸入一條元組,2019/7/13,第8章 數(shù)據(jù)庫編程,51,INSERT語句（續(xù)）,例9 某個(gè)學(xué)生新選修了某門課程，將有關(guān)記錄插入SC表 假設(shè)學(xué)生的學(xué)號已賦給主變量stdno，課程號已賦給主變量couno。 gradeid=-1; EXEC SQL INSERT INTO SC(Sno, Cno, Grade) VALUES(:stdno, :couno, :gr:gradeid); 由于該學(xué)生剛選修課程，尚未考試，因此成績列為空。所以本例中用指示變量指示相應(yīng)的主變量為空值。,2019/7/13,第8章 數(shù)據(jù)庫編程,52,8.1.4 使用游標(biāo)的SQL語句,必須使用游標(biāo)的SQL語句 查詢結(jié)果為多條記錄的SELECT語句 CURRENT形式的UPDATE語句 CURRENT形式的DELETE語句,2019/7/13,第8章 數(shù)據(jù)庫編程,53,一、 查詢結(jié)果為多條記錄的SELECT語句,使用游標(biāo)的步驟 1. 說明游標(biāo) 2. 打開游標(biāo) 3. 推進(jìn)游標(biāo)指針并取當(dāng)前記錄 4. 關(guān)閉游標(biāo),2019/7/13,第8章 數(shù)據(jù)庫編程,54,1. 說明游標(biāo),使用DECLARE語句 語句格式 EXEC SQL DECLARE CURSOR FOR ; 功能 是一條說明性語句，這時(shí)DBMS并不執(zhí)行SELECT指定的查詢操作。,2019/7/13,第8章 數(shù)據(jù)庫編程,55,2. 打開游標(biāo),使用OPEN語句 語句格式 EXEC SQL OPEN ; 功能 打開游標(biāo)實(shí)際上是執(zhí)行相應(yīng)的SELECT語句，把所有滿足查詢條件的記錄從指定表取到緩沖區(qū)中 這時(shí)游標(biāo)處于活動(dòng)狀態(tài)，指針指向查詢結(jié)果集中第一條記錄,2019/7/13,第8章 數(shù)據(jù)庫編程,56,3. 推進(jìn)游標(biāo)指針并取當(dāng)前記錄,使用FETCH語句 語句格式 EXEC SQL FETCH INTO ,.; 功能 推進(jìn)游標(biāo)指針，然后將緩沖區(qū)中的當(dāng)前記錄取出來送至主變量供主語言進(jìn)一步處理。,2019/7/13,第8章 數(shù)據(jù)庫編程,57,說明,(1) 主變量必須與SELECT語句中的目標(biāo)列表達(dá)式具有一一對應(yīng)關(guān)系 (2) FETCH語句通常用在一個(gè)循環(huán)結(jié)構(gòu)中，通過循環(huán)執(zhí)行FETCH語句逐條取出結(jié)果集中的行進(jìn)行處理 (3) 為進(jìn)一步方便用戶處理數(shù)據(jù)，現(xiàn)在一些關(guān)系數(shù)據(jù)庫管理系統(tǒng)對FETCH語句做了擴(kuò)充，允許用戶向任意方向以任意步長移動(dòng)游標(biāo)指針,2019/7/13,第8章 數(shù)據(jù)庫編程,58,4. 關(guān)閉游標(biāo),使用CLOSE語句 語句格式 EXEC SQL CLOSE ; 功能 關(guān)閉游標(biāo)，釋放結(jié)果集占用的緩沖區(qū)及其他資源 說明 游標(biāo)被關(guān)閉后，就不再和原來的查詢結(jié)果集相聯(lián)系 被關(guān)閉的游標(biāo)可以再次被打開，與新的查詢結(jié)果相聯(lián)系,2019/7/13,第8章 數(shù)據(jù)庫編程,59,例 題,例1 查詢某個(gè)系全體學(xué)生的信息（學(xué)號、姓名、性別和年齡）。要查詢的系名由用戶在程序運(yùn)行過程中指定，放在主變量deptname中 EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION;,2019/7/13,第8章 數(shù)據(jù)庫編程,60,例題（續(xù)）, /* 說明主變量 deptname,HSno,HSname,HSsex,HSage等*/ EXEC SQL END DECLARE SECTION; gets(deptname); /* 為主變量deptname賦值 */ ,2019/7/13,第8章 數(shù)據(jù)庫編程,61,例題（續(xù)）,EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname; /* 說明游標(biāo) */ EXEC SQL OPEN SX /* 打開游標(biāo) */,2019/7/13,第8章 數(shù)據(jù)庫編程,62,例題（續(xù)）,WHILE(1) /* 用循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 將游標(biāo)指針向前推進(jìn)一行，然后從結(jié) 果集中取當(dāng)前行，送相應(yīng)主變量*/,2019/7/13,第8章 數(shù)據(jù)庫編程,63,例題（續(xù)）,if (sqlca.sqlcode SUCCESS) break; /* 若所有查詢結(jié)果均已處理完或出現(xiàn) SQL語句錯(cuò)誤，則退出循環(huán) */ /* 由主語言語句進(jìn)行進(jìn)一步處理 */ ;,2019/7/13,第8章 數(shù)據(jù)庫編程,64,例題（續(xù)）,EXEC SQL CLOSE SX; /* 關(guān)閉游標(biāo) */ ,2019/7/13,第8章 數(shù)據(jù)庫編程,65,例題（續(xù)）,例2 查詢某些系全體學(xué)生的信息。 EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* 說明主變量 deptname,HSno,HSname,HSsex,HSage等*/ ,2019/7/13,第8章 數(shù)據(jù)庫編程,66,例題（續(xù)）,EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname; /* 說明游標(biāo) */,2019/7/13,第8章 數(shù)據(jù)庫編程,67,例題（續(xù)）,WHILE (deptname!=NULL) /* 接收主變量deptname的值 */ /* 下面開始處理deptname指定系的學(xué)生信息, 每次循環(huán)中deptname可具有不同的值 */ EXEC SQL OPEN SX /* 打開游標(biāo) */,2019/7/13,第8章 數(shù)據(jù)庫編程,68,例題（續(xù)）,WHILE (1) /* 用循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 將游標(biāo)指針向前推進(jìn)一行，然后從結(jié)果集中取當(dāng)前行，送相應(yīng)主變量*/,2019/7/13,第8章 數(shù)據(jù)庫編程,69,例題（續(xù)）,if (sqlca.sqlcode ！=0) break; /* 若所有查詢結(jié)果均已處理完或出現(xiàn) SQL語句錯(cuò)誤，則退出循環(huán) */ /* 由主語言語句進(jìn)行進(jìn)一步處理 */ ; /* 內(nèi)循環(huán)結(jié)束 */,2019/7/13,第8章 數(shù)據(jù)庫編程,70,例題（續(xù)）,EXEC SQL CLOSE SX; /* 關(guān)閉游標(biāo) */ ; /* 外循環(huán)結(jié)束 */ ,2019/7/13,第8章 數(shù)據(jù)庫編程,71,二、CURRENT形式的UPDATE語句和DELETE語句,CURRENT形式的UPDATE語句和DELETE語句的用途 非CURRENT形式的UPDATE語句和DELETE語句 面向集合的操作 一次修改或刪除所有滿足條件的記錄,2019/7/13,第8章 數(shù)據(jù)庫編程,72,二、CURRENT形式的UPDATE語句和DELETE語句,CURRENT形式的UPDATE語句和DELETE語句的用途（續(xù)） 如果只想修改或刪除其中某個(gè)記錄 用帶游標(biāo)的SELECT語句查出所有滿足條件的記錄 從中進(jìn)一步找出要修改或刪除的記錄 用CURRENT形式的UPDATE語句和DELETE語句修改或刪除之,2019/7/13,第8章 數(shù)據(jù)庫編程,73,CURRENT形式的UPDATE語句和DELETE語句（續(xù)）,步驟 (1) DECLARE 說明游標(biāo) (2) OPEN 打開游標(biāo)，把所有滿足查詢條件的記錄從指定表取至緩沖區(qū) (3) FETCH 推進(jìn)游標(biāo)指針，并把當(dāng)前記錄從緩沖區(qū)中取出來送至主變量,2019/7/13,第8章 數(shù)據(jù)庫編程,74,CURRENT形式的UPDATE語句和DELETE語句（續(xù)）,(4) 檢查該記錄是否是要修改或刪除的記錄，是則處理之 (5) 重復(fù)第(3)和(4)步，用逐條取出結(jié)果集中的行進(jìn)行判斷和處理 (6) CLOSE 關(guān)閉游標(biāo)，釋放結(jié)果集占用的緩沖區(qū)和其他資源,2019/7/13,第8章 數(shù)據(jù)庫編程,75,CURRENT形式的UPDATE語句和DELETE語句（續(xù)）,修改或刪除當(dāng)前記錄 經(jīng)檢查緩沖區(qū)中記錄是要修改或刪除的記錄，則用UPDATE語句或DELETE語句修改或刪除該記錄 語句格式 WHERE CURRENT OF WHERE CURRENT OF WHERE CURRENT OF 子句：表示修改或刪除的是該游標(biāo)中最近一次取出的記錄,2019/7/13,第8章 數(shù)據(jù)庫編程,76,CURRENT形式的UPDATE語句和DELETE語句（續(xù)）,注意：當(dāng)游標(biāo)定義中的SELECT語句帶有UNION或ORDER BY子句時(shí)，或者該SELECT語句相當(dāng)于定義了一個(gè)不可更新的視圖時(shí)，不能使用CURRENT形式的UPDATE語句和DELETE語句。,2019/7/13,第8章 數(shù)據(jù)庫編程,77,例 題,例3 對某個(gè)系的學(xué)生信息，根據(jù)用戶的要求修改其中某些人的年齡字段。 思路 查詢某個(gè)系全體學(xué)生的信息(要查詢的系名由主變量deptname指定） 然后根據(jù)用戶的要求修改其中某些記錄的年齡字段,2019/7/13,第8章 數(shù)據(jù)庫編程,78,例題（續(xù)）, EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* 說明主變量 deptname,HSno,HSname,HSsex,HSage,NEWAge等*/ ,2019/7/13,第8章 數(shù)據(jù)庫編程,79,例題（續(xù)）, EXEC SQL END DECLARE SECTION; gets(deptname); /* 為主變量deptname賦值 */ ,2019/7/13,第8章 數(shù)據(jù)庫編程,80,例題（續(xù)）,EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname FOR UPDATE OF Sage; /* 說明游標(biāo) */ EXEC SQL OPEN SX /* 打開游標(biāo) */,2019/7/13,第8章 數(shù)據(jù)庫編程,81,例題（續(xù)）,WHILE(1) /* 用循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 將游標(biāo)指針向前推進(jìn)一行，然后從結(jié)果集 中取當(dāng)前行，送相應(yīng)主變量*/,2019/7/13,第8章 數(shù)據(jù)庫編程,82,例題（續(xù)）,if (sqlca.sqlcode SUCCESS) break; /* 若所有查詢結(jié)果均已處理完或 出現(xiàn)SQL語句錯(cuò)誤，則退出循環(huán) */ printf(“%s, %s, %s, %d“, Sno, Sname, Ssex, Sage); /* 顯示該記錄 */ printf(“UPDATE AGE ? “); /* 問用戶是否要修改 */ scanf(“%c“,2019/7/13,第8章 數(shù)據(jù)庫編程,83,例題（續(xù)）,if (yn=y or yn=Y) /* 需要修改 */ printf(“INPUT NEW AGE: “); scanf(“%d“,2019/7/13,第8章 數(shù)據(jù)庫編程,84,例題（續(xù)）, ; EXEC SQL CLOSE SX; /* 關(guān)閉游標(biāo) */ ,2019/7/13,第8章 數(shù)據(jù)庫編程,85,例題（續(xù)）,例4 對某個(gè)系的學(xué)生信息，根據(jù)用戶的要求刪除其中某些人的記錄。 EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* 說明主變量 deptname,HSno,HSname,HSsex,HSage等*/,2019/7/13,第8章 數(shù)據(jù)庫編程,86,例題（續(xù)）, EXEC SQL END DECLARE SECTION; gets(deptname); /* 為主變量deptname賦值 */ ,2019/7/13,第8章 數(shù)據(jù)庫編程,87,例題（續(xù)）,EXEC SQL DECLARE SX CURSOR FOR SELECT Sno, Sname, Ssex, Sage FROM Student WHERE SDept=:deptname FOR DELETE; /* 說明游標(biāo) */ EXEC SQL OPEN SX /* 打開游標(biāo) */,2019/7/13,第8章 數(shù)據(jù)庫編程,88,例題（續(xù)）,WHILE(1) /* 用循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄 */ EXEC SQL FETCH SX INTO :HSno, :HSname, :HSsex, :HSage; /* 將游標(biāo)指針向前推進(jìn)一行，然后從結(jié) 果集中取當(dāng)前行，送相應(yīng)主變量*/,2019/7/13,第8章 數(shù)據(jù)庫編程,89,例題（續(xù)）,if (sqlca.sqlcode SUCCESS) break; /* 若所有查詢結(jié)果均已處理完或 出現(xiàn)SQL語句錯(cuò)誤，則退出循環(huán) */ printf(“%s, %s, %s, %d“, Sno, Sname, Ssex, Sage); /* 顯示該記錄 */,2019/7/13,第8章 數(shù)據(jù)庫編程,90,例題（續(xù)）,printf(“DELETE ? “); /* 問用戶是否要?jiǎng)h除 */ scanf(“%c“, /* 刪除當(dāng)前記錄 */,2019/7/13,第8章 數(shù)據(jù)庫編程,91,例題（續(xù)）, ; EXEC SQL CLOSE SX; /* 關(guān)閉游標(biāo) */ ,2019/7/13,第8章 數(shù)據(jù)庫編程,92,8.1.5 動(dòng)態(tài)SQL簡介,靜態(tài)嵌入式SQL 動(dòng)態(tài)嵌入式SQL,2019/7/13,第8章 數(shù)據(jù)庫編程,93,一、靜態(tài)SQL的特點(diǎn),用戶可以在程序運(yùn)行過程中根據(jù)實(shí)際需要輸入WHERE子句或HAVING子句中某些變量的值。 語句中主變量的個(gè)數(shù)與數(shù)據(jù)類型在預(yù)編譯時(shí)都是確定的，只有是主變量的值是程序運(yùn)行過程中動(dòng)態(tài)輸入的。,2019/7/13,第8章 數(shù)據(jù)庫編程,94,靜態(tài)SQL的不足,靜態(tài)SQL語句提供的編程靈活性在許多情況下 仍顯得不足，不能編寫更為通用的程序。,2019/7/13,第8章 數(shù)據(jù)庫編程,95,需求,例，對SC： 任課教師想查選修某門課程的所有學(xué)生的學(xué)號及其成績 班主任想查某個(gè)學(xué)生選修的所有課程的課程號及相應(yīng)成績 學(xué)生想查某個(gè)學(xué)生選修某門課程的成績 即：查詢條件是不確定的，要查詢的屬性列也是不確定的,2019/7/13,第8章 數(shù)據(jù)庫編程,96,二、動(dòng)態(tài)SQL,1. 什么是動(dòng)態(tài)嵌入式SQL 動(dòng)態(tài)SQL方法允許在程序運(yùn)行過程中臨時(shí)“組裝”SQL語句。 2. 應(yīng)用范圍 在預(yù)編譯時(shí)下列信息不能確定時(shí) SQL語句正文 主變量個(gè)數(shù) 主變量的數(shù)據(jù)類型 SQL語句中引用的數(shù)據(jù)庫對象（列、索引、基本表、視圖等）,2019/7/13,第8章 數(shù)據(jù)庫編程,97,動(dòng)態(tài)SQL（續(xù)）,3. 動(dòng)態(tài)SQL的形式 語句可變 臨時(shí)構(gòu)造完整的SQL語句 條件可變 WHERE子句中的條件 HAVING短語中的條件 數(shù)據(jù)庫對象可變 SELECT子句中的列名 FROM子句中的表名或視圖名,2019/7/13,第8章 數(shù)據(jù)庫編程,98,動(dòng)態(tài)SQL（續(xù)）,4. 使用動(dòng)態(tài)SQL技術(shù)更多的是涉及程序設(shè)計(jì)方面的知識，而不是SQL語言本身 5. 動(dòng)態(tài)SQL支持的兩種形式 一、動(dòng)態(tài)組裝SQL語句 二、動(dòng)態(tài)參數(shù),2019/7/13,第8章 數(shù)據(jù)庫編程,99,一、動(dòng)態(tài)組裝SQL語句,特點(diǎn)：程序主變量為SQL語句主變量 例9創(chuàng)建基本表TEST。 EXEC SQL BEGIN DECLARE SETION； Const char *stmt=“CREATE TABLE TEST（ a，int）”；/*定義SQL語句主變量*/ EXEC SQL END DECLARE SECTION； EXEC SQL EXECUTE IMMEDIATE :stmt；,2019/7/13,第8章 數(shù)據(jù)庫編程,100,二、動(dòng)態(tài)參數(shù),動(dòng)態(tài)參數(shù)是SQL語句中的可變參數(shù)，使用參數(shù)符號（？）表示該位置的數(shù)據(jù)在運(yùn)行時(shí)設(shè)定。 特點(diǎn)：動(dòng)態(tài)參數(shù)的輸入不是編譯時(shí)完成綁定，而是通過PREPARE語句準(zhǔn)備主變量和執(zhí)行時(shí)綁定數(shù)據(jù)或主變量來完成。 使用動(dòng)態(tài)參數(shù)的步驟： 聲名SQL語句主變量：變量的SQL內(nèi)容包含動(dòng)態(tài)參數(shù)（？） 準(zhǔn)備SQL語句：EXEC SQL PREPARE FROM 執(zhí)行準(zhǔn)備好的語句：EXEC SQL EXECUTE INTO USING P245 例子,2019/7/13,第8章 數(shù)據(jù)庫編程,101,小 結(jié)嵌入式SQL,SQL的特點(diǎn) 綜合統(tǒng)一 2. 高度非過程化 3. 面向集合的操作方式 4. 同一種語法結(jié)構(gòu)提供兩種使用方式 5. 語言簡捷，易學(xué)易用,2019/7/13,第8章 數(shù)據(jù)庫編程,102,小 結(jié)嵌入式SQL,交互式SQL 數(shù)據(jù)定義 查詢 數(shù)據(jù)更新 數(shù)據(jù)控制 嵌入式SQL 與主語言的通信方式 1. SQL通信區(qū) 向主語言傳遞SQL語句的執(zhí)行狀態(tài)信息 2. 主變量 1）主語言向SQL語句提供參數(shù) 2）將SQL語句查詢數(shù)據(jù)庫的結(jié)果交主語言進(jìn)一步處理 3. 游標(biāo) 解決集合性操作語言與過程性操作語言的不匹配,2019/7/13,第8章 數(shù)據(jù)庫編程,103,小 結(jié)嵌入式SQL,嵌入式SQL(續(xù)) 靜態(tài)SQL 不用游標(biāo) 不需要返回結(jié)果數(shù)據(jù)的SQL語句 只返回一條結(jié)果的SQL語句 使用游標(biāo) 說明游標(biāo) 打開游標(biāo) 推進(jìn)游標(biāo)并取當(dāng)前記錄 關(guān)閉游標(biāo) 動(dòng)態(tài)SQL,2019/7/13,第8章 數(shù)據(jù)庫編程,104,8.2 存儲過程,SQL 99中的SQL-invoked routines可分為 存儲過程（SQL-invoked procedure） 函數(shù)（SQL-invoked function） 編寫存儲過程的一種過程語言：PL/SQL（Procedural Language）,2019/7/13,第8章 數(shù)據(jù)庫編程,105,8.2 目 錄,8.2.1 PL/SQL的塊結(jié)構(gòu) 8.2.2 變量常量的定義 8.2.3 控制結(jié)構(gòu) 8.2.4 存儲過程 8.2.5 小結(jié),BACK！,2019/7/13,第8章 數(shù)據(jù)庫編程,106,8.2.1 PL/SQL的塊結(jié)構(gòu),PL/SQL是對SQL的擴(kuò)展，增加了過程化語句功能。 PL/SQL程序的基本結(jié)構(gòu)是塊。每個(gè)塊完成一個(gè)邏輯操作。/見p246 圖8.2 PL/SQL塊的基本結(jié)構(gòu)包括 定義部分：變量、常量、游標(biāo)、異常等 執(zhí)行部分：SQL語句、PL/SQL的流程控制語句、異常處理,BACK,2019/7/13,第8章 數(shù)據(jù)庫編程,107,8.2.2 變量、常量的定義,定義變量的語法形式： 變量名 數(shù)據(jù)類型 NOT NULL:=初值表達(dá)式 變量名 數(shù)據(jù)類型 NOT NULL 初值表達(dá)式 常量定義 常量名 數(shù)據(jù)類型 CONSTANT:=常量表達(dá)式 注意：應(yīng)用中不能改變 賦值語句 變量名稱：=表達(dá)式,BACK,2019/7/13,第8章 數(shù)據(jù)庫編程,108,8.2.3 控制結(jié)構(gòu),流程控制語句主要有 條件控制語句 IFEND IF IFELSEEND IF 嵌套IF 語句 循環(huán)控制語句 LOOP WHILE-LOOP FOR-LOOP 錯(cuò)誤處理,BACK,2019/7/13,第8章 數(shù)據(jù)庫編程,109,8.2.4 存儲過程,PL/SQL塊的兩種類型 匿名塊：每次執(zhí)行需要編譯；不能被調(diào)用 命名塊：編譯后保存在庫中，可被調(diào)用，如函數(shù)和存儲過程。,2019/7/13,第8章 數(shù)據(jù)庫編程,110,一、存儲過程的優(yōu)點(diǎn),提供了在服務(wù)器端快速執(zhí)行SQL語句的有效途徑 降低了客戶機(jī)和服務(wù)器間的通信量 有利于集中控制 方便維護(hù),2019/7/13,第8章 數(shù)據(jù)庫編程,111,二、存儲過程的用戶接口,1、創(chuàng)建存儲過程 CREATE Procedure 過程名(參數(shù)1，參數(shù)2，.)-首部 AS -過程體：包括定義部分和可執(zhí)行部分 P248 例11,2019/7/13,第8章 數(shù)據(jù)庫編程,112,存儲過程的用戶接口,2、執(zhí)行存儲過程 CALL/PERFORM Procedure 過程名(參數(shù)1，參數(shù)2，.) 3、刪除存儲過程 DROP Procedure 過程名,2019/7/13,第8章 數(shù)據(jù)庫編程,113,SQL Server 存儲過程使用示例,定義存儲過程，基本語法如下： CREATE PROCEDURE Procedure_name parameter data_type OUTPUT ,. AS sql_statement . ,2019/7/13,第8章 數(shù)據(jù)庫編程,114,例A：創(chuàng)建一個(gè)存儲過程，返回指定公司的所有合同明細(xì)情況。該過程需要用到一個(gè)輸入?yún)?shù)：公司名稱。 CREATE PROCEDURE Co_Contract_Info CompanyName varchar(30) AS SELECT CompanyName,Contract.ContractID, ContractDetailID,Volume FROM Company INNER JOIN Contract ON Company.CompanyID=Contract.CompanyID INNER JOIN ContractDetail ON ontract.ContractID=ContractDetail.ContractID WHERE CompanyName = CompanyName,2019/7/13,第8章 數(shù)據(jù)庫編程,115,創(chuàng)建一個(gè)存儲過程后就可以執(zhí)行該存儲過程。 查詢公司名稱為“北京怡神工貿(mào)有限公司”的所有合同明細(xì)。 EXECUTE Co_Contract_Info CompanyName = 北京怡神工貿(mào)有限公司 或： EXECUTE Co_Contract_Info 北京怡神工貿(mào)有限公司,2019/7/13,第8章 數(shù)據(jù)庫編程,116,通過RETURN參數(shù)返回狀態(tài) RETURN返回值是整型值，不能返回空值。 例B：創(chuàng)建一個(gè)存儲過程，檢查公司信息表中是否有記錄，如果有，返回1，否則返回0。 CREATE PROCEDURE get_co_rec AS BEGIN DECLARE count integer SELECT count = count(*) FROM Company IF count = 0 RETURN(0) ELSE RETURN (1) END,2019/7/13,第8章 數(shù)據(jù)庫編程,117,以下用于查看返回值情況 BEGIN DECLARE status integer EXECUTE status = get_co_rec IF status = 1 PRINT “存在記錄” ELSE PRINT “沒有記錄” END,2019/7/13,第8章 數(shù)據(jù)庫編程,118,可以象修改和刪除數(shù)據(jù)表一樣，對存儲過程進(jìn)行修改和刪除操作。 修改使用的語句是：ALTER PROCEDURE。 刪除使用的語句是：DROP PROCEDURE,2019/7/13,第8章 數(shù)據(jù)庫編程,119,三、游標(biāo),當(dāng)存儲過程中的SELECT語句返回的結(jié)果集需要一行一行處理時(shí)，可在存儲過程中使用游標(biāo)。 例如：例A中，若要返回所有公司的合同總金額，則需要使用一個(gè)游標(biāo)類型的參數(shù)。,BACK,2019/7/13,第8章 數(shù)據(jù)庫編程,120,8.2.5 小結(jié),SQL的過程化擴(kuò)展PL/SQL 存儲過程 各RDBMS提供的PL/SQL和存儲過程語法不同，但原理一致，具體使用時(shí)參考相關(guān)資料或使用手冊。,BACK,2019/7/13,第8章 數(shù)據(jù)庫編程,121,8.3 ODBC編程,數(shù)據(jù)庫本身的設(shè)計(jì)是數(shù)據(jù)庫應(yīng)用系統(tǒng)設(shè)計(jì)的一部分。 數(shù)據(jù)庫編程編程接口通常有：ODBC、JDBC、OLEDB、ADO等。 使用ODBC編寫的應(yīng)用程序可移植性好，能同時(shí)訪問不同數(shù)據(jù)庫，共享多個(gè)數(shù)據(jù)資源。,2019/7/13,第8章 數(shù)據(jù)庫編程,122,8.3.1 數(shù)據(jù)庫互連概述,ODBC(Open Database Connectivity)是由微軟公司提出的一個(gè)用于訪問數(shù)據(jù)庫的統(tǒng)一界面標(biāo)準(zhǔn)，它提供了一個(gè)一致的應(yīng)用程序編程接口，該接口使用一個(gè)針對數(shù)據(jù)庫的驅(qū)動(dòng)程序和不同的數(shù)據(jù)庫協(xié)同工作。 ODBC規(guī)范的兩重功效和約束力