declare
cursor cur is select ename from emp where empno=&请输入员工编号;
v_name emp.ename%type;
begin
open cur;
fetch cur into v_name;
dbms_output.put_line(v_name);
close cur;
exception
when no_data_found then
dbms_output.put_line('对不起,没有找到数据');
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;当输入empno找不到数据时为什么不能抛no_data_found异常
cursor cur is select ename from emp where empno=&请输入员工编号;
v_name emp.ename%type;
begin
open cur;
fetch cur into v_name;
dbms_output.put_line(v_name);
close cur;
exception
when no_data_found then
dbms_output.put_line('对不起,没有找到数据');
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;当输入empno找不到数据时为什么不能抛no_data_found异常
--使用%NOTFOUND declare
cursor cur is select ename from emp where empno=&请输入员工编号;
v_name emp.ename%type;
begin
open cur;
fetch cur into v_name;
dbms_output.put_line(v_name);
close cur;
exception
when %NOTFOUND then
dbms_output.put_line('对不起,没有找到数据');
when others then
dbms_output.put_line(sqlcode||sqlerrm);
end;
-- TEST中当执行SELECT INTO语句赋值时因为当"ENTER_EMP = 6",没有相应的ENAME返回,所以触发了NO_DATA_FOUND异常。SQL> SET SERVEROUTPUT ON
SQL> SELECT * FROM EMP; EMPNO ENAME DEPT_NUM
---------- ---------------------------------------- ----------
5 zzchr&yy 50
1 chol 10
2 uday 20
3 vaibhav 20
4 ghosh 30-- ENTER_EMP = 3
SQL> DECLARE
2 CURSOR CUR IS
3 SELECT ENAME FROM EMP WHERE EMPNO = &ENTER_EMP;
4 V_NAME EMP.ENAME%TYPE;
5 BEGIN
6 OPEN CUR;
7 FETCH CUR INTO V_NAME;
8 DBMS_OUTPUT.PUT_LINE(V_NAME);
9 CLOSE CUR;
10 EXCEPTION
11 WHEN NO_DATA_FOUND THEN
12 DBMS_OUTPUT.PUT_LINE('SORRY, NO DATA FOUND');
13 WHEN OTHERS THEN
14 DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
15 END;
16 /vaibhavPL/SQL procedure successfully completed-- ENTER_EMP = 6
SQL> DECLARE
2 CURSOR CUR IS
3 SELECT ENAME FROM EMP WHERE EMPNO = &ENTER_EMP;
4 V_NAME EMP.ENAME%TYPE;
5 BEGIN
6 OPEN CUR;
7 FETCH CUR INTO V_NAME;
8 DBMS_OUTPUT.PUT_LINE(V_NAME);
9 CLOSE CUR;
10 EXCEPTION
11 WHEN NO_DATA_FOUND THEN
12 DBMS_OUTPUT.PUT_LINE('SORRY, NO DATA FOUND');
13 WHEN OTHERS THEN
14 DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
15 END;
16 /
PL/SQL procedure successfully completed
--------------- TEST ---------------------------------- ENTER_EMP = 3
SQL> DECLARE
2 V_NAME EMP.ENAME%TYPE;
3 V_EMPNO EMP.EMPNO%TYPE;
4 BEGIN
5 V_EMPNO := &ENTER_EMP;
6 SELECT ENAME
7 INTO V_NAME
8 FROM EMP
9 WHERE EMPNO = V_EMPNO;
10 DBMS_OUTPUT.PUT_LINE('SELECTED EMP NAME IS:'||V_NAME);
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN
13 DBMS_OUTPUT.PUT_LINE('SORRY, NO DATA FOUND FOR EMP_NO: '||V_EMPNO);
14 WHEN OTHERS THEN
15 DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
16 END;
17 /SELECTED EMP NAME IS:vaibhavPL/SQL procedure successfully completed-- ENTER_EMP = 6
SQL> DECLARE
2 V_NAME EMP.ENAME%TYPE;
3 V_EMPNO EMP.EMPNO%TYPE;
4 BEGIN
5 V_EMPNO := &ENTER_EMP;
6 SELECT ENAME
7 INTO V_NAME
8 FROM EMP
9 WHERE EMPNO = V_EMPNO;
10 DBMS_OUTPUT.PUT_LINE('SELECTED EMP NAME IS:'||V_NAME);
11 EXCEPTION
12 WHEN NO_DATA_FOUND THEN
13 DBMS_OUTPUT.PUT_LINE('SORRY, NO DATA FOUND FOR EMP_NO: '||V_EMPNO);
14 WHEN OTHERS THEN
15 DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
16 END;
17 /SORRY, NO DATA FOUND FOR EMP_NO: 6PL/SQL procedure successfully completed
/--另改一下:declare
cursor cur is select ename from emp where empno=&请输入员工编号;
v_name emp.ename%type;
begin
open cur;
fetch cur into v_name;
dbms_output.put_line(v_name);
exception
when %NOTFOUND then
dbms_output.put_line('对不起,没有找到数据');
when others then
dbms_output.put_line(sqlcode||sqlerrm);
close cur;
end;
-- ENTER_EMP = 3
SQL> DECLARE
2 CURSOR CUR IS
3 SELECT ENAME FROM EMP WHERE EMPNO = &ENTER_EMP;
4 V_NAME EMP.ENAME%TYPE;
5 BEGIN
6 OPEN CUR;
7 FETCH CUR INTO V_NAME;
8 IF CUR%ROWCOUNT = 0 THEN
9 DBMS_OUTPUT.PUT_LINE('SORRY, NO DATA FOUND');
10 ELSE
11 DBMS_OUTPUT.PUT_LINE('SELECTED EMP NAME IS:'||V_NAME);
12 END IF;
13 CLOSE CUR;
14
15 EXCEPTION
16 WHEN OTHERS THEN
17 DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
18 END;
19 /SELECTED EMP NAME IS:vaibhavPL/SQL procedure successfully completed-- ENTER_EMP = 6
SQL> DECLARE
2 CURSOR CUR IS
3 SELECT ENAME FROM EMP WHERE EMPNO = &ENTER_EMP;
4 V_NAME EMP.ENAME%TYPE;
5 BEGIN
6 OPEN CUR;
7 FETCH CUR INTO V_NAME;
8 IF CUR%ROWCOUNT = 0 THEN
9 DBMS_OUTPUT.PUT_LINE('SORRY, NO DATA FOUND');
10 ELSE
11 DBMS_OUTPUT.PUT_LINE('SELECTED EMP NAME IS:'||V_NAME);
12 END IF;
13 CLOSE CUR;
14
15 EXCEPTION
16 WHEN OTHERS THEN
17 DBMS_OUTPUT.PUT_LINE(SQLCODE || SQLERRM);
18 END;
19 /SORRY, NO DATA FOUNDPL/SQL procedure successfully completed