Oracle基础三

视图的优点

视点集中、简化操作、定制数据、合并分割数据、逻辑数据独立性、安全性

 

创建视图

create [or replace] [force/noforce] view  view_name

as 查询语句

[with check option[约束]]

[with read only[约束]]

注意对视图的插入、删除、修改最后的结果数据还是要符合视图的约束

 

 

触发器

 

作用：安全性、审计、实现复杂的数据完整性规则、实现复杂的非标准的数据库相关的完整性规则、自动计算数据值

oracle触发器种类：DML触发器、替代触发器（只能用于视图上）、事件触发器(DBA使用的对于数据库上的)

 

建立触发器

create [or replace] trigger 触发器名

触发时间{before|after|instead of} 触发事件1 {or 触发事件2...}

on表名

when 触发条件

[for each row]

 

触发级别

如果是for each row 则变成行级触发器、如果不加就是语句级触发器

 

 

注意：

触发器不能执行 commit、rollback、savepoint而且不能调用执行这些语句之一的函数或过程

触发器不可以声明long或long raw类型变量

触发器不可以在定义它的表上执行DML操作

 

 

DML中有谓词

inserting、deleteing、updating

:new代表新行

:old代表旧行

when 条件中new和old不需要带：

 

 

alter trigger dml_log disable

alter trigger dml_log enable

drop trigger dml_log

 

游标

分为显式游标（多条语句必须要声明称这样）和隐式游标（所有语句都声明了一个隐式游标）

首先open语句初始化一个游标,然后重复执行fetch语句取出已检索到的数据，或是使用bulk collect批量选取数据。当处理完结果集最后一行数据时就可以close语句关闭游标。此外还可以同时打开多个游标并发处理多个查询操作。

 

oracle游标有4个属性

%ISOPEN 判断游标是否被打开

%FOUND %NOTFOUND 判断游标所在行是否有效

%ROWCOUNT 返回当前位置为止游标读取的记录行数

 

声明

CURSOR cursor_name [(parameter...)][RETURN return_type] IS select_statement;

注意：游标是可以接受参数的，且游标的形式参数必须都是IN模式。即参数只能由于传进值，不能对其进行赋值。此外不能给游标参数加NOT NULL约束

 

有时候希望能够对满足特定条件的记录逐一进行更新处理，这时可以使用带FOR UPDATE子句的游标。

 

如

Cursor c is

select username,job from users where sal<3000 for update of sal;

begin

open c;

while c%FOUND loop

   update users set sal=sal+3000 where CURRENT OF c;

end loop;

close c;

commit;

end;

 

可以使用子查询代替显式游标

 

游标变量

TYPE ref_type_name IS REF CURSOR [return_type] 

 

强类型需要指定返回类型，弱类型不需要

 

BULK COLLECT子句批量地从游标变量中取得数据放到一个或多个集合中

 

 

 

数据库事务

 

原子性、一致性、独立性、持久性

设置事务：SET_TRANSACTION

开始事务：START_TRANSACTION

COMMIT、ROLLBACK、SAVEPOINT

 

SAVEPOINT 事物保存点名称

 

ROLLBACK是返回所有的事物

ROLLBACK to 事物保存点

 

 

 

oracle指定2种事务：访问级别（READ ONLY，READ WRITE）、隔离级别

标准的隔离级别，按限制程度从少到多为

未提交读(READ UNCOMMITTED)

提交读(READ COMMITTED)

重复读(REPEATABLE READ)

序列化(SERIALIZABLE)串行

ORACLE中只支持2种：未提交读(READ UNCOMMITTED)和序列化(SERIALIZABLE)

 

事物遇到的异常主要有：错读、非重复读、假读。

事物级别越高，对性能影响越大

如设置：SET TRANSACTION ISOLATION LEVEL READ COMMITTED

锁：LOCK TABLE 锁住整张表或SELECT FOR UPDATE [NOWAIT] [OF]锁住特殊行

CURRENT OF 子句引用从游标中取出最新的行数据

如UPDATE employees set sal=newsal where CURRENT OF cl;

LOCK TABLE Employees IN ROW SHARE MODE NOWAIT

 

事物日志也叫重做日志

oracle可以在2中模式下运行

ARCHIVELOG保存所有日志

NOARCHIVELOG不保存所有日志

 

oracle数据库由2种类型的文件组成

结合在一起构成表空间的数据文件

结合在一起构成重做日志的系列的数据文件

 

 

 

 

 

 

集合

 

 联合数组、嵌套表、变长数组

 

联合数组

TYPE table_type_name IS TABLE OF

scalar_type [NOT NULL] INDEX BY index_datatypes;

其中index_datatypes既可以是数字也可以是字符

调用联合数组前要先给联合数组元素赋值。

 

嵌套表

TYPE table_name IS TABLE OF table_type [NOT NULL];

嵌套表的下标是从1开始的

嵌套表变量在定义时，必须对其进行初始化。

 

变长数组

TYPE type_name IS [VARRAY | VARYING ARRAY](max_size) OF element_type [NOT NULL]

 

自定义数据类型

CREATE OR REPLACE TYPE item_type as OBJECT(id varchar2(20),name varchar2(40));

 

集合类型转换用CAST(itemlist AS itemArray)

变长数组可以转换成嵌套表，反之亦可

 

集合的属性

COUNT、EXISTS FIRST LAST LIMIT   NEXT  PRIOR

 

集合的方法

DELETE() 、EXTEND()、TRIM()

其中

DELETE全部

DELETE(x)第几

DELETE(x，y)之间

 

EXTEND加一null

EXTEND(x)加x个null

EXTEND(x,y)加x个y

 

 

TRIM

TRIM（2）

 

 

集合操作符

SET 消除重复内容

MINUS UNION [DISTINCT] 获得并集[不包括重复]

MULTISET INTERSECT交集

MULTISET EXCEPT差集

 

多级集合（嵌套表和变长数组相互包含或包含自身）

 

 

过程、函数、包

 

 函数：返回单个值，可以作为表达式的一部分

存储过程：不返回值，不能作为表达式的一部分

 

 

 

 CREATE [OR REPLACE] PROCEDURE PROC_NAME

(

[IN|OUT|IN OUT|NOCOPY] DATATYE

)

IS|AS

   数据定义

BEGIN

    执行语句

EXCEPTION

  异常处理

END  PROC_NAME;

 

 

 存储过程有3种参数形式：IN  、OUT、IN OUT、NOCOPY（引用传递而不是值传递）

 

 

sqlplus中调用存储过程：

EXECUTE dispaly_systimes();

或

SET SERVEROUTPUT ON;

CALL  dispaly_systimes();

 

 传递参数方式：位置传递、名称传递、组合传递

 

函数

CREATE OR REPLACE FUNCTION f_name(参数)

return datatype

as

定义数据类型

BEGIN

  语句执行

EXCEPTION

   异常执行

END f_name;

 

 

包：创建包分2个步骤：包说明和包体

 

create  [or replace] package package_name IS

定义类型或函数或存储过程

END package_name

 

create [or replace] package body package_name  IS

 实现

END package_name

 

 

pl/sql优化

1.使用case替代多个查询

2.避免使用"*"

3.查询表顺序：没有索引及oracle没有对表进行统计分析的情况下，oracle会按表出现的顺序进行连接，因此因为表的顺序不对会产生十分耗服务器的数据交叉。 如果对表进行了统计分析，oracle会自动先小表后大表。

4.使用表的别名

5.用where子句替换having子句

6.减少对表的查询

7.where后面的条件顺序影响

8.不推荐使用not in（不能应用表的索引）和in

9.<>和！=永远不会使用到索引 可以使用a>0 or a<0

10.is null和 is not null 也不会使用索引

11.like操作符，如果%或_作为查询的第一字符，也不会使用索引

12.union没有union all效率高

13.用表连接替换exists

14.使用decode函数

15.尽量多使用commit （可以释放资源）

16.truncate可以替换delete

17.利用索引

 

索引使用资本原则

查询用不到，就不要建立索引

查用的查询表需要建立索引

包含的列数相对较少的表

表中的大多数查询都包含相对简单的where子句

选择where子句频繁使用关键字作为索引列

选择sql语句频繁用于表连接的关键字作为索引列

不要用那些频繁修改的列作为索引列

有函数的最好建立函数索引

对于低基数的列，并包含or等逻辑运算，考虑使用位图索引，对于大量行的表中返回大量的行时也可以考虑位图索引。

有时候复合索引比单索引更好的性能