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

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

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

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

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

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

6、整個游標(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|的編號|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個屬性：%found: 布爾型屬性，如果SQL語句至少影響到一行的數(shù)據(jù)，該屬性為true, 否則為false%notfound:布爾型屬性，與%found相反。%isopen: 布爾型屬性，游標(biāo)是否打開%rowcount: 數(shù)字型屬性，返

8、回受SQL語句影響的行數(shù)使用指南，聲明一個游標(biāo)，檢索指定員工編號的雇員信息，然后使用游標(biāo)的%found屬性來判斷是否檢索到指定員工編號的雇員信息。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(編號

9、是7499|var_name); else dbms_output.put_line(查無此人); endif;end;/3. 隱式游標(biāo)在執(zhí)行一個SQL語句時，oracle會自動創(chuàng)建一個隱式游標(biāo)。這個游標(biāo)是內(nèi)存中處理該語句的工作區(qū)域。隱式游標(biāo)主要處理數(shù)據(jù)操縱語句（如update, delete語句）的執(zhí)行結(jié)果，當(dāng)然，特殊情況下，也可以處理select語句的查詢結(jié)果。隱式游標(biāo)，也有屬性。當(dāng)使用隱式游標(biāo)的屬性時，需要在屬性前面加上隱式游標(biāo)的默認(rèn)名稱-SQL.示例代碼：把emp表中銷售員（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(沒有雇員上調(diào)工資); else dbms_output.put_line(有|sql%rowcount|個雇員上調(diào)工資); end if;end;/4. 通過for語句循環(huán)遍歷游標(biāo)使用for語句遍歷游標(biāo)中的數(shù)據(jù)時，可以把其計數(shù)器看做一個自動的RECORD類型變量。4-1 遍歷顯式游標(biāo)for var_auto_record in cur_name loop plsqlsentence;end loop; var_auto_record

11、: 自動的RECORD類型的變量，可以是任意合法的變量名稱。 cur_name: 指定游標(biāo)的名稱 plsqlsentence: PL/SQL語句示例代碼：使用顯示游標(biāo)和for語句檢索部門編號是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語句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;/在編寫PL/SQL程序時，避免不了發(fā)生一些錯誤，可能是程序員自己造成，也可能是操作系統(tǒng)或者硬件環(huán)境錯誤，比如除數(shù)為零，磁盤I/O錯誤等，oracle采用異常機(jī)制來處理，異常處理代碼通常放在PL/SQL的exception代碼塊中。根據(jù)異常產(chǎn)生的機(jī)制和原理，oracle異常分為兩大類： 預(yù)定義異常：oracle系統(tǒng)自身為用戶提供大量的、可在PL/SQL中使用的預(yù)定義異常，以便檢查用戶代碼失敗的一般原因。這種異常，用戶無序搭理，由oracle自動引發(fā)。 自定義異常：出現(xiàn)操作系統(tǒng)錯誤，機(jī)器硬件損壞等，以及業(yè)務(wù)實際需求，程序設(shè)計人員需要自定義一些錯誤的業(yè)務(wù)邏輯，而P

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

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

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

17、select into語句檢索emp表中部門編號為10的雇員記錄信息，然后使用too_many_rows預(yù)定義異常捕獲錯誤信息并輸出，代碼如下：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(返回記錄超過一行); when no_data_found then dbms_output.put_line(無數(shù)據(jù)記錄); -處理所有的錯誤 when others then dbms_output.put_line(SQLCODE|-|SQLERRM);end;/注意：oracle系統(tǒng)內(nèi)部規(guī)定不允許select into語句的返回行數(shù)超過一行，所以，必然會引發(fā)異常，即引發(fā)too_many_rows系統(tǒng)預(yù)定義異常。5-2 自定義異常oracle系統(tǒng)內(nèi)部的預(yù)定義異常僅僅20多個，而實際程序運(yùn)行過程中可能產(chǎn)生幾千個異常情況，為此oracle經(jīng)常使

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

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