Oracle PL/SQL之處理index不連續的table類型變數

測試代碼：

DECLARE<br /> TYPE list_of_names_t IS TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;<br /> happyfamily list_of_names_t;<br /> l_row PLS_INTEGER;<br />BEGIN<br /> --build table data which index is not consecutive.<br /> happyfamily(2020202020) := 'Eli';<br /> happyfamily(-15070) := 'Steven';<br /> happyfamily(-90900) := 'Chris';<br /> happyfamily(-90899) := 'Chris90899';<br /> happyfamily(88) := 'Veva';</p><p> <<err1>><br /> BEGIN<br /> dbms_output.put_line('=======err1========');<br /> --i is starting from 1 and increase one by one.<br /> FOR i IN 1 .. happyfamily.COUNT<br /> LOOP<br /> dbms_output.put_line(i);<br /> dbms_output.put_line(happyfamily(i));<br /> END LOOP;<br /> EXCEPTION<br /> WHEN OTHERS THEN<br /> dbms_output.put_line('err1=>' || SQLERRM);<br /> END;</p><p> <<err2>><br /> BEGIN<br /> dbms_output.put_line('=======err2========');<br /> --i is starting from -90900 and increase one by one.<br /> FOR i IN happyfamily.FIRST .. happyfamily.LAST<br /> LOOP<br /> dbms_output.put_line(i);<br /> dbms_output.put_line(happyfamily(i));<br /> END LOOP;<br /> EXCEPTION<br /> WHEN OTHERS THEN<br /> dbms_output.put_line('err2=>' || SQLERRM);<br /> END;</p><p> <<pass>><br /> BEGIN<br /> dbms_output.put_line('=======pass========');<br /> --i is starting from -90900 and increase discrete<br /> l_row := happyfamily.FIRST;<br /> WHILE (l_row IS NOT NULL)<br /> LOOP<br /> dbms_output.put_line(l_row);<br /> dbms_output.put_line(happyfamily(l_row));<br /> l_row := happyfamily.NEXT(l_row);<br /> END LOOP;<br /> EXCEPTION<br /> WHEN OTHERS THEN<br /> dbms_output.put_line('err3=>' || SQLERRM);<br /> END;<br />END;<br />

 

輸出：

=======err1========<br />1<br />err1=>ORA-01403: no data found<br />=======err2========<br />-90900<br />Chris<br />-90899<br />Chris90899<br />-90898<br />err2=>ORA-01403: no data found<br />=======pass========<br />-90900<br />Chris<br />-90899<br />Chris90899<br />-15070<br />Steven<br />88<br />Veva<br />2020202020<br />Eli<br />