程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle 批處理利器游標教程

Oracle 批處理利器游標教程

編輯:Oracle數據庫基礎
 

我們經常寫這樣的代碼:

-- Created on 2015-7-15 by JellyThink 
declare 
    strAreaCode VARCHAR2(10);
    strUserName VARCHAR2(30);
    strTelNum VARCHAR2(12);begin
    -- 我們這裡確信只返回一行數據
    select a.area_code, a.user_name, a.tel_num into strAreaCode, 
           strUserName, strTelNum 
        from xg.sys_spec_tel a where a.tel_num='15034974832';
    dbms_output.put_line(strAreaCode);
    dbms_output.put_line(strUserName);
    dbms_output.put_line(strTelNum);
exception
    when NO_DATA_FOUND then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
    when TOO_MANY_ROWS then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
    when others then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);end;

在使用select ... into ...語句時,我們笃信肯定會返回一行數據,而且只有一行;但是,如果返回的數據不止一行,這樣都會拋出異常。而我們期望的不是拋出異常,那麼如果返回的數據不止一行的時候,如何將每一行的值賦值給變量進行處理呢?這就要用到游標了,這篇文章就對Oracle中的游標進行詳細的總結。

隱式游標

在Oracle中,游標分為以下兩種:

  • 隱式游標
  • 顯式游標

我們先說說隱士游標。
隱式游標又叫SQL游標,是在執行DML操作和查詢操作返回單條記錄時,由PL/SQL自動、隱藏定義。隱式游標由PL/SQL自動定義、自動打開、自動關閉、不需要用戶的參與;隱式游標的游標名叫SQL。

隱式游標有以下四個屬性,這些游標屬性用於返回DML和查詢操作的信息。

屬性名稱 屬性含義 結果類型 %FOUND 用於判斷DML語句是否改變了行,或者判斷SELECT INTO是否返回了一行數據 布爾類型 %NOTFOUND 和%FOUND的含義相反 布爾類型 %ISOPEN 判斷游標是否打開,對於隱式游標來說,當SQL語句執行完成以後,游標被自動關閉,因此SQL%ISOPEN的值永遠是FALSE 布爾類型 %ROWCOUNT 用於判斷DML語句影響了多少行,或者SELECT INTO返回了多少行 整數

看一個小小的例子:

declare
begin
    delete from xg.sys_spec_tel where tel_num='15034784641';
    if sql%found then
        dbms_output.put_line(sql%rowcount);
        commit; -- 刪除成功了,就提交
    else
        NULL; -- 執行空語句
    end if;
exception
    when others then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);end;

顯式游標

顯式游標用CURSOR...IS命令定義,它可以對查詢語句返回的多條記錄進行處理。顯示游標需要用戶顯示進行定義、顯示打開、從游標取數據和顯示關閉。

  1. 定義游標
    -- 其中,cursor_name是游標的名字,SELECT_statement是查詢語句
    CURSOR cursor_name IS SELECT_statement;

    比如:

    declare
        cursor cSpecTel is select * from xg.sys_spec_tel;begin
        -- Do Somethingend;
  2. 打開游標
    定義了游標,在真正的使用游標之前,我們需要打開游標。具體語法如下:

     

    OPEN cursor_name;
  3. 從游標取數據
    我們使用游標,主要就是為了批量處理數據,從游標中提取數據的語法如下:

     

    FETCH cursor_name INTO variable1[, variable2, ... ];

    使用FETCH命令從游標中提取數據,每提取一次,游標都指向結果集的下一行。其中variable1是變量,從游標中取得的數據就存放在該變量中。

    -- Created on 2015-7-17 by JellyThink 
    declare 
        cursor cSpecTel is select tel_num from xg.sys_spec_tel;
        phone varchar2(13);begin
        dbms_output.enable(1000000); -- 1~1000000
        open cSpecTel;
    
        loop
            fetch cSpecTel into phone;
            exit when cSpecTel%NOTFOUND or cSpecTel%NOTFOUND is null;
            dbms_output.put_line('Special Phone Number:' || phone);
        end loop;   
    
        close cSpecTel;
    exception
        when others then
            dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);
    
            -- 如果發生異常以後,檢查游標是否被關閉
            if cSpecTel%isopen then
                close cSpecTel;
            end if;end;
  4. 關閉游標
    在使用完游標以後,必須要關閉游標,釋放游標占用的系統資源,如果數據庫中存在很多的未關閉的游標,還可能導致數據庫死機。關閉游標的語法如下:

     

    CLOSE cursor_name;

在上面的代碼中,我使用了%NOTFOUND%ISOPEN等顯式游標的屬性,對於顯式游標來說,它也有四大屬性,分別如下:

屬性名稱 屬性含義 結果類型 %FOUND 當最後一次讀(FETCH)記錄成功,則返回TRUE 布爾類型 %NOTFOUND 和%FOUND的含義相反 布爾類型 %ISOPEN 判斷游標是否打開,當游標已打開時返回TRUE 布爾類型 %ROWCOUNT 返回已從游標中讀取的記錄數(到目前為止) 整數

 

游標FOR循環

我們可以清楚的知道,使用顯式游標的標准步驟如下:

  1. 打開游標
  2. 開始循環游標
  3. 從游標取值,並處理數據
  4. 退出循環
  5. 關閉游標

這樣的過程非常繁瑣,因此Oracle中引入了FOR循環。FOR循環把上面的過程融合在一起,忽略了顯式的打開游標、關閉游標、從游標中取數據等過程。使用這種方式,系統隱藏定義了一個%ROWTYPE類型的記錄。游標FOR循環的語法如下所示:

FOR record_name IN (cursor_name) | (query_difinition)
LOOP
    statements
END LOOP;

元素名稱 說明 record_name 記錄的名稱,%ROWTYPE類型的記錄 cursor_name 游標的名稱 query_difinition 如果不指定游標的話,可以指定一個查詢

現在將上面的示例代碼改寫成使用FOR的形式。

-- Created on 2015-7-17 by JellyThink 
declare 
    cursor cSpecTel is select * from xg.sys_spec_tel;begin
    dbms_output.enable(1000000);
    for specTel in cSpecTel
    loop
        dbms_output.put_line('Phone Number:' || specTel.Tel_Num);
    end loop;
exception
    when others then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);end;

這樣寫起來,代碼是不是簡潔多了。

向游標中傳遞參數

可以看到,上面的查詢語句select * from xg.sys_spec_tel都是寫死的。如果我們想動態的根據條件改變查詢,那怎麼辦?這個時候,我們可以利用游標參數動態改變查詢語句。

-- 定義一個根據區號獲得特殊號碼的存儲過程
create or replace procedure p_get_spec_tel(area varchar2)as
    phone varchar2(13);
    areaCode varchar2(5);
    cursor cSpecTel(area1 varchar2) is -- area1就是定義的游標參數
        select a.tel_num, a.area_code
        from XG.SYS_SPEC_TEL a
        where a.area_code=area1;begin
    dbms_output.enable(1000000);

    -- 打開游標時,將參數傳遞進去
    -- 使用FOR隱藏了打開游標的過程,不知道怎麼將參數傳遞進去
    open cSpecTel(area);

    loop
        fetch cSpecTel into phone, areaCode;
        exit when cSpecTel%notfound or cSpecTel%notfound is null;
        dbms_output.put_line('Phone Number:' || phone || '  Area Code:' || areaCode);
    end loop;
    close cSpecTel;
exception
    when others then
        dbms_output.put_line('ERROR ' || SQLCODE || ' ' || SQLERRM);end;

這樣的話,我們在運行存儲過程的過程中,可以傳遞一個參數進去,定義游標的時候,可以指定參數,這樣的話,就可以成功進行動態的指定查詢條件。

游標變量

在下面的代碼中:

cursor cSpecTel is
    select a.tel_num, a.area_code
    from XG.SYS_SPEC_TEL a;

當我們這樣定義了游標以後,這個名為cSpecTel的游標就只對應查詢語句select a.tel_num, a.area_code from XG.SYS_SPEC_TEL a;;也就是說一旦我們定義了這樣的一個游標,這個游標就相當於一個常量了。如果我們想定義一個游標變量,想給這個游標變量賦什麼值就賦什麼值,這該多好。

游標變量就是一個指針,這個指針可以指向不同的查詢工作區;而顯式游標總是指向相同的查詢工作區。簡單的說,游標變量是一個變量,只是這個變量可以動態指向不同的游標,所以游標變量又稱動態游標;而顯式游標又稱靜態游標。游標變量可以作為函數或者存儲過程的參數。

定義游標變量分為兩步(自定義類型都是這樣,C++也是如此):

  1. 定義CURSOR類型的指針
  2. 聲明游標變量

在創建游標變量之前,我們必須定義REF CURSOR類型。定義REF CURSOR類型的語法如下:

TYPE ref_type_name IS REF CURSOR [RETURN return_type]

其中ref_type_name是新類型的名字,是聲明游標變量的類型,這裡實際上定義的是一種新的數據類型。return_type是可選的,用於指定游標變量返回值的類型,它必須是一個記錄類型(RECORD)或者行類型(ROWTYPE),如果有返回類型,則稱之為“強REF CURSOR”;如果沒有返回類型,則稱之為“弱REF CURSOR”。我們使用“弱REF CURSOR”,則允許把游標變量與任何查詢進行關聯;如果使用“強REF CURSOR”,則只允許把游標變量與特定查詢進行關聯。

定義一個強REF CURSOR:

-- 有返回類型
TYPE strongType IS REF CURSOR RETURN specialTel%ROWTYPE;

定義一個弱REF CURSOR:

-- 沒有返回類型
TYPE weakType IS REF CURSOR;

游標類型定義完成以後,我們就可以聲明一個游標變量了。

telnum_cur weekType;

現在截取我們生產庫上的一個例子看看游標變量的具體使用方法。

create or replace procedure cp.insert_accountrent_rate
IS
    type t_cur IS ref cursor; -- 定義一個弱REF CURSOR類型
    rate_cur t_cur; -- 聲明一個游標變量
    sql_stmt varchar2(10240);begin
    sql_stmt :='SELECT curve_id,base_val,rate_val,formula_id,share_num FROM cp.PM_CURVE_SEGMENTS';

    -- 使用open...for...打開這個游標
    open rate_cur for sql_stmt;
    loop
        fetch rate_cur into mrate_id,mbase_val,mrate_val,mformula_id,mshare_num;
        exit when rate_cur%notfound;
    end loop;
    close rate_cur;end;

總結

游標在實際工作中是經常使用的,而且對於批處理來說,使用起來也非常方便,掌握了游標,將會極大的提高你的工作效率。

這篇文章稍長,代碼稍多,希望你能耐心的看完。如果連這麼點文字都看不下去,這麼點耐心都沒有,那你也太浮躁了。最後,希望這篇文章能夠對大家有所幫助。

 
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved