---------------------------------------------------------------Table building

----------------Determine whether the student table exists

declare num number; --Declare parameters num  type number
begin --start
    select count(1) into num from user_tables where table_name='student'; 
    if num>0 then 
      execute immediate 'drop table student'; --dynamic sql Execute statement
    end if;
end;

----------------Table building    
create table student 
(  
 name varchar2(50),  --full name
 class varchar2(50),  --subject
 score int   --fraction
) 
select * from student;

insert data

----------------Part I test data
insert into student(name,class,score)values('Xiaoming','chinese',80);  
insert into student(name,class,score)values('Xiaoming','English',89);  
insert into student(name,class,score)values('Xiaoming','mathematics',95);  
insert into student(name,class,score)values('cockroach','mathematics',90);  
insert into student(name,class,score)values('cockroach','English',85);  
insert into student(name,class,score)values('cockroach','chinese',70);  
COMMIT;
--select * from student;

Create row to column stored procedure

Idea: using concatenated string and using row to column decode function

---------------------------------------------------------------Stored procedure for row to column

CREATE OR REPLACE PROCEDURE P_STU  ---Stored procedure name P_STU   Stored procedure does not return result
IS     ---Declare variables
  V_SQL VARCHAR2(2000);  --V_SQL  Variable name  Varchar2 Type for splicing SQL sentence
  CURSOR CLASS_CURSOR IS SELECT DISTINCT CLASS FROM student;  
  --Cursor loop using cursor declaration CURSOR  Define cursor name CLASS_CURSOR Query statement  SELECT DISTINCT CLASS FROM student;  
  --distinct Unique value 
  --Query: class adopt student surface 
    
    BEGIN
      V_SQL := 'SELECT name'; --Initial value assignment :=
      --Loop statement start
      FOR C IN CLASS_CURSOR  LOOP --Cycle parameters C  Loop the cursor in the cursor: CLASS_CURSOR

        --For splicing strings decode function  sum(decode(condition,Parameter, value 1, value 2)   Row to column    Return value 1 if the condition is satisfied (equal to) and return value 2 if the condition is not satisfied (not equal to)

        V_SQL := V_SQL || ',' || 'SUM(DECODE(CLASS,''' || C.CLASS ||
                 ''',score,0)) AS ' || C.CLASS;
      END LOOP;
      --End cycle
      V_SQL := V_SQL || ' FROM student GROUP BY name';
      --DBMS_OUTPUT.PUT_LINE(V_SQL);
      V_SQL := 'CREATE OR REPLACE VIEW RESULT  AS '||  V_SQL; --Create view
      --DBMS_OUTPUT.PUT_LINE(V_SQL);
      EXECUTE IMMEDIATE V_SQL; -- Execution dynamics sql sentence
    END;
----------------------------------------------------------------result

You can see the new stored procedure p_stu

----------------Execute stored procedure and generate view

BEGIN
  P_STU;               
END;
----------------result
SELECT * FROM RESULT T;

----------------Part II test data
insert into student(name,class,score)values('Xiaoming','Chemistry',80);   
insert into student(name,class,score)values('cockroach','Chemistry',90);   
COMMIT;
----------------Report stored procedures, generating views
BEGIN
 P_STU;              
END;
----------------result
SELECT * FROM RESULT T;

--------------- Delete entity
DROP VIEW RESULT;
DROP PROCEDURE P_stu;
DROP TABLE student;  

Possible problems:

Cannot create stored procedure function when executing, indicating insufficient permission

You need the permission to create views and dba for grant, the oracle operation user

Suppose user A logs in to operate the database

grant create view to A;

grant  dba to A;

If the row and column are fixed, you can use the decode function directly:

 http://blog.csdn.net/weixin_39209728/article/details/77323623