Question

如何在oracle建一个存储过程来遍历数组，新手求解

Answer 1

　　SQL code
　　DECLARE
-- Define a varray of twelve strings.
TYPE months_varray IS VARRAY(12) OF STRING(9 CHAR);

-- Define an associative array of strings.
TYPE calendar_table IS TABLE OF VARCHAR2(9 CHAR)
INDEX BY BINARY_INTEGER;

-- Declare and construct a varray.
month MONTHS_VARRAY :=
months_varray('January','February','March'
,'April','May','June'
,'July','August','September'
,'October','November','December');

-- Declare an associative array variable.
calendar CALENDAR_TABLE;
BEGIN
-- Check if calendar has no elements.
IF calendar.COUNT = 0 THEN
-- Print a title

DBMS_OUTPUT.PUT_LINE('Assignment loop:');
DBMS_OUTPUT.PUT_LINE('----------------');
-- Loop through all the varray elements.

FOR i IN month.FIRST..month.LAST LOOP
-- Initialize a null associative array element.
calendar(i) := '';
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
-- Assign the numeric index valued varray element
-- to an equal index valued associative array element.
calendar(i) := month(i);
END LOOP;
-- Print a title
DBMS_OUTPUT.PUT(CHR(10));
DBMS_OUTPUT.PUT_LINE('Post-assignment loop:');
DBMS_OUTPUT.PUT_LINE('---------------------');
-- Loop through all the associative array elements.
FOR i IN calendar.FIRST..calendar.LAST LOOP
-- Print an indexed element from the associative array.
DBMS_OUTPUT.PUT_LINE(
'Index ['||i||'] is ['||calendar(i)||']');
END LOOP;
END IF;
END;