mysql行转列 subs_Oracle函数、存储过程、视图详解

Oracle函数、存储过程

Oracle函数

以下查询基于此表：

(1)行转列

表中成绩是这样的，现在要求显示 张三 语文成绩 数学成绩 英语成绩

SELECT NAME,

MAX(CASE WHEN SUBS='语文' THEN CJ END) AS 语文,

MAX(CASE WHEN SUBS='数学' THEN CJ END) AS 数学,

MAX(CASE WHEN SUBS='英语' THEN CJ END) AS 英语

FROM STUDENT

GROUP BY NAME

(2) group by是分组函数，partition by是分区函数

over(partition by cno order by degree )

先对cno 中相同的进行分区，在cno 中相同的情况下对degree 进行排序

例一：对每个同学所有成绩进行排序

select t.*, rank() over(partition by name order by to_number(cj) desc) as rank from STUDENT t;

例二：查询每位同学的最高成绩的科目

select * from (select t.*, rank() over(partition by name order by to_number(cj) desc) as rank from STUDENT t) where rank = 1

(3)rank()与dense_rank()的区别

由以上的例子得出，rank()和dense_rank()都可以将并列第一名的都查找出来；但rank()是跳跃排序，有两个第一名时接下来是第三名；而dense_rank()是非跳跃排序，有两个第一名时接下来是第二名

(4)rownum的用法

缺点：order by后会乱序

select rownum,t.* from STUDENT t

(5)rank() over()

对总分进行排名并加上序号

SELECT NAME,

MAX(CASE WHEN SUBS='语文' THEN CJ END) AS 语文,

MAX(CASE WHEN SUBS='数学' THEN CJ END) AS 数学,

MAX(CASE WHEN SUBS='英语' THEN CJ END) AS 英语,

SUM(CJ),

RANK () OVER (ORDER BY SUM(CJ) DESC) AS rowno

FROM STUDENT

GROUP BY NAME

(6) group by分组查询后，查询的数据只能跟分组的那个字段有关

Oracle存储过程

PLSQL创建存储过程的方法

参数解释：

CREATE OR REPLACE PROCEDURE demo(param1 student.id%TYPE)

AS/IS

name student.name%TYPE;

age number :=20;

BEGIN

--业务处理.....

END

这里的as和is一样任选一个，在这里没有区别，其中demo是存储过程名称。

param1 是参数，类型和student表id字段的类型一样。

声明变量age，类型数数字，初始化为20

输出查询结果，在数据库中“||”用来连接字符串

其中参数IN表示输入参数，是参数的默认模式。

OUT表示返回值参数，类型可以使用任意Oracle中的合法类型。

OUT模式定义的参数只能在过程体内部赋值，表示该参数可以将某个值传递回调用他的过程

IN OUT表示该参数可以向该过程中传递值，也可以将某个值传出去

运算语法

选择语句

a.IF…END IF

学生表的sex字段：1-男生；0-女生

IF s_sex=1 THEN

dbms_output.put_line('这个学生是男生');

END IF

b.IF…ELSE…END IF

IF s_sex=1 THEN

dbms_output.put_line('这个学生是男生');

ELSE

dbms_output.put_line('这个学生是女生');

END IF

循环语句

a.基本循环

LOOP

IF 表达式 THEN

EXIT;

END IF

END LOOP;

b.while循环

WHILE 表达式 LOOP

dbms_output.put_line('haha');

END LOOP;

c.for循环

FOR a in 10 .. 20 LOOP

dbms_output.put_line('value of a: ' || a);

END LOOP;

(1) 没有参数的存储过程

create or replace procedure demo_cdd as

s_name varchar2(10); -- 声明变量

s_subs varchar2(10);

s_cj number;

begin

-- 给单个变量赋值

select name into s_name

from student where id = 1and name = 'zs';

-- 给多个变量赋值

select subs, cj into s_subs,s_cj

from student where id = 1 and name = 'zs';

-- 输出

dbms_output.put_line('姓名：'||s_name||',科目：'||s_subs||',成绩：'||s_cj);

end ;

(2)有参数的存储过程

create or replace procedure demoParms(

s_cj in number

) as

total number := 0;

begin

select count(1) into total from student s where s.cj = s_cj;

dbms_output.put_line('符合该条件的学生有'||total||'人');

exception

when too_many_rows then

DBMS_OUTPUT.PUT_LINE('返回值多于1行');

end;

create or replace procedure text is

begin

demoParms(89);

end;

参考链接

Oracle视图

视图：是基于一个表或多个表或视图的逻辑表，本身不包含数据，通过它可以对表里面的数据进行查询和修改。视图基于的表称为基表，Oracle的数据库对象分为五种：表，视图，序列，索引和同义词。

视图是存储在数据字典里的一条select语句。通过创建视图可以提取数据的逻辑上的集合或组合。

视图的优点：

1.对数据库的访问，因为视图可以有选择性的选取数据库里的一部分。

2.用户通过简单的查询可以从复杂查询中得到结果。

3.维护数据的独立性，试图可从多个表检索数据。

4.对于相同的数据可产生不同的视图。

视图分为简单视图和复杂视图：

1、简单视图只从单表里获取数据，复杂视图从多表；

2、简单视图不包含函数和数据组，复杂视图包含；

3、简单视图可以实现DML操作，复杂视图不可以。