Oracle高级编程

Oracle概述：

以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器（CLIENT/SERVER）或B/S体系结构的数据库之一

Oracle数据库特地特点：

支持多用户，大事务量的事务处理

数据安全性和完整性控制

支持分布式数据处理

可移植性

Oracle体系结构：

数据库：数据的物理存储

实例：一个Oracle实例（Oracle Instance）有一系列的后台进程（BackguoundProcesses)           和内存结构（Memory Structures)组成。

数据文件：数据文件是数据库的物理存储单位。

表空间：表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻

辑映射。

用户：用户是在表空间下建立的。

Oracle语法：

创建表空间：

Create tablespace 表空间名称

Datafile    ‘物理文件名称’

Size    ’设置表空间初始大小’

Autoextend on 设置自动增长

Next    设置扩容的空间大小

创建用户:

Create user 用户名

Identified by 用户密码

Default tablespace 所属表空间

Grant dba to 用户名 赋予用户权限

整库导出：

Exp system/itcast file=导出之后的文件名 full = y

整库导入：

Imp system/itcast full = y

按用户导出

exp system/itcast owner=用户名file=导出之后的文件名

按用户导入：

imp system/itcast file=文件名 fromuser=用户名

按表导出：

exp 用户名/密码 file=备份文件名tables=表名

按表导入：

Imp 用户名/密码 file = 目标文件名 table = 表名

其他增删改查与mysql中一致

Oracle数据类型：

字符型：

Char：固定长度的字符类型，最多存储 2000 个字节

Varchar：可变长度的字符类型，最多存储 4000 个字节

Long：大文本类型。最大可以存储 2 个 G

数值型：

NUMBER : 数值类型

日期型：

DATE：日期时间型，精确到秒

TIMESTAMP：精确到秒的小数点后 9 位

二进制型：

CLOB : 存储字符,最大可以存 4 个 G

BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个 G

Oracle中特殊语法

Isnull：判断是否是空值

Between...and：范围查询（介于两者之间）

Distinct：去掉重复记录

Oracle 中的伪列：

Rowid：返回的是该行的物理地址

Rownum：为结果集中每一行表示一个行号，第一行返回1，第二行返回2......

聚合函数：

Sum()：求和

Avg()：平均数

Max()：最大值

Min()：最小值

Count()：统计

Groupby():分组

Having()：分组后条件查询

单行函数：

Length():字符串长度

Substr()：截取字符串

Concat()：字符串拼接

数值函数：

Round():四舍五入

Trunc()：截取函数

Mod()：取模

日期函数：

Sysdate：当前系统时间

Add_months:在当前基础上加上指定的月份

Last_day：所在月的最后一天

Trunc：日期截取

转换函数：

To_char()：数字转换字符串

To_char()：日期转换字符串

To_date()：字符串转日期

To_number()：字符串换数字

其他函数：

空值处理函数：

Nvl(检测的值，如果为null的值)

Nvl2(检测的值，如果不为null的值，如果为null的值)

条件取值decode

decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)

【功能】根据条件返回相应值

行列转换：

Case 条件 when  then  else end

分析函数：

Rank()：相同的值排名相同，排名跳跃

Dense_rank()：相同的值排名相同，排名连续

Row_number()：连续的排名，无论值是否相等

集合运算：

UNIONALL(并集)，返回各个查询的所有记录，包括重复记录。

UNION(并集)，返回各个查询的所有记录，不包括重复记录。

INTERSECT(交集)，返回两个查询共有的记录。

MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之

Oracle 对象

使用视图的优点：

     *简化数据操作：视图可以简化用户处理数据的方式

     *着重于特定数据：不必要的额数据或敏感数据可以不出现在视图中

     *视图提供了一个简单而有效的安全机制，可以定制不同用户得数据的访问权限

     *提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。

什么情况下使用视图：

     *SQL语句略显复杂

     *SQL语句稍显通用

     *数据比较庞大

创建或修改视图语法

    语法：CREATE [OR REPLACE][FORCE] VIEW view_name

            ASsubquery

            [WITHCHECK OPTION ]

            [WITHREAD ONLY]

        ORREPLACE：若创建的视图已经存在，ORACLE自动重建该视图。

        FORCE：不管基表是否存在ORACLE都会自动创建该视图

        subquery：一条完整的SEKECT语句，可以在该语句中定义别名

        WITHCHECK OPTION：插入或修改的数据必须满足视图定义的约束

        WITHREAD ONLY：该视图上不能进行任何DML操作

删除视图语法：

       语法：DROP VIEW 视图名；   

物化视图：

      视图是一个虚拟表（也可以认为是一条语句），基于他创建时指定的查询语句返回的结果集。每次访问它都会导致这个嘻哈讯语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。

创建物化视图语法：

      代码: CREATE METERIALIZEDVIEW view_name

            [BUILDIMMEDIATE | BUILD DEFERRED ]

            REFRESH[FAST|COMPLETE|FORCE]

            [

            ON[COMMIT |DEMAND ] | START WITH (start_time) NEXT

            (next_time)

            AS

            subquery

    解析：

        BUILDIMMEDIATE 是在创建物化视图的时候就生成数据

        BUILDDEFERRED 则在创建时不生成数据，以后根据需要再生成数据。

            默认为 BUILD IMMEDIATE。

        刷新（REFRESH）：指当基表发生了 DML 操作后，物化视图何时采用哪种

            方式和基表进行同步。

        REFRESH 后跟着指定的刷新方法有三种：FAST、COMPLETE、FORCE。FAST

            刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE 刷新对整

            个物化视图进行完全的刷新。如果选择 FORCE 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE的方式。FORCE 是默认的方式。

        刷新的模式有两种：ON DEMAND 和 ON COMMIT。ON DEMAND 指需要

            手动刷新物化视图（默认）。ONCOMMIT 指在基表发生 COMMIT 操作时自动

            刷新。         

序列：

     序列是oracle提供的用于产生一系列唯一数字的数据库对象。

创建序列：

     createsequence 序列名称

序列中伪列：

     nextval：查询序列的下一个值

     currval：查询当前序列值

创建复杂序列：

     语法：CREATE SEQUENCEsequence //创建序列名称

[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是 1

            [STARTWITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue

            [{MAXVALUEn | NOMAXVALUE}] //最大值

            [{MINVALUEn | NOMINVALUE}] //最小值

            [{CYCLE| NOCYCLE}] //循环/不循环

            [{CACHEn | NOCACHE}];//分配并存入到内存中

复杂序列循环缓存数据公式

     ceil((maxvalue-minvalue)/incrementby)>=cache(缓存)

修改和删除序列：

      修改序列：使用altresequence 语句修改系列，不能更改序列的start with参数

             altersequence 序列名称 maxvalue 5000 cycle；

      删除序列：

           dropsequence 序列名称。

同义词：

     同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。

同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。

     同义词本身不涉及安全，当你赋予一个同义词对象权限时，你实质上是在给同义词的基对象赋予权限，同义词只是基对象的一个别名。

创建同义词语法：

     create[public] synonym synooym for object;

其中synonym表示要创建的同义词的名称，object表示表，视图，序列等我们要创建同义词的对象名称。

索引：

     索引是用于加速数据存取的数据对象。合理使用索引可以大大降低i/o次数，从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据，形式类似于一颗树，而树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列。

普通索引语法：

     语法：create index 索引名称 on 表名（列名）。

唯一索引：如果我们需要在某个表某个列创建索引，而这列的值是不会重复的。这是我们可以创建唯一索引。

     语法：create uniqueindex 索引名称 on 表名（列名）。

复合索引：基于两个以上的列建立一个索引。

     创建复合索引 ：create indexindex_owners_ah on t_owners(addressid,housenumber).

反向键索引：

      应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能下降。建立反向键索引，可以是索引的值变的不规则，从而使索引数能够均匀分布。

      语法： create  index 索引名称 on 表名（列名） recerse            

位图索引：位图索引不直接存储rowid，而是存储字节位到rowid的映射

     使用场景：位图索引适合创建在低基数列上。   

     优点：减少响应时间，节省空间占用

     语法：create bitmapinidex 索引名称  on 表名（列名）;

Oracle 编程

pl/sql：

是Oracle对SQL语言的过程化扩展，指在sql命令语言中增加了过程处理语句（如分支。循环等），使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。

基本语法结构：

[declare

---声明变量

begin

-----代码逻辑

[exception

-----异常处理

end;

声明变量的语法：

变量名 类型（长度）；

变量赋值的语法：

·        直接赋值：

变量名：=变量值（这里单等相当于JAVA双等，用于判断是否相等）

·        selectinto查询赋值：

select 列名 into 变量名 from 表名 where 条件

注：结果必须是一条记录，有多条记录和没有记录都会报错

属性类修

·        %type引用型，作用：引用某表某列的字段类型。

·        %rowtype记录型， 作用：标识某个表的一行记录类型。

记录型（类似JAVA中实体类）

异常：在运行程序时出现的错误叫做异常，发生异常后，控制权转移到PL/SQL块儿的异常处理部分，通常分为两种：

·        预定义异常：当pl/sql程序违反Oracle规则或超越系统限制时隐式引发。

·        用户定义异常：用户可以在pl/sql块的声明部分定义异常，自定义的异常通过raise语句显式引发。

Oracle预定义异常21个：

access_into_null：未定义对象

case_not_found：case中若未包含相应的when，并且没有设置 else时。

COLLECTION_IS_NULL： 集合元素未初始化

CURSER_ALREADY_OPEN:游标已经打开

DUP_VAL_ON_INDEX:唯一索引对应的列上有重复的值

INVALID_CURSOR: 在不合法的游标上进行操作

INVALID_NUMBER:内嵌的 SQL 语句不能将字符转换为数字。

NO_DATA_FOUND:使用 selectinto 未返回行

TOO_MANY_ROWS: 执行 select into 时，结果集超过一行

ZERO_DIVIDE: 除数为 0

SUBSCRIPT_BEYOND_COUNT: 元素下标超过嵌套表或VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT:使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE_ERROR:赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED PL/SQL: 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码

NOT_LOGGED_ONPL/SQL: 应用程序在没有连接 oralce 数据库的情况下访问数据

PROGRAM_ERRORPL/SQL :内部问题，可能需要重装数据字典＆pl./SQL 系统包

ROWTYPE_MISMATCH: 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容

SELF_IS_NULL: 使用对象类型时，在 null 对象上调用对象 方法

STORAGE_ERROR: 运行 PL/SQL 时，超出内存空间

SYS_INVALID_ID:无效的 ROWID 字符串

TIMEOUT_ON_RESOURCE:Oracle 在等待资源时超时

异常语法：

exception

when 异常类型 then

异常处理逻辑

条件判断：

·        基本语法1：

if 条件 then

业务逻辑

endif；

·         基本语法2：

if 条件 then

业务逻辑

else

业务逻辑

endif；

·        基本语法3：

if 条件 then

业务逻辑

elsif 条件 then

业务逻辑

elsif 条件 then

业务逻辑

endif；

9，循环：

·        无条件循环：语法：

declare

定义变量

begin

loop

循环语句；

exitwhen 循环结束条件；

endloop；

end；

·        条件循环：语法：

declare

定义变量

begin

while 条件

loop

endloop；

end；

·        for循环

begin

for 变量 in 起始值 .. 终止值

loop

endloop；

end；

游标：

是系统为用户开设的一个数据缓冲区，存放sql语句的执行结果。我们可

以把游标理解为PL/SQL中的结果集。

语法结构：

在声明区声明游标：cursor 游标名称 is sql语句；

使用游标语法：

open 游标名称

loop

fetch 游标名称 into 变量

exitwhen 游标名称%notfound

endloop；

close 游标名称；

存储函数：

存储函数又称为自定义函数。可以接受一个或多个参数，返回一个结果。在函数中我们可以使用P/SQL进行逻辑的处理。

存储函数语法：

创建或修改存储过程的语法如下：

CREATE[ OR REPLACE ] FUNCTION 函数名称

（参数名称 参数类型, 参数名称 参数类型, ...）

RETURN 结果变量数据类型

IS

变量声明部分;

BEGIN

逻辑部分;

RETURN 结果变量;

[EXCEPTION

END;

存储过程：

存储过程是被命名的PL/SQL块，存储于数据库中，是数据库的一种。应用程序可以调用存储过程，执行相应的逻辑。

存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：

1.  存储函数中有返回值，且必去返回；二存储过程没有返回值，可以通过传出参数返回多个值。

2.  存储函数可以在select语句中直接使用，而存储过程不能，过程多数是被程序所调用。

3.  存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。

存储过程语法：

创建或修改存储过程的语法如下：

CREATE[ OR REPLACE ] PROCEDURE 存储过程名称

（参数名 类型, 参数名 类型, 参数名 类型）

IS|AS

变量声明部分;

BEGIN

逻辑部分

[EXCEPTION

END;

参数只指定类型，不指定长度

过程参数的三种模式：

IN 传入参数（默认）

OUT 传出参数 ，主要用于返回程序运行结果

INOUT 传入传出参数

触发器：

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。

触发器可用于:

l 数据确认

l 实施复杂的安全性检查

l 做审计，跟踪表上所做的数据操作等

l 数据的备份和同步

触发器分类:

l 前置触发器（BEFORE）

l 后置触发器（AFTER）

语法：

CREATE[or REPLACE] TRIGGER 触发器名

BEFORE| AFTER

[DELETE][[or] INSERT] [[or]UPDATE [OF 列名]]

ON 表名

[FOREACH ROW ][WHEN(条件)]

declare

……

begin

PLSQL 块

End ；

FOREACH ROW 作用是标注此触发器是行级触发器 语句级触发器

在触发器中触发语句与伪记录变量的值:

触发语句                :old                        :new

Insert               所有字段都是空(null)         将要插入的数据

Update              更新以前该行的值           更新后的值

delete               删除以前该行的值           所有字段都是空(null)

Oracle高级编程

Oracle概述：

以分布式数据库为核心的一组软件产品，是目前最流行的客户/服务器（CLIENT/SERVER）或B/S体系结构的数据库之一

Oracle数据库特地特点：

支持多用户，大事务量的事务处理

数据安全性和完整性控制

支持分布式数据处理

可移植性

Oracle体系结构：

数据库：数据的物理存储

实例：一个Oracle实例（Oracle Instance）有一系列的后台进程（BackguoundProcesses)           和内存结构（Memory Structures)组成。

数据文件：数据文件是数据库的物理存储单位。

表空间：表空间是 Oracle 对物理数据库上相关数据文件（ORA 或者 DBF 文件）的逻

辑映射。

用户：用户是在表空间下建立的。

Oracle语法：

创建表空间：

Create tablespace 表空间名称

Datafile    ‘物理文件名称’

Size    ’设置表空间初始大小’

Autoextend on 设置自动增长

Next    设置扩容的空间大小

创建用户:

Create user 用户名

Identified by 用户密码

Default tablespace 所属表空间

Grant dba to 用户名 赋予用户权限

整库导出：

Exp system/itcast file=导出之后的文件名 full = y

整库导入：

Imp system/itcast full = y

按用户导出

exp system/itcast owner=用户名file=导出之后的文件名

按用户导入：

imp system/itcast file=文件名 fromuser=用户名

按表导出：

exp 用户名/密码 file=备份文件名tables=表名

按表导入：

Imp 用户名/密码 file = 目标文件名 table = 表名

其他增删改查与mysql中一致

Oracle数据类型：

字符型：

Char：固定长度的字符类型，最多存储 2000 个字节

Varchar：可变长度的字符类型，最多存储 4000 个字节

Long：大文本类型。最大可以存储 2 个 G

数值型：

NUMBER : 数值类型

日期型：

DATE：日期时间型，精确到秒

TIMESTAMP：精确到秒的小数点后 9 位

二进制型：

CLOB : 存储字符,最大可以存 4 个 G

BLOB：存储图像、声音、视频等二进制数据,最多可以存 4 个 G

Oracle中特殊语法

Isnull：判断是否是空值

Between...and：范围查询（介于两者之间）

Distinct：去掉重复记录

Oracle 中的伪列：

Rowid：返回的是该行的物理地址

Rownum：为结果集中每一行表示一个行号，第一行返回1，第二行返回2......

聚合函数：

Sum()：求和

Avg()：平均数

Max()：最大值

Min()：最小值

Count()：统计

Groupby():分组

Having()：分组后条件查询

单行函数：

Length():字符串长度

Substr()：截取字符串

Concat()：字符串拼接

数值函数：

Round():四舍五入

Trunc()：截取函数

Mod()：取模

日期函数：

Sysdate：当前系统时间

Add_months:在当前基础上加上指定的月份

Last_day：所在月的最后一天

Trunc：日期截取

转换函数：

To_char()：数字转换字符串

To_char()：日期转换字符串

To_date()：字符串转日期

To_number()：字符串换数字

其他函数：

空值处理函数：

Nvl(检测的值，如果为null的值)

Nvl2(检测的值，如果不为null的值，如果为null的值)

条件取值decode

decode(条件,值 1,翻译值 1,值 2,翻译值 2,...值 n,翻译值 n,缺省值)

【功能】根据条件返回相应值

行列转换：

Case 条件 when  then  else end

分析函数：

Rank()：相同的值排名相同，排名跳跃

Dense_rank()：相同的值排名相同，排名连续

Row_number()：连续的排名，无论值是否相等

集合运算：

UNIONALL(并集)，返回各个查询的所有记录，包括重复记录。

UNION(并集)，返回各个查询的所有记录，不包括重复记录。

INTERSECT(交集)，返回两个查询共有的记录。

MINUS(差集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之

Oracle 对象

使用视图的优点：

     *简化数据操作：视图可以简化用户处理数据的方式

     *着重于特定数据：不必要的额数据或敏感数据可以不出现在视图中

     *视图提供了一个简单而有效的安全机制，可以定制不同用户得数据的访问权限

     *提供向后兼容性：视图使用户能够在表的架构更改时为表创建向后兼容接口。

什么情况下使用视图：

     *SQL语句略显复杂

     *SQL语句稍显通用

     *数据比较庞大

创建或修改视图语法

    语法：CREATE [OR REPLACE][FORCE] VIEW view_name

            ASsubquery

            [WITHCHECK OPTION ]

            [WITHREAD ONLY]

        ORREPLACE：若创建的视图已经存在，ORACLE自动重建该视图。

        FORCE：不管基表是否存在ORACLE都会自动创建该视图

        subquery：一条完整的SEKECT语句，可以在该语句中定义别名

        WITHCHECK OPTION：插入或修改的数据必须满足视图定义的约束

        WITHREAD ONLY：该视图上不能进行任何DML操作

删除视图语法：

       语法：DROP VIEW 视图名；   

物化视图：

      视图是一个虚拟表（也可以认为是一条语句），基于他创建时指定的查询语句返回的结果集。每次访问它都会导致这个嘻哈讯语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图（也叫实体化视图）。

创建物化视图语法：

      代码: CREATE METERIALIZEDVIEW view_name

            [BUILDIMMEDIATE | BUILD DEFERRED ]

            REFRESH[FAST|COMPLETE|FORCE]

            [

            ON[COMMIT |DEMAND ] | START WITH (start_time) NEXT

            (next_time)

            AS

            subquery

    解析：

        BUILDIMMEDIATE 是在创建物化视图的时候就生成数据

        BUILDDEFERRED 则在创建时不生成数据，以后根据需要再生成数据。

            默认为 BUILD IMMEDIATE。

        刷新（REFRESH）：指当基表发生了 DML 操作后，物化视图何时采用哪种

            方式和基表进行同步。

        REFRESH 后跟着指定的刷新方法有三种：FAST、COMPLETE、FORCE。FAST

            刷新采用增量刷新，只刷新自上次刷新以后进行的修改。COMPLETE 刷新对整

            个物化视图进行完全的刷新。如果选择 FORCE 方式，则 Oracle 在刷新时会去判断是否可以进行快速刷新，如果可以则采用 FAST 方式，否则采用 COMPLETE的方式。FORCE 是默认的方式。

        刷新的模式有两种：ON DEMAND 和 ON COMMIT。ON DEMAND 指需要

            手动刷新物化视图（默认）。ONCOMMIT 指在基表发生 COMMIT 操作时自动

            刷新。         

序列：

     序列是oracle提供的用于产生一系列唯一数字的数据库对象。

创建序列：

     createsequence 序列名称

序列中伪列：

     nextval：查询序列的下一个值

     currval：查询当前序列值

创建复杂序列：

     语法：CREATE SEQUENCEsequence //创建序列名称

[INCREMENT BY n] //递增的序列值是n 如果n是正数就递增,如果是负数就递减 默认是 1

            [STARTWITH n] //开始的值,递增默认是 minvalue 递减是 maxvalue

            [{MAXVALUEn | NOMAXVALUE}] //最大值

            [{MINVALUEn | NOMINVALUE}] //最小值

            [{CYCLE| NOCYCLE}] //循环/不循环

            [{CACHEn | NOCACHE}];//分配并存入到内存中

复杂序列循环缓存数据公式

     ceil((maxvalue-minvalue)/incrementby)>=cache(缓存)

修改和删除序列：

      修改序列：使用altresequence 语句修改系列，不能更改序列的start with参数

             altersequence 序列名称 maxvalue 5000 cycle；

      删除序列：

           dropsequence 序列名称。

同义词：

     同义词实质上是指定方案对象的一个别名。通过屏蔽对象的名称和所有者以及对分布式数据库的远程对象提供位置透明性，同义词可以提供一定程度的安全性。同时，同义词的易用性较好，降低了数据库用户的 SQL 语句复杂度。

同义词允许基对象重命名或者移动，这时，只需对同义词进行重定义，基于同义词的应用程序可以继续运行而无需修改。

     同义词本身不涉及安全，当你赋予一个同义词对象权限时，你实质上是在给同义词的基对象赋予权限，同义词只是基对象的一个别名。

创建同义词语法：

     create[public] synonym synooym for object;

其中synonym表示要创建的同义词的名称，object表示表，视图，序列等我们要创建同义词的对象名称。

索引：

     索引是用于加速数据存取的数据对象。合理使用索引可以大大降低i/o次数，从而提高数据访问性能。

索引是需要占据存储空间的，也可以理解为是一种特殊的数据，形式类似于一颗树，而树的节点存储的就是每条记录的物理地址，也就是我们提到的伪列。

普通索引语法：

     语法：create index 索引名称 on 表名（列名）。

唯一索引：如果我们需要在某个表某个列创建索引，而这列的值是不会重复的。这是我们可以创建唯一索引。

     语法：create uniqueindex 索引名称 on 表名（列名）。

复合索引：基于两个以上的列建立一个索引。

     创建复合索引 ：create indexindex_owners_ah on t_owners(addressid,housenumber).

反向键索引：

      应用场景：当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能下降。建立反向键索引，可以是索引的值变的不规则，从而使索引数能够均匀分布。

      语法： create  index 索引名称 on 表名（列名） recerse            

位图索引：位图索引不直接存储rowid，而是存储字节位到rowid的映射

     使用场景：位图索引适合创建在低基数列上。   

     优点：减少响应时间，节省空间占用

     语法：create bitmapinidex 索引名称  on 表名（列名）;

Oracle 编程

pl/sql：

是Oracle对SQL语言的过程化扩展，指在sql命令语言中增加了过程处理语句（如分支。循环等），使SQL语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来使得PLSQL面向过程但比过程语言简单、高效、灵活和实用。

基本语法结构：

[declare

---声明变量

begin

-----代码逻辑

[exception

-----异常处理

end;

声明变量的语法：

变量名 类型（长度）；

变量赋值的语法：

·        直接赋值：

变量名：=变量值（这里单等相当于JAVA双等，用于判断是否相等）

·        selectinto查询赋值：

select 列名 into 变量名 from 表名 where 条件

注：结果必须是一条记录，有多条记录和没有记录都会报错

属性类修

·        %type引用型，作用：引用某表某列的字段类型。

·        %rowtype记录型， 作用：标识某个表的一行记录类型。

记录型（类似JAVA中实体类）

异常：在运行程序时出现的错误叫做异常，发生异常后，控制权转移到PL/SQL块儿的异常处理部分，通常分为两种：

·        预定义异常：当pl/sql程序违反Oracle规则或超越系统限制时隐式引发。

·        用户定义异常：用户可以在pl/sql块的声明部分定义异常，自定义的异常通过raise语句显式引发。

Oracle预定义异常21个：

access_into_null：未定义对象

case_not_found：case中若未包含相应的when，并且没有设置 else时。

COLLECTION_IS_NULL： 集合元素未初始化

CURSER_ALREADY_OPEN:游标已经打开

DUP_VAL_ON_INDEX:唯一索引对应的列上有重复的值

INVALID_CURSOR: 在不合法的游标上进行操作

INVALID_NUMBER:内嵌的 SQL 语句不能将字符转换为数字。

NO_DATA_FOUND:使用 selectinto 未返回行

TOO_MANY_ROWS: 执行 select into 时，结果集超过一行

ZERO_DIVIDE: 除数为 0

SUBSCRIPT_BEYOND_COUNT: 元素下标超过嵌套表或VARRAY 的最大值

SUBSCRIPT_OUTSIDE_LIMIT:使用嵌套表或 VARRAY 时，将下标指定为负数

VALUE_ERROR:赋值时，变量长度不足以容纳实际数据

LOGIN_DENIED PL/SQL: 应用程序连接到 oracle 数据库时，提供了不正确的用户名或密码

NOT_LOGGED_ONPL/SQL: 应用程序在没有连接 oralce 数据库的情况下访问数据

PROGRAM_ERRORPL/SQL :内部问题，可能需要重装数据字典＆pl./SQL 系统包

ROWTYPE_MISMATCH: 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容

SELF_IS_NULL: 使用对象类型时，在 null 对象上调用对象 方法

STORAGE_ERROR: 运行 PL/SQL 时，超出内存空间

SYS_INVALID_ID:无效的 ROWID 字符串

TIMEOUT_ON_RESOURCE:Oracle 在等待资源时超时

异常语法：

exception

when 异常类型 then

异常处理逻辑

条件判断：

·        基本语法1：

if 条件 then

业务逻辑

endif；

·         基本语法2：

if 条件 then

业务逻辑

else

业务逻辑

endif；

·        基本语法3：

if 条件 then

业务逻辑

elsif 条件 then

业务逻辑

elsif 条件 then

业务逻辑

endif；

9，循环：

·        无条件循环：语法：

declare

定义变量

begin

loop

循环语句；

exitwhen 循环结束条件；

endloop；

end；

·        条件循环：语法：

declare

定义变量

begin

while 条件

loop

endloop；

end；

·        for循环

begin

for 变量 in 起始值 .. 终止值

loop

endloop；

end；

游标：

是系统为用户开设的一个数据缓冲区，存放sql语句的执行结果。我们可

以把游标理解为PL/SQL中的结果集。

语法结构：

在声明区声明游标：cursor 游标名称 is sql语句；

使用游标语法：

open 游标名称

loop

fetch 游标名称 into 变量

exitwhen 游标名称%notfound

endloop；

close 游标名称；

存储函数：

存储函数又称为自定义函数。可以接受一个或多个参数，返回一个结果。在函数中我们可以使用P/SQL进行逻辑的处理。

存储函数语法：

创建或修改存储过程的语法如下：

CREATE[ OR REPLACE ] FUNCTION 函数名称

（参数名称 参数类型, 参数名称 参数类型, ...）

RETURN 结果变量数据类型

IS

变量声明部分;

BEGIN

逻辑部分;

RETURN 结果变量;

[EXCEPTION

END;

存储过程：

存储过程是被命名的PL/SQL块，存储于数据库中，是数据库的一种。应用程序可以调用存储过程，执行相应的逻辑。

存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：

1.  存储函数中有返回值，且必去返回；二存储过程没有返回值，可以通过传出参数返回多个值。

2.  存储函数可以在select语句中直接使用，而存储过程不能，过程多数是被程序所调用。

3.  存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。

存储过程语法：

创建或修改存储过程的语法如下：

CREATE[ OR REPLACE ] PROCEDURE 存储过程名称

（参数名 类型, 参数名 类型, 参数名 类型）

IS|AS

变量声明部分;

BEGIN

逻辑部分

[EXCEPTION

END;

参数只指定类型，不指定长度

过程参数的三种模式：

IN 传入参数（默认）

OUT 传出参数 ，主要用于返回程序运行结果

INOUT 传入传出参数

触发器：

数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。

触发器可用于:

l 数据确认

l 实施复杂的安全性检查

l 做审计，跟踪表上所做的数据操作等

l 数据的备份和同步

触发器分类:

l 前置触发器（BEFORE）

l 后置触发器（AFTER）

语法：

CREATE[or REPLACE] TRIGGER 触发器名

BEFORE| AFTER

[DELETE][[or] INSERT] [[or]UPDATE [OF 列名]]

ON 表名

[FOREACH ROW ][WHEN(条件)]

declare

……

begin

PLSQL 块

End ；

FOREACH ROW 作用是标注此触发器是行级触发器 语句级触发器

在触发器中触发语句与伪记录变量的值:

触发语句                :old                        :new

Insert               所有字段都是空(null)         将要插入的数据

Update              更新以前该行的值           更新后的值

delete               删除以前该行的值           所有字段都是空(null)