Oracle中建立存储过程
CREATE OR REPLACE PROCEDURE HXRecord
(
c IN varchar2,
s IN number,
d IN varchar2,
con IN number,
descr IN varchar2
)
IS
n INTEGER :=0;
BEGIN
select count(*) into n from gas_realtime_hx where code=c;
if n=0 then
--插入语句
INSERT INTO GAS_REALTIME_HX(CODE,STATUS,REC_TIME,CONCENTRATION,DESCRIPTION) VALUES(c,s,to_date(d,'yyyy-mm-dd hh24:mi:ss'),con,descr);
-- dbms_output.put_line('更新n');
else
--更新语句
UPDATE GAS_REALTIME_HX SET STATUS=s,REC_TIME=to_date(d,'yyyy-mm-dd hh24:mi:ss'),CONCENTRATION=con,DESCRIPTION=descr WHERE CODE=c;
--dbms_output.put_line('更新456');
end if;
--插入历史表
INSERT INTO GAS_INFO_HX(CODE,STATUS,REC_TIME,CONCENTRATION,DESCRIPTION) VALUES(c,s,to_date(d,'yyyy-mm-dd hh24:mi:ss'),con,descr);
--dbms_output.put_line('更新');
END HXRecord;
(
c IN varchar2,
s IN number,
d IN varchar2,
con IN number,
descr IN varchar2
)
IS
n INTEGER :=0;
BEGIN
select count(*) into n from gas_realtime_hx where code=c;
if n=0 then
--插入语句
INSERT INTO GAS_REALTIME_HX(CODE,STATUS,REC_TIME,CONCENTRATION,DESCRIPTION) VALUES(c,s,to_date(d,'yyyy-mm-dd hh24:mi:ss'),con,descr);
-- dbms_output.put_line('更新n');
else
--更新语句
UPDATE GAS_REALTIME_HX SET STATUS=s,REC_TIME=to_date(d,'yyyy-mm-dd hh24:mi:ss'),CONCENTRATION=con,DESCRIPTION=descr WHERE CODE=c;
--dbms_output.put_line('更新456');
end if;
--插入历史表
INSERT INTO GAS_INFO_HX(CODE,STATUS,REC_TIME,CONCENTRATION,DESCRIPTION) VALUES(c,s,to_date(d,'yyyy-mm-dd hh24:mi:ss'),con,descr);
--dbms_output.put_line('更新');
END HXRecord;
一起学习GIS及其二次开发,一起进步!