Oracle游标/异常/过程/函数

2018-01-31 12:41:41来源:cnblogs.com作者:莫伊筱筱人点击

分享

1、基础语法

http://692088846.iteye.com/blog/2017137   (%type、%rowtype、if/if else/if elseif else、while、do..while、游标、异常、函数、过程)

1.1 声明变量赋值并输出

 set serveroutput on --设置数据库输出,默认为关闭,每次重新打开窗口需要重新设置。    Declare       result integer;  --声明变量【变量名 变量类型】    begin       result:=10+3*4-20+5**2;  --给变量赋值【:=】       dbms_output.put_line('运算结果是:'||to_char(result));    end; --------------------------------------------------------------------------------------------------    dbms_output.put_line函数输出只能是字符串,因此利用to_char函数将数值型结果转换为字符型。    运算的优先次序为NOT、AND和OR。    To_char:将其他类型数据转换为字符型。    To_date:将其他类型数据转换为日期型。    To_number:将其他类型数据转换为数值型。

1.2 %type、%rowtype、select...into

        -- %type 变量的类型和数据表中的字段的数据类型一致        -- %rowtype  变量的类型和数据表中的一行记录数据类型一致        --  select ... into 变量     表中查询数据并赋值,可以一次性给多个变量赋值
    declare       v_object CUSTOMER%Rowtype;     begin      select * into v_object from customer where rownum=1;      dbms_output.put_line(v_object.Enterprise_Code ||','||v_object.CreateDate);    end;

1.3 只可以增删改  

     -- insert,update,delete,select都可以,create table,drop table不行。DPL,DML,和流程控制语句可以在pl/sql里用但DDL语句不行,表定义语言不可以在plsql中改变        declare       v_name student.name%type:='wang'; --声明变量并赋值    begin       insert into student(id,name,age) values(2,v_name,26);  --插入数据    end;  --------------------------------------------    declare       v_name student.name%type:='hexian';              --更新数据    begin       update student set name=v_name where id=1;    end;    begin       update student set name='qinaide' where id=2;    end;  ------------------------------------------------- --------------

2、 PLSQL流程控制

        --if判断---        declare             v_b boolean:=true;   -- := 是赋值        begin                         if v_b then                 dbms_output.put_line('ok');                end if;        end;        --if else判断---        declare             v_b boolean:=true;        begin             if v_b then                  dbms_output.put_line('ok');             else                  dbms_output.put_line('false');             end if;        end;        --if elsif else判断--        declare                v_name varchar2(20):='cheng';        begin                 if v_name='0701' then                        dbms_output.put_line('0701');                 elsif v_name='cheng' then                        dbms_output.put_line('cheng');                 else                        dbms_output.put_line('false');                 end if;        end;        --loop循环,注意推出exit是推出循环,而不是推出整个代码块       --------------------------------------------------------         loop简化写法        declare                v_i binary_integer :=0;        begin                 loop                     exit when v_i>10;                         v_i :=v_i+1;                         dbms_output.put_line('hehe');                 end loop;                         dbms_output.put_line('over');        end;        while循环-------------------------------------------------        declare                     v_i binary_integer:=0;        begin                     while v_i<10 loop                                 dbms_output.put_line('hello'||v_i );                                 v_i:=v_i+1;                     end loop;                     dbms_output.put_line('over');        end;        for循环,注意不需要声明变量-----------------------------------------------        begin                 for v_i in 0..10 loop                         dbms_output.put_line('hello'||v_i);                 end loop;                         dbms_output.put_line('over');        end;                                        *****************************************     PLSQL异常处理        *****************************************        1、声明异常         异常名 EXCEPTION;        2、抛出异常         RAISE 异常名        3、处理异常         抛出异常后的逻辑代码不会被继续执行        异常的定义使用                ―――――――――――――――――――――――――――――――――――――    begin          dbms_output.put_line(1/0);  --1/0出现错误,抛出异常,输出error    exception          when others then             dbms_output.put_line('error');    end;        ----------------    declare            e_myException exception; --声明异常    begin            dbms_output.put_line('hello');                        --raise抛出异常,用此关键字,抛出后转到自定义的e_myException,执行其                        --里面的putline函数后,再跳到end处,结束PL/SQL块,raise接下面的2句不会继续执行。            raise e_myException;             dbms_output.put_line('world');            dbms_output.put_line(1/0);    exception            when e_myException then   --执行异常                dbms_output.put_line(sqlcode); --当前会话执行状态,错误编码                dbms_output.put_line(sqlerrm); --当前错误信息                dbms_output.put_line('my error');            when others then                dbms_output.put_line('error');    end;        ============================================================================================================                *****************************************            PLSQL游标        *****************************************        备注:下面提到的游标为静态cursor,包括显示和隐式。        游标,从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的,显式cursor是静态cursor,她的作用域是全局的,但也必须明白,静态cursor也只有pl/sql代码才可以使用它。静态游标变量是在定义时就必须指定SQL语句。        cursor 游标(结果集)用于提取多行数据,定义后不会有数据,使用后才有。一旦游标被打开,就无法再次打开(可以先关闭,再打开)。                declare                     cursor c_student is  select * from book;                begin                     open c_student;                     close c_student;                end;        第二种游标的定义方式,用变量控制结果集的数量。                declare                      v_id binary_integer;                      cursor c_student is select * from book where id>v_id;                      begin                      v_id:=10;                      open c_student;                      close c_student;                end;        第三种游标的定义方式,带参数的游标,用的最多。                declare                     cursor c_student(v_id binary_integer) is select * from book where id>v_id;                begin                     open c_student(10);                     close c_student;                end;        游标的使用,一定别忘了关游标。                declare                     v_student book%rowtype;                     cursor c_student(v_id binary_integer) is select * from book where id>v_id;                begin                     open c_student(10);                     fetch c_student into v_student;                     close c_student;                     dbms_output.put_line(v_student.name);                end;         如何遍历游标fetch            游标的属性 %found,%notfound,%isopen,%rowcount。            %found:若前面的fetch语句返回一行数据,则%found返回true,如果对未打开的游标使用则报ORA-1001异常。            %notfound,与%found行为相反。            %isopen,判断游标是否打开。            %rowcount:当前游标的指针位移量,到目前位置游标所检索的数据行的个数,若未打开就引用,返回ORA-1001。        注:        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。        下面是几个实例:        create table BOOK        (            ID       VARCHAR2(10) not null,            BOOKNAME VARCHAR2(10) not null,            PRICE    VARCHAR2(10) not null,            CID      VARCHAR2(10) not null        );        -- %rowcount是SQL的属性表示影响了多少条记录         ======insert===================================================================        create or replace procedure say_hello(                 i_name in varchar2,                 o_result_msg out varchar2          )       as       v_price varchar2(100);        e_myException exception;         begin          insert into book(id,bookname,price) values (1,2,3);  --这里会报错          o_result_msg := 'success';       exception          when others then               rollback;               --o_result_msg := substr(sqlerrm, 1, 200);  --返回完整错误信息               o_result_msg := substr(sqlcode, 1, 200);    --返回错误码         end;                   ====update/delete======================================================================        create or replace procedure say_hello(                  i_name in varchar2,                  o_result_msg out varchar2          )       as       v_price varchar2(100);        e_myException exception;         begin          update book set price = '55' where bookname = i_name;          delete from book where bookname = i_name;          if sql%notfound then             raise e_myException;           end if;          o_result_msg := 'success';       exception          when e_myException then               rollback;               o_result_msg := 'update or delete dail';       end;          ===select======================================================================     create or replace procedure say_hello(            i_name in varchar2,            o_result_msg out varchar2        )       as       v_price varchar2(100);        e_myException exception;         begin          select price into v_price from book where bookname = i_name;          o_result_msg := 'success';       exception          when no_data_found then               rollback;               o_result_msg := 'select into dail';       end;                  ===============================================================================        loop方式遍历游标    declare          v_bookname  varchar2(100);          cursor c_book(i_id number) is select bookname from book where id = i_id;    begin        Open  c_book(i_id);        Loop            Fetch c_book into v_bookname;            exit when c_student%notfound;              update book set price = '33' where bookname = v_bookname;        End Loop;        Close c_book;    end;    或    declare          v_bookname  varchar2(100);          cursor c_book(i_id number) is select bookname from book where id = i_id;    begin        Open  c_book(i_id);          Fetch c_book into v_bookname;          While c_book%Found          Loop              update book set price = '33' where bookname = v_bookname;          Fetch  c_book into v_bookname;          End Loop;        Close c_book;    end;        ============================================================================================        while循环遍历游标,注意,第一次游标刚打开就fetch,%found为null,进不去循环解决方法:while nvl(c_student%found,true) loop    declare         v_bookname  varchar2(100);         cursor c_book(i_id number) is select bookname from book where id = i_id;    begin         Open  c_book(i_id);         while nvl(c_book%found,true) --或这种写法:while c_book%found is null or c_book%found loop                 Fetch c_book into v_bookname;             update book set price = '33' where bookname = v_bookname;         End Loop;         Close c_book;    end;===================================================================================================    for循环遍历,最简单,用的最多,不需要 声明v_student,Open和Close游标和fetch操作(不用打开游标和关闭游标,实现遍历游标最高效方式)    declare         cursor c_book(i_id number) is select bookname from book where id = i_id;    begin         for cur in c_book(i_id) --直接将入参i_id传入cursor即可         loop             update book set price = '53' where bookname = cur.bookname;         end loop;    end;           *****************************************             Oracle存储过程        *****************************************   在谈存储过程书写中的一些规则时,先看一下执行它的规则,在命令窗口执行存储过程sp_get_product_prompt      set serveroutput on     var ret1 varchar2(200);     var ret2 varchar2(200);     exec sp_get_product_prompt(83,:ret1,:ret2); --或execute     print ret1;     print ret2;     或     set serveroutput on     declare        ret1 varchar2(200);        ret2 varchar2(200);     begin        sp_get_product_prompt(83,ret1,ret2);        dbms_output.put_line(ret1);        dbms_output.put_line(ret2);     end;存储过程入参,不论类型,缺省情况下值都为null,入参和出参不能有长度,其中关键字as可以替换成is,存储过程中变量声明在as和begin之间,同时,存储过程中可以再调用其它的存储过程,如果要保证存储过程之间的事务处理不受影响,可以定义为自治事务。     create or replace procedure say_hello(       v_name in varchar2,       v_flag number,       o_ret out number     )     as     begin       if v_name is null and v_flag is null then --v_name和v_flag都等于null           o_ret := 10;       else           o_ret := 100;        end if;     end;对于入参为null情况下给予缺省值     create or replace procedure say_hello(       i_name in varchar2,       i_flag number,       o_ret out number     )     as       v_name  varchar2(100);     begin       if i_name is null then           v_name := '0';       else          v_name := i_name;       end if;       insert into phone(..,wname..,) values(..,v_name,..);            end;或直接在insert语句中调用nvl函数赋缺省值     insert into phone(..,wname..,) values(..,nvl(v_name,' '),..); ----如果将' '写成'',则insert进来的v_name值还是为''等价于null值带一个参数的存储过程   输入参数in,输入参数不能进行:=赋值,但可以将它赋给as后面定义的变量;   输入参数in,可以作为变量进行条件判断;   默认不写就是in;   存储过程没有重载,这个有参的say_hello会替代已经存在的无参say_hello。     create or replace procedure say_hello(v_name in varchar2)     as     begin        --v_name:='a'; --存储过程入参v_name不能做为赋值目标        dbms_output.put_line('hello '||v_name);      end;存储过程输入参数作为变量进行条件判断     create or replace procedure say_hello(        i_opFlag in number     )     as        v_name varchar2(100);     begin        if i_opFlag = 1 then           v_name :='0';         else           v_name :='haha';        end if;         dbms_output.put_line('hello '||v_name);      end; 利用存储过程中定义的变量对入参的空值处理:     create or replace procedure say_hello(        i_name in varchar2     )     as        v_name varchar2(100);     begin        if i_name is null then    v_name :='0';         else    v_name :=i_name;--将入赋值给定义变量        end if;         dbms_output.put_line('hello '||v_name);      end;多个参数的存储过程     create or replace procedure say_hello(        v_first_name in varchar2,        v_last_name in varchar2)     as     begin        dbms_output.put_line('hello '||v_first_name||'.'||v_last_name);     end;out输出参数,用于利用存储过程给一个或多个变量赋值,类似于返回值     create or replace procedure say_hello(        v_name in varchar2,        v_content out varchar2     )     begin        v_content:='hello'||v_name;     end;     调用:     declare        v_con varchar2(200);        v_in varchar2(20):='wang';     begin        say_hello(v_in,v_con);        dbms_output.put_line(v_con);     end;in out参数,既赋值又取值     create or replace procedure say_hello(v_name in out varchar2)     as     begin        v_name:='hi '||v_name;     end;     调用:     declare        v_inout varchar2(20):='wangsu';     begin        say_hello(v_inout);        dbms_output.put_line(v_inout);     end;对存储过程入参赋缺省值     create or replace procedure say_hello(        v_name varchar2 default 'susu',        v_content varchar2 default 'hello'     )     as     begin        dbms_output.put_line(v_name||' '||v_content);     end;     调用:(用指明形参名的方式调用更好)     begin        say_hello();     end;     或     begin        say_hello('cheng');     end;     或     begin     say_hello(v_name=>'cheng');    end;                        *****************************************         PLSQL中的function        *****************************************        FUNCTION和PROCEDURE的区别        1、函数有返回值,过程没有        2、函数调用在一个表达式中,过程则是作为pl/sql程序的一个语句            过程和函数都以编译后的形式存放在数据库中,函数可以没有参数也可以有多个参数并有一个返回值。过程            有零个或多个参数,没有返回值。函数和过程都可以通过参数列表接收或返回零个或多个值,函数和过程的            主要区别不在于返回值,而在于他们的调用方式,过程是作为一个独立执行语句调用的,函数以合法的表达            式的方式调用    create or replace function func(v_name in varchar2)    return varchar2    is    begin       return(v_name||' hello');    end;    --调用:    declare       v_name varchar2(20);    begin       v_name:=func('cheng');       dbms_output.put_line(v_name);    end;带out参数的函数    create or replace function func(       v_name in varchar2,       v_content out varchar2    )    return varchar2    is    begin       v_content:=v_name||' hello';       return v_content;    end;    调用:    declare       v_name varchar2(20);       v_name1 varchar2(20);    begin       v_name1:=func('susu',v_name);--返回v_name值       dbms_output.put_line(v_name1);--打印func结果       dbms_output.put_line(v_name);--打印v_name结果    end;带in out 参数的函数    create or replace function func(       v_name in out varchar2)    return varchar2    is    begin       v_name:=v_name||' hello';       return 'cheng';    end;    调用:    declare       v_inout varchar2(20):='world';       v_ret varchar2(20);    begin       v_ret:=func(v_inout);--返回调用v_inout值(作为出参)       dbms_output.put_line(v_ret);--打印func结果            dbms_output.put_line(v_inout);--返回v_name结果    end;                                                        

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台