Re: How to exit a procedure if condition is true

Originally posted by Ethel Aardvark
> Yes, but it might do something different as it will simply skip NULLs
> rather than stop at the first one.
>
> ETA
>
> "Noel" wrote in message news:news:...
> > Uzytkownik "D Newsham" napisal w wiadomosci
> > news:c883e8dd.0305061349.18397f3d_at_posting.google.com"]new-
> s:c883e8dd.0305061349.18397f3d_at_posting.google.com[/url]...
> > > 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.

I'll bet a pound to a penny that id_no is a NOT NULL column, in fact it's the primary key, and that the original code was trying to register the v_cursor%NOTFOUND condition using the false assumption that the unsuccessful FETCH would set v_id to NULL. In which case Noel's procedure can drop the WHERE clause altogether for an even neater solution.

--
Posted via http://dbforums.com