第八章數(shù)據(jù)庫(kù)編程

數(shù)據(jù)庫(kù)系統(tǒng)概論AnIntroductiontoDatabaseSystem

第八章數(shù)據(jù)庫(kù)編程數(shù)據(jù)庫(kù)編程應(yīng)用系統(tǒng)訪問(wèn)和管理數(shù)據(jù)庫(kù)中數(shù)據(jù)的方式主要有：嵌入式SQL（EmbeddedSQL,ESQL）PL/SQL(ProceduralLanguage/SQL)

通過(guò)API（ApplicationProgrammingInterface）

ODBCJDBCOLEDB第八章數(shù)據(jù)庫(kù)編程8.1嵌入式SQL8.2存儲(chǔ)過(guò)程8.3ODBC編程8.1嵌入式SQLSQL語(yǔ)言提供了兩種不同的使用方式：交互式嵌入式為什么要引入嵌入式SQLSQL語(yǔ)言是非過(guò)程性語(yǔ)言事務(wù)處理應(yīng)用需要高級(jí)語(yǔ)言這兩種方式細(xì)節(jié)上有差別，在程序設(shè)計(jì)的環(huán)境下，SQL語(yǔ)句要做某些必要的擴(kuò)充8.1嵌入式SQL（續(xù)）嵌入式SQL(ESQL)是用于SQL數(shù)據(jù)庫(kù)訪問(wèn)的SQL-92標(biāo)準(zhǔn)應(yīng)用程序接口(API)。ESQL的編譯需要分兩步進(jìn)行：

1、預(yù)編譯器將嵌入式SQL語(yǔ)句翻譯成編寫(xiě)該應(yīng)用程序所使用的編程語(yǔ)言中的命令。生成的語(yǔ)句專(zhuān)供提供預(yù)編譯器的數(shù)據(jù)庫(kù)使用，因此，雖然原始源程序與ESQL同類(lèi)，但生成的語(yǔ)句和最后的可執(zhí)行文件專(zhuān)門(mén)用于某個(gè)數(shù)據(jù)庫(kù)供應(yīng)商。

2、然后，用應(yīng)用程序編程語(yǔ)言的編譯器編譯由預(yù)編譯器生成的源程序。

8.1嵌入式SQL8.1.1嵌入式SQL的處理過(guò)程8.1.2嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信8.1.3不使用游標(biāo)的SQL語(yǔ)句8.1.4使用游標(biāo)的SQL語(yǔ)句8.1.5動(dòng)態(tài)SQL8.1.6小結(jié)8.1.1嵌入式SQL的處理過(guò)程主語(yǔ)言嵌入式SQL是將SQL語(yǔ)句嵌入程序設(shè)計(jì)語(yǔ)言中，被嵌入的程序設(shè)計(jì)語(yǔ)言，如C、C++、Java，稱(chēng)為宿主語(yǔ)言，簡(jiǎn)稱(chēng)主語(yǔ)言。處理過(guò)程預(yù)編譯方法嵌入式SQL的處理過(guò)程（續(xù)）主語(yǔ)言程序含ESQL語(yǔ)句RDBMS的預(yù)處理程序ESQL語(yǔ)句轉(zhuǎn)換為函數(shù)調(diào)用主語(yǔ)言編譯程序目標(biāo)語(yǔ)言程序ESQL基本處理過(guò)程嵌入式SQL的處理過(guò)程（續(xù)）為了區(qū)分SQL語(yǔ)句與主語(yǔ)言語(yǔ)句，所有SQL語(yǔ)句必須加前綴EXECSQL，以(;)結(jié)束:EXECSQL<SQL語(yǔ)句>;8.1嵌入式SQL8.1.1嵌入式SQL的處理過(guò)程8.1.2嵌入式SQL與主語(yǔ)言的通信8.1.3不使用游標(biāo)的SQL語(yǔ)句8.1.4使用游標(biāo)的SQL語(yǔ)句8.1.5動(dòng)態(tài)SQL8.1.6小結(jié)8.1.2嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信將SQL嵌入到高級(jí)語(yǔ)言中混合編程，程序中會(huì)含有兩種不同計(jì)算模型的語(yǔ)句SQL語(yǔ)句描述性的面向集合的語(yǔ)句負(fù)責(zé)操縱數(shù)據(jù)庫(kù)高級(jí)語(yǔ)言語(yǔ)句過(guò)程性的面向記錄的語(yǔ)句負(fù)責(zé)控制程序流程它們之間應(yīng)該如何通信？嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信（續(xù)）首先用SQL通信區(qū)SQLCA向主語(yǔ)言傳遞SQL語(yǔ)句的執(zhí)行狀態(tài)信息，使主語(yǔ)言能夠據(jù)此控制程序流程；在程序運(yùn)行中，主語(yǔ)言向SQL語(yǔ)句提供參數(shù)，使用主變量（hostvariable）輸入數(shù)據(jù)；同時(shí)，程序把SQL語(yǔ)句查詢(xún)數(shù)據(jù)庫(kù)的結(jié)果交主語(yǔ)言進(jìn)一步處理，其中使用主變量和游標(biāo)（cursor）向主語(yǔ)言輸出數(shù)據(jù)，從而實(shí)現(xiàn)了SQL語(yǔ)言的過(guò)程化工作。

SQL語(yǔ)句執(zhí)行后，系統(tǒng)要反饋給應(yīng)用程序若干信息，主要包括描述系統(tǒng)當(dāng)前工作狀態(tài)和運(yùn)行環(huán)境的各種數(shù)據(jù)，這些信息將送到SQL通信區(qū)SQLCA中。應(yīng)用程序從SQLCA中取出這些狀態(tài)信息，據(jù)此決定接下來(lái)執(zhí)行的語(yǔ)句。嵌入式SQL語(yǔ)句與主語(yǔ)言之間的通信（續(xù)）數(shù)據(jù)庫(kù)工作單元與源程序工作單元之間的通信：1.SQL通信區(qū)向主語(yǔ)言傳遞SQL語(yǔ)句的執(zhí)行狀態(tài)信息使主語(yǔ)言能夠據(jù)此控制程序流程2.主變量主語(yǔ)言向SQL語(yǔ)句提供參數(shù)將SQL語(yǔ)句查詢(xún)數(shù)據(jù)庫(kù)的結(jié)果交主語(yǔ)言進(jìn)一步處理3.游標(biāo)解決集合性操作語(yǔ)言與過(guò)程性操作語(yǔ)言的不匹配一、SQL通信區(qū)SQLCA：SQLCommunicationAreaSQLCA是一個(gè)數(shù)據(jù)結(jié)構(gòu)SQLCA的用途SQL語(yǔ)句執(zhí)行后，RDBMS反饋給應(yīng)用程序信息

描述系統(tǒng)當(dāng)前工作狀態(tài)描述運(yùn)行環(huán)境這些信息將送到SQL通信區(qū)SQLCA中應(yīng)用程序從SQLCA中取出這些狀態(tài)信息，據(jù)此決定接下來(lái)執(zhí)行的語(yǔ)句SQL通信區(qū)SQLCA使用方法：定義SQLCA

用EXECSQLINCLUDESQLCA定義使用SQLCASQLCA中有一個(gè)存放每次執(zhí)行SQL語(yǔ)句后返回代碼的變量SQLCODE如果SQLCODE等于預(yù)定義的常量SUCCESS，則表示SQL語(yǔ)句成功，否則表示出錯(cuò)應(yīng)用程序每執(zhí)行完一條SQL語(yǔ)句之后都應(yīng)該測(cè)試一下SQLCODE的值，以了解該SQL語(yǔ)句執(zhí)行情況并做相應(yīng)處理二、主變量主變量嵌入式SQL語(yǔ)句中可以使用主語(yǔ)言的程序變量來(lái)輸入或輸出數(shù)據(jù)在SQL語(yǔ)句中使用的主語(yǔ)言程序變量簡(jiǎn)稱(chēng)為主變量（HostVariable）主變量（續(xù)）

主變量的類(lèi)型輸入主變量輸出主變量一個(gè)主變量有可能既是輸入主變量又是輸出主變量主變量（續(xù)）

在SQL語(yǔ)句中使用主變量1)說(shuō)明主變量BEGINDECLARESECTION ......... .........(說(shuō)明主變量) .........ENDDECLARESECTION主變量（續(xù)）

2)使用主變量說(shuō)明之后的主變量可以在SQL語(yǔ)句中任何一個(gè)能夠使用表達(dá)式的地方出現(xiàn)為了與數(shù)據(jù)庫(kù)對(duì)象名（表名、視圖名、列名等）區(qū)別，SQL語(yǔ)句中的主變量名前要加冒號(hào)（:）作為標(biāo)志主變量（續(xù)）

在SQL語(yǔ)句之外(主語(yǔ)言語(yǔ)句中)使用主變量的方法可以直接引用，不必加冒號(hào)三、游標(biāo)（cursor）為什么要使用游標(biāo)SQL語(yǔ)言與主語(yǔ)言具有不同數(shù)據(jù)處理方式SQL語(yǔ)言是面向集合的，一條SQL語(yǔ)句原則上可以產(chǎn)生或處理多條記錄主語(yǔ)言是面向記錄的，一組主變量一次只能存放一條記錄僅使用主變量并不能完全滿足SQL語(yǔ)句向應(yīng)用程序輸出數(shù)據(jù)的要求嵌入式SQL引入了游標(biāo)的概念，用來(lái)協(xié)調(diào)這兩種不同的處理方式游標(biāo)（續(xù)）游標(biāo)游標(biāo)是系統(tǒng)為用戶開(kāi)設(shè)的一個(gè)數(shù)據(jù)緩沖區(qū)，存放SQL語(yǔ)句的執(zhí)行結(jié)果每個(gè)游標(biāo)區(qū)都有一個(gè)名字用戶可以用SQL語(yǔ)句逐一從游標(biāo)中獲取記錄，并賦給主變量，交由主語(yǔ)言進(jìn)一步處理四、建立和關(guān)閉數(shù)據(jù)庫(kù)連接建立數(shù)據(jù)庫(kù)連接

EXECSQLCONNECTTOtarget[ASconnection-name][USERuser-name];

target是要連接的數(shù)據(jù)庫(kù)服務(wù)器：常見(jiàn)的服務(wù)器標(biāo)識(shí)串，如<dbname>@<hostname>:<port>包含服務(wù)器標(biāo)識(shí)的SQL串常量DEFAULTconnect-name是可選的連接名，連接必須是一個(gè)有效的標(biāo)識(shí)符在整個(gè)程序內(nèi)只有一個(gè)連接時(shí)可以不指定連接名關(guān)閉數(shù)據(jù)庫(kù)連接 EXECSQLDISCONNECT[connection];程序運(yùn)行過(guò)程中可以修改當(dāng)前連接：

EXECSQLSETCONNECTIONconnection-name|DEFAULT;五、程序?qū)嵗齕例1]依次檢查某個(gè)系的學(xué)生記錄，交互式更新某些學(xué)生年齡。EXECSQLBEGINDECLARESECTION;/*主變量說(shuō)明開(kāi)始*/ chardeptname[64]; charHSno[64]; charHSname[64]; charHSsex[64]; int HSage; int NEWAGE;EXECSQLENDDECLARESECTION;/*主變量說(shuō)明結(jié)束*/longSQLCODE;EXECSQLINCLUDEsqlca;/*定義SQL通信區(qū)*/程序?qū)嵗ɡm(xù)）intmain(void)/*C語(yǔ)言主程序開(kāi)始*/{ int count=0; charyn;/*變量yn代表yes或no*/ printf("Pleasechoosethedepartmentname(CS/MA/IS):"); scanf("%s",deptname);/*為主變量deptname賦值*/ EXECSQLCONNECTTOTEST@localhost:54321USER"SYSTEM"/"MANAGER";/*連接數(shù)據(jù)庫(kù)TEST*/ EXECSQLDECLARESXCURSORFOR/*定義游標(biāo)*/ SELECTSno,Sname,Ssex,Sage/*SX對(duì)應(yīng)語(yǔ)句的執(zhí)行結(jié)果*/ FROMStudent WHERESDept=:deptname; EXECSQLOPENSX;/*打開(kāi)游標(biāo)SX便指向查詢(xún)結(jié)果的第一行*/程序?qū)嵗ɡm(xù)）for(;;) /*用循環(huán)結(jié)構(gòu)逐條處理結(jié)果集中的記錄*/{ EXECSQLFETCHSXINTO:HSno,:HSname,:HSsex,:HSage; /*推進(jìn)游標(biāo)，將當(dāng)前數(shù)據(jù)放入主變量*/ if(sqlca.sqlcode!=0)/*sqlcode!=0,表示操作不成功*/ break; /*利用SQLCA中的狀態(tài)信息決定何時(shí)退出循環(huán)*/ if(count++==0)/*如果是第一行的話，先打出行頭*/ printf("\n%-10s%-20s%-10s%-10s\n","Sno","Sname","Ssex","Sage"); printf("%-10s%-20s%-10s%-10d\n",HSno,HSname,HSsex,HSage); /*打印查詢(xún)結(jié)果*/ printf("UPDATEAGE(y/n)?");/*詢(xún)問(wèn)用戶是否要更新該學(xué)生的年齡*/ do{ scanf("%c",&yn); } while(yn!='N'&&yn!='n'&&yn!='Y'&&yn!='y');

程序?qū)嵗ɡm(xù)） if(yn=='y'||yn=='Y')/*如果選擇更新操作*/ { printf("INPUTNEWAGE:"); scanf("%d",&NEWAGE);/*用戶輸入新年齡到主變量中*/ EXECSQLUPDATEStudent /*嵌入式SQL*/ SETSage=:NEWAGE WHERECURRENTOFSX; }/*對(duì)當(dāng)前游標(biāo)指向的學(xué)生年齡進(jìn)行更新*/}EXECSQLCLOSESX;/*關(guān)閉游標(biāo)SX不再和查詢(xún)結(jié)果對(duì)應(yīng)*/ EXECSQLCOMMITWORK;/*提交更新*/ EXECSQLDISCONNECTTEST;/*斷開(kāi)數(shù)據(jù)庫(kù)連接*/}8.1.6小結(jié)在嵌入式SQL中，SQL語(yǔ)句與主語(yǔ)言語(yǔ)句分工非常明確SQL語(yǔ)句直接與數(shù)據(jù)庫(kù)打交道，取出數(shù)據(jù)庫(kù)中的數(shù)據(jù)。主語(yǔ)言語(yǔ)句控制程序流程對(duì)取出的數(shù)據(jù)做進(jìn)一步加工處理小結(jié)（續(xù)）SQL語(yǔ)言是面向集合的，一條SQL語(yǔ)句原則上可以產(chǎn)生或處理多條記錄主語(yǔ)言是面向記錄的，一組主變量一次只能存放一條記錄僅使用主變量并不能完全滿足SQL語(yǔ)句向應(yīng)用程序輸出數(shù)據(jù)的要求嵌入式SQL引入了游標(biāo)的概念，用來(lái)協(xié)調(diào)這兩種不同的處理方式與OLEDB這類(lèi)COMAPI或ODBC這類(lèi)調(diào)用級(jí)接口相比，嵌入式SQL的語(yǔ)法更簡(jiǎn)單，因此更易于學(xué)習(xí)和編程。但嵌入式SQL不如OLEDB或ODBC靈活，在用OLEDB或ODBC精心編寫(xiě)的應(yīng)用程序中，只需切換驅(qū)動(dòng)程序或提供程序，即可從一個(gè)DBMS切換到另一個(gè)。在編譯應(yīng)用程序（例如開(kāi)發(fā)按需查詢(xún)工具時(shí)）時(shí)不識(shí)別SQL語(yǔ)句的環(huán)境中，更適合使用OLEDB和ODBC。小結(jié)（續(xù)）MicrosoftSQLServer提供用于C應(yīng)用程序的嵌入式SQL預(yù)編譯器。這個(gè)SQLServer預(yù)編譯器將嵌入式SQL語(yǔ)句翻譯成對(duì)適當(dāng)DB-LibraryAPI函數(shù)的調(diào)用。在以后的版本中也將不再提供這種支持。可以使用MicrosoftActiveXDataObjects(ADO)、OLEDB或ODBC來(lái)代替用于C語(yǔ)言的嵌入式SQL，對(duì)SQLServer中的數(shù)據(jù)進(jìn)行訪問(wèn)。小結(jié)（續(xù)）第八章數(shù)據(jù)庫(kù)編程8.1嵌入式SQL8.2存儲(chǔ)過(guò)程8.3ODBC編程PL/SQL介紹PL/SQL(ProceduralLanguage/SQL)SQL的擴(kuò)展增加了過(guò)程化語(yǔ)句功能基本結(jié)構(gòu)是塊塊之間可以互相嵌套每個(gè)塊完成一個(gè)邏輯操作PL/SQL的塊結(jié)構(gòu)PL/SOL塊的基本結(jié)構(gòu)：

1.定義部分 DECLARE ------變量、常量、游標(biāo)、異常等定義的變量、常量等只能在該基本塊中使用當(dāng)基本塊執(zhí)行結(jié)束時(shí)，定義就不再存在PL/SQL的塊結(jié)構(gòu)（續(xù)）PL/SQL塊的基本結(jié)構(gòu)(續(xù))：2.執(zhí)行部分BEGIN ------SQL語(yǔ)句、PL/SQL的流程控制語(yǔ)句 EXCEPTION------異常處理部分 END；8.2.2變量常量的定義1.PL/SQL中定義變量的語(yǔ)法形式是:變量名數(shù)據(jù)類(lèi)型［［NOTNULL］:=初值表達(dá)式］或 變量名數(shù)據(jù)類(lèi)型［［NOTNULL］初值表達(dá)式］2.常量的定義類(lèi)似于變量的定義:

常量名數(shù)據(jù)類(lèi)型CONSTANT:=常量表達(dá)式 常量必須要給一個(gè)值，并且該值在存在期間或常量的作用域內(nèi)不能改變。如果試圖修改它，PL/SQL將返回一個(gè)異常。3.賦值語(yǔ)句

變量名稱(chēng):=表達(dá)式8.2.3控制結(jié)構(gòu)PL/SQL

功能：一、條件控制語(yǔ)句二、循環(huán)控制語(yǔ)句三、錯(cuò)誤處理控制結(jié)構(gòu)（續(xù)）一、條件控制語(yǔ)句IF-THEN，IF-THEN-ELSE和嵌套的IF語(yǔ)句

1.IFconditionTHEN

Sequence_of_statements; ENDIF 2.IFconditionTHEN

Sequence_of_statements1; ELSE

Sequence_of_statements2;

ENDIF; 3.在THEN和ELSE子句中還可以再包括IF語(yǔ)句，即IF語(yǔ)句可以嵌套

控制結(jié)構(gòu)（續(xù)）二、循環(huán)控制語(yǔ)句

LOOP，WHILE-LOOP和FOR-LOOP

1.最簡(jiǎn)單的循環(huán)語(yǔ)句LOOP LOOP Sequence_of_statements;

ENDLOOP; 多數(shù)數(shù)據(jù)庫(kù)服務(wù)器的PL/SQL都提供EXIT、BREAK或LEAVE等循環(huán)結(jié)束語(yǔ)句，保證LOOP語(yǔ)句塊能夠結(jié)束?？刂平Y(jié)構(gòu)（續(xù)）二、循環(huán)控制語(yǔ)句（續(xù)）2.WHILE-LOOPWHILEconditionLOOP

Sequence_of_statements; ENDLOOP;每次執(zhí)行循環(huán)體語(yǔ)句之前，首先對(duì)條件進(jìn)行求值如果條件為真，則執(zhí)行循環(huán)體內(nèi)的語(yǔ)句序列。如果條件為假，則跳過(guò)循環(huán)并把控制傳遞給下一個(gè)語(yǔ)句3.FOR-LOOP FORcountIN［REVERSE］bound1…bound2LOOP

Sequence_of_statements; ENDLOOP;控制結(jié)構(gòu)（續(xù)）三、錯(cuò)誤處理：如果PL/SQL在執(zhí)行時(shí)出現(xiàn)異常，則應(yīng)該讓程序在產(chǎn)生異常的語(yǔ)句處停下來(lái)，根據(jù)異常的類(lèi)型去執(zhí)行異常處理語(yǔ)句SQL標(biāo)準(zhǔn)對(duì)數(shù)據(jù)庫(kù)服務(wù)器提供什么樣的異常處理做出了建議，要求PL/SQL管理器提供完善的異常處理機(jī)制8.2存儲(chǔ)過(guò)程8.2.1存儲(chǔ)過(guò)程的定義8.2.2存儲(chǔ)過(guò)程的優(yōu)點(diǎn)8.2.3存儲(chǔ)過(guò)程的創(chuàng)建8.2.4重新編譯存儲(chǔ)過(guò)程8.2.5自動(dòng)執(zhí)行存儲(chǔ)過(guò)程8.2.6查看、修改和刪除存儲(chǔ)過(guò)程8.2.1存儲(chǔ)過(guò)程的定義存儲(chǔ)過(guò)程：由PL/SQL語(yǔ)句書(shū)寫(xiě)的過(guò)程，經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，使用時(shí)只要調(diào)用即可。在SQLServer中存儲(chǔ)過(guò)程分為三類(lèi)：即系統(tǒng)提供的存儲(chǔ)過(guò)程（sp_）

、擴(kuò)展存儲(chǔ)過(guò)程（xp_）和用戶自定義的存儲(chǔ)過(guò)程。8.2.2存儲(chǔ)過(guò)程的優(yōu)點(diǎn)存儲(chǔ)過(guò)程是一種把重復(fù)的任務(wù)操作封裝起來(lái)的一種方法，支持用戶提供參數(shù)，可以返回、修改值，允許多個(gè)用戶使用相同的代碼，完成相同的數(shù)據(jù)操作。它提供了一種集中且一致的實(shí)現(xiàn)數(shù)據(jù)完整性邏輯的方法。存儲(chǔ)過(guò)程用于實(shí)現(xiàn)頻繁使用的查詢(xún)、業(yè)務(wù)規(guī)則、被其它過(guò)程使用的公共例行程序。存儲(chǔ)過(guò)程具有以下優(yōu)點(diǎn)：實(shí)現(xiàn)了模塊化編程。存儲(chǔ)過(guò)程具有對(duì)數(shù)據(jù)庫(kù)立即訪問(wèn)的功能。使用存儲(chǔ)過(guò)程可以加快程序的運(yùn)行速度。使用存儲(chǔ)過(guò)程可以減少網(wǎng)絡(luò)流量。使用存儲(chǔ)過(guò)程可以提高數(shù)據(jù)庫(kù)的安全性。8.2.3存儲(chǔ)過(guò)程的創(chuàng)建在SQLServer中，可以使用兩種方法創(chuàng)建存儲(chǔ)過(guò)程：①使用T-SQL語(yǔ)句中的CREATEPROCEDURE命令創(chuàng)建存儲(chǔ)過(guò)程。②利用SQLServer企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程。創(chuàng)建存儲(chǔ)過(guò)程時(shí)，需要確定存儲(chǔ)過(guò)程的三個(gè)組成部分：所有的輸入?yún)?shù)以及傳給調(diào)用者的輸出參數(shù)。被執(zhí)行的針對(duì)數(shù)據(jù)庫(kù)的操作語(yǔ)句，包括調(diào)用其它存儲(chǔ)過(guò)程的語(yǔ)句。返回給調(diào)用者的狀態(tài)值，以指明調(diào)用是成功還是失敗。存儲(chǔ)過(guò)程的創(chuàng)建創(chuàng)建存儲(chǔ)過(guò)程： CREATEProcedure過(guò)程名（［參數(shù)1，參數(shù)2，...］）AS <PL/SQL塊>；過(guò)程名：數(shù)據(jù)庫(kù)服務(wù)器合法的對(duì)象標(biāo)識(shí)參數(shù)列表：用名字來(lái)標(biāo)識(shí)調(diào)用時(shí)給出的參數(shù)值，必須指定值的數(shù)據(jù)類(lèi)型。參數(shù)也可以定義輸入?yún)?shù)、輸出參數(shù)或輸入/輸出參數(shù)。默認(rèn)為輸入?yún)?shù)。過(guò)程體：是一個(gè)<PL/SQL塊>。包括聲明部分和可執(zhí)行語(yǔ)句部分

1.使用T-SQL語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程創(chuàng)建一個(gè)存儲(chǔ)過(guò)程的語(yǔ)法如下：CREATEPROC[EDURE][OWNER.]procedure_name[({@parameterdata_type}[VARYING][=default][OUTPUT])][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]ASsql_statement[...n]創(chuàng)建存儲(chǔ)過(guò)程參數(shù)的含義

procedure_name：用于指定要?jiǎng)?chuàng)建的存儲(chǔ)過(guò)程的名稱(chēng)。@parameter：過(guò)程中的參數(shù)。在CREATEPROCEDURE語(yǔ)句中可以聲明一個(gè)或多個(gè)參數(shù)。data_type：用于指定參數(shù)的數(shù)據(jù)類(lèi)型。VARYING：用于指定作為輸出OUTPUT參數(shù)支持的結(jié)果集。Default：用于指定參數(shù)的默認(rèn)值。OUTPUT：表明該參數(shù)是一個(gè)返回參數(shù)。RECOMPILE：表明SQLServer不會(huì)保存該存儲(chǔ)過(guò)程的執(zhí)行計(jì)劃。ENCRYPTION：表示SQLServer加密了syscomments

表，該表的text字段是包含CREATEPROCEDURE語(yǔ)句的存儲(chǔ)過(guò)程文本。AS：用于指定該存儲(chǔ)過(guò)程要執(zhí)行的操作。sql_statement：是存儲(chǔ)過(guò)程中要包含的任意數(shù)目和類(lèi)型的Transact-SQL語(yǔ)句。

【例】創(chuàng)建一存儲(chǔ)過(guò)程，要求該存儲(chǔ)過(guò)程返回學(xué)生姓名、所學(xué)課程和任課教師。CREATEPROCEDURE

spStuCouTea_nameASSELECTa.student_name,b.course_name,c.teacher_nameFROMstudentaINNERJOINstudent_coursedONa.student_id=d.student_idINNERJOINcoursebONd.course_id=b.course_idINNERJOINteacher_course_classeON(e.class_id=a.class_id)and(e.course_id=d.course_id)INNERJOINteachercONc.teacher_id=e.teacher_id執(zhí)行以上腳本，便可創(chuàng)建存儲(chǔ)過(guò)程spStuCouTea_name。如要執(zhí)行該存儲(chǔ)過(guò)程，可在查詢(xún)分析器中執(zhí)行如下語(yǔ)句：EXEC

spStuCouTea_name創(chuàng)建存儲(chǔ)過(guò)程-例存儲(chǔ)過(guò)程的用戶接口（續(xù)）［例11］利用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)下面的應(yīng)用:從一個(gè)賬戶轉(zhuǎn)指定數(shù)額的款項(xiàng)到另一個(gè)賬戶中。

CREATEPROCEDURETRANSFER(inAccountINT，outAccountINT，amountFLOAT) ASDECLARE totalDepositFLOAT; BEGIN/*檢查轉(zhuǎn)出賬戶的余額*/ SELECTtotalINTOtotalDepositFROMACCOUNTWHEREACCOUNTNUM=outAccount; IFtotalDepositISNULLTHEN/*賬戶不存在或賬戶中沒(méi)有存款*/ ROLLBACK; RETURN; ENDIF;存儲(chǔ)過(guò)程的用戶接口（續(xù)）IFtotalDeposit<amountTHEN /*賬戶賬戶存款不足*/ ROLLBACK; RETURN; ENDIF; UPDATEaccountSETtotal=total-amount

WHEREACCOUNTNUM=outAccount; /*修改轉(zhuǎn)出賬戶，減去轉(zhuǎn)出額*/UPDATEaccountSETtotal=total+amountWHEREACCOUNTNUM=inAccount; /*修改轉(zhuǎn)入賬戶，增加轉(zhuǎn)出額*/ COMMIT; /*提交轉(zhuǎn)賬事務(wù)*/ END;①不能將CREATEPROCEDURE語(yǔ)句與其它SQL語(yǔ)句組合到單個(gè)批處理中。②只能在當(dāng)前數(shù)據(jù)庫(kù)中創(chuàng)建存儲(chǔ)過(guò)程。③一個(gè)存儲(chǔ)過(guò)程的最大尺寸為128M。創(chuàng)建存儲(chǔ)過(guò)程2.使用企業(yè)管理器創(chuàng)建存儲(chǔ)過(guò)程（１）在SQLServer企業(yè)管理器中，選擇指定的服務(wù)器和數(shù)據(jù)庫(kù)，用右鍵單擊要?jiǎng)?chuàng)建存儲(chǔ)過(guò)程的數(shù)據(jù)庫(kù)，在彈出的快捷菜單中選擇“新建”選項(xiàng)，再選擇下一級(jí)菜單中的“存儲(chǔ)過(guò)程…”選項(xiàng)，或者用右鍵單擊存儲(chǔ)過(guò)程圖標(biāo)，從彈出的快捷菜單中選擇“新建存儲(chǔ)過(guò)程…”選項(xiàng)，均會(huì)出現(xiàn)創(chuàng)建存儲(chǔ)過(guò)程對(duì)話框。（２）在文本框中可以輸入創(chuàng)建存儲(chǔ)過(guò)程的T_SQL語(yǔ)句，單擊“檢查語(yǔ)法”，則可以檢查語(yǔ)法是否正確；單擊“確定”按鈕，即可保存該存儲(chǔ)過(guò)程。如果要設(shè)置權(quán)限，單擊“權(quán)限…”按鈕。3.創(chuàng)建帶輸入?yún)?shù)的存儲(chǔ)過(guò)程輸入?yún)?shù)是指由調(diào)用程序向存儲(chǔ)過(guò)程傳遞的參數(shù)。它們?cè)趧?chuàng)建存儲(chǔ)過(guò)程語(yǔ)句中被定義，其參數(shù)值在執(zhí)行該存儲(chǔ)過(guò)程時(shí)由調(diào)用該存儲(chǔ)過(guò)程的語(yǔ)句給出。具體語(yǔ)法如下:

@parameter_namedataype[=default]【例】建立一個(gè)存儲(chǔ)過(guò)程，選擇某一個(gè)指定學(xué)生的學(xué)生姓名、所學(xué)課程及任課教師姓名。CREATEPROCEDUREspStuCouTea_withParam@studentnamevarchar(8)

/*@studentnamevarchar(8)='%'*/ASSELECTa.student_name,b.course_name,c.teacher_nameFROM

studentaINNERJOINstudent_coursed

ONa.student_id=d.student_idINNERJOIN

coursebONd.course_id=b.course_idINNERJOINteacher_course_classeON(e.class_id=a.class_id)and(e.course_id=d.course_id)

INNERJOINteacherc

ONc.teacher_id=e.teacher_idWHEREstudent_name=@studentname4.創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過(guò)程通過(guò)在創(chuàng)建存儲(chǔ)過(guò)程的語(yǔ)句中定義輸出參數(shù)，可以創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過(guò)程。執(zhí)行該存儲(chǔ)過(guò)程，可以返回一個(gè)或多個(gè)值。具體語(yǔ)法如下：

@parameter_namedataype[=default]OUTPUT【例】創(chuàng)建一個(gè)實(shí)現(xiàn)加法計(jì)算并將運(yùn)算結(jié)果作為輸出參數(shù)的存儲(chǔ)過(guò)程。CREATEPROCEDURE

spAdd@Value1INT,@Value2INT,@ResultValue

INT

OUTPUTASSELECT

@ResultValue=@Value1+@Value2創(chuàng)建了上面的存儲(chǔ)過(guò)程之后，為了使用spAdd，接受其輸出參數(shù)的返回值，調(diào)用它的程序中也必須定義一個(gè)變量，并使用OUTPUT關(guān)鍵字指定它為調(diào)用輸出參數(shù)?！纠繄?zhí)行spAdd存儲(chǔ)過(guò)程，輸入?yún)?shù)由value1和value2提供，輸出參數(shù)valueTotal。DECLARE

@value1

INTDECLARE

@value2

INTDECLARE

@valueTotal

INTSET

@value1=125SET

@value2=3SET

@valueTotal=34EXEC

spAdd@value1,@value2,@valueTotal

OUTPUTPRINT

CONVERT(CHAR(5),@value1)+‘與’+CONVERT(CHAR(5),@value2)+‘的和等于:’+CONVERT(CHAR(5),@valueTotal)4.創(chuàng)建帶輸出參數(shù)的存儲(chǔ)過(guò)程-例5查看、修改和刪除存儲(chǔ)過(guò)程

1.查看存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程被創(chuàng)建之后，它的名字就存儲(chǔ)在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中。可以使用使用企業(yè)管理器或系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶創(chuàng)建的存儲(chǔ)過(guò)程。⑴查看存儲(chǔ)過(guò)程的定義系統(tǒng)存儲(chǔ)過(guò)程sp_helptext可查看未加密的存儲(chǔ)過(guò)程的定義腳本，也可用于查看規(guī)則、默認(rèn)值、用戶定義函數(shù)、觸發(fā)器或視圖的定義腳本。使用其語(yǔ)法如下：

sp_helptext[@objname=]’name’⑵查看有關(guān)存儲(chǔ)過(guò)程的信息使用系統(tǒng)存儲(chǔ)過(guò)程sp_help可查看有關(guān)存儲(chǔ)過(guò)程的信息。具體語(yǔ)法形式如下：sp_helpproc_name2.修改存儲(chǔ)過(guò)程使用T-SQL語(yǔ)句修改存儲(chǔ)過(guò)程Transact-SQL中提供了ALTERPROCEDURE語(yǔ)句來(lái)更改已經(jīng)創(chuàng)建的存儲(chǔ)過(guò)程，它不會(huì)更改權(quán)限，也不影響相關(guān)的存儲(chǔ)過(guò)程或觸發(fā)器。它的語(yǔ)法如下所示：ALTERPROC

[EDURE]procedure_name

[{@parameterdata_type}

[

VARYING

][=default][OUTPUT]

][,...n][

WITH

{

RECOMPILE|ENCRYPTION

|RECOMPILE,ENCRYPTION}][

FORREPLICATION

]AS

sql_statement[...n]使用企業(yè)管理器修改存儲(chǔ)過(guò)程3.刪除存儲(chǔ)過(guò)程使用SQL語(yǔ)句刪除存儲(chǔ)過(guò)程從當(dāng)前數(shù)據(jù)庫(kù)中刪除一個(gè)或多個(gè)存儲(chǔ)過(guò)程的T-SQL語(yǔ)句是DROPPROCEDURE。具體語(yǔ)法如下：

DROPPROCEDURE{procedure}[,…n]【例】要將spAdd存儲(chǔ)過(guò)程刪除，則可以執(zhí)行

DROPPROC

spAdd語(yǔ)句。使用企業(yè)管理器刪除存儲(chǔ)過(guò)程8.2存儲(chǔ)過(guò)程8.2.1PL/SQL的塊結(jié)構(gòu)8.2.2變量常量的定義8.2.3控制結(jié)構(gòu)8.2.4存儲(chǔ)過(guò)程8.2.5小結(jié)8.2.5小結(jié)存儲(chǔ)過(guò)程的優(yōu)點(diǎn)經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫(kù)服務(wù)器中，運(yùn)行效率高降低客戶機(jī)和服務(wù)器之間的通信量有利于集中控制，方便維護(hù)第八章數(shù)據(jù)庫(kù)編程8.1嵌入式SQL8.2存儲(chǔ)過(guò)程8.3ODBC編程8.3ODBC編程O(píng)DBC優(yōu)點(diǎn)：移植性好能同時(shí)訪問(wèn)不同的數(shù)據(jù)庫(kù)共享多個(gè)數(shù)據(jù)資源8.3ODBC編程8.3.1數(shù)據(jù)庫(kù)互連概述8.3.2ODBC工作原理概述8.3.3ODBCAPI基礎(chǔ)8.3.4ODBC的工作流程8.3.5小結(jié)8.3.1數(shù)據(jù)庫(kù)互連概述ODBC產(chǎn)生的原因：由于不同的數(shù)據(jù)庫(kù)管理系統(tǒng)的存在，在某個(gè)RDBMS下編寫(xiě)的應(yīng)用程序就不能在另一個(gè)RDBMS下運(yùn)行許多應(yīng)用程序需要共享多個(gè)部門(mén)的數(shù)據(jù)資源，訪問(wèn)不同的RDBMS數(shù)據(jù)庫(kù)互連概述（續(xù)）ODBC：是微軟公司開(kāi)放服務(wù)體系(WindowsOpenServicesArchitecture，WOSA)中有關(guān)數(shù)據(jù)庫(kù)的一個(gè)組成部分提供了一組訪問(wèn)數(shù)據(jù)庫(kù)的標(biāo)準(zhǔn)APIODBC約束力：規(guī)范應(yīng)用開(kāi)發(fā)規(guī)范RDBMS應(yīng)用接口8.3ODBC編程8.3.1數(shù)據(jù)庫(kù)互連概述8.3.2ODBC工作原理概述

8.3.3ODBCAPI基礎(chǔ)8.3.4ODBC的工作流程8.3.5小結(jié)8.3.2ODBC工作原理概述ODBC應(yīng)用系統(tǒng)的體系結(jié)構(gòu)：一、用戶應(yīng)用程序二、驅(qū)動(dòng)程序管理器三、數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序四、ODBC數(shù)據(jù)源管理一、應(yīng)用程序ODBC應(yīng)用程序包括的內(nèi)容:

請(qǐng)求連接數(shù)據(jù)庫(kù)；向數(shù)據(jù)源發(fā)送SQL語(yǔ)句；為SQL語(yǔ)句執(zhí)行結(jié)果分配存儲(chǔ)空間，定義所讀取的數(shù)據(jù)格式；獲取數(shù)據(jù)庫(kù)操作結(jié)果，或處理錯(cuò)誤；進(jìn)行數(shù)據(jù)處理并向用戶提交處理結(jié)果；請(qǐng)求事務(wù)的提交和回滾操作；斷開(kāi)與數(shù)據(jù)源的連接。

二、驅(qū)動(dòng)程序管理器

驅(qū)動(dòng)程序管理器：用來(lái)管理各種驅(qū)動(dòng)程序包含在ODBC32.DLL中管理應(yīng)用程序和驅(qū)動(dòng)程序之間的通信建立、配置或刪除數(shù)據(jù)源并查看系統(tǒng)當(dāng)前所安裝的數(shù)據(jù)庫(kù)ODBC驅(qū)動(dòng)程序主要功能：裝載ODBC驅(qū)動(dòng)程序選擇和連接正確的驅(qū)動(dòng)程序管理數(shù)據(jù)源檢查ODBC調(diào)用參數(shù)的合法性記錄ODBC函數(shù)的調(diào)用等三、數(shù)據(jù)庫(kù)驅(qū)動(dòng)程序ODBC通過(guò)驅(qū)動(dòng)程序來(lái)提供應(yīng)用系統(tǒng)與數(shù)據(jù)庫(kù)平臺(tái)的獨(dú)立性O(shè)DBC