1.存储过程结构 create or replace procedure proc1( para1 varchar2, para2 out varchar2, para3 in out varchar2 ) as v_name varchar2(20); begin v_name :='zhangsf'; para3 := v_name; dbms_output.put_line('para3:'||para3); 上面就是一个最简单的存储过程。一个存储过程大体分为这么几个部分: 1.2 存储过程的参数传递方式 create or replace procedure proc1( para1 varchar2, para2 out varchar2, para3 in out varchar2 ) as v_name varchar2(20); begin para1 := 'aaa'; para2 := 'bbb'; v_name :='zhangsf'; para3 := v_name dbms_output.put_line('para3:'||para3); null; end;
首先,我们要明白,我们无法在存储过程的定义中指定存储参数的宽度,也就导致了我们无法在存储过程中控制传入变量的宽度。这个宽度是完全由外部传入时决定的。 create or replace procedure proc2( para1 varchar2, para2 out varchar2, para3 in out varchar2 ) as v_name varchar2(2); begin para2 :='aaaaaaaaaaaaaaaaaaaa'; end; --调用proc2 var p1 varchar2(1); var p2 varchar2(1); var p3 varchar2(1); exec :p2 := 'a'; exec proc1(:p1,:p2,:p3); 在该过程中,p_para2被赋予了20个字符a. 执行这个过程,仍然正确执行。 1.3 参数的默认值 create or replace procedure procdefault( p1 varchar2, p2 varchar2 default 'mark' ) as begin dbms_output.put_line(p2); end; mark create or replace procedure procdefault2( p1 varchar2 default 'remark', p2 varchar2 ) as begin dbms_output.put_line(p1); end; 第一个参数有默认值,第二个参数没有。如果我们想使用第一个参数的默认值时 SQL> exec procdefault2(p2 =>'aa'); remark Declare .. beign ... exception ... end; create or replace procedure innerBlock( p1 varchar2 ) as o1 varchar2(10) := 'out1'; begin dbms_output.put_line(o1); declare inner1 varchar2(20); begin inner1 := 'inner1'; dbms_output.put_line(inner1); declare inner2 varchar2(20); begin inner2 := 'inner2'; dbms_output.put_line(inner2); end; exception when others then null; end; end; 需要注意变量的作用域。 3.2 选用何种游标? create or replace procedure proccursor( p varchar2 ) as v_rownum number(10) := 1; cursor c_postype is select pos_type from pos_type_tb1 where rownum = 1; cursor c_postype1 is select pos_type from pos_type_tb1 where rownum = v_rownum; cursor c_postype2(p_rownum number) is select pos_type from pos_type_tb1 where rownum = p_rownum; type t_postype is ref cursor; c_postype3 t_postype; v_postype varchar2(20); begin open c_postype; fetch c_postype into v_postype; dbms_output.put_line(v_postype); close c_postype; open c_postype1; fetch c_postype1 into v_postype; dbms_output.put_line(v_postype); close c_postype1; open c_postype2(1); fetch c_postype2 into v_postype; dbms_output.put_line(v_postype); close c_postype2; open c_postype3 for select post_type from pos_type_tb1 where rownum=1; fetch c_postype3 into v_postype; dbms_output.put_line(v_postype); close c_postype3 end; cursor c_postype is select pos_type from pos_type_tbl where rownum =1 3.3 游标循环最佳策略 create or replace procedure proccycle( p varchar2 ) as cursor c_postype is select pos_type,description from pos_type_tb1 where rownum < 6; v_postype varchar2(20); v_description varchar2(50); begin open c_postype; if c_postype%found then dbms_output.put_line('found true'); elseif c_postype%found = false then dbms_output.put_line('found false'); else dbms_output.put_line('found null'); end if; loop fetch c_postype into v_postype,v_description; exit when c_postype%notfound; dbms_output.put_line('postype:'||v_postype||',description:'||v_description); end loop; close c_postype; dbms_output.put_line('---loop end---'); open c_postype; fetch c_postype into v_postype,v_description; while c_postype%found loop dbms_output.put_line('postype:'||v_postype||',description:'||v_description); fetch c_postype into v_postype,v_description; end loop; close c_postype; dbms_output.put_line('---while end---'); for v_pos in c_postype loop v_postype := v_pos.pos_type; v_description := v_pos.description; dbms_output.put_line('postype:'||v_postype||',description:'||v_description); end loop; dbms_output.put_line('---for end---'); end; 使用游标之前需要开打游标,open cursor,循环完后再关闭游标close cursor. open c_postype; if c_postype%found then dbms_output.put_line('found true'); elseif c_postype%found = false then dbms_output.put_line('found false'); else dbms_output.put_line('found null'); end if; 在打开一个游标之后,马上检查它的%found或%notfound属性,它得到的结果即不是true也不是false.而是null.必须执行一条fetch语句后,这些属性才有值。 loop fetch c_postype into v_postype,v_description; exit when c_postype%notfound; ... end loop; 这里需要注意,exit when语句一定要紧跟在fetch之后。必避免多余的数据处理。 第二种使用while循环。 fetch c_postype into v_postype,v_description; while c_postype%found loop ... fetch c_postype into v_postype,v_description; end loo; 我们知道了一个游标打开后,必须执行一次fetch语句,游标的属性才会起作用。所以使用while 循环时,就需要在循环之前进行一次fetch动作。 第三种 for循环 for v_pos in c_postype loop v_postype := v_pos.pos_type; v_description := v_pos.description; ... end loop; 可见for循环是比较简单实用的方法。 create or replace procedure procexception( p varchar2 ) as v_postype varchar2(20); begin select pos_type into v_postype from pos_type_tb1 where 1=0;--绝壁为空,报no_data_found的错误 dbms_output.put_line(v_postype); end; 处理这个有三个办法 create or replace procedure procexception( p varchar2 ) as v_postype varchar2(20); begin select pos_type into v_postype from pos_type_tb1 where 1=0;--绝壁为空 dbms_output.put_line(v_postype); --解决办法1:增加异常处理模块 exception when no_data_found then dbms_output.put_line("未找到数据"); end; 这样做换汤不换药,程序仍然被中断。可能这样不是我们所想要的。 create or replace procedure procexception( p varchar2 ) as v_postype varchar2(20); begin select pos_type into v_postype from pos_type_tb1 where 1=0;--绝壁为空 dbms_output.put_line(v_postype); --其实吧,我觉得和上面那种差不多,原理一样的呀,no zuo nod ie exception when no_data_found then v_postype := ''; end; dbms_output.put_line(v_postype); end; 这是一种比较好的处理方式了。不会因为这个异常而引起程序中断。 create or replace procedure proceexception( p varchar2 ) as v_postype varchar2(20); cursor c_postype is select pos_type from pos_type_tb1 where 1=0; beign open c_postype; fetch c_postype into v_postype; close c_postype; dbms_output.put_line(v_postype); end; --个人觉得这样先进行非空判断会更好 create or replace procedure proceexception( p varchar2 ) as v_postype varchar2(20); cursor c_postype is select pos_type from pos_type_tb1 where 1=0; beign if c_postype%found then open c_postype; fetch c_postype into v_postype; close c_postype; dbms_output.put_line(v_postype); end if; end; 这样就完全的避免了no_data_found异常。完全交由程序员来进行控制了。 create or replace proceduer procexception2( p varchar2 ) as v_postype varchar2(20); begin begin select pos_type into v_postype from pos_type_tb1 where rownum < 5; exception when no_data_found then v_postype := null; when too_many_rows then raise_application_error(-20000,'对v_postype赋值是,找到多条数据'); end; dbms_output.put_line(v_postype); end; 需要注意的是一定要加上对no_data_found的处理,对出现多条记录的情况则继续抛出异常,让上一层来处理。 3.5 在存储过程中返回结果集
|
|