数据库面试必问知识

数据字典：

USER_INDEXS:

USER_IND_COLUNMS:

USER_OBJECTS:

USER_SEQUENSE:

Oracle数据库的很多对象，都是属于某个模式(Schema)的，模式对应于某个账户，如SCOTT 模式对应SCOTT账户。往往我们对模式和账户不做区分。数据库的表是模式对象中的一种，是最常见和最基本的数据库模式对象。一般情况下，如果没有特殊的授权，用户只能访问和操作属于自己的模式对象。比如以SCOTT账户登录，就只能访问属于SCOTT模式的表。所以通过以不同的用户身份连接，可以访问属于不同用户模式的表。

输入内容中书写注释，或将原有内容变成注释：

在一行中插入“--”，将其后的内容注释掉。

使用/*...... */，可以用来注释任何一段内容

事务

事务是一个逻辑单元执行的一系列操作，一个逻辑单元只有具备四个属性：原子性、一致性、隔离性、持久性，才能成为事务。

原子性：事务对数据的操作要么全部执行，要么全部不执行。

隔离性：由并发事务所作的修改必须和其他并发事务所做的修改隔离。事务查看数据时数据所处的状态，要么是其他事务修改前的状态，要么是之后，不能是中间状态。

一致性：事务不能破坏数据库的完整性和业务逻辑的一致性。食物不管成功还是失败，事务都是结束时，整个数据库内部数据都是正确的。

持久性：事务完成后，对系统的影响是永久的，该修改即使是出现系统故障已将保持一致。

数据库的索引

索引(INDEX)是为了加快数据的查找而创建的数据库对象，特别是对大表，索引可以有效地提高查找速度，也可以保证数据的惟一性。索引是由Oracle自动使用和维护的，一旦创建成功，用户不必对索引进行直接的操作。索引是独立于表的数据库结构，即表和索引是分开存放的，当删除索引时，对拥有索引的表的数据没有影响。

在创建PRIMARY KEY和UNIQUE约束条件时，系统将自动为相应的列创建惟一(UNIQUE)索引。索引的名字同约束的名字一致。

索引有两种：B*树索引和位图(BITMAP)索引

索引的创建：

CREATE [{UNIQUE|BITMAP}] INDEX 索引名ON 表名(列名1[，列名2，...])；

UNIQUE代表创建惟一索引，不指明为创建非惟一索引。

BITMAP 代表创建位图索引，如果不指明该参数，则创建B*树索引

删除索引：

DROP INDEX EMP_ENAME

5.2.3 查看索引

通过查询数据字典USER_INDEXES可以检查创建的索引。

通过查询数据字典USER_IND_COLUMNS可以检查索引的列。

【训练1】显示emp表的索引：

SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS FROM USER_INDEXES WHERE TABLE_NAME='EMP';

显示索引的列。

SELECT COLUMN_NAME FROM USER_IND_COLUMNS

WHERE INDEX_NAME='EMP_JOBSAL';

序列

为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。其主要的用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。序列的创建:

CREATE SEQUENCE 序列名

[INCREMENT BY n]

[START WITH n]

[{MAXVALUE n|NOMAXVALUE}]

[{MINVALUE n|NOMINVALUE}]

[{CYCLE|NOCYCLE}]

[{CACHE n|NOCACHE}]；

删除序列的语法是：

DROP SEQUENCE 序列名;

序列的修改:

ALTER SEQUENCE 序列名

[INCREMENT BY n]

[{MAXVALUE n|NOMAXVALUE}]

[{MINVALUE n|NOMINVALUE}]

[{CYCLE|NOCYCLE}]

[{CACHE n|NOCACHE}]；

序列的使用:

序列名.NEXTVAL

序列名.CURRVAL

序列的使用例子：

INSERT INTO 图书VALUES(‘A’||TO_CHAR(BOOKID.NEXTVAL, ‘fm0000’), ‘网页制作精选’,‘01’,‘刘莹’,4,26.50);

PL/SQL

PL/SQL程序的基本单元是块(BLOCK)，块就是实现一定功能的逻辑模块。一个PL/SQL程序由一个或多个块组成。块有固定的结构，也可以嵌套。一个块可以包括三个部分，每个部分由一个关键字标识。

块中各部分的作用解释如下：

(1) DECLARE：声明部分标志。

(2) BEGIN：可执行部分标志。

(3) EXCEPTION：异常处理部分标志。

(4) END；：程序结束标志。

DBMS_OUTPUT是Oracle提供的包，提供三个输出函数：

DBMS_OUTPUT.PUT(字符串表达式)；

DBMS_OUTPUT.PUT_LINE(字符串表达式)；

DBMS_OUTPUT.NEW_LINE；

游标

游标是SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下，需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。

游标有两种类型：显式游标和隐式游标。显式游标对应一个返回结果为多行多列的SELECT 语句。

显示游标的使用分成以下4个步骤：

声明游标：

在DECLARE部分按以下格式声明游标：

CURSOR 游标名[(参数1 数据类型[，参数2 数据类型...])]

IS SELECT语句;

打开游标

在可执行部分，按以下格式打开游标：

OPEN 游标名[(实际参数1[，实际参数2...])];

提取数据

在可执行部分，按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。

FETCH 游标名INTO 变量名1[，变量名2...];

或

FETCH 游标名INTO 记录变量;

关闭游标

CLOSE 游标名;

练习：

用游标提取emp表中7788雇员的名称和职务。

SET SERVEROUTPUT ON

DECLARE

v_ename VARCHAR2(10);

v_job VARCHAR2(10);

CURSOR emp_cursor IS

SELECT ename,job FROM emp WHERE empno=7788;

BEGIN

OPEN emp_cursor;

FETCH emp_cursor INTO v_ename,v_job;

DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);

CLOSE emp_cursor;

END;

执行结果为:

SCOTT,ANALYST

PL/SQL 过程已成功完成。

SET SERVEROUTPUT ON

DECLARE

CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno=7788;

emp_record emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

FETCH emp_cursor INTO emp_record;

DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal);

CLOSE emp_cursor;

END;

存储过程和函数

存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。但存储过程和函数不同于PL/SQL程序，通常把PL/SQL程序称为无名块，而存储过程和函数是以命名的方式存储于数据库中的。其优点有：

1_存储过程和函数以命名的数据库对象形式存储于数据库当中。代码不保存在本地，用户可以在任何客户机上登录到数据库，并调用或修改代码。

2_存储过程和函数可由数据库提供安全保证，要想使用存储过程和函数，需要有存储过程和函数的所有者的授权，只有被授权的用户或创建者本身才能执行存储过程或调用函数。

3_存储过程和函数的信息是写入数据字典的，所以存储过程可以看作是一个公用模块，用户编写的PL/SQL程序或其他存储过程都可以调用它（注：但存储过程和函数不能调用PL/SQL 程序）。所以，一个重复使用的功能，可以设计成为存储过程。

4_可以传递参数给存储过程和函数。存储过程的参数可以使in或out，函数的参数只能是in，函数在return部分确定返回值类型。

5_存储过程和函数必须通过编译后才能调用。

//创建存储过程

Create procedure 存储过程名[（参数）[in|out] 数据类型]

As [说明部分]

Begin

可执行部分

Exception

错误处理

End

//===================

//创建一个现实雇员总人数的存储过程

Create procedure emp_count

As

v_totalnumber number(10)

begin

select count(*) into v_totalnumber from emp;

dbms_output……

end;

//编译

//调用

Execute emp_count;

//创建函数

Create function 函数名[参数[in|out] 数据类型]

Return 数据类型

As [说明部分]

Begin

可执行部分

Return （表达式）

Exception

错误处理

End;

创建一个通过雇员编号返回雇员名称的函数GET_EMP_NAME。

步骤1：登录SCOTT账户。

步骤2：在SQL*Plus输入区中输入以下存储函数并编译：

CREATE OR REPLACE FUNCTION GET_EMP_NAME(P_EMPNO NUMBER DEFAULT 7788)

RETURN VARCHAR2

AS

V_ENAME VARCHAR2(10);

BEGIN

SELECT ENAME INTO V_ENAME FROM EMP WHERE EMPNO=P_EMPNO;

RETURN(V_ENAME);

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('没有该编号雇员！');

RETURN (NULL);

WHEN TOO_MANY_ROWS THEN

DBMS_OUTPUT.PUT_LINE('有重复雇员编号！');

RETURN (NULL);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('发生其他错误！');

RETURN (NULL);

END;

步骤3：调用该存储函数，输入并执行以下程序：

BEGIN

DBMS_OUTPUT.PUT_LINE('雇员7369的名称是：'|| GET_EMP_NAME(7369));

DBMS_OUTPUT.PUT_LINE('雇员7839的名称是：'|| GET_EMP_NAME(7839));

END;

触发器

触发器必须由事件才能触发，数据库的事件是具体的，在创建触发器时要指明触发的事件。触发器的触发事件可分为3类，分别是DML事件、DDL事件和数据库事件。

DML事件（insert，update，delete）：对表或视图操作时触发

DDL事件（create，alter，drop）创建，修改，删除数据库对象时触发

数据库事件：startup；数据打开时触发

Startdown：数据关闭是触发

Logon：用户连接数据库并建立会话时触发

Logoff：会话从数据库中断时触发

Servererror：发生服务器错误时触发

DML触发器要点：1，确定触发的表，2确定触发的事件（ＤＭＬ触发器的触发事件有ＩＮＳＥＲＴ,ＵＰＤＡＴＥ,ＤＥＬＥＴＥ三种）。3，确定触发时间：有before和after两种，分别表示触发动作发生在dml语句之前和之后。4，确定触发级别：语句级触发器和行级触发器，前者表示sql语句只触发一次触发器，后者表示sql语句影响到的每一行都要触发一次。５如果多个触发器有相同的触发时间，触发事件，最后一个被触发，其他的被覆盖。６，触发器类禁止使用ｃｏｍｍｉｔ，ｒｏｌｌｂａｃｋ等语句，也也禁止调用含ｃｏｍｍｉｔ，ｒｏｌｌｂａｃｋ语句的过程。７一个触发器可以对应不同的ｄｍｌ操作，用ｉｎｓｅｒｔｉｎｇ，ｄｅｌｅｔｉｎｇ，ｕｐｄａｔｉｎｇ这些谓词分别对应这些操作，这些谓词可以作为ｉｆ语句的判断条件。８，在行级触发器中，可以使用：ｎｅｗ和：ｏｌｄ（伪记录）来调用数据变更前后的值。

DML触发器的创建

CREATE [OR REPLACE] TRIGGER 触发器名

{BEFORE|AFTER|INSTEAD OF} 触发事件1 [OR 触发事件2...]

ON 表名

WHEN 触发条件

[FOR EACH ROW]

DECLARE

声明部分

BEGIN

主体部分

END;

例如：

触发器的创建=============================================

//先建表

create table trigger_logs(

log_id number(10) premary key,

log_table varchar2(20) not null,

log_dml varchar2(10),

log_key_id number(10),

log_date date,

log_user varchar2(20));

//序列

create sequence logs_id_sq increment by 1 start with 1

minvalue 1 maxvalue 1000 nocycle nocache;

//创建触发器

create (or raplace) trigger dml_trg

before

delete or update or insert

on emp

for eachrow

begin

if updating then

insert into triger_logs values(logs_id_sq.nextval,"emp",update,:new.empno,sysdate,user); else if deleting then

insert into triger_logs values

(......);

else

isert into trigger_logs values

(......);

endif;

end;