oracle 过程游标循环,oracle数据库存储过程的一个实例（运用到游标和循环）

自己写的一个存储过程实例(格式可以参照)：

create or replace procedure comm.procedure_luguagua(patientId in varchar2,//入参

visitId in number,

ldt_start in date,

ldt_end in date,

isSuccess out number,//出参

errorInfo out varchar2) as

name_temp VARCHAR2(20);//存储过程的变量

sex_temp VARCHAR2(4);

date_of_birth_temp date;

charge_type_temp varchar2(8);

settling_date_temp date;

subj_name_temp varchar2(16);

costs_temp number(11, 4);

begin

declare

cursor mycursor is //声明游标

select b.name,

b.sex,

b.date_of_birth,

b.charge_type,

a.settling_date,

c.subj_name,

sum(a.costs)

from inp_settle_master a,

pat_master_index b,

tally_subject_dict c,

inp_bill_detail d

where a.patient_id = b.patient_id

and a.patient_id = d.patient_id

and a.visit_id = d.visit_id

and d.subj_code = c.subj_code

and a.patient_id = patientId

and a.visit_id = visitId

and a.transact_type = '正常'

and a.settling_date between ldt_start and ldt_end

group by b.name,

b.sex,

b.date_of_birth,

b.charge_type,

a.settling_date,

c.subj_name;

begin

open mycursor;//打开游标

loop

fetch mycursor into name_temp,sex_temp,date_of_birth_temp,charge_type_temp,settling_date_temp,subj_name_temp,costs_temp;

if mycursor%found then //判断是否fetch值

insert into comm.table_luguagua ( patient_id,visit_id,name,sex,date_of_birth,charge_type,settling_date, subj_name,costs)

values ( patientId,visitId,name_temp,sex_temp,date_of_birth_temp,

charge_type_temp,settling_date_temp, subj_name_temp,costs_temp);

end if;

exit when mycursor%notfound; //判断是否提取完成

end loop;

close mycursor;//关闭游标

isSuccess:= 1;

errorInfo:= '';

commit;

end ;

exception //异常处理

when NO_DATA_FOUND then

isSuccess:=0;

errorInfo:='no data found';

rollback;

when others then

isSuccess:=0;

errorInfo:='other error';

rollback;

end procedure_luguagua;//结算存储过程