Oracle数据库知识 day02 索引&pl/sql语法&存储过程&触发器

一、视图

1.1 概念： 视图就是封装了一条复杂查询的语句。 

1.2 语法：

        CREATE VIEW 视图名称 AS 子查询

        CREATE OR REPLACE VIEW 视图名称 AS 子查询：如果视图已经存在我们可以使用语法 2 来创建视图，这样已有的视图会被覆盖。

        CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY：设置视图为只读

1.3 范例：建立一个视图，此视图包括了 20 部门的全部员工信息 

create view empvd20 as select * from emp t where t.deptno = 20 

视图创建完毕就可以使用视图来查询，查询出来的都是 20 部门的员工

二、索引

2.1 概念： 索引是用于加速数据存取的数据对象。

2.2 创建索引： 

2.2.1 单列索引：单列索引是基于单个列所建立的索引

                          CREATE index 索引名 on 表名(列名)

2.2.2 复合索引：复合索引是基于两个列或多个列的索引。

2.2.3 范例：

给 person 表的 name 建立索引 

create index pname_index on person(name); 

给 person 表创建一个 name 和 gender 的索引 create index pname_gender_index on person(name, gender);

2.3 索引的使用原则： 

        在大表上建立索引才有意义 

        在 where 子句后面或者是连接条件上的字段建立索引 

        表中数据修改频率高时不建议建立索引

三、pl/sql 基本语法

3.1 什么是 PL/SQL? 

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

3.2 pl/sql 程序语法

 

3.3 常量和变量定义

 

3.4 if 分支

语法 1： 

            IF 条件 THEN 语句 1; 

            语句 2; 

            END IF; 

语法 2： 

            IF 条件 THEN 语句序列 1； 

            ELSE 语句序列 2； 

            END IF； 

语法 3： 

            IF 条件 THEN 语句; 

            ELSIF 语句 THEN 语句;

            ELSE 语句; 

            END IF;

3.5 LOOP 循环语句

语法 1： 

            WHILE total <= 25000 LOOP

            ... 

            total : = total + salary; 

            END LOOP;

语法 2： 

            Loop 

            EXIT [when 条件]; 

            …… 

            End loop 

语法 3： 

            FOR I IN 1 . . 3 LOOP 

            语句序列 ; 

            END LOOP ;

 

3.6 游标 Cursor：游标可以存储查询返回的多条数据。

 

四、存储过程

4.1 概念：存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

4.2 创建存储过程语法：

    create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] 

    AS 

    begin 

            PLSQL 子程序体； 

    End;

五、存储函数

5.1 存储过程和存储函数的区别：

 一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。 但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以利用 out 参数，在过程和函数中实现返回多个值。

5.2 创建存储函数：

create or replace function 函数名(Name in type, Name in type, ...) return 数据类型 is 结果变量 数据类型; 

begin 

        return(结果变量); 

end 函数名;

六、触发器

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

6.2 触发器可用于：

            数据确认

            实施复杂的安全性检查

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

            数据的备份和同步

6.3 触发器的类型：

        语句级触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。 

        行级触发器（FOR EACH ROW） ：触发语句作用的每一条记录都被触发。在行级触发器中使用 old 和 new 伪记录变量, 识别值的状态。

6.4 语法：

CREATE [or REPLACE] TRIGGER 触发器名 

        {BEFORE | AFTER} 

        {DELETE | INSERT | UPDATE [OF 列名]} 

        ON 表名 

        [FOR EACH ROW [WHEN(条件) ] ] 

begin 

        PLSQL 块 

End 触发器名

6.5 范例：

 

七、Java 程序调用存储过程