Oracle - Procedure 문법_Cursor_01

[ Oracle - Procedure 문법_Cursor_01 ]

 

declare

    x_sa_code    number;

    x_tax_io     number;

    x_io_date    date;

    x_io_no      number;

    x_tax_sno    number;

    CURSOR c_aaa is select distinct SA_CODE,TAX_IO,IO_DATE from ACC.TAX;

    CURSOR c_bbb is select TAX_SNO

                      from ACC.TAX

                     where SA_CODE=x_sa_code and TAX_IO=x_tax_io and IO_DATE=x_io_date;

begin

    open c_aaa;

 

    loop

        fetch c_aaa into x_sa_code,x_tax_io,x_io_date;

 

        exit when c_aaa%notfound;

 

        x_io_no:=0;

 

        open c_bbb;

        loop

                      c_bbb into x_tax_sno;

                      when c_bbb%notfound;

                      x_io_no:=x_io_no+1;

 

                      update ACC.TAX

                      set    IO_NO=x_io_no

              where  TAX_SNO=x_tax_sno;

        end loop;

 

        close c_bbb;

    commit;

 

    end loop;

    close c_aaa;

 

  exception

      when others then

         rollback;

         raise_application_error(-20001,sqlErrm(SqlCode));

end;