建表语句:
create table BAOZ_CWSBXX (
XH NUMBER(16,0) not null,
XMXH NUMBER(16,0),
SBBH VARCHAR2(32),
SBLX VARCHAR2(50),
SBXH VARCHAR2(50),
SBZT VARCHAR2(32),
TZLJ VARCHAR2(256),
LK VARCHAR2(50),
AZRQ DATE,
ZBQ DATE,
JDHGRQ DATE,
XJSJKSSJ DATE,
XJSJJSSJ DATE,
JXAZFS VARCHAR2(10),
BZ VARCHAR2(1000),
constraint PK_BAOZ_CWSBXX primary key (XH)
);create table BAOZ_WHLRTJ (
XH NUMBER(16,0) not null,
SBLX VARCHAR2(50),
SBXH VARCHAR2(50),
RWHL NUMBER(3,3),
RQ DATE,
SBZCSYTS NUMBER(10),
SBSYZZTS NUMBER(10),
AZDD VARCHAR2(10),
constraint PK_BAOZ_WHLRTJ primary key (XH)
);create or replace procedure insert_baoz_whlrtj isCURSOR cur1 IS
select sy.sblx,sy.sbxh,zc.sl,sy.zsl,zc.sl/sy.zsl as whl from
(select count(sblx) as zsl,sblx,sbxh from baoz_cwsbxx where (sbzt='正常' or sbzt='不正常') group by sblx,sbxh) sy,
(select count(sblx) as sl,sblx,sbxh from baoz_cwsbxx where sbzt='正常' group by sblx,sbxh) zc
where sy.sblx = zc.sblx and sy.sbxh = zc.sbxh;CURSOR cur2 IS
select sblx,sbxh,sl from
(select count(sblx) as sl,sblx,sbxh from baoz_cwsbxx where (sbzt='正常' or sbzt='不正常') group by sblx,sbxh)
where
sblx not in (select sblx from baoz_cwsbxx where sbzt='正常' group by sblx,sbxh)
or
sbxh not in (select sbxh from baoz_cwsbxx where sbzt='正常' group by sblx,sbxh);
begin FOR cur_result in cur1 loop
begin
SELECT SEQ_ID.NEXTVAL into xh FROM DUAL;
insert into BAOZ_WHLRTJ(XH,SBLX,SBXH,SBZCSYTS,SBSYZZTS,RWHL,AZDD,RQ) values (
xh,
cur_result.sblx,cur_result.sbxh,cur_result.sl,cur_result.zsl,cur_result.whl,
'场外',SYSDATE-1
);
end;
end loop;
FOR cur_result in cur2 loop
begin
SELECT SEQ_ID.NEXTVAL into xh FROM DUAL;
insert into BAOZ_WHLRTJ(XH,SBLX,SBXH,SBSYZZTS,SBZCSYTS,RWHL,AZDD,RQ) values (
xh,
cur_result.sblx,cur_result.sbxh.cur_result.sl,
0,0,'场外',SYSDATE-1
);
end;
end loop;
end;CURSOR 中的Select 都是经过测试没有问题的。帮帮忙看看
create table BAOZ_CWSBXX (
XH NUMBER(16,0) not null,
XMXH NUMBER(16,0),
SBBH VARCHAR2(32),
SBLX VARCHAR2(50),
SBXH VARCHAR2(50),
SBZT VARCHAR2(32),
TZLJ VARCHAR2(256),
LK VARCHAR2(50),
AZRQ DATE,
ZBQ DATE,
JDHGRQ DATE,
XJSJKSSJ DATE,
XJSJJSSJ DATE,
JXAZFS VARCHAR2(10),
BZ VARCHAR2(1000),
constraint PK_BAOZ_CWSBXX primary key (XH)
);create table BAOZ_WHLRTJ (
XH NUMBER(16,0) not null,
SBLX VARCHAR2(50),
SBXH VARCHAR2(50),
RWHL NUMBER(3,3),
RQ DATE,
SBZCSYTS NUMBER(10),
SBSYZZTS NUMBER(10),
AZDD VARCHAR2(10),
constraint PK_BAOZ_WHLRTJ primary key (XH)
);create or replace procedure insert_baoz_whlrtj isCURSOR cur1 IS
select sy.sblx,sy.sbxh,zc.sl,sy.zsl,zc.sl/sy.zsl as whl from
(select count(sblx) as zsl,sblx,sbxh from baoz_cwsbxx where (sbzt='正常' or sbzt='不正常') group by sblx,sbxh) sy,
(select count(sblx) as sl,sblx,sbxh from baoz_cwsbxx where sbzt='正常' group by sblx,sbxh) zc
where sy.sblx = zc.sblx and sy.sbxh = zc.sbxh;CURSOR cur2 IS
select sblx,sbxh,sl from
(select count(sblx) as sl,sblx,sbxh from baoz_cwsbxx where (sbzt='正常' or sbzt='不正常') group by sblx,sbxh)
where
sblx not in (select sblx from baoz_cwsbxx where sbzt='正常' group by sblx,sbxh)
or
sbxh not in (select sbxh from baoz_cwsbxx where sbzt='正常' group by sblx,sbxh);
begin FOR cur_result in cur1 loop
begin
SELECT SEQ_ID.NEXTVAL into xh FROM DUAL;
insert into BAOZ_WHLRTJ(XH,SBLX,SBXH,SBZCSYTS,SBSYZZTS,RWHL,AZDD,RQ) values (
xh,
cur_result.sblx,cur_result.sbxh,cur_result.sl,cur_result.zsl,cur_result.whl,
'场外',SYSDATE-1
);
end;
end loop;
FOR cur_result in cur2 loop
begin
SELECT SEQ_ID.NEXTVAL into xh FROM DUAL;
insert into BAOZ_WHLRTJ(XH,SBLX,SBXH,SBSYZZTS,SBZCSYTS,RWHL,AZDD,RQ) values (
xh,
cur_result.sblx,cur_result.sbxh.cur_result.sl,
0,0,'场外',SYSDATE-1
);
end;
end loop;
end;CURSOR 中的Select 都是经过测试没有问题的。帮帮忙看看
select sy.sblx,sy.sbxh,zc.sl,sy.zsl,zc.sl/sy.zsl as whl from
(select count(sblx) as zsl,sblx,sbxh from baoz_cwsbxx where (sbzt='正常' or sbzt='不正常') group by sblx,sbxh) sy,
(select count(sblx) as sl,sblx,sbxh from baoz_cwsbxx where sbzt='正常' group by sblx,sbxh) zc
where sy.sblx = zc.sblx and sy.sbxh = zc.sbxh;CURSOR cur2 IS
select sblx,sbxh,sl from
(select count(sblx) as sl,sblx,sbxh from baoz_cwsbxx where (sbzt='正常' or sbzt='不正常') group by sblx,sbxh)
where
sblx not in (select sblx from baoz_cwsbxx where sbzt='正常' group by sblx,sbxh)
or
sbxh not in (select sbxh from baoz_cwsbxx where sbzt='正常' group by sblx,sbxh);xh NUMBER(20);
begin FOR cur_result in cur1 loop
begin
SELECT SEQ_ID.NEXTVAL into xh FROM DUAL;
insert into BAOZ_WHLRTJ(XH,SBLX,SBXH,SBZCSYTS,SBSYZZTS,RWHL,AZDD,RQ) values (
xh,
cur_result.sblx,cur_result.sbxh,cur_result.sl,cur_result.zsl,cur_result.whl,
'场外',SYSDATE-1
);
end;
end loop; FOR cur_result in cur2 loop
begin
SELECT SEQ_ID.NEXTVAL into xh FROM DUAL;
insert into BAOZ_WHLRTJ(XH,SBLX,SBXH,SBSYZZTS,SBZCSYTS,RWHL,AZDD,RQ) values (
xh,
cur_result.sblx,cur_result.sbxh,cur_result.sl,
0,0,'场外',SYSDATE-1
);
end;
end loop;
end;
cur_result.sblx,cur_result.sbxh.cur_result.sl,
这段写错一个符号。
select sy.sblx,sy.sbxh,zc.sl,sy.zsl,zc.sl/sy.zsl as whl from 我在第一个cur里面有个计算,涉及到精度问题
insert into BAOZ_WHLRTJ(XH,SBLX,SBXH,SBZCSYTS,SBSYZZTS,RWHL,AZDD,RQ) values (
xh,
cur_result.sblx,cur_result.sbxh,cur_result.sl,cur_result.zsl,trunc(cur_result.whl,3),
'场外',SYSDATE-1
);
插入的时候使用了trunc但是还是报
ORA-01438: 值大于为此列指定的允许精度
ORA-06512: 在 "BAOKANG.INSERT_BAOZ_WHLRTJ", line 29
ORA-06512: 在 line 2
143546, 'DZJC','0',2,3,trunc(0.666666666667,3),
'场外',SYSDATE-1
);
直接这样可以插入,,,,,select sy.sblx,sy.sbxh,zc.sl,sy.zsl,trunc(zc.sl/sy.zsl,3) as whl from
尝试在这里加入精度控制,一样报:
ORA-01438: 值大于为此列指定的允许精度
ORA-06512: 在 "BAOKANG.INSERT_BAOZ_WHLRTJ", line 29
ORA-06512: 在 line 2