Oracle 常用的循环小结

来源：互联网发布：mysql 查询总数和分页编辑：程序博客网时间：2024/06/06 03:19

引言

作为程序语言中的三大基本逻辑之一的循环，这次在我的项目开发中也用到了不少，但是有一个问题不知道是不是蛮长时间没有使用导致，要用到的时候，不能第一时间完整地写出，也许是生疏了，也也许是之前为了方便都是从以前的笔记ctrl+v的缘故吧。

Oracle中五种循环：

1、普通循环： FOR i IN （1…N）

应用:

FOR i IN (1...100)  LOOP//循环语句END LOO;其实就相当于 for(int i=0;i<100;i++)

2、集合（游标）循环： FOR k IN (sql子查询结果集合/游标)

2.1集合循环应用：

 FOR k IN (Select ra2.Levelno，rownum + 8 ordernum              From (Select Distinct Cl.Levelno                    From Pdm_Ylzj_Crutoqualinspe c                    --只选择合并后的数据                    Inner Join PDM_YLZJ_CruToQualSumSheetD sd On sd.Pdm_Ylzj_Crutoqualinspeid =                                                               c.Pdm_Ylzj_Crutoqualinspeid                    Inner Join Pdm_Ylzj_Crutolevelstruc Cl On Cl.Pdm_Ylzj_Crutoqualinspeid =                                                              c.Pdm_Ylzj_Crutosendinspeid                                                              And c.Provincesno = p_province                                                              And c.Purchaselevel = i.PURCHASELEVEL                    Left Join Pdm_Ylzj_Recrutoqualinspe Fj On Fj.Inspectionno = c.Inspectionno                                                              And Nvl(Fj.Isdeleted, '0') = '0'                    Where Nvl(c.Isdeleted, 0) = 0                          And Cl.Leveltype = '2'                          And c.Provincesno = p_Province                          And Instr(Decode(p_Yearno, '', c.Yearno, p_Yearno), c.Yearno) > 0                    Order By Cl.Levelno Asc) ra2)    LOOP      v_tmpDiff2Ratio := F_GetStrutsLvlWeightRatio(p_Cigatype, p_Yearno, p_Projtype, p_Level, p_Part,                                                   p_Grade, p_Way, p_Inspeuser, p_Inspedatestart,                                                   p_Inspedateend, p_Province, p_city, p_country,                                                   p_Isqualify, i.Purchaselevel, k.levelno, '2');      v_upd2Diff2Sql := 'Update DynatempData dd set T' || k.ordernum || '=''' || v_tmpDiff2Ratio ||                        ''' where dd.Dynatempdataid=''' || v_Row2Guid || ''' and dd.conditionid=''' ||                        p_conditionId || '''';      Execute Immediate v_upd2Diff2Sql;    END LOOP;

2.2 游标For循环应用

create or replace procedure p_cursor_Employee is EmpRows Employees%rowtype; cursor empRows is select * from Employees; begin for EmpRows in empRows loop dbms_output.put_line(EmpRows.EmployeeId||’,'||EmpRows.Name||’,'||empRows%rowcount); end loop; end p_cursor_Employee;

3、WHILE 循环：While 逻辑判断式 Loop …End Loop

应用：

While i<100 Loopi :=i+2;End loop;

4、EXIT WHEN ：Exit when（逻辑表达式）

应用例

CREATE OR REPLACE PROCEDURE proc_test_exit_when IS  i Number;BEGIN  i := 0;  LOOP    Exit When(i > 10);    Dbms_Output.put_line(i);    i := i + 1;  END LOOP;END;当i>10 时退出循环，否则一直执行

0 0