oracle的存储过程

资料：http://blog.csdn.net/christine_ruan/article/details/7584832

declare
–常量定义部分
v_channelId number(11);
v_channelName varchar2(64);
begin
–执行部分
select id,channel_name into v_channelId,v_channelName from t_channel_info where id=&channelId;
–在控制台上显示
dbms_output.put_line(‘渠道编码：’||v_channelId||’ 渠道名称：’||v_channelName);
exception
–异常处理
when no_data_found then
dbms_output.put_line(‘你输入的渠道编码不存在，请重新输入’);
end;

存储过程

–无参存储过程
create or replace procedure p_test1
is
–常量定义
v_channelId varchar2(11):=’12583000000’;
v_channelName varchar2(64);
begin
select channel_name into v_channelName from t_channel_info where id=v_channelId;
dbms_output.put_line(‘渠道编码：’||v_channelId||’ 渠道名称：’||v_channelName);
end;

–含有入参存储过程
create or replace procedure p_test(temp_channelId in number)
is
v_channelId number(11);
v_corpId number(6);
v_count number;
begin
select count(1) into v_count from t_channel_info where id=temp_channelId;
if(v_count > 0) then
select id,corp_id into v_channelId,v_corpId from t_channel_info where id=temp_channelId;
dbms_output.put_line(‘channelId=’||v_channelId||’ ‘||’v_corpId=’||v_corpId);
else
dbms_output.put_line(‘该渠道编码不存在’);
end if;
end;

–含有出参存储过程

create or replace procedure p_test(scole in number,otype out varchar2)
    is
    begin
        if(scole>=90) then
            otype := 'A';
        elsif(scole >=80) then
            otype := 'B';
        else
            otype := 'C';
        end if;
        dbms_output.put_line('otype='||otype);
    end;

调用方法(命令窗口)： var result varchar2;
                            exec p_test(80,:result);

–loop 使用
–1+2+…+10
create or replace procedure p_test
is
v_count number :=0;
v_sum number :=0;
begin
loop
v_count := v_count +1;
v_sum := v_sum + v_count;
exit when v_count =10;
end loop;
dbms_output.put_line(‘v_sum=’||v_sum);
end;

–while loop使用

    --1+2+...+10
    create or replace procedure p_test
        is
           v_count number :=0;
           v_sum number :=0;
        begin
          while v_count <10 loop
            v_count := v_count +1;
            v_sum := v_sum + v_count;

          end loop;
          dbms_output.put_line('v_sum='||v_sum||' v_count='||v_count);
        end;

–for loop 使用
–1+2+…+10
create or replace procedure p_test
is
v_sum number :=0;
begin

           for v_count in 1..10 loop
             v_sum := v_sum +v_count;
           end loop;
          dbms_output.put_line('v_sum='||v_sum);
        end;

–switch case 使用
create or replace procedure p_test
is
v_grade varchar2(50):=’及格’;
v_scole varchar2(50);
begin
v_scole :=case v_grade
when ‘不及格’ then ‘成绩<60’
when ‘及格’ then ‘60<=成绩<70’
when ‘中等’ then ‘70<=成绩<80’
when ‘良好’ then ‘80<=成绩<90’
when ‘优秀’ then ‘90<=成绩<=100’
else ‘输入有误’
end;
dbms_output.put_line(v_scole);
end;

— 游标使用
定义：游标分为显式游标和隐式游标。显式游标是由用户声明和操作的一种游标；
隐式游标是oracle为所有数据操纵语句（包括只返回单行数据的查询语句）自动声明和操作的一种游标。

  1)显式游标的处理包括声明游标、打开游标、提取游标、关闭游标4个步骤。

    声明游标-》打开游标-》提取游标-》关闭游标（判断是否为空为空则关闭）

   create or replace procedure p_test
        is
        v_corpId varchar2(6);
        v_channelId varchar2(11);
        cursor test_cursor is
               select corp_id,channel_id from test2;
        begin
          open test_cursor;
          loop
             fetch test_cursor into v_corpId,v_channelId;

            exit when  test_cursor%notfound ;
           dbms_output.put_line('corpId='||v_corpId||' channelId='||v_channelId);

          end loop;
          close test_cursor;
         end;
       注意点：
            1、使用前须用%ISOPEN检查其打开状态，只有此值为true的游标才可使用，否则要先将游标打开；
            2、在使用游标过程中，每次都要用%FOUND或%NOTFOUND属性检查是否返回成功，即是否还要操作的行；
            3、将游标中行取变量组中时，对应变量个数和数据类型必须完全一致；
            4、使用完游标必须将其关闭，以释放相应内存资源。

 2)隐式游标：在pl/sql中用select语句进行操作，则隐式的使用了游标，也就是隐式游标，这种游标无需定义，也无需打开和关闭。

    create or replace procedure p_test
        is
        v_corpId varchar2(6);
        v_channelId varchar2(11);

        begin
            select corp_id,channel_id into v_corpId,v_channelId from test2 where corp_id=100010;
            if SQL%FOUND then
            dbms_output.put_line('corpId='||v_corpId||' channelId='||v_channelId);
        end;

 游标属性：
    1、是否找到游标（%FOUND）：该属性标识当前游标是否指向有效一行，若是则为true，否则为false。检查此属性可以判断是否结束游                                      标使用。
            显示游标： exit when  not test_cursor%found ;
            隐式游标：
                             create or replace procedure p_test
                                is
                                   begin
                                           delete from test2 where corp_id=101011;
                                           if sql%found then
                                               dbms_output.put_line('删除成功');
                                               commit;
                                            else
                                              dbms_output.put_line('没查询到数据');
                                              rollback;
                                           end if;
                                   end;
    2、是否没有找到游标（%NOTFOUND）：该属性与%FOUND属性相类似，但其值正好相反。

    3、游标行数（%ROWCOUNT）:该属性记录了游标抽取过的记录数，也可以理解为当前游标所在 的行号，这个属性在循环判断中也很有                                       效，使得不必抽取所有记录行就可以中断游标操作。       

            显示游标：loop
                            fetch test_cursor into v_corpId,v_channelId;
                            exit when test_cursor%rowcount =10;--只抽取10条记录
                      end loop;
            隐式游标：该属性的引用方法是SQL%ROWCOUNT，表示最新处理过的sql语句影响的记录数。

    4、游标是否打开（%ISOPEN）:该属性表示游标是否处于打开状态。隐式游标中该属性始终为true

    5、参数化游标

       create or replace procedure p_test(corpId in number)
                      as
                        v_channelId varchar2(11);
                        v_corpId number;
                        cursor  test_cursor(cursor_corpId number) is
                            select corp_id,channel_id from test2 where corp_id=cursor_corpId;
                    begin
                            open  test_cursor(corpId);

                            loop
                                    fetch  test_cursor into v_corpId,v_channelId;
                                    exit when  test_cursor%notfound;
                                    dbms_output.put_line('v_corpId='||v_corpId||' v_channelId='||v_channelId);
                            end loop;
                    close  test_cursor;
            end;

  6、游标变量：在同一个pl/sql块中，游标变量不同于特定的查询绑定，而是在打开游标时才确定所对应的查询。
            在使用游标变量之前，必须先声明，然后在运行时必须为其分配存储空间，因为游标变量是ref类型的                   变量，类似于高级语言中的指针。
    create or replace procedure p_test as
              v_channelId varchar2(11);
              v_corpId    number;
              type test_cursorRef is ref cursor;--游标变量定义
              test_cursor test_cursorRef;
            begin
              open test_cursor for  select corp_id, channel_id from test2;

              loop
                fetch test_cursor into v_corpId, v_channelId;
                exit when test_cursor%notfound;
                dbms_output.put_line('v_corpId=' || v_corpId || ' v_channelId=' ||v_channelId);
              end loop;
              close test_cursor;
            end;