• <ins id="pjuwb"></ins>
    <blockquote id="pjuwb"><pre id="pjuwb"></pre></blockquote>
    <noscript id="pjuwb"></noscript>
          <sup id="pjuwb"><pre id="pjuwb"></pre></sup>
            <dd id="pjuwb"></dd>
            <abbr id="pjuwb"></abbr>

            Jiang's C++ Space

            創(chuàng)作,也是一種學(xué)習(xí)的過程。

               :: 首頁 :: 新隨筆 :: 聯(lián)系 :: 聚合  :: 管理 ::

            [20060407發(fā)表于blog.csdn.net,20090929重新編輯]

            重新編輯注釋:由于太久沒有使用PL/SQL,如今再看這篇筆記,幾乎完全陌生了,自己還到baidu去搜索了一下PL/SQL和Pro*C才依稀記起,我不知道以后還有沒有機(jī)會使用PL/SQL,但無疑的是這篇筆記還是有點(diǎn)價(jià)值的,或者作為一個紀(jì)念吧。PL/SQL(準(zhǔn)確的寫法就是“PL/SQL”,沒有別的,不是Plus SQL,也不是SQL Plus)是Oracle對SQL的擴(kuò)展,也就是說,PL/SQL只能用于Oracle,所以不要指望代碼能移植給MS SQL Server用,另外我印象比較深刻的是PL/SQL的語法很不嚴(yán)謹(jǐn),bug比較多,用之前請三思,再有一點(diǎn),這些代碼怎么用?就像C語言,要有編譯連接器生成可執(zhí)行文件一樣,這些PL/SQL代碼也是需要編譯器的,不用說,它的編譯器一定是Oracle提供的,所以你得先安裝Oracle的客戶端,利用上面的調(diào)試工具調(diào)試。我以前是在Pro*C(在C代碼中嵌入PL/SQL,利用Oracle提供的預(yù)編譯生成C,然后才使用C的編譯器進(jìn)行下去)中使用PL/SQL的。

            程序不區(qū)分大小寫

            1、SQL*Plus

            SQL*Plus(Oracle客戶端提供)這個程序可以用來執(zhí)行腳本,執(zhí)行腳本前先設(shè)置假脫機(jī)文件,這樣就可以方便地查看出錯之處了。或者使用PL/SQL Developer的Command窗口來執(zhí)行腳本。

            2、代碼中變量的聲明

            set serveroutput on;                      --設(shè)置輸出
            DECLARE
                lv_ord_date DATE;                     
            --日期類型
                lv_last_txt VARCHAR2(25):='Jiang';    --可變字符串類型,并初始化
                lv_qty_num CONSTANT NUMBER(2):=20;    --2位整數(shù),常量,初始化
                lv_shipflag_bln BOOLEAN:=FALSE;       --布爾型,初始化為FALSE
                lv_name_txt CHAR(10NOT NULL:='CCC'--定長字符串類型,不能為空,初始化
            BEGIN
                dbms_output.put_line (lv_qty_num);    
            --這里一定要寫點(diǎn)什么東西才行
            END;

            3、簡單查詢

            declare
                lv_basket_num 
            number(3);
                lv_created_date date;
                lv_qty_num 
            number(2);
                lv_sub_num 
            number(5,2);
                lv_days_num 
            number(3);
                lv_shopper_num 
            number(3):=25;
            begin
                
            select idbasket, dtcreated, quantity, subtotal
                
            into lv_basket_num, lv_created_date, lv_qty_num, lv_sub_num
                
            from bb_basket
                
            where idshopper = lv_shopper_num
                    
            and orderplaced = 0;
                lv_days_num:
            =sysdate-lv_created_date;
                dbms_output.put_line(lv_basket_num
            ||' * '||lv_created_date
                    
            ||' * '||lv_qty_num||' * '||lv_sub_num||' * '||lv_days_num);
            end;

            4、%type的使用

            declare
                lv_basket_num bb_basket.idbasket
            %type;   --與bb_basket.idbasket字段同類型
                lv_created_date bb_basket.dtcreated%type;
                lv_qty_num bb_basket.quantity
            %type;
                lv_sub_num bb_basket.subtotal
            %type;
                lv_days_num 
            number(3);
            begin
                
            select idbasket, dtcreated, quantity, subtotal
                
            into lv_basket_num, lv_created_date, lv_qty_num,lv_sub_num
                
            from bb_basket
                
            where idshopper=25
                
            and orderplaced = 0;
                lv_days_num:
            = sysdate-lv_created_date;
                dbms_output.put_line(lv_basket_num 
            || ' * ' ||
                    lv_created_date 
            || ' * ' || lv_qty_num || ' * ' ||
                    lv_sub_num 
            || ' * ' || lv_days_num);
            end;

            5、附加說明

            要執(zhí)行declare聲明的過程,需要使用“/”。而其它不需要。
            在SQL*Plus窗口中直接鍵入“/”,表示執(zhí)行最近declare的過程。

            6、復(fù)合數(shù)據(jù)類型:RECORD

            DECLARE 
                TYPE type_basket 
            IS RECORD (
                    basket bb_basket.idBasket
            %TYPE,
                    created bb_basket.dtcreated
            %TYPE,
                    qty bb_basket.quantity
            %TYPE,
                    sub bb_basket.subtotal
            %TYPE);
                rec_basket type_basket;
                lv_days_num 
            NUMBER(3);
                lv_shopper_num 
            NUMBER(3) := 25;
            BEGIN
                
            SELECT idBasket, dtcreated, quantity, subtotal
                
            INTO rec_basket
                
            FROM bb_basket
                
            WHERE idShopper = lv_shopper_num 
                    
            AND orderplaced = 0;
                lv_days_num :
            = SYSDATE - rec_basket.created;
                DBMS_OUTPUT.PUT_LINE(rec_basket.basket);
                DBMS_OUTPUT.PUT_LINE(rec_basket.created);
                DBMS_OUTPUT.PUT_LINE(rec_basket.qty);
                DBMS_OUTPUT.PUT_LINE(rec_basket.sub);
                DBMS_OUTPUT.PUT_LINE(lv_days_num);
            END;

            7、復(fù)合數(shù)據(jù)類型:%ROWTYPE的使用

            DECLARE 
                rec_shopper bb_shopper
            %rowtype;
            BEGIN
                
            SELECT *
                
            INTO rec_shopper
                
            FROM bb_shopper
                
            WHERE idShopper = 25;
                DBMS_OUTPUT.PUT_LINE(rec_shopper.lastname);
                DBMS_OUTPUT.PUT_LINE(rec_shopper.address);
                DBMS_OUTPUT.PUT_LINE(rec_shopper.email);
            END;

            “%rowtype”能根據(jù)表的結(jié)構(gòu)自動創(chuàng)建rec_shopper這種數(shù)據(jù)類型,十分方便。

            8、復(fù)合數(shù)據(jù)類型:記錄集

            定義成“is table”就想當(dāng)于一個記錄集,存在行數(shù)。
            另外:聲明全局變量:

            SQL>VARIABLE g_row NUMBER

            注意,不需要“;”,也不需要“/”,使用變量的時(shí)候前面加上“:”。

            variable g_row number
            variable g_prod 
            number
            variable g_price 
            number
            variable g_qty 
            number
            variable g_opt1 
            number
            variable g_opt2 
            number
            begin
                :g_row :
            = 1;
                :g_prod :
            = 7;
                :g_price :
            = 10.8;
                :g_qty :
            = 2;
                :g_opt1 :
            = 2;
                :g_opt2 :
            = 3;
            end;
            /
             
            declare
                type type_basketitem 
            is table of bb_basketitem%rowtype
                    
            index by binary_integer;
                tbl_basketitems type_basketitem;
            begin
                tbl_basketitems(:g_row).idproduct :
            = :g_prod;
                tbl_basketitems(:g_row).price :
            = :g_price;
                tbl_basketitems(:g_row).quantity :
            = :g_qty;
                tbl_basketitems(:g_row).option1 :
            = :g_opt1;
                tbl_basketitems(:g_row).option2 :
            = :g_opt2;
                dbms_output.put_line(:g_row);
                :g_row :
            = :g_row + 1;
                dbms_output.put_line(:g_row);
                dbms_output.put_line(:g_prod);
            end;
            /

            9、隱式游標(biāo)SQL%ROWCOUNT SQL%FOUND SQL%NOTFOUND

            begin
                
            update bb_product
                
            set stock = stock + 25
                
            where idProduct in (123);
                
            if sql%notfound then
                    dbms_output.put_line(
            'Not found.');
                
            end if;
                
            if sql%found then
                    dbms_output.put_line(
            'Completed '||sql%rowcount||' rows.');
                
            end if;
            end;
            /

            10、游標(biāo)的使用

            variable g_basket number
            begin
                :g_basket:
            =6;
            end;
            /

            declare
                
            cursor cur_basket is
                    
            select bi.idbasket, p.type, bi.price, bi.quantity
                    
            from bb_basketitem bi inner join bb_product p on bi.idproduct=p.idproduct
                    
            where bi.idbasket = :g_basket;
                type type_basket 
            is record
                (
                    basket bb_basketitem.idbasket
            %type,
                    type bb_product.type
            %type,
                    price bb_basketitem.price
            %type,
                    qty bb_basketitem.quantity
            %type
                );
                rec_basket type_basket;
                lv_rate_num 
            number(2,2);
                lv_tax_num 
            number(4,2):=0;
            begin
                
            open cur_basket;
                loop
                    
            fetch cur_basket into rec_basket;
                    
            exit when cur_basket%notfound;
                    dbms_output.put_line(
            'Every record:' || rec_basket.basket||' '||
                        rec_basket.type
            ||' '||rec_basket.price||' '||rec_basket.qty);
                    
            if rec_basket.type = 'E' then
                        lv_rate_num :
            = .05;
                    
            end if;
                    
            if rec_basket.type = 'C' then
                        lv_rate_num :
            = .03;
                    
            end if;
                    lv_tax_num :
            = lv_tax_num +((rec_basket.price * rec_basket.qty) * 
                        lv_rate_num);
                
            end loop;
                
            close cur_basket;
                dbms_output.put_line(lv_tax_num);
            end;
            /

            游標(biāo)除了上面的直接使用loop...end loop與fetch的結(jié)合來使用記錄集之外,還有下面的方式:

            declare
                
            cursor cur_prod is
                    
            select type, price
                    
            from bb_product
                    
            where active=1
                    
            for update nowait; 
                    
            --另一會話已經(jīng)鎖定了游標(biāo)正在檢索的行時(shí),不必等待,繼續(xù)往下。
                    --另外可寫成:for update of type, price nowait;指定鎖定的列。
                lv_sale bb_product.saleprice%type;
            begin
                
            for rec_prod in cur_prod loop
                    
            if rec_prod.type = 'C' then
                        lv_sale:
            =rec_prod.price * 0.8;
                    
            end if;
                    
            if rec_prod.type = 'E' then
                        lv_sale :
            = rec_prod.price * 0.85;
                    
            end if;
                    
            update bb_product
                        
            set saleprice = lv_sale
                        
            where current of cur_prod;
                    
            --dbms_output.put_line('Updated record:' || sql%rowcount);
                end loop;
                
            commit;
            end;

            上面兩個例子都是在declare中聲明并定義游標(biāo),下面的例子則是:仍然在declare中聲明,但是定義放在begin...end中。

            declare
                type type_curvar 
            is ref cursor;
                cv_prod type_curvar;
                rec_basket bb_basket
            %rowtype;
                rec_shipping bb_shipping
            %rowtype;
            begin
                dbms_output.put_line(
            '################ bb_basket ################');
                
            open cv_prod for select * from bb_basket;
                loop
                    
            fetch cv_prod into rec_basket;
                    
            exit when cv_prod%notfound;
                    dbms_output.put_line(rec_basket.idbasket 
            || ' ' || 
                        rec_basket.quantity 
            || ' ' || rec_basket.idshopper || ' ' || 
                        rec_basket.orderplaced);
                
            end loop;
                dbms_output.put_line(
            '############### bb_shipping ###############');
                
            open cv_prod for select * from bb_shipping;
                loop
                    
            fetch cv_prod into rec_shipping;
                    
            exit when cv_prod%notfound;
                    dbms_output.put_line(rec_shipping.idrange 
            || ' ' || 
                        rec_shipping.low 
            || ' ' || rec_shipping.high || ' ' || 
                        rec_shipping.fee);
                
            end loop;
            end;

            關(guān)于游標(biāo)的,講了好多啊……沒辦法,游標(biāo)就是很重要的概念。

            11、變量定義與范圍示例

            declare
                lv_one 
            number(2):=10;
                lv_two 
            number(2):=20;
            begin
                
            declare
                    lv_one 
            number(2):=30;
                    lv_three 
            number(2):=40;
                
            begin
                    lv_one:
            =lv_one+10;
                    lv_two:
            =lv_two+10;
                    dbms_output.put_line(
            'Nested lv_one='||lv_one);
                    dbms_output.put_line(
            'Nested lv_two='||lv_two);
                    dbms_output.put_line(
            'Nested lv_three='||lv_three);
                
            end;
                lv_one:
            =lv_one+10;
                lv_two:
            =lv_two+10;
                dbms_output.put_line(
            'Enclosing lv_one='||lv_one);
                dbms_output.put_line(
            'Enclosing lv_two='||lv_two);
            end;

            塊頭declare處定義的變量只在當(dāng)塊中生效。如果塊頭沒有定義,但塊體中使用了,程序就把變量當(dāng)作外部變量。

            12、if/elsif語句

            非常有意思,這里用的不是elseif,而是elsif,千萬別寫錯了。

            declare
                type type_order 
            is record(
                    basket bb_basket.idbasket
            %type,
                    sub bb_basket.subtotal
            %type,
                    state bb_basket.shipstate
            %type);
                rec_order type_order;
                lv_tax_num 
            number(4,2):=0;
            begin
                
            select idbasket, subtotal, shipstate
                    
            into rec_order
                    
            from bb_basket
                    
            where idbasket=6;
                
            if rec_order.state='VA' then
                    lv_tax_num:
            =rec_order.sub*0.06;
                elsif rec_order.state
            ='ME' then
                    lv_tax_num:
            =rec_order.sub*0.05;
                elsif rec_order.state
            ='NY' then
                    lv_tax_num:
            =rec_order.sub*0.07;
                
            else
                    lv_tax_num:
            =rec_order.sub*0.04;
                
            end if;
                dbms_output.put_line(
            'State='||rec_order.state);
                dbms_output.put_line(
            'Subtotal='||rec_order.sub);
                dbms_output.put_line(
            'Tax amount='||lv_tax_num);
            end;

            if語句也可以使用or,and,in等運(yùn)算符。例子這里就不舉了。

            13、循環(huán) loop,for,while

            理所當(dāng)然了,講完分支就講循環(huán),一般的高級語言都這樣的。

            declare
                lv_cnt_num 
            number(2):=1;
            begin
                loop
                    dbms_output.put_line(lv_cnt_num);
                    
            exit when lv_cnt_num>5;
                    lv_cnt_num:
            =lv_cnt_num+1;
                
            end loop;
            end;

            上面的范例是最簡單的loop循環(huán)。還可以參照游標(biāo)的使用這一部分,也使用到了循環(huán)。

            begin
                
            for i in 1..5 loop
                    dbms_output.put_line(i);
                
            end loop;
            end;

            上面范例是最簡單的for循環(huán)。還可以參照游標(biāo)這部分,有類似循環(huán)的使用。

            declare
                lv_cnt_num 
            number(2):=1;
            begin
                
            while lv_cnt_num<=5 loop
                    dbms_output.put_line(lv_cnt_num);
                    lv_cnt_num:
            =lv_cnt_num+1;
                
            end loop;
            end;

            使用while循環(huán)……

            14、goto語句

            說實(shí)在,對goto語句還是有點(diǎn)感情的。因?yàn)槲易钤缃佑|電腦的時(shí)候,認(rèn)為電腦就是BASIC。

            variable lv_rows_num number

            begin
                :lv_rows_num:
            =0;
                
                
            if :lv_rows_num=0 then
                    
            goto insert_row;
                
            end if;
                
                dbms_output.put_line(
            'test point 1');
                
            <<insert_row>>
                dbms_output.put_line(
            'test point 2');
            end;

            15、意外處理

            相當(dāng)于VB中的on error goto ...之類的。下面是范例:

            declare
                type type_basket 
            is record(
                    basket bb_basket.idbasket
            %type,
                    created bb_basket.dtcreated
            %type,
                    qty bb_basket.quantity
            %type,
                    sub bb_basket.subtotal
            %type);
                rec_basket type_basket;
                lv_days_num 
            number(3);
                lv_shopper_num 
            number(3):=22;
            begin
                
            select idbasket, dtcreated, quantity, subtotal
                    
            into rec_basket
                    
            from bb_basket
                    
            where idshopper = lv_shopper_num and orderplaced = 0;
                lv_days_num :
            = sysdate - rec_basket.created;
                dbms_output.put_line(rec_basket.basket);
                dbms_output.put_line(rec_basket.created);
                dbms_output.put_line(rec_basket.qty);
                dbms_output.put_line(rec_basket.sub);
                dbms_output.put_line(lv_days_num);
            exception
                
            when no_data_found then
                    dbms_output.put_line(
            'You have no saved baskets!');
                
            when too_many_rows then
                    dbms_output.put_line(
            'You don't have enough space!');
            end;

            像上面的出錯標(biāo)志no_data_found和too_many_rows是已經(jīng)定義好的,常見的幾種出錯是:
            NO_DATA_FOUND,TOO_MANY_ROWS,ZERO_DIVIDE,DUP_VAL_ON_INDEX(違反唯一性約束或主鍵約束)。如果非預(yù)定義Oracle錯誤,得參照下面的例子:

            declare
                ex_basket_fk exception;
                pragma exception_init(ex_basket_fk, 
            -2292);
            begin
                
            delete from bb_basket where idbasket=4;
            exception
                
            when ex_basket_fk then
                    dbms_output.put_line(
            'Items still in the basket!');
            end;

            關(guān)于出錯信息:ORA-02292:integrity constraint violated - child record found。

            如果需要手動拋出錯誤,得參照下面的例子:

            declare
                ex_prod_update exception;
            begin
                
            update bb_product
                    
            set description = 'NO NO NO NO'
                    
            where idproduct = 30;
                
            if sql%notfound then
                    raise ex_prod_update;
                
            end if;
            exception
                
            when ex_prod_update then
                    dbms_output.put_line(
            'Invalid product id entered.');
            end;

            手動拋出錯誤的條件不僅僅是sql%notfound、sql%found和sql%rowcount幾個,條件可以是多方面的。如下例:

            declare
                lv_ordqty_num 
            number(2):=99;
                lv_stock_num 
            number(4);
                ex_prod_stk exception;
            begin
                
            select stock
                    
            into lv_stock_num
                    
            from bb_product
                    
            where idproduct=2;
                
            if lv_stock_num<lv_ordqty_num then
                    raise ex_prod_stk;
                
            end if;
            exception
                
            when ex_prod_stk then
                dbms_output.put_line(
            'request quantity beyond stock level.');
            end;

            下面的例子說明內(nèi)部塊的錯誤如果在塊內(nèi)無法解決的話將傳遞到塊外解決。另外還說明了出錯代碼和出錯信息的獲取。

            declare
                lv_junk1_num 
            number(3):=200;
            begin
                
            declare
                    lv_junk2_num 
            number(3);
                
            begin
                    lv_junk2_num:
            ='cat';  --引發(fā)錯誤1
                exception
                    
            when others then
                        lv_junk2_num:
            ='hat';  --引發(fā)錯誤2
                        dbms_output.put_line('handler in nested block');
                        dbms_output.put_line(
            'Error code =' || sqlcode);  --錯誤碼
                        dbms_output.put_line('Error message =' || sqlerrm);  --錯誤信息
                end;
                lv_junk1_num:
            =300;
            exception
                
            when others then
                    dbms_output.put_line(
            'handler in outer block');
                    dbms_output.put_line(
            'Error code =' || sqlcode);  --錯誤碼
                    dbms_output.put_line('Error message =' || sqlerrm);  --錯誤信息
            end;

            16、過程(Procedure)

            過程可以保存起來,類似函數(shù)(除了沒有返回值),能供別處調(diào)用。下面是簡單示例:

            create or replace procedure ship_cost_sp
            (p_qty 
            in number, p_ship out number)
            is
            begin
                
            if p_qty>10 then
                    p_ship:
            =11.00;
                elsif p_qty
            >5 then
                    p_ship:
            =8.00;
                
            else
                    p_ship:
            =5.00;
                
            end if;
            end;
            /
             
            variable g_ship 
            number;
             
            execute ship_cost_sp(7, :g_ship);
            begin
                dbms_output.put_line(
            'Now g_ship is '|| :g_ship);
            end;
            /

            上面的例子是用命令方式來調(diào)用過程。下面的例子將說明如何使用過程來調(diào)用過程。

            create or replace procedure order_total_sp
            (p_bsktid 
            in number,p_cnt out number, p_sub out number,p_ship out number, p_total out number)
            is
            begin
                
            select sum(quantity), sum(quantity*price)
                    
            into p_cnt, p_sub
                    
            from bb_basketitem
                    
            where idbasket=p_bsktid;
                    ship_cost_sp(p_cnt, p_ship);  
            --過程調(diào)用過程
                    p_total :=nvl(p_sub,0)+nvl(p_ship, 0);
            end;
            /
             
            variable g_cnt 
            number
            variable g_sub 
            number
            variable g_ship 
            number
            variable g_total 
            number
             
            execute order_total_sp(12, :g_cnt, :g_sub, :g_ship, :g_total);
             
            print :g_cnt
            print :g_sub
            print :g_ship
            print :g_total

            這個例子除了說明如何用過程調(diào)用過程之外,還教了“print”命令,用來打印單個變量的值,調(diào)試的時(shí)候非常有用。
            另外發(fā)現(xiàn)個有意思的現(xiàn)象,過程定義的時(shí)候不需要使用declare關(guān)鍵字,使用了也沒錯,但會產(chǎn)生warning,不好的。

            17、復(fù)雜度進(jìn)一步的過程

            仔細(xì)閱讀下面的例子:

            create or replace procedure promo_test_sp
            (p_mth 
            in char, p_year in char)
            is
            cursor cur_purch is
                
            select idshopper, sum(subtotal) sub
                    
            from bb_basket
                    
            where to_char(dtcreated, 'MM')=p_mth
                        
            and to_char(dtcreated, 'YYYY')=p_year
                        
            and orderplaced=1
                    
            group by idshopper;
                promo_flag 
            char(1);
            begin
                
            for rec_purch in cur_purch loop
                    
            if rec_purch.sub>50 then
                        promo_flag:
            ='A';
                    elsif rec_purch.sub
            >25 then
                        promo_flag:
            ='B';
                    
            end if;
                    
                    
            if promo_flag is not null then
                        
            insert into bb_promolist
                            
            values(rec_purch.idshopper, p_mth, p_year, promo_flag, null);
                    
            end if;
                    promo_flag:
            =null;
                
            end loop;
                
            commit;
            end;
            /
            execute promo_test_sp ('02''2003');

            這個例子和上個例子其實(shí)沒有本質(zhì)的區(qū)別。但注意紅色字部分,原本是寫成“promo_flag:=''”的形式,事實(shí)上,null并不等于“''”,這個千萬要注意,否則得不到正確的結(jié)果。

            18、刪除過程

            一句話,沒什么好說的了。
            drop procedure procedure_name;

            19、函數(shù)

            其實(shí)和過程沒什么區(qū)別,就像VB,區(qū)別就在于一個有返回值,一個沒有。
            當(dāng)然,例子很重要,先舉個例子啦。

            create or replace function ship_calc_sf
            (p_qty 
            in number)    --函數(shù)同樣有輸入輸出參數(shù)
            return number
            is
                lv_ship_num 
            number(5,2);
            begin
                
            if p_qty > 10 then
                    lv_ship_num :
            = 11.00;
                elsif p_qty
            >5 then
                    lv_ship_num:
            =8.00;
                
            else
                    lv_ship_num:
            =5.00;
                
            end if;
                
            return lv_ship_num;
            end;
            /

            函數(shù)的使用方法,如下:

            select idbasket, shipping actual, ship_calc_sf(quantity) calc, 
                ship_calc_sf(quantity)
            -shipping diff
            from bb_basket
            where orderplaced =1;

            在函數(shù)中使用函數(shù)的示例:

            create or replace function memfmt1_sf(
                p_id 
            in number,
                p_first 
            in varchar2,
                p_last 
            in varchar2)
            return varchar2
            is
                lv_mem_txt 
            varchar2(35);
            begin
                lv_mem_txt:
            ='Member '||p_id||'-'||p_first||' '||p_last;
                
            return lv_mem_txt;
            end;
            /
            declare
                lv_name_txt 
            varchar2(35);
                lv_id_num 
            number(4):=25;
                lv_first_txt 
            varchar2(15):='Scott';
                lv_last_txt 
            varchar2(20):='Savid';
            begin
                lv_name_txt:
            =memfmt1_sf(lv_id_num, lv_first_txt, lv_last_txt);
                dbms_output.put_line(lv_name_txt);
            end;
            /

            上面例子很簡單,就不必多說了。
            在過程中調(diào)用函數(shù),順便復(fù)習(xí)一下“過程”吧。

            create or replace procedure login2_sp(
                p_user 
            in varchar2,
                p_pass 
            in varchar2,
                p_id out 
            number,
                p_flag out 
            char,
                p_mem out 
            varchar2)
            is
                lv_first_txt bb_shopper.firstname
            %type;
                lv_last_txt bb_shopper.lastname
            %type;
            begin
                p_flag :
            = 'N';
                
            select idshopper, firstname, lastname
                    
            into p_id, lv_first_txt, lv_last_txt
                    
            from bb_shopper
                    
            where username=p_user and password=p_pass;
                
            if sql%notfound then
                    
            return;
                
            else
                    p_flag:
            ='Y';
                    p_mem:
            =memfmt1_sf(p_id, lv_first_txt, lv_last_txt);
                
            end if;
            end;

            20、刪除函數(shù)

            呵呵,同理的。
            drop function function_name;

            21、軟件包入門

            軟件包,我理解成過程與函數(shù)的集合。下面是簡單范例。

            CREATE OR REPLACE PACKAGE ordering_pkg
            IS
                pv_total_num 
            NUMBER(3,2);
            PROCEDURE order_total_pp
                (p_bsktid 
            IN NUMBER,
                p_cnt OUT 
            NUMBER,
                p_sub OUT 
            NUMBER,
                p_ship OUT 
            NUMBER,
                p_total OUT 
            NUMBER);
            FUNCTION ship_calc_pf
                (p_qty 
            IN NUMBER)
            RETURN NUMBER;
            END;
            /
             
            CREATE OR REPLACE PACKAGE BODY ordering_pkg IS
             
            FUNCTION ship_calc_pf  
                (p_qty 
            IN NUMBER)
                
            RETURN NUMBER
            IS
                lv_ship_num 
            NUMBER(5,2);
            BEGIN
                
            IF p_qty > 10 THEN
                    lv_ship_num :
            = 11.00;
                ELSIF p_qty 
            > 5 THEN
                    lv_ship_num :
            = 8.00;
                
            ELSE
                    lv_ship_num :
            = 5.00;
                
            END IF;
                
            RETURN lv_ship_num;
            END ship_calc_pf;
             
            PROCEDURE order_total_pp
                (p_bsktid 
            IN NUMBER,
                p_cnt OUT 
            NUMBER,
                p_sub OUT 
            NUMBER,
                p_ship OUT 
            NUMBER,
                p_total OUT 
            NUMBER)
            IS
            BEGIN
                
            SELECT SUM(quantity),SUM(quantity*price)
                
            INTO p_cnt, p_sub
                
            FROM bb_basketitem
                
            WHERE idbasket = p_bsktid;
                    p_ship :
            = ship_calc_pf(p_cnt);
                    p_total :
            = NVL(p_sub,0+ NVL(p_ship,0);
            END order_total_pp;
             
            END;
            /

            執(zhí)行完以上代碼之后,就能生成了ordering_pkg的軟件包。下面的操作用來測試軟件包:

            variable cnt number
            variable sub 
            number
            variable ship 
            number
            variable total 
            number
            execute ordering_pkg.order_total_pp(12, :cnt, :sub, :ship, :total);

            22、觸發(fā)器

            初步了解到觸發(fā)器的觸發(fā)條件是:使用insert、update或delete對某個表進(jìn)行操作。簡單范例如下:

            CREATE OR REPLACE TRIGGER product_inventory_trg
                AFTER 
            UPDATE OF orderplaced ON bb_basket
                
            FOR EACH ROW
                
            WHEN (OLD.orderplaced <> 1 AND NEW.orderplaced = 1)
            DECLARE
                
            CURSOR basketitem_cur IS
                    
            SELECT idproduct, quantity, option1
                        
            FROM bb_basketitem
                        
            WHERE idbasket = :NEW.idbasket;
                lv_chg_num 
            NUMBER(3,1);
            BEGIN
                
            FOR basketitem_rec IN basketitem_cur LOOP
                    
            IF basketitem_rec.option1 = 1 THEN
                        lv_chg_num :
            = (.5 * basketitem_rec.quantity);
                    
            ELSE
                        lv_chg_num :
            = basketitem_rec.quantity;
                    
            END IF;
                    
            UPDATE bb_product
                        
            SET stock = stock - lv_chg_num
                        
            WHERE idproduct = basketitem_rec.idproduct;
                
            END LOOP;
            END;
            /

            “AFTER UPDATE OF orderplaced ON bb_basket”為主觸發(fā)條件,“WHEN (OLD.orderplaced <> 1 AND NEW.orderplaced = 1)”為附加條件,條件都成立后,才能觸發(fā)。OLD為事件發(fā)生前的相關(guān)記錄,NEW為事件發(fā)生后的相關(guān)記錄。測試的時(shí)候,試圖把bb_basket表中的某條記錄的orderplaced字段由0至成1,然后觀察表bb_product。
            下面的例子說明多條件觸發(fā),和條件謂詞判斷:

            create or replace trigger product_inventory_trg
                after 
            delete or update on bb_basket
                
            for each row
            declare
                
            cursor basketitem_cur is
                    
            select idproduct, quantity
                        
            from bb_basketitem
                        
            where idbasket=:new.idbasket;
            begin
                
            if updating then
                    
            for basketitem_rec in basketitem_cur loop
                    
            update bb_product
                        
            set stock = stock - basketitem_rec.quantity
                        
            where idproduct = basketitem_rec.idproduct;
                    
            end loop;
                
            end if;
                
                
            if deleting then
                    
            for basketitem_rec in basketitem_cur loop
                    
            update bb_product
                        
            set stock = stock + basketitem_rec.quantity
                        
            where idproduct=basketitem_rec.idproduct;
                    
            end loop;
                
            end if;
            end;

            23、“instead of”觸發(fā)器

            利用instead of觸發(fā)器可以將特定的常規(guī)操作取代為相應(yīng)的DML語句。

            create or replace trigger jgg_trg
                instead 
            of update on view_jgg
                
            for each row
            begin
                
            update bb_basket
                    
            set orderplaced = :new.orderplaced
                    
            where idbasket = :new.idbasket;
                
            insert into bb_basketstatus
                    
            values(553'15-2月-03''JGG'null,null);
            end;

            “instead of xxx on yyy”中的yyy只能是視圖,不能是表。建立了上面這么個觸發(fā)器,如果對view_jgg執(zhí)行update操作的時(shí)候,就會取而代之地執(zhí)行begin...end之間的語句。

            posted on 2009-09-29 13:32 Jiang Guogang 閱讀(963) 評論(0)  編輯 收藏 引用 所屬分類: Knowledge
            久久人妻少妇嫩草AV无码专区| 97久久精品人妻人人搡人人玩| 国产成人精品久久一区二区三区av | 婷婷久久久亚洲欧洲日产国码AV | 国产精品久久新婚兰兰 | 久久久国产精品福利免费| Xx性欧美肥妇精品久久久久久| 国产精品成人久久久久三级午夜电影 | 久久精品国产半推半就| 久久综合九色综合久99| 亚洲国产精品久久久天堂| 国产精品九九久久精品女同亚洲欧美日韩综合区 | 91精品国产色综久久 | 日韩久久无码免费毛片软件| 波多野结衣久久| 久久精品国产亚洲AV无码娇色| 久久99国产乱子伦精品免费| 国产福利电影一区二区三区,免费久久久久久久精 | 久久精品毛片免费观看| 久久男人AV资源网站| 精品久久久久久国产免费了| 欧美精品一区二区精品久久| 性欧美丰满熟妇XXXX性久久久 | 亚洲αv久久久噜噜噜噜噜| 无码任你躁久久久久久老妇App| 一本大道久久香蕉成人网| 久久国产精品免费| 久久精品aⅴ无码中文字字幕重口| 国产亚洲精久久久久久无码AV| 亚洲中文久久精品无码| 日本精品久久久中文字幕| 亚洲国产欧美国产综合久久| 久久这里有精品视频| 国产精品美女久久久久AV福利| 日韩精品久久无码人妻中文字幕| 三级韩国一区久久二区综合| 国产精品丝袜久久久久久不卡| 久久国产精品久久| 久久久久久久久久久久中文字幕| 伊人久久大香线蕉av一区| 2021国内精品久久久久久影院|