Question

在oracle要写这样一个存储过程，不知为什么老是报错，求解答?

create or replace procedure priceplanpro
(nstart in number,noid in number,plan in number)
as
begin
if nstart > 0 then
update priceplan r inner join ductprice p on r.ncorp=p.ncorp
set p.nprice= plan where noid = noid and start=nstart and nsta = 2021;
end if;
exception
when no_data_found then
dbms_output.put_line('数据不存在！');
end priceplanpro;

Answer 1

那段update语句改成如下语句
merge priceplan r
using ductprice p
on (r.ncorp=p.ncorp and r.noid = p.noid and r.start=p.nstart and r.nsta = 2021)
when matched then
update
set p.nprice= plan
;

追问

填上into 还是报下面的错，求解答啊大神？

merge into priceplan r using ductprice p on (r.ncorp=p.ncorp and r.noid =0 and r.start=12
and r.nsta = 2021) when matched then update set p.nprice= 0

Answer 2

Oracle中没有Update from 的语法

create or replace procedure priceplanpro
(nstart in number,noid in number,plan in number)
as
begin
if nstart > 0 then
update ductprice p set p.nprice= plan
where noid = noid and start=nstart and nsta = 2021
and exists(select * from priceplan r where r.ncorp=p.ncorp) ;
end if;
exception
when no_data_found then
dbms_output.put_line('数据不存在！');
end priceplanpro;