• 带有输入参数的存储过程

create or replace procedure pro_goods_in (g_id in number default 5)as        type goods_info is record       (v_goodsid goods.goodsid%type,        v_goodsname goods.goodsname%type,        v_goodsremark goods.remark%type);       v_goods_info goods_info;    cursor cur_goods     is    select * from goods where goodsid < g_id;begin    open cur_goods;         loop           fetch cur_goods into v_goods_info;           exit when cur_goods%notfound;           dbms_output.put_line(v_goods_info.v_goodsid ||'-'||v_goods_info.v_goodsname||'-'||v_goods_info.v_goodsremark);                        end loop;    close cur_goods;       end;begin    pro_goods_in();end;

 

• 带有输出参数的存储过程

create or replace procedure pro_goods_out (g_name in VARCHAR2,g_count out number)as  begin        select count(*) into g_count from goods where goodsname = g_name; end;  declare  goods_count number; begin         pro_goods_out('快克',goods_count);       dbms_output.put_line('条数为：'||goods_count); end;

• 查看存储过程

select distinct name from user_source where type = 'procedure'  select * from user_objects where object_type ='procedure'  select name,line,text from user_source where type = 'procedure' and name ='pro_goods_out'

• 查看存储过程的错误信息

show errors procedure pro_goods_out;

• 重新编译存储过程

alter procedure pro_goods_out compile;

• 删除存储过程

drop procedure pro_goods_out;