數(shù)據(jù)庫(kù)講義2游標(biāo)、異常(課堂版)

1、1.游標(biāo)游標(biāo)提供了一種從表中檢索數(shù)據(jù)并進(jìn)行操作的靈活手段。PL/SQL在執(zhí)行SELECT、INSERT、DELETE和UPDATE語(yǔ)句時(shí)，ORACLE會(huì)在內(nèi)存中為其分配上下文區(qū)（Context Area），即緩沖區(qū)。游標(biāo)是指向該區(qū)的一個(gè)指針，通過(guò)游標(biāo)PL/SQL程序可以一次處理查詢結(jié)果集中的一行，并可以對(duì)該行數(shù)據(jù)執(zhí)行特定操作，從而為用戶在處理數(shù)據(jù)的過(guò)程中提供方便。在oracle中，主要使用顯式游標(biāo)，隱式游標(biāo)，動(dòng)態(tài)游標(biāo)（以后講存儲(chǔ)過(guò)程實(shí)現(xiàn)分頁(yè)的時(shí)候）。2. 顯式(示)游標(biāo)顯式游標(biāo)是由用戶聲明和操作的一種游標(biāo)，通常用于操作查詢結(jié)果集（即由select語(yǔ)句返回的查詢結(jié)果），使用它處理數(shù)據(jù)的步驟為：聲

2、明游標(biāo)、打開(kāi)游標(biāo)、讀取游標(biāo)和關(guān)閉游標(biāo)4個(gè)步驟。其中，讀取游標(biāo)很可能是一個(gè)反復(fù)的步驟(迭代)，因?yàn)橛螛?biāo)每次只能讀取一行數(shù)據(jù)，對(duì)于多條記錄，需要反復(fù)讀取，直到游標(biāo)讀取不到數(shù)據(jù)為止。2-1 游標(biāo)使用的步驟a. 聲明游標(biāo)定義游標(biāo)名字，以及對(duì)應(yīng)的select語(yǔ)句。 CURSOR cursor_name(input_parameter1, input_parameter2) RETURN ret_type IS select_statement;cursor_name: 所聲明游標(biāo)名稱ret_name： 執(zhí)行游標(biāo)操作后的返回值類(lèi)型，這是一個(gè)可選項(xiàng)。select_statement: 游標(biāo)所使用的selec

3、t語(yǔ)句，為游標(biāo)的反復(fù)讀取提供了結(jié)果集。input_parameter1: 游標(biāo)的“輸入?yún)?shù)”，可以有多個(gè)，這是可選項(xiàng)。它指定用戶在打開(kāi)游標(biāo)后，向游標(biāo)中傳遞的值，該參數(shù)的定義和初始化格式如下： para_name in datatype := | default para_value 其中，para_name表示參數(shù)名稱，其后面的關(guān)鍵字in表示輸入方向，可以省略；datatype表示參數(shù)的數(shù)據(jù)類(lèi)型，但數(shù)據(jù)不可以指定長(zhǎng)度； para_value表示該參數(shù)的初始值或默認(rèn)值，也可以是表達(dá)式； para_name參數(shù)的初始值可以用常規(guī)方式賦值（:=）,也可以使用關(guān)鍵字default。示例代碼：聲明一個(gè)游

4、標(biāo)，用來(lái)讀取emp表中職務(wù)是銷(xiāo)售員(SALESMEN)的雇員信息。declare cursor cur_emp(var_job in varchar2:=SALESMEN) is select empno, ename, sal from emp where job=var_job;b. 打開(kāi)游標(biāo)在游標(biāo)聲明完畢之后，必須打開(kāi)才能使用，打開(kāi)游標(biāo)的語(yǔ)法：open cur_name(para_values1,para_values2.);cur_name: 打開(kāi)游標(biāo)的名字para_values1: 指定“輸入?yún)?shù)”的值，根據(jù)聲明游標(biāo)時(shí)的實(shí)際情況，可以是多個(gè)或一個(gè)，這是可選項(xiàng)。示例代碼：open cu

5、r_emp(MANAGER);c. 讀取游標(biāo)fetch cur_name into variable;variable: 表明一個(gè)變量列表或者“記錄”變量(RECORD類(lèi)型)，oracle使用“記錄”變量來(lái)存儲(chǔ)游標(biāo)中的數(shù)據(jù)。在游標(biāo)中包含了一個(gè)數(shù)據(jù)行指針，其用來(lái)指向當(dāng)前數(shù)據(jù)行。剛剛打開(kāi)游標(biāo)時(shí)，指針指向結(jié)果集中的第一行，當(dāng)使用fetch-into語(yǔ)句讀取數(shù)據(jù)完畢后，游標(biāo)中的指針自動(dòng)指向下一行數(shù)據(jù)，直到指針指向結(jié)果集中最后一條記錄之后為止(當(dāng)最后一條記錄為空時(shí)，表示讀取完畢），這是游標(biāo)的%found屬性值為false.4. 關(guān)閉游標(biāo)游標(biāo)使用完畢后，需要關(guān)閉，以釋放資源。close cur_name;

6、整個(gè)游標(biāo)操作的示例代碼：declare cursor cur_emp (var_job varchar2 := SALESMAN) is select empno, ename, sal from emp where job = var_job; type record_emp is record (var_empno emp.empno%type, var_ename emp.ename%type, var_sal emp.sal%type); emp_row record_emp;begin open cur_emp(MANAGER); fetch cur_emp into emp_row

7、; while cur_emp%found loop dbms_output.put_line(emp_row.var_ename|的編號(hào)|emp_row.var_empno|,的工資|emp_row.var_sal); fetch cur_emp into emp_row; end loop; close cur_emp;end;/2-2 游標(biāo)的屬性游標(biāo)有4個(gè)屬性：%found: 布爾型屬性，如果SQL語(yǔ)句至少影響到一行的數(shù)據(jù)，該屬性為true, 否則為false%notfound:布爾型屬性，與%found相反。%isopen: 布爾型屬性，游標(biāo)是否打開(kāi)%rowcount: 數(shù)字型屬性，返

8、回受SQL語(yǔ)句影響的行數(shù)使用指南，聲明一個(gè)游標(biāo)，檢索指定員工編號(hào)的雇員信息，然后使用游標(biāo)的%found屬性來(lái)判斷是否檢索到指定員工編號(hào)的雇員信息。declare var_name varchar2(50); var_job varchar2(50); /*聲明游標(biāo)*/ cursor cur_emp is select ename, job form emp where empno=7499;begin open cur_emp; fetch cur_emp into var_name, var_job; if cur_emp%found then dbms_output.put_line(編號(hào)

9、是7499|var_name); else dbms_output.put_line(查無(wú)此人); endif;end;/3. 隱式游標(biāo)在執(zhí)行一個(gè)SQL語(yǔ)句時(shí)，oracle會(huì)自動(dòng)創(chuàng)建一個(gè)隱式游標(biāo)。這個(gè)游標(biāo)是內(nèi)存中處理該語(yǔ)句的工作區(qū)域。隱式游標(biāo)主要處理數(shù)據(jù)操縱語(yǔ)句（如update, delete語(yǔ)句）的執(zhí)行結(jié)果，當(dāng)然，特殊情況下，也可以處理select語(yǔ)句的查詢結(jié)果。隱式游標(biāo)，也有屬性。當(dāng)使用隱式游標(biāo)的屬性時(shí)，需要在屬性前面加上隱式游標(biāo)的默認(rèn)名稱-SQL.示例代碼：把emp表中銷(xiāo)售員（SALESMAN）的工資上調(diào)20%,使用隱式游標(biāo)的屬性輸出上調(diào)工資的員工數(shù)量。begin update emp

10、set sal=sal*(1+0.2) where job=SALESMAN; if sql%notfound then dbms_output.put_line(沒(méi)有雇員上調(diào)工資); else dbms_output.put_line(有|sql%rowcount|個(gè)雇員上調(diào)工資); end if;end;/4. 通過(guò)for語(yǔ)句循環(huán)遍歷游標(biāo)使用for語(yǔ)句遍歷游標(biāo)中的數(shù)據(jù)時(shí)，可以把其計(jì)數(shù)器看做一個(gè)自動(dòng)的RECORD類(lèi)型變量。4-1 遍歷顯式游標(biāo)for var_auto_record in cur_name loop plsqlsentence;end loop; var_auto_record

11、: 自動(dòng)的RECORD類(lèi)型的變量，可以是任意合法的變量名稱。 cur_name: 指定游標(biāo)的名稱 plsqlsentence: PL/SQL語(yǔ)句示例代碼：使用顯示游標(biāo)和for語(yǔ)句檢索部門(mén)編號(hào)是30的雇員信息并輸出：declare cursor cur_emp is select * from emp where deptno=30;begin for emp_record in cur_emp loop dbms_output.put(emp_record.empno); dbms_output.put(emp_record.ename); dbms_output.put(emp_record

12、.job); end loop;end;4-2 使用隱式游標(biāo)和for語(yǔ)句begin for emp_record in (select empno, ename, sal from emp where job=SALESMAN) loop dbms_output.put(emp_record.empno); dbms_output.put(emp_record.ename); dbms_output.put_line(emp_record.sal); end loop;end;/ 5. 異常理解成java trycatch-.在PL/SQL程序中，有exception塊的定義。declareb

13、eginexceptionend;/在編寫(xiě)PL/SQL程序時(shí)，避免不了發(fā)生一些錯(cuò)誤，可能是程序員自己造成，也可能是操作系統(tǒng)或者硬件環(huán)境錯(cuò)誤，比如除數(shù)為零，磁盤(pán)I/O錯(cuò)誤等，oracle采用異常機(jī)制來(lái)處理，異常處理代碼通常放在PL/SQL的exception代碼塊中。根據(jù)異常產(chǎn)生的機(jī)制和原理，oracle異常分為兩大類(lèi)： 預(yù)定義異常：oracle系統(tǒng)自身為用戶提供大量的、可在PL/SQL中使用的預(yù)定義異常，以便檢查用戶代碼失敗的一般原因。這種異常，用戶無(wú)序搭理，由oracle自動(dòng)引發(fā)。 自定義異常：出現(xiàn)操作系統(tǒng)錯(cuò)誤，機(jī)器硬件損壞等，以及業(yè)務(wù)實(shí)際需求，程序設(shè)計(jì)人員需要自定義一些錯(cuò)誤的業(yè)務(wù)邏輯，而P

14、L/SQL程序在運(yùn)行過(guò)程中，可能觸發(fā)這些錯(cuò)誤的業(yè)務(wù)邏輯，對(duì)于上述異常，需要在程序中自定義異常，然后由oracle自動(dòng)觸發(fā)。 5-1 預(yù)定義異常 當(dāng)PL/SQL程序返回了oracle系統(tǒng)內(nèi)部規(guī)定的設(shè)計(jì)規(guī)范，會(huì)自動(dòng)引發(fā)一個(gè)預(yù)定義異常，詳見(jiàn)表格： 預(yù)定義說(shuō)明的部分 ORACLE 異常錯(cuò)誤錯(cuò)誤號(hào)異常錯(cuò)誤信息名稱說(shuō)明ORA-0001Dup_val_on_index違反了唯一性限制ORA-0051Timeout-on-resource在等待資源時(shí)發(fā)生超時(shí)ORA-0061Transaction-backed-out由于發(fā)生死鎖事務(wù)被撤消ORA-1001Invalid-CURSOR試圖使用一個(gè)無(wú)效的游標(biāo)ORA

15、-1012Not-logged-on沒(méi)有連接到ORACLEORA-1017Login-denied無(wú)效的用戶名/口令ORA-1403No_data_foundSELECT INTO沒(méi)有找到數(shù)據(jù)ORA-1422Too_many_rowsSELECT INTO 返回多行ORA-1476Zero-divide試圖被零除ORA-1722Invalid-NUMBER轉(zhuǎn)換一個(gè)數(shù)字失敗ORA-6500Storage-error內(nèi)存不夠引發(fā)的內(nèi)部錯(cuò)誤ORA-6501Program-error內(nèi)部錯(cuò)誤ORA-6502Value-error轉(zhuǎn)換或截?cái)噱e(cuò)誤ORA-6504Rowtype-mismatch宿主游標(biāo)變量

16、與 PL/SQL變量有不兼容行類(lèi)型ORA-6511CURSOR-already-OPEN試圖打開(kāi)一個(gè)已處于打開(kāi)狀態(tài)的游標(biāo)ORA-6530Access-INTO-null試圖為null 對(duì)象的屬性賦值ORA-6531Collection-is-null試圖將Exists 以外的集合( collection)方法應(yīng)用于一個(gè)null pl/sql 表上或varray上ORA-6532Subscript-outside-limit對(duì)嵌套或varray索引得引用超出聲明范圍以外ORA-6533Subscript-beyond-count對(duì)嵌套或varray 索引得引用大于集合中元素的個(gè)數(shù).示例代碼：使用

17、select into語(yǔ)句檢索emp表中部門(mén)編號(hào)為10的雇員記錄信息，然后使用too_many_rows預(yù)定義異常捕獲錯(cuò)誤信息并輸出，代碼如下：declare var_empno number; var_ename varchar2(50);begin select empno, ename into var_empno, var_ename from emp where deptno=10; if sql%found then dbms_output.put_line(var_empno| |var_ename); end if;exception when too_many_rows th

18、en dbms_output.put_line(返回記錄超過(guò)一行); when no_data_found then dbms_output.put_line(無(wú)數(shù)據(jù)記錄); -處理所有的錯(cuò)誤 when others then dbms_output.put_line(SQLCODE|-|SQLERRM);end;/注意：oracle系統(tǒng)內(nèi)部規(guī)定不允許select into語(yǔ)句的返回行數(shù)超過(guò)一行，所以，必然會(huì)引發(fā)異常，即引發(fā)too_many_rows系統(tǒng)預(yù)定義異常。5-2 自定義異常oracle系統(tǒng)內(nèi)部的預(yù)定義異常僅僅20多個(gè)，而實(shí)際程序運(yùn)行過(guò)程中可能產(chǎn)生幾千個(gè)異常情況，為此oracle經(jīng)常使

19、用錯(cuò)誤編號(hào)和相關(guān)描述輸出異常。另外，程序員可以根據(jù)實(shí)際的業(yè)務(wù)需求定義一些特殊的異常，這樣oracle的自定義異常分為：錯(cuò)誤編號(hào)異常和業(yè)務(wù)邏輯異常兩種。5-2-1 錯(cuò)誤編號(hào)異常錯(cuò)誤編號(hào)異常指的是：oracle系統(tǒng)發(fā)生錯(cuò)誤時(shí)，系統(tǒng)會(huì)顯示錯(cuò)誤編號(hào)和相關(guān)的異常信息。對(duì)于這種異常，首先在聲明部分使用exception類(lèi)型定義一個(gè)異常變量名，然后使用語(yǔ)句pragme exception_init為“錯(cuò)誤編號(hào)”關(guān)聯(lián)這個(gè)異常變量名，接下來(lái)可以像對(duì)待系統(tǒng)預(yù)定義異常一樣來(lái)處理。實(shí)際演示：向dept表中插入一個(gè)部門(mén)編號(hào)為10的記錄（事先已知，10已經(jīng)存在，并且dept為唯一主鍵），執(zhí)行insert操作，會(huì)顯示-00

20、01的錯(cuò)誤編號(hào)。定義錯(cuò)誤編號(hào)為-00001的異常變量，然后向dept表中插入一條“違反唯一約束條件”的記錄，最后在exception代碼塊輸出異常提示信息。declare v_exception_key exception; -定義一個(gè)異常變量 pragma exception_init(v_exception_key,-00001); -關(guān)聯(lián)錯(cuò)誤號(hào)和異常變量名begin insert into dept values(1,); exception when v_exception_key then dbms_output.put_line(違反主鍵約束); - when NO_DATA_FOUND then - dbms_output.put_line(沒(méi)有數(shù)據(jù)); -處理所有的錯(cuò)誤 when others then dbms_output.put_line(SQLCODE|-|SQLERRM); end;5-2-2 業(yè)務(wù)邏輯異常符合數(shù)據(jù)庫(kù)的規(guī)范，但是，不符合現(xiàn)實(shí)邏輯。 在實(shí)際的應(yīng)用中，程序開(kāi)放人員可以根據(jù)具體的業(yè)務(wù)邏輯規(guī)則自定義一個(gè)異常。這樣，當(dāng)用戶違反業(yè)務(wù)邏輯規(guī)則時(shí)，