学习游标和存储过程的好例子。嘻嘻嘻。。。。 请大家多多指点。。。。 无 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 TES
学习游标和存储过程的好例子。嘻嘻嘻。。。。
请大家多多指点。。。。
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;
本文原创发布php中文网,转载请注明出处,感谢您的尊重!