oracle遊標（一）

標籤：style   blog   http   color   os   使用   ar   strong   for   

1.遊標概念

 在PL/SQL塊中執行SELECT、INSERT、DELETE和UPDATE語句時，ORACLE會在記憶體中為其分配上下文區（Context Area），即緩衝區。遊標是指向該區的一個指標，或是一種結構化資料類型。它提供了一種對具有多行資料查詢結果集中的每一行資料分別進行單獨處理的方法，是設計嵌入式SQL語句的應用程式的常用編程方式。

 

2.遊標使用

遊標分為兩種；

　　1，隱式遊標

　　2，顯示遊標,

 

A，隱式遊標

隱式遊標是當執行sql語句時自動建立的，當沒有聲明顯示遊標時，程式是無法控制隱式遊標的。

當執行DML語句（insert,update 和 delete）時，隱式遊標就會自動建立，對於insert操作，遊標控制插入的資料，對於delete,update,遊標會記錄影響的行數

注意：任何遊標都能訪問 sql%attribute_name .

 

遊標屬性

 

屬性	描述
%FOUND	如果insert,update,delete 執行影響了行數，或者select into 有返回的行數，意思就是有記錄被更新，則返回true,否則返回false.
%NOTFOUND	跟上面相反，沒有記錄被更新則返回true,否則返回false.
%ISOPEN	對於隱式遊標總是返回false,因為oracle在執行相關的sql語句後會自動關閉。 對於顯示遊標，如果被開啟了，就返回true,否則返回false.
%ROWCOUNT	返回受影響的行數.當執行Insert,update 或delete語句時，或者是select into 時

隱式遊標屬性

屬性	值	SELECT	INSERT	UPDATE	DELETE
SQL%ISOPEN		FALSE	FALSE	FALSE	FALSE
SQL%FOUND	TRUE	有結果		成功	成功
SQL%FOUND	FALSE	沒結果		失敗	失敗
SQL%NOTFUOND	TRUE	沒結果		失敗	失敗
SQL%NOTFOUND	FALSE	有結果		成功	失敗
SQL%ROWCOUNT		返回行數，只為1	插入的行數	修改的行數	刪除的行數

 

看例子的前提還是先準備資料

 

drop table customer;create table customer(name varchar(100) primary key,age int,address varchar(100),salary decimal)delete from customer;insert into customer values (‘jack‘,22,‘Singapore‘,5000.00);insert into customer values (‘rose‘,22,‘Japan‘,2000.00);insert into customer values (‘Jet‘,32,‘HongKong‘,7000.00);insert into customer values (‘John‘,30,‘American‘,5000.00);insert into customer values (‘Merry‘,25,‘Singapore‘,3000.00);insert into customer values (‘Peter‘,26,‘China‘,1000.00);insert into customer values (‘Adi‘,27,‘India‘,2400.00);select * from customerselect * from customer where salary <3000

View Code

 

例1；

例1，set serveroutput on;DECLARE    total_rows number(2);BEGIN   UPDATE customer   SET salary = salary + 500   where salary < 3000;   IF sql%notfound THEN      dbms_output.put_line(‘no customers selected‘);   ELSIF sql%found THEN      total_rows := sql%rowcount;      dbms_output.put_line( total_rows || ‘ customers selected ‘);   END IF; END;/

View Code

 

結果：

3 customers selected

查看資料會發現，資料庫更新了3條記錄。

大多數時候我們還是使用的顯示遊標，這樣更加容易去控制。

 

B，顯示遊標

 

遊標的聲明：

    CURSOR cursor_name[(parameter[, parameter]…)]               [RETURN datatype]    IS         select_statement;

 

 

實用顯示遊標會有以下4個步驟;

 

• 聲明遊標初始化記憶體
• 開啟遊標分配記憶體
• Fetch cursor 擷取記錄
• 關閉遊標釋放記憶體

 

1. 聲明遊標

CURSOR c1 is select name, salary from customer;

    2，開啟遊標分配記憶體

OPEN c1;    

    3，擷取記錄

FETCH c1 INTO c_name, c_salary;

　4，關閉遊標

CLOSE c_1;

 

下面看一個簡單使用顯示遊標的完整例子

 

例2;

 

set serveroutput on;DECLARE    c_salary customer.salary%TYPE;   c_name customer.name%TYPE;   CURSOR c1 is   select name,salary from customer;BEGIN  open c1;  loop  fetch c1 into c_name,c_salary;  exit when c1%notfound ;   dbms_output.put_line(c_name||‘ ‘||c_salary);  end loop;  close c1;END;/

View Code

 

結果；

jack 5000rose 2500Jet 7000John 5000Merry 3000Peter 1500Adi 2900

View Code

 

 

PL/SQL語言提供了遊標FOR迴圈語句，自動執行遊標的OPEN、FETCH、CLOSE語句和迴圈語句的功能；當進入迴圈時，遊標FOR迴圈語句自動開啟遊標，並提取第一行遊標資料，當程式處理完當前所提取的資料而進入下一次迴圈時，遊標FOR迴圈語句自動提取下一行資料供程式處理，當提取完結果集合中的所有資料行後結束迴圈，並自動關閉遊標。

 

  FOR index_variable IN cursor_name[(value[, value]…)] LOOP    -- 遊標資料處理代碼  END LOOP;

 

用for loop,可以完成上面例2同樣的效果。

例3;

　　

set serveroutput on;DECLARE    c_salary customer.salary%TYPE;   c_name customer.name%TYPE;   CURSOR c1 is   select name,salary from customer;BEGIN for f in c1  loop   dbms_output.put_line(f.name||‘ ‘||f.salary);  end loop;END;/

View Code

 

帶參數的遊標

set serveroutput on;DECLARE    c_salary customer.salary%TYPE;   c_name customer.name%TYPE;   CURSOR c1(p_salary decimal default 3000) is   select name,salary from customer where salary >= p_salary;BEGIN  --open c1(p_salary=>5000);  open c1(5000);   loop  fetch c1 into c_name,c_salary;  exit when c1%notfound ;   dbms_output.put_line(c_name||‘ ‘||c_salary);  end loop;  close c1;END;

View Code

 

這篇就先看到基礎的東西，遊標知識還是比較多，後面再更新。　

這篇文檔比較深入，供參考

http://www.codeproject.com/Articles/580628/OracleplusPL-fSQLplusCursor

oracle遊標（一）