视图

来源：互联网发布：四维码扫描软件下载编辑：程序博客网时间：2024/06/08 14:34

视图 view

命名的sql语句,存储在数据字典中

视图本身不包含数据(物化视图除外)，视图的数据来源于基表或其他视图（创建视图时的查询所关联的表）

视图优缺点

可以限制数据的访问

方便管理

增加了数据的负担

查看视图的定义

user_views

视图分类

简单视图

只有单表

不包含去重,聚集,分组,函数,伪列,计算表达式

表中受非空约束的列在试图中没包含

可以在视图上执行DML操作，这叫可更新视图

对简单视图作delete和update的限制:

组函数

分组计算

有去重处理(distinct)

有rownum伪列

带有计算表达式所生成的伪列

对简单视图作insert的限制:

组函数

分组计算

有去重处理(distinct)

有rownum伪列

带有计算表达式所生成的伪列

表中受非空约束作用的列在视图中没有包含

复杂视图

包含一个或多个表

可以包含函数和分组数据

不能进行DML操作基表

创建语法

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name

[col1_alias,col2_alias...]

AS subquery

[WITH CHECK OPTION [CONSTRATINT constraint]]

[WITH READ ONLY [CONSTRATINT constraint]]

OR REPLACE ：如果视图存在则替换它(DDL重定义视图,生产中不要乱用)

[FORCE|NOFORCE]: 是否忽略基表不存在的错误默认是不忽略(可以先建视图再建表)

col1_alias,col2_alias...:可以自己定义列名目的为了隐藏表中的原始列名

SUBQUERY：是一个完整的SELECT语句，定义了视图的行和列

WITH CHECK OPTION：用于控制DML视图时不满足视图的数据报错

WITH READ ONLY：设置只读视图

conn scott/seker

SQL> select 'drop table '||tname||' purge;' from tab where tname not in ('DEPT','EMP','BONUS','SALGRADE');

SQL> select deptno,max(sal) from emp group by deptno;

DEPTNO MAX(SAL)

---------- ----------

30 2850

20 3000

10 5000

SQL> create or replace view v1 as select deptno,max(sal) from emp group by deptno;

create or replace view v1 as select deptno,max(sal) from emp group by deptno

ERROR at line 1:

ORA-00998: must name this expression with a column alias

涉及到聚集函数时一定要取别名别名的本质就是将非法的列名合法化

SQL> create or replace view v1 as select deptno,max(sal) sal from emp group by deptno;

View created.

SQL> select * from v1;

DEPTNO SAL

---------- ----------

30 2850

20 3000

10 5000

SQL>

SQL> select VIEW_NAME,TEXT from user_views where VIEW_NAME='V1';

VIEW_NAME

------------------------------

TEXT

--------------------------------------------------------------------------------

select deptno,max(sal) sal from emp group by deptno

SQL>

查询过程:在数据字典里找到视图的名字根据名字取出视图的定义执行定义的SQL 返回数据

删除视图

drop view view_name;

强制选项

SQL> create or replace force view v1 as select empno,ename,sal,deptno from my_emp where deptno=10;

Warning: View created with compilation errors.

SQL> select * from v1;

select * from v1

ERROR at line 1:

ORA-04063: view "SCOTT.V1" has errors

SQL> create table my_emp as select * from emp;

Table created.

SQL> select * from v1;

EMPNO ENAME SAL DEPTNO

---------- ---------- ---------- ----------

7782 CLARK 2450 10

7839 KING 5000 10

7934 MILLER 13001 10

SQL>

SQL> delete my_emp where empno=9999;

1 row deleted.

SQL> update v1 set sal=sal+1 where empno=7934;

1 row updated.

SQL> insert into v1 values(9999,'SEKER',1000,20);

1 row created.

SQL> select * from v1;

EMPNO ENAME SAL DEPTNO

---------- ---------- ---------- ----------

7782 CLARK 2450 10

7839 KING 5000 10

7934 MILLER 1302 10

SQL> 视图的条件是deptno=10 , 却可以插入deptno!=10的内容没有约束不太合理

with check option 选项

对视图添加约束,只能操作符合定义中where条件的约束

SQL> create or replace force view v1 as select empno,ename,sal,deptno from my_emp where deptno=10 with check option;

View created.

SQL> insert into v1 values(8888,'BLUES',1000,20);

insert into v1 values(8888,'BLUES',1000,20)

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> insert into v1 values(8888,'BLUES',1000,10);

1 row created.

SQL>

带有with check option就依据当前定义的where子句生成check约束对象这个约束对象默认系统取名可以定义名字

SQL> create or replace force view v1 as select empno,ename,sal,deptno from my_emp where deptno=10;

View created.

SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name='V1';

no rows selected

SQL> create or replace force view v1 as select empno,ename,sal,deptno from my_emp where deptno=10 with check option ;

View created.

SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name='V1';

TABLE_NAME CONSTRAINT_NAME C

---------- -------------------- -

V1 SYS_C005236V

SQL> create or replace force view v1 as select empno,ename,sal,deptno from my_emp where deptno=10 with check option constraint ck_ON_V1;

View created.

SQL> select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from user_constraints where table_name='V1';

TABLE_NAME CONSTRAINT_NAME C

---------- -------------------- -

V1 CK_ON_V1V

SQL>

WITH READ ONLY

视图是只读的，因此不能做DML操作

IN LINE 视图 (内嵌视图)

不需要创建

子查询出现在 from 子句中

查询那些人的工资大于所在部门的平均工资

SQL> select a.ename,a.sal,a.deptno,round(b.avgsal)

from emp a,

(select deptno,avg(sal) avgsal from emp group by deptno) b

where a.deptno=b.deptno and a.sal > b.avgsal order by 3;

ENAME SAL DEPTNO ROUND(B.AVGSAL)

---------- ---------- ---------- ---------------

KING 5000 10 2917

JONES 2975 20 2175

SCOTT 3000 20 2175

FORD 3000 20 2175

ALLEN 1600 30 1567

BLAKE 2850 30 1567

6 rows selected.

SQL>

(select deptno,avg(sal) avgsal from emp group by deptno) b 既是内嵌视图

内嵌视图+ROWNUM 实现分页

ROWNUM

伪列

随获取行的数量递增

SQL> select rownum,empno,sal from emp;

ROWNUM EMPNO SAL

---------- ---------- ----------

1 7369 800

2 7499 1600

3 7521 1250

4 7566 2975

5 7654 1250

6 7698 2850

7 7782 2450

8 7788 3000

9 7839 5000

10 7844 1500

11 7876 1100

12 7900 950

13 7902 3000

14 7934 1300

14 rows selected.

SQL>

SQL> select rownum,empno,sal from emp where rownum>=2;

no rows selected

SQL>

上面的执行语句类似于向下面的伪代码段传入表达式

rownum = 1

for i in (select * from emp)

loop

exit when not rownum>=N

OUTPUT record to temp

rownum = rownum+1

end loop

sort temp

试想一下如果你传入的值时2 代码是否能运行

我们得到了 1>=2的条件直接exit了所以无获取行

rownum 使用在where时的规则

条件必须是从1开始

合法条件:小于(<) 一个大于1的数

小于等于(<=)一个大于等于1的数

不等于(<>) 一个非1和非0的正数

不符合上面条件则没有返回行

rownum不能以任何表的名称作为前缀

这样才有意义否则取不到行

练习:取出emp表按工资排序后结果的 6-10行

SQL> select * from (select rownum row_num,a.* from (select empno,ename,sal from emp order by sal) a) where row_num >=6 and row_num <=10;

---------- ---------- ---------- ----------

6 7934 MILLER 1300

7 7844 TURNER 1500

8 7499 ALLEN 1600

9 7782 CLARK 2450

10 7698 BLAKE 2850

SQL>

写SQL向替代变量传递值第一个参数输入每页的行数第二个参数输入要打印的第几页

分页算法

开始位置大于每页的行数*(要打印的第几页-1)

结束位置小于等于开始位置+每页的行数

select * from (select rownum rn,ename from emp) b where rn>&&r*(&&p-1) and rn <=&r*(&p-1)+&r;