oracle的存储过程

来源:互联网 发布:触景无限 知乎 编辑:程序博客网 时间:2024/03/29 18:10

资料: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;
原创粉丝点击