当我们查询学生课程及成绩时, 他们会按照列对应排列, 但是我们导出Excel时, 要把课程变成第一行, 该如何做呢?
建表SQL
CREATE TABLE ROOT.STUDENT
(
NAME VARCHAR2(50),
CLASS VARCHAR2(50),
SCORE NUMBER(*)
);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小明', '化学', 80);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小强', '化学', 90);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小明', '语文', 80);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小明', '英语', 89);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小明', '数学', 95);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小强', '数学', 90);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小强', '英语', 85);
INSERT INTO ROOT.STUDENT (NAME, CLASS, SCORE) VALUES ('小强', '语文', 70);
数据是酱紫的
如何查看我们创建的函数
-- 查看建了哪些函数,注意,引号内大写
select * from USER_OBJECTS where object_type='PROCEDURE';
select * from user_objects where object_type='VIEW';
-- 查看函数内容,引号内为你要查询的函数名,也要大写
select text from user_source where name='P_DEPT';
-- 创建个简单的函数 小栗子
create or replace function c_hs1(sname varchar2,sclass varchar2) return number is sscore number(7,2);
begin
--开始执行
SELECT score into sscore from STUDENT s WHERE s."NAME" = sname AND s."CLASS" = sclass;
--返回值
return sscore;
end;
select s.NAME,s.CLASS,c_hs1(s.NAME,s.CLASS)score from STUDENT s;
-- 创建函数
create PROCEDURE P_STU IS
-- 创建一个变量,存放我们的SQL语句
V_SQL VARCHAR2(4000);
CURSOR CLASS_CURSOR IS SELECT DISTINCT CLASS FROM student;
BEGIN
-- 拼接sql
V_SQL:= 'SELECT name ';
FOR C IN CLASS_CURSOR LOOP
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 ';
V_SQL := ' CREATE OR REPLACE VIEW RESULT AS ' || V_SQL;
DBMS_OUTPUT.PUT_LINE(V_SQL);
EXECUTE IMMEDIATE V_SQL;
END;
-- 执行函数,生成视图 若存在权限不足 则执行:grant all privileges to 用户名
BEGIN
P_STU;
END;
----结果
SELECT * FROM RESULT;
11