程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> PL/SQL之--游標，plsql游標

PL/SQL之--游標，plsql游標

編輯：Oracle教程

PL/SQL之--游標，plsql游標

一、游標簡介

　　在PL/SQL中執行SELECT、INSERT、DELETE和UPDATE語句時，ORACLE會在內存中為其分配上下文區（Context Area），也稱為緩沖區。游標是指向該區的一個指針。它提供了一種在多行結果集中對每一行數據分別進行單獨處理的方法。用戶可以用SQL語句逐一從游標中獲取記錄，並賦給主變量，交由主語言進一步處理。

　　oracle中游標有如下兩種：

靜態游標：分為顯式游標和隱式游標。
ref游標：引用類型，類似於C中的指針。

二、靜態游標

　　靜態游標分為顯式游標和隱式游標，靜態游標在編譯的時候已經確定，然後把結果復制到內存中。

　　1、隱式游標

　　　　在PL/SQL中執行數據操作語句查詢（查詢一行），修改，刪除（DML語句）時，oracle預先定義一個名稱為SQL的隱式游標，在執行DML語句之後通過檢查隱式游標的屬性獲取與最近執行的SQL語句相關信息。用戶只能通過隱式游標的相關屬性，從而完成相應的操作。在隱式游標的工作區中，所存放的數據是與用戶自定義的顯示游標無關的、最新處理的一條SQL 語句所包含的數據。

調用語法為：SQL%

　　隱式游標屬性%found、%notfound、%rowcount、%isopen這四種，詳情如下：

%found: 只有DML語句影響一行或多行時，%found屬性才返回true；
%notfound: 如果DML語句沒有影響任何行數，則%notfound返回true；
%rowcount: 返回DML影響的行數，如果DML沒有影響任何行數，則%rowcount返回0；
%isopen: 判斷SQL游標是否已經打開，在執行SQL語句之後，oracle會自動關閉SQL游標，所有隱式游標的%isopen屬性始終為false;

　　當PL/SQL拋出異常的時候，將不使用屬性%found、%notfound、%rowcount來查明DML語句是否已經影響了行數。

　　代碼示例如下：

　　select中使用隱式游標

-- select隱式游標
declare
  v_id number := 1;
   v_username varchar2(255);
   v_age number;
   v_password varchar2(255);
begin
  select username, age, password into v_username, v_age, v_password from person where id = v_id;
   dbms_output.put_line(SQL%ROWCOUNT);--影響的行數
  if SQL%ISOPEN then
      dbms_output.put_line('open');    
  else    
    dbms_output.put_line('not open');
  end if;   
 
  if SQL%FOUND then --成功
    dbms_output.put_line('found');
  else --失敗
    dbms_output.put_line('not found');  
  end if; 
  
  if SQL%NOTFOUND then --失敗
    dbms_output.put_line('not found');
  else --成功
    dbms_output.put_line('found');  
  end if;    
end;

　　insert中的隱式游標

-- insert 隱式游標
declare
   v_id number := 26;
   v_username varchar2(255) := 'xiaoming';
   v_age number := 19;
   v_password varchar2(255) := 'xiao123' ;
begin
  insert into person (id, username, age, password) values (v_id, v_username, v_age, v_password);
  -- commit; --此時commit後面都會沒有值
  dbms_output.put_line(SQL%ROWCOUNT);--增加的行數
  if SQL%ISOPEN then
      dbms_output.put_line('open');    
  else    
    dbms_output.put_line('not open');
  end if;   
 
  if SQL%FOUND then --成功
    dbms_output.put_line('found');
  else --失敗
    dbms_output.put_line('not found');  
  end if; 
  
  if SQL%NOTFOUND then --失敗
    dbms_output.put_line('not found');
  else --成功
    dbms_output.put_line('found');  
  end if;    
  -- commit;
end;

　　update中使用隱式游標

-- update 隱式游標
declare
 v_id number := 25;
 v_age number := 100; 
begin 
  update person set age = v_age where id = v_id;
  --commit; --此時commit後面都會沒有值
  dbms_output.put_line(SQL%ROWCOUNT);--修改的行數
  if SQL%ISOPEN then
      dbms_output.put_line('open');    
  else    
    dbms_output.put_line('not open');
  end if;    
  
  if SQL%FOUND then --成功
    dbms_output.put_line('found');
  else --失敗
    dbms_output.put_line('not found');  
  end if; 
  
  if SQL%NOTFOUND then --失敗
    dbms_output.put_line('not found');
  else --成功
    dbms_output.put_line('found');  
  end if;       
  commit;
end;

　　delete中使用隱式游標

--  delete 隱式游標
declare
   v_id number := 26; 
begin
  delete from person where id = v_id;
  -- commit; --此時commit後面都會沒有值
  dbms_output.put_line(SQL%ROWCOUNT);--刪除的行數  
  if SQL%ISOPEN then
      dbms_output.put_line('open');    
  else    
    dbms_output.put_line('not open');
  end if;   
  
  if SQL%FOUND then --成功
    dbms_output.put_line('found');
  else --失敗
    dbms_output.put_line('not found');  
  end if; 
  
  if SQL%NOTFOUND then --失敗
    dbms_output.put_line('not found');
  else --成功
    dbms_output.put_line('found');  
  end if;    
  commit;
end;

　　2、顯式游標

　　顯示游標時由用戶顯示聲明的游標。根據在游標中定義的查詢，查詢返回的行集合可以包括0行貨多行，這些行稱為活動集。游標將指向活動集中的當前行。
顯示游標的操作過程如下：
　　1）、定義游標

　　　　CURSOR 游標名稱 is 查詢語句;
　　　　CURSOR 游標名稱[參數1,參數2、...]
　　　　[RETURN 數據類型]
　　　　IS
　　　　查詢語句;
　　　　游標參數只能為輸入參數，格式為：
　　　　參數名稱 [IN] 數據類型 [值]

　　2）、打開游標

　　　　open 游標名稱 [參數名稱 => 值, 參數名稱 => 值,..];
　　3）、從游標中獲取結果集

　　　　FETCH 游標名稱 INTO 變量;

　　　　執行FETCH語句時，每次返回一條數據行，並自動將游標移動指向下一條數據行。當檢索到最後一條數據時，如果再次執行FETCH語句，操作將失敗，游標屬性%NOTFOUND設置為TRUE。所以每次執行完FETCH語句後，檢查游標屬性%NOTFOUND就從而判斷FETCH語句是否執行成功並返回一個數據行，確定是否為最後一行。

　　4）、關閉游標

　　　　CLOSE 游標名稱;

　　顯式游標屬性如下：

%FOUND 布爾型屬性，當最近一次提取游標操作FETCH成功則為 TRUE,否則為FALSE；
%NOTFOUND 布爾型屬性，與%FOUND相反；
%ISOPEN 布爾型屬性，當游標已打開時返回 TRUE；
%ROWCOUNT 數字型屬性，返回已從游標中讀取的記錄數；

　　代碼示例如下：

　　不帶參數不帶返回值的游標

-- 不帶參數的游標不帶返回值的游標    
declare
   -- 1、定義一個游標，將person所有數據提取出來
   cursor c_person is
   select * from person;
   r_person person%rowtype;
begin
  --2、打開游標
  open c_person;
  --3、提取數據
  loop
    fetch c_person 
    into r_person;
    exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('id:'||r_person.id);
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
  
  end loop;
  --4、關閉游標
  close c_person;
end;

　　帶參數不帶返回值的游標

--帶參數不帶返回值的游標
declare
   -- 1、定義一個游標，將person所有數據提取出來
   cursor c_person(v_age number) is
   select * from person where age < v_age;
   r_person person%rowtype;
begin
  --2、打開游標
  open c_person(20);--打開游標,傳遞參數值
  --3、提取數據
  loop
    fetch c_person 
    into r_person;
    exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('id:'||r_person.id);
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
  
  end loop;
  --4、關閉游標
  close c_person;
end;

　　帶參數帶返回值的游標,返回type類型

-- 帶參數帶返回值的游標,返回type類型
declare
    r_person person%rowtype;
   
   -- 1、定義一個游標，有參數和返回值
   cursor c_person(v_age number) 
     return  person%rowtype
   is
      select * from person where age < v_age;
begin
  --2、打開游標
  open c_person(20);--打開游標,傳遞參數值
  --3、提取數據
  loop
    fetch c_person 
    into r_person;
    exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('id:'||r_person.id);    
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
    dbms_output.put_line('password:'||r_person.password);
    
  end loop;
  --4、關閉游標
  close c_person;
end;

　　帶參數帶返回值的游標,返回自定義類型

 -- 帶參數帶返回值的游標,返回自定義類型
declare
   type person_record_type is record(
        t_username person.username%type,
        t_age person.age%type,
        t_password person.password%type
   );   
   v_person_record person_record_type;
   
   -- 1、定義一個游標，有參數和返回值
   cursor c_person(v_age number) 
     return person_record_type
   is
      select username, age, password from person where age < v_age;
begin
  --2、打開游標
  open c_person(20);--打開游標,傳遞參數值
  --3、提取數據
  loop
    fetch c_person 
    into v_person_record;
    exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('username:'||v_person_record.t_username);
    dbms_output.put_line('age:'||v_person_record.t_age); 
    dbms_output.put_line('password:'||v_person_record.t_password);
    
  end loop;
  --4、關閉游標
  close c_person;
end;

三、ref 類型游標

　　ref游標也是一個指向多行查詢結果集合中當前數據行的指針。但與游標不同的是，ref游標是動態的，而游標是靜態的。隱式游標和顯示游標都是靜態定義的。它們在編譯的時候結果集就已經被確定。而ref游標在運行時候可以指向不同查詢語句的結果集。　　定義游標變量類型，可以采用強類型定義和弱類型定義兩種。強類型定義必須指定游標變量的返回值類型，而弱類型定義則定義說明返回值類型。

　　語法如下：

　　1、定義一個REF CURSOU 數據類型
　　type 游標名稱 IS REF CURSOR
　　return 返回類型;
　　2、聲明一個該數據類型的游標變量，如：
　　游標變量名稱游標名稱;

　　示例代碼如下：

　　ref 類型游標弱類型（無返回值）

-- ref 類型游標弱類型（無返回值）
declare
   type c_person_type is ref cursor;
   c_person c_person_type;
   r_person person%rowtype;
begin
   -- 遍歷person表
   open c_person for select * from person_back;--person_back與person表結構一樣
   loop
    fetch c_person 
    into r_person;
    exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('id:'||r_person.id);
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
  end loop;
  --關閉游標
    dbms_output.put_line('---------------------------------');   
  -- 變量person_back
   open c_person for select * from person;
   loop
    fetch c_person 
    into r_person;
    exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('id:'||r_person.id);
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
  end loop;
  close c_person;
end;

　　ref 類型游標強類型(返回row type)

-- ref 類型游標強類型(返回row type)
declare
  type c_persontype is ref cursor return person%rowtype;
  c_person c_persontype;
  r_person person%rowtype;
begin
  -- 遍歷person表
  open c_person for select * from person;
  loop
  fetch c_person
  into r_person;
   exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('id:'||r_person.id);
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
  end loop;
  close c_person;
  dbms_output.put_line('--------------------------------------');
   -- 遍歷person_back表
  open c_person for select * from person_back;
  loop
  fetch c_person
  into r_person;
   exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('id:'||r_person.id);
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
  end loop;
  close c_person;
end;

　　-- ref 類型游標強類型(返回自定義類型)

-- ref 類型游標強類型(返回自定義類型)
declare
  -- 定義一個數據類型
  type person_record is record(
       username varchar2(255), -- 定義好長度，否則會保持
       age number,
       password varchar2(255)
  );
  -- 定義返回自定義數據類型的游標類型
  type c_persontype is ref cursor return person_record;
  c_person c_persontype; -- 游標類型的變量
  r_person person_record; -- 自定義數據類型變量
begin
  -- 遍歷person表
  open c_person for select username, age, password from person;
  loop
  fetch c_person
  into r_person;
   exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age);
    dbms_output.put_line('password:'||r_person.password); 
  end loop;
  close c_person;
  dbms_output.put_line('--------------------------------------');
   -- 遍歷person_back表
  open c_person for select username, age, password from person_back;
  loop
  fetch c_person
  into r_person;
   exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
    dbms_output.put_line('password:'||r_person.password);
  end loop;
  close c_person;
end;

　　sys_refcursor

　　sys_refcursor是oracle9i以後系統定義的一個refcursor,主要用在過程中返回結果集。我們也可以直接使用sys_refcursor遍歷結果集。

　　示例代碼如下：

declare 
   c_person sys_refcursor;
   r_person person%rowtype;
begin
   -- 遍歷person表
   open c_person for select * from person_back;--person_back與person表結構一樣
   loop
    fetch c_person 
    into r_person;
    exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('id:'||r_person.id);
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
  end loop;
  --關閉游標
    dbms_output.put_line('---------------------------------');   
  -- 變量person_back
   open c_person for select * from person;
   loop
    fetch c_person 
    into r_person;
    exit when c_person%notfound; -- 下面沒有數據的時候，退出
    dbms_output.put_line('id:'||r_person.id);
    dbms_output.put_line('username:'||r_person.username);
    dbms_output.put_line('age:'||r_person.age); 
  end loop;
  close c_person;
end;

PL/SQL的游標怎使用？語法是什？

declare
... ...
cursor 游標名 is Select語句; --聲明游標
v 游標名%rowtype; --為讀取游標數據定義變量
begin
open 游標名; --打開游標
loop
fetch 游標名 into v; --讀取游標當前指針所在的行數據
exit when 游標名%notfound; --判斷游標數據是否讀完，並退出循環
... ...
end loop;
close 游標名; --關閉游標
end;
更詳細內容，學習相關教程或參考書吧。

PL/SQL的游標怎使用？語法是什？

CREATE PROCEDURE [dbo].[pro_CURSOR]
AS
BEGIN

--聲明一個游標
DECLARE MyCURSOR CURSOR FOR
SELECT userid,depid FROM users

--打開游標
open MyCURSOR

--聲明兩個變量
declare @userid varchar(50)
declare @depid varchar(50)

--循環移動
fetch next from MyCURSOR into @userid,@depid
while(@@fetch_status=0)
begin
update users set description=(select dep_des from department where depid=@depid)
where userid=@userid
fetch next from MyCURSOR into @userid,@depid
end

close MyCURSOR
deallocate MyCURSOR

END