Oracle PL/SQL之处理index不连续的table类型变量

PL/SQL表遍历技巧

最新推荐文章于 2023-02-28 13:02:03 发布

最新推荐文章于 2023-02-28 13:02:03 发布 · 81 阅读

文章标签：

#数据库

本文通过一个具体的示例展示了如何在PL/SQL中使用不同方法遍历一个索引表，并讨论了当表索引不连续时各种遍历方法的表现。通过对比三种不同的循环方式，说明了正确选择循环策略的重要性。

测试代码：

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

输出：

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