oracle数据库实现以表格形式返回数据

已知表关系：如下图所示

期望：以表格的形式返回所有学生的考试记录：学科作为横向表头，姓名作为纵向表头，如下图所示

分两大步骤

第一步：创建一个以学科为列的新表(test_info)，操作如下

详细案例请参考https://blog.csdn.net/super_DuoLa/article/details/103199300

1、打开PL/SQL，找到“Procedures”，右键“New...”，出现下图内容，根据格式填写自己所需内容

2、点击“OK”，进入到存储过程中，开始编写代码

create or replace procedure testTable(graId VARCHAR2) is
      str1 varchar2(4000); --获取建表所需数据的SQL语句
      str2 varchar2(200); --用于存放  原数据中的单个元素
      ds sys_refcursor; --游标，指针用于循环 
      createsql varchar2(4000); --创建新表
      a number; --表存在的个数
      dl varchar2(4000); --删除表
      dorpsql varchar2(4000); --验证表是否存在
  begin
        DBMS_OUTPUT.ENABLE(buffer_size => null); 
   str1:='SELECT DISTINCT subname from msubject where graid='''||graId||'''';  --所需数据
   dl:='drop table test_info';  --删除表
   createsql:='create table test_info(姓名  varchar2(4000),合计 varchar2(4000)';
     open ds for str1; --将获取到的原数据进行循环，ds作为过渡元素使用
           LOOP  
              FETCH ds INTO str2; --将ds中的结果依次插入到str2中
              EXIT WHEN ds%NOTFOUND;  --没有数据的时候，循环结束
                 createsql:=createsql||','||str2||' varchar2(4000)'; --拼接字符串【原数据中的元素】             
          END LOOP;         
      CLOSE ds;
        createsql:=createsql||')'; 
        dorpsql:='select count(*) from user_tables  where TABLE_NAME =upper(''test_info'')'; --查表
         execute immediate dorpsql into a;  --将查询是否存在的结果赋值给计数变量a
         if a = 1 then  --表存在
          execute immediate dl;   --执行删除语句
         end if;
         dbms_output.put_line(createsql); --打印语句
         execute immediate createsql;  --执行创建新表的语句

end testTable;

3、编写完成后执行存储过程：点击下图中圈起来的黄色齿轮即可

4、调用存储过程（mybatis中调用格式与之不同，详情可参考）

--语法  call  方法名称(参数)
call testTable('11111')

5、执行成功后，查看结果

到此，用查询结果作为新的列名动态创建表结构完成。

第二步：编写获取结果的存储过程

1、找到“Packages”，右键“New...”，出现下图内容，根据格式填写自己所需内容

2、与基本的存储过程不同的是，该存储过程是创建在包(Packges)下的，该存储过程分为两部分，一个是在“Packages”下，一个是在“Packages bodies”，如下图所示

3、了解基本结构后，开始编程：分两部分

create or replace package testRecords is

  -- Author  : ADMIN
  -- Created : 2019/11/27 14:07:47
  -- Purpose : 学生的考试记录
  
  TYPE testdata IS TABLE of test_info%rowtype; --返回结果testdata是表test_info类型
   
  --实现的方法（参数是年级）
  function mResult(graId VARCHAR2)   return testdata PIPELINED;


end testRecords;

create or replace package body testRecords is

  --获取学生的考试记录
  function  mResult(graId VARCHAR2)  return testdata PIPELINED is
    str varchar2(4000);--存放“课程”查询语句
    transition varchar2(4000);--循环
    ds sys_refcursor;--游标
    rests test_info%rowtype;--结果行
    mstr LONG; --结果SQL语句
  begin
   DBMS_OUTPUT.ENABLE(buffer_size => null);
   str:='SELECT DISTINCT subname from msubject where graid='''||graId||'''';
   mstr:='select MAX (U . NAME) AS 姓名,COUNT (a. ID) AS 合计';
   open ds for str;   --将str中的值赋值给ds
       LOOP  
          FETCH ds INTO transition;  --循环ds，并依次将值赋给transition
          EXIT WHEN ds%NOTFOUND;  --没有数据的时候，循环结束
          mstr:=mstr||',COUNT(CASE WHEN subname ='''||transition||''' THEN 1 END) AS  "'||transition||'"';  --如果满足学生与学科对应，则+1
       END LOOP;     
    CLOSE ds;
       mstr:=mstr||' FROM MUSER U LEFT JOIN (SELECT * FROM MRECORDS) A ON u.id=a.usid LEFT JOIN MSUBJECT  b ON b.ID = a.subname  WHERE  b.graid='''||graId||''' GROUP BY U . ID';
       dbms_output.put_line(mstr);  --打印语句【相当于Java中的  System.out.print()】
       open ds for mstr;   --将查询到的结果集赋值给临时变量ds
       LOOP  
          FETCH ds INTO rests;   --将结果集循环插入到rests结果行中
          EXIT WHEN ds%NOTFOUND;
          PIPE ROW(rests);   --多个结果行最终会返回结果行集合，即生成表格 【PIPE：管道函数即是可以返回行集合】      
       END LOOP;     
    CLOSE ds;
   return;
  end;


end testRecords;

4、执行存储过程，跟第一步中的操作一样。

5、调用存储过程（mybatis中同样的调用格式）

--语法  select * FROM table(名称.方法名(参数))
select * FROM table(testrecords.mResult('11111'))

6、查看结果：执行成功后控制台会出现以下结果

PS：点击上图中圈住的标签，可查看存储过程中打印的结果，如下图所示

至此：已实现“将学生的考试记录以表格的形式返回数据”。文章中的说明/注释，基于个人的理解，若有不妥之处，感谢指正！