Re: How to exit a procedure if condition is true

Uzytkownik "D Newsham" <d_newsham_at_hotmail.com> napisal w wiadomosci 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;

WHY so complex?????

create or replace procedure proc_cleanup  is
 begin

  INSERT INTO TABLE2(col1,col2)
   SELECT id_no, quantity
     FROM table1
    WHERE id_no IS NOT NULL;

end proc_cleanup;

Isn't it better ??

--
Noel.




-- 
Serwis Usenet w portalu Gazeta.pl -> http://www.gazeta.pl/usenet/