【Oracle學習筆記】游標

来源:https://www.cnblogs.com/yscit/archive/2018/11/27/10027788.html
-Advertisement-
Play Games

1. 分類 常見的游標可分為顯示游標、隱式游標、靜態游標和動態游標四大類： 1.1 顯示游標 顯式是相對與隱式cursor而言的，就是有一個明確的聲明的cursor。顯式游標的聲明類似如下： delcare 游標關鍵字cursor 游標名 is 數據集; 游標從declare、open、fetch、 ...


1. 分類

       常見的游標可分為顯示游標、隱式游標、靜態游標和動態游標四大類：

1.1 顯示游標

       顯式是相對與隱式cursor而言的，就是有一個明確的聲明的cursor。顯式游標的聲明類似如下：

       delcare 游標關鍵字cursor 游標名 is 數據集;

       游標從declare、open、fetch、close是一個完整的生命旅程。當然了一個這樣的游標是可以被多次open進行使用的，顯式cursor是靜態cursor，她的作用域是全局的，但也必須明白，靜態cursor也只有pl/sql代碼才可以使用她。下麵看一個簡單的靜態顯式cursor的示例：

 1 declare
 2   cursor get_subid(pid a_test.parentid%type) is
 3     select subid from a_test where parentid = pid;
 4   v_subid a_test.subid%type;
 5 begin
 6   open get_subid(1);
 7   loop
 8     fetch get_subid
 9       into v_subid;
10     exit when get_subid%notfound;
11     dbms_output.put_line(v_subid);
12   end loop;
13   close get_subid;
14   dbms_output.put_line('--------這是分割線----------');
15   open get_subid(4);
16   loop
17     fetch get_subid
18       into v_subid;
19     exit when get_subid%notfound;
20     dbms_output.put_line(v_subid);
21   end loop;
22   close get_subid;
23 end;
View Code

1.2 隱式游標

       隱式cursor當然是相對於顯式而言的，就是沒有明確的cursor的declare。在Oracle的PL/SQL中，所有的DML操作都被Oracle內部解析為一個cursor名為SQL的隱式游標，只是對我們透明罷了。

begin
  for rec in (select user, sysdate from dual) loop
    dbms_output.put_line(rec.user || '：' ||
                         to_char(rec.sysdate, 'yyyy-mm-dd hh24:mi:ss'));
  end loop;
end;
View Code

1.3 靜態游標

       靜態游標是相對於動態游標而言的，普通顯示定義的游標都是靜態游標。

1.4 動態游標

         動態游標是相對於靜態游標而言的，要等到運行時才知道結果集查詢語句是什麼樣的。

 1 declare
 2   type atest_rec is record(
 3     pid   a_test.parentid%type,
 4     subid a_test.subid%type);
 5 
 6   type app_ref_cur_type is ref cursor return atest_rec;
 7   my_cur app_ref_cur_type;
 8   my_rec atest_rec;
 9 begin
10 
11   if (to_char(sysdate, 'dd') = 30) then
12     open my_cur for
13       select parentid, subid from a_test where parentid = 1;
14   else
15     open my_cur for
16       select parentid, subid from a_test where parentid = 2;
17   end if;
18 
19   fetch my_cur
20     into my_rec;
21   while my_cur%found loop
22     --當前不是30號 執行else 結果：
23     --2#4
24     --2#5
25     dbms_output.put_line(my_rec.pid || '#' || my_rec.subid);
26     fetch my_cur
27       into my_rec;
28   end loop;
29   close my_cur;
30 
31 end;
View Code

　　【註】Record為記錄數據類型。它類似於C語言中的結構數據類型(STRUCTURE)，PL/SQL提供了將幾個相關的、分離的、基本數據類型的變數組成一個整體的方法，即RECORD複合數據類型。在使用記錄數據類型變數時，需要在聲明部分先定義記錄的組成、記錄的變數，然後在執行部分引用該記錄變數本身或其中的成員。

　　定義記錄數據類型的語法如下：

1 TYPE RECORD_NAME IS RECORD(
2 V1  DATA_TYPE1 [NOT NULL][:=DEFAULT_VALUE],
3 V2  DATA_TYPE2 [NOT NULL][:=DEFAULT_VALUE],
4 VN  DATA_TYPEN [NOT NULL][:=DEFAULT_VALUE]);

由上面的例子，可知cursor與REF cursor大致有以下幾點區別：

1）PL/SQL靜態游標不能返回到客戶端，只有PL/SQL才能利用它。動態游標能夠被返回到客戶端，這就是從Oracle的存儲過程返回結果集的方式。

2）PL/SQL靜態游標可以是全局的，而動態游標則不是，不能在包說明或包體中的過程或函數之外定義動態游標。

3）動態游標可以從子常式傳遞到子常式，而普通游標則不能。如果要共用靜態游標，必須在包說明或包體中把它定義為全局游標。 因為使用全局變數通常不是一種很好的編碼習慣，因此可以用動態游標來共用PL/SQL中的游標，無需混合使用全局變數。

4）靜態游標比動態游標標效率要高，所以在使用游標時首先考慮使用靜態游標，也有人建議儘量使用隱式游標，避免編寫附加的游標控制代碼（聲明，打開，獲取，關閉），也不需要聲明變數來保存從游標中獲取的數據。這個就因人因事而定吧。

另外，在oracle9i以後系統定義的一個refcursor， 這是一個弱類型的游標，相當於.Net中用戶var聲明的變數，主要用在過程中返回結果集。

 1 --創建存儲過程
 2 create or replace procedure sp_get_subid(pid       ina_test.parentid%type,
 3  out_subid out SYS_REFCURSOR) as
 4 begin
 5   open out_subid for
 6     SELECT * FROM a_test WHERE parentid = pid;
 7 EXCEPTION
 8   WHEN OTHERS THEN
 9     RAISE_APPLICATION_ERROR(-20101, 'Error in sp_get_subid' || SQLCODE);
10 end sp_get_subid;
11 
12 --調用存儲過程
13 declare
14   v_rent_rows SYS_REFCURSOR;
15   v_rent_row  a_test%rowType;
16 begin
17   sp_get_subid(1, v_rent_rows);
18   Dbms_output.put_line('parentid subid');
19   loop
20     fetch v _rows
21       into v _row;
22     exit when v _rows%NOTFOUND;
23     Dbms_output.put_line(v _row.parentid || ' ' || v _row.subid);
24   end loop;
25   close v_rows;
26 end;
View Code

 

2. 屬性

2.1 說明

1 %FOUND： bool - TRUE if >1 row returned
2 %NOTFOUND：bool - TRUE if 0 rows returned
3 %ISOPEN： bool - TRUE if cursor still open
4 %ROWCOUNT：int - number of rows affected by last SQL statement

【註】NO_DATA_FOUND和%NOTFOUND的用法是有區別的，小結如下：

1）SELECT . . . INTO 語句觸發 NO_DATA_FOUND；

2）當一個顯式游標的 where 子句未找到時觸發 %NOTFOUND；

3）當UPDATE或DELETE 語句的where 子句未找到時觸發 SQL%NOTFOUND；

4）在游標的提取(Fetch)迴圈中要用 %NOTFOUND 或%FOUND 來確定迴圈的退出條件，不要用NO_DATA_FOUND

2.2 示例

2.2.1 示例一：

 1 begin
 2 
 3   update A_TEST set SUBID = '15' WHERE PARENTID = 4;
 4 
 5   --SQL%ISOPEN是一個布爾值，如果游標打開，則為TRUE，如果游標關閉，則為FALSE.
 6 
 7   if sql%isopen then
 8 
 9     dbms_output.put_line('Openging');
10 
11   else
12 
13     dbms_output.put_line('closing'); --對於隱式游標而言SQL%ISOPEN總是FALSE，這是因為隱式游標在DML語句執行時打開，結束時就立即關閉。
14 
15   end if;
16 
17   if sql%found then
18 
19     dbms_output.put_line('游標指向了有效行'); --判斷游標是否指向有效行
20 
21   else
22 
23     dbms_output.put_line('Sorry');
24 
25   end if;
26 
27   if sql%notfound then
28 
29     dbms_output.put_line('Also Sorry');
30 
31   else
32 
33     dbms_output.put_line('Haha');
34 
35   end if;
36 
37   dbms_output.put_line(sql%rowcount);
38 
39 exception
40 
41   when no_data_found then
42 
43     dbms_output.put_line('Sorry No data');
44 
45   when too_many_rows then
46 
47     dbms_output.put_line('Too Many rows');
48 
49 end;
View Code

       【註】SQL語言分為DDL（Data Definition Language，數據定義語言，用來維護數據對象）和DML（Data Manipulation Language，數據操作語言，用於增刪改表中數據，DML是伴隨TCL事務控制的）。

2.2.2 示例二：

 1 declare
 2 
 3   empNumber a_test.parentid%TYPE;
 4 
 5   empName   a_test.subid%TYPE;
 6 
 7 begin
 8 
 9   if sql%isopen then
10 
11     dbms_output.put_line('Cursor is opinging');
12 
13   else
14 
15     dbms_output.put_line('Cursor is Close');
16 
17   end if;
18 
19   if sql%notfound then
20 
21     dbms_output.put_line('No Value');
22 
23   else
24 
25     dbms_output.put_line(empNumber); --沒有賦值，輸出為空白
26 
27   end if;
28 
29   dbms_output.put_line(sql%rowcount); --沒有記錄，輸出為空白
30 
31   dbms_output.put_line('-------------');
32 
33  
34 
35   select parentid, subid into empNumber, empName from a_test where parentid = 4;
36 
37   dbms_output.put_line(sql%rowcount);
38 
39  
40 
41   if sql%isopen then
42 
43     dbms_output.put_line('Cursor is opinging');
44 
45   else
46 
47     dbms_output.put_line('Cursor is Closing');
48 
49   end if;
50 
51   if sql%notfound then
52 
53     dbms_output.put_line('No Value');
54 
55   else
56 
57     dbms_output.put_line(empNumber);
58 
59   end if;
60 
61 exception
62 
63   when no_data_found then
64 
65     dbms_output.put_line('No Value');
66 
67   when too_many_rows then
68 
69     dbms_output.put_line('too many rows');
70 
71 end;
View Code

 

       【註】%Type是Oracle提供的一種數據定義方法，為的是使一個新定義的變數與另一個已經定義了的變數(通常是表的某一列)的數據類型保持一致，當被參照的那個變數的數據類型發生改變時,那麼這個新定義的變數的數據類型也會隨之發生改變。當不能確切的知道那個變數的類型是,就採用這種方法來定義變數的數據類型。

3. 操作

3.1 For迴圈游標

 1  --聲明游標：delcare 游標關鍵字cursor 游標名 is 數據集;
 2 
 3 declare
 4 
 5 cursorc_list is
 6 
 7 selectp.fid, max(t.exp) exp
 8 
 9 from view_pilot p
10 
11 left join IO_FMS_BILLOFHEALTH t
12 
13 ont.phr = p.fjobnumber
14 
15 group by p.fid;
16 
17 
18 --For迴圈，類似.Net中的foreach方法：
19 
20 --begin
21 
22 --for 元素名 in 游標名 迴圈關鍵字loop
23 
24 --執行語句;
25 
26 --endloop;
27 
28 begin
29 
30   for c_row in c_list loop
31 
32     update alarm_pilotintelligence
33 
34        set C = GetAlarmStateByExp(c_row.exp)
35 
36      where isprimary = 0
37 
38        and pid = c_row.fid;
39 
40 end loop;

3.2 Fetch游標

 1 --定義游標
 2 
 3 declare
 4 
 5   cursor c_job is
 6 
 7     select * from a_test order by parentid;
 8 
 9   --定義一個游標變數
10 
11   c_row c_job%rowtype;
12 
13 begin
14 
15   --使用的時候必須要明確的打開游標
16 
17   Open c_job;
18 
19   --開始迴圈標記
20 
21   loop
22 
23     --提取一行數據到c_row，相當ADO.Net中的SqlDataReader.Read()方法
24 
25     fetch c_job into c_row;
26 
27     --判讀是否提取到值，沒取到值就退出
28 
29     --取到值c_job%notfound 是false
30 
31     --取不到值c_job%notfound 是true
32 
33     exit when c_job%notfound;
34 
35     dbms_output.put_line(c_row.parentid || '-' || c_row.subid); --用於輸出，這是oracle中最基礎的方法之一
36 
37     --結束迴圈，並關閉游標
38 
39   end loop;
40 
41   close c_job;
42 
43 end;

       【註】如果一個表有較多的列，使用%ROWTYPE來定義一個表示表中一行記錄的變數，比分別使用%TYPE來定義表示表中各個列的變數要簡潔得多，並且不容易遺漏、出錯。這樣會增加程式的可維護性。當不能確切地知道被參照的那個表的結構及其數據類型時，可以採用這種方法定義變數的數據類型。

3.3 While迴圈游標

　　上面【示例二】中的結果還可以通過While迴圈與Fetch相結合來實現：

 1 --定義游標
 2 
 3 declare
 4 
 5   cursor c_job is
 6 
 7     select * from a_test order by parentid;
 8 
 9   --定義一個游標變數
10 
11   c_row c_job%rowtype;
12 
13 begin
14 
15   --使用的時候必須要明確的打開游標
16 
17   Open c_job;
18 
19   --開始迴圈標記
20 
21   --提取一行數據到c_row，相當ADO.Net中的SqlDataReader.Read()方法
22 
23   fetch c_job
24 
25     into c_row;
26 
27   --while迴圈
28 
29   while c_job%found loop
30 
31     dbms_output.put_line(c_row.parentid || '-' || c_row.subid);
32 
33     fetch c_job
34 
35       into c_row;
36 
37     --結束迴圈，並關閉游標
38 
39   end loop;
40 
41   close c_job;
42 
43 end;

 

參考資料：

1．Oracle 游標使用全解

2．游標屬性SQL%FOUND, SQL%NOTFOUND, SQL%ROWCOUNT, SQL%ISOPEN

3．ORACLE中%TYPE和%ROWTYPE的使用

4. cursor 與refcursor及sys_refcursor的區別 (轉載)


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

-Advertisement-
Play Games
更多相關文章
  • Linux(CentOS6.5 x64)下版本安裝及升級kangle+EasyPanel
    說明：(easypanel集成了kangle web 伺服器和mysql,僅支持centos 5和centos 6) 。執行下麵的命令即可，安裝程式將自動安裝或者升級： yum -y install wget;wget http://kangle.odata.cc/start;sh start 運行 ...
  • 查看用戶的信息文件-passwd
    passwd 文件 位置：/etc/passwd 作用：用於保存用戶的賬戶信息 註意點：由於passwd也可以作為一個命令直接使用，也可以作為配置文件，所以如果使用man命令進行查看幫助信息時，應該有2種寫法 使用 cat /etc/passwd 命令以後，會看到這樣的畫面 我們會看到7個欄位，分別 ...
  • openSUSE Leap 15.0 Adobe Flash Player 安裝說明
    鑒於Firefox安裝配置文件： 因此對於Firefox下的安裝如下： sudo cp libflashplayer.so /usr/lib64/browser-plugins/（64位Firefox） sudo cp libflashplayer.so /usr/lib/browser-plugi ...
  • CPU 實用工具
    系統版本：CentOS 7.4 top uptime vmstat mpstat sar pidstat ...
  • linux一切皆文件之tty字元設備(深入理解sshd創建pty的過程) （五）
    一、知識準備 1、在linux中，一切皆為文件，所有不同種類的類型都被抽象成文件（比如：塊設備，socket套接字，pipe隊列） 2、操作這些不同的類型就像操作文件一樣，比如增刪改查等 3、塊設備支持隨機訪問，而字元設備只能依據先後順序來讀取數據。最典型的字元設備就是tty 二、環境準備 | 組件 ...
  • ubuntu 16.04伺服器 搭建php LAMP環境
    1.安裝Apache 下載目錄： 安裝程式： 檢查是否安裝成功： 檢查安裝位置： 檢查安裝版本： 檢查安裝成功：在本機瀏覽器輸入伺服器ip，預設根目錄 /var/www 2.安裝Mysql 安裝mysql服務： 安裝mysql客戶端： 查詢mysql狀態： 登錄mysql： 3.安裝php 安裝ph ...
  • oracle中的greatest 函數和 least函數
    oracle中的greatest 函數和 least函數 原文地址：https://blog.csdn.net/sinat_32023305/article/details/78778596 oracle中的greatest 函數和 least函數 oracle中的greatest 函數和 leas ...
  • Windows Cluster 在群集管理器下 集群或可用性組 都不顯示的問題
    作為一個IT成員，特別是偏支持的。很多時候就是和各種異常打交道，總會碰到一些奇奇怪怪的問題。很多時候，可能是一個小小的異常都需要花費很長時間去解決。 SQL Server AlwaysOn 是建立在Windows集群上的，有一次，我登上節點，發現集群和可用性組信息都沒了。雖然，咋一看到，有點怵了。不 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類