good_test_function2.txt

oracle中重要的常用函数decode.以及自定义函数

请根据A表的内容用一句SQL生成报表A.
表A：
Order_no 	amt 		item		item_group
001		2000.00	HardDisk	Hardware
001		3000.00 	17'Display	Hardware
002		1600.00 	Windows2000	Software
002		800.00 	Documentation	Other

报表A:
Order_no 	Hardware 	Software 		Other
001		5000.00		0		0
002		0.00	  	1600.00		800

答案:

create or replace function concateName(v_sname varchar2) return varchar2 is
  cursor c_cursor is
    select course.cname
      from student, course, stu_course
     where student.sno = stu_course.sno
       and stu_course.cno = course.cno
       and student.sname = v_sname;
  v_concate course.cname%type := ' ';
  i         number := 0;
begin
  for cursor1 in c_cursor loop
    if i > 0 then
      v_concate := v_concate ||','||  cursor1.cname ;
    else  
      v_concate := cursor1.cname;
    end if;
    i:=1;
  end loop;
  return(v_concate);
exception
  when no_data_found then
    dbms_output.put_line(sqlcode || SQLERRM);
  WHEN OTHERS THEN
    dbms_output.put_line(sqlcode || SQLERRM);
end concateName;



调用函数

select distinct sname,concatename(sname)
      from student, course, stu_course
     where student.sno = stu_course.sno
       and stu_course.cno = course.cno