# 存储过程算法

JS 2013-06-28 12:31:41

create table test_20130627
(nm1 varchar2(10),
nm2 varchar2(10),
nm3 varchar2(10),
sizes varchar2(10)
;

insert into test_20130627
select '一级A','二级A','三级A','1' from dual
union all
select '一级A','二级A','三级A','2' from dual
union all
select '一级A','二级A','三级A','3' from dual
union all
select '一级A','二级A','三级A','4' from dual
union all
select '一级A','二级A','三级A','5' from dual
union all
select '一级A','二级A','三级B','1' from dual
union all
select '一级A','二级A','三级B','2' from dual
union all
select '一级A','二级A','三级B','3' from dual
union all
select '一级A','二级A','三级B','4' from dual
union all
select '一级A','二级B','三级C','1' from dual
union all
select '一级A','二级B','三级C','2' from dual
union all
select '一级A','二级B','三级C','3' from dual
union all
select '一级B','二级A','三级A','1' from dual
union all
select '一级B','二级A','三级A','2' from dual
union all
select '一级B','二级B','三级B','1' from dual
commit;

1-----------------------------------------------
/*这中方式已经达到目的，贴到这里给大家分享*/
create table temp
as
select nm1,nm2,nm3,count(1) cn from test_20130627 group by nm1,nm2,nm3;
commit;

create or replace procedure wx_20130627
is
v_n number:=1;
tmp temp%rowtype;
cursor c1 is select * from temp;
begin
open c1;
loop
fetch c1 into tmp;
v_n :=1;
exit when c1%notfound;
while v_n<=tmp.cn loop
update test_20130627 set nm3=nm3||chr(64+v_n)
where nm1=tmp.nm1
and nm2=tmp.nm2
and nm3=tmp.nm3
and sizes=v_n;
dbms_output.put_line(tmp.nm1||v_n);
v_n:=v_n+1;
end loop;
end loop;
end;
------------------------------------------------
2----------------------------------------------
/*这个语法有问题，但是不知道哪里错了，请大神看看*/
create or replace procedure wx_20130627
is
v_n number:=1;
tmp test_20130627%rowtype;
cursor c1 is select nm1,nm2,nm3 from temp;
cursor c2(p1 test_20130627%rowtype) is select * from test_20130627
where nm1=p1.nm1
and nm2=p1.nm2
and nm3=p1.nm3 for update;
begin
open c1;
loop
fetch c1 into tmp;
exit when c1%notfound;
for i in c2(tmp) loop
update test_20130627 set nm3=nm3||chr(64+v_n) where current of c2;
v_n:=v_n+1;
end loop

end loop;
close c1;
end ;
------------------------------------------------

...全文
84 点赞 收藏 1

1 条回复

Oracle

1.6w+

Oracle开发相关技术讨论

2013-06-28 12:31