Oracle学习
第一章 Oracle数据库概述
1.2 数据库基本术语
数据库
数据库是位于计算机存储设备上存放数据的仓库
数据库管理系统（DBMS）
数据库系统中对数据进行管理的软件系统，踏实数据库系统的核心部分，数据库系统的一切操作，包括查询、更新和各种控制，都是在DBMS进行的。
数据库系统（DBM）
在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统（及其开发工具）、应用系统和数据库管理员（DataBase Administrator, DBA）构成。
数据库的三级模式
外模式
外模式也称子模式或用户模式，它是数据库用户（包括应用程序员和最终用户）能够看见和使用的局部数据的逻辑结构和特征的描述，是数据库用户的数据视图，是与某一应用有关的数据的逻辑表示
概念模式
也称模式，是对数据库中全局数据逻辑结构的描述，是全体用户公共的数据视图。
主要描述数据的概念记录类型及其关系，还包括数据间的一些语句约束，对它的描述可用DBMS中的DDL定义。
DML（Data Manipulation Language，数据操作语言）：用于检索或者修改数据。 DML包括:SELECT：用于检索数据； INSERT：用于增加数据到数据库； UPDATE：用于从数据库中修改现存的数据 DELETE：用于从数据库中删除数据。 DDL（Data Definition Language，数据定义语言）： 用于定义数据的结构，比如 创建、修改或者删除数据库对象。 DDL包括：DDL语句可以用于创建用户和重建数据库对象。下面是DDL命令： CREATE TABLE：创建表 ALTER TABLE DROP TABLE：删除表 CREATE INDEX DROP INDEX DCL（Data Control Language，数据控制语言）：用于定义数据库用户的权限。 DCL包括： ALTER PASSWORD GRANT REVOKE CREATE SYNONYM ———————————————— 版权声明：本文为CSDN博主「w183705952」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。 原文链接：https://blog.csdn.net/w183705952/article/details/7354974
内模式
也称存储模式，一个模式只有一个内模式，它是数据物理结构和存储方式的描述，定义所有的内部记录类型、索引和文件的组织形式，以及数据控制方面的细节。
数据库的二级映象
外模式/模式映象和模式/内模式映象
数据模型
概念数据模型
概念模型是从现实世界到机器世界的一个中间层次
面向世界、面型用户的模型
逻辑数据模型
面向数据库的模型
是概念模型到计算机之间的中间层次
比较成熟的包括：
层次模型——树结构——表示数据之间的联系
关系模型——二维表
网状模型——图结构
面向对象模型——对象
物理数据模型
面向计算机物理表示的模型
数据模型三大要素
- 数据结构
- 数据操纵
- 完整性约束
1.06.数据完整性约束
实体完整性
若属性A是基本关系R的主属性，则属性A不能取空值。
参照完整性
参照完整性规则就是定义外码与主码之间的引用规则。参照完整性规则描述如下：
若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码Ks相对应，则对于R中每个元组在F上的值必须为：取空值（F的每个属性值均为空值），或等于S中某个元组的主码值。
用户定义的完整性
针对一些具体关系数据库的约束条件，它反映某一具体应用所涉及的数据必须满足的语义要求。
1.7 联机事务处理（OLTP）和联机分析处理（OLAP）
1.3 Oracle基本术语
1.1 数据字典
它是由一系列拥有数据库元数据信息的数据字典表和用户可以读取的数据字典视图组成，存放Oracle数据库所用的有用的有关信息。
1.2 数据文件
是用于存储数据库数据的文件
表空间是一个或多个数据文件在逻辑上的统一组织，数据文件是表空间在物理上的存在形式。
大小： 字节和数据块，数据块是Oracle数据库中最小的数据组织单位。
1.3 控制文件
是一个很小的二进制文件，他维护着数据库的全局物理结构，用以支持数据库成功的启动和运行
如果数据库的物理结构发生了变化用户应该立即备份控制文件。
1.4 日志文件
也称重做日志文件，重做日志文件用于记录对数据库的所有修改信息，修改信息包括用户对数据的修改，以及管理员对数据库结构的修改。
重做日志文件是保证数据库安全和数据库备份与恢复的文件。
1.5 表空间
组织数据和进行空间分配的逻辑结构，可以将表空间看作是数据库对象的容器。
表空间就是一个或 多个数据文件（物理文件）的集合（逻辑文件），所有的数据对象都被逻辑地存放在指定的表空间中。
一个数据库通常包括SYSTEM、SYSAUX和TEMP三个默认表空间，一个或多个临时表空间，还有一个撤销表空间和几个应用程序专用的表空间。
表空间的类型
-
系统表空间
包括SYSTEM和SYSAUX表空间，系统表空间是所有数据库必须且自动创建的
-
永久表空间
永久表空间用于保存永久数据
-
临时表空间
主要在查询带有排序算法时使用，当用完后就立即释放
-
撤销表空间
-
大文件表空间和小文件表空间
1.6 段
用于存储表空间中某一种特定的具有独立存储结构的对象的所有数据，它是由一个或多个区组成。
- 数据段
- 用于存储表中的所有数据。
- 当用户创建表时，就会在该用户的默认表空间中为该表分配一个与表名相同的数据段。
- 索引段
- 用于存储索引的所有数据
- 临时段
- 用于存储排序操作所产生的的临时数据
- 回滚段
- 用于存储用户数据被修改之前的值
- 每个数据库都将至少拥有一个回滚段
1.7 区
是由物理上连续存放的块构成的。区是Oracle存储分配的最小单位，有一个或多个块组成，一个或多个区将组成段。
1.8 数据块
是最小的数据管理单位，也是执行输入输出操作时的最小单位。
第二章 Oracle在Windows平台上的安装与配置
第三章 SQL语言基础
3.1 SQL概述
01.SQL语言功能
-
数据定义功能
- 由数据定义语言（Data Definition Language，DDL）实现
- 定义数据库的逻辑结构，包括定义基表、视图和索引。
- 基本的DDL包括三类：
- 定义——CREATE
- 修改——ALTER
- 删除——DROP
-
数据查询功能
- 由数据查询语言（Data Query Language，DQL）实现。
- 用来对数据库中的各种数据对象进行查询
- 查询操作——SELECT
-
数据操纵功能
-
通过数据操纵语句（Data Control Language , DCL）
-
用于改变数据库中的数据
-
数据更新包括
- 插入——INSERT
- 删除——DELETE
- 修改——UPDATE
-
-
数据控制功能
- 指数据库的安全性和完整性控制
- 通过数据控制语言（Data Control Language，DCL）实现
- 包括对基表和视图的授权、完整性规则的描述以及事物开始和结束等控制语句。
- 包括
- 授权——GRANT
- 回收——REVOKE
- 提交——COMMIT
- 回滚——ROLLBACK
02.SQL语句编写规则
关键字、对象名和列名不区分大小写
字符值和日期值区分大小写
3.2 数据定义
01.CREATE语句
-
创建表
同MySQL用法
create table 表名( 列名 数据类型 列级完整性约束条件, 列名 数据类型 列级完整性约束条件), -
创建视图
是一个或几个基表（或视图）导出的表，是一个虚表。数据库中只存放视图的定义，而不存放视图对应的数据。
一般格式：
create view 视图名 [(<列名>[,<列名>]...)] as <子查询> [with check option];子查询不能包含order子句和distinct短句。
with check option 表示对视图进行update、insert和delete操作时，要保证更新、插入和删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）
属性列名，全部省略或全部指明
明确所有的列名
- 目标列存在集函数或列表达式
- 多表连接时存在几个同名列作为视图的字段
- 某个列需要重命名
-
创建索引
create [uniqun][cluster][]index 索引名 on 表名( 列名, 列名);uniqun表示次索引的每个索引值不能重复，对应唯一的数据记录
cluster表示要建立的索引是聚簇索引——查询频率最高的列上建立
聚簇索引：将索引和表记录放在一起存储，一个基表上最多只能建立一个聚簇索引。
02.DROP语句
-
删除表
drop table 表名; -
删除视图
drop view 视图名; -
删除索引
drop index 索引名;
03.ALTER语句
修改基表
alter table 表名
[add <新列名> <数据类型> [完整性约束]]
[drop <完整性约束名>]
[modify <列名><数据类型>]
3.3 数据查询
完整语法：
select [all|distinct] top n[percent] with ties select_list
[into [new table name]]
[from {table_name|view_name}]
01.简单查询
select * from employees;
-
使用from子句指定表
from子句指定查询中包含的行和列所在的表
查询方案HR的COUNTRIES表中的所有行数据的SQL语句如下：
select * from HR.COUNTRIES;指定多个表，每个表用逗号隔开
select * from HR.COUNTRIES, HE.DEPARTMENTS; -
使用select指定列
同mysql
-
算术表达式
对于数字数据和日期数据都可以使用算术表达式。在select语句中可以使用的算术运算符包括：加（+），减（-），乘（*），除（/）
-
例子：员工的薪资增加10%
select employee_id, first_name, last_name ,salary*(1+0.1) from employees;
-
-
distinct关键字
删除结果集中重复的行
select distinct department_id from employees;
02.where子句
用于筛选从from子句中返回的值，完成选择操作。
-
条件表达式
-
A=B 相等 TURE
-
A>B 大于 TURE
-
A<B 小于 TURE
-
A!=B或A<>B 不等于 TURE
-
A like B like是匹配运算符
通配符：
- % ： 代表0个、1个或多个任意字符
- _ ：代表一个任意字符
-
not <条件表达式> not运算符对结果取反
-
-
连接运算符
在where子句中使用连接运算符将各个表达式关联起来组成复合判断条件。
and 和 or
同mysql
-
NULL值
用来描述记录中没有定义内容的字段值
判断某个条件的值是，返回值可能是true、false或unknown。
如果查询一个列的值是否等于20 ，而该列的值为NULL，那么说明无法判断该列是否为20 。如果列值为NULL，则对该列进行判断是的值就会为UNKNOWN，它可能等于20 ，也可能不等于20 。
使用"="对NULL值进行查询是无法得到需要的结果的。
IS NULL IS NOT NULL 判断是否为NULL值
03.order by 子句
排序
用法同mysql
默认的排列顺序，升序排列
降序排列
select employee_id, first_name, salary from employees
where salary > 2000
order by salary desc;
对多个列进行排序，只需在order by子句后指定多个列名，先对第一列进行排序，当第一列的值相同时，再对第二列进行比较排序。
04. group by子句
对查询结果集中对记录进行分组，一汇总数据或者为整个分组显示单行的汇总信息。
使用group by子句和统计函数，可以实现对查询结果中每一组数据进行分类统计。
select job_id, avg(salary), sum(salary), count(job_id) from employees
group by job_id ;
使用group by子句时，必须满足下面的条件：
-
在select子句的后面只有两类表达式：统计函数和进行分组的列名。
-
在select子句中的列名必须是进行分组的列，除此之外添加其他的列名都是错误的，但是，group by子句后面的列名可以不出现在select子句中。
-
group by子句中还可以使用运算符rollup和cube， 使用它们后，都将会在查询结果中附件一行汇总信息。
select job_id, avg(salary), sum(salary), max(salary),count(*)
from employees
group by rollup(job_id);
05. HAVING 子句
having子句通常与group by子句一起使用，在完成对分组结果统计后，可以使用having子句对分组结果做进一步的筛选。
-
having子句与组有关
-
where子句与单个的行有关
06. 多表连接查询
-
简单连接
select emploee_id, last_name, department_name from employess, departments;- 条件限定
select emploee_id, last_name, department_name from employess, departments where employees.department_id = departments.department_id and department.department_name = 'Shipping';- 表别名
select em.emploee_id, em.last_name, dep.department_name from employess em, departments dep where em.department_id = dep.department_id and dep.department_name = 'Shipping';如果为表指定了别名，那么语句中所有子句都必须使用别名，而不允许再使用实际的表名。
在select语句的执行顺序中，from子句最先被执行，然后就是where子句，最后才是select子句。
-
JOIN连接
-
内连接
- 关键字 inner join
- on子句 ：on子句指定内连接操作列出与连接条件匹配的数据行。
select em.emploee_id, em.last_name, dep.department_name from employess em inner join departments dep on em.department_id = dep.department_id where em.job_id = 'AD_ASST';- 实现两个以上表的查询
select em.emploee_id, em.last_name, dep.department_name,j.job_title from employess em inner join jobs on em.job_id = jobs.job_id inner join departments dep on em.department_id = dep.department_id where em.job_id = 'IT_PROG'; -
自然连接
Oracle会将第一个表中的那些列与第二个表中具有相同名称的列进行连接。
select em.employee_id, em.first_name, em.last_name, dep.departmentname from employees em natural join departments dep where dep.departmentname = 'Sales'; -
外连接
内连接消除了与另外一个表中的任何行不匹配的行，而外连接扩展了内连接的结果集，除返回所有匹配的行外，还会返回一部分或全不匹配的行。
- 左连接：LEFT OUTER JOIN 或 LEFT JOIN
- 右连接：RIGHT OUTER JOIN 或 RIGHT JOIN
insert into employees(employee_id, last_name, email, hire_date, job_id, department_id) values(1000, 'blaine', 'blaine@hotmail.com', to_date('2009-05-01', yyy-mm-dd), 'IT_PROG', null); select em.employee_id, em.last_name, dep.department_name from employee em left outer join departments dep on em.department_id = dep.department_id where em.job_id = 'IT_PROG';-
完全外连接：FULL OUTER JOIN
返回所有满足连接条件的行
select em.employee_id, em.last_name, department_name from employees em full outer jion departments dep on em.department_id = dep.department_id where dep.location_id = 1700 or em.job_id = 'IT_PROG'; -
自连接
在from子句中两次在指定了同一个表，为了在其他子句中区分，非别为表指定了表别名，这样Oracle就可以将两个表看做是分离的两个数据源，从中获取相应的数据。
-
07.集合操作
将两个或多个SQL 查询结果合并构成符合查询
常用的集合操作符包括：
-
UNION：并运算
结果等同于集合运算中的并运算
select employee_id, last_name from employees where last_name like 'C%' or last_name like 'S%' union select employee_id, last_name from employees where last_name like 'S%' or last_name like 'T%';结果会消除其中重复的行
-
UNION ALL：
包含两个子结果集中重复的行
-
INTERSECT：交运算
UNION基本上是一个OR运算
INTERSE则比较像AND
使用intersect集合操作，在查询结果中共集中保留LAST_NAME以S开头的雇员。
select employee_id, last_name from employees where last_name like 'C%' or last_name like 'S%' intersect select employee_id, last_name from employees where last_name like 'S%' or last_name like'T%'; -
MINUS：差运算
可以找到两个给定的集合之间的差集
将返回LAST_NAME以C开头的那些雇员
select employee_id, last_name from employees where last_name like 'C%' or last_name like 'S%' minus select employee_id, last_name from employees where last_name like 'S%' or last_name like'T%';
在使用集合操作符编写查询时的规则：
- 各select语句指定的列必须在数量上和数据类型上相匹配
- 不允许在构成复合查询的各个查询中规定ORDER BY 子句
- 不允许在BLOB\LONG这样的大数据对象上使用集合操作符
08.子查询
-
IN关键字
使用子查询查看所有部门在某一地区（1700）的雇员信息
select employee_id, last_name, department_id from employees where department_id in ( select department_id from departments where locationg_id = 1700); -
EXISTS关键字
只需要考虑是否满足判断条件，而数据本身并不重要。
只注重子查询是否返回行，如果子查询返回一个或多个行，那么EXISTS便返回TURE , 否则为FALSE。
select employee_id, last_name from employees em where exists ( select * from departments dep where em.department_id = dep.department_id and locationg_id = 1700); -
比较运算符
等于(=)，不等于(<>)，小于(<) ，大于、小于等于、大于等于
将薪资大于本职位平均薪资的雇员信息显示出来。
select employee_id, last_name, job_id, salary from employees where job_id = 'PU_MAN' and salary >=(select avg(salary) from employees where job_id = 'PU_MAN');
04.数据操控
01.insert语句
- insert语句
同MySQL
insert into jobs(job_id, job_title, min_salary, max_salary)
values('IT_TEST','测试员'，3000.00,8000.00)
-
批量insert
用select语句替换values语句
02.UPDATE语句
语法形式
update employees
set salary = salary*15
where job_id = 'IT_PROG';
未使用where子句限定修改的行，则会更新整个表
可以使用select语句的查询结果来实现更新数据。
update employees
set salary = (select avg(salary)
from employees
where job_id = 'IT_PROG')
where employee_id = 104;
03.delete语句
delete from table_name
where employee_id = 107;
04.truncate语句
删除表中所有的记录
还可以使用关键字
reuse storage：表示删除记录后仍然保存记录占用的空间
drop storage：删除记录后立即回收记录占用的空间
delete语句可以使用rollback来恢复数据，而truncate语句不能。
05.数据控制
1.grant语句
SQL用grant语句向用户授予操作权限，grant语句的一般格式为：
grant <权限>[,<权限>]...
[on<对象类型><对象名>]
to<用户>[,<用户>]...
[with grant option]
接受权限的用户可以是一个或多个具体用户也可以是public，即全体用户
如果指定了with grantoption子句，则获得某种权限的用户还可以把这种权限再授予其他的用户。否则，只能使用该权限，不能传播该权限。
- 例子：把查询IT_EMPLOYEES表的权限授给用户User1
grant select
on table IT_EMPLOYEES
to user ;
-
把查询IT_EMPLOYEES表和修改雇员编号的权限授给用户User4，并允许将此权限在授予其他的用户。
grant update(employee_id), select on table IT_EMPLOYEES to User4 with grant option;
2.revoke语句
授予的权限可以由DBA或其他授予者用revoke语句收回。
一般格式：
revoke <权限>
on <对象类型><对象名>
from <用户>
-
把用户User4修改雇员编号的权限收回、
revoke update(employee_id) on table IT_EMPLOYEES FROM User4;收回权限的操作会级联下去
系统之收回直接或间接从删除权限的用户处获得的权限。
06.Oracle常用函数
01 字符类函数
-
ASCII()
select ASCII('a') BIG_A, ASCII('a') SMALL_A FROM dual ; -
CHR()
select chr(97) from dual; -
CONCAT(C1 , C2)
将C2连接到C1后面
select concat('oracle', '11g') name from daul ; -
INITCAP(c1)
将c1中每个单词的第一个字母大写，其他字母小写返回。
select initcap('oracle universal installer') name from dual; -
INSTR(c1,[c2,[,j]])
返回c2在c1中第j次出现的位置，搜索从c1的第i个字符开始。
-
LENGTH(c1)
返回c1的长度
-
LOWER(c1)
将c1全体变为小写
-
LTRIM(c1, c2)
将c1中最左边的字符去掉，是其第一个字符不在c2中
-
REPLACE(c1, c2[,c3])
用c3代替c1中出现的c2后返回。
-
SUBSTR(c1, [,j])
表示从c1的第i位开始返回长度为j的子字符串。
02.数字类函数
03.日期类函数
04.转换类函数
05.聚集类函数
第四章 Oracle PL/SQL语言及编程
4.1 PL/SQL简介
01.PL/SQL的基本结构
PL/SQL的大体结构如下：
DECLARE
--声明一些变量、常量、用户定义的数据类型以及游标等
--这一部分可选，如不需要可以不写
BEGIN
--主程序体，在这里可以加入各种合法语法
EXCEPTION
--异常处理程序，当程序中出现错误时执行这一部分
END;--主程序体结束
02.PL/SQL注释
-
单行注释
由两个连字符开始，一直到行尾（回车符标志着注释的结束）
-
多行注释
有/开头，由*/结尾
03.PL/SQL字符集
- 合法字符集
- 分界符
04.PL/SQL数据类型
-
数字类型
数字类型 NUMBER PLS_INTEGER BINARY_INTEGER 该类型的变量可存储 整数或浮点数 整数 整数 number(p,s) ，其中P是精度(数值中所有有效数字的个数)，S是刻度范围（小数点右边数字位的个数）
-
字符类型
字符类型 VARCHAR2 CHAR LONG NCHAR NVARCHAR2 -
VARCHAR2可以存储变长字符串，声明语法为：
VARCHAR2 (MaxLength); -
CHAR 表示定长字符串
char(Maxlength);
-
-
日期类型
DATE
-
布尔类型
BOOLEAN—主要用于控制程序流程
一个布尔类型变量的值可以是TURE、FALSE或NULL。
-
type定义的数据类型
定义数据类型的语句格式如下：
type <数据类型名> is <数据类型>;在Oracle中允许用户定义两种数据类型，RECORD(记录类型)和TABLE(表类型)。
type teacher_record is RECORD ( TID NUMBER(5) NOT NULL:=0, NAME VARCHAR2(50), TITLE VARCHAR2(50), SEX CHAR(1) );-
定义一个teacher_record类型的记录变量ateacher。
ateacher teacher_record;引用这个记录变量时要指明内部变量，如ateacher.id或ateacher.name
-
%TYPE： 单属性的数据类型
-
%ROWTYPE：整个属性列表的结构
用于声明与表的列相匹配的变量和用户定义数据类型
type teacher_record is record ( TID TEACHERS.TID%TYPE NOT NULL:=0, NAME TEACHERS.NAME%TYPE, TITLE TEACHERS.TITLE%TYPE, SEX TEACHERS.SEX%TYPE);
定义一个与表TEACHERS的结构类型一致的记录变量
teacher_record TEACHERS%ROWTYPE; -
-
05.PL/SQL 变量和常量
-
定义常量
<常量名> constant<数据类型>:=<值>;Pass_Score constant INTEGER:=60; -
定义变量
<变量名><数据类型>[(宽度):=<初始值>];address VARCHAR2(30);
06.PL/SQL语句控制结构
-
选择结构
IF语句
命令格式
if(条件表达式1) then {语句序列1;} [elseif(条件表达式2) then {语句序列2;}] [else {语句序列3;}] end if ;CASE语句
命令格式
case 检测表达式 where 表达式1 then 语句序列1 where 表达式2 then 语句序列2 ... where 表达式n then 语句序列n [else 其他语句序列] END;-
根据学生的考试等级获得对应分数范围
declare v_grade VARCHAR2(20):='及格'; v_score VARCHAR2(50); BEGIN v_score:= CASE V_grade where '不及格' then '成绩<60' where '及格' then '60<= 成绩 <70' where '中等' then '70<= 成绩 <80' where '良好' then '80<= 成绩 <90' where '优秀' then '90<= 成绩 <=100' else '输入有误' end;
-
-
NULL结构
在于程序块中添加NULL检查。
-
循环结构
-
LOOP…EXIT…END语句
control_var:=0; --初始化control_var为0 LOOP --开始循环 IF control_var>5 THEN --如果control_var的值大于5则退出循环 EXIT; END IF; contro_var:=control_var+1; --改变control_var的值 END LOOP; -
LOOP…EXIT WHEN …END语句
control_var:=0; --初始化control_var为0 LOOP --开始循环 EXIT WHEN control_var>5 THEN--如果control_var的值大于5则退出循环 contro_var:=control_var+1; --改变control_var的值 END LOOP; --循环尾 -
WHILE…LOOP…END语句
control_var:=0; --初始化control_var为0 WHILE control_var<=5 LOOP--如果control_var的值小于或等于5则循环 contro_var:=control_var+1; --改变control_var的值 END LOOP; -
FOR…IN…LOOP…END语句
FOR control in 0...5 Loop --control_var从0到5进行循环 NULL; --因为for语句自动给control_var加1，故这里是一个空操作。 END LOOP; -
goto语句
goto label;无条件转向语句。
-
07.PL/SQL表达式
-
字符表达式
唯一的字符运算符—并置运算符"||"
作用：把几个字符串连在一起，如：
‘Hello’||‘World’||'!‘的值等于’Hello World!’
-
布尔表达式
三个布尔运算符：
AND 、OR 和NOT
4.2 PL/SQL游标
SQL是面向集合的
4.2.1 基本原理
在PL/SQL块中执行SELECT、INSERT、UPDATE和DELETE语句时，Oracle会在内存中为其分配上下文区，即一个缓冲区。游标是指向该区的一个指针，或是命名一个工作区，或是一种结构化数据类型。它为应用程序提供了一种具有多行 数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。
4.2.2 显示游标
显示游标的处理包括
- 声明游标
- 打开游标
- 提取游标
- 关闭游标
-
声明游标
cursor <游标名> is select <语句>;游标名是一个PL/SQL标识符，所以它必须在被引用以前声明。
游标声明可以在where子句中共引用PL/SQL变量， 这些变量被认为是联编变量bindVARIABLE，即已经被分配空间并映射到绝对地址的变量。由于可以使用通常的作用域法则，因此这些变量必须在声明游标的位置是可见的。
声明游标举例
declare teacher_id number(5); --定义4个变量来存放TEACHERS表中的内容 teacher_name varchar2(50); teacher_title varchar2(50); teacher_sex char(1); cursor teacher_cur is --定义游标teacher_cur select TID, TNAME, TITLE, SEX from teachers where TID < 117 ; --选出号码小于117的老师在游标定义的SELECT<语句>不包含INTO子句。INTO子句是FETCH(提取游标)的一部分
-
打开游标
open <游标名>打开游标就是执行定义的select语句，执行完毕，查询结果装入内存，游标停在查询结果的首部，注意并不是第一行。当打开一个游标时，会完成以下几件事情：
- 检查联编变量的取值
- 根据联编变量的取值，确定活动集
- 活动集的指针指向第一行。
打开游标举例
declare teacher_id number(5); --定义4个变量来存放TEACHERS表中的内容 teacher_name varchar2(50); teacher_title varchar2(50); teacher_sex char(1); cursor teacher_cur is --定义游标teacher_cur select TID, TNAME, TITLE, SEX from teachers where TID < 117 ; --选出号码小于117的老师 begin open teacher_cur --打开游标一次也可以同时打开多个游标
-
提取游标
fetch <游标名> into <变量列表>； 或 fetch <游标名> into PL/SQL记录;其中：
<游标名>标识了已经被声明的并且别被打开的游标
<变量列表>是已经声明的PL/SQL变量的列表（变量之间用逗号隔开）
PL/SQL记录是已经声明的PL/SQL记录。
在这两种情况下，into子句中的变量的类型都必须与查询的选择列表的类型相兼容，否则将拒绝执行。
declare teacher_id number(5); --定义4个变量来存放TEACHERS表中的内容 teacher_name varchar2(50); teacher_title varchar2(50); teacher_sex char(1); cursor teacher_cur is --定义游标teacher_cur select TID, TNAME, TITLE, SEX from teachers where TID < 117 ; --选出号码小于117的老师 begin open teacher_cur --打开游标 fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher_sex; --将第一行数据放入变量中，游标后移。fetch语句每执行一次，游标向后移动一行，直到结束
-
关闭游标
当所有的活动集都被检索以后，游标就应该被关闭。
declare teacher_id number(5); --定义4个变量来存放TEACHERS表中的内容 teacher_name varchar2(50); teacher_title varchar2(5); teacher_sex char(1); cursor teacher_cur is --定义游标teacher_cur select TID, TNAME, TITLE, SEX from teachers where TID < 117 ; --选出号码小于117的老师 begin open teacher_cur --打开游标 fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher_sex; --将第一行数据放入变量中，游标后移。 loop exit when not teacher_cur%FOUND; --如果游标到位则结束 if teacher_sex = 'M' then --将性别为男的行放入男老师表MALE_TEACHERS中 insert into MALL_TEACHERS(TID, TNAME, TITLE) values(teeacher_id, teacher_name, teacher_title); else insert into FEMALE_TEACHERS(TID, TNAME, TITLE) values(teachers_id, teacher_name, teacher_title); end if; fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher_sex; end loop; close teacher_cur; --关闭游标 end;
4.2.3 隐式游标
在PL/SQL程序中用select语句进行操作，则隐式地使用了游标
对每个隐式游标来说，必须要有一个into子句，因此使用隐式游标的select语句必须只选中一行数据或只产生一行数据。
4.2.4 游标属性
四种：
-
%ISOPEN
-
%FOUND
-
%NOTFOUND
-
%ROWCOUNT
它们描述与游标操作相关的DML语句的执行情况。游标属性只能在PL/SQL的流程控制语句，而不能用在SQL语句内。
-
是否找到游标(%FOUND)
该属性表示当前游标是否指向有效一行，是—TURE，否则为FALSE。
检查此属性可以判断是否结束游标使用。
-
%FOUND示例
open teacher_cur; --打开游标 fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher Sex; --将第一行数据放入变量中，游标后移 loop exit when not teacher_cur%FOUND; --使用了%FOUND属性 end loop; -
SQL%FOUND示例
delete from TEACHERS where TID=teacher_id; --teacher_id为一个有值变量 if SQL%FOUND then --如果删除成功则写入SUCCESS表中该行号码 insert into SUCCESS values(TID); else --不成功则写入FALL次奥中共该行号码。 insert into FALL values(TID); end if;
-
-
是否没找到游标(%NOTFOUND)
该属性与%FOUND属性相类似，但其值正好相反
-
游标行数
该属性记录了游标抽取过得记录行数
loop fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher_sex; exit when teacher_cur%ROWCOUNT = 10; --只抽取10条记录 ... end loop;还可以用for语句控制游标的循环，系统隐含地定义了一个数据类型为%ROWCOUNT的记录，作为循环计数器，并将隐式地打开和关闭游标。
- FOR语句中%ROWCOUNT示例。
for teacher_record in teacher_cur loop --teacher_record为记录名，它隐含地打开游标teacher_cur insert into temp TEACHERS(TID, TNAME, TITLE, SEX) values(teacher_record.TID, teacher_record.TNAME, teacher_record.TITLE, teacher_record.SEX); end loop; -
游标是否打开(%ISOPEN)
先检查，再操作
if teacher_cur%ISOPEN then fetch teacher_cur into teacher_id, teacher_name, teacher_title, teacher_sex; else open teacher_cur; end if; -
参数化游标
动态使用
ACCEPT my_tid prompt 'Please input the tid:' declare --定义游标时带上参数cursor_id cursor teacher_cur(cursor_id number) is select TNAME, TITLE, SEX from TEACHERS where TID = cursor_id; --使用参数 begin open teacher_cur(my_tid); --带上实参量 loop fetch teacher_cur into teacher_name, teacher_title, teacher_sex; exit when teacher_cur%NOTFOUND; ... end loop; close teacher_cur; end;
4.2.5 游标变量
游标变量被用于处理多行的查询结果集
-
声明游标变量
PL/SQL中的引用类型通过下述的语法进行声明
REF type;type是已经被定义的类型。REF关键字指明新的类型必须是一个指向经过定义的类型的指针。因此，游标可以使用的类型就是REF CURSOR。
定义一个游标变量类型的完整语法如下：
type <类型名> is REF cursor return <返回类型>;<类型名>是新的引用类型的名字，
<返回类型>是一个记录类型，它指明了最终由游标变量返回的选择列表的类型。
游标变量的返回类型必须是一个记录类型
declare type t_StudentRef is ref cursor --定义使用%ROWTYPE return students%ROWTYPE; type t_AbstractstudentRecord IS record( --定义新的记录类型 sname STUDENTS.sname%TYPE, sex STUDENTS.sex%TYPE); v_AbstractStudentsRecord t_AbstractStudentsRecord; type t_AbstractStudentsRef is REF cursor --使用记录类型的游标变量 return t_AbstractStudentsRecord; type t_NamesRef2 is REF cursor --另一类型定义 return v_AbstractStudentsRecord%TYPE; v_StudentCV t_StudentsRef; --声明上述类型的游标变量 v_AbstractStudentCV t_ABstractStudnetsRef;游标变量受限，它的返回类型只能是特定类型。而PL/SQL语言中还有一种非受限游标变量，它在声明的时候没有return子句。一个非受限游标变量可以为任何查询打开
-
定义游标变量
declare --定义非受限游标变量 type t_FlexibleRef IS REF CURSOR; --游标变量 V_CURSORVar t_FlexibleRef;
-
-
打开游标变量
如果要将一个游标变量与一个特定的selec语句相关联，需要使用open for语句
open <游标变量> for <select语句>；如果游标变量是受限的，则select语句的返回类型必须与游标所限的记录类型匹配。
游标变量的打开示例
declare type t_StudentsRef IS ref cursor --定义使用%ROWTYPE return STUDNETS%ROWTYPE; V_StudentSCV t_StudentRef; begin open v_studentSCV for select * from STUDENTS； end; -
关闭游标变量
使用close语句。
4.3 过程
4.3.1 创建过程
create [or replace] procedure <过程名>
(
<参数1>, [方式1]<数据类型1>，
<参数2>， [方式2]<数据类型2>
...)
is/as
PL/SQL过程体;
-
过程创建示例
set serveroutput on format wrapped create or replace procedure count_num( in_sex in teachers.sex%type) --输入参数 as out_num number; begin if in_sex = 'M' then select count(sex) into out_num from teachers where sex = 'M'; dbms_output.put_line('NUMBER of Male Teachers:'||out_num); else select count(sex) into out_num from teachers where sex = 'F'; dbms_output.put_line('NUMBER of Female Teachers:'||out_num); end if; end count_num;
4.3.2 调用过程
调用过程的命令是EXECUTE
execute count_num('M');
execute count_num('F');
4.3.2 删除过程
drop procedure count_num;
重新定义
create or replace procedure count_num;
4.3.4 过程的参数类型及传递
-
in参数类型
输入类型参数，表示这个参数值输入给过程，供过程使用
create or replace procedure double --完成将一个数加倍 ( in_num in number, out_num out number ) as begin out_num:= in_num*2 end double; -
out参数类型
输出类型的参数，表示这个参数在这个过程中被赋值，可以传给过程体以外的部分或环境。
-
in out参数类型
既向过程体传值，在过程体中也被赋值而传向过程体外。
create or replace procedure double --完成将一个数加倍 ( in_out_num in out number ) as begin out_num:= in_num*2 end double;
4.4 函数
4.4.1 创建函数
语法表达式
create [or replace] function <>
(<参数1>, [方式1]<数据类型1>,
<参数2>, [方式2]<数据类型2>
...)
return <表达式>
is|as
PL/SQL程序体 --其中必须要有一个return子句
return在声明部分需要定义一个返回参数的类型，而在函数体必须有一个return语句。
create or replace function count_num
(in_sex in teachers.sex%type)
return number
as
out_num number;
begin
if in_sex = 'M' then
select count(sex) into out_num
from teachers
where sex = 'M'
else
select count(sex) into out_num
from teachers
where sex = 'F';
end if;
return(out_num);
end count_num;
4.4.2 调用函数
调用函数时可以用全局变量接受其返回值
vareable man_num number
vareable woman_num number
execute man_num:=count_num('m')
execute woman_num:=count_num('f')
程序中调用函数示例
declare
m_num number;
f_num number;
begin
m_num:=count_num('M');
f_num:=count_num('F');
end;
4.4.3 删除函数
drop function count_num;
重新定义
create or replace function count_num;
4.5 程序包
-
说明部分
- 包与应用程序之间的接口，只是过程、函数、游标等名称或首部
-
包体部分
- 是这些过程、函数、游标等的具体体现
4.5.2 创建包
格式:
-
包说明部分
create package <包名> is 变量、常量及数据类型定义; 游标定义头部； 函数、过程的定义和参数列表以及返回类型; end <包名>; -
包体部分
是包的说明部分中的游标、函数及过程的具体定义
create package body <包名> as 游标、函数、过程的具体定义; end<包名>;
创建一个包说明部分
create package my_package
is
man_num number; --定义了两个全局变量
woman_num number;
cursor teacher_cur; --定义了一个游标
create function F_count_num(in_sex in teachers.sex%type)
return number; --定义了一个函数
create procedure P_count_num
(in_sex in teachers.sex%type, out_num outnumber); --定义了一个过程
end my package;
对应包体的定义
SQL> create package body my_package
2 as
3 cursor teacher_cur is --游标具体定义
4 select TID, TNAME, TITLE, SEX
5 from teachers
6 where TID < 117;
7 function F_count_num --函数具体定义
8 (in_sex in teachers.sex%type)
9 return number
10 as
11 out_num number;
12 begin
13 if in_sex = 'm' then
14 select count(sex) into out_num
15 from teachers
16 where sex = 'm';
17 else
18 select count(sex) into out_num
19 from teachers
20 where sex = 'f';
21 end if;
22 return(out_num);
23 procedure P_count_num --过程具体定义
24 (in_sex in teachers.sex%type, out_num out number)
25 as
26 begin
27 if in_sex = 'm' then
28 select count(sex) into out_num
29 from teachers
30 where sex = 'm';
31 else
32 select count(sex) into out_num
33 from teachers
34 where sex = 'f';
35 end if;
36 end P_count_num;
37 end my_package; --包体定义结束
38 /
4.5.3 调用包
包的调用方式为：
包名.变量名(常量名)
包名.游标名
包名.函数名(过程名)
variable man_num nuumber
execute man_num := my_package.F_count_num('M')
4.5.4 删除包
drop package my_package;
重新定义
create or replace package my_package;
4.6 触发器
4.6.1 触发器的基本原理
触发器是当某些事件发生时，由Oracle自动执行，触发器的执行对用户来说是透明的。
-
触发器类型
包括三种
- DML触发器：对表或视图执行DML操作时触发
- INSTEAD OF触发器：只定义在视图上，用来替换实际的操作语句。
- 系统触发器：对数据库系统进行操作（如DDL语句、启动或关闭数据库等系统事件）时触发。
-
相关概念
（1）触发事件
引起触发器被触发的事件。
（2）触发条件
触发条件是由where子句指定的一个逻辑表达式。
（3）触发对象
包括表、视图、模式、数据库。
（4）触发操作
触发器所要执行的PL/SQL程序，即执行部分。 （5）触发时机
触发时机指定触发器的触发时间
- before ：表示在执行DML操作之前触发，以便防止 某些错误操作发生或实现某些业务规则； - after：则表示在DML操作之后触发，以便记录该操作或做某些事后处理。（6）条件谓词
当在触发器中包含了多个触发事件（insert、update、delete）的组合时，为了分别针对不同的事件进行不同的处理，需要使用Oracle提供的如下条件谓词。
- inserting：当触发事件是insert时，取值为true，否则为false。
- updating[(column_1, column_2, … column_n)]：当触发事件是update时，如果修改了column_x列，则取值为true，否则为false。
（7）触发子类型
- 行触发：即对每一行操作都要触发，一般进行SQL语句操作时都应是行触发。 - 语句触发：只对这种操作触发一次，对整个表做安全检查（即防止非法操作）是时才用语句触发。
4.6.2 创建触发器
create or replace trigger <触发器名>
触发条件
触发体
示例
create trigger my_trigger --定义一个触发器my_trigger
before insert or update of TID, TNAME on TEACHERS
for each row
where(new.TNAME = 'David') --这一部分是触发条件
declare --下面这一部分是触发体
teacher_id teachers.TID%type;
insert_exist_teacher exception;
begin
select TID into teacher_id
from teachers
where TNAME = new.TNAME;
raise insert_exist_teacher;
exception --处理也可用在这里
where insert_exist_teacher then
insert into error(TID,ERR)
values(teacher_id, 'the teacher already exists!');
end my trigger;
4.6.3 执行触发器
要求：
- 对一张表上的触发器最好加以限制，否则会因为触发器过多而加重负载，影响性能。
- 最好将一张表的触发事件编写在一个触发体中，这也可以大大改善性能
把与表teachers有关的所有触发事件都放在触发器my_trigger1中。
create trigger my_trigger1
after insert or update or delete on teachers
from each row;
declare
info char(10);
begin
if inserting then --如果进行插入操作
info := 'insert';
elsif updating then --如果进行修改操作
info := 'update'
else --如果进行删除操作
info := 'delete'；
end if;
insert into sql_info values(info); --记录这次操作信息
end my_trigger;
4.6.4 删除触发器
drop trigger my_trigger;
重新定义
create or replace trigger my_trigger;
第五章 熟悉SQL*Plus—Oracle数据库环境
5.2.2 保存命令
-
SAVE命令
格式：save file_name
使用save命令可以直接将缓冲区的SQL语句保存到当前路径或指定路径下指定的文件中，扩展名是.SQL，说明是一个SQL查询文件。
-
INPUT命令
可以将input和save命令结合使用，使用input命令将SQL*Plus命令输入到缓冲区中，然后可以使用save命令保存到文件中
-
EDIT命令
可以直接使用edit命令创建文件
5.2.3 加入注释
-
使用REMARK命令
使用remake命令在一个命令文件的一行加上注释，
remake creatreport.sql -
使用
/* only female*/ where sex = 'temale' -
使用–
--清除屏幕 clear screen
5.2.4 运行命令
-
命令行方式
在命令后面加分号(;)作为终止符来运行SQL命令的方式。
-
SQL缓冲区方式
SQL*Plus提供了RUN命令和斜杆(/)命令来以缓冲区方式执行SQL命令。其中，run命令的格式为：
R[un]- run命令：列出并执行当前存储在缓冲区中的SQL命令或PL/SQL块，它可以显示缓冲区的命令并返回查询结果，并使缓冲区中的最后一行成为当前行。
- 斜杠(/)命令：类似于run命令，它执行存储在缓冲区的SQL命令或PL/SQL块，但是不显示缓冲区内容，也不会是缓冲区的最后一行成为当前行。
-
命令文件方式
以命令文件方式运行一个SQL命令或SQL*Plus命令或PL/SQL块，有两种方式：state命令和@命令。其中，state命令的格式为：
statq file_name[.sql][arg1 arg2]
5.2.5 编写交互命令
-
定义用户变量
define newstu = zhangsan; -
在命令中代替值
替代变量是在用户变量前加入一个或两个&符号的变量。当SQL*Plus晕倒一个替代变量是，执行命令，好像它包含替代变量的值一样。
变量sortcol包含值tea_id，变量mytable包含值tea_view，则
select &sortcol, salary from &mytable where salary > 15000;等价于
select tea_id, salary from tea_view where salary > 15000; -
使用start命令提供值
在编写SQL*Plus命令时，可以使用start命令将命令文件的参数值传递给替代变量这时需要将&符号值域命令文件数字面前，替换替代变量。当每次运行该命令文件时，start使用第一个值替换&1，使用第二个值替换&2，以此类推。
select * from tea_view where tea_id = '&1' and salary = '&2'执行如下的start命令
start myfile pu_cleark 2000 --使用pu_cleark替换&1，用2000替换&2。
5.3 设置SQL*Plus环境
5.3.1 show命令
显示当前SQL*Plus环境中的系统变量，还可以显示错误信息、初始化参数、当前用户等信息。该命令的格式：
sho[w] option
5.3.2 set命令
set命令用于设置系统变量的值
set system_variablel valuel [system_variable value2] ...
-
arraysize
用于设置从数据库中一次提取的行数，默认值为15。
show arraysize arraysize 15 -
autocommit
用于在执行DML语句时设置是否自动提交，默认值为off。当设置为on并设置n时，表示成功执行n条SQL语句或PL/SQL块后自动提交。
SQL> show arraysize arraysize 15 SQL> show autocommit autocommit OFF SQL> set autocommit 5 SQL> show autocommit AUTOCOMMIT ON for every 5 DML statements -
colsep
用于设置在选定列之间的分隔符，默认为空格。
-
echo
在用start命令执行一个脚本文件时，echo命令用于控制是否显示脚本文件中正在执行的SQL语句。默认值为off。
第六章 Oracle的基本操作
6.1 Oracle的启动与关闭
6.1.1 启动Oracle数据库
- 每个启动的数据库至少对应有一个例程，例程是Oracle用来管理数据库的一个实体。
- 在服务器中，例程是由一组逻辑内存结构和一系列后台服务进程组成的。
- 启动Oracle数据库需执行三个操作步骤
- 启动例程
- 装载数据库
- 打开数据库
1. 一般启动
（1）启动例程
当启动例程时，这些内存结构和服务进程得到分配、初始化和启动。但是，此时的例程还没有与一个确定的数据库相联系，或者说数据库是否存在对例程的启动并没有影响，即没有装载数据库。
启动例程包括执行如下几个任务：
- 读取初始化参数文件，默认时读取spfile服务器参数文件，或读取由pfile选项指定的文件参数文本。
- 根据该初始化参数文件中有关SCA区、PGA区的参数及其设置值，在内存中共分配相应的空间。
- 根据该初始参数文件中有关后台进程的参数及其设置值，启动相应的后台进程。
- 打开跟踪文件、预警文件。
如果使用startup nomount命令启动例程（但不打开控制文件，也不装载数据库）。通常，使用数据库的这种状态来创建一个新的数据库，或创建一个新的控制文件。
（2）装载数据库
装载数据库是，例程将打开数据库的控制文件，根据初始化参数control_files的设置，找到控制文件，并从中获取数据库物理文件（即数据文件、重做日志文件）的位置和名称等关于数据库物理结构的信息，为下一步打开数据库做好准备。
在装载阶段，例程并不会打开数据库的物理文件，所以数据库仍然处于关闭状态，仅数据库管理权可以通过部分命令修改数据库，用户无法与数据库建立连接或会话，因此无法使用数据库。如果控制文件损坏或是不存在，例程将无法加载数据库。
在执行下列任务时，需要数据库处于装载状态，但无须打开数据库：
- 重新命名、增加、删除数据文件和重做日志文件。
- 执行数据库的完全恢复。
- 改变数据库的归档模式。
使用startup mount命令启动例程并装载数据库。
（3）打开数据库
在启动数据库的过程中，文件的的使用顺序是参数文件、控制文件、数据文件和重做日志文件，只有这些文件都被正常读取和使用后，数据库才完全启动，用户才能使用数据库。
2. Windows服务窗口启动
3. SQL*Plus启动
数据库有3种启动模式，可以分别代表数据库的3个步骤。当数据库管理员使用startup命令时，可以指定不同的选项来决定数据库的启动推进到那个启动模式。在进入某个模式后，可以使用alterdatabase命令来将数据库提升到更高的启动模式，但不能使数据库降低到前面的启动模式。
| 启动模式 | 说明 | SQL*Plus中提示信息 |
|---|---|---|
| NOMOUNT | 启动例程，不装载数据库 | Oracle例程已经启动 |
| MOUNT | 启动例程、装载数据库、不打开数据库 | Oracle例程已经启动，数据库装载完毕 |
| OPEN | 启动例程、装载数据库并打开数据库 | Oracle例程已经启动，数据库装载完毕，数据库已经打开 |
-
NOMOUNT模式：启动例程，但不装载数据库，即只完成启动步骤的第一步，
提示：Oracle例程已经启动
-
MOUNT模式：启动例程、装载数据库，但不打开数据库，即只完成启动步骤的第一步和第二步，
提示：Oracle例程已经启动，数据库装载完毕
-
OPEN模式：启动例程、装载数据库、打开数据库，即完成全部的3个启动步骤
提示：Oracle例程已经启动，数据库装载完毕，数据库已经打开。
-
启动数据库的语法如下：
startup [NOMOUNT|MOUNT|OPEN|FORCE] [RESTRICT][PFILE = 'pfile_name'];-
NOMOUNT选项
startup nomount结果没有权限，待解决
如果要执行下列维护工作，就必须用NOMOUNT选项启动数据库
- 运行一个创建数据库的脚本
- 重建控制文件
-
MOUNT选项
Oracle读取控制文件，并从中获取数据库名称、数据文件的位置和名称等关于数据库物理结构的信息，为下一步打开数据库做好准备。
如果要执行下列维护工作，就必须用MOUNT选项启动数据库
- 重新命名、增加、删除数据库和重做日志文件
- 执行数据库的完全恢复
- 改变数据库的归档模式。
-
OPEN选项
-
FORCE选项
force选项首先异常关闭数据库，然后重新启动它，而不需要事先用SHUTDOWN语句关闭数据库。
-
RESTRICT选项
- 用restrict选项启动数据库时，会将数据库启动到open模式，但此时只有拥有restricted session权限的用户才能访问数据库。
- 如果需要在数据库处于open模式下维护任务，又要保证此时其他用户不能在数据库上建立连接和执行任务，则需要使用restrict选项来打开数据库，以便完成如下任务：
- 执行数据库数据的导出和导入操作
- 执行数据装载操作（用SQL*Loader）
- 暂时阻止一般的用户使用数据
- 进行数据库迁移或升级
-
RFILE选项
-
4. OEM控制台启动
6.1.2 关闭Oracle数据库
当执行数据库的定期冷备份、数据库软件的升级时，常需要关闭数据库。
-
关闭数据库
关闭数据库时，Oracle将重做日志高速缓存中的内容写入重做日志文件，并将数据库高速缓存中被改动过的数据写入数据文件，在数据文件中执行一个检查点，即记录下数据库关闭的时间，然后再关闭所有的数据文件和重做日志文件。这时数据库的控制文件仍然处于打开状态，但是由于数据库已经处于关闭状态，所以用户将无法访问数据库。
-
卸载数据库
-
终止数据库
1. 在OEM中关闭
2. 关闭服务
3. SQL*Plus关闭
SQL*Plus关闭数据库的语法如下
shutdown [normal|transactional|immediate|abort];
normal(正常)选项
-
normal(正常)选项
对关闭数据库的时间没有限制
shutdown和shutdown normal作用相同。
使用带有normal选项的shutdown语句将以正常方式关闭数据库。
步骤：
-
阻止任何用户建立新的连接
-
等待当前所有正在连接的用户主动断开连接。正在连接的用户能够继续他们当前的工作，甚至能够提交新的事物。
-
一旦所有的用户都断开连接，才能进行关闭 、卸载数据库，并终止例程。
-
-
TRANSACTINONAL(事务处理)选项
Oracle将等待所有当前未提交的事务完成后再关闭数据库。
Oracle将执行如下操作：
- 阻止任何用户建立新的连接，同时阻止当前连接的用户开始任何新的事物
- 等待所有当前未提交的活动事物提交完毕，然后立即断开用户的连接
- 一旦所有的用户都断开连接，才进行关闭、卸载数据库，并终止例程。
-
IMMEDIATE（立即）选项
在尽可能短的时间内关闭数据库
在如下几种情况需要IMMEDIATE选项来关闭数据库
- 即将发生电力中断
- 即将启动自动数据备份操作
- 数据库本身或耨个数据库应用程序发生异常，并且这时无法通知用户主动断开连接，或用户根本无法执行断开操作。
用IMMEDIATE选项关闭数据库时，Oracle将执行如下操作：
- 阻止任何用户建立新的连接，同时阻止当前连接的用户开始任何新的事物
- 任何的当前未提交的事物均被回退
- Oracle不再等待用户主动断开连接，而是直接关闭、卸载数据库，并终止例程。
-
以上三种都不行，用ABORT选项来关闭Oracles数据库
以下几种情况，使用ABORT选项
- 数据库本身或某个数据库应用程序发生异常，并且使用其它选项均无效时。
- 出现紧急情况，需要立即关闭数据库（停电）
- 启动数据库例程的过程中产生错误
Oracle将执行如下操作：
- 阻止任何用户建立新的连接，同时组织当前连接的用户开始任何新的事物
- 立即结束当前正在执行的SQL语句
- 任何未提交的事物均不被回退
- 立即断开所有用户的连接，关闭、卸载数据库，并终止例程。
6.2 表的创建与改进
6.2.1 表的基本概念
表是数据库存储数据的基本单位，在进行数据库设计时，需要先构造E-R图（实体联系图），然后再将E-R图转变为数据库中的表。
从用户的角度来看，表中存储的数据的逻辑结构是一张二维表，即表由行、列两部分组成。通常称表中的一行为一条记录，表中的一列为属性列。一条记录描述一个实体，一个属性描述实体的一个属性。
表一般指的是一个关系表，也可以生成对象表及临时表。其中，对象表是通过用户定义的数据类型生成的，临时表用于储存专用某个事物或者会话的临时数据。
6.2.2 表结构设计
-
表与列的命名
表和列的名称要求
- 长度必须在1~30个字节之间
- 必须以一个字母开头
- 能够包含字母、数值、下划线符号_、英镑符号#和美元符号￥。
- 不能使用保留字，如CHAR或是NUMBER。
- 若名称被围在双引号""中，唯一的要求是名字的长度在1~30个字符之间，并且不含有嵌入的双引号
- 每个列名称在单个表内必须是唯一的。
-
列的类型
- 字符数据类型
- CHAR[()[BYTE|CHAR]]
- NCHAR[()]
- VARCHAR2([BYTE|CHAR])
- NVA
- 字符数据类型
6.2.2 表结构设计
-
表与列的命名
表和列的名称要求
- 长度必须在1~30个字节之间
- 必须以一个字母开头
- 能够包含字母、数值、下划线符号_、英镑符号#和美元符号￥。
- 不能使用保留字，如CHAR或是NUMBER。
- 若名称被围在双引号""中，唯一的要求是名字的长度在1~30个字符之间，并且不含有嵌入的双引号
- 每个列名称在单个表内必须是唯一的。
-
列的类型
- 字符数据类型
- CHAR[()[BYTE|CHAR]]
- NCHAR[()]
- VARCHAR2([BYTE|CHAR])
- NVARCHAR2()
- 大对象数据类型
- CLOB
- NCLOB
- BLOB
- BFILE
- 数字数据类型
- NUMBER[([,])]
- 日期和时间数据类型
- DATE
- TIMESTAMP[()]
- TIMESTAMP[()] WITH TIME ZONE
- TIMESTAMP[()] WITH LOCAL TIME ZONE
- INTERVAL DAY[()] TO SECOND
- 二进制数据类型
- ROW()
- LONG ROW
- 行数据类型
- ROWID
- UROWID
- 字符数据类型
-
列的约束
Oracle通过为表的列定义各种约束条件来保证表中数据的完整性
在 Oracle中可以建立的约束条件包括NOT NULL、UNIQUE、CHECK、PRIMARY KEY、FOREIGN KEY。
-
NOT NULL约束
NOT NULL即非空约束，主要用于防止NULL值进图到指定的列。
NOT NULL约束有以下特点：
- 定义了NOT NULL约束的列中不能包含NULL值或无值 。在默认情况下，Oracle允许在任何列中有NULL值或无值。如果再某个列上定义了NOT NULL约束，则插入数据是就必须为该列提供数据。
- 只能在单列上定义NOT NULL约束条件
- 在同一个表中可以在多个列上分别定义NOT NULL约束。
-
UNIQUE约束
唯一约束，该约束用于保证在该表中指定的各列的组合中没有重复的值。其主要特点如下：
- 定义了UNIQE约束的列不能包含重复的值，但如果再一个列上仅定义了UNIQUE约束，而没有定义NOT NULL约束，则该列可以包含多个NULL值或无值。
- 可以为单个列定义UNIQE约束，也可以为多个列的组合定义UNIQE约束。因此，UNIQE约束既可以在列级定义，也可以在表级定义。
- Oracle会自动为具有UNIQUE约束的列建立一个唯一索引。如果这个列已经具有唯一或非唯一索引，Oracle将使用已有的索引
- 对同一个列，可以同时定义UNIQUE约束和NOT NULL 约束
- 在定义UNIQUE约束时可以为他的索引指定存储位置和存储参数。
-
CHECK 约束
即检查约束，其用于检查在约束只能指定的条件是否的得到了满足。
-
PRIMARY KEY约束
PRIMARY KEY约束即主键约束，其用来唯一地标识出表的每一行，并且防止出现NULL值。一个表只能有一个主键约束。PRIMARY KEY约束具有如下的特点。
- 定义了PRIMARY KEY约束的列（或列组合）不能包含重复值，并且不能包含NULL值
- Oracle会自动为具有PRIMARY KEY 约束的列建立一个唯一索引（unique index）和一个NOT NULL约束
- 同一个表只能够定义一个PRIMARY KEY 约束的列（或组合）
- 可以在一个列上定义PRIMARY KEY 约束，也可以在多个列的组合上定义PRIMARY KEY 约束。因此，PRIMARY KEY 约束及可以在列级定义，也可以在表级定义
-
FOREIGN KEY 约束
FOREIGN KEY 约束即外键约束，通过使用外键，保证表与表之间的参照完整性。在参照表上定义的外键需要参照主表的主键。
-
6.2.3 表的创建
-
用CREATE TABLE命令创建表
基本语法格式是：
create [[global] temporpry|table|schema.]table_name (column1 datatype1 [default expq][column1 constaint], column2 datatype2 [default exp2][column1 constraint] [table constraint]) [on commit {delete|preserve} rows] [organizition {help|index|externai...}] [partition by...(...)] [tablespace tablespace_name] [logging | nologging] [compress|nocompress];其中：
- column datatype1 为列指定数据类型
- default exq1 为指定默认值
- column constraint为列定义完整性约束(constraint)
- [table constraint]为表定义完整性约束(constraint)
- [organizition {help|index|external…}]为表的类型，如关系型（标准、按堆组织）、临时型、索引型、外部型或对象型。
- [partition by…(…)]为分区及子分区信息
- [tablespace tablespace_name]指示用于存储表或索引的表空间
- [logging | nologging] 是否保留重做日志
- [commpress|nocompress] 知识是否压缩。
6.2.4 修改表结构
普通用户只能对自己方案中的表进行更改
-
用ALTER TABLE命令修改表结构
-
增加列
alter table [schema.] table_name add(column defintion1, column defition2);新添加的列总是位于表的结尾。column definition 部分包括列名、列的数据类型以及将具有的任何默认值
-
更改列
alter table [schema.] table_name modify(column_name1 new_attributes1, column_name1 new_attributes2...); -
直接删除列
alter table [schema.] table_name drop (colume_name1, column_name2...) [cascade constraints]如果删除的列是一个多列约束的组成部分，那么就必须指定cascade constraint选项，这样才会删除相关的约定。
-
将列标记为UNUSED状态
为了避免在数据库使用高峰期间由于执行删除列的操作而占用过多系统资源，可以暂时通过ALTERTABLE SET UNUSED 语句将要删除的列设置为UNUSED状态。
该语句的语法格式为
altertable [schema.] table_name set unused(column_name1, column_name2) [cascade constraints];被标记为UNUSED状态的列与被删除的列之间是没有区别的，都无法通过数据字典或在查询中看到。另外、甚至可以为表添加与UNUSED状态具有相同名称的新列。
在数据字典视图USER_UNUSED_COL_TABS, ALL_UNUSED_COLTABS和DBA_UNUSED_COL_TABS中可以查看到数据库有哪些表哪几列被标记为UNUSED状态。
-
6.3 索引
索引是将创建列的键值和对应记录的物理记录号（ROWID）排序后存储起来，需要占用额外的存储空间来存放。
6.3.2 创建索引
-
用SQL*Plus创建索引
创建索引的语法格式为：
create [unique]|[bitmap] index [schema.] index_name on [schema.] table_name([column1[ASC|DESC], column2[ASC|DESC], ...]|[express]) [tablespace tablespace_name] [pctfree n1] [storage(initial n2)] [compress na]|[nocompress] [logging]|[nologging] [online] [compute statistics] [reverse]|[nosort];其中：
- pctfree选项用于指定为将来的insert操作所预留的百分比空间。假定表已经包含了大量数据u，那么在建立索引时应该仔细规划pctfree的值，以便为以后的insert操作预留空间。
- tablespace选项用于指定索引段所在的表空间
- 如果不指定bitmap选项，则默认创建的是B树索引。
6.3.3 删除索引
若出现如下几种情况之一将有必要删除 相应的索引
- 索引的创建不合理或不必要，应删除该索引，已释放其占用的空间
- 通过一段时间的监控，发现几乎没有查询，或者只有极少数查询会使用到该索引
- 由于该索引中共包含损坏的数据块，或者包含过多的存储碎片，需要首先删除该索引，然后再重建该索引。
- 如果移动了表的数据，导致索引无效，此时需要删除并重建该索引
- 当使用SQL*Loader 给单个表装载数据是，系统也会同时给该表的索引增加数据，为了加快数据装载速度，应在装载之前删除所有索引，然后再数据装载完毕之后重新创建各个索引。
如果索引是使用create index语句创建的，可以使用drop index语句删除索引；如果索引是在定义约束时由Oracle自动创建的，则可以通过禁用约束（disable）或删除约束的方式来删除对应的索引。
-
在SQL*Plus中删除索引
drop index index_name;
6.4 视图
视图是由select子查询语句定义的一个逻辑表，只有定义而无数据，因此它是一个“虚表”。
使用视图的优点：提供各种数据表现形式、提供某些数据的安全性、隐藏数据的复杂性、简化查询语句、执行特殊查询、保存复杂查询等。
6.4.1 视图的概念
使用视图的作用主要表现在以下几个方面：
- 提供面向用户的数据表现形式
- 提供面向用户的安全性保证
- 隐藏数据的逻辑复杂性
- 简化用户权限的管理
- 重构数据库的灵活性
6.4.2 创建视图
-
语法
创建视图时，视图的名称和列名必须符合表的命名规则，但又建议使用另一种命名习惯，以便区分表盒视图。
create [or replace] [force] view [schema.] view_name [(column1, column2...)] as select...from...where... [with check option][constraint constraint_name] [with read only];- force ：强制创建视图，不考虑基表是否存在，也不考虑是否具有使用基表的权限。
- schema：指出在哪个方案中创建视图
- with：使用视图时，检查涉及的数据是否能通过select子查询的where条件，否则不允许操作并返回错误提示。
- constraint constraint_name：当使用with check option 选项时，用于指定该视图的该约束的名称。如果没有提供一个约束名称，Oracle就会生成一个以SYS C 开头的约束名称，后面是一个唯一的字符串
- with read only：创建的视图只能用于查询数据，而不能用于更改数据。该子句不能与order by子句同时存在。
注意：同所有的子查询一样，定义视图的查询不能包含for update子句。
在Oracle中，提供强制创建视图的功能是为了使基表的创建和修改与视图的创建和修改之间没有必然的依赖性，便于同步工作，提高工作效率，并且可以据需进行目前的工作。
-
创建视图的步骤
- 编写select子查询语句
- 测试select子查询语句
- 检查查询结果的正确性
- 使用该select子查询语句创建视图，并注意命名方面与选项方面的规定。
复杂视图是指视图的select子查询中包含函数、表达式或分组数据的视图。使用复杂视图的主要目的是为了简化查询操作。
6.4.4 删除视图
drop view view_name ;
6.5 数据操纵与数据查询
6.5.1 复制原表插入记录
在Oracle中，可以使用create table table_name as 语句来创建一个表并且向其中插入记录。
-
创建表High_salary，该表对应了employees表中月薪超过5000元的雇员信息
create table High_Salary as select * from employees where salary > 5000.00;
6.5.2 使用视图
-
用视图进行插入
使用视图进行插入时，插入的数据需要满足对应基表相关约束。
-
创建一个视图v_department，再使用insert语句向视图中插入数据。
create view v_department as select department_id, department_name, manager_id from departments where location_id = 1700; insert into v_department value(999, 'bacmp', 205);
-
-
用视图进行修改
-
修改视图v_department中的记录
update v_department set manager_id = 206 where department_id = 110;修改视图中的数据同样需要通过对应基表相关约束的检查。
-
-
用视图进行删除
select * from v_department where manager_id is not null; delete from v_department where department_id = 300; select * from v_department where manager_id is not null;
6.5.3 使用PL/SQL语言
declare
emp_id number(6);
dep_id number(4);
fname varchar2(20);
Iname varchar2(25);
c_email varchar2(25);
phone varchar2(20);
job varchar2(10);
n_salary number(6,2);
m_id number(6);
it_id number(4);
select department_id into it_id
from departments
where department_name = 'IT';
cursor cur1 is select department_id, employee_id, first_name, last_name, email, phone_number, job_id, salary, manager_id
from employees;
begin
open cur1;
fetch cur1 into dep_id, emp_id, fname, lname, c_email, phone, job, n_salary, m_id;
loop
exit when cur1%NOTFOUND;
if dep_id = it_id then
insert into IT_EMPLOYEES
values(emp_id, fname, lname, c_email, phone, job, n_salary, m_id);
else
end if;
fetch cur1 into dep_id, emp_id, fname， lname, c_email, phone, job, n_salary, m_id;
end loop;
close cur1;
end;
6.5.4 数据查询
-
一般条件查询
select employee_id, first_name, salary from employees where salary > 5000.00; -
组合条件查询
- 完成对表employees中IT部门的雇员信息查询，IT部门的部门编号从departments表中获得
select employee_id, first_name, salary from employees emp, departments dep where department_name = 'IT' and emp.department_id = dep.department_id;-
完成对表employees中共IT部门月薪超过5000元的雇员信息查询
select employee_id, first_name, salary from employees emp, departments dep where department_name = 'IT' and emp.department_id = dep.department_id and salary > 5000.00;
-
用group进行分组查询
-
查询部门名称、员工数量、总薪资和平均月薪，语句如下
select dep.department_name, count(emp.employee_id)dep_count, sum(emp.salary)total_salary, avg(salary)average_salary from employees emp, departments dep where emp.department_id = dep.department_id group by dep.department_name; -
查询平局工资超过5000元的部门的部门名称、员工数量、总薪资和平均薪资。
由于在条件比较中不能使用组函数，首先创建视图dep_salary获得部门编号和对应的员工数量、总薪资和平均月薪信息，语句如下：
create view dep_salary as select department_id, count(employee_id)dep_salary from employees emp group by department_id;再由视图dep_salary和表departments做组合查询，获得所需信息，对应语句
select dep.department_name, average_salary from dep_salary, departments dep where dep_salary.department_id =dep.department_id and average_salary > 5000.00;
-
第七章 Oracle数据库管理操作
7.1 学会使用视图
7.1.1 增加安全性
在需要授予用户只对表的一部分访问权限的情况下，选择通过视图进行权限设置对于系统的安全非常有效。
在许多情况下，需要更精确地授予用户的权限，而不仅仅是一个表的授权。例如：
- 不应将员工表中存储的个人信息或敏感信息的访问权授予那些需要访问该表其他部分用户
- 用户可能希望授予销售代表更新表的权限，包括更新其销售电话的说明，但此权限要仅限于其自己的电话。
通过视图用户只能查看和修改他们所能看到的数据，其他数据库或表既不可见也不可以访问。
使用视图增加安全性的具体操作过程如下：
-
建立视图，将不允许用户访问的数据隐藏起来
-
将对视图权限通过使用grant语句授予相关用户。
-
示例
-
销售经理需要访问数据库中有关该部门员工的信息。但是，该经理没有理由访问有关其他部门员工的信息。
该示例描述了在人力资源HR示例数据库中，如何成为销售经理创建用户ID，如何创建可提供其所需信息的视图，以及如何给此销售经理用户ID授予相应的权限。
-
使用grant语句创建新用户ID。在使用具有系统管理员（DBA）权限的用户ID登录时，其代码如下：
conn sys/zzuli as sysdba -
激活人力资源示例数据库账户，连接到HR示例数据库
Alter user hr identified by hr account unlock conn hr/hr -
为销售经理创建用户ID，用户名为salesmanager，密码为sales，并将connect权限授予销售经理
grant connect to salesmanager identified by sales -
定义一个只查看员工编号和姓名的视图，将数据库中员工的工资等星系隐藏起来
create view emp_sale as select employee_id, first_name, last_name from hr.employees; -
授予salesmanager查看该视图的权限
grant select on emp_sale to salesmanager -
让销售经理以用户salesmanager登录数据后，销售经理可以查看员工的编号和姓名，但是不允许销售经理查看员工的工资信息，这样就一定程度上保证了数据的安全性。
conn saleamanager/sales select * from sys.emp_sale; select salary from hr.employees;
-
-
7.1.2 隐藏数据的复杂
-
示例
现有机场数据库，拥有PilotSkills表和Hangar表，其中表PilotSkills描述了飞行员和他们能够驾驶的飞机信息，表Hangar描述了停在飞机棚中的飞机信息。现要求查询能够驾驶飞机棚中共每一架飞机的飞行员的姓名。
提示：该查询的实现代码非常复杂，需要用到除法运算，他的思想是用除数除去被除数表，产生商或结果表。将PilotSkills表用飞机棚中共的飞机去除，就可以得到结果。
-
首先创建两个表，用PilotSkills表来描述分星宇以及飞行员可以驾驶的飞机信息，用Hangar表来描述飞机鹏中的飞机信息。
--创建PilotSkills表 create table PilotSkills ( pilot char(15) not null, plane char(15) not null, primary key(pilot, plane)); --创建Hangar表 create table Hangar (plane char(15) primary key);为实现“找出能够驾驶飞机棚中每一架飞机的飞行员的名字”，可以创建一个视图
create view QualifiedPilots(pilot) as select distinct pilot from PilotSkills PS1 where not exists(select * from Hangar where not exists(select * from PilotSkills PS2 where (PS1.pilot = PS2.pilot) and (PS2.plane = Hangar.plane)));另一种方法
create view QualifiedPilots(pilot) as select PS1.pilot from PilotSkills PS1, Hangar H1 where PS1.plane = H1.plane group by PS1.pilot having count(PS1.plane) = (select count(plane) from Hangar);用以下代码实现“找出能够驾驶飞机棚中每一架飞机的飞行员姓名”
select pilot from Qualifiedpilots；
-
7.1.3 实现命名简洁性和易读性
如果感觉表名或是列名比较复杂，那么就可以将其定义为一个视图，然后为其中的表和列重新定义一个简单易用的名字，这样操作视图就可以了。
-
示例
可以通过视图来将英文名称改为所习惯的中文名称
建立一个名为雇员基本信息的视图，将基表employees中共对应的英文名称分别改为雇员编号、雇员姓名、电子邮件、电话、雇用日期。
create view 雇员基本信息(雇员编号, 雇员姓名, 电子邮件, 电话, 雇用日期) as select employee_id, first_name+last_name, email, phone_number, hire_date from employees;以后要查询员工的信息
select 雇员编号, 雇员姓名, 电子邮件 from 雇员基本信息
7.1.4 实现更改灵活性
7.2 实现记录的唯一性
为了避免输入重复的数据信息，用户可以通过设置字段的记录唯一性来定义其具体内容。实现记录唯一性的方法主要有以下3种：
- 用键实现
- 创建唯一索引
- 用序列生成唯一索引
7.2.1 用键实现
键能够唯一区分数据表中每个记录的属性或者属性组合，因此使用键可以保证记录的唯一性。
当为表指定primary key约束时，Oracle通过为主键列创建唯一索引强制数据的唯一性。当在查询中使用主键时，该索引还可用来对数据进行访问。如果PRIMARY KEY 约束定义在不止一列上，则一列中的值可以重复，但PRIMARY KEY 约束定义中的所有列的组合值必须唯一。
当插入新纪录或对现有记录进行修改时，系统会自动对定义了键的列实施实体完整性检查，一旦发现插入的键值或修改之后的键值出现重复，则不允许提交所做的修改，以保证记录的唯一性。
-
示例
在学生信息数据库中创建一个名为stu的学生信息表，该表由三个属性组成，分别为学号、姓名、年龄。
create table stu (sno varchar(10), sname varchar(10), sage int);首先插入一条记录(“001”, “tom”, 18)，再插入一条记录(“001”, “jerry”, 20)，这两条具有相同的学号
insert into stu values('001', 'tom', 18); insert into stu values('001', 'tom' ,20);SQL> create table stu 2 (sno varchar(10), 3 sname varchar(10), 4 sage int); Table created. SQL> insert into stu 2 values('001', 'tom', 18); 1 row created. SQL> insert into stu 2 values('001', 'tom' ,20); 1 row created. SQL> select * from stu; SNO SNAME SAGE ---------- ---------- ---------- 001 tom 18 001 tom 20 SQL> delete from stu; 2 rows deleted. SQL> alter table stu 2 add constraint c1 primary key(sno); Table altered. SQL> insert into stu 2 values('001', 'jerry', 20); 1 row created. SQL> insert into stu 2 values('001', 'tom', 18); insert into stu * ERROR at line 1: ORA-00001: unique constraint (SYSTEM.C1) violated系统不允许插入重复记录。
7.2.2 创建唯一索引
唯一索引是不允许任何两行具有相同索引值的索引。当现有数据中存在重复的键值时，大多数数据库不允许将新建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。
创建唯一索引可以确保任何生成重复键值的尝试都会失败
创建爱你UNIQUE约束和创建与约束无关事物唯一索引并没有明显的区别。
创建唯一索引的语法格式如下：
create unique index name on table (column[,...]);
目前，只有B树索引可以声明是唯一的。如果索引声明为唯一的，那么就不允许出现多个索引值相同的行。
-
示例
使用唯一索引来完成
SQL> alter table stu 2 drop constraint c1; Table altered. SQL> create unique index indl on stu(sno); Index created. SQL> insert into stu 2 values('001', 'jerry', 20); insert into stu * ERROR at line 1: ORA-00001: unique constraint (SYSTEM.INDL) violated
7.2.3 使用序列实现
序列是一个可以为表中的行自动生成序列号的数据库对象，利用它可生成唯一的整数，产生一组等间隔的数值（类型为数字），主要用于生成唯一、连续的序号。一个序列的值是由特殊的Oracle程序自动生成，因此序列避免了在应用层实现序列而引起的性能瓶颈。
序列的主要用途是生成表的主键值。
创建序列需要CREATE SEQUENCN系统权限，其格式语法如下：
create sequence 序列名
[increment by n]
[start with n]
[{maxvalue/minvalue n|nomaxvalue}]
[{cycle|nocycle}]
[{cache n|nocache}];
其中，各选项的意义为：
-
increment by ：用于定义序列的步长，若省略，则默认为1；如出现负值，则代表序列的值是按照此步长递减的。
-
start with：用于定义序列的初始值，默认值为1.
-
maxvalue：用于定义序列生成器能产生的最大值。
nomaxvalue：为默认选项，代表没有最大值定义，这是对于递增序列，系统能够产生的最大值是10^27；对于 递减序列，最大值是-1。
-
minvalue：用于定义序列生成器能产生的最小值。
nominvalue：为默认选项，代表没有最小值定义，这是对于递减序列，系统能够产生的最大值是-10^26；对于 递增序列，最大值是1。
-
cycle和nocycle表示当序列生成器的值达到限制值后是否循环。cycle代表循环，nocycle代表不循环。若循环，则当递增序列达到最大值时，循环到最小值；当递减序列达到最小值时，循环到最大值。如果不循环，达到限制值后，继续产生新值就会发生错误。
-
cache：用于定义存放序列的内存块的大小，默认值为20。nocache表示不对序列进行内存缓冲。对序列进行内存缓冲，可以改善序列的性能。
删除序列的语法格式为：
drop sequence 序列名;
下面将对序列的创建、删除、使用和查看操作进行详细介绍。
SQL> --创建序列
SQL> create sequence abc
increment by 1
start with 10
maxvalue 999999
nocycle nocache;
Sequence created.
SQL> --删除序列
SQL> drop sequence abc;
Sequence dropped.
-
使用序列
使用CURRVAL和NEXTVAL来引用序列的值
-
调用NEXTVAL将生成序列中的下一个序列号，调用时要指出序列名，其格式如下：
序列名.NEXTVAL
-
CURRVAL用于产生序列的当前值，无论调用多少次后不会产生序列的下一个值。调用CURRVAL方法同上，要指出序列名：
序列名.CURRVAL
如果序列还没有用过调用NEXTVAL产生过下一个值，先引用CURRVAL没有意义。
SQL> --使用序列 SQL> --产生序列的第一个值： SQL> select abc.nextval from dual; NEXTVAL ---------- 10 SQL> --产生序列的下一个值 SQL> select abc.nextval from dual; NEXTVAL ---------- 11 SQL> --产生序列的当前值 SQL> select abc.currval from dual; CURRVAL ---------- 11 -
-
查看序列
通过数据字典UESR_OBJECTS可以查看用户拥有的序列。通过数据字典UESR_SEQUENCES可以查看序列的设置。
SQL> select sequence_name, min_value, increment_by, last_number 2 from user_sequences; SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER ------------------------------ ---------- ------------ ----------- ABC 1 1 12 LOGMNR_EVOLVE_SEQ$ 1 1 1 LOGMNR_SEQ$ 1 1 1 LOGMNR_UIDS$ 1 1 100 MVIEW$_ADVSEQ_GENERIC 1 1 1 MVIEW$_ADVSEQ_ID 1 1 1 REPCAT$_EXCEPTIONS_S 1 1 1 REPCAT$_FLAVORS_S -2.147E+09 1 1 REPCAT$_FLAVOR_NAME_S 1 1 1 REPCAT$_REFRESH_TEMPLATES_S 1 1 1 REPCAT$_REPPROP_KEY 1 1 1 SEQUENCE_NAME MIN_VALUE INCREMENT_BY LAST_NUMBER ------------------------------ ---------- ------------ ----------- REPCAT$_RUNTIME_PARMS_S 1 1 1 REPCAT$_TEMPLATE_OBJECTS_S 1 1 1 REPCAT$_TEMPLATE_PARMS_S 1 1 1 REPCAT$_TEMPLATE_REFGROUPS_S 1 1 1 REPCAT$_TEMPLATE_SITES_S 1 1 1 REPCAT$_TEMP_OUTPUT_S 1 1 1 REPCAT$_USER_AUTHORIZATIONS_S 1 1 1 REPCAT$_USER_PARM_VALUES_S 1 1 1 REPCAT_LOG_SEQUENCE 1 1 1 TEMPLATE$_TARGETS_S 1 1 1 21 rows selected.
7.3 实现数据的完整性
-
域完整性
域完整性是对数据表中字段属性的约束，包括字段的值域、字段的类型及字段的有效规则等约束，是由确定关系结构时所定义的字段的属性决定的
-
实体完整性
实体完整性即指关系中的主属性值不能为NULL且不能有相同的值。实体完整性是对关系中的记录唯一性，也就是主键的约束。
-
参照完整性
参照完整性即指关系中的外键必须是另一个关系的主键有效值，或是NULL。参照完整性是对关系数据库中建立关联关系的数据表间数据参照引用的约束，也就是对外键的约束。
7.3.1 域完整性
域完整性指列的值域的完整性，保证表中数据列取值的合理性。如数据类型、格式、值域范围、是否允许空值等。域完整性限制了某些属性中出现的值，把属性限制在一个有限的集合中。
在Oracle中域完整性主要通过如下3中约束来实现：
- NOT NULL(非空)约束
- UNIQUE(唯一)约束
- CHECH约束
-
NOT NULL(非空约束)
SQL> create table student 2 (sno char(7), 3 sname char(10) not null, 4 ssex char(2), 5 sage int, 6 sdept char(20)); Table created. SQL> --在修改表时删除约束 SQL> alter table student 2 modify sname null; Table altered. SQL> --在修改表时添加约束 SQL> alter table student 2 modify sname not null; Table altered. -
UNIQUE(唯一)约束
可使用UNQIUE约束确保在非主键列中不输入重复值。对于UNIQUE约束中的列，表中不允许有两行包含相同的非空值。
SQL> --在创建表时定义约束 SQL> create table student 2 (sno char(7), 3 sname char(10) unique, 4 ssex char(2), 5 sage int, 6 sdept char(20)); --修改表时添加约束‘ alter table student add constraint stu_uk unique(sname); --修改表时删除约束 alter table student drop constraint stu_uk;修改表时删除约束没有成功。
-
CHECH（检查）约束
SQL> --创建表时定义约束 SQL> create table student 2 ( 3 sno char(7), 4 sname char(10), 5 ssex char(2), 6 sage int check(sage > 0 and sage <=100), 7 sdept char(20)); Table created.SQL> --创建表时定义约束 SQL> create table student ( sno char(7), sname char(10), ssex char(2), sage int sdept char(20)); Table created.SQL> create table student 2 (sno char(7), 3 sname char(10), 4 ssex char(2), 5 sage int, 6 sdept char(20)); Table created. SQL> --修改表时添加约束 SQL> alter table student 2 add constraint stu_ck check(sage > 0 and sage <= 100); Table altered. SQL> --使用alter table语句删除check约束。 SQL> alter table student 2 drop constraint stu_ck; Table altered.
7.3.2 实体完整性
实体完整性规则是针对现实世界的一个实体集，而现实世界中的实体是可区分的。实体完整性可描述为：若属性a是基本关系R的主属性，则属性a不能取空值。
该规则的目的是利用关系模式中的主键或主属性来区分现实世界中的实体集中的实体，所以不能取空值。
该规则的目的是利用关系模式中的主键或主属性来区分现实世界中的实体集中的实体，所以不能取空值。
当用户对基表插入一条记录或者对主键列进行更新操作的时候，关系数据库管理系统将自动进入检察。包括：
- 检查主键值是否唯一，如果不唯一则拒绝插入或者修改
- 检查主键的各个属性是否为空，如果有一个为空，则拒绝插入或者修改。
实体完整性约束是通过定义PRIMARY KEY 约束来实现的。
定义为primary key约束的列被称为“主键列”。
-
在创建表时定义约束
SQL> --创建表时定义约束 SQL> create table student 2 (sno char(7) primary key, 3 sname char(10), 4 ssex char(2), 5 sage int, 6 sdept char(20)); Table created. -
在修改表时添加约束
SQL> --在修改表时添加约束 SQL> create table student 2 (sno char(7) , 3 sname char(10), 4 ssex char(2), 5 sage int, 6 sdept char(20)); Table created. SQL> alter table student 2 add constraint stu_pk primary key(sno); Table altered. SQL> alter table student 2 drop constraint stu_pk; Table altered.
7.3.3 引用完整性
引用完整性也称为参数完整性，它定义了外键与主键之间的引用规则
引用完整性规则的内容：如果属性（或属性组）F是关系R的外键，它与关系S主键K相对性，则对于关系R中每个元组在属性（或属性组）F上的值必须取空值，或者等于S中某个元组的主键的值。
引用完整性是通过外键foreign key 约束来实现的。定义为foreign key约束的列成为“外键列”，被foreign key约束引用的列称为“引用列”。
包含外键的表称为子表，也称引用表，包含引用列的表称为父表，也称为被引用表，通过使用公共列在表之间建立一种父子关系。
-
示例
SQL> alter table student 2 add constraint stu_pk primary key(sno); Table altered. --首先创建sc表，并将sno设置为外键 SQL> create table sc 2 (sno char(7), 3 cno char(10), 4 grade int, 5 foreign key(sno) references student(sno)); --外键的定义 Table created. SQL> insert into sc 2 values('007','c01',90); insert into sc * ERROR at line 1: ORA-02291: integrity constraint (SYSTEM.SYS_C009662) violated - parent key not found
在定义外键primary key约束时，还可以 通过关键字on指定引用行为的类型。
当尝试删除被引用表中的一条记录时，通过引用行为可以确定如何处理外检表中的外键列。引用类型包含如下几种：
-
如果在定义外键PRIMARY KEY约束时使用了CASCADE关键字，那么当被引用表中的被引用列的数据被删除时，引用表中对应的外键数据也将被删除。
-
如果在定义外键primary key约束时使用了set null关键字，那么当被引用表中的引用列的数据被删除时，引用表中对应的外键数据江北设置为null，要使这个关键字起作用，外键列必须支持null值
-
如果再定义外键primary key约束时使用了农action关键字，那么删除被引用表中的被引用列的数据将违反外键约束，该操作也会被禁止执行，这也是外键的默认引用类型。
-
示例
以下示例将演示外键的级联删除：
--1)首先创建一个新的被引用表dep，并为其添加主键约束。 create table dep as select * from hr.departments where department_id = 50; alter table dep add primary key(department_id); --然后创建一个新的引用表null_emp create table null_emp as select * from hr.employee where employee_id is null; --2)使用ON DELETE CASCADE关键字添加null_emp表的外键约束 alter constraint null_emp add constraint nullemp_fk foreign key(department_id) references dep on delete cascade; --3)向null_emp表添加数据 insert into null_emp select * from employees where department_id = 50; --4)在指定行为类型为 ON DELETE CASCADE后，再删除被引用表dep中编号为50的行，会导致null_emp表中所有的记录同时也被删除。 delete dep where department_id = 50; select count(*) from null_emp; --与其它约束相同，如果想要删除外键约束，可以使用如下的alter table语句形式 alter table null_emp drop constraint nullemp_fk;
7.3.4 存储过程检查
-
在人力资源示例数据库HR中，部门信息表departments由四个属性组成，分别为department_id、department_name、manager_id和location_id。为了保证插入数据的完整性，也即部门编号department_id不能重复以及部门编号和部门名称department_name不能为空，我们可以先创建一个名为p_insert_dept的存储过程。
在使用函数DBMS_output时，一定要先执行set serveroutput on，以保证函数可以有正确的输出。
(dep_id IN departments.department_id%TYPE, dept_name IN departments.department_name%TYPE, mgr_id IN departments.manager_id%TYPE, loc_id IN departments.location_id%TYPE) IS ept_nullerror EXCEPTION; PRAGMA EXCEPTION_INIT(ept_nullerror, -1400); ept_nolocid EXCEPTION; PRAGMA EXCEPTION_INIT(ept_nolocid, -2291); BEGIN INSERT INTO departments (department_id, department_name, manager_id, location_id) VALUES(dept_id, dept_name, mgr_id,loc_id); DBMS_OUTPUT.PUT_LINE('成功添加部门'||dep_id); EXCEPTION WHEN DUP_VAL_ON_INDX THEN RAISE_APPLICATION_ERROR(-20000, '部门编号不能重复'); WHEN ept_nullerror THEN RAISE_APPLICATION_ERROR(-20001, '部门编号及部门不能为空'); WHEN ept_nolocid THEN RAISE_APPLICATION_ERROR(-20002, '没有该地址'); END p_insert_dept;
7.3.5 使用触发器
- 触发器是一种特殊的存储过程，该过程在插入、修改和删除等操作事前或事后由DBS自动执行。经常用于实现逻辑上相关的数据表之间的数据完整性和一致性，例如可用于强制引用完整性，以便在多个表中添加、更新或删除行时，保留在这些表之间所定义的关系。
7.4 避免更改引起的大量改动
为了避免直接依赖基表的问题，可采用以下几种方法：
- 使用视图为表名和列名起别名，在应用过程中可以借助视图的名字来代替基表名和列名，当表名和列名改变时，只需改变相应视图的定义即可。
- ‘类似于视图定义，只不过提供了一种更直接更广泛的方法来为各种对象定义别名，其中也包括视图对象
- 在程序中用定义游标的方法防止直接依赖表。当表名改变时，只需改变游标定义即可
7.4.1 使用视图
视图可以在以下几个方面使程序与数据独立：
-
如果应用建立在数据表上，当数据库表发生变化时，可以在表上建立视图，通过视图屏蔽表的变化，从而应用程序可以不动。
-
如果应用建立在数据库表上，当应用发生改变时，可以在表上建立视图，通过视图屏蔽应用的变化，从而是数据库表不动
-
如果应用建立在视图上，当数据库发生改变时，可以在表上修改视图，通过视图屏蔽表的变化，从而应用程序不动。
-
如果应用建立在视图上，当应用发生变化时，可以在表上修改视图，通过视图屏蔽应用的变化，从而数据库可以不动。
-
示例
在学生信息数据库中，有一个学生信息表student，其结构如下：
Student(Sno, Sname, Ssex, Sage, Sdept)假设一个应用程序需要用到 sno、same、ssex和sage，而且该程序是基于student表的，如果student表结构发生变化，例如数据库逻辑结构发生变化，"垂直"地分为两个基表：
SX(Sno, Sname, Sage) SY(Sno, Ssex, Sdept)则这个时候，应用程序就需要随之该表。
如果我们应用程序是基于视图的。如：
create view v_student(学号、姓名、性别、年龄) as select Sno, Sname, Ssex, Sage from Student在这种基表student发生变化的情况下，我们只需要将视图进行如下修改，而不需要对源程序进行修改，这样就避免了因为基表发生改变而导致的大量修改。代码如下：
create view v_student(学号, 姓名, 性别, 年龄) as select SX.Sno, SX.Sname, SY.Ssex, SX.Sage, SY.Sdept from SX, SY where SX.Sno = SY.Sno;
7.4.2 使用同义词
-
同义词也能实现操作不直接依赖于基表
-
同义词不但可以使用在表的命名中，同样也可以应用在视图、序列 、存储过程和函数以及包中，因此它的应用方位更广泛。
-
使用同义词的不便之处是它不能对列起别名。、
-
创建同义词的语法格式如下：
create [public] synonym 同义词 for 对象
其中：
- public：公共同义词，所有客户都可以引用，若省略此关键字不写，则默认是private同义词，即私有同义词，它只能为某一用户使用。
- 同义词：为对象起的别名，在以后使用对象时可以用此名来代替原对象名。
- 对象：某一特定对象名，它可以是基表、视图、序列、存储函数、存储包和其他同义词，指定对象时可以指定所属用户，中间"."分开。
-
下面将对同义词的相关操作进行介绍
-
创建私有同义词
为student表创建同义词stu
create synonym stu for student;此处用的是默认private同义词，只能为当前用户使用
-
创建公共同义词
create public synonym stu for student; -
使用同义词
insert into stu values('100', '黎明', '男', 25, 'phy');在以后使用过程中，若基表的名字变了，只需修改同义词的定义即可。但同义词创建不支持replace命令，因此必须将其删除在重新创建。
drop synonym person; create synonym stu for student_infor; -
删除同义词
若一个对象（如表）被删除了，则同时也要将相应的同义词删除，因为此时再引用同义词将产生错误，另外也为了清理数据字典。
drop [public] synonym;其中，public在删除公共同义词的情况下使用，若删除某一个用户的同义词，则必须加上用户名。
7.4.3 使用游标
游标相当于定义了一个查询，在以后应用中可能用这个游标的查询结果
当表名改变时，在存储过程和函数中只需要改变定义在这个表上的游标即可，后面对游标的引用不用变，从而避免了直接依赖表的操作。
-
在程序中使用游标
如下代码实现将男女学生分别登记在Sman和Swoman表中的功能，它用到了一个游标cur_stu。可以看出，在程序体中，虽然引用了表student的内容，但却没有引用student表名，而只是在游标定义时引用了一次。
declare stu_no char(10); stu_name char(20); stu_sex char(10); cursor cur_stu is select sno, sname, ssex from student where sage < 20; begin open cur_stu; fetch cur_stu into stu_no, stu_name, stu_sex; loop exit when cur_stu%NOTFOUND if stu_sex = '男' then insert into Sman values(stu_no, stu_name); else insert into Swoman values(stu_no, stu_name); end if ; fetch cur_stu into stu_no, stu_name, stu_sex; end loop; close cur_stu; end; -
修改游标定义以适应变化
当persons表名改为persons_information时，只需将游标定义处的表名修改一下即可，而程序将完成相同的功能。
扫一扫
235
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
