块如下所示:
declare
v_name varchar2(16);
BEGIN
v_name := 'pp';
select name into v_name from student where name = 'kk';
if v_name is null then
dbms_output.put_line(v_name);
else
dbms_output.put_line('can not find!');
end if;
END;实际上数据库里没有 name = 'kk'的情况.所以在SQLPLUS里报错为: ORA-01403 未找到数据
但我是流程是想如果没找到这个"KK",就输出一个值.找到了,就输出另一个值.
请问有什么办法做到此点?或者当该元组的这个字段没有值的时候,我怎么去判断?
declare
v_name varchar2(16);
BEGIN
v_name := 'pp';
select name into v_name from student where name = 'kk';
if v_name is null then
dbms_output.put_line(v_name);
else
dbms_output.put_line('can not find!');
end if;
END;实际上数据库里没有 name = 'kk'的情况.所以在SQLPLUS里报错为: ORA-01403 未找到数据
但我是流程是想如果没找到这个"KK",就输出一个值.找到了,就输出另一个值.
请问有什么办法做到此点?或者当该元组的这个字段没有值的时候,我怎么去判断?
另外请详解一下"事先COUNT"是何意?
bonus REAL;
comm_missing EXCEPTION;
BEGIN -- executable part starts here
SELECT comm * 0.15 INTO bonus FROM emp WHERE empno = emp_id;
IF bonus IS NULL THEN
RAISE comm_missing;
ELSE
UPDATE payroll SET pay = pay + bonus WHERE empno = emp_id;
END IF;
EXCEPTION -- exception-handling part starts here
WHEN comm_missing THEN
...
END award_bonus;这是ORACLE给出的例子, 不像你说的那样,会报ORA错误也
v_name varchar2(16);
v_count number(3);
BEGIN
v_name := 'pp';
select count(1) into v_count from student where name = 'kk';
if v_count >= 1 then
select name into v_name from student where name = 'kk';
dbms_output.put_line(v_name);
else
dbms_output.put_line('can not find!');
end if ;
end; or,declare
v_name varchar2(16);
BEGIN
v_name := 'pp';
select name into v_name from student where name = 'kk';
dbms_output.put_line(v_name);
exception
when no_data_found then
dbms_output.put_line('can not find!');
end;