存储过程和函数学习

平常只关心工作中用到的和自己感兴趣的,因为没有用到这东西所以也没有在意;最近问的人多了就学学。

这个东西网上很多，找了个百度文库开始自己写(http://wenku.baidu.com/link?url=4kZCAq87BmnGJcjYDZnn751D2qLjzrHj0kKy9VhLSjraFEu0OB9_RJQYB2Z-gm8jEjRcPcFduH-6Rs3Z2iC9C9uvE4n_WcGqJRj4qWyWcny)。

参照例子自己写了几个存储过程：

1.无参procedure,有个局部变量,给局部变量复制,输出;

create or replace procedure demo_one
as
v_total number(10);
begin
select count(*) into v_total from so4.ins_user_573;
dbms_output.put_line('ins_user表中记录数:'||v_total);
end;

--运行上述SQL编译

begin
so4.demo_one;
end;

--执行

2.使用游标进行loop循环遍历输出;同时引用其他的procedure;

create or replace procedure demo_two
as
cursor user_loop is select user_id,bill_id from so4.ins_user_573;
begin
for user_record in user_loop loop
dbms_output.put_line(user_record.user_id||'+'||user_record.bill_id);
end loop;
so4.demo_one;
end;

--运行SQL编译

begin
so4.demo_two;
end;

--执行

3.设置两个入参变量default一个默认值;两个局部变量,但没有用到;游标遍历输出;异常处理;事务提交更新;
create or replace procedure demo_three(
p_id in number default 2,
p_type in number default 1
)as
v_bill_id varchar2(20);
v_sub_bill_id varchar2(30);
--v_ins_user so4.ins_user_573%rowtype;
cursor user_cursor is select bill_id,sub_bill_id into v_bill_id,v_sub_bill_id from so4.ins_user_573
where prod_catalog_id = p_id and cust_type = p_type;
begin
for user_record in user_cursor loop
dbms_output.put_line('号码为：'||user_record.bill_id||',IMSI为：'||user_record.sub_bill_id);
end loop;
update so4.ins_user_573 set remarks = null /*where cust_type = p_type and prod_catalog_id = p_id*/;
commit;
exception
when others then
dbms_output.put_line('发生错误无法修改'||SQLERRM);
rollback;
end;

--编译

begin
demo_three(1,1);
end;

--执行

4.输出参数的使用;

create or replace procedure demo_four(
p_total out number
) as
begin
select count(*) into p_total from so4.ins_user_573;
dbms_output.put_line('内部展示记录->记录数据为:'||p_total);
end;

--编译

declare total_1 number;
begin
so4.demo_four(total_1);
dbms_output.put_line('外部展示记录->记录数据为:'||total_1);
end;
--执行

5.in out类型兼容出入参数的使用;
create or replace procedure demo_five(
p_remarks in out varchar2
) as
begin
p_remarks := '前缀-'||p_remarks;
end;

--编译

declare remarks varchar2(50);
begin
remarks := '求加前缀';
so4.demo_five(remarks);
dbms_output.put_line(remarks);
end;

--执行

 6.函数返回;打印;

alter function demo_six compile;

create or replace function demo_six(
p_id in number,
p_type in number
) return varchar2
as
v_bill_id varchar2(20);
begin
select bill_id into v_bill_id from so4.ins_user_573 where prod_catalog_id = p_id and cust_type = p_type;
return(v_bill_id);
exception
when no_data_found then
dbms_output.put_line('没有记录');
return(null);
when too_many_rows then
dbms_output.put_line('有重复记录');
return(null);
when others then
dbms_output.put_line('其他错误');
return(null);
end;

declare ret varchar2(20);
begin
ret := so4.demo_six(1,1);
if ret is null then ret := 0;dbms_output.put_line('有'||ret||'条记录');
else dbms_output.put_line('有记录,号码是：'||ret);
end if;
end;

===========================================

学习了基本语法使用,难的复杂的以后实际用到了再说。

使用PL\SQL Developer工具执行：

　　1.command window 窗口使用 exec procedure_name或execute procedure_name执行后,只会显示PL/SQL procedure successfully completed,不会显示执行结果;

　　2.SQL window下使用 begin 模式.procedure_name; end;可执行,同时output标签下显示执行结果;

　　3.可以直接右击procedure使用test(测试)进入Test Window进行测试自己写的procedure;

　　4.参数的定义只要定义类型就行了,不能定义大小,定义大小则会报错;

　　5.异常处理部分有很多异常类型和处理方式;

　　6.函数和存储过程最大的差别是表示不一样function和procedure,前者有必须有返回值,后者无返回值,语法类似;