Example 1: VARRAY TYPE 1
DECLARE
TYPE namesarray IS VARRAY(5) OF VARCHAR2(10);
names_ namesarray;
TYPE grades IS VARRAY(5) OF INTEGER;
marks_ grades;
total_ INTEGER;
BEGIN
names_ := namesarray('Name 1', 'Name 2', 'Name 3', 'Name 4', 'Name 5');
marks_ := grades(12, 15, 36, 18, 21);
total_ := names_.COUNT;
dbms_output.put_line('Total '|| total_ || ' Students');
FOR i IN 1 .. total_ LOOP
Dbms_Output.Put_Line('Student: ' || names_(i) || ' Marks: ' || marks_(i));
END LOOP;
END;
Example 2: VARRAY TYPE 2
DECLARE
CURSOR c_customers is
SELECT name, customer_id
FROM customer_info
WHERE ROWNUM <= 5;
TYPE c_list IS VARRAY (5) OF customer_info.name%TYPE;
name_list c_list := c_list();
counter INTEGER := 0;
BEGIN
FOR n IN c_customers LOOP
counter := counter + 1;
name_list.extend;
name_list(counter) := n.name;
Dbms_Output.Put_Line('Customer(' || counter || '):' || name_list(counter));
END LOOP;
END;
Example 3: SINGLE-VALUE ARRAY
DECLARE
TYPE country_tab_1 IS TABLE OF VARCHAR2(50) INDEX BY VARCHAR2(5);
t_country_1 country_tab_1;
TYPE country_tab_2 IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
t_country_2 country_tab_2;
BEGIN
-- Example 1
-- Populate lookup
t_country_1('UK') := 'United Kingdom';
t_country_1('US') := 'United States of America';
t_country_1('FR') := 'France';
t_country_1('DE') := 'Germany';
-- Find country name for ISO code "&cc"
Dbms_Output.Put_Line('ISO code "UK" = ' || t_country_1(upper('UK')));
Dbms_Output.Put_Line('ISO code "US" = ' || t_country_1(upper('US')));
Dbms_Output.Put_Line('ISO code "FR" = ' || t_country_1(upper('FR')));
Dbms_Output.Put_Line('ISO code "DE" = ' || t_country_1(upper('DE')));
----------------------------------------------------------------------
-- Example 2
-- Populate lookup
t_country_2(1) := 'United Kingdom';
t_country_2(2) := 'United States of America';
t_country_2(3) := 'France';
t_country_2(4) := 'Germany';
FOR i_ IN 1 .. t_country_2.COUNT LOOP
-- Find country name for ISO code
Dbms_Output.Put_Line('ISO code = ' || t_country_2(i_));
END LOOP;
END;
Example 4: Array from a RECORD TYPE | RECORD TYPE ARRAY
DECLARE
TYPE country_type IS RECORD (
iso_code VARCHAR2(5),
name VARCHAR2(50)
);
TYPE country_tab IS TABLE OF country_type INDEX BY BINARY_INTEGER;
t_country country_tab;
BEGIN
-- Populate lookup
t_country(1).iso_code := 'UK';
t_country(1).name := 'United Kingdom';
t_country(2).iso_code := 'US';
t_country(2).name := 'United States of America';
t_country(3).iso_code := 'FR';
t_country(3).name := 'France';
t_country(4).iso_code := 'DE';
t_country(4).name := 'Germany';
-- Find country name for ISO code "DE"
FOR i IN 1 .. t_country.COUNT LOOP
Dbms_Output.Put_Line('ISO code "' || t_country(i).iso_code || '" = ' || t_country(i).name);
END LOOP;
END;
Example 5: Array from a CURSOR | CURSOR TYPE
DECLARE
rec_limit_ NUMBER := 100;
fetch_limit_ CONSTANT PLS_INTEGER DEFAULT 9;
CURSOR get_co_info IS
SELECT t.order_no,
t.contract,
t.customer_no,
t.currency_code
FROM customer_order_tab t;
TYPE customer_order_list_tab IS TABLE OF get_co_info%ROWTYPE INDEX BY BINARY_INTEGER;
customer_order_list customer_order_list_tab;
PROCEDURE Print_Info (
rec_ IN get_co_info%ROWTYPE )
IS
BEGIN
Dbms_Output.Put_Line(rec_.order_no || ' - ' ||
rec_.contract || ' - ' ||
rec_.customer_no || ' - ' ||
rec_.currency_code);
END Print_Info;
BEGIN
OPEN get_co_info;
LOOP
FETCH get_co_info BULK COLLECT INTO customer_order_list LIMIT fetch_limit_;
EXIT WHEN rec_limit_ <= 0;
Dbms_Output.Put_Line('Retrieved: ' || customer_order_list.COUNT);
FOR i_ IN 1 .. customer_order_list.COUNT LOOP
Print_Info(customer_order_list(i_));
END LOOP;
rec_limit_ := rec_limit_ - fetch_limit_;
END LOOP;
CLOSE get_co_info;
END;Related Posts:
Related Links:
Comments