oracle用存储过程 进行行转列(执行动态SQL语句)

---------------------------------------------------------------建表

----------------判断student表是否存在

declare num number; --声明  参数num  类型number
begin --开始
    select count(1) into num from user_tables where table_name='student'; 
    if num>0 then 
      execute immediate 'drop table student'; --动态sql 执行语句
    end if;
end;
----------------建表    
create table student 
(  
 name varchar2(50),  --姓名
 class varchar2(50),  --科目
 score int   --分数
) 
select * from student;

插入数据

----------------第一部分测试数据
insert into student(name,class,score)values('小明','语文',80);  
insert into student(name,class,score)values('小明','英语',89);  
insert into student(name,class,score)values('小明','数学',95);  
insert into student(name,class,score)values('小强','数学',90);  
insert into student(name,class,score)values('小强','英语',85);  
insert into student(name,class,score)values('小强','语文',70);  
COMMIT;
--select * from student;

创建行转列存储过程  

思路:使用拼接字符串方式及用到行转列decode函数实现

---------------------------------------------------------------行转列的存储过程

CREATE OR REPLACE PROCEDURE P_STU  ---存储过程名称 P_STU   存储过程没有返回结果的
IS     ---声明变量
  V_SQL VARCHAR2(2000);  --V_SQL  变量名  Varchar2 类型  用于拼接SQL语句
  CURSOR CLASS_CURSOR IS SELECT DISTINCT CLASS FROM student;  
  --游标循环使用  游标声明 CURSOR  定义游标名称 CLASS_CURSOR 查询语句  SELECT DISTINCT CLASS FROM student;  
  --distinct 唯一值 
  --查询:class 通过 student表 
    
    BEGIN
      V_SQL := 'SELECT name'; --初始值赋值 :=
      --循环语句开始
      FOR C IN CLASS_CURSOR  LOOP --循环参数 C  在游标里循环 游标:CLASS_CURSOR

        --拼接字符串  用decode 函数  sum(decode(条件,参数,值1,值2)   行转列    条件满足(等于)参数 返回 值1 ,不满足(不等于)返回 值2

        V_SQL := V_SQL || ',' || 'SUM(DECODE(CLASS,''' || C.CLASS ||
                 ''',score,0)) AS ' || C.CLASS;
      END LOOP;
      --结束循环
      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; --创建视图
      --DBMS_OUTPUT.PUT_LINE(V_SQL);
      EXECUTE IMMEDIATE V_SQL; -- 执行动态sql 语句
    END;
----------------------------------------------------------------结果

可以看到新增的存储过程p_stu


----------------执行存储过程,生成视图

BEGIN
  P_STU;               
END;
----------------结果
SELECT * FROM RESULT T;


----------------第二部分测试数据
insert into student(name,class,score)values('小明','化学',80);   
insert into student(name,class,score)values('小强','化学',90);   
COMMIT;
----------------报告存储过程,生成视图
BEGIN
 P_STU;              
END;
----------------结果
SELECT * FROM RESULT T;

--------------- 删除实体
DROP VIEW RESULT;
DROP PROCEDURE P_stu;
DROP TABLE student;  


可能遇到的问题:

执行存储过程函数时创建不了 表示权限不足

需要对oracle 操作用户grant  创建 视图的权限 以及dba的权限

假设用户A登陆操作数据库

grant create view to A;

grant  dba to A;


如果 行列固定 可以直接用decode函数实现:

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



  • 0
    点赞
  • 1
    评论
  • 7
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值