第20講數(shù)據(jù)庫編程

《數(shù)據(jù)庫原理與應(yīng)用》第10章數(shù)據(jù)庫編程

數(shù)據(jù)庫設(shè)計數(shù)據(jù)庫設(shè)計概述需求分析概念設(shè)計邏輯設(shè)計物理設(shè)計數(shù)據(jù)庫的實施與維護回顧回顧

數(shù)據(jù)庫（存放了讀者信息、圖書信息、讀者借還書信息等）

實現(xiàn)對數(shù)據(jù)庫查詢、更新和數(shù)據(jù)處理的相關(guān)應(yīng)用程序.1.實現(xiàn)讀者借書、還書等功能的程序。

2.實現(xiàn)圖書、讀者信息查詢和更新功能的程序。這些程序還需為最終的用戶提供了友好的圖形用戶界面（GUI）。設(shè)計圖書借閱系統(tǒng)：數(shù)據(jù)庫設(shè)計

對于一個給定的應(yīng)用環(huán)境，構(gòu)造（設(shè)計）優(yōu)化的數(shù)據(jù)庫邏輯模式和物理結(jié)構(gòu)，并據(jù)此建立數(shù)據(jù)庫及其應(yīng)用系統(tǒng)，使之能夠有效地存儲和管理數(shù)據(jù)，滿足各種用戶的應(yīng)用需求，包括信息管理要求和數(shù)據(jù)操作要求。信息管理要求：數(shù)據(jù)庫中應(yīng)該存儲哪些數(shù)據(jù)；數(shù)據(jù)操作要求：對數(shù)據(jù)對象進行哪些操作。圖借閱系統(tǒng)

圖書借閱系統(tǒng)應(yīng)提供的主要功能有：對讀者信息進行管理：讀者信息的添加、修改、刪除和查詢。(2)對圖書信息進行管理：圖書信息的添加、修改、刪除及查詢。(3)對借還書進行管理，并可查詢當前的借閱情況及借閱歷史信息。圖書借閱系統(tǒng)

圖書借閱系統(tǒng)的控制流程圖書借閱系統(tǒng)概念設(shè)計(概念模型）數(shù)據(jù)對象學生借書證號、姓名、專業(yè)、性別、出生時健、照片圖書ISBN、書名、作者、出版社、價格、復本數(shù)、庫存量姓名學生圖書借閱nm

作者性別專業(yè)照片出生日期出版社價格復本數(shù)借書證號ISBN庫存量索書號借閱時間圖書借閱系統(tǒng)邏輯結(jié)構(gòu)設(shè)計關(guān)系模型XS（借書證號、姓名、專業(yè)、性別、出生時間、照片）BOOK（ISBN、書名、作者、出版社、價格、復本數(shù)、庫存量）JY(索書號,借書證號,ISBN,借閱時間)基本表字段名XS借書證號、姓名、性別、出生時間、借書數(shù)BOOKISBN、書名、作者、出版社、價格、復本數(shù)、庫存量JY索書號、借書證號、ISBN、借書時間、還書時間圖書借閱系統(tǒng)數(shù)據(jù)庫為了處理的方便，在此對XSBOOK數(shù)據(jù)庫中XS表的結(jié)構(gòu)進行了修改，增加filelen字段，用于存放照片文件的長度，修改后的XS表結(jié)構(gòu)如表所示。字段名類型與寬度是否主碼是否允許空值說明借書證號Char(8)√NOTNULL姓名Char(8)NOTNULL專業(yè)名Char(12)NOTNULL性別bitNOTNULL0：男，1：女出生時間datetimeNOTNULL借書量integerNOTNULL照片imageNULLfilelenbigintNULL應(yīng)用開發(fā)工具數(shù)據(jù)庫管理系統(tǒng)（DBMS）

操作系統(tǒng)（OS）計算機

DB

應(yīng)用系統(tǒng)PB,VB,VCDelphiOracle,Sybase,SQLServer,Informix用戶非計算機專業(yè)人員，不熟悉SQL語言，不能直接使用SQL語言來操縱數(shù)據(jù)庫數(shù)據(jù)庫編程對數(shù)據(jù)庫的操作方式交互方式利用DBMS提供的軟件工具，直接對數(shù)據(jù)庫進行操作，操作結(jié)果立即返回到操作界面。圖形界面，SQLServer2000的企業(yè)管理器命令方式，SQLServer2000的查詢分析器數(shù)據(jù)編程數(shù)據(jù)庫編程數(shù)據(jù)庫編程標準SQL是非過程化的查詢語言，具有操作統(tǒng)一、面向集合、功能豐富、使用簡單等多項優(yōu)點。缺少流程控制能力，難以實現(xiàn)應(yīng)用業(yè)務(wù)中的邏輯控制。SQL編程技術(shù)可以有效克服SQL語言實現(xiàn)復雜應(yīng)用方面的不足，提高應(yīng)用系統(tǒng)和RDBMS間的互操作性。數(shù)據(jù)庫編程應(yīng)用系統(tǒng)中使用SQL編程來訪問和管理數(shù)據(jù)庫中數(shù)據(jù)的方式有：嵌入式SQL（EmbededSQL,ESQL)PL/SQL(ProceduralLanguage/SQL)ODBC(OpenDataBaseConnectivity)編程、JDBC(JavaDataBaseConnectivity)編程和OLEDB(ObjectLinkingandEmbeded)編程等

數(shù)據(jù)庫編程嵌入式SQL（EmbededSQL,ESQL)ESQL將SQL語句嵌入程序設(shè)計語言，借助高級語言的控制能力實現(xiàn)過程化。被嵌入的程序設(shè)計語言，如C、C++、Java，稱為宿主語言，簡稱主語言。形式EXECSQL<SQL語句>;由RDBMS的預處理程序?qū)υ闯绦蜻M行掃描，識別出ESQL語句，轉(zhuǎn)換成主語言調(diào)用語句，由主語言的編譯程序?qū)⑵渚幾g成目標碼。數(shù)據(jù)庫編程：Intmain(void){：EXECSQLCONNECTTOTEST@localhost:54321USER“SYSTEM”/”MANAGER”;EXECSQLDECLARESXCURSORFOR/*定義游標SX

SELECTSno,Sn,Sex,Sa/*SX對應(yīng)的語句

FROMSWHERESD=:deptname;EXECSQLOPENSX;/*打開游標，指向查詢結(jié)果的第一行

for(;;)/*循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄

{EXECSQLFETCHSXINTO:HSno,:HSname,:HSsex,:HSage;:/*推進游標，將當前數(shù)據(jù)放入主變量

print(“UPDATEAGE(y/n)?”);:

EXECSQL

UPDATES/*嵌入式SQL更新語句

SETSa=:NEWAGEWHERECURRENTOFSX;}

EXECSQLCLOSESX;/*關(guān)閉游標，不在和查詢結(jié)果對應(yīng)

EXECSQLCOMMITWORK;/*提交更新

EXECSQLDISCONNECTTEST;}/*斷開數(shù)據(jù)庫連接數(shù)據(jù)庫編程游標的概念SQL是面向集合的，一條SQL語句可以產(chǎn)生和處理多條語句。主語言是面向記錄的，一組主變量一次只能存放一條記錄。用游標來協(xié)調(diào)這兩種不同的處理方式。

EXECSQLDECLARESXCURSORFOR

/*定義游標SX

EXECSQLOPENSX/*打開游標

EXECSQLFETCHSXINTO/*推進游標

EXECSQLCLOSESX;/*關(guān)閉游標數(shù)據(jù)庫編程PL/SQL(ProceduralLanguage/SQL)編寫數(shù)據(jù)庫存儲過程的一種過程語言。結(jié)合了SQL的數(shù)據(jù)操作能力和過程化語言的流程控制能力，是SQL的過程化擴展，使其增加了過程化語句功能。PL/SQLT-SQL增加的語言元素微軟為了用戶編程方便增加的語言元素這些語言元素主要包括：變量、運算符、函數(shù)、流程控制語句和注釋1.變量

1)標識符(1)常規(guī)標識符(2)分隔標識符2)變量的分類(1)全局變量(2)局部變量

以ASCII字母、Unicode字母、下劃線(_)、@或#開頭，可后續(xù)一個或若干個ASCII字符、Unicode字符、下劃線(_)、美元符號（$）、@或#，但不能全為下劃線(_)、@或#。PL/SQL變量2.局部變量的使用1)局部變量的定義與賦值(1)局部變量的定義(2)局部變量的賦值PL/SQL變量例如：創(chuàng)建一個名為sex的局部變量，并在SELECT語句中使用該局部變量查找表XS中所有男同學的借書證號、姓名。PL/SQL變量2）局部游標變量的定義與賦值(1)局部游標變量的定義PL/SQL變量PL/SQL5.1.3變量(3)游標變量的使用步驟例如：使用游標變量

PL/SQL變量PL/SQL運算符與表達式算術(shù)運算符賦值運算符位運算符比較運算符邏輯運算符字符串連接運算符一元運算符PL/SQL流程控制語句PL/SQL系統(tǒng)內(nèi)置函數(shù)SQLServer包含如下幾類標量函數(shù)：PL/SQL系統(tǒng)表sysobjectsPL/SQL存儲過程和觸發(fā)器設(shè)計

存儲過程名說明Xs_insert向XS表中插入一條記錄Xs_update修改表XS中指定記錄Xs_delete在XS表中刪除一條記錄BOOK_insert向BOOK表中插入一條記錄BOOK_update修改表BOOK中指定記錄BOOK_delete在BOOK表中刪除一條記錄JY_insert向JY表中插入一條記錄JY_delete在JY表中刪除一條記錄觸發(fā)器名說明tjy_insert對JY表定義的INSERT觸發(fā)器tjy_delete對JY表定義的DELETE觸發(fā)器PL/SQL1.存儲過程1)對XS表進行操作的存儲過程(1)在XS表中插入一條記錄USEXSBOOKCREATEPROCEDURExs_insert@jszhchar(8),@xmchar(8),@zymchar(12),@xbbit,@cssjdatetimeASINSERTINTOXS(借書證號,姓名,專業(yè)名,性別,出生時間)VALUES(@jszh,@xm,@zym,@xb,@cssj)GOPL/SQL(2)在XS表中修改一條記錄當記錄的照片信息不變化時，存儲過程xs_update直接修改記錄的信息。USEXSBOOKCREATEPROCEDURExs_update

@jszhchar(8),@xmchar(8),@zymchar(12),@xbbit,@cssjdatetime,@jssintASUPDATExsSET

姓名=@xm,專業(yè)名=@zym,性別=@xb,出生時間=@cssj,借書數(shù)=@jssWHERE借書證號=@jszhPL/SQL(3)在XS表中刪除一條記錄USEXSBOOKCREATEPROCEDURExs_delete@jszhchar(8),@flagintoutputASIFEXISTS(SELECT*FROMxsWHERE借書證號=@jszhand借書數(shù)=0)BEGINDELETEFROMXSWHERE借書證號=@jszhand借書數(shù)=0SET@flag=0ENDELSESET@flag=1PL/SQL2)對BOOK表進行操作的存儲過程(1)在BOOK表中插入一條記錄USEXSBOOKCREATEPROCEDUREbook_insert@isbnchar(16),@smchar(26),@zzchar(8),@cbschar(20),@jgfloat(8),@fblint,@kclintASINSERTINTOBOOK(ISBN,書名,作者,出版社,價格,復本量,庫存量)VALUES(@isbn,@sm,@zz,@cbs,@jg,@fbl,@kcl)PL/SQL(2)對BOOK表中的一條記錄進行修改USEXSBOOKCREATEPROCEDUREbook_update@isbnchar(16),@smchar(26),@zzchar(8),@cbschar(20),@jgfloat(8),@fblint,@kclintASUPDATEbookSET

書名=@sm,作者=@zz,出版社=@cbs,價格=@jg,復本量=@fbl,庫存量=@kclWHEREISBN=@isbnPL/SQL(3)刪除BOOK表中的一條記錄USEXSBOOKCREATEPROCEDUREbook_delete@isbnchar(16),@flagintoutputASIFEXISTS(SELECT*FROMBOOKWHEREISBN=@isbnand復本量=庫存量)BEGINDELETEFROMbookWHEREISBN=@isbnSET@flag=0/*表示已刪除*/ENDELSESET@flag=1/*表示不能刪除*/PL/SQL3)對JY表進行操作的存儲過程在JY表中插入一條記錄(用于“借書”操作)USEXSBOOKCREATEPROCEDUREjy_insert

@jszhchar(8),@isbnchar(16),@sshchar(10),@jssjdatetime,@flagintOUTPUTASSET@flag=(SELECT庫存量FROMBOOKWHEREISBN=@isbn)IF@flag>0BEGININSERTINTOjy(借書證號,ISBN,索書號,借書時間)VALUES(@jszh,@isbn,@ssh,@jssj)ENDELSESET@flag=0PL/SQL2.觸發(fā)器1)JY表定義的INSERT觸發(fā)器USEXSBOOKCREATETRIGGERtjy_insertONjyFORINSERTASBEGINUPDATEXSSET

借書數(shù)=借書數(shù)+1WHEREXS.借書證號IN(SELECTinserted.借書證號

FROMinserted)UPDATEBOOKSET

庫存量=庫存量-1WHEREBOOK.ISBNIN(SELECTinserted.ISBNFROMinserted)ENDPL/SQL2)對JY表定義的DELETE觸發(fā)器USEXSBOOKCREATETRIGGERtjy_deleteonjyFORDELETEASBEGINUPDATEXSSET

借書數(shù)=借書數(shù)-1WHEREXS.借書證號IN(SELECTdeleted.借書證號

FROMdeleted)UPDATEBOOKSET

庫存量=庫存量+1WHEREBOOK.ISBNIN(SELECTdeleted.ISBNFROMdeleted)ENDPL/SQL創(chuàng)建存儲過程

CREATEProcedure過程名（[參數(shù)1，參數(shù)2，…])AS<PL/SQL塊>;執(zhí)行存儲過程

CALL/PERFORMProcedure過程名([參數(shù)1，參數(shù)2，…]);刪除存儲過程DROPProcedure過程名();PL/SQL數(shù)據(jù)庫編程不同RDBMS存在差異；在某一個DBMS下編寫的應(yīng)用程序不能在另一個DBMS下運行，數(shù)據(jù)庫應(yīng)用程序的可移植性比較差；許多應(yīng)用程序需要共享多個部門的數(shù)據(jù)資源，訪問不同的RDBMS。EmbeddedEquipmentlinuxPCServerWindowsMilitaryApplicationSystem數(shù)據(jù)庫編程

研究和開發(fā)連接不同RDBMS的方法、技術(shù)和軟件，使數(shù)據(jù)庫系統(tǒng)“開放”，能夠”數(shù)據(jù)庫互聯(lián)“。1992年microsoft公司推出開放服務(wù)體系（WOSA)中有關(guān)數(shù)據(jù)庫的一個組成部分——ODBC(Open

DataBaseConnectivity,開放數(shù)據(jù)庫互連)產(chǎn)品。如何解決？

數(shù)據(jù)庫編程ODBC編程ODBC(OpenDataBaseConnectivity,開放數(shù)據(jù)庫互連)是數(shù)據(jù)庫訪問標準接口。建立一個公共的、與數(shù)據(jù)庫無關(guān)的應(yīng)用程序編程接口（ApplicationProgrammingInterface,簡記為API）；公共接口API提供了一組規(guī)范規(guī)范應(yīng)用開發(fā)規(guī)范RDBMS應(yīng)用接口利用ODBC所開發(fā)的應(yīng)用程序具有數(shù)據(jù)庫無關(guān)性，程序員不必修改應(yīng)用程序源代碼就可以實現(xiàn)對不同DBMS的訪問，而且可以同時存取多個數(shù)據(jù)庫中的數(shù)據(jù)。ODBC是為最大的互操作（interoperability）而設(shè)計的，即一個應(yīng)用程序可使用相同的接口訪問不同的DBMS。ODBC編程ODBC應(yīng)用系統(tǒng)的體系結(jié)構(gòu)

networkDriver（SQLServer）

ApplicationDriverManagerSQLServerDriver（MySQL）

MySQLDriver（Oracle）

OracleODBCAPIODBC編程ODBC編程應(yīng)用程序提供用戶界面和應(yīng)用邏輯，調(diào)用ODBCAPI與數(shù)據(jù)庫進行交互。

networkDriver（SQLServer）

ApplicationDriverManagerSQLServerDriver（MySQL）

MySQLDriver（Oracle）

OracleODBCAPIODBC應(yīng)用系統(tǒng)的體系結(jié)構(gòu)應(yīng)用程序調(diào)用ODBCAPI與數(shù)據(jù)庫進行交互的操作包括：建立數(shù)據(jù)庫連接向數(shù)據(jù)庫發(fā)送SQL語句獲取數(shù)據(jù)庫操作結(jié)果斷開與數(shù)據(jù)庫的連接ODBC編程ODBC編程ODBC驅(qū)動管理器負責在運行時根據(jù)應(yīng)用程序的訪問要求，搜索相應(yīng)的驅(qū)動程序，并動態(tài)裝入，將用戶的數(shù)據(jù)訪問傳遞給驅(qū)動程序來執(zhí)行。

networkDriver（SQLServer）

ApplicationDriverManagerSQLServerDriver（MySQL）

MySQLDriver（Oracle）

OracleODBCAPIODBC應(yīng)用系統(tǒng)的體系結(jié)構(gòu)驅(qū)動程序管理器由微軟公司提供，它包含在ODBC32.DLL中。它管理應(yīng)用程序和驅(qū)動程序之間的通信，主要功能包括：選擇和連接正確的驅(qū)動程序，裝載ODBC驅(qū)動程序；管理數(shù)據(jù)源（驅(qū)動類型和數(shù)據(jù)庫服務(wù)器地址信息）；檢查ODBC調(diào)用參數(shù)的合法性及記錄ODBC函數(shù)的調(diào)用。ODBC編程ODBC編程驅(qū)動程序?qū)崿F(xiàn)對數(shù)據(jù)源的各種操作，與數(shù)據(jù)庫建立連接并向DBMS提交數(shù)據(jù)操作請求

networkDriver（SQLServer）

ApplicationDriverManagerSQLServerDriver（MySQL）

MySQLDriver（Oracle）

OracleODBCAPIODBC應(yīng)用系統(tǒng)的體系結(jié)構(gòu)ODBC編程數(shù)據(jù)源是與具體數(shù)據(jù)庫建立的連接的抽象，用數(shù)據(jù)源名代表用戶名、服務(wù)器名、所連接的數(shù)據(jù)庫名等。

networkDriver（SQLServer）

ApplicationDriverManagerSQLServerDriver（MySQL）

MySQLDriver（Oracle）

OracleODBCAPIODBC應(yīng)用系統(tǒng)的體系結(jié)構(gòu)ODBCAPI

—ODBC3.0標準提供了76個函數(shù)接口(API)，按其功能大致可以分為：分配和釋放環(huán)境句柄、連接句柄、語句句柄函數(shù)連接函數(shù)與信息相關(guān)的函數(shù)事務(wù)處理函數(shù)執(zhí)行相關(guān)函數(shù)編目函數(shù)。應(yīng)用程序通過對編目函數(shù)的調(diào)用來獲取數(shù)據(jù)字典的信息如權(quán)限、表結(jié)構(gòu)等。ODBC編程句柄(handle)：

在ODBC開發(fā)的過程中，ODBC驅(qū)動管理器根據(jù)應(yīng)用程序的請求，為每個驅(qū)動程序提供一塊內(nèi)存空間，用以存儲相關(guān)的ODBC環(huán)境、每個連接以及每條SQL語句的相關(guān)信息，并把指向這些內(nèi)存空間的地址作為變量返回給應(yīng)用程序。句柄就是這些在應(yīng)用程序中使用的變量。ODBC編程句柄(handle)：32位整數(shù)值，代表一個指針，用于標識一個特定的對象。

環(huán)境句柄：定義一個數(shù)據(jù)庫環(huán)境，用以存儲有關(guān)數(shù)據(jù)庫環(huán)境信息。其數(shù)據(jù)類型為HENV。

連接句柄：定義一個數(shù)據(jù)庫連接，用以存儲有關(guān)數(shù)據(jù)庫連接的信息，其數(shù)據(jù)類型為HDBC。

語句句柄：定義一條SQL語句，其數(shù)據(jù)類型為HSTMT。ODBC編程為什么需要句柄？ConnectDB(SQLServer_DSN);ConnectDB(MySQL_DSN);ExecSQL(“select*fromodbc”)handle1=ConnectDB(SQLServer_DSN);handle2=ConnectDB(MySQL_DSN);ExecSQL(handle1,“select*fromodbc”);ExecSQL(handle2,“select*fromodbc”);ODBC編程ODBC應(yīng)用程序環(huán)境句柄連接句柄數(shù)據(jù)源語句句柄n1n11n11各對象之間的關(guān)系ODBC編程ODBC編程配置數(shù)據(jù)源初始化環(huán)境建立連接分配語句句柄執(zhí)行SQL語句有無結(jié)果集有無結(jié)果集處理ODBC編程ODBC工作流程配置數(shù)據(jù)源又兩種方法：（1）運行數(shù)據(jù)源管理工具進行配置（2）使用DriverManager提供的ConfigDsn函數(shù)來增加、修改或刪除數(shù)據(jù)源?！癘DBC”數(shù)據(jù)源管理器界面

“ODBC”數(shù)據(jù)源的FileDSN界面

ODBC編程

ODBC數(shù)據(jù)源驅(qū)動程序選擇窗口

ODBC數(shù)據(jù)源名稱輸入界面

ODBC編程指定SQLServer服務(wù)器的界面

選擇連接的數(shù)據(jù)庫ODBC編程SQLAllocEnv()SQLAllocConnect()配置數(shù)據(jù)源初始化環(huán)境建立連接分配語句句柄執(zhí)行SQL語句有無結(jié)果集有無結(jié)果集處理ODBC編程ODBC工作流程SQLConnect（）配置數(shù)據(jù)源初始化環(huán)境建立連接分配語句句柄執(zhí)行SQL語句有無結(jié)果集有無結(jié)果集處理ODBC編程ODBC工作流程SQLAllocStmt()配置數(shù)據(jù)源初始化環(huán)境建立連接分配語句句柄執(zhí)行SQL語句有無結(jié)果集有無結(jié)果集處理ODBC編程ODBC工作流程SQLExecDirect()配置數(shù)據(jù)源初始化環(huán)境建立連接分配語句句柄執(zhí)行SQL語句有無結(jié)果集有無結(jié)果集處理ODBC編程ODBC工作流程SQLFetch()配置數(shù)據(jù)源初始化環(huán)境建立連接分配語句句柄執(zhí)行SQL語句有無結(jié)果集有無結(jié)果集處理ODBC編程語句句柄存放SQL語句執(zhí)行結(jié)果。通過游標訪問結(jié)果集，游標分為forward-only游標和可滾動游標（scroll）當結(jié)果集剛剛生成時，游標指向第一行數(shù)據(jù)之前ODBC工作流程RETCODEret;//ODBC返回代碼HENVhenv;//環(huán)境句柄HDBChdbc;//連接句柄HSTMThstmt;//語句句柄SQLCHARsData[255];