Re: PL/SQL Newbie: Porting the SUSPEND statement from other RDBMS's to Oracle

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sun, 14 Feb 1999 21:26:10 GMT
Message-ID: <36c83d51.5326449@192.86.155.100>

A copy of this was sent to slf_at_compulink.gr (if that email address didn't require changing) On Sun, 14 Feb 1999 21:00:12 +0200, you wrote:

>In several RDBMS's there is a SUSPEND statement that suspends execution
>of a select stored proc, passes control to the caller along with the
>values of the
>OUT parameters, and resumes execution of the sp with the next fetch.
>
>For example:
>
>CREATE PROCEDURE test
>RETURNS (retval INTEGER) <-------- the OUT parameter as defined
>in Interbase
>AS
>BEGIN
>FOR SELECT aFIELD FROM aTABLE
>INTO :retval
>DO
> BEGIN
> SUSPEND; <--------- here the control is passed to the caller
>statement
> along with the value of the :retval
>parameter.
> END;
>END;
>
>When I do a
>SELECT * FROM test
>in effect I get all the aFIELDs of aTABLE.
>
>How can I do this in PL/SQL?

you would use modular code in a package. A package can maintain a state between calls so you can open a cursor in a package and return from procedures and let the cursor stay open. You can maintain any sort of state you want in a package (not just cursors, variables, arrays, records, etc can be maintained as well)

for example:

SQL> create or replace package my_pkg
  2 as
  3 procedure open_cursor;
  4
  4 procedure get_some_data( p_empno out number );   5
  5 procedure clean_up;
  6
  6 end;
  7 /

Package created.

SQL>
SQL> create or replace package body my_pkg   2 as
  3
  3 cursor c1 is select empno from emp;   4
  4
  4 procedure open_cursor
  5 is
  6 begin
  7 open c1;
  8 end;
  9
  9 procedure get_some_data( p_empno out number )  10 is
 11 begin

 12          fetch c1 into p_empno;
 13          if ( c1%notfound ) then
 14                  raise no_data_found;
 15          end if;

 16 end;
 17
 17 procedure clean_up
 18 is
 19 begin
 20 close c1;
 21 end;
 22
 22 end;
 23 /

Package body created.

The following shows how a program might interact with such a stateful package:

SQL> exec my_pkg.open_cursor
PL/SQL procedure successfully completed.

SQL> variable x number

SQL> exec my_pkg.get_some_data(:x)
PL/SQL procedure successfully completed.

SQL> print x

         X

---

      7369

SQL> exec my_pkg.get_some_data(:x)
PL/SQL procedure successfully completed.

SQL> print x

         X

---

      7499

SQL> exec my_pkg.get_some_data(:x)
begin my_pkg.get_some_data(:x); end;

*
ERROR at line 1:
ORA-01403: no data found

SQL> exec my_pkg.clean_up
PL/SQL procedure successfully completed.  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sun Feb 14 1999 - 15:26:10 CST