Re: How to create oracle procedure??

From: Maxim V. Krapivin <baltros_at_mail.wplus.net>
Date: Tue, 5 May 1998 15:41:06 +0400
Message-ID: <354efaee.0@nam>

Josef wrote :
>How can I create a stored procedure? The procedure
>have to select
>the last column(datum) of the table. I' m using Oracle 7.3!!

You can create a simple procedure that returns simple result this way :

Create or replace procedure Get_Datum (aParam in <Your DataType>, aDatum out Date) IS
BEGIN
    SELECT Datum INTO aDatum FROM WetterDaten WHERE criteria_column = aParam;
END;
/

And if you want to receive a set of data you can use packages:

Create or Replace PACKAGE MyPack IS

• DataTypes -------- TYPE DatumList IS TABLE OF Date INDEX BY BINARY_INTEGER;

procedure Get_Datum (aParam in <Your DataType>, aDatum in out DatumList);

END;
/

Create Or Replace PACKAGE BODY MyPack IS Procedure Get_Datum (aParam in <Your DataType>, aDatum in out DatumList) IS

CURSOR DatumCur IS
  SELECT Datum FROM WetterDaten
   WHERE criteria_column = aParam;

i NUMBER;
CurDate Date;

BEGIN
    i := 0;
    Open DatumCur;
    While True Loop

        Fetch DatumCur INTO CurDate;
        if DatumCur%NotFound then Exit; end if;

        i := i + 1;
        aDatum(i) := CurDate;

    End Loop;

END;
END;
/

Regards. Maxim. maximka_kr_at_usa.net Received on Tue May 05 1998 - 06:41:06 CDT