Re: pl/sql

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Fri, 01 Oct 1999 11:23:02 -0400
Message-ID: <Gs70N2Dc7MmS4wlN+jp5AxPLOeq2@4ax.com>

A copy of this was sent to "Andreas Schwaiger" <andreas.schwaiger_at_strasser.at> (if that email address didn't require changing) On Fri, 1 Oct 1999 13:58:17 +0200, you wrote:

>i have following script
>
>TYPE Nodes IS TABLE OF NUMBER(10,0) INDEX BY BINARY_INTEGER;
>di_dist Nodes;
>
>When i fill the di_dist with Numbers, how can I get the minimum value
>without using a LOOP statement?
>Thanks
>Andi
>
>
>

I suppose you could use a GOTO and a label...

tkyte_at_8.0> declare

  2          TYPE Nodes IS TABLE OF NUMBER(10,0) INDEX BY BINARY_INTEGER;
  3          di_dist Nodes;
  4  
  4          idx number;
  5          maxVal number ;
  6  begin
  7          di_dist(1) := 1;
  8          di_dist(-2) := 2;
  9  
  9          idx := di_dist.first;
 10          <<LABEL1>>
 11                  if ( idx is null ) then GOTO LABEL2; end if;
 12                  if ( maxVal is null or di_dist(idx) > maxVal ) then
 13                          maxVal := di_dist(idx);
 14                  end if;
 15                  if ( idx <> di_dist.last ) then
 16                          idx := di_dist.next(idx);
 17                          GOTO LABEL1;
 18                  end if;
 19          <<LABEL2>>
 20          dbms_output.put_line( 'Max = ' || maxVal );

 21 end;
 22 /
Max = 2

PL/SQL procedure successfully completed.

but thats probably not what you meant :)

In 8.0 and up, you could use an Object Type instead of a plsql table and do this:

tkyte_at_8.0> create or replace type myTableType as table of number;   2 /

Type created.

tkyte_at_8.0>
tkyte_at_8.0> REM now, we want to "select * from PLSQL_FUNCTION()" not from a table:
tkyte_at_8.0>
tkyte_at_8.0> create or replace function getMyTableType return myTableType   2 as
  3 l_x myTableType := myTableType( 1, 2, 3, 4, 5, 6, 7, 8, 9 );   4 begin
  5 return l_x;
  6 end;
  7 /

Function created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> REM here we go... selecting from it:
tkyte_at_8.0> 
tkyte_at_8.0> select max( a.column_value )  val

  2 from THE ( select cast( getMyTableType() as mytableType ) from dual ) a   3 /

       VAL

---

         9

In 7.x, there is a 'trick' you can use to select from a plsql table. If you are interested in that, see: http://www.deja.com/getdoc.xp?AN=417733109&fmt=text for an example...

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

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

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Oct 01 1999 - 10:23:02 CDT