游标的使用

-- 声明游标；CURSOR cursor_name IS select_statement

--For 循环游标
--（1）定义游标
--（2）定义游标变量
--（3）使用for循环来使用这个游标
declare
--类型定义
cursor c_job
is
select empno,ename,job,sal
from emp
where job='MANAGER';
--定义一个游标变量v_cinfo c_emp%ROWTYPE ，该类型为游标c_emp中的一行数据类型
 c_row c_job%rowtype;
begin
for c_row in c_job loop
 dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
end;



--Fetch游标
--使用的时候必须要明确的打开和关闭

declare 
--类型定义
cursor c_job
is
select empno,ename,job,sal
from emp
where job='MANAGER';
--定义一个游标变量
 c_row c_job%rowtype;
begin
open c_job;
 loop
--提取一行数据到c_row
fetch c_job into c_row;
--判读是否提取到值，没取到值就退出
--取到值c_job%notfound 是false 
--取不到值c_job%notfound 是true
exit when c_job%notfound;
 dbms_output.put_line(c_row.empno||'-'||c_row.ename||'-'||c_row.job||'-'||c_row.sal);
end loop;
--关闭游标
close c_job;
end;

--下面是我用for循环游标做的一个逐行缴费

create or replace procedure f_jaiofei (
  resultStr out char,
  r in CHAR,
  v_payment in NUMBER,
  n in NUMBER,
  m in CHAR,
  v_c_code in CHAR,
  v_id in CHAR,
  v_t in CHAR
) is
 paymentInfo number;
 Begin
declare
  --定义游标
cursor c_l_jdc is select * from l_jdc where c_code=v_c_code ORDER by deadline asc ;
v_l_jdc c_l_jdc%rowtype;
amoutpaidInfo l_jdc.amoutpaid%type;
begin
paymentInfo := v_payment;
if paymentInfo >r then ResultStr := '超出最大付款金额.';
return;
  --判断交纳最大限额
else
for v_l_jdc in c_l_jdc loop
   if paymentInfo>= (v_l_jdc.latefee+v_l_jdc.l_fine-v_l_jdc.amoutpaid) then
amoutpaidInfo:= v_l_jdc.latefee+v_l_jdc.l_fine;
paymentInfo:=paymentInfo-(v_l_jdc.latefee+v_l_jdc.l_fine)+v_l_jdc.amoutpaid ;
  update l_jdc set amoutpaid=amoutpaidInfo,paycondition='已交清' where l_no=v_l_jdc.l_no;
    else amoutpaidInfo:=paymentInfo+v_l_jdc.amoutpaid ;
    paymentInfo:=0;
    if amoutpaidInfo!=0  then update l_jdc set amoutpaid=amoutpaidInfo,paycondition='未交清' where l_no=v_l_jdc.l_no;
    else
    update l_jdc set amoutpaid=amoutpaidInfo,paycondition='未交费' where l_no=v_l_jdc.l_no;
    end if;
    end if;
      end loop;
   commit;
   end if ;
  end;
  --插入日志
  insert into log(lognumber,o_time,operationdetails,operator,c_code)
values('RZ'||RZ.nextval,sysdate,'支付了罚金'||v_payment||'元',v_id,v_c_code);
  --生成缴费单
insert into pay(pay_no,jf_time,fine,person,c_code,payment,rest_fine)
values('JF'||JDC_JF_FQ.nextval,v_t,r,v_id,v_c_code,v_payment,(r-v_payment));
ResultStr := 'success: 支付成功.';
End;

 

转载于:https://www.cnblogs.com/devin818/p/5090919.html