主頁 > 知識庫 > PostgreSQL教程(二十):PL/pgSQL過程語言

PostgreSQL教程(二十):PL/pgSQL過程語言

熱門標簽:海豐有多少商家沒有地圖標注 美容工作室地圖標注 打電話智能電銷機器人授權(quán) 重慶自動外呼系統(tǒng)定制 地圖標注和圖片名稱的區(qū)別 辦公外呼電話系統(tǒng) 外呼調(diào)研系統(tǒng) 漯河外呼電話系統(tǒng) 合肥公司外呼系統(tǒng)運營商

一、概述:

    PL/pgSQL函數(shù)在第一次被調(diào)用時,其函數(shù)內(nèi)的源代碼(文本)將被解析為二進制指令樹,但是函數(shù)內(nèi)的表達式和SQL命令只有在首次用到它們的時候,PL/pgSQL解釋器才會為其創(chuàng)建一個準備好的執(zhí)行規(guī)劃,隨后對該表達式或SQL命令的訪問都將使用該規(guī)劃。如果在一個條件語句中,有部分SQL命令或表達式?jīng)]有被用到,那么PL/pgSQL解釋器在本次調(diào)用中將不會為其準備執(zhí)行規(guī)劃,這樣的好處是可以有效地減少為PL/pgSQL函數(shù)里的語句生成分析和執(zhí)行規(guī)劃的總時間,然而缺點是某些表達式或SQL命令中的錯誤只有在其被執(zhí)行到的時候才能發(fā)現(xiàn)。
    由于PL/pgSQL在函數(shù)里為一個命令制定了執(zhí)行計劃,那么在本次會話中該計劃將會被反復(fù)使用,這樣做往往可以得到更好的性能,但是如果你動態(tài)修改了相關(guān)的數(shù)據(jù)庫對象,那么就有可能產(chǎn)生問題,如:
 

復(fù)制代碼 代碼如下:

    CREATE FUNCTION populate() RETURNS integer AS $$
    DECLARE
        -- 聲明段
    BEGIN
        PERFORM my_function();
    END;
    $$ LANGUAGE plpgsql;
 

    在調(diào)用以上函數(shù)時,PERFORM語句的執(zhí)行計劃將引用my_function對象的OID。在此之后,如果你重建了my_function函數(shù),那么populate函數(shù)將無法再找到原有my_function函數(shù)的OID。要解決該問題,可以選擇重建populate函數(shù),或者重新登錄建立新的會話,以使PostgreSQL重新編譯該函數(shù)。要想規(guī)避此類問題的發(fā)生,在重建my_function時可以使用CREATE OR REPLACE FUNCTION命令。

    鑒于以上規(guī)則,在PL/pgSQL里直接出現(xiàn)的SQL命令必須在每次執(zhí)行時均引用相同的表和字段,換句話說,不能將函數(shù)的參數(shù)用作SQL命令的表名或字段名。如果想繞開該限制,可以考慮使用PL/pgSQL中的EXECUTE語句動態(tài)地構(gòu)造命令,由此換來的代價是每次執(zhí)行時都要構(gòu)造一個新的命令計劃。

    使用PL/pgSQL函數(shù)的一個非常重要的優(yōu)勢是可以提高程序的執(zhí)行效率,由于原有的SQL調(diào)用不得不在客戶端與服務(wù)器之間反復(fù)傳遞數(shù)據(jù),這樣不僅增加了進程間通訊所產(chǎn)生的開銷,而且也會大大增加網(wǎng)絡(luò)IO的開銷。

二、PL/pgSQL的結(jié)構(gòu):

    PL/pgSQL是一種塊結(jié)構(gòu)語言,函數(shù)定義的所有文本都必須在一個塊內(nèi),其中塊中的每個聲明和每條語句都是以分號結(jié)束,如果某一子塊在另外一個塊內(nèi),那么該子塊的END關(guān)鍵字后面必須以分號結(jié)束,不過對于函數(shù)體的最后一個END關(guān)鍵字,分號可以省略,如:
 

復(fù)制代碼 代碼如下:

    [ label>> ]
    [ DECLARE declarations ]
    BEGIN
        statements
    END [ label ];
 

    在PL/pgSQL中有兩種注釋類型,雙破折號(--)表示單行注釋。/* */表示多行注釋,該注釋類型的規(guī)則等同于C語言中的多行注釋。
    在語句塊前面的聲明段中定義的變量在每次進入語句塊(BEGIN)時都會將聲明的變量初始化為它們的缺省值,而不是每次函數(shù)調(diào)用時初始化一次。如:
 
復(fù)制代碼 代碼如下:

    CREATE FUNCTION somefunc() RETURNS integer AS $$
    DECLARE
       quantity integer := 30;
    BEGIN
       RAISE NOTICE 'Quantity here is %', quantity;      --在這里的數(shù)量是30
       quantity := 50;
       --
       -- 創(chuàng)建一個子塊
       --
       DECLARE
          quantity integer := 80;
       BEGIN
          RAISE NOTICE 'Quantity here is %', quantity;   --在這里的數(shù)量是80
       END;
       RAISE NOTICE 'Quantity here is %', quantity;      --在這里的數(shù)量是50   
       RETURN quantity;
    END;
    $$ LANGUAGE plpgsql;
    #執(zhí)行該函數(shù)以進一步觀察其執(zhí)行的結(jié)果。
    postgres=# select somefunc();
    NOTICE:  Quantity here is 30
    NOTICE:  Quantity here is 80
    NOTICE:  Quantity here is 50
     somefunc
    ----------
           50
    (1 row)

    最后需要說明的是,目前版本的PostgreSQL并不支持嵌套事務(wù),函數(shù)中的事物總是由外層命令(函數(shù)的調(diào)用者)來控制的,它們本身無法開始或提交事務(wù)。

三、聲明:

    所有在塊里使用的變量都必須在塊的聲明段里先進行聲明,唯一的例外是FOR循環(huán)里的循環(huán)計數(shù)變量,該變量被自動聲明為整型。變量聲明的語法如下:
 

復(fù)制代碼 代碼如下:

    variable_name [ CONSTANT ] variable_type [ NOT NULL ] [ { DEFAULT | := } expression ];
 

    1). SQL中的數(shù)據(jù)類型均可作為PL/pgSQL變量的數(shù)據(jù)類型,如integer、varchar和char等。
    2). 如果給出了DEFAULT子句,該變量在進入BEGIN塊時將被初始化為該缺省值,否則被初始化為SQL空值。缺省值是在每次進入該塊時進行計算的。因此,如果把now()賦予一個類型為timestamp的變量,那么該變量的缺省值將為函數(shù)實際調(diào)用時的時間,而不是函數(shù)預(yù)編譯時的時間。
    3). CONSTANT選項是為了避免該變量在進入BEGIN塊后被重新賦值,以保證該變量為常量。
    4). 如果聲明了NOT NULL,那么賦予NULL數(shù)值給該變量將導致一個運行時錯誤。因此所有聲明為NOT NULL的變量也必須在聲明時定義一個非空的缺省值。

    1. 函數(shù)參數(shù)的別名:
    傳遞給函數(shù)的參數(shù)都是用$1、$2這樣的標識符來表示的。為了增加可讀性,我們可以為其聲明別名。之后別名和數(shù)字標識符均可指向該參數(shù)值,見如下示例:
    1). 在函數(shù)聲明的同時給出參數(shù)變量名。
 

復(fù)制代碼 代碼如下:

    CREATE FUNCTION sales_tax(subtotal real) RETURNS real AS $$
    BEGIN
        RETURN subtotal * 0.06;
    END;
    $$ LANGUAGE plpgsql;
 

    2). 在聲明段中為參數(shù)變量定義別名。
 
復(fù)制代碼 代碼如下:

    CREATE FUNCTION sales_tax(REAL) RETURNS real AS $$
    DECLARE
        subtotal ALIAS FOR $1;
    BEGIN
        RETURN subtotal * 0.06;
    END;
    $$ LANGUAGE plpgsql;
 

    3). 對于輸出參數(shù)而言,我們?nèi)匀豢梢宰袷?)和2)中的規(guī)則。
 
復(fù)制代碼 代碼如下:

    CREATE FUNCTION sales_tax(subtotal real, OUT tax real) AS $$
    BEGIN
        tax := subtotal * 0.06;
    END;
    $$ LANGUAGE plpgsql;  
 

    4). 如果PL/pgSQL函數(shù)的返回類型為多態(tài)類型(anyelement或anyarray),那么函數(shù)就會創(chuàng)建一個特殊的參數(shù):$0。我們?nèi)匀豢梢詾樵撟兞吭O(shè)置別名。
 
復(fù)制代碼 代碼如下:

    CREATE FUNCTION add_three_values(v1 anyelement, v2 anyelement, v3 anyelement)
    RETURNS anyelement AS $$
    DECLARE
        result ALIAS FOR $0;
    BEGIN
        result := v1 + v2 + v3;
        RETURN result;
    END;
    $$ LANGUAGE plpgsql;
   

    2. 拷貝類型:
    見如下形式的變量聲明:
 
復(fù)制代碼 代碼如下:

    variable%TYPE
 

    %TYPE表示一個變量或表字段的數(shù)據(jù)類型,PL/pgSQL允許通過該方式聲明一個變量,其類型等同于variable或表字段的數(shù)據(jù)類型,見如下示例:
 
復(fù)制代碼 代碼如下:

    user_id users.user_id%TYPE;
 

    在上面的例子中,變量user_id的數(shù)據(jù)類型等同于users表中user_id字段的類型。
    通過使用%TYPE,一旦引用的變量類型今后發(fā)生改變,我們也無需修改該變量的類型聲明。最后需要說明的是,我們可以在函數(shù)的參數(shù)和返回值中使用該方式的類型聲明。

    3. 行類型:
    見如下形式的變量聲明:
 

復(fù)制代碼 代碼如下:

    name table_name%ROWTYPE;
    name composite_type_name;
 

    table_name%ROWTYPE表示指定表的行類型,我們在創(chuàng)建一個表的時候,PostgreSQL也會隨之創(chuàng)建出一個與之相應(yīng)的復(fù)合類型,該類型名等同于表名,因此,我們可以通過以上兩種方式來聲明行類型的變量。由此方式聲明的變量,可以保存SELECT返回結(jié)果中的一行。如果要訪問變量中的某個域字段,可以使用點表示法,如rowvar.field,但是行類型的變量只能訪問自定義字段,無法訪問系統(tǒng)提供的隱含字段,如OID等。對于函數(shù)的參數(shù),我們只能使用復(fù)合類型標識變量的數(shù)據(jù)類型。最后需要說明的是,推薦使用%ROWTYPE的聲明方式,這樣可以具有更好的可移植性,因為在Oracle的PL/SQL中也存在相同的概念,其聲明方式也為%ROWTYPE。見如下示例:
 
復(fù)制代碼 代碼如下:

    CREATE FUNCTION merge_fields(t_row table1) RETURNS text AS $$
    DECLARE
        t2_row table2%ROWTYPE;
    BEGIN
        SELECT * INTO t2_row FROM table2 WHERE id = 1 limit 1;
        RETURN t_row.f1 || t2_row.f3 || t_row.f5 || t2_row.f7;
    END;
    $$ LANGUAGE plpgsql;

    4. 記錄類型:
    見如下形式的變量聲明:
 
復(fù)制代碼 代碼如下:

    name RECORD;
 

    記錄變量類似于行類型變量,但是它們沒有預(yù)定義的結(jié)構(gòu),只能通過SELECT或FOR命令來獲取實際的行結(jié)構(gòu),因此記錄變量在被初始化之前無法訪問,否則將引發(fā)運行時錯誤。
    注:RECORD不是真正的數(shù)據(jù)類型,只是一個占位符。

四、基本語句:

    1. 賦值:
    PL/pgSQL中賦值語句的形式為:identIFier := expression,等號兩端的變量和表達式的類型或者一致,或者可以通過PostgreSQL的轉(zhuǎn)換規(guī)則進行轉(zhuǎn)換,否則將會導致運行時錯誤,見如下示例:
 

復(fù)制代碼 代碼如下:

    user_id := 20;
    tax := subtotal * 0.06;
   

    2. SELECT INTO:
    通過該語句可以為記錄變量或行類型變量進行賦值,其表現(xiàn)形式為:SELECT INTO target select_expressions FROM ...,該賦值方式一次只能賦值一個變量。表達式中的target可以表示為是一個記錄變量、行變量,或者是一組用逗號分隔的簡單變量和記錄/行字段的列表。select_expressions以及剩余部分和普通SQL一樣。
    如果將一行或者一個變量列表用做目標,那么選出的數(shù)值必需精確匹配目標的結(jié)構(gòu),否則就會產(chǎn)生運行時錯誤。如果目標是一個記錄變量,那么它自動將自己構(gòu)造成命令結(jié)果列的行類型。如果命令返回零行,目標被賦予空值。如果命令返回多行,那么將只有第一行被賦予目標,其它行將被忽略。在執(zhí)行SELECT INTO語句之后,可以通過檢查內(nèi)置變量FOUND來判斷本次賦值是否成功,如:
 
復(fù)制代碼 代碼如下:

    SELECT INTO myrec * FROM emp WHERE empname = myname;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'employee % not found', myname;
    END IF;
 

    要測試一個記錄/行結(jié)果是否為空,可以使用IS NULL條件進行判斷,但是對于返回多條記錄的情況則無法判斷,如:
 
復(fù)制代碼 代碼如下:

    DECLARE
        users_rec RECORD;
    BEGIN
        SELECT INTO users_rec * FROM users WHERE user_id = 3;
        IF users_rec.homepage IS NULL THEN
            RETURN 'http://';
        END IF;
    END;
 

   
    3. 執(zhí)行一個沒有結(jié)果的表達式或者命令:
    在調(diào)用一個表達式或執(zhí)行一個命令時,如果對其返回的結(jié)果不感興趣,可以考慮使用PERFORM語句:PERFORM query,該語句將執(zhí)行PERFORM之后的命令并忽略其返回的結(jié)果。其中query的寫法和普通的SQL SELECT命令是一樣的,只是把開頭的關(guān)鍵字SELECT替換成PERFORM,如:
 
復(fù)制代碼 代碼如下:

    PERFORM create_mv('cs_session_page_requests_mv', my_query);

    4. 執(zhí)行動態(tài)命令:
    如果在PL/pgSQL函數(shù)中操作的表或數(shù)據(jù)類型在每次調(diào)用該函數(shù)時都可能會發(fā)生變化,在這樣的情況下,可以考慮使用PL/pgSQL提供的EXECUTE語句:EXECUTE command-string [ INTO target ],其中command-string是用一段文本表示的表達式,它包含要執(zhí)行的命令。而target是一個記錄變量、行變量或者一組用逗號分隔的簡單變量和記錄/行域的列表。這里需要特別注意的是,該命令字符串將不會發(fā)生任何PL/pgSQL變量代換,變量的數(shù)值必需在構(gòu)造命令字符串時插入到該字符串中。

    和所有其它PL/pgSQL命令不同的是,一個由EXECUTE語句運行的命令在服務(wù)器內(nèi)并不會只prepare和保存一次。相反,該語句在每次運行的時候,命令都會prepare一次。因此命令字符串可以在函數(shù)里動態(tài)的生成以便于對各種不同的表和字段進行操作,從而提高函數(shù)的靈活性。然而由此換來的卻是性能上的折損。見如下示例:
 

復(fù)制代碼 代碼如下:

    EXECUTE 'UPDATE tbl SET ' || quote_ident(columnname) || ' = ' || quote_literal(newvalue);

五、控制結(jié)構(gòu):

    1. 函數(shù)返回:

    1). RETURN expression
    該表達式用于終止當前的函數(shù),然后再將expression的值返回給調(diào)用者。如果返回簡單類型,那么可以使用任何表達式,同時表達式的類型也將被自動轉(zhuǎn)換成函數(shù)的返回類型,就像我們在賦值中描述的那樣。如果要返回一個復(fù)合類型的數(shù)值,則必須讓表達式返回記錄或者匹配的行變量。
    2). RETURN NEXT expression
    如果PL/pgSQL函數(shù)聲明為返回SETOF sometype,其行記錄是通過RETURN NEXT命令進行填充的,直到執(zhí)行到不帶參數(shù)的RETURN時才表示該函數(shù)結(jié)束。因此對于RETURN NEXT而言,它實際上并不從函數(shù)中返回,只是簡單地把表達式的值保存起來,然后繼續(xù)執(zhí)行PL/pgSQL函數(shù)里的下一條語句。隨著RETURN NEXT命令的迭代執(zhí)行,結(jié)果集最終被建立起來。該類函數(shù)的調(diào)用方式如下:
    SELECT * FROM some_func();
    它被放在FROM子句中作為數(shù)據(jù)源使用。最后需要指出的是,如果結(jié)果集數(shù)量很大,那么通過該種方式來構(gòu)建結(jié)果集將會導致極大的性能損失。

    2. 條件:
    在PL/pgSQL中有以下三種形式的條件語句。

    1). IF-THEN
 

復(fù)制代碼 代碼如下:

    IF boolean-expression THEN
        statements
    END IF;   
 

    2). IF-THEN-ELSE
 
復(fù)制代碼 代碼如下:

    IF boolean-expression THEN
        statements
    ELSE
        statements
    END IF;
 

    3). IF-THEN-ELSIF-ELSE
 
復(fù)制代碼 代碼如下:

    IF boolean-expression THEN
        statements
    ELSIF boolean-expression THEN
        statements
    ELSIF boolean-expression THEN
        statements
    ELSE
        statements
    END IF;   
 

    關(guān)于條件語句,這里就不在做過多的贅述了。

    3. 循環(huán):
    1). LOOP
 

復(fù)制代碼 代碼如下:

    LOOP
        statements
    END LOOP [ label ];
 

    LOOP定義一個無條件的循環(huán),直到由EXIT或者RETURN語句終止??蛇x的label可以由EXIT和CONTINUE語句使用,用于在嵌套循環(huán)中聲明應(yīng)該應(yīng)用于哪一層循環(huán)。
    2). EXIT
 
復(fù)制代碼 代碼如下:

    EXIT [ label ] [ WHEN expression ];
 

    如果沒有給出label,就退出最內(nèi)層的循環(huán),然后執(zhí)行跟在END LOOP后面的語句。如果給出label,它必須是當前或更高層的嵌套循環(huán)塊或語句塊的標簽。之后該命名塊或循環(huán)就會終止,而控制則直接轉(zhuǎn)到對應(yīng)循環(huán)/塊的END語句后面的語句上。
    如果聲明了WHEN,EXIT命令只有在expression為真時才被執(zhí)行,否則將直接執(zhí)行EXIT后面的語句。見如下示例:
 
復(fù)制代碼 代碼如下:

    LOOP
        -- do something
        EXIT WHEN count > 0;
    END LOOP;
 

    3). CONTINUE
 
復(fù)制代碼 代碼如下:

    CONTINUE [ label ] [ WHEN expression ];
 

    如果沒有給出label,CONTINUE就會跳到最內(nèi)層循環(huán)的開始處,重新進行判斷,以決定是否繼續(xù)執(zhí)行循環(huán)內(nèi)的語句。如果指定label,則跳到該label所在的循環(huán)開始處。如果聲明了WHEN,CONTINUE命令只有在expression為真時才被執(zhí)行,否則將直接執(zhí)行CONTINUE后面的語句。見如下示例:
 
復(fù)制代碼 代碼如下:

    LOOP
        -- do something
        EXIT WHEN count > 100;
        CONTINUE WHEN count 50;
    END LOOP;   
 

    4). WHILE
 
復(fù)制代碼 代碼如下:

    [ label>> ]
    WHILE expression LOOP
        statements
    END LOOP [ label ];
 

    只要條件表達式為真,其塊內(nèi)的語句就會被循環(huán)執(zhí)行。條件是在每次進入循環(huán)體時進行判斷的。見如下示例:
 
復(fù)制代碼 代碼如下:

    WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP
        --do something
    END LOOP;
 

    5). FOR
 
復(fù)制代碼 代碼如下:

    [ label>> ]
    FOR name IN [ REVERSE ] expression .. expression LOOP
        statements
    END LOOP [ label ];
 

    變量name自動被定義為integer類型,其作用域僅為FOR循環(huán)的塊內(nèi)。表示范圍上下界的兩個表達式只在進入循環(huán)時計算一次。每次迭代name值自增1,但如果聲明了REVERSE,name變量在每次迭代中將自減1,見如下示例:
 
復(fù)制代碼 代碼如下:

    FOR i IN 1..10 LOOP
        --do something
        RAISE NOTICE 'i IS %', i;
    END LOOP;
   
    FOR i IN REVERSE 10..1 LOOP
        --do something
    END LOOP;   
   

    4. 遍歷命令結(jié)果:
 
復(fù)制代碼 代碼如下:

    [ label>> ]
    FOR record_or_row IN query LOOP
        statements
    END LOOP [ label ];
 

    這是另外一種形式的FOR循環(huán),在該循環(huán)中可以遍歷命令的結(jié)果并操作相應(yīng)的數(shù)據(jù),見如下示例:
 
復(fù)制代碼 代碼如下:

    FOR rec IN SELECT * FROM some_table LOOP
        PERFORM some_func(rec.one_col);
    END LOOP;
 

    PL/pgSQL還提供了另外一種遍歷命令結(jié)果的方式,和上面的方式相比,唯一的差別是該方式將SELECT語句存于字符串文本中,然后再交由EXECUTE命令動態(tài)的執(zhí)行。和前一種方式相比,該方式的靈活性更高,但是效率較低。
 
復(fù)制代碼 代碼如下:

    [ label>> ]
    FOR record_or_row IN EXECUTE text_expression LOOP
        statements
    END LOOP [ label ];
   

    5. 異常捕獲:
    在PL/pgSQL函數(shù)中,如果沒有異常捕獲,函數(shù)會在發(fā)生錯誤時直接退出,與其相關(guān)的事物也會隨之回滾。我們可以通過使用帶有EXCEPTION子句的BEGIN塊來捕獲異常并使其從中恢復(fù)。見如下聲明形式:
 
復(fù)制代碼 代碼如下:

    [ label>> ]
    [ DECLARE
        declarations ]
    BEGIN
        statements
    EXCEPTION
        WHEN condition [ OR condition ... ] THEN
            handler_statements
        WHEN condition [ OR condition ... ] THEN
            handler_statements
    END;
 

    如果沒有錯誤發(fā)生,只有BEGIN塊中的statements會被正常執(zhí)行,然而一旦這些語句中有任意一條發(fā)生錯誤,其后的語句都將被跳過,直接跳轉(zhuǎn)到EXCEPTION塊的開始處。此時系統(tǒng)將搜索異常條件列表,尋找匹配該異常的第一個條件,如果找到匹配,則執(zhí)行相應(yīng)的handler_statements,之后再執(zhí)行END的下一條語句。如果沒有找到匹配,該錯誤就會被繼續(xù)向外拋出,其結(jié)果與沒有EXCEPTION子句完全等同。如果此時handler_statements中的語句發(fā)生新錯誤,它將不能被該EXCEPTION子句捕獲,而是繼續(xù)向外傳播,交由其外層的EXCEPTION子句捕獲并處理。見如下示例:
 
復(fù)制代碼 代碼如下:

    INSERT INTO mytab(firstname, lastname) VALUES('Tom', 'Jones');
    BEGIN
        UPDATE mytab SET firstname = 'Joe' WHERE lastname = 'Jones';
        x := x + 1;
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE 'caught division_by_zero';
            RETURN x;
    END;
 

    當以上函數(shù)執(zhí)行到y(tǒng) := x / 0語句時,將會引發(fā)一個異常錯誤,代碼將跳轉(zhuǎn)到EXCEPTION塊的開始處,之后系統(tǒng)會尋找匹配的異常捕捉條件,此時division_by_zero完全匹配,這樣該條件內(nèi)的代碼將會被繼續(xù)執(zhí)行。需要說明的是,RETURN語句中返回的x值為x := x + 1執(zhí)行后的新值,但是在除零之前的update語句將會被回滾,BEGIN之前的insert語句將仍然生效。
  
六、游標:

    1. 聲明游標變量:

    在PL/pgSQL中對游標的訪問都是通過游標變量實現(xiàn)的,其數(shù)據(jù)類型為refcursor。 創(chuàng)建游標變量的方法有以下兩種:

    1). 和聲明其他類型的變量一樣,直接聲明一個游標類型的變量即可。
    2). 使用游標專有的聲明語法,如:
 

復(fù)制代碼 代碼如下:

    name CURSOR [ ( arguments ) ] FOR query;
 

    其中arguments為一組逗號分隔的name datatype列表,見如下示例:
 
復(fù)制代碼 代碼如下:

    curs1 refcursor;
    curs2 CURSOR FOR SELECT * FROM tenk1;
    curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;
 

    在上面三個例子中,只有第一個是未綁定游標,剩下兩個游標均已被綁定。

    2. 打開游標:

    游標在使用之前必須先被打開,在PL/pgSQL中有三種形式的OPEN語句,其中兩種用于未綁定的游標變量,另外一種用于綁定的游標變量。

    1). OPEN FOR:

    其聲明形式為:
 

復(fù)制代碼 代碼如下:

    OPEN unbound_cursor FOR query;
 

    該形式只能用于未綁定的游標變量,其查詢語句必須是SELECT,或其他返回記錄行的語句,如EXPLAIN。在PostgreSQL中,該查詢和普通的SQL命令平等對待,即先替換變量名,同時也將該查詢的執(zhí)行計劃緩存起來,以供后用。見如下示例:
 
復(fù)制代碼 代碼如下:

    OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
 

    2). OPEN FOR EXECUTE
    其聲明形式為:
 
復(fù)制代碼 代碼如下:

    OPEN unbound_cursor FOR EXECUTE query-string;  
 

    和上面的形式一樣,該形式也僅適用于未綁定的游標變量。EXECUTE將動態(tài)執(zhí)行其后以文本形式表示的查詢字符串。
 
復(fù)制代碼 代碼如下:

    OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
 

    3). 打開一個綁定的游標
    其聲明形式為:
 
復(fù)制代碼 代碼如下:

    OPEN bound_cursor [ ( argument_values ) ];  
 

    該形式僅適用于綁定的游標變量,只有當該變量在聲明時包含接收參數(shù),才能以傳遞參數(shù)的形式打開該游標,這些參數(shù)將被實際代入到游標聲明的查詢語句中,見如下示例:
 
復(fù)制代碼 代碼如下:

    OPEN curs2;
    OPEN curs3(42);
 

    3. 使用游標:
    游標一旦打開,就可以按照以下方式進行讀取。然而需要說明的是,游標的打開和讀取必須在同一個事物內(nèi),因為在PostgreSQL中,如果事物結(jié)束,事物內(nèi)打開的游標將會被隱含的關(guān)閉。

    1). FETCH
    其聲明形式為:
 

復(fù)制代碼 代碼如下:

    FETCH cursor INTO target;
 

    FETCH命令從游標中讀取下一行記錄的數(shù)據(jù)到目標中,其中目標可以是行變量、記錄變量,或者是一組逗號分隔的普通變量的列表,讀取成功與否,可通過PL/pgSQL內(nèi)置變量FOUND來判斷,其規(guī)則等同于SELECT INTO。見如下示例:
 
復(fù)制代碼 代碼如下:

    FETCH curs1 INTO rowvar;  --rowvar為行變量
    FETCH curs2 INTO foo, bar, baz;
 

    2). CLOSE
    其聲明形式為:
 
復(fù)制代碼 代碼如下:

    CLOSE cursor;
 

    關(guān)閉當前已經(jīng)打開的游標,以釋放其占有的系統(tǒng)資源,見如下示例:
 
復(fù)制代碼 代碼如下:

    CLOSE curs1;
 

七、錯誤和消息:

    在PostgreSQL中可以利用RAISE語句報告信息和拋出錯誤,其聲明形式為:
 

復(fù)制代碼 代碼如下:

    RAISE level 'format' [, expression [, ...]];
 

    這里包含的級別有DEBUG(向服務(wù)器日志寫信息)、LOG(向服務(wù)器日志寫信息,優(yōu)先級更高)、INFO、NOTICE和WARNING(把信息寫到服務(wù)器日志以及轉(zhuǎn)發(fā)到客戶端應(yīng)用,優(yōu)先級逐步升高)和EXCEPTION拋出一個錯誤(通常退出當前事務(wù))。某個優(yōu)先級別的信息是報告給客戶端還是寫到服務(wù)器日志,還是兩個均有,是由log_min_messages和client_min_messages這兩個系統(tǒng)初始化參數(shù)控制的。
    在format部分中,%表示為占位符,其實際值僅在RAISE命令執(zhí)行時由后面的變量替換,如果要在format中表示%自身,可以使用%%的形式表示,見如下示例:
 
復(fù)制代碼 代碼如下:

    RAISE NOTICE 'Calling cs_create_job(%)',v_job_id;  --v_job_id變量的值將替換format中的%。
    RAISE EXCEPTION 'Inexistent ID --> %',user_id;  
 

   

您可能感興趣的文章:
  • pgsql查詢優(yōu)化之模糊查詢實例詳解
  • C#使用EF連接PGSql數(shù)據(jù)庫的完整步驟
  • 簡單的pgsql pdo php操作類實現(xiàn)代碼
  • MySQL鎖阻塞的深入分析
  • mysql查看死鎖與去除死鎖示例詳解
  • Mysql查看死鎖與解除死鎖的深入講解
  • mysql插入前判斷數(shù)據(jù)是否存在的操作
  • PgSQl臨時表創(chuàng)建及應(yīng)用實例解析

標簽:來賓 烏海 衡陽 晉城 蚌埠 株洲 珠海 錦州

巨人網(wǎng)絡(luò)通訊聲明:本文標題《PostgreSQL教程(二十):PL/pgSQL過程語言》,本文關(guān)鍵詞  PostgreSQL,教程,二十,pgSQL,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題,煩請?zhí)峁┫嚓P(guān)信息告之我們,我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《PostgreSQL教程(二十):PL/pgSQL過程語言》相關(guān)的同類信息!
  • 本頁收集關(guān)于PostgreSQL教程(二十):PL/pgSQL過程語言的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章