Re: stored proc for select statement

From: <bialik_at_wis.weizmann.ac.il>
Date: Thu, 09 Apr 1998 14:26:31 -0600
Message-ID: <6gj7d6$rit$1@nnrp1.dejanews.com>

In article <H5zW.189$Tf3.5121906_at_news.ipass.net>,   "David Sisk" <davesisk_at_ipass.net> wrote:
>
> Hi: Could you elaborate more? We've tried to do this before (from VB) and
> couldn't make it work. Please see below.....
>
> Thanks,
> Dave
>

 Hi.
  Following an example ( Oracle 7.3.2.3 ) that works just fine:

1. Table dfinition: create table mck0000 ( f0001 number(8) primary key, f0002 varchar2(20)); insert into mck0000 values ( 1, 'aaa'); insert into mck0000 values ( 2, 'bbb'); insert into mck0000 values ( 3, 'ccc'); insert into mck0000 values ( 4, 'ddd'); commit;
2. Create PACKAGE spec: CREATE OR REPLACE PACKAGE mck0000_ftch AS CURSOR mck0000_cr ( from_number NUMBER )IS SELECT * FROM mck0000 WHERE f0001 > from_number;

       PROCEDURE open_mck0000_cr ( from_number IN NUMBER );

       PROCEDURE fetch_mck0000_cr ( rc IN OUT NUMBER,
          mck0000_row IN OUT mck0000%rowtype );

       PROCEDURE close_mck0000_cr;

    END mck0000_ftch;
    /
  3. Create PACKAGE body:
    CREATE OR REPLACE PACKAGE BODY mck0000_ftch AS

     PROCEDURE open_mck0000_cr ( from_number IN NUMBER ) IS
     BEGIN
       OPEN mck0000_cr ( from_number );
     END open_mck0000_cr;

     PROCEDURE fetch_mck0000_cr ( rc IN OUT NUMBER,
       mck0000_row IN OUT mck0000%ROWTYPE ) IS
     BEGIN
       rc := 0;
       FETCH mck0000_CR INTO mck0000_row;
       IF mck0000_cr%NOTFOUND THEN
         rc := 1;
       END IF;
     END fetch_mck0000_cr;

     PROCEDURE close_mck0000_cr IS
     BEGIN
       CLOSE mck0000_cr;
     END close_mck0000_cr;

   END mck0000_ftch;
   /

  4. Execute stored procedures from PL/SQL ( anywhere ):    set serveroutput on
   DECLARE

     ret_code NUMBER;
     tab_row mck0000%ROWTYPE;
   BEGIN
     mck0000_ftch.open_mck0000_cr(2);
     ret_code := 0;
     mck0000_ftch.fetch_mck0000_cr ( ret_code, tab_row );
     WHILE ( ret_code = 0 ) LOOP
       DBMS_OUTPUT.PUT_LINE ( tab_row.f0001 || ' ' || tab_row.f0002);
       mck0000_ftch.fetch_mck0000_cr ( ret_code, tab_row );
     END LOOP;
     mck0000_ftch.close_mck0000_cr;

   END;
   /

   Michael Bialik.

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Thu Apr 09 1998 - 15:26:31 CDT