Oracle数据库基础

---

title: Oracle数据库
date: 2019-05-09 22:13:41
tags: OracleDB

---

同义词(Synonym)

 通过同义词可以给表、索引、视图等数据库对象创建一个别名， 来隐藏一些信息，提供一定的安全性

 当DBA改变数据库对象的名称时，通过同义词可以避免前台应 用程序的改变

 同义词分公有和私有，公有同义词每个用户都能使用，私有同义 词必须具有访问权限的用户才能使

---

格式

1. 创建

CREATE [OR REPLACE] [PUBLIC]SYNONYM <同义词名> FOR <对象名> 

2. 删除

异常(Exception)

1. SYSTEM EXCEPTION

DECLARE
STU STUDENT%ROWTYPE;
BEGIN
SELECT * INTO STU
FROM STUDENT WHERE SNO='201901';
DBMS_OUTPUT.PUT_LINE(STU.SNAME||','||STU.SNO);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('FUCK!');
END;

PL_SQL

1. 列变量

DECLARE
stuno student.sno%type;
stuname student.sname%TYPE;
BEGIN
SELECT SNO,SNAME INTO STUNO,STUNAME
FROM student WHERE SNO='201901';
DBMS_OUTPUT.PUT_LINE(STUNO||','||STUNAME);
END; 

1. 行变量

DECLARE
STU STUDENT%ROWTYPE;
BEGIN
SELECT * INTO STU
FROM STUDENT WHERE SNO='201901';
DBMS_OUTPUT.PUT_LINE(STU.SNAME||','||STU.SNO);
END;

3. 游标变量

DECLARE
STU STUDENT%ROWTYPE;
BEGIN
SELECT * INTO STU
FROM STUDENT WHERE SNO='201901';
DBMS_OUTPUT.PUT_LINE(STU.SNAME||','||STU.SNO);
END;
declare 
stu student%rowtype;
cursor cur_stu is select * from student;
begin 
OPEN cur_stu;
LOOP 
FETCH CUR_STU INTO STU;
EXIT WHEN CUR_STU%NOTFOUND;

4. 隐式游标

begin 
    UPDATE student set totalcredit =totalcredit*1.5;
    if sql%notfound then 
        dbms_output.putline(sql%rowcount);
    end if;
end;

触发器(Trigger)

• 触发器（Trigger）是一种特殊类型的存储过程。编译后存储在数据 库服务器中。触发器不同于存储过程。存储过程是通过存储过程名字 而被直接调用，而触发器主要是通过事件触发而被系统自动调用执行 的，不是显示执行。
• 触发器不接受任何参数，而存储过程需要显示调用，并可以接受和传 回参数。
• 触发器通常是与基本表紧密联系在一起的，可以看作是基本表定义的 一部分。触发器是在特定表上进行定义的，该表称为触发器表。当有 针对触发器表的操作时，如在表中插入（Insert）、删除（Delete） 、修改（Update）数据时，触发器就自动触发执行。

---

触发器事件可分为3类：

1. DML事件
2. DDL事件
3. 数据库事件

---

触发器类型可分为4类：

 DML触发器

 DDL触发器

 替代触发器

 数据库事件触发

格式

CREATE [OR REPLACE] TRIGGER 触发器名 

{BEFORE|AFTER|INSTEAD OF} 触发事件1 [OR 触发事件2...]
ON 表名 --为哪一张表创建触发器 
WHEN 触发条件 --当该条件满足时，触发器才能执行 
[FOR EACH ROW] --触发器为行级触发器，即对满足条件的记录触发执行一次 
DECLARE 
    声明部分 
BEGIN 
    主体部分 
END; 

存储过程(SP)

存储过程和函数也是一种PL/SQL块，是存入数据库的PL/SQL块。

• 但存储过程和函数不同于已经介绍过的PL/SQL程序，我们通常把 PL/SQL程序称为无名（匿名）块，而存储过程和函数是以命名的 方式存储于数据库中的。

其优点如下：

1. 简化输入，可以重复执行
2. 存储过程在服务器端运行，执行速度快
3. 确保数据库的安全性,只有被授权的用户或创建者本身才能执行存 储过程

1. 创建存储过程

create [or replace] procedure <过程名> (参数 [in|out|in out] 数据类型，…） is/as 
[declare 变量声明;] 
begin 执行语句; 
[exception  异常处理部分]  
end;

• in表示参数是输入给过程的；
• out表示参数需要在存储过程执行后返回给调用环境一个值；
• in out表示在过程调用时必须给定的并且在执行后返回给调用环境的参

2. 执行存储过程

• 方法1：

  EXECUTE 模式名.存储过程名[(参数1,…)];

  模式名即方案名，也就是这个存储过程是属于哪个方案。

• 方法2：

  BEGIN

  ​ 模式名.存储过程名[(参数1,…)];

  END;
  用一个匿名的PL/SQL块来执行存储过程。

• 当含有out型参数时只能使用pl_sql块来实现(在pl_sql块中可以定义变量,作为缓冲区接收回传参数).只含有in类型参数时可以采用excute语句.

3. EX

1. 定义

   • 根据学号和课程号查询学生的成绩信息。

   CREATE OR REPLACE PROCEDURE PRO_SELGRADE 
   (stuno in xs.sno%type,stucno in kc.cno%type, stugrade out cj.grade%type) 
   is
   BEGIN 
       select grade into stugrade  
       from cj 
       where 
       sno=stuno and cno=stucno; 
       exception 
       when no_data_found then dbms_output.put_line('数据没找到'); 
       when others then dbms_output.put_line('产生异常'); 
   END;
   

2. 调用

   查询学号为001221学生的101号课程的成绩信息。

   DECLARE grade cj.grade%type; 
   BEGIN 
    pro_selgrade('001221','101',grade); 
    dbms_output.put_line(grade);
   END;
   

   视图(View)

    视图和基本表不同，视图是一种虚拟的表

    视图是定义在基本表上的，也可以定义在视图上；一个视图可在几 个表或视图上建立，一个表或视图也可建立多个视图

    视图只保存查询的定义（语句），而不存储任何数据（结果）

    视图一经定义，用户就可以把它当作表一样来查询数据

   1. 视图可以来自于基本表
   2. 视图也可以来自基本表和视图
   3. 视图也可以来自于视图

   视图定义/创建

   CREATE [OR REPLACE] VIEW <视图名> [（<列名>[, <列名>]…）] AS <子查询> [WITH CHECK OPTION] [WITH READ ONLY] 
   

   执行CREATE VIEW语句时只是把视图定义存入数据字典，并不执行其中的SELECT语句

   如果子查询中包含有计算列，则必须指定列名（别名），也就是给这一列要起个别名

   WITH CHECK OPTION表示对视图进行UPDATE和INSERT操作时要保证更新的元组和 新插入的元组满足视图定义中子查询的WHERE子句中的条件表达式

   WITH READ ONLY用于确保在该视图中没有DML操作被执行

约束(Constants)

关系的完整性约束

类型	说明
实体完整性	约束主键的值
参照完整性	约束外键的值
域完整性(用户自定义完整性)	约束其他字段的值

1. 实体完整性（PRIMARY KEY)

• 规则：设属性A是基本关系R的主属性（码的组成部分）， 则属性A不能空值（NULL）和码不能出现重复值。
• 说明：
  1. 实体完整性是针对基本关系的
  2. 实体完整性要求基本关系中的元组在组成码属性上不能有空值
  3. 现实世界中的实体是可区分的，即它们具有某种唯一性标识（不取重复值)

2. 参照（引用）完整性（FOREIGN KEY）

• 规则：基本关系R中含有与另一个基本关系S的主码相对应的属性组F （F称为R的外码），则对于R中的每个元组在F上的值必须为：
  1. 或者取空值（F的每个属性值均为空值）
  2. 或者等于S中某个元组的主码值

索引(Index)

1.创建索引方式

1. 隐式创建：DBMS一般会在创建PRIMARY KEY和UNIQUE约束 列上自动建立索引
2. 显式创建：使用CREATE INDEX语句创

---

2. 显示创建索引的一般格式

CREATE [UNIQUE] INDEX <索引名> ON 表名(<列名> [ASC|DESC]) 

 索引可建立在一列或多列上，各列名之间用逗号分隔

 ASC(升序)或DESC（降序），指定索引值的排列次序，缺省为ASC

 [UNIQUE]创建唯 一性索引(即如果这一属性值有重复值,无法插入)

• 报错ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

 索引创建后，由DBMS负责使用和维护

---

3.删除索引

DROP INDEX 索引名

删除索引时，系统从数据字典中删去有关该索引的描述，同时 从数据区释放索引占用的存储空间