set serveroutput on;
declare
cursor cemp is select to_char(hiredate, 'yyyy') from emp;
phiredate varchar2(4);
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
begin
open cemp;
loop
fetch cemp into phiredate;
exit when cemp%notfound;
if phiredate='1980' then count80:=count80+1;
elsif phiredate='1981' then count81:=count81+1;
elsif phiredate='1982' then count82:=count82+1;
else count87 := count87 + 1;
end if;
end loop;
close cemp;
dbms_output.put_line('Total '||count80 + count81 + count82 + count87);
dbms_output.put_line('1980 '||count80);
dbms_output.put_line('1981 '||count81);
dbms_output.put_line('1982 '||count82);
dbms_output.put_line('1987 '||count87);
end;
/
错误报告 -
ORA-06502: PL/SQL: 数字或值错误 : 字符到数值的转换错误
ORA-06512: 在 line 20
06502. 00000 - "PL/SQL: numeric or value error%s"
*Cause: An arithmetic, numeric, string, conversion, or constraint error
occurred. For example, this error occurs if an attempt is made to
assign the value NULL to a variable declared NOT NULL, or if an
attempt is made to assign an integer larger than 99 to a variable
declared NUMBER(2).
*Action: Change the data, how it is manipulated, or how it is declared so
that values do not violate constraints.

DECLARE
CURSOR cemp IS
cursor cemp is select to_char(hiredate, 'yyyy') from emp;
phiredate VARCHAR2(4);
count80 NUMBER := 0;
count81 NUMBER := 0;
count82 NUMBER := 0;
count87 NUMBER := 0;
num NUMBER;
BEGIN
OPEN cemp;
LOOP
FETCH cemp
INTO phiredate;
EXIT WHEN cemp%NOTFOUND;
IF phiredate = '1980' THEN
count80 := count80 + 1;
ELSIF phiredate = '1981' THEN
count81 := count81 + 1;
ELSIF phiredate = '1982' THEN
count82 := count82 + 1;
ELSE
count87 := count87 + 1;
END IF;
END LOOP;
CLOSE cemp;
num := count80 + count81 + count82 + count87;
dbms_output.put_line('Total ' || num);
dbms_output.put_line('1980 ' || count80);
dbms_output.put_line('1981 ' || count81);
dbms_output.put_line('1982 ' || count82);
dbms_output.put_line('1987 ' || count87);
END;

DECLARE
CURSOR cemp IS
cursor cemp is select to_char(hiredate, 'yyyy') from emp;
phiredate VARCHAR2(4);
count80 NUMBER := 0;
count81 NUMBER := 0;
count82 NUMBER := 0;
count87 NUMBER := 0;
BEGIN
OPEN cemp;
LOOP
FETCH cemp
INTO phiredate;
EXIT WHEN cemp%NOTFOUND;
IF phiredate = '1980' THEN
count80 := count80 + 1;
ELSIF phiredate = '1981' THEN
count81 := count81 + 1;
ELSIF phiredate = '1982' THEN
count82 := count82 + 1;
ELSE
count87 := count87 + 1;
END IF;
END LOOP;
CLOSE cemp;
dbms_output.put_line('Total ' || (count80 + count81 + count82 + count87));
dbms_output.put_line('1980 ' || count80);
dbms_output.put_line('1981 ' || count81);
dbms_output.put_line('1982 ' || count82);
dbms_output.put_line('1987 ' || count87);
END;
要么加上括号，提高运算优先级，要么定义一个变量，都可以