Oracle PL/SQL之处理index不连续的table类型变量
Oracle PL/SQL之处理index不连续的table类型变量
测试代码:
- 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
评论暂时关闭