oracle存储过程动sql语句,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