Question

sql server 存储过程转换成 oracle

CREATE procedure jsmxq
@gongsi int, -----参数公司
@period varchar(30) -----参数期间
as
declare @cunhuobianma int,@scl decimal(16,2)
declare loop_cursor cursor for
select cunhuobianma,scl
from scl
where gongsi=@gongsi and month=@period
begin
open loop_cursor
fetch next from loop_cursor
into @cunhuobianma, @scl
delete from mxql where gongsi=@gongsi and month=@period
while @@FETCH_STATUS = 0
begin
IF EXISTS (select * from scm_chanpinjiegouhead a where gongsi=@gongsi and chanchengpin=@cunhuobianma )
begin

Answer 1

-- 这个之所以修改参数的名字的原因是你的参数与表中的列重名了
CREATE or Replace procedure jsmxq(Pgongsi int, Pperiod varchar(30)) as
declare
cunhuobianma int;
scl number(16,2);
begin
for cur in (select cunhuobianma, scl
from scl
where gongsi = pgongsi
and month = pperiod) loop
delete from mxql where gongsi = pgongsi and month= pperiod;
if sql%notfound then
begin
-- 你没有写全的语句
end;
end if;
end loop;
end;

Answer 2

CREATE OR REPLACE procedure jsmxq
gongsi NUMBER, -----参数公司
period varchar2(30) -----参数期间
as
BEGIN
declare
cunhuobianma NUMBER,
scl decimal(16,2)
declare loop_cursor cursor for
select cunhuobianma,scl
from scl
where gongsi:=gongsi
and month:=period
begin
open loop_cursor
fetch next from loop_cursor
into cunhuobianma, scl
delete from mxql where gongsi:=gongsi and month=:period
while @@FETCH_STATUS = 0
begin
IF EXISTS (select * from scm_chanpinjiegouhead a where gongsi=@gongsi and chanchengpin=@cunhuobianma )
begin