合工大數(shù)據(jù)庫課件

1、第八講 數(shù)據(jù)庫編程沈明玉數(shù)據(jù)庫編程一、嵌入式SQL二、PL/SQL與Transact-SQL三、存儲(chǔ)過程四、事務(wù)的概念與使用數(shù)據(jù)庫編程一、嵌入式SQL嵌入式SQL什么是嵌入式SQL？ 交互式SQL和嵌入式SQL是SQL的兩種使用方法。當(dāng)一個(gè)程序既要訪問數(shù)據(jù)庫，又要處理數(shù)據(jù)時(shí)，將SQL語言嵌入到程序設(shè)計(jì)語言（宿主語言）中，將SQL語言訪問數(shù)據(jù)庫的功能和宿主語言的數(shù)據(jù)處理功能相結(jié)合，這種使用SQL的方法稱為嵌入式SQL。嵌入式SQL（續(xù)）如何實(shí)現(xiàn)嵌入式SQL？宿主語言的編譯器不能識(shí)別SQL，如何將嵌有SQL語句的宿主語言編譯成可執(zhí)行代碼？宿主語言與DBMS之間如何傳遞數(shù)據(jù)和信息？如何解決元組集合數(shù)

2、據(jù)對(duì)宿主語言變量的賦值？在宿主語言中標(biāo)注SQL語句的方法用EXEC SQL前綴來標(biāo)識(shí)SQL語句，以區(qū)分宿主語言與SQL語言。嵌入式SQL（續(xù)）宿主語言與DBMS之間如何通信？SQL通信區(qū)（SQLCA）SQLCA是一個(gè)數(shù)據(jù)結(jié)構(gòu)SQLCA的用途描述系統(tǒng)當(dāng)前工作狀態(tài) 描述運(yùn)行環(huán)境SQLCA使用方法定義SQLCA：Exec SQL Include SQLCA使用SQLCA：變量SQLCODE，為0-執(zhí)行成功，否則表示出錯(cuò)。嵌入式SQL（續(xù)）共享變量（主變量）宿主語言與SQL語言之間通過共享變量進(jìn)行數(shù)據(jù)傳送；共享變量由宿主語言程序定義，用SQL的Declare說明后可在SQL語句中引用（引用時(shí)需加:號(hào)，

3、以區(qū)分?jǐn)?shù)據(jù)庫自身的變量，如：屬性）。嵌入式SQL（續(xù)）執(zhí)行部分：各種SQL語句的執(zhí)行Exec SQL Insert into Students (sno,sname,sage) values (:sno, :sname, :sage);Exec SQL Update Students SET sname=:sname, sage=:sage Where sno=:sno;Exec SQL Delete from SC Where sno = ( Select sno from Student Where sno=:sno); Exec SQL Select sname, sage into :

4、sname, :sage from Students Where sno=:sno;嵌入式SQL（續(xù)）使用游標(biāo)（cursor）的SQL編程解決多對(duì)一的問題！說明游標(biāo)語句：Exec SQL Declare Cursor for Select from Where ；打開游標(biāo)語句：Exec SQL Open ；推進(jìn)游標(biāo)語句：Exec SQL Fetch into ;關(guān)閉游標(biāo)語句：Exec SQL Close ;嵌入式SQL（續(xù)）建立數(shù)據(jù)庫連接 Exec SQL Connect TO target AS connection-name User user-name; target是要連接的數(shù)據(jù)庫服務(wù)

5、器：常見的服務(wù)器標(biāo)識(shí)串，如: connect-name是可選的連接名，連接必須是一個(gè)有效的標(biāo)識(shí)符 在整個(gè)程序內(nèi)只有一個(gè)連接時(shí)可以不指定連接名。嵌入式SQL（續(xù)）例 依次檢查某個(gè)系的學(xué)生記錄，交互式更新某些學(xué)生年齡。 EXEC SQL BEGIN DEC LARE SECTION; /*主變量說明開始*/ char deptname64; char HSno64; char HSname64; char HSsex64; int HSage; intNEWAGE;EXEC SQL END DECLARE SECTION; /*主變量說明結(jié)束*/long SQLCODE;EXEC SQL INCL

6、UDE sqlca; /*定義SQL通信區(qū)*/嵌入式SQL（續(xù)）int main(void) /*C語言主程序開始*/int count = 0;char yn; /*變量yn代表yes或no*/printf(Please choose the department name(CS/MA/IS): ); scanf(%s, deptname); /*為主變量deptname賦值*/EXEC SQL CONNECT TO TESTlocalhost:54321 USER SYSTEM /MANAGER; /*連接數(shù)據(jù)庫TEST*/EXEC SQL DECLARE SX CURSOR FOR /*

7、定義游標(biāo)*/SELECT Sno, Sname, Ssex, Sage /*SX對(duì)應(yīng)語句的執(zhí)行結(jié)果*/FROM StudentWHERE SDept = :deptname;EXEC SQL OPEN SX; /*打開游標(biāo)SX便指向查詢結(jié)果的第一行*/嵌入式SQL（續(xù)） if (yn = y | yn = Y) /*如果選擇更新操作*/ printf(INPUT NEW AGE:); scanf(%d,&NEWAGE); /*用戶輸入新年齡到主變量中*/ EXEC SQL UPDATE Student /*嵌入式SQL*/SET Sage = :NEWAGE WHERE CURRENT OF

8、SX ; /*對(duì)當(dāng)前游標(biāo)指向的學(xué)生年齡進(jìn)行更新*/ EXEC SQL CLOSE SX; /*關(guān)閉游標(biāo)SX不再和查詢結(jié)果對(duì)應(yīng)*/EXEC SQL COMMIT WORK; /*提交更新*/EXEC SQL DISCONNECT TEST; /*斷開數(shù)據(jù)庫連接*/數(shù)據(jù)庫編程二、PL/SQL與Transact-SQLPL/SQL與Transact-SQLPL/SQLPL/SQL是Oracle對(duì)標(biāo)準(zhǔn)SQL的過程化擴(kuò)充，它將數(shù)據(jù)庫技術(shù)和過程化程序設(shè)計(jì)語言聯(lián)系起來，可使用循環(huán)、分支和嵌套，將SQL的數(shù)據(jù)操縱功能與過程化語言數(shù)據(jù)處理功能相結(jié)合。PL/SQL與Transact-SQL（續(xù)）PL/SQL的作用

9、能夠使一組SQL語句的功能更具模塊化程序特點(diǎn)；采用了過程性語言控制程序的結(jié)構(gòu)；可以對(duì)程序中的錯(cuò)誤進(jìn)行自動(dòng)處理，使程序能夠在遇到錯(cuò)誤的時(shí)候不會(huì)被中斷；具有較好的可移植性，可以移植到另一個(gè)Oracle數(shù)據(jù)庫中；集成在數(shù)據(jù)庫中，調(diào)用更快；減少了網(wǎng)絡(luò)的交互，有助于提高程序性能。PL/SQL與Transact-SQL（續(xù)）PL/SQL程序的基本結(jié)構(gòu)DECLARE可選部分變量、常量、游標(biāo)、用戶定義異常的聲明BEGIN必要部分SQL語句和PL/SQL語句構(gòu)成的執(zhí)行程序EXCEPTION可選部分程序出現(xiàn)異常時(shí)，捕捉異常并處理異常END；必須部分PL/SQL與Transact-SQL（續(xù)）PL/SQL的變量簡(jiǎn)單

10、變量格式：變量名 constant 變量類型 not null default 值 | :=值v_name varchar2(10); v_age constant number:=20;v_sex char(2) default 男;PL/SQL與Transact-SQL（續(xù)）PL/SQL的語句賦值語句：變量名稱:=表達(dá)式 v_sno:=20090080;條件語句： IF-THEN，IF-THEN-ELSE和嵌套的IF語句 IF (new.Job=講師) AND (new.Sal y print x y else if y z print y z else print z yPL/SQL與T

11、ransact-SQL（續(xù)）分支語句Case when 條件表達(dá)式1 then when 條件表達(dá)式n then else EndPL/SQL與Transact-SQL（續(xù)）循環(huán)語句 while begin break continue endPL/SQL與Transact-SQL（續(xù)）異常處理 Transact-SQL 代碼中的錯(cuò)誤可使用 TRYCATCH 構(gòu)造處理。TRYCATCH 構(gòu)造包括兩部分：一個(gè) TRY 塊和一個(gè) CATCH 塊BEGIN TRYSELECT 1/0;END TRYBEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, E

12、RROR_PROCEDURE() AS ErrorProcedure, ERROR_MESSAGE() AS ErrorMessage;END CATCH;數(shù)據(jù)庫編程三、存儲(chǔ)過程存儲(chǔ)過程存儲(chǔ)過程：由PL/SQL語句書寫的過程，經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫服務(wù)器中，使用時(shí)只要調(diào)用即可。 存儲(chǔ)過程的優(yōu)點(diǎn)：運(yùn)行效率高；降低了客戶機(jī)和服務(wù)器之間的通信量；方便實(shí)施企業(yè)規(guī)則。存儲(chǔ)過程（續(xù)）PL/SQL創(chuàng)建存儲(chǔ)過程Create or replace procedure 過程名 (argument1 in|out|in out type1 , argument2 in|out|in out type2, )IS

13、 | AS Begin Exception end;存儲(chǔ)過程（續(xù)）PL/SQL創(chuàng)建函數(shù)Create or replace function 函數(shù)名 (argment in|out|in out type,) Return return_type IS |AS Begin Exception End;存儲(chǔ)過程（續(xù)）創(chuàng)建PL/SQL存儲(chǔ)過程示例create or replace procedure set_grade(sid sc.sno%type, cid o%type, newgrade sc.grade%type, st out varchar2) isbegin update sc set

14、 grade=newgrade where sno=sid and cno=cid; st:=OK！; commit; exception when no_data_found then st:=數(shù)據(jù)不存在！; when others then st:=操作失?。?end;存儲(chǔ)過程（續(xù)）存儲(chǔ)過程的使用declare mm varchar2(20);begin set_grade(20090001,1001,90,mm); dbms_output.put_line(操作結(jié)果： | mm);end;存儲(chǔ)過程（續(xù)）創(chuàng)建PL/SQL函數(shù)create or replace function get_gr

15、ade(s_no sc.sno%type,c_no o%type)return numberis r_grade number;begin select grade into r_grade from sc where sno=s_no and cno=c_no; if r_grade is null then r_grade:=0; end if; return r_grade; 存儲(chǔ)過程（續(xù)） exception when no_data_found then begin dbms_output.put_line(指定的數(shù)據(jù)不存在！); return -1; end; when other

16、s then begin dbms_output.put_line(sqlcode | - | sqlerrm); return -10; end;end get_grade;存儲(chǔ)過程（續(xù)）PL/SQL的函數(shù)調(diào)用declare vv number;begin vv := shen.get_grade(20090001, 2010); dbms_output.put_line(成績(jī)= | vv);end;存儲(chǔ)過程（續(xù)）創(chuàng)建T-SQL存儲(chǔ)過程create procedure sc_avgsno char(10),avg int outputASbegin select avg=avg(grade)

17、 from sc where sno=sno print(ok ! 學(xué)號(hào)： + sno+ 的數(shù)據(jù)已處理！)end存儲(chǔ)過程（續(xù)）T-SQL函數(shù)的使用declare tmp char(10),avg_out intset tmp=20090001exec sc_avg tmp,avg_out output數(shù)據(jù)庫編程四、事務(wù)的概念與使用事務(wù)的概念與使用事務(wù)(Transaction)的定義一個(gè)數(shù)據(jù)庫操作序列；一個(gè)不可分割的工作單位；恢復(fù)和并發(fā)控制的基本單位。一個(gè)事務(wù)就是將一系列的數(shù)據(jù)操縱SQL語句作為一個(gè)邏輯單元，邏輯單元里面的單個(gè)操作要么全做，要么全部不做，以保證數(shù)據(jù)的完整性。事務(wù)的概念與使用（續(xù)）事務(wù)的ACID特性：原子性（Atomicity）一致性（Consistency）隔離性（Isolation）持續(xù)性（Durability ）事務(wù)的概念與使用（續(xù)）PL/SQL事務(wù)支持的相關(guān)語句 Commit, rollback, savepoint, rollback to savepoint, PL/SQL未提供事務(wù)開始的顯式定義語句事務(wù)的提交與回滾 Commit, rollback基于保存點(diǎn)的事務(wù)回滾savepoint sp1;rollback to sp1;事務(wù)的概念與使用（續(xù)）T-SQL的事務(wù) begin transaction trans_name，c