ORACLE預存程序（四）之遊標

遊遊標的概念:

遊標是SQL的一個記憶體工作區，由系統或使用者以變數的形式定義。遊標的作用就是用於臨時儲存從資料庫中提取的資料區塊。在某些情況下，需要把資料從存放在磁碟的表中調到電腦記憶體中進行處理，最後將處理結果顯示出來或最終寫回資料庫。這樣資料處理的速度才會提高，否則頻繁的磁碟資料交換會降低效率。遊標有兩種類型：顯式遊標和隱式遊標。在前述程式中用到的SELECT...INTO...查詢語句，一次只能從資料庫中提取一行資料，對於這種形式的查詢和DML操作，系統都會使用一個隱式遊標。但是如果要提取多行資料，就要由程式員定義一個顯式遊標，並通過與遊標有關的語句進行處理。顯式遊標對應一個返回結果為多行多列的SELECT語句。遊標一旦開啟，資料就從資料庫中傳送到遊標變數中，然後應用程式再從遊標變數中分解出需要的資料，並進行處理。
  隱式遊標:
如前所述，DML操作和單行SELECT語句會使用隱式遊標，它們是：
* 插入操作：INSERT。
* 更新操作：UPDATE。
* 刪除操作：DELETE。
* 單行查詢操作：SELECT ... INTO ...。
當系統使用一個隱式遊標時，可以通過隱式遊標的屬性來瞭解操作的狀態和結果，進而控製程序的流程。隱式遊標可以使用名字SQL來訪問，但要注意，通過SQL遊標名總是只能訪問前一個DML操作或單行SELECT操作的遊標屬性。所以通常在剛剛執行完操作之後，立即使用SQL遊標名來訪問屬性。遊標的屬性有四種，如下所示。

雖然可以使用前面的形式獲得遊標資料，但是在遊標定義以後使用它的一些屬性來進行結構控制是一種更為靈活的方法。顯式遊標的屬性如下所示。

Sql代碼

1. 遊標的屬性 傳回值類型 意 義
   
2. %ROWCOUNT 整型 獲得FETCH語句返回的資料行數
3. %FOUND 布爾型 最近的FETCH語句返回一行資料則為真，否則為假
4. %NOTFOUND 布爾型 與%FOUND屬性傳回值相反
5. %ISOPEN 布爾型 遊標已經開啟時值為真，否則為假

  begin    update user_info set pwd='liyanbin' where id='u002';    if SQL%FOUND then --SQL%FOUND判斷sql語句是否執行成功       dbms_output.put_line('修改成功，請查看。');       commit;    else       dbms_output.put_line('修改失敗，請查看你的遊標。');    end if;end;

 

顯式遊標
遊標的定義和操作
遊標的使用分成以下4個步驟。
1．聲明遊標
在DECLEAR部分按以下格式聲明遊標：
CURSOR 遊標名[(參數1 資料類型[，參數2 資料類型...])]
IS SELECT語句;
參數是可選部分，所定義的參數可以出現在SELECT語句的WHERE子句中。如果定義了參數，則必須在開啟遊標時傳遞相應的實際參數。
SELECT語句是對錶或視圖的查詢語句，甚至也可以是聯集查詢。可以帶WHERE條件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。在SELECT語句中可以使用在定義遊標之前定義的變數。
2．開啟遊標
在可執行部分，按以下格式開啟遊標：
OPEN 遊標名[(實際參數1[，實際參數2...])];
開啟遊標時，SELECT語句的查詢結果就被傳送到了遊標工作區。
3．提取資料
在可執行部分，按以下格式將遊標工作區中的資料取到變數中。提取操作必須在開啟遊標之後進行。
FETCH 遊標名 INTO 變數名1[，變數名2...];
或
FETCH 遊標名 INTO 記錄變數;
遊標開啟後有一個指標指向資料區，FETCH語句一次返回指標所指的一行資料，要返回多行需重複執行，可以使用迴圈語句來實現。控制迴圈可以通過判斷遊標的屬性來進行。
下面對這兩種格式進行說明：
第一種格式中的變數名是用來從遊標中接收資料的變數，需要事先定義。變數的個數和類型應與SELECT語句中的欄位變數的個數和類型一致。
第二種格式一次將一行資料取到記錄變數中，需要使用%ROWTYPE事先定義記錄變數，這種形式使用起來比較方便，不必分別定義和使用多個變數。
定義記錄變數的方法如下：
變數名 表名|遊標名%ROWTYPE；
其中的表必須存在，遊標名也必須先定義。
4．關閉遊標
CLOSE 遊標名;
顯式遊標開啟後，必須顯式地關閉。遊標一旦關閉，遊標佔用的資源就被釋放，遊標變成無效，必須重新開啟才能使用。
顯式遊標之HelloWorld----資料變數（提取表中id為u002的欄位值）

  declare    name varchar(15);    pwd varchar(15);    CURSOR cursor is         select name,pwd from user_info where id='u002';    begin       open cursor;       fetch cursor into name,pwd;       dbms_output.put_line(name||','||pwd);       close cursor;    end;

顯式遊標之HelloWorld----記錄變數

    declare     CURSOR cursor is         select name,pwd from user_info where id='u002';     record cursor%rowtype;--定義在遊標之後      begin       open cursor;       fetch cursor into record;       dbms_output.put_line(record.name||','||record.pwd);       close cursor;     end;

顯式遊標之HelloWorld----迴圈輸出(在郭靖、喬峰他們之中找出功夫最好的前三人)

    create table EMP    (      NAME   VARCHAR2(10),      SALARY VARCHAR2(10),      TITLE  VARCHAR2(10)    );

 

 declare  name varchar(15);  salary varchar(15);  CURSOR emp_cursor is     select name,salary from emp order by salary desc;  begin    open emp_cursor;    for i IN 1..3 loop        fetch emp_cursor into name,salary;       dbms_output.put_line(name||','||salary);    end loop;    close emp_cursor;  end;

執行結果：風清揚,10000      喬峰,8000      郭靖,6000

顯式遊標之HelloWorld----特殊for迴圈----省略遊標定義（使用特殊for迴圈顯示所有僱員的頭銜和工資）

  declare  CURSOR emp_cursor is     select title,salary from emp order by salary desc;  begin     for emp_record in emp_cursor loop       dbms_output.put_line(emp_record.title||','||emp_record.salary);     end loop;  end;

這個pl/sql看起來比較特殊，沒有申明變數emp_record卻可以直接使用，遊標沒有開啟也沒有關閉更沒有資料的提出，那是如何?輸出的呢，等看完下面這個例子，就會總結。

 begin   for emp in(select title,salary from emp order by salary desc)loop     dbms_output.put_line(emp.title||','||emp.salary);   end loop; end;

顯式遊標屬性前面我們使用fetch來擷取遊標的資料，但是在顯示遊標中有可以使用屬性來靈活的控制結構化組織，下面是常用屬性

1. 遊標的屬性 傳回值類型 意 義
   
2. %ROWCOUNT 整型 獲得FETCH語句返回的資料行數
3. %FOUND 布爾型 最近的FETCH語句返回一行資料則為真，否則為假
4. %NOTFOUND 布爾型 與%FOUND屬性傳回值相反
5. %ISOPEN 布爾型 遊標已經開啟時值為真，否則為假

 declare  name varchar2(10);  salary number;  title varchar2(10);  CURSOR emp_cursor is    select name,salary,title from emp order by salary desc;  begin    open emp_cursor;    if emp_cursor%isopen then      loop        fetch emp_cursor into name,salary,title;        exit when emp_cursor%notfound;        dbms_output.put_line(to_char(emp_cursor%rowcount)||name||'-'||salary||'-'||title);      end loop;    else      dbms_output.put_line('遊標沒開啟！');    end if;    close  emp_cursor;  end;

參數遊標參數遊標之HelloWorld

 

 declare   name varchar2(10);  salary number;  CURSOR emp_cursor(t_age number,t_educational varchar2) is     select name,salary from emp       where age = t_age and educational = t_educational;  begin    open emp_cursor(101,'postdoctor');    loop      fetch emp_cursor into name,salary;      exit when emp_cursor%notfound;      dbms_output.put_line(name||'----'||salary);    end loop;  end;

 輸出：風清揚----10000

動態SELECT語句和動態資料指標的用法Oracle支援動態SELECT語句和動態資料指標，動態方法大大擴充了程式設計的能力。 對於查詢結果為一行的SELECT語句，可以用動態產生查詢語句字串的方法，在程式執行階段臨時地產生並執行，

文法是： execute immediate 查詢語句字串 into 變數1[,變數2...];

 declare   queryStr varchar2(100);  t_name varchar2(10);  begin    queryStr := 'select name from emp where salary>8000';    execute immediate queryStr into t_name;    dbms_output.put_line(t_name);  end;

在變數聲明部分定義的遊標是靜態，不能在程式運行過程中修改。雖然可以通過參數傳遞來取得不同的資料，但還是有很大的局限性。通過採用動態資料指標，可以在程式運行階段隨時產生一個查詢語句作為遊標。要使用動態資料指標需要先定義一個遊標類型，然後聲明一個遊標變數，遊標對應的查詢語句可以在程式的執行過程中動態地說明。

定義遊標類型的語句： TYPE 遊標類型名 REF CURSOR;

聲明遊標變數的語句如下： 遊標變數名 遊標類型名;

在可執行部分可以如下形式開啟一個動態資料指標： OPEN 遊標變數名 FOR 查詢語句字串;

declare     type cur_type is ref cursor;    cur cur_type;    rec scott.emp%rowtype;    str varchar2(50);    letter char:= 'A';   begin          loop                    str:= 'select name from emp where name like ''%'||letter||'%''';            open cur for str;            dbms_output.put_line('包含字母'||letter||'的名字：');             loop            fetch cur into rec.name;            exit when cur%notfound;           dbms_output.put_line(rec.name);   end loop;     exit when letter='Z';     letter:=chr(ascii(letter)+1);    end loop;   end;

總結：