insert into p_s_total(EEMPTOT) select count(1) from hr_emnemployee group by fstatetype;这个意思吗 一定要写循环过程的话 可以这么改 1 create or replace procedure testcursor is 2 statetype hr_emnemployee.fstatetype%type; //定义一个变量 3 sSqlStr varchar2(300); 5 cursor cs_statetype 6 is 7 select distinct fstatetype from hr_emnemployee ; 8 begin 9 OPEN cs_statetype; loop 10 FETCH cs_statetype INTO statetype; //取游标结果赋变量 exit when cs_statetype%notfound; 12 sSqlStr:='insert into p_s_total(EEMPTOT)'||' //处理统计字段中值的结果插入到另一张表中 13 ('|| 14 'select count(fstatetype) from hr_emnemployee where fstatetype='''|| statetype|| 15 ''')'; 16 execute immediate sSqlStr; 17 end loop;
。。我是看了你的过程里用了count，以为你要插入这个统计数值呢 那你这个过程完全多此一举 insert into p_s_total(EEMPTOT) select distinct fstatetype from hr_emnemployee 就完事了过程的话也一样 1 create or replace procedure testcursor is 2 statetype hr_emnemployee.fstatetype%type; //定义一个变量 3 sSqlStr varchar2(300); 5 cursor cs_statetype 6 is 7 select distinct fstatetype from hr_emnemployee ; 8 begin 9 OPEN cs_statetype; loop 10 FETCH cs_statetype INTO statetype; //取游标结果赋变量 exit when cs_statetype%notfound; 12 insert into p_s_total(EEMPTOT) values(statetype); 17 end loop; 18 CLOSE cs_statetype; 19 end testcursor; 20 / 或者更简单 1 create or replace procedure testcursor is 8 begin insert into p_s_total(EEMPTOT) select distinct fstatetype from hr_emnemployee ; 19 end testcursor; 20 /
是不是将hr_emnemployee的几个值分别统计出条数，插入到p_s_total对应的字段中 你看看是不是这样？（由于你没有说明字段的对应关系，我就按字符顺序来排） insert into p_s_total select count(decode(dk,1,1)), count(decode(dk,2,1)), count(decode(dk,3,1)), count(decode(dk,4,1)), count(decode(dk,5,1)), count(decode(dk,6,1)) from( select fstatetype,dense_rank()over(order by fstatetype)dk from hr_emnemployee)
比方说： select count(t.fstatetype) as A from hr_emnemployee t where t.fstatetype='estRegular' union select count(t.fstatetype) as B from hr_emnemployee t A ----------- 1 23 2 31转换成
A B ----------|--------- 1 23 31 --------------------
statetype没有用处
这个循环相当于
insert into p_s_total(EEMPTOT)
select count(fstatetype)over() from hr_emnemployee
---------------
estRegular
---------------
estRegular
---------------
estNewEmployee
---------------
estIllnessRest
---------------
等值 目前只写了个简单存储过程：
1 create or replace procedure testcursor is
2 statetype hr_emnemployee.fstatetype%type; //定义一个变量
3 sSqlStr varchar2(300);
5 cursor cs_statetype
6 is
7 select fstatetype from hr_emnemployee ;
8 begin
9 OPEN cs_statetype;
10 FETCH cs_statetype INTO statetype; //取游标结果赋变量
12 sSqlStr:='insert into p_s_total(EEMPTOT)'||' //处理统计字段中值的结果插入到另一张表中
13 ('||
14 'select count(fstatetype) from hr_emnemployee'||
15 ')';
16 execute immediate sSqlStr;
17 end if;
18 CLOSE cs_statetype;
19 end testcursor;
20 /
//需求是需要统计指定字段中不同的内容字段
select count(1) from hr_emnemployee group by fstatetype;这个意思吗
一定要写循环过程的话
可以这么改
1 create or replace procedure testcursor is
2 statetype hr_emnemployee.fstatetype%type; //定义一个变量
3 sSqlStr varchar2(300);
5 cursor cs_statetype
6 is
7 select distinct fstatetype from hr_emnemployee ;
8 begin
9 OPEN cs_statetype;
loop
10 FETCH cs_statetype INTO statetype; //取游标结果赋变量
exit when cs_statetype%notfound;
12 sSqlStr:='insert into p_s_total(EEMPTOT)'||' //处理统计字段中值的结果插入到另一张表中
13 ('||
14 'select count(fstatetype) from hr_emnemployee where fstatetype='''|| statetype||
15 ''')';
16 execute immediate sSqlStr;
17 end loop;
18 CLOSE cs_statetype;
19 end testcursor;
20 /
分别插入 p_s_total 表中 以下字段
EEMPTOT
EONWORK
ENOWORK
ELAIDOFF
EEARRET
EOTHERS1
那你这个过程完全多此一举
insert into p_s_total(EEMPTOT)
select distinct fstatetype from hr_emnemployee
就完事了过程的话也一样
1 create or replace procedure testcursor is
2 statetype hr_emnemployee.fstatetype%type; //定义一个变量
3 sSqlStr varchar2(300);
5 cursor cs_statetype
6 is
7 select distinct fstatetype from hr_emnemployee ;
8 begin
9 OPEN cs_statetype;
loop
10 FETCH cs_statetype INTO statetype; //取游标结果赋变量
exit when cs_statetype%notfound;
12 insert into p_s_total(EEMPTOT) values(statetype);
17 end loop;
18 CLOSE cs_statetype;
19 end testcursor;
20 / 或者更简单
1 create or replace procedure testcursor is 8 begin
insert into p_s_total(EEMPTOT)
select distinct fstatetype from hr_emnemployee ;
19 end testcursor;
20 /
---------------
estRegular
---------------
estRegular
---------------
estNewEmployee
---------------
estIllnessRest
---------------
字段里有这些字段内容，要分类去统计内容条数，然后插入指定的几个字段里EEMPTOT、EONWORK、ENOWORK、ELAIDOFF、EEARRET、EOTHERS1、
以下为字段里头值的内容
--------------
estRegular
---------------
estRegular
---------------
estNewEmployee
---------------
estIllnessRest
---------------
现在，我要将这些值分别去统计，并将统计结果插入到
另一张统计表中 指定一些字段中 EEMPTOT、EONWORK、ENOWORK、ELAIDOFF、EEARRET、EOTHERS1
你看看是不是这样？（由于你没有说明字段的对应关系，我就按字符顺序来排）
insert into p_s_total
select count(decode(dk,1,1)),
count(decode(dk,2,1)),
count(decode(dk,3,1)),
count(decode(dk,4,1)),
count(decode(dk,5,1)),
count(decode(dk,6,1))
from(
select fstatetype,dense_rank()over(order by fstatetype)dk
from hr_emnemployee)
select count(t.fstatetype) as A from hr_emnemployee t where t.fstatetype='estRegular'
union
select count(t.fstatetype) as B from hr_emnemployee t
A
-----------
1 23
2 31转换成
A B
----------|---------
1 23 31
--------------------
将原表中的各个不同的FSTATETYPE值的统计放在一条记录里。如果有5组不同的FSTATETYPE，则第六列为空
你看下结果
count(decode(dk,2,1)),
count(decode(dk,3,1)),
count(decode(dk,4,1)),
count(decode(dk,5,1)),
count(decode(dk,6,1))
from(
select fstatetype,dense_rank()over(order by fstatetype)dk
from hr_emnemployee)结果count(decode(dk,2,1)|count(decode(dk,2,1)|count(decode(dk,2,1) 1 2 24 .....直接插表无法操作，没有对应字段
你在
insert into ..后面加上字段名呗
比如
insert into p_s_total(EEMPTOT,EONWORK,ENOWORK,ELAIDOFF,EEARRET,EOTHERS1 )
select ........
动态的问题你可以参考下
http://topic.csdn.net/u/20091019/11/67cd55a3-3f42-4db7-a3f8-91dd52a913cd.html
max可以改成count
不需要group by 可以在col1参数上填1，col3也填1
或者参考下
http://topic.csdn.net/u/20091018/15/7c6729b6-79d1-492e-862d-af9c4921f172.html
begin
proc('tab','subject','class','counter');
end;ORA-01031: 权限不足
ORA-06512: 在"TEST.PROC", line 20
ORA-06512: 在line 2
使用dba用户（如果本身就是DBA用户，则可以自己对自己授权）
grant create any view to xxx--你的用户名