title: Oracle数据库
date: 2019-05-09 22:13:41
tags: OracleDB
同义词(Synonym)
通过同义词可以给表、索引、视图等数据库对象创建一个别名， 来隐藏一些信息，提供一定的安全性
当DBA改变数据库对象的名称时，通过同义词可以避免前台应 用程序的改变
同义词分公有和私有，公有同义词每个用户都能使用，私有同义 词必须具有访问权限的用户才能使
格式
- 创建
CREATE [OR REPLACE] [PUBLIC]SYNONYM <同义词名> FOR <对象名>
- 删除
异常(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
- 列变量
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;
- 行变量
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;
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;
- 隐式游标
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类：
- DML事件
- DDL事件
- 数据库事件
触发器类型可分为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. 创建存储过程
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
-
定义
- 根据学号和课程号查询学生的成绩信息。
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; -
调用
查询学号为001221学生的101号课程的成绩信息。
DECLARE grade cj.grade%type; BEGIN pro_selgrade('001221','101',grade); dbms_output.put_line(grade); END;视图(View)
视图和基本表不同，视图是一种虚拟的表
视图是定义在基本表上的，也可以定义在视图上；一个视图可在几 个表或视图上建立，一个表或视图也可建立多个视图
视图只保存查询的定义（语句），而不存储任何数据（结果）
视图一经定义，用户就可以把它当作表一样来查询数据
- 视图可以来自于基本表
- 视图也可以来自基本表和视图
- 视图也可以来自于视图
视图定义/创建
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）和码不能出现重复值。
-
说明：
- 实体完整性是针对基本关系的
- 实体完整性要求基本关系中的元组在组成码属性上不能有空值
- 现实世界中的实体是可区分的，即它们具有某种唯一性标识（不取重复值)
2. 参照（引用）完整性（FOREIGN KEY）
-
规则：基本关系R中含有与另一个基本关系S的主码相对应的属性组F （F称为R的外码），则对于R中的每个元组在F上的值必须为：
- 或者取空值（F的每个属性值均为空值）
- 或者等于S中某个元组的主码值
索引(Index)
1.创建索引方式
- 隐式创建：DBMS一般会在创建PRIMARY KEY和UNIQUE约束 列上自动建立索引
- 显式创建：使用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 索引名
删除索引时，系统从数据字典中删去有关该索引的描述，同时 从数据区释放索引占用的存储空间
