Home » SQL & PL/SQL » SQL & PL/SQL » FORALL with associative array and INDEX BY VARCHAR (xe 11g R2)
FORALL with associative array and INDEX BY VARCHAR [message #681013] Sat, 06 June 2020 22:05 Go to next message
hudo
Messages: 165
Registered: May 2004
Senior Member
My import table looks like
CREATE TABLE IMPORT_TAB ( IDNR VARCHAR2(10) , PIECES NUMBER )
ALTER TABLE IMPORT_TAB ADD (
	CONSTRAINT IMPORT_TAB_PK PRIMARY KEY ( IDNR ));

INSERT INTO IMPORT_TAB ( IDNR , PIECES ) VALUES ( '012.34' , 5 );
INSERT INTO IMPORT_TAB ( IDNR , PIECES ) VALUES ( '012.34A' , 3 );
INSERT INTO IMPORT_TAB ( IDNR , PIECES ) VALUES ( '012.34B' , 1 );
INSERT INTO IMPORT_TAB ( IDNR , PIECES ) VALUES ( '056.78B' , 3 );
INSERT INTO IMPORT_TAB ( IDNR , PIECES ) VALUES ( '789.34A' , 2 );
INSERT INTO IMPORT_TAB ( IDNR , PIECES ) VALUES ( '789.34C' , 5 );
/
In my TARGET_TAB ( IDNR VARCHAR2(10), PIECES1 NUMBER, MYCOL3 NUMBER, ... )
are only IDNR without a letter.
I like to get the sum of PIECES for 'similar' IDNR and write those sums into TARGET_TAB.PIECES1 , with the mentioned IDNR something like


UPDATE TARGET_TAB SET PIECES1 = 9 WHERE IDNR = '012.34';  -- where 9 is the sum of all '012.34%' in IMPORT_TAB
UPDATE TARGET_TAB SET PIECES1 = 3 WHERE IDNR = '056.78';
UPDATE TARGET_TAB SET PIECES1 = 7 WHERE IDNR = '789.34';
I populated a collection of type associative array

TYPE kdmenge_tab IS TABLE OF NUMBER   
    INDEX BY VARCHAR2( 10 );
kdmenge     kdmenge_tab;

l_idnr TARGET_TAB.IDNR%TYPE;

CURSOR c1 IS
SELECT IDNR, PIECES FROM IMPORT_TAB ORDER BY IDNR;

counter NUMBER;

BEGIN
counter := 0 ;

-- populate collection
FOR rec in c1
LOOP
	counter := counter +1 ;
	l_idnr := REGEXP_REPLACE ( rec.IDNR , 'A|B|C|D|E' , '' ) ;
	
	IF ( kdmenge.EXISTS( l_idnr ) ) THEN
		kdmenge( l_idnr ) := kdmenge( l_idnr ) + rec.PIECES ;
	ELSE
		kdmenge( l_idnr ) := rec.PIECES ;
	END IF;

NULL;
END LOOP;
I wonder if it is possible to use FORALL for updating the TARGET_TAB, in order to minimize context switches between PL/SQL and SQL. My problem is the INDEX BY VARCHAR of the associative array


At the moment I just use a while loop

l_idnr := kdmnege.FIRST;
WHILE ( l_idnr IS NOT NULL ) 
LOOP
	UPDATE TARGET_TAB
	SET PIECES1 = kdmenge( l_idnr )
	WHERE TARGET_TAB.IDNR = l_idnr ;
	
	l_idnr := kdmenge.NEXT( l_idnr );
END LOOP;
Maybe even the population of the collection can be made faster with regard to minimized context switches.




[Updated on: Sat, 06 June 2020 22:27]

Report message to a moderator

Re: FORALL with associative array and INDEX BY VARCHAR [message #681015 is a reply to message #681013] Sun, 07 June 2020 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why not just something like:
UPDATE TARGET_TAB  T
SET PIECES1 = ( SELECT SUM(PIECES) FROM IMPORT_TAB I WHERE I.IDNR LIKE T.IDNR||'%' )
WHERE EXISTS ( SELECT NULL FROM IMPORT_TAB I WHERE I.IDNR LIKE T.IDNR||'%' )
/
Re: FORALL with associative array and INDEX BY VARCHAR [message #681018 is a reply to message #681015] Sun, 07 June 2020 04:12 Go to previous messageGo to next message
hudo
Messages: 165
Registered: May 2004
Senior Member
@Michel,

nice solution, thx.

What about the use of FORALL , if the associative array is INDEX(-ed) BY VARCHAR or non-number in general ?


Re: FORALL with associative array and INDEX BY VARCHAR [message #681019 is a reply to message #681018] Sun, 07 June 2020 05:25 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Never do in PL/SQL what can be done in SQL.

And, to answer your question, just try it and give us the result. Wink

Previous Topic: Adding [CDATA[ element in an XML
Next Topic: Commands in SQl
Goto Forum:
  


Current Time: Thu Mar 28 20:01:27 CDT 2024