cx_oracle返回游标,oracle 行转列(用函数和游标完成)

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的末尾。