Oracle数据库 - 2

Oracle

1. 视图： 视图简单的说就是封装了一条复杂查询的语句 用户可以通过视图以不同形式来显示基表中的数据

   • 创建视图有三种语法 ：Oracle中的语法非常的严格

     • 创建一个视图：CREATE VIEW 视图名称 AS 子查询

       create view empvd20 as select * from emp t where t.deptno = 20
       -- 使用视图
       select * from empvd20;
       

     • 没有创建过视图就进行视图的创建 要是已有视图进行覆盖 CREATE OR REPLACE VIEW 视图名称 AS 子查询

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

     • 创建只读视图：CREATE OR REPLACE VIEW 视图名称 AS 子查询 WITH READ ONLY

       create or replace view empvd20 as select * from emp t where t.deptno = 20 with read only
       

2. 索引：索引是用于加速数据存取的数据对象 合适使用索引能减少io的次数提高数据的访问性能 （简单的说就像是书中的目录一样 但是 要是不经常进行查询的话不建议创建索引）

   • 索引的类型：两种
     • 单列索引：单列索引是基于单个列所建立的索引 语法：CREATE index 索引名 on 表名(列名)
     • 复合索引：复合索引是基于两个列或多个列的索引（一张表中能创建多个索引但是 需要的是列的组合不同）语法：CREATE index 索引名 on 表名(列名1,；列名2,...);
   • 索引的使用原则：
     • 在数据较多的表上建立索引才有意义
     • 在 where 子句后面或者是连接条件上的字段建立索引
     • 表中数据修改频率高时不建议建立索引

3. PL/SQL（Procedure Language/SQL）

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

     declare
      	说明部分 （变量说明，游标申明，例外说明 〕
     begin
      	语句序列 （DML 语句〕… 
     exception
      	例外处理语句 
     End;
     

   • 常量和变量的定义：（在程序的声明部分进行定义）
     • 变量的类型就是 Oracle数据库中数据的类型
       • 定义语法：变量名、数据类型和长度后用分号结束说明语句
     • 常量定义：married constant boolean:=true
     • 引用变量定义：Myname emp.ename%type 引用型变量，即 my_name 的类型与 emp 表中 ename 列的类型一样 在 sql中使用 into 来赋值 如 ：

       declare
        emprec emp.ename%type;
       begin
        select t.ename into emprec from emp t where t.empno = 7369;
        dbms_output.put_line(emprec);
       end;
       

     • 记录型变量：Emprec emp%rowtype 记录变量分量的引用 emp_rec.ename:=‘ADAMS’
   • if 分支
     • 语法 1：

       IF 条件 THEN 语句 1;
        语句 2;
        END IF;
       

     • 语法 2：

       IF 条件 THEN 语句序列 1； 
        ELSE 语句序列 2；
        END IF；
       

     • 语法 3：

       IF 条件 THEN 语句;
       ELSIF 语句 THEN 语句; 
       ELSE 语句;
       END IF;
       

   • 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 ;
       

   • 游标：游标的概念就类似Java中的集合的概念 可以存储查询返回的多条数据
     • 语法：CURSOR 游标名 [ (参数名 数据类型,参数名 数据类型,...)] IS SELECT 语句; 例如：cursor c1 is select ename from emp;
     • 游标的使用步骤：
       • 打开游标： open c1; (打开游标执行查询)
       • 取一行游标的值：fetch c1 into pjob; (取一行到变量中) *
       • 关闭游标： close c1;(关闭游标释放资源)
       • 游标的结束方式 exit when c1%notfound
     • 注意： 上面的 pjob 必须与 emp 表中的 job 列类型一致

4. 存储过程：

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

   • 创建存储过程语法：

     create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] 
     AS 
     begin
      PLSQL 子程序体；
     End;
     

     create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)] 
     is
     begin
      PLSQL 子程序体；
     End 过程名;
     

5. 存储函数：

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

   • 语法：

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

6. 触发器：

   • 简介：数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列
   • 触发器的功能：
     • 数据确认
     • 实施复杂的安全性检查
     • 做审计，跟踪表上所做的数据操作等
     • 数据的备份和同步
   • 触发器的类型
     • 语句级触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。
     • 行级触发器（FOR EACH ROW） ：触发语句作用的每一条记录都被触发。在行级触发器中使用 old 和 new 伪记录变量, 识别值的状态
   • 创建的语法：

     CREATE [or REPLACE] TRIGGER 触发器名
      {BEFORE | AFTER}
      {DELETE | INSERT | UPDATE [OF 列名]}
      ON 表名
      [FOR EACH ROW [WHEN(条件) ] ]
     begin
      PLSQL 块 End 触发器名