Oracle存储过程小解

Oracle存储过程小解
1.创建语法
    create or replace procedure pro_name(
    paramIn in type,                      
    paramOUt out type,
    paramInOut in out type
    )
    as(is)[类似于mysql的declare]
    begin
        statement...
    end;
    注:<1>入参不用写长度，只需写类型，in、out、in out写在参数名后，区别于mysql写在参数名前，不写默认为in;
       <2>in是值传递，out和in out是引用传递，in的值不可修改，out的值在进入存储过程是初始化null。
       <3>as(is)类似于mysql的declare，也可以在begin后使用declare定义变量；
          设置变量值，也不用set，直接name := value或者使用select...into...;
       <4>没有类似于mysql中的变换分隔符，最后end;即可。
       <5>没有入参时，可不要pro_name后();
　　　　<6>变量声明：在begin之前，直接varName type；在内部，declare varName type.变量赋值需要使用 := 符号
2.常用方法
    <1> if...then...elseif...then...end if;
    <2>多种循环：
        a.loop...exit when... end loop;
        b.while...loop....end loop;
        c.for...in...loop...end loop;(强烈推荐，mysql没有for循环)
　　　　 d.exit可用于跳出循环，return结束存储过程
　　　　 e.<<loopName>>...goto loopName:类似于标记；

<3>游标cursor：
       游标属性：
            cursor%found;     --有数据
            cursor%notfound;  --无数据
            cursor%isopen;    --游标已开启
            cursor%rowcount;  --受最后SQL语句影响的行数
3.异常处理
    <1>.通过关键字exception捕获异常
        语法：
        exception
        when exception_decription then
        statemnt
        when exception_description2 then
        statement
        when others then
        statement
    <2>.最常用的异常：
        no_data_found:select into语句没有数据；
        too_many_rows:select into有多条数据；
        dup_val_on_index:唯一索引列重复；
        storage_error:内存溢出；
        zero_devide:除数为0；
        case_not_found:case没有匹配的条件且没有else；
        cursor_already_open:游标已打开；
        timeout_on_resource：请求资源超时。
    <3>.自定义异常:(类似于mysql的自定义condition,避免error_code值带来的阅读性太差的问题);
        progma exception_init(selfexception,-oracle_error_code);
        示例：declare demo_exception exception;
              progma exception_init(demo_exception,-60);
        <4>.处理异常
        a.不抛出，statement处理；
        b.抛出异常：
            ●存储过程自动抛出
            ●通过raise关键字抛出,如 raise no_data_found;
            ●通过raise_application_error(error_number,message[flag(true,false)]);
             error_number数值范围从-20999到-20000；
             messgae表示异常描述；
             flag表示是添加到(true)或者覆盖(false)错误堆，默认是false；
             如:raise_application_error(-20001,'invalid id number');
    <5>异常处理机制与java异常处理机制相似。
4.常用技巧：
    <1>execute immediate statement to param;
       关键字:execute immediate...to...;
       它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块,可以理解为执行动态SQL。
       注意几点：
            a.不支持返回多行的操作，这种情况应该用refcursor来处理
            b.执行sql时不要加分好，pl/sql块时加分号；
            c.使用之前应该将之前的事务显示提交。
           示例：
        execute immediate 'select dname, loc from dept where deptno = :1'
            into l_nam, l_loc
            using l_dept ;
    <2>sys_refscursor:非正常游标，用于返回结果集
       示例：
        create or replace procedure up_test(o out sys_refcursor) is --可以在代码中获取返回值
        begin
        open o for select * from lq_test;
        end;
    <3>%type
       作用：与关联表的关联字段类型长度绑定起来，跟随绑定表字段的变化，是一种非常好的变成习惯，避免多次更改：
       示例：
        declare v_name students.name%type;
    <4>%rowtype
       表示该列为行数据类型，存储的为一行数据，相当于一条record相对于查询结果或者游标。
       作用：当查询一行数据时，比多个字段采用%type效率要高一些。
       示例：
           declare 
           v_emp emp%rowtype;
           cursor cursor_name is select...from table...
           open cursor_name
           for xxx in cursor_name loop
           v_emp := xxx;
           end loop;
           end cursor_name;