我是PL/SQL的新用户，最近在Stack Overflow中创建了一个帐户。我试图使用一个循环来检索数据并将其存储在按表索引中，然后使用另一个循环显示按表索引的数据，但它总是给我一个错误，即找不到数据。这是我的代码：
SET SERVEROUTPUT ON
DECLARE
TYPE dept_table_type IS TABLE OF departments.department_name%TYPE INDEX BY PLS_INTEGER;
my_dept_table dept_table_type;
loop_count NUMBER := 10;
deptno NUMBER := 10;
BEGIN
dbms_output.put_line('DEPARTMENT_ID DEPARTMENT_NAME');
LOOP
SELECT department_name INTO my_dept_table(loop_count)
FROM departments
WHERE department_id = deptno;
deptno := deptno + 10;
loop_count := loop_count + 10;
EXIT WHEN loop_count > 100;
END LOOP;
FOR I in my_dept_table.FIRST..my_dept_table.LAST
LOOP
DBMS_OUTPUT.PUT_LINE(I||' '||my_dept_table(I));
END LOOP;
END;
/
Result: Error report -
ORA-01403: no data found
ORA-06512: at line 18
01403. 00000 - "no data found"
*Cause: No data was found from the objects.
*Action: There was no data from the objects which may be due to end of fetch.
谁能查一下密码吗。我是PLSQL的新用户，谢谢。
有两个可疑的地方会导致NO_DATA_FOUND异常。
第一个是select语句，如果您试图获取属于不存在的部门编号的部门名称，该语句将失败。
基于Scott的DEPT表(因为我没有您的表(：这是表的内容：
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------------- --------------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
你的代码，重写：
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 TYPE dept_table_type IS TABLE OF dept.dname%TYPE
3 INDEX BY PLS_INTEGER;
4
5 my_dept_table dept_table_type;
6 l_deptno NUMBER := 10;
7 BEGIN
8 LOOP
9 DBMS_OUTPUT.put_line (l_deptno);
10
11 SELECT dname --> this fails
12 INTO my_dept_table (l_deptno)
13 FROM dept
14 WHERE deptno = l_deptno;
15
16 l_deptno := l_deptno + 10;
17 EXIT WHEN l_deptno > 100;
18 END LOOP;
19 END;
20 /
10
20
30
40
50 --> because department 50 doesn't exist
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 11
那么，该怎么办呢？处理异常！
SQL> DECLARE
2 TYPE dept_table_type IS TABLE OF dept.dname%TYPE
3 INDEX BY PLS_INTEGER;
4
5 my_dept_table dept_table_type;
6 l_deptno NUMBER := 10;
7 BEGIN
8 LOOP
9 DBMS_OUTPUT.put_line (l_deptno);
10
11 BEGIN
12 SELECT dname
13 INTO my_dept_table (l_deptno)
14 FROM dept
15 WHERE deptno = l_deptno;
16 EXCEPTION
17 WHEN NO_DATA_FOUND
18 THEN
19 DBMS_OUTPUT.put_line (l_deptno || ' department does not exist');
20 END;
21
22 l_deptno := l_deptno + 10;
23 EXIT WHEN l_deptno > 100;
24 END LOOP;
25 END;
26 /
10
20
30
40
50
50 department does not exist
60
60 department does not exist
70
70 department does not exist
80
80 department does not exist
90
90 department does not exist
100
100 department does not exist
PL/SQL procedure successfully completed.
SQL>
另一个地方(这就是您的代码所遭受的(是当您试图显示不存在的数组项时。
SQL> DECLARE
2 TYPE dept_table_type IS TABLE OF dept.dname%TYPE
3 INDEX BY PLS_INTEGER;
4
5 my_dept_table dept_table_type;
6 l_deptno NUMBER := 10;
7 BEGIN
8 LOOP
9 BEGIN
10 SELECT dname
11 INTO my_dept_table (l_deptno)
12 FROM dept
13 WHERE deptno = l_deptno;
14 EXCEPTION
15 WHEN NO_DATA_FOUND
16 THEN
17 NULL;
18 END;
19
20 l_deptno := l_deptno + 10;
21 EXIT WHEN l_deptno > 100;
22 END LOOP;
23
24 DBMS_OUTPUT.put_line ('Number of items: ' || my_dept_table.COUNT);
25
26 FOR I IN my_dept_table.FIRST .. my_dept_table.LAST
27 LOOP
28 DBMS_OUTPUT.put_line ('i = ' || i);
29 DBMS_OUTPUT.put_line (my_dept_table (i));
30 END LOOP;
31 END;
32 /
Number of items: 4 --> department numbers 10, 20, 30, 40
i = 10 --> this is OK - department 10 = ACCOUNTING
ACCOUNTING
i = 11 --> but, there's no department 11 and you got an error
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 29
SQL>
建议：完全重写代码以提高效率：
SQL> DECLARE
2 TYPE dept_table_type IS TABLE OF dept.dname%TYPE
3 INDEX BY PLS_INTEGER;
4
5 my_dept_table dept_table_type;
6 BEGIN
7 SELECT dname
8 BULK COLLECT INTO my_dept_table
9 FROM dept;
10
11 FOR i IN my_dept_table.FIRST .. my_dept_table.LAST
12 LOOP
13 DBMS_OUTPUT.put_line (my_dept_table (i));
14 END LOOP;
15 END;
16 /
ACCOUNTING
RESEARCH
SALES
OPERATIONS
PL/SQL procedure successfully completed.
SQL>