軟件倉(cāng)庫(kù)第章

1、accp v4.0第五章第五章使用 pl/sqlaccp v4.02回顧q同義詞是現(xiàn)有數(shù)據(jù)庫(kù)對(duì)象的別名q序列用于生成唯一、連續(xù)的序號(hào)q視圖是基于一個(gè)或多個(gè)表的虛擬表q索引是與表相關(guān)的一個(gè)可選結(jié)構(gòu)，用于提高 sql 語(yǔ)句執(zhí)行的性能q索引類(lèi)型有標(biāo)準(zhǔn)索引、唯一索引、反向鍵索引、位圖索引和基于函數(shù)的索引q索引組織表基于主鍵訪問(wèn)數(shù)據(jù)accp v4.03目標(biāo)q理解 pl/sql 功能和特點(diǎn)q了解數(shù)據(jù)類(lèi)型及其用法q理解邏輯比較q理解控制結(jié)構(gòu)q掌握錯(cuò)誤處理accp v4.04pl/sql 簡(jiǎn)介qpl/sql 是過(guò)程語(yǔ)言(procedural language)與結(jié)構(gòu)化查詢(xún)語(yǔ)言(sql)結(jié)合而成的編程語(yǔ)言qpl

2、/sql 是對(duì) sql 的擴(kuò)展q支持多種數(shù)據(jù)類(lèi)型，如大對(duì)象和集合類(lèi)型，可使用條件和循環(huán)等控制結(jié)構(gòu)q可用于創(chuàng)建存儲(chǔ)過(guò)程、觸發(fā)器和程序包，給sql語(yǔ)句的執(zhí)行添加程序邏輯q與 oracle 服務(wù)器和 oracle 工具緊密集成，具備可移植性、靈活性和安全性accp v4.05pl/sql 的優(yōu)點(diǎn) 2-1q支持 sql，在 pl/sql 中可以使用：q數(shù)據(jù)操縱命令q事務(wù)控制命令q游標(biāo)控制qsql 函數(shù)和 sql 運(yùn)算符q支持面向?qū)ο缶幊?(oop) q可移植性，可運(yùn)行在任何操作系統(tǒng)和平臺(tái)上的oralce 數(shù)據(jù)庫(kù)q更佳的性能，pl/sql 經(jīng)過(guò)編譯執(zhí)行用戶(hù)將整個(gè)語(yǔ)句塊發(fā)送給 oracleoraclepr

3、ocedurebegin procedurecall sql command endaccp v4.06pl/sql 的優(yōu)點(diǎn) 2-2 pl/sqlsqlq與 sql 緊密集成，簡(jiǎn)化數(shù)據(jù)處理。q支持所有 sql 數(shù)據(jù)類(lèi)型q支持 null 值q支持 %type 和 %rowtype 屬性類(lèi)型q安全性，可以通過(guò)存儲(chǔ)過(guò)程限制用戶(hù)對(duì)數(shù)據(jù)的訪問(wèn)accp v4.07pl/sql 的體系結(jié)構(gòu)qpl/sql 引擎駐留在 oracle 服務(wù)器中q該引擎接受 pl/sql 塊并對(duì)其進(jìn)行編譯執(zhí)行將pl/sql 塊發(fā)送給 oracle 服務(wù)器用戶(hù)用戶(hù)執(zhí)行過(guò)程語(yǔ)句引擎將 sql 語(yǔ)句發(fā)送給sql 語(yǔ)句執(zhí)行器oracle

4、服務(wù)器pl/sql引擎引擎sql 語(yǔ)句執(zhí)行器過(guò)程語(yǔ)句過(guò)程語(yǔ)句執(zhí)行器執(zhí)行器執(zhí)行 sql 語(yǔ)句將結(jié)果發(fā)送給用戶(hù)accp v4.08pl/sql 塊簡(jiǎn)介qpl/sql 塊是構(gòu)成 pl/sql 程序的基本單元q將邏輯上相關(guān)的聲明和語(yǔ)句組合在一起qpl/sql 分為三個(gè)部分，聲明部分、可執(zhí)行部分和異常處理部分 declare declarations begin executable statements exception handlers end;declare qty_on_hand number(5);begin select quantity into qty_on_hand from pro

5、ducts where product = 芭比娃娃 for update of quantity; if qty_on_hand 0 then update products set quantity = quantity + 1 where product = 芭比娃娃; insert into purchase_record values (已購(gòu)買(mǎi)芭比娃娃, sysdate); end if; commit;exception /* 異常處理語(yǔ)句 */ when others then dbms_output.put_line(出錯(cuò):| sqlerrm); end;聲明部分定義變量、游標(biāo)

6、和自定義異常包含 sql 和 pl/sql 語(yǔ)句的可執(zhí)行部分指定出現(xiàn)錯(cuò)誤時(shí)需要執(zhí)行的操作accp v4.09變量和常量 1-2 qpl/sql 塊中可以使用變量和常量q在聲明部分聲明，使用前必須先聲明q聲明時(shí)必須指定數(shù)據(jù)類(lèi)型，每行聲明一個(gè)標(biāo)識(shí)符q在可執(zhí)行部分的 sql 語(yǔ)句和過(guò)程語(yǔ)句中使用q聲明變量和常量的語(yǔ)法：q給變量賦值有兩種方法：q使用賦值語(yǔ)句 :=q使用 select into 語(yǔ)句accp v4.010變量和常量 2-2declare icode varchar2(6); p_catg varchar2(20); p_rate number; c_rate constant numb

7、er := 0.10;begin . icode := i205; select p_category, itemrate * c_rate into p_catg, p_rate from itemfile where itemcode = icode; .end;accp v4.011數(shù)據(jù)類(lèi)型qpl/sql 支持的內(nèi)置數(shù)據(jù)類(lèi)型數(shù)據(jù)類(lèi)型數(shù)據(jù)類(lèi)型lob類(lèi)型標(biāo)量類(lèi)型屬性類(lèi)型%rowtype數(shù)字字符布爾型日期時(shí)間bfileblobclobnclob%type提供某個(gè)變量或數(shù)據(jù)庫(kù)表列的數(shù)據(jù)類(lèi)型提供表示表中一行的記錄類(lèi)型 存儲(chǔ)非結(jié)構(gòu)化數(shù)據(jù)塊accp v4.012數(shù)字?jǐn)?shù)據(jù)類(lèi)型q指定數(shù)值的存儲(chǔ)格式數(shù)字?jǐn)?shù)據(jù)

8、類(lèi)型數(shù)字?jǐn)?shù)據(jù)類(lèi)型numberbinary_integerpls_integernaturalnaturallnpositivepositivendecimalfloatintegerrealsigntype存儲(chǔ)有符號(hào)整數(shù)，所需存儲(chǔ)空間少于number類(lèi)型值存儲(chǔ)整數(shù)、實(shí)數(shù)和浮點(diǎn)數(shù)存儲(chǔ)有符號(hào)整數(shù)，可使算術(shù)計(jì)算快速而有效accp v4.013字符數(shù)據(jù)類(lèi)型q字符數(shù)據(jù)類(lèi)型包括：qcharqvarchar2qlongqrawqlong rawqpl/sql 的數(shù)據(jù)類(lèi)型與 sql數(shù)據(jù)類(lèi)型的比較數(shù)據(jù)類(lèi)型sql類(lèi)型pl/sql類(lèi)型char1.20001.32767long1.2gb1.32760long raw

9、1.2gb1.32760raw1.20001.32767varchar21.40001.32767accp v4.014日期時(shí)間和布爾數(shù)據(jù)類(lèi)型q日期時(shí)間類(lèi)型q存儲(chǔ)日期和時(shí)間數(shù)據(jù)q常用的兩種日期時(shí)間類(lèi)型qdateqtimestampq布爾數(shù)據(jù)類(lèi)型q此類(lèi)別只有一種類(lèi)型，即boolean類(lèi)型q用于存儲(chǔ)邏輯值(true、false和null)q不能向數(shù)據(jù)庫(kù)中插入boolean數(shù)據(jù)q不能將列值保存到boolean變量中q只能對(duì)boolean變量執(zhí)行邏輯操作accp v4.015lob 數(shù)據(jù)類(lèi)型 2-1q用于存儲(chǔ)大文本、圖像、視頻剪輯和聲音剪輯等非結(jié)構(gòu)化數(shù)據(jù)。qlob 數(shù)據(jù)類(lèi)型可存儲(chǔ)最大 4gb的數(shù)據(jù)。q

10、lob 類(lèi)型包括：qblob 將大型二進(jìn)制對(duì)象存儲(chǔ)在數(shù)據(jù)庫(kù)中qclob 將大型字符數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)庫(kù)中qnclob 存儲(chǔ)大型unicode字符數(shù)據(jù)qbfile 將大型二進(jìn)制對(duì)象存儲(chǔ)在操作系統(tǒng)文件中accp v4.016lob 數(shù)據(jù)類(lèi)型 2-2qlob 類(lèi)型的數(shù)據(jù)庫(kù)列僅存儲(chǔ)定位符，該定位符指向大型對(duì)象的存儲(chǔ)位置qdbms_lob程序包用于操縱 lob 數(shù)據(jù)set serveroutput ondeclare clob_var clob; amount integer; offset integer; output_var varchar2(100);begin select chapter_tex

11、t into clob_var from my_book_text where chapter_id=5; amount := 24; - 要讀取的字符數(shù) offset := 1; - 起始位置 dbms_lob.read(clob_var,amount,offset,output_var); dbms_output.put_line(output_var);end;/從表中選擇 clob 定位符到 clob_var變量中從clob數(shù)據(jù)中讀取24個(gè)字符存儲(chǔ)到 output_var 變量中顯示讀到的信息accp v4.017屬性類(lèi)型q用于引用數(shù)據(jù)庫(kù)列的數(shù)據(jù)類(lèi)型，以及表示表中一行的記錄類(lèi)型q屬性類(lèi)

12、型有兩種：q%type - 引用變量和數(shù)據(jù)庫(kù)列的數(shù)據(jù)類(lèi)型q%rowtype - 提供表示表中一行的記錄類(lèi)型q使用屬性類(lèi)型的優(yōu)點(diǎn)：q不需要知道被引用的表列的具體類(lèi)型q如果被引用對(duì)象的數(shù)據(jù)類(lèi)型發(fā)生改變，pl/sql 變量的數(shù)據(jù)類(lèi)型也隨之改變icode itemfile.itemcode%type;emp_rec scott.emp%rowtype;accp v4.018q布爾表達(dá)式的結(jié)果為true、false或null，通常由邏輯運(yùn)算符and、or和not連接q布爾表達(dá)式有三種類(lèi)型：q數(shù)字布爾型q字符布爾型q日期布爾型邏輯比較q邏輯比較用于比較變量和常量的值，這些表達(dá)式稱(chēng)為布爾表達(dá)式q布爾表達(dá)式由

13、關(guān)系運(yùn)算符與變量或常量組成關(guān)系運(yùn)算符關(guān)系運(yùn)算符說(shuō)明說(shuō)明=比較兩個(gè)變量是否相等，如果值相當(dāng)，則返回比較兩個(gè)變量是否相等，如果值相當(dāng)，則返回 true, !=比較兩個(gè)變量，如果不相等，則返回比較兩個(gè)變量，如果不相等，則返回 true比較兩個(gè)變量，檢查值比較兩個(gè)變量，檢查值 1 是否大于是否大于 值值 2=比較兩個(gè)變量，檢查變量比較兩個(gè)變量，檢查變量 1 是否大于等于變量是否大于等于變量 2accp v4.019控制結(jié)構(gòu)pl/sql 支持的流程控制結(jié)構(gòu)：q條件控制qif 語(yǔ)句qcase 語(yǔ)句q循環(huán)控制qloop 循環(huán)qwhile 循環(huán)qfor 循環(huán)q順序控制qgoto 語(yǔ)句qnull 語(yǔ)句accp

14、v4.020條件控制 2-1qif 語(yǔ)句根據(jù)條件執(zhí)行一系列語(yǔ)句，有三種形式：if-then、if-then-else 和 if-then-elsifdeclare icode varchar2(4); irate number;begin icode := i203; select itemrate into irate from itemfile where itemcode = icode; if irate 200 then update itemfile set itemrate = itemrate - 200 where itemcode = icode; else update

15、itemfile set itemrate = itemrate - 50 where itemcode = icode; end if; dbms_output.put_line(itemrate=| irate);end;accp v4.021條件控制 2-2begin case &grade when a then dbms_output.put_line(優(yōu)異優(yōu)異); when b then dbms_output.put_line (優(yōu)秀優(yōu)秀); when c then dbms_output.put_line (良好良好); when d then dbms_output.

16、put_line (一般一般); when f then dbms_output.put_line (較差較差); else dbms_output.put_line (沒(méi)有此成績(jī)沒(méi)有此成績(jī)); end case;end;qcase 語(yǔ)句用于根據(jù)單個(gè)變量或表達(dá)式與多個(gè)值進(jìn)行比較q執(zhí)行 case 語(yǔ)句前，先計(jì)算選擇器的值accp v4.022循環(huán)控制q循環(huán)控制用于重復(fù)執(zhí)行一系列語(yǔ)句q循環(huán)控制語(yǔ)句包括：qloop、exit 和 exit whenq循環(huán)控制的三種類(lèi)型：qloop - 無(wú)條件循環(huán)qwhile - 根據(jù)條件循環(huán)qfor - 循環(huán)固定的次數(shù)loop sequence_of_stateme

17、ntsend loop;while condition loop sequence_of_statementsend loop;for counter in reverse value1.value2loop sequence_of_statementsend loop;accp v4.023順序控制q順序控制用于按順序執(zhí)行語(yǔ)句q順序控制語(yǔ)句包括：qgoto 語(yǔ)句 - 無(wú)條件地轉(zhuǎn)到標(biāo)簽指定的語(yǔ)句qnull 語(yǔ)句 - 什么也不做的空語(yǔ)句declare qtyhand itemfile.qty_hand%type; relevel itemfile.re_level%type;begin sele

18、ct qty_hand,re_level into qtyhand,relevel from itemfile where itemcode = i201; if qtyhand relevel then goto updation; else goto quit; end if; update itemfile set qty_hand = qty_hand + re_level where itemcode = i201; null;end;accp v4.024動(dòng)態(tài) sqlq動(dòng)態(tài) sql 是指在pl/sql程序執(zhí)行時(shí)生成的 sql 語(yǔ)句q編譯程序?qū)?dòng)態(tài) sql 不做處理，而是在程序運(yùn)行時(shí)

19、動(dòng)態(tài)構(gòu)造語(yǔ)句、對(duì)語(yǔ)句進(jìn)行語(yǔ)法分析并執(zhí)行qddl 語(yǔ)句命令和會(huì)話(huà)控制語(yǔ)句不能在 pl/sql 中直接使用，但是可以通過(guò)動(dòng)態(tài) sql 來(lái)執(zhí)行q執(zhí)行動(dòng)態(tài) sql 的語(yǔ)法： execute immediate dynamic_sql_string into define_variable_list using bind_argument_list;declare sql_stmt varchar2(200); emp_id number(4) := 7566; emp_rec emp%rowtype;begin execute immediate create table bonus1 (id num

20、ber, amt number); sql_stmt := select * from emp where empno = :id; execute immediate sql_stmt into emp_rec using emp_id;end;accp v4.025錯(cuò)誤處理 2-1q在運(yùn)行程序時(shí)出現(xiàn)的錯(cuò)誤叫做異常q發(fā)生異常后，語(yǔ)句將停止執(zhí)行，控制權(quán)轉(zhuǎn)移到 pl/sql 塊的異常處理部分q異常有兩種類(lèi)型：q預(yù)定義異常 - 當(dāng) pl/sql 程序違反 oracle 規(guī)則或超越系統(tǒng)限制時(shí)隱式引發(fā)q用戶(hù)定義異常 - 用戶(hù)可以在 pl/sql 塊的聲明部分定義異常，自定義的異常通過(guò) raise 語(yǔ)句

21、顯式引發(fā)accp v4.026錯(cuò)誤處理 2-2q處理預(yù)定義異常q處理用戶(hù)定義異常declare ordernum varchar2(5);begin select orderno into ordernum from order_master;exception when too_many_rows then dbms_output.put_line (返回多行);end;declare invalidcategory exception; category varchar2(10);begin category := &category; if category not in (附件,頂蓋,備件) then raise invalidcategory; else dbms_output.put_line(您輸入的類(lèi)別是| category); end if;exception when invalidcategory then dbms_output