- --建表
- 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 is
- CURSOR 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;
- --该片段来自于http://www.codesnippet.cn/detail/1102201511753.html
来源: http://www.codesnippet.cn/detail/1102201511753.html