/Oracle PL/SQL
------------------------------------------------视图-------------------------------------------------
1.创建视图语法:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY]
其中:
OR REPLACE:若所创建的试图已经存在,ORACLE自动重建该视图;
FORCE:不管基表是否存在ORACLE都会自动创建该视图;
NOFORCE:只有基表都存在ORACLE才会创建该视图:
alias:为视图产生的列定义的别名;
subquery:一条完整的SELECT语句,可以在该语句中定义别名;
WITH CHECK OPTION : 插入或修改的数据行必须满足视图定义的约束;
WITH READ ONLY : 该视图上不能进行任何DML操作。
案例:
CREATE OR REPLACE VIEW dept_sum_vw
(name,minsal,maxsal,avgsal)
AS SELECT d.dname,min(e.sal),max(e.sal),avg(e.sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.dname;
2.视图的定义原则:
1.视图的查询可以使用复杂的SELECT语法,包括连接/分组查询和子查询;
2.在没有WITH CHECK OPTION和 READ ONLY 的情况下,查询中不能使用 ORDER BY 子句;
3.如果没有为CHECK OPTION约束命名,系统会自动为之命名,形式为SYS_Cn;
4.OR REPLACE选项可以不删除原视图便可更改其定义并重建,或重新授予对象权限。
3.查询视图:
视图创建成功后,可以从视图中检索数据,这点和从表中检索数据一样。
示例:
SQL>SELECT * FROM dept_sum_vw;
4.修改视图:
通过OR REPLACE 重新创建同名视图即可。
5.删除视图:
DROP VIEW VIEW_NAME语句删除视图。
删除视图的定义不影响基表中的数据。
只有视图所有者和具备DROP VIEW权限的用户可以删除视图。
视图被删除后,基于被删除视图的其他视图或应用将无效。
6.查询视图定义:
SELECT view_name,text from user_views;
其中text显示的内容为视图定义的SELECT语句,可通过DESC USER_VIEWS 得到相关信息。
7.视图上的DML 操作:
DML操作应遵循的原则:
1.简单视图可以执行DML操作;
2.在视图包含GROUP 函数,GROUP BY子句,DISTINCT关键字时不能删除数据行;
3.在视图不出现下列情况时可通过视图修改基表数据或插入数据:
a.视图中包含GROUP 函数,GROUP BY子句,DISTINCT关键字;
b.使用表达式定义的列;
c.ROWNUM伪列。
d.基表中未在视图中选择的其他列定义为非空且无默认值。
------------------------------------------------序列-------------------------------------------------
1.创建序列
语法
CREATE SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1
[START WITH n] //开始的值,递增默认是minvalue 递减是maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中
NEXTVAL 返回序列中下一个有效的值,任何用户都可以引用
CURRVAL 中存放序列的当前值
NEXTVAL 应在 CURRVAL 之前指定 ,二者应同时有效
案例:
Create sequence seqEmp increment by 1 start with 1 maxvalue 3 minvalue 1
Cycle cache 2;
Cache<max-min/increment
//解释
{
Create 创建
Sequence 序列 seqEmop 序列名称
Increment by 步长
Stat with 1 开始值
Maxvalue 最大值
Minvalue 最小值
Cycle 循环 nocycle 不循环
Cache 缓存 Cache<maxvalue-minvalue/increment by//一般不采用缓存
Nextvalue 下一个
Currval 当前值
2.使用序列//先nextval 后 currval
elect seqEmp.nextval from dual;
Select seqEmp.currval from dual;
3.修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存
alter SEQUENCE sequence //创建序列名称
[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是1
[START WITH n] //开始的值,递增默认是minvalue 递减是maxvalue
[{MAXVALUE n | NOMAXVALUE}] //最大值
[{MINVALUE n | NOMINVALUE}] //最小值
[{CYCLE | NOCYCLE}] //循环/不循环
[{CACHE n | NOCACHE}];//分配并存入到内存中
4.修改序列的注意事项:
1.必须是序列的拥有者或对序列有 ALTER 权限
2.只有将来的序列值会被改变
3.改变序列的初始值只能通过删除序列之后重建序列的方法实现
5.删除序列
1.使用DROP SEQUENCE 语句删除序列
2.删除之后,序列不能再次被引用
Alter sequence seqEmp maxvalue 5;
Select seqEmp.nextval from dual;
------------------------------------------------索引-------------------------------------------------
l 一种独立于表的模式对象, 可以存储在与表不同的磁盘或表空间中
l 索引被删除或损坏, 不会对表产生影响, 其影响的只是查询的速度
l 索引一旦建立, Oracle 管理系统会对其进行自动维护, 而且由 Oracle 管理系统决定何时使用索引. 用户不用在查询语句中指定使用哪个索引
l 在删除一个表时, 所有基于该表的索引会自动被删除
l 通过指针加速 Oracle 服务器的查询速度
l 通过快速定位数据的方法,减少磁盘 I/O
--索引(主键默认建立了索引)
--当前表的数据经常被查询(建)
--当前列经常被更改(不推荐)
--表的数据量较大时(建)
//列建索引
Create index empJob on emp(job)
什么时候创建索引?
以下情况可以创建索引:
l 列中数据值分布范围很广
l 列经常在 WHERE 子句或连接条件中出现
l 表经常被访问而且数据量很大 ,访问的数据大概占数据总量的2%到4%
//删除索引
Drop index empjob;
------------------------------------------------伪列和分页-------------------------------------------------
1 ROWID
表中的每一行在数据文件中都有一个物理地址,ROWID伪列返回的就是该行的物理地址。
使用ROWID可以快速的定位表中的某一行。ROWID值可以唯一的标识表中的一行。
由于ROWID返回的是该行的物理地址,因此使用ROWID可以显示行是如何存储的。
2 ROWNUM
在查询的结果集中,ROWNUM为结果集中每一行标识一个行号,第一行返回1,第二行返回2,以此类推。
通过ROWNUM伪列可以限制查询结果集中返回的行数。
select rowid,s.*,rownum from (select * from student) s
---------------------------------------------------------------------------------------------
select *,rownum from student
https://git.oschina.net/DemoXiaoDuan/codes/l1x6a8f9wbtnmv7jidgco37