温故而知新：Oracle基础知识复习(二)

一、表和视图

1.1.表：表由记录(行row)和字段(列column)构成,是数据库中存储数据的结构。

1.1.1.表的创建和操作：

CREATE TABLE library(
      libno VARCHAR2(2),
      libname VARCHAR2(30),
      libaddress VARCHAR2(30),
      libphone VARCHAR2(20)
);

通过子查询创建表： CREATE TABLE library1 AS SELECT *FROM library;

复制表：CREATE TABLE library2(bname,price) AS SELECT bname,price FROM library WHERE 1=2;

删除表：DROP TABLE library2;

重命名［RENAME 旧表名 TO 新表名］：RENAME library2 TO library3;

清空表［TRUNCATE TABLE 表名］：TRUNCATE TABLE library3;

添加注释［COMMENT ON TABLE 表名 IS '...'］;

查看表［DESCRIBE 表名］;DESC library;

1.1.2.表的约束

五种约束：

1.NOT NULL（非空）：用于确保列不能为 NULL，必须为该列提供数据；

2.UNIQUE（唯一约束）：唯一的标识列的数据，该列可以为空 NULL，但不能重复；

3.PRIMARY KEY（主键约束）：唯一的标识表行的数据，不能重复，也不能为 NULL；

4.FOREIGN KEY（外键约束）：定义主从表之间的关系；

5.CHECK（检查约束）：强制表行数据必须满足的条件。

1）定义约束：

定义NOT NULL约束：

CREATE TABLE TEST01(
    tno INT NOT NULL,
    tname varchar2(10)  CONSTRAINT con_name NOT NULL, //con_name是约束名.
    salary number(6,2)  );

定义UNIQUE约束：

CREATE TABLE TEST02(
    tno INT,
    name varchar2(10),
    salary number(6,2),
    CONSTRAINT con_name UNIQUE(name)  );

定义PRIMARY KEY约束：

CREATE TABLE TEST03(
    tno INT PRIMARY KEY,
    tname varchar2(10),
    tlocal varchar2(20)  );

定义 FOREIGN KEY 约束：

CREATE TABLE TEST04(
    tno int,
    tname varchar2(10),
    salary  number(6,2),
    tno int CONSTRAINT fk_tno references dept(deptno)  );

定义 CHECK 约束（既可以在列级定义，也可以在表级定义）：

CREATE  TABLE  TEST05(
    tno int,
    tname varchar2(10),
    salary number(6,2),
    CHECK (salary between 1000 and 5000)  );

复合约束（只在表级定义）：

CREATE TABLE item(
  order_id number(3),
  item_id number(3),
  product varchar2(20),
  PRIMARY KEY(order_id,item_id)  );

2）增加约束：

unique,primary key,foreign key,check约束：使用alter table…add…子句;
not null约束：必须使用alter table…modify…子句;

alter  table  test  modify name not null;
alter  table  test  add constraint u_name  unique(dname);
alter  table  test  add  primary key(name);

3）删除约束：

ALTER TABLE test DROP CONSTRAINT fk_dno;

带cascade的删除约束：

当删除特定表的主键约束时，若该表具有相关的从表，那么在删除主键约束时必须带有cascade选项，否则会显示错误信息。

ALTER TABLE test DROP primary key CASCADE;

1.1.3.分区表

分区表的作用：

        在某些场合会使用非常大的表，比如人口信息统计表。若一个表很大，就会降低查询的速度，并增加管理的难度。一旦发生

磁盘损坏，可能整个表的数据就会丢失，恢复比较困难。根据这一情况，可以创建分区表，把一个大表分成几个区(小段)，对数据

的操作和管理都可以针对分区进行，这样就可以提高数据库的运行效率。分区可以存在于不同的表空间上，提高了数据的可用性。

        分区有三种：范围分区、哈斯分区和混合分区。

1.2.视图

1.2.1.视图的概念：

        视图是基于一张或多张表，或其他视图的逻辑表。本身没有数据，在视图上进行的select，insert，update，delete操作都是

针对视图基表完成的。视图与表不同，表是独立存在的实体，而视图只是一种定义，对应一个查询语句。

视图创建的语法：

        CREATE [OR REPLACE] [FORCE|NOFORCE]VIEW 视图名[(别名1[,别名2...])]AS 子查询

        [WITH CHECK OPTION    [CONSTRAINT 约束名]]  [WITH READ ONLY]

        其中：

        OR REPLACE 表示替代已经存在的视图。FORCE表示不管基表是否存在，创建视图。

        NOFORCE表示只有基表存在时，才创建视图，是默认值。别名是为子查询中选中的列新定义的名字，替代查询表中原有的

        列名。子查询是一个用于定义视图的SELECT查询语句，可以包含连接、分组及子查询。

        WITH CHECK OPTION表示进行视图插入或修改时必须满足子查询的约束条件。后面的约束名是该约束条件的名字。

        WITH READ ONLY表示视图是只读的。

视图删除的语法：

        DROP VIEW 视图名；

1.2.2.作用：

        限制数据访问：视图只能访问 select 语句涉及到的列； 

        简化复杂查询：可以基于常用的复杂查询建立视图。

1.2.3.分类：

        简单视图：基于单个表建立，不包含函数，表达式和分组数据； 
        复杂视图：包含函数，表达式和分组数据； 
        连接视图：基于多个表建立的视图，简化连接查询； 
        只读视图：只允许 select 操作，禁止任何 DML 操作。 

在视图上执行 DML 操作的原则： 

        若视图包含group by子句，分组函数，distinct关键字和rowid伪列，那么不能在该视图上执行delete操作和update操作。

或者如果视图没有包含视图基表的not null列，也不能执行insert操作。 

1.2.4.建立视图：

        当建立视图时，如果不提供视图列别名，那么 oracle 会自动使用子查询的列名或者列别名；如果视图子查询包含函数或

表达式，那么必须定义列别名。

建立简单视图：

CREATE VIEW boo AS SELECT bname,author,price FROM book WHERE library='01';
CREATE VIEW emp_vu AS SELECT empno,ename,sal,job,deptno FROM emp;

建立复杂视图： 

CREATE VIEW job_vu AS SELECT job,avg(sal) avgsal,sum(sal) sumsal,max(sal) maxsal,min(sal) minsal FROM emp GROUP BY job;
SELECT * FROM job_vu WHERE job='CLERK';

建立连接视图： 

CREATE VIEW d_view AS SELECT d.deptno,d.dname,e.empno,e.ename,e.sal FROM dept d,emp e WHERE a.deptno=b.deptno AND a.deptno=20;
SELECT * FROM d_view;

建立只读视图： 

CREATE VIEW emp_vu AS SELECT * FROM emp WHERE deptno=20 WITH READ ONLY; 
SELECT ename,sal,deptno FROM emp_vu;

在视图上定义CHECK约束：

CREATE VIEW emp AS SELECT * FROM emp WHERE deptno=10 WITH CHECK OPTION CONSTRAINT chk_vu10; 

建立视图时定义别名：

CREATE VIEW empview(name,salary,title) AS SELECT ename,sal,job FROM emp;
SELECT name,salary,title FROM empview WHERE title='CLERK'; 

修改视图定义：

CREATE OR REPLACE VIEW empview(name,salary,title) AS SELECT ename,sal,job FROM emp;

重新编译视图(当视图基表定义改变之后)：

ALTER VIEW empview COMPILE;

删除视图：

DROP VIEW empview;

显示视图信息：

SELECT view_name FROM user_views;

确定视图是否允许 DML 操作： 

SELECT column_name,insertable,updatable,deletable FROM user_updatable_columns 
                                                         WHERE table_name='DE_VIEW' AND column_name IN ('DNAME','ENAME');

二、其他数据库对象

2.1.Oracle数据库模式对象：

TABLE（表）：用于存储数据的基本结构；

VIEW（视图）：以不同的侧面反映表的数据，是一种逻辑上的表；

INDEX（索引）：加快表的查询速度；

CLUSTER（聚簇）：将不同表的字段并用的一种特殊结构的表集合；

SEQUENCE（序列）：生成数字序列，用于在插入时自动填充表的字段；

SYNONYM（同义词）：为简化和便于记忆，给对象起的别名；

DATABASE LINK（数据库链接）：为访问远程对象创建的通道；

STORED PROCEDURE＆FUNCTION（存储过程和函数）：存储于数据库中的可调用的程序和函数；

PACKAGE＆PACKAGE BODY（包和包体）：将存储过程、函数及变量按功能和类别进行捆绑；

TRIGGER（触发器）：由DML操作或数据库事件触发的事件处理程序；

2.2.索引：

        索引(INDEX)是为了加快数据的查找而创建的数据库对象，特别是对大表，索引可以有效地提高查找速度，也可以保证数据

的惟一性。索引是独立于表的数据库结构，即表和索引是分开存放的，当删除索引时，对拥有索引的表的数据没有影响。

索引有两种：B树索引和位图索引。B树索引包括：惟一索引、非惟一索引、一列简单索引和多列复合索引。

建立索引的原则： 

        只有较大的表才需要索引。索引应建立在where子句经常引用的列上，为了提高多表连接的性能，应在连接列上建立索引。

 若经常基于某些列进行排序操作，应在这些列上建立索引。不要在小表上建立索引，会降低DML操作的速度。

 

创建单列索引：CREATE INDEX i_ename ON emp(ename);

创建复合索引（不超过32列）：CREATE INDEX i_deptno_job ON emp(deptno,job);

创建惟一索引：CREATE UNIQUE INDEX i_dname ON dept(dname); 

重建索引(执行delete或update操作后)：ALTER INDEX i_ename REBUILD; 

联机重建索引：ALTER INDEX i_ename REBUILD ONLINE; 

删除索引：DROP INDEX i_deptno_job;

显示索引信息：SELECT index_name,uniqueness,status FROM ind WHERE table_name='EMP';

2.3.序列：

概念：

        序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。其主要用途

是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。

建立序列（创建序列需要CREATE SEQUENCE系统权限）：

CREATE SEQUENCE devno_seq START WITH 50 INCREMENT BY 10 MAXVALUE 99 CACHE 10;

说明：sequence指定序列名，increment by指定序列增量（默认值 1），start with指定生成的第一个序列号，maxvalue指定生成

的最大序列号，cache指定在内存中可以预分配的序列号个数(默认 20)。

修改序列(序列的初始值不能修改) ：

ALTER SEQUENCE deptno_seq MAXVALUE 200 CACHE 20;

删除序列：

DROP SEQUENCE deptno_seq;

查看用户的序列：

SELECT SEQUENCE_NAME,MIN_VALUE,MAX_VALUE,INCREMEN T_BY,LAST_NUMBER FROM USER_SEQUENCES;

2.4.同义词

        同义词(SYNONYM)是为模式对象起的别名，可以为表、视图、序列、过程、函数和包等数据库模式对象创建同义词。

同义词有两种：公有同义词和私有同义词。公有同义词是对所有用户都可用的。创建公有同义词必须拥有系统权限

CREATE PUBLIC SYNONYM；创建私有同义词需要CREATE SYNONYM系统权限。私有同义词只对拥有同义词的

账户有效，但私有同义词也可以通过授权，使其对其他用户有效。同义词通过给本地或远程对象分配一个通用或简单的名称，

隐藏了对象的拥有者和对象的真实名称，也简化了SQL语句。

2.5.聚簇

        所谓聚簇(CLUSTER)，就是生长在一起的表。聚簇包含一张或多张表，表的公共列被称为聚簇关键字，在公共列上具有

同一值的列物理上存储在一起。那么在什么情况下需要创建聚簇呢?通常在多个表有共同的列时，应使用聚簇。比如有一张学生

基本情况表，其中包含学生的学号、姓名、性别、住址等信息。另外，还设计了一张学生成绩表，其中除了包含学生成绩，也

包含学生的学号、姓名、性别。那么这两张表共同的列就可以创建成聚簇。这样两张表的共同的学号、姓名和性别，就存放在

了一起，相同的值只存放一次。如果两个表通过聚簇列进行联合，则会大大提高查询的速度，但对于插入等操作则会降低效率。

2.6.数据库链接

        数据库链接(DATABASE LINK)是在分布式环境下，为了访问远程数据库而创建的数据通信链路。数据库链接隐藏了对远程

数据库访问的复杂性。通常，我们把正在登录的数据库称为本地数据库，另外的一个数据库称为远程数据库。有了数据库链接，

可以直接通过数据库链接来访问远程数据库的表。

三、PL/SQL基础

3.1.PL/SQL的基本构成：

3.1.1.概念：

        PL/SQL语言是SQL语言的扩展，具有为程序开发而设计的特性，如数据封装、异常处理、面向对象等特性。PL/SQL是嵌入

到Oracle服务器和开发工具中的，所以具有很高的执行效率和同Oracle数据库的完美结合。在PL/SQL模块中可以使用查询语句

和数据操纵语句(即进行DML操作)，这样就可以编写具有数据库事务处理功能的模块。

        数据定义(DDL)和数据控制(DCL)命令的处理，需要通过Oracle提供的特殊的DBMS_SQL包来进行。PL/SQL还可以用来编写

过程、函数、包及数据库触发器。过程和函数也称为子程序，在定义时要给出相应的过程名和函数名。它们可以存储在数据库中

成为存储过程和存储函数，并可以由程序来调用，它们在结构上同程序模块类似。

        PL/SQL过程化结构的特点是：可将逻辑上相关的语句组织在一个程序块内。通过嵌入或调用子块，构造功能强大的程序。

可将一个复杂的问题分解成为一组便于管理、定义和实现的小块。

3.1.2.块结构和基本语法要求：

PL/SQL程序的基本单元是块(BLOCK)，块就是实现一定功能的逻辑模块。一个PL/SQL程序由一个或多个块组成。块有固定的结构，

也可以嵌套。一个块可以包括三个部分，每个部分由一个关键字标识。块中各部分的作用解释如下：

1).DECLARE：声明部分标志；

2).BEGIN：可执行部分标志；

3).EXCEPTION：异常处理部分标志；

4).END：程序结束标志；

3.2.结构控制语句

3.2.1.分支结构

分支结构是最基本的程序结构，分支结构由IF语句实现。使用IF语句，根据条件可以改变程序的逻辑流程。

分支结构形式如下：

        IF 条件1 THEN

        语句序列1;

        [ELSIF 条件2 THEN

        语句序列2;

        ELSE

        语句序列n;]

        END IF;
-----------------------
条件部分是一个逻辑表达式，值只能是真(TRUE)、假(FALSE)或空(NULL)。语句序列为多条可执行的语句。

根据具体情况，分支结构可以有以下几种形式：

IF-THEN-END IF

IF-THEN-ELSE-END IF

IF-THEN-ELSIF-ELSE-END IF

-------------------------------------------------------

3.2.2.选择结构

CASE语句适用于分情况的多分支处理,可有以下三种用法。

1.基本CASE结构

语句的语法如下：

        CASE 选择变量名

        WHEN 表达式1 THEN

        语句序列1

        WHEN 表达式2 THEN

        语句序列2

        WHEN 表达式n THEN

        语句序列n

        ELSE

        语句序列n+1

        END CASE;
------------------------------
        在整个结构中选择变量的值同表达式的值进行顺序匹配，若相等，则执行相应的语句序列，若不等，则执行ELSE部分的语句

序列。

3.2.3.循环结构

循环结构是最重要的程序控制结构，用来控制反复执行一段程序。比如我们要进行累加，则可通过适当的循环程序实现。

PL/SQL循环结构可划分为以下3种：

1).基本LOOP循环

2).FOR LOOP循环

3).WHILE LOOP循环

四、游标和异常处理

4.1.游标的概念

        游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。

在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。

这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

        游标有两种类型：显式游标和隐式游标。对于一次从数据库中提取一行数据的查询和DML操作，系统都会使用一个隐式游标。

但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为

多行多列的SELECT语句。游标一旦打开，数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，

并进行处理。

4.2.隐式游标

4.2.1.概念：DML操作和单行SELECT语句会使用隐式游标。

4.3.显式游标

4.4.异常处理

4.4.1.错误处理

错误处理部分位于程序的可执行部分之后，是由WHEN语句引导的多个分支构成的。

错误处理的语法如下：

EXCEPTION

WHEN 错误1[OR 错误2] THEN

语句1；

WHEN 错误3[OR 错误4] THEN

语句2；

WHEN OTHERS

语句n；

END；
-------------
其中：
        错误是在标准包中由系统预定义的标准错误，或是由用户在程序的说明部分自定义的错误。语句序列就是不同分支的错误处理

部分。

        凡是出现在WHEN后面的错误都是可以捕捉到的错误，其他未被捕捉到的错误，将在WHEN OTHERS部分进行统一处

理，OTHENS必须是EXCEPTION部分的最后一个错误处理分支。若在该分支中进一步判断错误种类，可以通过使用预定义函数

SQLCODE()和SQLERRM()来获得系统错误号和错误信息。若在程序的子块中发生了错误，但子块没有错误处理部分，则错误会

传递到主程序中。

五、存储过程、函数和包

5.1.存储过程和函数

5.1.1.存储过程的概念：

        存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。但存储过程和函数不同于已经介绍过的PL/SQL程序，通常

把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。存储过程的一些优点：

1).存储过程和函数以命名的数据库对象形式存储于数据库当中。存储在数据库中的优点很明显，因为代码不保存在本地，用户可以

    在任何客户机上登录到数据库，并调用或修改代码。

2).存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的

    用户或创建者本身才能执行存储过程或调用函数。

3).存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的PL/SQL程序或其他存储过程都

    可调用它(但存储过程和函数不能调用PL/SQL程序)。一个重复使用的功能，可以设计成为存储过程，比如：显示一张工资统计

    表，可以设计成为存储过程；一个经常调用的计算，可以设计成为存储函数；根据雇员编号返回雇员的姓名，可以设计成存储

    函数。

4).像其他高级语言的过程和函数一样，可传递参数给存储过程或函数，参数的传递也有多种方式。存储过程可以有返回值，也可以

    没有返回值，存储过程的返回值必须通过参数带回；函数有一定的数据类型，像其他的标准函数一样，可通过对函数名的调用

    返回函数值。存储过程和函数需要进行编译，以排除语法错误，只有编译通过才能调用。

5.2.包

5.2.1.包的概念和组成

        包是用来存储相关程序结构的对象，它存储于数据字典中。包由两个分离的部分组成：包头(package)和包体(package body)。

包头是包的说明部分，是对外的操作接口，对应用是可见的；包体是包的代码和实现部分，对应用来说是不可见的黑盒。

包中包含的程序结构：

1.过程(Procedure)：带参数的命名的程序模块

2.函数(Function)：带参数、具有返回值的命名的程序模块

3.变量(Variable)：存储变化的量的存储单元

4.常量(Constant)：存储不变的量的存储单元

5.游标(Cursor)：用户定义的数据操作缓存区，在可执行部分使用

6.类型(Type)：用户定义的新的结构类型

7.异常(Execption)：在标准包中定义或由用户自定义，用于处理程序错误

六、触发器

6.1.触发器的种类和触发事件

概念：触发器必须由事件才能触发。触发器的触发事件分可为3类，分别是DML事件、DDL事件和数据库事件。

触发器的类型可划分为4种：数据操纵语言(DML)触发器、替代(INSTEAD OF)触发器、数据定义语言(DDL)触发器和数据库事件

触发器。

触发器事件的种类：

1.DML 事件(3 种) 

1.1.INSERT：在表或视图中插入数据时触发

1.2.UPDATE：修改表或视图中的数据时触发 

1.3.DELETE：在删除表或视图中的数据时触发

2.DDL 事件(3 种)

2.1.CREATE：在创建新对象时触发

2.2.ALTER：修改数据库或数据库对象时触发

2.3.DROP：删除对象时触发

3.数据库事件(5 种)

3.1.STARTUP：数据打开时触发

3.2.SHUTDOWN：在使用NORMAL或IMMEDIATE选项关闭数据库时触

3.3.LOGON：当用户连接到数据库并建立会话时触发

3.4.LOGOFF：当一个会话从数据库中断开时触发

3.5.SERVERERROR：发生服务器错误时触发

6.2.DML触发器

6.3.数据库事件触发器

6.4.DDL事件触发器

6.5.替代触发器

6.6.查看触发器

修订：

参考：

注：