Re: How to exit a procedure if condition is true

Why do the prime read then the loop (unless you are an old COBOL programmer).

 create or replace
procedure proc_cleanup
 is

    v_id             integer;
    v_quantity       integer;

    cursor v_cursor is
    select id_no, quantity
    from table1;
 BEGIN
    open v_cursor;

    loop

      fetch v_cursor into v_id, v_quantity;
      exit when v_cursor%notfound;

 ---****

• This is where I need to test for a value in the variable v_id IF V_ID IS NULL THEN EXIT; -- THIS PROCEDURE -- SYNTAX?? END IF; --- ---****

      insert into table2 values(v_id, v_quantity);     end loop;

    close v_cursor;

 end proc_cleanup;

"D Newsham" <d_newsham_at_hotmail.com> wrote in message news:c883e8dd.0305061349.18397f3d_at_posting.google.com...
> I have a procedure that I need to jump out of if a variable is null.
> Should I use an exception, and if so what is the syntax for exiting
> the procedure? I know I can't use "exit" unless I'm in a loop - I'm
> stumped!
>
> create or replace procedure proc_cleanup
> is
> begin
> DECLARE
> v_id integer;
> v_quantity integer;
>
> cursor v_cursor is
> select id_no, quantity
> from table1;
> BEGIN
> open v_cursor;
> --fetch first recordset to get initial values
> fetch v_cursor into v_id, v_quantity;
>
> ---****
> --- This is where I need to test for a value in the variable v_id
> IF V_ID IS NULL THEN
> EXIT THIS PROCEDURE -- SYNTAX??
> END IF;
> ---
> ---****
>
> --insert the fetched values into table2
> insert into table2 values(v_id, v_quantity);
> --loop through the remaining records and insert until finished
> loop
> fetch v_cursor into v_id, v_quantity;
> exit when v_cursor%notfound;
> insert into table2 values(v_id, v_quantity);
> end loop;
> close v_cursor;
> END;
> end proc_cleanup;
Received on Wed May 07 2003 - 07:31:31 CDT