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.