CREATE OR REPLACE PROCEDURE test IScursor curm is select rownum rn_m,a,b from m order by a; m_row curm%rowtype;cursor curn is select rownum rn_n,c,d from n order by c; n_row curn%rowtype; BEGIN open curm; loop fetch curm into m_row; exit when curm%notfound; open curn; loop fetch curn into n_row; exit when curn%notfound; if m_row.rn_m=n_row.rn_n then UPDATE N SET E = D * m_row.b; commit; end if; end loop; close curn; end loop; close curm; END;
create or replace procedure proc_update_n is cursor cur_n is select * from n; v_m_max int; v_m_b float; v_sql varchar2(100); begin select max(a) into v_m_max from m; for n_result in cur_n loop if n_result.c <= v_m_max then select b into v_m_b from ( select * from m where n_result.c <= m.a order by m.a ) where rownum < 2; v_sql := 'update n set e=d*'||v_m_b||' where c='||n_result.c||' and d='||n_result.d||''; execute immediate v_sql; end if; end loop; end; /
3楼4楼 代码都没有判断c在a的区间里面,不是用n表的第一行更新m表的第一行
1条sql应该可以搞定吧?merge into n using (select a, b, lag(a) over(order by a) la from m) m on (n.c > la and n.c <= a) when matched then update set n.e = m.b * n.d;
先: create view v_xxx as select lad(t.a,1,0) over(order by t.a) value_begin, t.a, t.b from tab_m t;然后再用这个view和你的N表联合, 直接一句update就应该能搞定了!!!good luck~
UPDATE n SET n.e=n.d*(SELECT m.b FROM m WHERE n.c>0 AND n.c<= m.a)
select lag(a,1) over (order by a) a1,a,d from m可以看下有没有帮助。
那要是n表中还有g,h,i字段需要根据区间来计算,怎么写? 真心求教
要是还有几个字段需要按照区间更新怎么改?update A set a=1,b=2,...
那要是n表中还有g,h,i字段需要根据区间来计算,怎么写? 真心求教举出 具体例子
那要是n表中还有g,h,i字段需要根据区间来计算,怎么写? 真心求教举出 具体例子比如 n表中 还有g,h两字段,g字段值需要根据m表中的a字段区间内,然后update set g=g*对应的b h字段需要根据m表中的a字段区间内,然后update set h=h*对应的b+100 能在一个语句里面实现吗? 谢谢!
IScursor curm is select rownum rn_m,a,b from m order by a;
m_row curm%rowtype;cursor curn is select rownum rn_n,c,d from n order by c;
n_row curn%rowtype; BEGIN
open curm;
loop
fetch curm into m_row;
exit when curm%notfound; open curn;
loop
fetch curn into n_row;
exit when curn%notfound;
if m_row.rn_m=n_row.rn_n then
UPDATE N SET E = D * m_row.b;
commit;
end if;
end loop;
close curn; end loop;
close curm; END;
create or replace procedure proc_update_n
is
cursor cur_n
is
select * from n;
v_m_max int;
v_m_b float;
v_sql varchar2(100);
begin
select max(a) into v_m_max from m;
for n_result in cur_n loop
if n_result.c <= v_m_max then
select b into v_m_b from (
select * from m where n_result.c <= m.a order by m.a
) where rownum < 2;
v_sql := 'update n set e=d*'||v_m_b||' where c='||n_result.c||' and d='||n_result.d||'';
execute immediate v_sql;
end if;
end loop;
end;
/
using (select a, b, lag(a) over(order by a) la from m) m
on (n.c > la and n.c <= a)
when matched then
update set n.e = m.b * n.d;
create view v_xxx as
select lad(t.a,1,0) over(order by t.a) value_begin, t.a, t.b from tab_m t;然后再用这个view和你的N表联合,
直接一句update就应该能搞定了!!!good luck~
那要是n表中还有g,h,i字段需要根据区间来计算,怎么写?
真心求教
那要是n表中还有g,h,i字段需要根据区间来计算,怎么写?
真心求教举出 具体例子
那要是n表中还有g,h,i字段需要根据区间来计算,怎么写?
真心求教举出 具体例子比如 n表中 还有g,h两字段,g字段值需要根据m表中的a字段区间内,然后update set g=g*对应的b
h字段需要根据m表中的a字段区间内,然后update set h=h*对应的b+100
能在一个语句里面实现吗?
谢谢!