create table HY_DP_C (
STCD char(8) not null,
DT DATE not null,
P NUMERIC(5, 1),
PRCD char(4),
constraint PK_HY_DP_C primary key (STCD, DT)
)create table ST_PPTN_R (
STCD CHAR(8) not null,
TM DATE not null,
DRP NUMERIC(5,1),
INTV DOUBLE PRECISION,
PDR DOUBLE PRECISION,
DYP NUMERIC(5,1),
constraint PK_ST_PPTN_R primary key (STCD, TM)
)create or replace procedure proc_ST_PPTN_R_TM(STCD1 in varchar2,dt1 IN date,p in NUMBER) as
p1 NUMBER(5,1);--日雨量值
pcount NUMBER(5,1);--计算的数;
temp varchar2(20);
begin
p1:=0;
SELECT count(*) into pcount FROM HY_DP_C where to_char(dt,'yyyy-mm-dd')=to_char(dt1,'yyyy-mm-dd') and STCD=STCD1;
if pcount<=0 then
--插入数据到HY_DP_C表中
temp:=to_char(dt1,'yyyy-mm-dd');
insert into HY_DP_C(stcd,DT,P) values(STCD1,to_date(temp,'yyyy-mm-dd'),p);
DBMS_OUTPUT.PUT_LINE('插入成功');
else
--游标
DECLARE
CURSOR mycur IS
SELECT * FROM ST_PPTN_R where to_char(tm,'yyyy-mm-dd')=to_char(dt1,'yyyy-mm-dd') and STCD=STCD1;--运行到这里的时候有错; myrecord ST_PPTN_R%ROWTYPE;
BEGIN
OPEN mycur;
FETCH mycur INTO myrecord;
WHILE mycur%FOUND LOOP
--drp:=myrecord.drp; 修改HY_DP_C表的日雨量值
-- p:=p+myrecord.drp;
p1:=(p1+myrecord.dyp);
-- DBMS_OUTPUT.PUT_LINE(myrecord.dyp);
--DBMS_OUTPUT.PUT_LINE('修改成功');
FETCH mycur INTO myrecord;
END LOOP;
CLOSE mycur;
END;
--把值进行修改
update HY_DP_C set p=p1 where dt=dt1 and STCD=STCD1;
end if;
end;调用proc_ST_PPTN_R_TM('5',sysdate,'3.0') ,运行到SELECT * FROM ST_PPTN_R where to_char(tm,'yyyy-mm-dd')=to_char(dt1,'yyyy-mm-dd') and STCD=STCD1;就报错
STCD char(8) not null,
DT DATE not null,
P NUMERIC(5, 1),
PRCD char(4),
constraint PK_HY_DP_C primary key (STCD, DT)
)create table ST_PPTN_R (
STCD CHAR(8) not null,
TM DATE not null,
DRP NUMERIC(5,1),
INTV DOUBLE PRECISION,
PDR DOUBLE PRECISION,
DYP NUMERIC(5,1),
constraint PK_ST_PPTN_R primary key (STCD, TM)
)create or replace procedure proc_ST_PPTN_R_TM(STCD1 in varchar2,dt1 IN date,p in NUMBER) as
p1 NUMBER(5,1);--日雨量值
pcount NUMBER(5,1);--计算的数;
temp varchar2(20);
begin
p1:=0;
SELECT count(*) into pcount FROM HY_DP_C where to_char(dt,'yyyy-mm-dd')=to_char(dt1,'yyyy-mm-dd') and STCD=STCD1;
if pcount<=0 then
--插入数据到HY_DP_C表中
temp:=to_char(dt1,'yyyy-mm-dd');
insert into HY_DP_C(stcd,DT,P) values(STCD1,to_date(temp,'yyyy-mm-dd'),p);
DBMS_OUTPUT.PUT_LINE('插入成功');
else
--游标
DECLARE
CURSOR mycur IS
SELECT * FROM ST_PPTN_R where to_char(tm,'yyyy-mm-dd')=to_char(dt1,'yyyy-mm-dd') and STCD=STCD1;--运行到这里的时候有错; myrecord ST_PPTN_R%ROWTYPE;
BEGIN
OPEN mycur;
FETCH mycur INTO myrecord;
WHILE mycur%FOUND LOOP
--drp:=myrecord.drp; 修改HY_DP_C表的日雨量值
-- p:=p+myrecord.drp;
p1:=(p1+myrecord.dyp);
-- DBMS_OUTPUT.PUT_LINE(myrecord.dyp);
--DBMS_OUTPUT.PUT_LINE('修改成功');
FETCH mycur INTO myrecord;
END LOOP;
CLOSE mycur;
END;
--把值进行修改
update HY_DP_C set p=p1 where dt=dt1 and STCD=STCD1;
end if;
end;调用proc_ST_PPTN_R_TM('5',sysdate,'3.0') ,运行到SELECT * FROM ST_PPTN_R where to_char(tm,'yyyy-mm-dd')=to_char(dt1,'yyyy-mm-dd') and STCD=STCD1;就报错
---------------------------------------
怎么有select没有into呢?
这句SQL干什么用的?
--而且应该有个异常处理
..............
update HY_DP_C
set p=p1
where dt=dt1
and STCD=STCD1;
end if;
exception--应该有个异常处理,因为设计到表更新
when others then
dbms_output.put_line(sqlerrm);
rollback;
...........
create or replace trigger biufer_ST_PPTN_R_TM
after insert or update
of tm
on ST_PPTN_R
referencing old as old_value
new as new_value
for each row
--when (to_CHAR(new_value,'HH').tm=7)
declare
timeValue varchar2(20); --
begin
timeValue:=to_char(:new_value.tm,'yyyy-mm-dd');
-- DBMS_OUTPUT.PUT_LINE('时间'||:new_value.tm);
--DBMS_OUTPUT.PUT_LINE('测站编码'||:new_value.stcd);
-- DBMS_OUTPUT.PUT_LINE('小时雨量'||:new_value.dyp);
proc_st_pptn_r_tm(:new_value.stcd,:new_value.tm,:new_value.dyp);
end;
那一个sql如果有问题 很有可能是to_char的时候有问题
可能是表中有些数据不满足日期的格式 你可以把数据查出来检查一下
估计是数据问题
报的错是ORA-04091: 表 SYSTEM.ST_PPTN_R 发生了变化, 触发器/函数不能读它
create table HY_DP_C (
STCD varchar2(8) not null,
DT DATE not null,
P NUMERIC(5, 1),
PRCD char(4),
constraint PK_HY_DP_C primary key (STCD, DT)
);create table ST_PPTN_R (
STCD varchar2(8) not null,
TM DATE not null,
DRP NUMERIC(5,1),
INTV DOUBLE PRECISION,
PDR DOUBLE PRECISION,
DYP NUMERIC(5,1),
constraint PK_ST_PPTN_R primary key (STCD, TM)
);--存储过程最后记得提交
CREATE OR REPLACE PROCEDURE proc_ST_PPTN_R_TM(STCD1 IN VARCHAR2,
dt1 IN DATE,
p IN NUMBER) AS
p1 NUMBER(5, 1); --日雨量值
pcount NUMBER(5, 1); --计算的数;
temp VARCHAR2(20);
BEGIN
p1 := 0;
SELECT COUNT(*)
INTO pcount
FROM HY_DP_C
WHERE to_char(dt, 'yyyy-mm-dd') = to_char(dt1, 'yyyy-mm-dd')
AND STCD = STCD1;
IF pcount <= 0 THEN
--插入数据到HY_DP_C表中
temp := to_char(dt1, 'yyyy-mm-dd');
INSERT INTO HY_DP_C
(stcd, DT, P)
VALUES
(STCD1, to_date(temp, 'yyyy-mm-dd'), p);
DBMS_OUTPUT.PUT_LINE('插入成功');
ELSE
--游标
DECLARE
CURSOR mycur IS
SELECT *
FROM ST_PPTN_R
WHERE to_char(tm, 'yyyy-mm-dd') = to_char(dt1, 'yyyy-mm-dd')
AND STCD = STCD1; --运行到这里的时候有错; myrecord ST_PPTN_R%ROWTYPE;
BEGIN
OPEN mycur;
FETCH mycur
INTO myrecord;
WHILE mycur%FOUND LOOP
--drp:=myrecord.drp; 修改HY_DP_C表的日雨量值
-- p:=p+myrecord.drp;
p1 := (p1 + myrecord.dyp);
-- DBMS_OUTPUT.PUT_LINE(myrecord.dyp);
--DBMS_OUTPUT.PUT_LINE('修改成功');
FETCH mycur
INTO myrecord;
END LOOP;
CLOSE mycur;
END;
--把值进行修改
UPDATE HY_DP_C
SET p = p1
WHERE dt = dt1
AND STCD = STCD1;
END IF;
COMMIT;
END;
/
还是报:ORA-04091: 表 SYSTEM.ST_PPTN_R 发生了变化, 触发器/函数不能读它
估计是在某一个触发器中有修改这个表的地方
你再仔细检查一下
其实在这里update我还没有写,我的想发是如果修改了ST_PPTN_R表的数据就应该修改HY_DP_C表中的数据
之前加一句(使用自治事务):
pragma autonomous_transaction;
我只是想,如果在表ST_PPTN_R中插入一条数据之后,再通过to_char(tm,'yyyy-mm-dd')=to_char(dt1,'yyyy-mm-dd') and STCD=STCD1;的条件,查找符合这个条件的数据,然后把dyp的值加起来,然后去修改HY_DP_C表中的数据
比如 你的insert执行的时候走触发器 你在触发器中提交了 但是insert完你又回滚了 那数据就不一致了
所以 触发器也好 触发器中的存储过程也好 默认都不让提交
你如果非要提交 就要用自制事务 就是pragma autonomous_transaction;
提交以后 那个表中的数据就可读了