create table students
(student_id varchar2(32),
student_name varchar2(180),
student_age number
)
insert into students values('1','金瑞','14');
insert into students values('2','中军','15');
insert into students values('3','于瑞','14');
insert into students values('4','快乐','14');
insert into students values('5','刘强','14');
insert into students values('6','红豆','14');
insert into students values('7','张明','14');
insert into students values('8','宿迁','14');
insert into students values('9','蓝蝶','14');
insert into students values('0','奇美','14');
-- 行转列 将多行数据转换为一列。例如学生表中将学生姓名串起来
create or replace
function getStudentNameString
return varchar2 as
begin
declare cursor cu_student is
select student_name from students order by student_id;
student_name varchar2(180);
rowString varchar2(1000);
begin
open cu_student;
fetch cu_student into student_name;
while cu_student%found loop
rowString :=rowString || student_name || ',';
fetch cu_student into student_name;
end loop;
return substr(rowString,1,length(rowString)-1);
end;
end getStudentNameString;
-- 测试
select getStudentNameString() from dual;
-- 代码说明
declare cursor cu_student is select student_name from students order by student_id;
用于声明一个游标,该游标可用于循环获得数据表中所有学生姓名记录;
fetch cu_student into student_name;
用于将游标所指向的当前记录的数据赋值给student_name;
while cu_student%found loop
用于循环处理游标所指向的记录;
rowString :=rowString || student_name || ',';
用于将变量student_name的值添加到rowString的末尾。
oracle 行转列(用函数和游标完成)
最新推荐文章于 2024-07-31 14:31:55 发布