Re: SUM using PL/SQL?

From: Tomm Carr <tommcatt_at_geocities.com>
Date: 1997/07/31
Message-ID: <33E12446.2BDD@geocities.com>#1/1

tdrudy_at_ix.netcom.com wrote:
>
> The question: I want to use a PL/SQL procedure(s) which will
> accept the retrieval parms from the Webserver, do the summing or
> counting based on those parms, and pass results back to the user.
> The COUNT function in PL/SQL 2.3 only retruns counts of retrieved rows,
> while I need counts of internal column values; also, there is no SUM
> per se within PL/SQL,

Huh? You say you are passing the retrieval parameter to the procedure. So the procedure will, I assume, execute a cursor based on the parameters. So why can't you use the SUM function there?

  procedure GetSum( Params in varchar2, Total out number )   is
  begin

      select SUM( numvalue )
      into Total
      from mytable
      where otherCol = Params ;

  end;

> and I do not want to use SQL within PL/SQL
> (such as the DBMS_SQL package). Is there a way to do the summing of
> internal column values using PL/SQL?

If the parameters you pass in are more complicated than can be done like above -- such as containing the text of the where clause itself -- you will have to use dynamic SQL (DBMS_SQL). Is that so terrible?

-- 
Tomm Carr
--
"Can you describe your assailant?"
"No problem, Officer.  That's exactly what I was doing when he hit me!"

Received on Thu Jul 31 1997 - 00:00:00 CDT