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;
/
过程是这样的,哪不对呀
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;
/
过程是这样的,哪不对呀
解决方案 »
- 如何求第二大的记录
- 关于专有连接UGA的简单问题
- 在线急等,linux下安装了oracle客户端后,无法使用sqlplus命令
- 两个比较简单的oracle问题
- 关于PreparedStatement的问题
- 建表的时候,如何指定表所在的表空间?
- 散分啦
- 请教SQL----时间可以固定一个时间段
- 一個小問題:在查詢中如何判斷一字段前13位中是否包含某一字符?
- Oracle的primary key 约束是通过unique index实现的?
- solaris下使用dbca创建数据库报错:ora-12560
- ora-28547 错误 用oracle developer 连接 一直报错 试过网上的很多方法 都不行、、高手来
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 errors
Errors for PROCEDURE SCOTT.PROC_COUNT_AGE:
LINE/COL ERROR
-------- ------------------------------------------------------
3/8 PLS-00201: identifier 'TB_STU_INFO' must be declared
3/8 PL/SQL: Item ignored
10/6 PL/SQL: ORA-00942: table or view does not exist
5/27 PL/SQL: SQL Statement ignored
18/22 PLS-00364: loop index variable 'COUNT1' use is invalid
18/1 PL/SQL: Statement ignored
这里count1应该表示一行记录吧,你上面写的错了。
--a row
count1 tb_stu_info%rowtype;
--a column
count1 tb_stu_info.column_name%type;