一个面试题,用存储过程实现。
程序员文章站
2024-02-11 09:30:16
...
学习游标和存储过程的好例子。嘻嘻嘻。。。。 请大家多多指点。。。。 无 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函数in_array()使用详解