mysql存储过程面试题_一个面试题，用存储过程实现。

学习游标和存储过程的好例子。嘻嘻嘻。。。。 请大家多多指点。。。。 无 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中文网，转载请注明出处，感谢您的尊重！