select * from CreAccAgeSet order by days 结果如下:
EmpCode CreDitLimit Days
5 20天后到期 -20
6 10天后到期 -10
2 5天后到期 -5
3 超期5天 5
4 超期10天 10EmpCode是主键。update 以后想得到结果:
EmpCode CreDitLimit Days
1 20天后到期 -20
2 10天后到期 -10
3 5天后到期 -5
4 超期5天 5
5 超期10天 10update 该怎么写(注意order by days 以后)?
EmpCode CreDitLimit Days
5 20天后到期 -20
6 10天后到期 -10
2 5天后到期 -5
3 超期5天 5
4 超期10天 10EmpCode是主键。update 以后想得到结果:
EmpCode CreDitLimit Days
1 20天后到期 -20
2 10天后到期 -10
3 5天后到期 -5
4 超期5天 5
5 超期10天 10update 该怎么写(注意order by days 以后)?
where exists (select 1 from CreAccAgeSet tt where t.Days=tt.days)
where exists (select 1 from CreAccAgeSet tt where t.Days=tt.days)
i number;
j number;
k number;
begin
select count(EmpCode) into j from CreAccAgeSet;
for k in 1 .. j loop
--找到最小未使用ID
select min(EmpCode - 1)
into i
from CreAccAgeSet a
where not exists
(select 1 from CreAccAgeSet b where a.EmpCode - 1 = b.EmpCode)
and a.EmpCode > 0;
--更新
update CreAccAgeSet
set = i
where in (select max(EmpCode) from CreAccAgeSet);
end loop;
exception
when no_data_found then
dbms_output.put_line('ok');
commit;--提交
when others then
dbms_output.put_line('NG');
rollback;--回滚
end;
不对,更新提示违反主键唯一。就算不唯一,更新结果全是1
to zlz_212()
不对,执行过不去。
2. CREATE TABLE TEMPTABLE AS SELECT days,row_number() over(order by days) rn FROM CreAccAgeSet
3. update CreAccAgeSet t set EmpCode =
(select rn from TEMPTABLE tt where t.Days=tt.days)
where exists (select 1 from TEMPTABLE tt where t.Days=tt.days)
as
v_num number:=0;
cursor c is select rowid rid,id,name,t from d order by t;
begin
for i in c loop
update d set id=v_num+1 where d.rowid=i.rid;
v_num:=v_num+1;
end loop;
end;
SQL> select * from d; ID NAME T
---------- ---------- ----------
1 5天后到期 -5
2 超期10天 10
3 超期5天 5
4 10天后到期 -10
5 20天后到期 -20SQL> create or replace procedure p_test
2 as
3 v_num number:=0;
4 cursor c is select rowid rid,id,name,t from d order by t;
5 begin
6 --open c;
7 for i in c loop
8 update d set id=v_num+1 where d.rowid=i.rid;
9 v_num:=v_num+1;
10 end loop;
11 end;
12 /过程已创建。SQL> exec p_testPL/SQL 过程已成功完成。
SQL> select * from d order by t; ID NAME T
---------- ---------- ----------
1 20天后到期 -20
2 10天后到期 -10
3 5天后到期 -5
4 超期5天 5
5 超期10天 10
1 update d set id=(select num from
2 (select row_number()over(order by t) num,rowid rid from d) d1
3* where d1.rid=d.rowid)
SQL> /已更新5行。SQL> select * from d order by t; ID NAME T
---------- ---------- ----------
1 20天后到期 -20
2 10天后到期 -10
3 5天后到期 -5
4 超期5天 5
5 超期10天 10
表结构和上面一样...