存储过程 循环回填数据

Oracle版本：

begin
for rec in( select t.ourid,t.price1, t.price2,t.amount2, cp.nowprice,t.calcquan
 from dema_deviceinnerdtl t INNER JOIN vw_sbas_chargeprice cp on t.deviceid = cp.chargeid and cp.orgid=2
where t.price2 = 0) loop
    update dema_deviceinnerdtl t set t.price2 = rec.nowprice, t.amount2 = rec.nowprice * rec.calcquan
    where t.ourid = rec.ourid and t.price2=0;
end loop;
end;

 

SQLServer版本：

declare  @id nvarchar(100),@value nvarchar(100);
begin
    declare c_test_main cursor fast_forward for select PatiID,ipNumber from [asg340].[dbo].[Test_Dict] ;
    open c_test_main;--打开游标
    while 1=1 --开始循环
    begin
        fetch next from c_test_main into @id,@value; --赋值到变量中
        if(@@fetch_status!=0)break;--如果没有结果退出循环
        update Study  set  HospitalizedNo = @id where Hospitalized = 'Y' and HospitalizedNo = @value;
        --select @id as a,@value as b
    end
    close c_test_main    --关闭游标
    deallocate c_test_main    --释放游标
end