create or replace procedure proc_count_age
as
count1 tb_stu_info%type;
begin
declare cursor c_count is select (case when a.age>=30 and a.age<=39 then '30-39'
when a.age>=40 and a.age<=50 then '40-50'
when a.age<30 then '<30'
else '>50' end) age,count(*)人数
from (select to_number(to_char(sysdate,'yyyy'))-to_number(substr(birth,1,4)) age
from tb_stu_info,dual)a group by
(case when a.age>=30 and a.age<=39 then '30-39'
when a.age>=40 and a.age<=50 then '40-50'
when a.age<30 then '<30'
else '>50' end);
begin
DBMS_OUTPUT.PUT_LINE('年龄段 人数');
for count1 in c_count loop
DBMS_OUTPUT.PUT_LINE(count1.age||' '||count1.人数);
end loop;
end;
end;
/
过程是这样的，哪不对呀
------解决方案--------------------
show errors
- SQL code
SQL> create or replace procedure proc_count_age 2 as 3 count1 tb_stu_info%type; 4 begin 5 declare cursor c_count is select (case when a.age>=30 and a.age<=39 then '30-39' 6 when a.age>=40 and a.age<=50 then '40-50' 7 when a.age<30 then '<30' 8 else '>50' end) age,count(*)人数 9 from (select to_number(to_char(sysdate,'yyyy'))-to_number(substr(birth,1,4)) age 10 from tb_stu_info,dual)a group by 11 (case when a.age>=30 and a.age<=39 then '30-39' 12 when a.age>=40 and a.age<=50 then '40-50' 13 when a.age<30 then '<30' 14 else '>50' end); 15 begin 16 DBMS_OUTPUT.PUT_LINE('年龄段 人数'); 17 for count1 in c_count loop 18 DBMS_OUTPUT.PUT_LINE(count1.age||' '||count1.人数); 19 end loop; 20 end; 21 end; 22 / Warning: Procedure created with compilation errors SQL> show errorsErrors for PROCEDURE SCOTT.PROC_COUNT_AGE: LINE/COL ERROR-------- ------------------------------------------------------3/8 PLS-00201: identifier 'TB_STU_INFO' must be declared3/8 PL/SQL: Item ignored10/6 PL/SQL: ORA-00942: table or view does not exist5/27 PL/SQL: SQL Statement ignored18/22 PLS-00364: loop index variable 'COUNT1' use is invalid18/1 PL/SQL: Statement ignored