PL/SQL 语句块初次接触（游标使用，实现loop的continue）

语句块结构

 

       包含三部分：声明部分、执行部分和异常部分，其中执行部分是必须的，声明和异常部分是可选的。

 

DECLARE
  --声明语句
BEGIN
  --执行语句
EXCEPTION
  --异常语句
END；

 

oracle 语句块，没有continue功能，可以用goto 实现：

下午写的一个简单的语句块功能：

      create table tmp_fjx
      (
              MPOLICYNO CHAR(15),
              POLICYNO  CHAR(15),
              CLASSCODE CHAR(6),
              BEGINDATE DATE
       );
DECLARE
      MPOLICYNO  CHAR(15);
      POLICYNO   CHAR(15);
      CLASSCODE  CHAR(6);
      BEGINDATE  DATE;
      TMP_POLICYNO   CHAR(15);
      CURSOR c_policyno IS
      select b.policyno mpolicyno,b.policyno,b.classcode,b.begdate
      from riskcon a,riskcon b
      where a.policyno=b.policyno
      and a.appf='1' and b.appf='2'
      union
      select b.policyno mpolicyno,b.policyno,b.classcode,b.begdate
      from appnext a ,riskcon b
      where  a.policyno!=a.mpolicyno and length(a.policyno)=15 and length(a.mpolicyno)=15
      and a.policyno=b.policyno and a.classcode=b.classcode and b.appf='2'
      order by begdate desc;
BEGIN
      TMP_POLICYNO :='';
      OPEN c_policyno;
      LOOP
           <<next>>
           FETCH c_policyno INTO MPOLICYNO,POLICYNO,CLASSCODE,BEGINDATE;
           EXIT WHEN c_policyno%NOTFOUND;
           IF(MPOLICYNO != TMP_POLICYNO) THEN
                   insert into tmp_fjx values(MPOLICYNO,POLICYNO,CLASSCODE,BEGINDATE);               
                   TMP_POLICYNO := MPOLICYNO;
           ELSE
                        GOTO next;
           END IF;
         
      END LOOP;
      COMMIT;
EXCEPTION
      WHEN OTHERS
      THEN
      ROLLBACK;
      RAISE;    
END