Oracle學習筆記之游標詳解

来源:http://www.cnblogs.com/lovetianjin/archive/2017/10/20/7698281.html
-Advertisement-
Play Games

游標 游標存在意義：解決“select *”返回空、多行記錄問題，但凡select，就可能多行結果集，也就需要用游標。 游標分4步走：cursor、open、fetch、close 可能省略open、close，用for ... in ... loop ... end loop; 1、靜態游標： （ ...


游標

游標存在意義：解決“select *”返回空、多行記錄問題，但凡select，就可能多行結果集，也就需要用游標。

游標分4步走：cursor、open、fetch、close

可能省略open、close，用for ... in ... loop ... end loop;

1、靜態游標：

（1）隱式游標：

在PL/SQL中DML（select into、insert、update、delete）時，Oracle隱式定義一個叫SQL的游標，自動聲明、打開、關閉。

①sql%found

如果DML語句影響一行或多行，則sql%found為true

例如：

declare

   empno emp.empno%type;

begin

   select empno into empno -- 對於DML之select into

   from emp

   where empno =7369;

   ifsql%foundthen

      dbms_output.put_line('有一行記錄');-- 用sql%found、sql%notfound判斷是否返回一行記錄

  endif;

Exception

  when no_data_found then

    dbms_output.put_line('查詢返回空行');-- 用no_data_found判斷是否返回空行記錄

  when too_many_rows then

dbms_output.put_line('查詢返回多行');-- 用too_many_rows判斷是否返回多行記錄

When others then

dbms_output.put_line(‘系統錯誤');

end;

例如：

 declare

   total integer;

   e_null exception;--自定義的異常

   e_toomanyrow exception;

begin

  select count(*) into total from emp where emp.deptno=20;   

  if total>1 then

    raise e_toomanyrow;--拋出異常

  elsif total<=0 then

    raise e_null;

  end if;

  --捕獲並處理異常

  Exception when e_toomanyrow then

    dbms_output.put_line('Exception：返回多行記錄');

  when e_null then

     dbms_output.put_line('Exception：無返回記錄');

  when others then

     dbms_output.put_line('Exception：其他異常');

 

end;

 ②sql%notfound

如果DML語句沒有影響任何行，則sql%notfound為true

第一種情況：DML是insert、delete、update

例如：

declare

begin

   deletefrom emp

   where empno =47;

   ifsql%notfoundthen

      dbms_output.put_line('未找到值');

   else

      dbms_output.put_line('已經刪除');

   endif;

end;

第二種情況：DML是select into

PL/SQL中的“select into”的結果必須“有且只有一行”，該行結果可以賦給列變數、或記錄變數

 例如：

declare

  type type_emp isrecord

  (

  empno emp.empno%type,

  ename emp.ename%type,

  dname dept.dname%type,

  sal emp.sal%type

  );

  v_emp type_emp;

begin

  select empno, ename, dname, sal into v_emp

  from emp innerjoin dept

  on emp.deptno = dept.deptno

  where ename ='SMITH';

  --if sql%notfound then

    --dbms_output.put_line('查無此人'); -- 永遠執行不到，因為查詢返回空行時觸發no_data_found異常

  --else

    dbms_output.put_line(v_emp.empno||'*'||v_emp.ename||'*'||v_emp.dname||'*'||v_emp.sal);

  --end if;

exception

  whenno_data_foundthen

    dbms_output.put_line('查無此人');

  whenothersthen

    dbms_output.put_line(sqlerrm);

end;

 

註意：

如果查詢返回空行，PL/SQL拋出no_data_found異常；

如果查詢返回多行，PL/SQL拋出too_many_rows異常；

所以在DML之“select into”引發異常時，不能使用sql%notfound屬性查看DML是否影響了行。

③sql%rowcount　

declare

  empno emp.empno%type;

begin

  select empno into empno

  from emp

  where empno =7788;

  ifsql%rowcount>0then

    dbms_output.put_line('從表中選擇了'||sql%rowcount||'行');

  else

    dbms_output.put_line('從表中未選擇行');

  endif;

end;

④sql%isopen

SQL%ISOPEN返回一個布爾值，如果游標打開，則為TRUE, 如果游標關閉，則為FALSE.對於隱式游標而言SQL%ISOPEN總是FALSE，這是因為隱式游標在DML語句執行時打開，結束時就立即關閉。

（２）顯式游標

重點在“步驟”:定義游標，打開游標， 抓取數據，關閉游標；

①游標%found

如果最後一條fetch語句成功提取行，則“游標%found”為true

②游標%notfound

如果最後一條fetch語句未能提取行，則“游標%notfound”為true

例如：

declare

cursor cur is

select ename,job from emp;  --這裡enamel，job 兩列

item emp.ename%type;   --定義item是emp表ename列的類型

item2 emp.job%type;    --定義item2 是emp表job列類型。 這裡聲明的變數必須對性查詢--出的列對應。

begin

open cur;

loop

fetch cur into item,item2;  --捕捉了游標cur 的元素放入，item，item2中。

 

exitwhencur%notfound;  -- 當cur游標都便利完就exit退出。

endloop;

dbms_output.put_line(item||item2);

 

close cur;                  --註意關閉游標

end;

 

 

③游標%isopen，判斷該游標是否被打開；

例如：

Declare

　　　--聲明一個游標

  cursor cur is　

  select*　from emp　where deptno =10;

  v_emp cur%rowtype;

begin

  ifnot cur%isopenthen

    open cur;--如果游標沒有被打開，那麼就代開游標

  endif;

--迴圈，從游標中抓取數據

  loop

    fetch cur into v_emp;

    exitwhen cur%notfound;--當最後一條數據被獲取後，exit退出迴圈

    dbms_output.put_line(v_emp.ename||':'||v_emp.sal);

  endloop;

  close cur;--關閉游標

end;

④游標%rowcount：返回游標查詢記錄數；

註意：SQL%ROWCOUNT:在執行任何DML語句之前，SQL%ROWCOUNT的值都是NULL,對於SELECT INTO語句，如果執行成功，SQL%ROWCOUNT的值為1,如果沒有成功，SQL%ROWCOUNT的值為0，同時產生一個異常NO_DATA_FOUND.

例如：（上面例子多了幾行代碼） declare

  cursor cur is

  select*

  from emp

  where deptno =10;

  v_emp cur%rowtype;

  a number;

begin

  ifnot cur%isopenthen

    open cur;

  endif;

  loop

    fetch cur into v_emp;

    exitwhen cur%notfound;

    dbms_output.put_line(v_emp.ename||':'||v_emp.sal);

  endloop;

-----------------------------------------------------------

 

  --下麵是sql%rowcount

  select empno into  a from emp where empno='7369';

  dbms_output.put_line(sql%rowcount);--如果查詢sql 有記錄數，此時返回1否則為null

  dbms_output.put_line(cur%rowcount);--返回游標查詢的最終記錄數

-----------------------------------------------------------

  close cur;

end;

例如：

 declare

cursor cur(i varchar2)is

select*from emp where deptno=i ;

    item cur%rowtype;

 

begin

 

for r in cur('10')  -- 註意這裡的in。。。 後面跟的是游標 cur 而不是 tiem

      --這裡可以寫成：for admin in cur（&部門編號）；用通配符，使鍵盤鍵入內容。

loop

dbms_output.put_line(r.ename|| cur%rowcount);--其中cur%rowcount 是有標明%rowcount 顯示游標序列

endloop;

end;

⑤使用顯式游標更新或刪除行：

declare

-- select語句必須只包括一個表

-- 某表不能是“含有distinct、order by的子查詢”查詢來的結果集（視圖）

cursor 游標名 is select 某列 from 某表 for update[ of 某列];

變數 某表.某列%type;

變數 某表%rowtype;

變數 游標%type;

變數 游標%rowtype;

begin

open 游標;

loop

      fetch 游標 into 變數;

      exit when 游標%notfound;

      if 某條件 then

            -- update、delete語句只有在打開游標、提取特定行之後才能使用

            -- update語句中使用的列必須出現在“select-for update of”語句中

            update 某表 set 某列 = 某值 where current of 游標;

      end if;

end loop;

close 游標;

commit;

end;

例如：

-- 如果sal < 2000，長工資1000

declare

   cursor cur is

       select sal from emp

      where sal <2000forupdate;

  cursor bbb is

       select*from emp;

begin

  for v_sal in cur

   loop

       update emp

       set sal = sal +1000

       wherecurrentof cur;

  endloop;

 

 Commit;

  for v_emp in bbb

  loop

    dbms_output.put_line(v_emp.ename||'*'||v_emp.sal);

  endloop;

end;

動態游標：

（1）弱類型游標

例如：

declare         -- //弱類型游標

   type cursor_type isrefcursor;

   cur cursor_type;

   temp emp%rowtype;

begin

    open cur for 

    select*from  emp;  

    loop

       fetch cur into temp;

       exitwhencur%notfound;

       dbms_output.put_line(temp.empno||temp.ename);

    endloop; 

end;

例如：

 declare

   type cur_type isrefcursor;-- 弱類型游標 通過is ref 定義

   cur   cur_type;

   v_emp emp%rowtype;

   v_dept dept%rowtype;

   selection varchar2(1):= upper('D');

 begin

   if selection ='E'then

     open cur for

      select*from emp;

     loop

       fetch cur into v_emp;

       exitwhen cur%notfound;

       dbms_output.put_line(v_emp.ename||':'||v_emp.sal);

     endloop;

     close cur;

   elsif selection ='D'then

     open cur for

       select*from dept;

     loop

       fetch cur intov_dept;

       exitwhen cur%notfound;

       dbms_output.put_line(v_dept.deptno||':'||v_dept.dname);

     endloop;

     close cur;

   else

     null;

   endif;

 end;

 

 

（2）強類型游標

例如：

 declare      --強類型游標 強制返回內容

   type record_type isrecord(empno number,ename varchar2(20));

   type cursor_type isrefcursorreturn record_type;

   rec record_type;

   cur cursor_type;

begin

    open cur for 

    select empno,ename from  emp;  

    loop

       fetch cur into rec;

       exitwhen cur%notfound;

       dbms_output.put_line(rec.empno||' '||rec.ename);

    endloop; 

end;

例如：

  -- 用強類型游標顯示員工姓名和工資

 declare

   type record_type isrecord(namevarchar2(10), sal number);-- 記錄類型

   type cur_type isrefcursorreturn record_type;-- 強類型游標，將來必須返回record_type類型的結果集

   emp_record record_type;-- 記錄類型變數

   emp_cur   cur_type;-- 強類型游標變數

 

 begin

   open emp_cur for

   select ename, sal from emp;-- 動態SQL兩邊的單引號可以省略，必須返回record_type類型的結果集

   loop

      fetch emp_cur into emp_record;-- 把record_type類型的結果集向record_type類型的變數中填充

      exitwhen emp_cur%notfound;

      dbms_output.put_line(emp_record.name||':'||emp_record.sal);

   endloop;

   close emp_cur;

 end;

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Mongodb 認證鑒權那點事
    [TOC] 一、Mongodb 的許可權管理 認識許可權管理，說明主要概念及關係 與大多數資料庫一樣，Mongodb同樣提供了一套許可權管理機制。 為了體驗Mongodb 的許可權管理，我們找一臺已經安裝好的Mongodb，可以 "參照這裡" 搭建一個單節點的Mongodb。 直接打開mongo shell ...
  • 連接池
    資料庫連接池 本詞條由“科普中國”百科科學詞條編寫與應用工作項目 審核 。 本詞條由“科普中國”百科科學詞條編寫與應用工作項目 審核 。 資料庫連接池負責分配、管理和釋放資料庫連接，它允許應用程式重覆使用一個現有的資料庫連接，而不是再重新建立一個；釋放空閑時間超過最大空閑時間的資料庫連接來避免因為沒 ...
  • Java基礎——Oracle（五）
    一、Oracle 中的分頁 1) select * from emp; 2)select * ,rownum from emp; //這樣寫不行 3)select ename,job,sal,rownum from emp; //可以 ENAME JOB SAL ROWNUM SMITH CLERK ...
  • oracle基礎之游標的理解與使用
    關於游標，首先要知道游標的定義。 游標，是記憶體中的一款區域，用來存放select的結果集 游標用來處理從資料庫中檢索的多行記錄（使用select語句）。利用游標，程式可以逐個的處理和遍歷一次索引返回的整個記錄集。 在資料庫中，存在兩種游標： 一、顯示游標（需要明確定義） 顯示游標被用於處理返回多行數 ...
  • 集群(cluster)和高可用性(HA)的概念
    轉載自:http://www.cnblogs.com/BlackWizard2016/p/5143816.html,侵刪,只為學習所用. 1.1 什麼是集群 簡單的說，集群（cluster）就是一組電腦，它們作為一個整體向用戶提供一組網路資源。這些單個的電腦系統就是集群的節點（node）。一個理 ...
  • 資料庫基礎
    一、基礎 1、說明：創建資料庫 CREATE DATABASE database-name 2、說明：刪除資料庫 drop database 資料庫名 3、說明：備份sql server 創建 備份數據的 device USE master EXEC sp_addumpdevice 'disk',  ...
  • SQL系列學習 存儲過程&事物語法
    /*學習事物基本語法*/ /*增加課室名的唯一索引*/ALTER table class add constraint uni_ClassName unique(name) /*創建存儲過程，其中增加教師，並增加課室*/CREATE proc pro_AddClass @className varc ...
  • SQL系列學習 基礎數據
    //班主任表 CREATE TABLE [dbo].[teacher]( [id] [int] IDENTITY(1,1) NOT NULL primary key, [name] [varchar](50) NOT NULL, [sj] [datetime] default(getdate()) ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類