学习游标和存储过程的好例子。嘻嘻嘻。。。。
请大家多多指点。。。。 create table test_sp( id varchar2(10), name varchar2(10))
insert into test_sp (id, name)values ('1', '北');insert into test_sp (id, name)values ('1', '京');insert into test_sp (id, name)values ('1', '的');insert into test_sp (id, name)values ('2', '天');insert into test_sp (id, name)values ('2', '气');insert into test_sp (id, name)values ('3', '不');insert into test_sp (id, name)values ('3', '好');
create or replace procedure sp_test iscursor test_id_cs is select distinct id from test_sp order by id asc;--声明游标 id游标 type mycur is ref cursor; cur mycur; v_name test_sp.name%type; v_id test_sp.id%type; v_sql varchar(512); v_names varchar(512); begin open test_id_cs;loop fetch test_id_cs into v_id; exit when test_id_cs%notfound;--当游标中没有数据时，退出循环 dbms_output.put_line(test_id_cs%rowcount||'--'||v_id); ---内循环 v_sql:='select name from test_sp where id ='||v_id; dbms_output.put_line('--'||v_sql); open cur for v_sql; loop fetch cur into v_name;--内循环 exit when cur%notfound;--结束内循环 -- dbms_output.put_line('name--'||v_name); //输出id相同的名字 v_names:=v_names||v_name; end loop; close cur; ----内循环 --dbms_output.put_line('names--'||v_names);--得到相同id的名字 insert into test_sp(id,name) values (v_id,v_names); v_names:='';--清空names 临时变量 end loop; commit; close test_id_cs; end sp_test;