貌似用一层循环就行,应该是这样的,我没生成,有问题再说吧...create or replace procedure Pro_Test is
/*a为表1,b为表2*/
v_bz3 b.z3%type; --表2字段3
v_bz5 b.z5%type; --表2字段5
begin
for i in (select * from a) loop
select min(z3)
into v_bz3
from b
where z1 = i.z1
and z2 = i.z2;
select z5
into v_bz5
from b
where z1 = i.z1
and z2 = i.z2
and z3 = v_bz3;
update a set z5 = v_bz5 where 主键 = a.主键;
end loop;
end Pro_Test;
/*a为表1,b为表2*/
v_bz3 b.z3%type; --表2字段3
v_bz5 b.z5%type; --表2字段5
begin
for i in (select * from a) loop
select min(z3)
into v_bz3
from b
where z1 = i.z1
and z2 = i.z2;
select z5
into v_bz5
from b
where z1 = i.z1
and z2 = i.z2
and z3 = v_bz3;
update a set z5 = v_bz5 where 主键 = a.主键;
end loop;
end Pro_Test;
解决方案 »
- Access数据库中Update问题
- PL/SQL触发器的疑问,急!!!!!
- 如何将只有只读权限的远程数据库的表复制到本地新建表中
- 痛苦的选择: 我有一个大型中文数据库, 现在需要增加英文版, 因此数据库面临分家的问题? 黑头想成了白头?
- 将帖子提前
- 有没有办法,把Oracle的存储过程,同步到SVN之类的版本工具上?
- 如果得到指定时间范围内的所有日期(按天或按月),一个不少。
- 高分求助:TNS-12157 TNS:internal network communication error
- @@@数据库链接不行!ORA-12154: TNS:无法处理服务名
- 触发器总是提示“无法从套接字读取更多数据”
- 关于ORACLE expdb 和 impdb的问题
- 在线求助关于游标遍历的问题!!
不过语句看起来好像存在问题,其中:
select z5
into v_bz5
from b
where z1 = i.z1
and z2 = i.z2
and z3 = v_bz3; 题目中,vbz3这个变量的值是通过表1和表2中的某些字段计算出来的,所以该地方用作where条件,肯定是不行的吧?
select min(z3)
into v_bz3
from b
where z1 = i.z1
and z2 = i.z2;如果你需要用到计算后的最小值,自己改改就行了...
/*a为表1,b为表2*/
v_result number;
v_id number;
v_bz3 b.z3%type; --表2字段3
v_bz5 b.z5%type; --表2字段5
begin
for i in (select * from a) loop
v_id := 0;
for j in (select *
from b
where z1 = i.z1
and z2 = i.z2) loop
if v_id := 0 then --开始循环赋默认值
v_result := j.z3 .. .各种计算;
v_bz3 := j.z3;
else
if v_result > j.z3...各种计算 then --循环比较取最小的
v_result := j.z3 ...各种计算;
v_bz3 := j.z3;
end if;
end if;
v_id := v_id + 1;
end loop;
select z5
into v_bz5
from b
where z1 = i.z1
and z2 = i.z2
and z3 = v_bz3;
update a set z5 = v_bz5 where 主键 = a.主键;
end loop;
end Pro_Test; 难道你要这样的???
create or replace procedure Pro_Test is
/*a为表1,b为表2*/
v_result number;
v_id number;
v_bz5 b.z5%type; --表2字段5
begin
for i in (select * from a) loop
v_id := 0;
for j in (select *
from b
where z1 = i.z1
and z2 = i.z2) loop
if v_id := 0 then --开始循环赋默认值
v_result := j.z3 .. .各种计算;
v_bz5 := j.z5;
else
if v_result > j.z3...各种计算 then --循环比较取最小的
v_result := j.z3 ...各种计算;
v_bz5 := j.z5;
end if;
end if;
v_id := v_id + 1;
end loop;
update a set z5 = v_bz5 where 主键 = i.主键;
end loop;
end Pro_Test;
看这个吧!!!
=select t2f5 from
(select t2f5,t2.t2f3-t1.t1f3 as res from t2
where t2.t2f1=t1.t1f1 and t2.t2f2=t1.t1f2 order by res desc)
where rownum=1
CREATE TABLE b(a1 NUMBER,b1 NUMBER,c1 NUMBER,d1 NUMBER,e1 NUMBER);
INSERT INTO a(a,b,c,d,e) VALUES(1,2,19,3,5);
INSERT INTO a(a,b,c,d,e) VALUES(2,2,14,3,5);
INSERT INTO b(a1,b1,c1,d1,e1) VALUES(1,2,195,3,589);
INSERT INTO b(a1,b1,c1,d1,e1) VALUES(1,2,1900,3,2);
INSERT INTO b(a1,b1,c1,d1,e1) VALUES(2,2,190,3,34);
INSERT INTO b(a1,b1,c1,d1,e1) VALUES(2,2,140,3,24);COMMIT;
下面的sql用作游标更新
SELECT a1,b1,e1,c_min FROM (
SELECT a1,b1,row_number() OVER(PARTITION BY a,b ORDER BY c1-c ) rn,c1-c AS c_min,e1
FROM a,b
WHERE a=a1 AND b=b1
)WHERE rn=1