ORACLE: How to Use array in PL/SQL

Because PL/SQL does not contain arrays, this is an example of data query and an example of self-writing to explain how to use arrays in PL/SQL. Many people may already know about it, but they just want to know about it.

---------------------- Single-Dimension Array ------------------------

DECLARE TYPE emp_ssn_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; best_employees emp_ssn_array; worst_employees emp_ssn_array; BEGIN best_employees(1) := '123456'; best_employees(2) := '888888'; worst_employees(1) := '222222'; worst_employees(2) := '666666'; FOR i IN 1..best_employees.count LOOP DBMS_OUTPUT.PUT_LINE('i='|| i || ', best_employees= ' ||best_employees(i) || ', worst_employees= ' ||worst_employees(i)); END LOOP; END;

---------------------- Multi-dimensional array ------------------------

DECLARE TYPE emp_type IS RECORD ( emp_id employee_table.emp_id%TYPE, emp_name employee_table.emp_name%TYPE, emp_gender employee_table.emp_gender%TYPE ); TYPE emp_type_array IS TABLE OF emp_type INDEX BY BINARY_INTEGER; emp_rec_array emp_type_array; emp_rec emp_type; BEGIN emp_rec.emp_id := 300000000; emp_rec.emp_name := 'Barbara'; emp_rec.emp_gender := 'Female'; emp_rec_array(1) := emp_rec; emp_rec.emp_id := 300000008; emp_rec.emp_name := 'Rick'; emp_rec.emp_gender := 'Male'; emp_rec_array(2) := emp_rec; FOR i IN 1..emp_rec_array.count LOOP DBMS_OUTPUT.PUT_LINE('i='||i ||', emp_id ='||emp_rec_array(i).emp_id ||', emp_name ='||emp_rec_array(i).emp_name ||', emp_gender = '||emp_rec_array(i).emp_gender); END LOOP; END; -------------- Result -------------- i=1, emp_id =300000000, emp_name =Barbara, emp_gender = Female i=2, emp_id =300000008, emp_name =Rick, emp_gender = Male

Note: PL/SQL does not contain arrays. But if programmers want to use array, they have to work around it. It is quite useful to use type and table of record instead of multi-dimensional arrays.

Emp_type is like a record in a table, which contains ID, name, gender, and so on. Emp_type_array is like a table, which contains a record (emp_type) Like a multi-dimensional array.

(T111)

Copy From: http://tech.ccidnet.com/art/1105/20050802/300959_1.html