martedì 4 febbraio 2014

Oracle Array und Hash table



I was thinking that in Oracle PLSQL the concept of Array is not implemented at his best!
Then I saw this implementation. Basically they decided to implement the concept of Array and of Hash table with ALMOST the same concept, simply distinguishing on the way you (developer) index it.


HASH TABLE:

 set serveroutput on  
 DECLARE  
  TYPE assoc_array IS TABLE OF VARCHAR2(30)  
  INDEX BY VARCHAR2(30);  
  state_array assoc_array;  
 BEGIN  
  state_array('Alaska') := 'Juneau';  
  state_array('California') := 'Sacramento';  
  state_array('Oregon') := 'Salem';  
  state_array('Washington') := 'Olympia';  
  dbms_output.put_line(state_array('Alaska'));  
  dbms_output.put_line(state_array('California'));  
  dbms_output.put_line(state_array('Oregon'));  
  dbms_output.put_line(state_array('Alaska'));  
 END;  
 /  



ARRAY:

 set serveroutput on  
 DECLARE  
  TYPE bin_array IS TABLE OF VARCHAR2(30)  
  INDEX BY BINARY_INTEGER;  
  state_array bin_array;   
 BEGIN  
  state_array(1) := 'Alaska';  
  state_array(2) := 'California';  
  state_array(3) := 'Oregon';  
  state_array(4) := 'Washington';  
  FOR i IN 1 .. state_array.COUNT LOOP  
   dbms_output.put_line(state_array(i));  
  END LOOP;  
 END;  
 /  
 CREATE TABLE t (  
 resultcol VARCHAR2(20));  
 DECLARE  
  TYPE bin_array IS TABLE OF VARCHAR2(30)  
  INDEX BY BINARY_INTEGER;  
  state_array bin_array;   
 BEGIN  
  state_array(1) := 'Alaska';  
  state_array(2) := 'California';  
  state_array(3) := 'Oregon';  
  state_array(4) := 'Washington';  
  FORALL i IN 1 .. state_array.COUNT  
  INSERT INTO t VALUES (state_array(i));  
  COMMIT;  
 END;  
 /  
 SELECT * FROM t;  


Well note that this is not really an array, but is an hash where the indexes are integers.

Source:
http://psoug.org/reference/arrays.html

Nessun commento:

Posta un commento