一个面试题，用存储过程实现。-mysql教程-PHP中文网

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

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

发布： 2016-06-07 14:58:00

原创

1988 人浏览过

学习游标和存储过程的好例子。嘻嘻嘻。。。。请大家多多指点。。。。无 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;

登录后复制