Oracle PL/SQL function to iterate through multiple rows

Question

A sample record in my xmltype is,

1 , XMLTYPE('<books> <book minprice = "10" maxprice="25"> .. </book> <book minprice = "15" maxprice="20"> </books>');

Following is my pl/sql function to find the max and min price in the whole plsql , which is incomplete.

create or replace 
FUNCTION DiffMaxMinPrice
 return double precision
is 
 diffprice double precision := 0;
 minprice long; 
 maxprice long;
 value long;
begin
 SELECT  x.* into minprice , maxprice 
       FROM sampletable ,
            XMLTABLE ('//book'
                      PASSING sampletable.xmlcol
                      COLUMNS maxprice VARCHAR2(30) PATH '@maxprice',
                              minprice VARCHAR2(30) PATH '@minprice') x 
                              where sampletable.indexid = 2;



 return 1;
end;

The select query returns multiple values, how to iterate through all those values.

I am using Oracle 11gr2.

Answer 1

I believe you're looking for a cursor. You could do something like...

    create or replace 
FUNCTION DiffMaxMinPrice
 return double precision
is 
 diffprice double precision := 0;
 minprice long; 
 maxprice long;
 value long;
begin
FOR row IN (
 SELECT  x.*
       FROM sampletable ,
            XMLTABLE ('//book'
                      PASSING sampletable.xmlcol
                      COLUMNS maxprice VARCHAR2(30) PATH '@maxprice',
                              minprice VARCHAR2(30) PATH '@minprice') x 
                              where tbltagdata.indexid = 2)
LOOP
... (work with each row here)
END LOOP;



 return 1;
end;