load each set of values into a collection and then do:
If collection_a = collection_b THEN ...
no looping required.
Here is an example that you can adapt: use the SELECT shown at the bottom in
place of the FROM DUAL parts.
CREATE OR REPLACE type integer_table is table of integer;
DECLARE
vendor_key_table integer_table;
vendor_key_table2 integer_table;
CURSOR tst
IS
SELECT purch_order, SUM (dollars),
CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
FROM DUAL
UNION ALL
SELECT 1 purch_order, 8 dollars, 123452 vendor_key
FROM DUAL
UNION ALL
SELECT 2 purch_order, 7 dollars, 433738 vendor_key
FROM DUAL
UNION ALL
SELECT 2 purch_order, 4 dollars, 383738 vendor_key
FROM DUAL
UNION ALL
SELECT 2 purch_order, 5 dollars, 387118 vendor_key
FROM DUAL)
GROUP BY purch_order;
CURSOR tst2
IS
SELECT purch_order, SUM (dollars),
CAST (COLLECT (TO_NUMBER (vendor_key)) AS integer_table)
FROM (SELECT 1 purch_order, 3 dollars, 435235 vendor_key
FROM DUAL
UNION ALL
SELECT 2 purch_order, 4 dollars, 383738 vendor_key
FROM DUAL
UNION ALL
SELECT 2 purch_order, 7 dollars, 433738 vendor_key
FROM DUAL
UNION ALL
SELECT 2 purch_order, 5 dollars, 387118 vendor_key
FROM DUAL)
GROUP BY purch_order;
v_purch_order NUMBER;
v_dollars NUMBER;
mystr VARCHAR2 (4000);
v_purch_order2 NUMBER;
v_dollars2 NUMBER;
mystr2 VARCHAR2 (4000);
BEGIN
OPEN tst;
open tst2;
LOOP
mystr := NULL;
mystr2 := NULL;
FETCH tst
INTO v_purch_order, v_dollars, vendor_key_table;
FETCH tst2
INTO v_purch_order2, v_dollars2, vendor_key_table2;
IF tst%NOTFOUND
THEN
EXIT;
END IF;
if vendor_key_table = vendor_key_table2 then
dbms_output.put_line('equal');
else
dbms_output.put_line(' not equal');
end if;
-- loop throught the collection and build a string so that
-- we can display it and prove that it works
FOR cur1 IN (SELECT COLUMN_VALUE vendor_key
FROM TABLE (vendor_key_table))
LOOP
mystr := mystr || ',' || cur1.vendor_key;
-- /* based on the value of the sum, you can do something with each
detail*/
-- if v_dollars > 12 then
-- UPDATE VENDOR SET paid_status = 'P' where vendor_key =
cur1.vendor_key;
-- end if;
END LOOP;
DBMS_OUTPUT.put_line ( 'Purchace Order-> '
|| TO_CHAR (v_purch_order)
|| ' dollar total-> '
|| TO_CHAR (v_dollars)
|| ' vendorkey list-> '
|| SUBSTR (mystr, 2));
-- loop throught the collection and build a string so that
-- we can display it and prove that it works
FOR cur2 IN (SELECT COLUMN_VALUE vendor_key
FROM TABLE (vendor_key_table2))
LOOP
mystr2 := mystr2 || ',' || cur2.vendor_key;
END LOOP;
DBMS_OUTPUT.put_line ( 'Purchace Order-> '
|| TO_CHAR (v_purch_order2)
|| ' dollar total-> '
|| TO_CHAR (v_dollars2)
|| ' vendorkey list-> '
|| SUBSTR (mystr2, 2));
END LOOP;
CLOSE tst;
CLOSE tst2;
END;
/* using a SUM function but saving the detail from each row */
/* this technique could be used instead of procedural logic which
manually sums the desired column and then tests if the grouping
column value has changed. There is no need for 'save previous values'
or 'if first_time' or 'if last_time' code
*/
-- CREATE OR REPLACE type integer_table is table of integer;
-- the above statement must be given in order to create the integer_table
SELECT EXTRACTVALUE (VALUE (d), '/g') AS rslt
FROM TABLE
(XMLSEQUENCE
(EXTRACT
(XMLTYPE.createxml ( '<row><g>'
|| REPLACE
('393938,9383739,93836,99977',
',',
'</g><g>'
)
|| '</g></row>'
),
'/row/g'
)
)
) d;
Mike
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
