CREATE OR REPLACE PROCEDURE update_msg
(
MSG_DATE in date,
MSG in VARCHAR2,
POST in VARCHAR2,
PROV in VARCHAR2,
CITY in VARCHAR2,
OTHER in VARCHAR2,
COMPANY in VARCHAR2,
TEL in VARCHAR2,
MSG_HASH in NUMBER
)
is
BEGIN
if MSG_HASH = 0 then
insert into msg(MSG_DATE,msg,postcity,provfrom,cityFrom,othercityfrom,company,tel,msg_hash) values(MSG_DATE,MSG,POST,PROV,CITY,OTHER,COMPANY,TEL,MSG_HASH);
else
update msg set msg_date=MSG_DATE where msg_hash=MSG_HASH;
if sql%rowcount = 0 then
insert into msg(MSG_DATE,msg,postcity,provfrom,cityFrom,othercityfrom,company,tel,msg_hash) values(MSG_DATE,MSG,POST,PROV,CITY,OTHER,COMPANY,TEL,MSG_HASH);
end if;
end if;
END;
/
---------------------------
---------------------------
---------------------------
---------------------------
---------------------------
问题:第一个else后面的代码似乎只能执行一次,导致表里只有一条msg_hash为非零的数据
。oracle存储过程
(
MSG_DATE in date,
MSG in VARCHAR2,
POST in VARCHAR2,
PROV in VARCHAR2,
CITY in VARCHAR2,
OTHER in VARCHAR2,
COMPANY in VARCHAR2,
TEL in VARCHAR2,
MSG_HASH in NUMBER
)
is
BEGIN
if MSG_HASH = 0 then
insert into msg(MSG_DATE,msg,postcity,provfrom,cityFrom,othercityfrom,company,tel,msg_hash) values(MSG_DATE,MSG,POST,PROV,CITY,OTHER,COMPANY,TEL,MSG_HASH);
else
update msg set msg_date=MSG_DATE where msg_hash=MSG_HASH;
if sql%rowcount = 0 then
insert into msg(MSG_DATE,msg,postcity,provfrom,cityFrom,othercityfrom,company,tel,msg_hash) values(MSG_DATE,MSG,POST,PROV,CITY,OTHER,COMPANY,TEL,MSG_HASH);
end if;
end if;
END;
/
---------------------------
---------------------------
---------------------------
---------------------------
---------------------------
问题:第一个else后面的代码似乎只能执行一次,导致表里只有一条msg_hash为非零的数据
。oracle存储过程
update msg set msg_date=MSG_DATE where msg_hash=MSG_HASH;
你这个sql能执行吗?
如果说用循环的话,需要在参数里传个list,应该怎么弄啊。。
oracle 变量不区分大小写,你这个msg_hash是入参还是字段名称?肯定会有问题。