Re: stored procedure howto???

"Björn Nilsson" schrieb:
>
> Hi all!
>
> Feels like an FAQ, but anyhow... (BTW, is there an FAQ for this
> group?)
>
> I'm building an ASP application connecting to an Oracle 7.3 DB. I'd
> like to put my queries in stored procedures for both preformance and
> other (read: Hiding the database specifics in the db.) reasons.
> What I want to be returned when I call the procedure with some
> parameters like e.g. FROM_DATE and TO_DATE in the example below, is a
> rowset that I process and print to the users browser.
> Now, I did think that constructing the procedures would be an easy
> task, somewhat like (There's maybe some syntax errors below, but from
> the top of my head.):
>
> CREATE OR REPLACE PROCEDURE GET_CASES_1
> (FROM_DATE IN TABLE1.DATE%TYPE, TO_DATE IN TABLE1.DATE%TYPE)
> BEGIN
> SELECT
> TABLE1.NAME, TABLE2.ADDRESS, TABLE2.CITY
> FROM
> TABLE1, TABLE2
> WHERE
> TABLE1.ID = TABLE2.ID (+) AND
> TABLE1.DATE_CREATED >= FROM_DATE AND
> TABLE1.DATE_CREATED <= TO_DATE;
> END GET_CASES_1;
> /
>
> which is roughly the way I thought it should be done. But from the
> examples I've seen about PL/SQL the above example is far from what it
> really should look like. Rather it seems like I'm supposed to use a
> cursor and loop through that. What confuses me in these examples is
> that they are all SQL-Plus orientated and all use
> DBMS_OUTPUT.PUT_LINE('Name= ' || NAME || ' Address= ' || ADDRESS || '
> City= ' || CITY);
> which, as far as I remember is a concatenated textstring, and not the
> recordset that I'm looking for.
>
> Does someone have any examples/receipies on how queries like the above
> (and also more complicated ones) are "transformed" into procedures
> that returns recordsets as per usual? (With IN parameters in this
> case, but that is not really the point.)
>
> Any help much appreciated.
>
> Björn

Hi,

use ref_cursors to reach your aim. Also use oracle objects for OLE to pass the parameters to your DB and fetch the records. OO4O is shipperd for free with the oracle client sw. Just install them and read %oracle_home%\mshelp\oracleo.hlp andhave a look at the samples.

Example for ref cursor:

	PROCEDURE ABRECHNUNGSLISTE
	(
		p_KB IN NUMBER,
		p_ZEDatum IN VARCHAR2, 
		p_BeginnDatum IN VARCHAR2, 
		p_EndeDatum IN VARCHAR2, 
		p_Cur IN OUT REF CURSOR
	)
	IS
		l_ZEDatum Date;
		l_BeginnDatum Date;
		l_EndeDatum Date;
	BEGIN
		l_ZEDatum := TO_DATE(p_ZEDatum, 'YYYY-MM-DD');
		l_BeginnDatum := TO_DATE(p_BeginnDatum, 'YYYY-MM-DD');
		l_EndeDatum := TO_DATE(p_EndeDatum, 'YYYY-MM-DD');
		OPEN p_Cur
		FOR
		SELECT 
			Protokolliert, 
			Kunde, 
			Objektanschrift, 
			DECODE( SIGN(OPBetrag-NVL(SummeBis, 0)-SUM(Betrag)), -1,
OPBetrag-NVL(SummeBis, 0), SUM(Betrag)) AS Betrag,
			Anteil, 
			Satz 
		FROM 
			V_PROV_ZELISTE ZE, 
			(
			SELECT 
				OPNummer AS OPNr,
				OPArt, 
				SUM(Betrag) as SummeBis 
			FROM 
				(
				SELECT 
					NP.OPNummer,
					12 OPArt, 
					NP.Betrag 
				FROM 
					(SELECT --Zahlungseingänge bis zum Abrechnungsdatum
						DECODE(SIGN(OPBetrag - SUM(Betrag)), -1, OPBetrag, SUM(Betrag)) As
Betrag, 
						VertragsNr, 
						Art, 
						OPNummer, 
						OPBetrag  
					FROM 
						V_PROV_NTPROV  
					WHERE 
						ZEDatum <= l_ZEDatum
					GROUP BY 
						OPNummer, 
						Art, 
						OPBetrag, 
						VertragsNr
					)NP
				GROUP BY 
					NP.OPNummer,
					12, 
					NP.Betrag 
				) 
			GROUP BY 
				OPNummer, 
				OPArt
			) Bis
		WHERE 
			ZE.OPNummer = Bis.OPNr (+) 
		AND 
			ZE.OPArt = Bis.OPArt (+) 
		AND 
			ZE.KBNr = p_KB 
		AND 
			ZE.Datum BETWEEN 
						l_BeginnDatum
					AND 
						l_EndeDatum
		GROUP BY 
			OPNummer, 
			OPBetrag, 
			SummeBis, 
			Protokolliert, 
			Kunde, 
			Objektanschrift, 
			Satz, 
			Anteil
		;
	END;

HTH
Matthias
--
grema_at_t-online.de

Es gibt nichts Neues mehr.
Alles, was man erfinden kann, ist schon erfunden worden. Charles H. Duell, Leiter des US Patentamtes bei seinem Rücktritt 1899 Received on Tue Feb 16 1999 - 05:07:15 CST