不错的plsql基础知识

  PLSQL异常处理
*****************************************
1、声明异常
 异常名 EXCEPTION；
2、抛出异常
 RAISE 异常名
3、处理异常
 抛出异常后的逻辑代码不会被继续执行

异常的定义使用
    ―――――――――――――――――――――――――――――――――――――
    begin
          dbms_output.put_line(1/0);
    exception
            when others then
                dbms_output.put_line('error');
    end;

    declare
            e_myException exception;
    begin
            dbms_output.put_line('hello');
            raise e_myException; --raise抛出异常,用此关键字，抛出后转到自定义的e_myException ，执行其里面的putline函数后，再跳到end处，结束PL/SQL块，raise接下面的2句不会继续执行。
            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游标和goto语句
*****************************************
备注：下面提到的游晡?STRONG>静态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
);

--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);
     end;

--update or 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 >    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 >    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 >    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 >    begin
         for cur in c_book(i_id) --直接将入参i_id传入cursor即可
         loop
             update book set price = '53' where bookname = cur.bookname;
         end loop;
    end;

goto例子，一般不推荐使用goto，会使程序结构变乱
    declare
         i number:=0;
    begin
         if i=0 then
             goto hello;
         end if;
         <>
         begin
             dbms_output.put_line('hello');
             goto over;
         end;
         <>
         begin
             dbms_output.put_line('world');
             goto over;
         end;
         <>
             dbms_output.put_line('over');
     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;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11536986/viewspace-625826/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11536986/viewspace-625826/