一. Oracle数据库的安装和配置

(二) 数据库的管理

Oracle 对象关系

1. 用户对象

(1) 系统默认

① sys  超级管理员

② system 管理员

③ scott账户

(2) 自定义用户

create user 用户名 identified by 密码

(3) sqlplus的登录

① sqlplus   

② sqlplus   用户名/密码     

③ sqlplus  /nolog  

conn   用户名/密码   

disconn   断开链接

exit 退出sqlplus的环境

1. 权限

(1) 权限分配

Grant 系统权限 to用户

Grant 对象权限  on 对象名  to用户

(2) 权限分类

①  系统权限

②  对象权限

(3) 撤回权限

Revoke 系统权限 from用户

revoke对象权限  on 对象名  from用户

 

 

1. 角色

(1) 自定义角色

Create role 名字

(2) 给角色分配权限

Grant 系统权限 to角色名

Grant 对象权限  on 对象名  to角色名

(3) 将角色分配给用户

Grant  角色名 ，角色名，。。to角色名

(4) 系统角色

角色是权限的集合，部分常用角色：一般情况下

1） 数据库管理员分别授予CONNECT，RESOURCE，DBA

2） 数据库开发人员授予CONNECT，RESOURSE

3） 数据库的导入导出，授予EXP_PULL_DATABASE，IMP_FULL_DATABASE

角色名	包含权限
CONNECT	ALTER SESSION	修改会话
	CREATE CLUSTER	创建簇
	CREATE DATABASE LINK	创建数据库连接
	CREATE SEQUENCE	创建序列
	CREATE SESSION	创建会话
	CREATE SYNONYM	创建同义词
	CREATE TABLE	创建表
	CREATE  VIEW	创建视图
RESOURCE	CREATE CLUSTER	创建簇
	CREATE INDEXTYPE	创建索引类型
	CREATE OPERATOR	定义一个新的操作符
	CREATE PROCEDURE	创建存储过程
	CREATE SEQUENCE	创建序列
	CREATE TABLE	创建表
	CREATE TRIGGER	创建触发器
	CREATE TYPE	创建类型
DBA	所有权限，不受限制
EXP_PULL_DATABASE	SELECT ANY TABLE	查询任何表
	BACKUP ANY TABLE	备份任何表
	EXECUTE ANY PROCEDURE	执行任何存储过程
	EXECUTE ANY TYPE	执行任何类型
	ADMINISTER RESOURCE MANAGER	资源管理
IMP_FULL_DATABASE	EXECUTE_CATALOG_ROLE	执行目录角色
	SELECT_CATALOG_ROLE	查询目录角色

 

查询用户与权限，相应的数据字典如下：

数据字典名称	说明
DBA_USERS	数据库用户基本信息表
DBA_SYS_PRIVS	已授予用户或角色的系统权限
DBA_TAB_PRIVS	数据库对象上的所有权限
USER_SYS_PRIVS	登录用户可以查看自己的系统权限
ROLE_SYS_PRIVS	登录用户查看自己的角色
ALL_TABLES	用户自己可以查询的基表信息
USER_TAB_PRIVS	用户自己将那些基表权限授予哪些用户
ALL_TAB_PRIVS	哪些用户给自己授权

练习

1. 启动oracle服务 2. 使用scott登录，查看emp表 3. 切换到system用户 4. 创建用户isoft,密码i1234 5. 测试是否能用isoft登录？ 6. 赋予isoft用户create session权限，再使用isoft登录 7. 测试使用isoft创建一个表 create table bookinfo(bid number(3),bname varchar2(20),price number(5,2)); 8. 赋予isoft用户适当权限使其能创建bookinfo表 9. 撤销isoft的create session权限，测试能否登录isoft 10.删除用户isoft

 

(三) 数据库的连接、创建、删除、选择等操作

二．Oracle的相关概念及体系结构概述

(一) 基本概念

1. 完整性约束

数据的完整性约束条件是一组完整性规则的集合。完整性规则是给定的数据模型中数据及其联系所具有的制约和依存规则，这些规则用来限定基于数据模型的数据库状态及状态的变化，以保证数据库中数据的正确性、有效性和相容性。

1) 域完整性约束

域完整性是保证数据库字段取值的合理性。域完整性规则要求关系中的属性值应是域中的值。另外，一个属性能否为空值（NULL），也是域完整性约束的主要内容。

技术点：非空、默认值、唯一，check

2) 实体完整性约束

实体完整性是指关系的主关键字不能重复也不能取空值。

技术点：主键

3) 参照完整性约束

参照完整性是定义建立关系之间联系的主关键字与外部关键字引用的约束条件。

技术点：外键

表现：1-1   1-M   M-1   M-N

4) 用户定义完整性约束  

                  技术点：触发器

实体完整性和参照完整性适用于任何关系型数据库系统，它主要是针对关系的主关键字和外部关键字取值必须有效而做出的约束。

1. 关系（Relation）：对应通常所说的二维表。

2. 元组（Tuple）：表中的一行即为一个元组。

3. 属性（Attribute）：表中的一列即为一个属性。

4. 候选关键字（Candidate Key）：如果关系中的某一属性组的值能唯一地标识一个元组，则称该属性组为候选关键字。

5. 主关键字（Primary Key）：如果一个关系中有多个候选关键字，则选定一个为主关键字。

6. 域（Domain）：属性的取值范围。

7. 分量：元组中的一个属性值。

8. 数据库的三范式

1） 第一范式：每个属性值都是不可再分的最小数据单位

数据组的每个属性只可以包含一个值

关系中的每个数组必须包含相同数量的值

关系中的每个数组一定不能相同

2） 第二范式：数据库中的每个记录必须可被唯一地区分

3)  第三范式：一个关系表中不包含已在其他表中已包含的非主关键字信息

1. 需求分析的步骤

1．需求信息的收集

（1）需求信息的收集应得到的主要材料

（2）需求信息的收集过程中常用的调查方法

2．需求信息的分析

（1）确定系统边界

（2）业务流程分析

3．需求信息的评审

(二) 体系结构

1. 逻辑存储结构

1） 数据块（Data Block）：数据块是Oracle逻辑存储结构中最小的逻辑单位，也是执行数据库输入输出操作的最小存储单位。通过v$parameter数据字典来查询数据块的大小

如：SELECT name,VALUE FROM v$parameter WHERE NAME='db_block_size'

2） 数据区（Extent）：数据区是由一组连续的Oracle数据块所构成的Oracle存储结构，一个或多个数据块组成一个数据区，一个或多个数据区再组成一个段（Segment）。数据区是Oracle存储分配的最小单位。

3） 段（Segment）:段是由一个或多个数据区构成的，它不是存储空间的分配单位，而是一个独立的逻辑存储结构，用于存储表、索引或簇等占用空间的数据对象

4） 表空间（TableSpace）：Oracle使用表空间将向相关的逻辑结构（如段，数据区等）组合在一起，表空间是数据库的最大的逻辑划分区域，通常用来存储数据表、索引、回滚段等数据对象，任何数据对象在创建时都必须被指定存储在某个表空间中。

1. 物理存储结构

1） 数据文件：数据文件用于保存用户应用程序数据和Oracle系统内部数据的文件。

2） 控制文件：二进制文件，记录了数据库的物理结构，其中主要包含数据库名，数据文件与日志文件的名字和位置、数据库建立日期等信息。

3） 日志文件：主要功能是记录对数据所做的修改。

4） 服务器参数文件：二级制文件，用来记录Oracle数据库的基本参数信息。

5） 密码文件，警告文件和跟踪文件

 

常用函数

一：字符类函数

1) ASCII：功能是返回某个字符的ASCII值。

SELECT ASCII('a'),ASCII('A'),ASCII('中') FROM dual;

2) CHR：该函数的功能与ASCII函数的功能相反，它是根据参数的数值返回某个对应的字符。

SELECT CHR(65),CHR(97),CHR(14989485) FROM dual; 

3) CONCAT: 与连接运算符“||”类似，用于将第一个字符串和第二个字符串连接成一个字符串后返回

SELECT CONCAT(CONCAT(journal_name, ' is '),journal_level) AS journal_level

FROM journal;

4) INITCAP：将首字母大写返回

SELECT INITCAP(journal_name) FROM journal;

5) INSTR: 是在一个字符串中搜索指定的字符，并返回发现指定的字符的位置

SELECT INSTR('abc','b') FROM dual;

SELECT * FROM JOURNAL WHERE INSTR(journal_name,UPPER('infor')) >0;

6) LOWER,UPPER:

SELECT UPPER('abc') FROM dual;

SELECT LOWER('ABC') FROM dual;

7) LPAD,RPAD: 在列名或表达式的左边补齐字符，补齐之后的长度为width，然后返回

SELECT LPAD(t_id,10,'0'),RPAD(t_id,10,'0') FROM teacher

8) LENGTH，LENGTHB: LENGTH('数据库'), LENGTHB('数据库') FROM dual;

9) REPLACE: 参数1：原串，参数2，旧串，参数3，新串

SELECT REPLACE('学习数据库','数据库','Oracle数据库')FROM dual

10) SUBSTR: 参数1：原串，参数2，起始位置（负数表示右边），参数3：长度

SELECT SUBSTR('学习数据库',3,3) FROM dual;

SELECT SUBSTR('学习数据库',-4,3) FROM dual;

 

二：数值类函数

1) ABS: SELECT ABS(12.34),ABS(-12.34), ABS(0) FROM dual;

2) CEIL: 该函数的功能是获取大于或等于参数值的最大整数

SELECT CEIL(12.34),CEIL(-12.34), CEIL(0) FROM dual;

3) FLOOR: 得到小于或等于x的最大整数

SELECT FLOOR(12.34),FLOOR(-12.34), FLOOR(0) FROM dual;

4) MOD: 取余数 SELECT MOD(3,2) FROM dual;

5) ROUND: 进行四舍五入

SELECT ROUND(12.34),ROUND(12.56，1) FROM dual;

6) TRUNC: 将列名或表达式所表示的数值进行截取（不四舍五入）

SELECT TRUNC(12.343,1),TRUNC(12.569,2) FROM dual;

三：日期类函数

1) ADD_MONTHS: 该函数的功能是在第一个参数的基础上加上第二个参数个月，如果第二个参数为负数，则表示在第一个参数的基础上减去第二个参数个月

SELECT add_months(t_entertime,12),add_months(t_entertime,-12)  FROM teacher;

2) CURRENT_DATE:，CURRENT_TIMESTAMP: 返回的是当前会话时间

SELECT current_date,current_timestamp FROM dual;

3) EXTRACT: 从日期时间类型的数据中提取特定部分的信息

SELECT EXTRACT(YEAR FROM SYSDATE) YEAR,EXTRACT(DAY FROM SYSDATE)DAY FROM dual;

4) LAST_DAY: 获取日期参数所在月的最后一天

SELECT last_day(SYSDATE) FROM dual;

5) NEXT_DAY: 当前日期之后下一个周几的日期

SELECT next_day(SYSDATE,1) FROM dual;

四：空值处理函数

1) NVL: 如果为空，则用参数2替换

SELECT s_name, NVL(s_duty,'没有职务')FROM student

2) NULLIF: 两个参数的值进行比较，如果相等，则返回空值，如果不相等，则返回第一个参数的值

SELECT s_name,NULLIF(s_duty,'班长') FROM student;

注，字符串常量一般被认为为varchar2类型，如果出现字符集不匹配的错误，说明两个参数，一个是varchar2，另一个是nvarchar2，需要进行字符集转换:

SELECT a_name,NULLIF('中国',to_char(a_country)) FROM author;

-- 将学生表中的所有国贸班级的学生的班级置为空值

SELECT s_name,s_classname,NULLIF(SUBSTR(s_classname,1,2),'国贸') FROM student;

五：转换类函数   

1) TO_CHAR: SELECT TO_char(1234,'9,999') FROM dual;

2) TO_NUMBER ：SELECT to_number('9999') FROM dual

3) TO_DATE: 

SELECT to_date('1998-09-08 15:34:33','yyyy-mm-dd hh24:mi:ss') FROM dual;

转换的格式：

l 表示year的：y 表示年的最后一位 yy 表示年的最后2位 yyy 表示年的最后3位 yyyy 用4位数表示年

l 表示month的：mm 用2位数字表示月；mon 用简写形式 比如11月或者nov ；month 用全称 比如11月或者november

l 表示day的：dd 表示当月第几天；ddd表示当年第几天；dy 当周第几天 简写 比如星期五或者fri；day当周第几天 全写

l 比如星期五或者friday。

l 表示hour的：hh 2位数表示小时 12进制； hh24 2位数表示小时 24小时

l 表示minute的：mi 2位数表示分钟

l 表示second的：ss 2位数表示秒 60进制

 

六：其他函数

1) DECODE:类似于ifelse

SELECT t_gender, DECODE(t_gender,'男','领带','女','围巾') FROM teacher;

第 六讲 ：模式对象（视图，同义词，序列，索引，分区和临时表）

一：视图

1） 视图（View）是一种虚拟表，内容由查询语句定义，本质上就是一条存储起来的SQL语句，是一个命名的查询，主要功能是用于改变基表（Base Table，组成视图的表）的数据显示，简化查询。

CREATE [OR REPLACE] [FORCE | NOFORCE] VIEW [schema].view_name

[(column_name[, column_name]...)]

AS Subquery

[WITH CHECK OPTION [CONSTRAINT constraint]]

[WITH READ ONLY [CONSTRAINT constraint]];

例如：CREATE VIEW s_view AS SELECT * FROM student;

CREATE VIEW t_view AS SELECT s_id,s_name,s_gender,s_language,s_math+s_chinese+s_foreign AS score

FROM student;

2） 视图和普通表一样，也可以进行增删改操作，对视图进行的增删改操作实际上就是对基表进行增删改操作。

3） 如果想要使创建的视图只能查询，而不能增删改，则可以使用WITH READ ONLY子句，使用WITH READ ONLY子句的视图表明该视图是一个只读视图。

CREATE OR REPLACE VIEW s_view AS SELECT * FROM student WITH READ ONLY;

4） 在创建视图时可以加上限制条件，当对视图进行增删改操作的时候会自动检查子查询中的条件，如果不满足子查询中的条件，就不允许对视图进行操作，从而可以保证视图操作前后的一致性。要完成此功能，需要指定WITH CHECK OPTION子句。

5）  并不是任何时候都可以对视图进行DML操作，当视图的子查询中有如下情景时不能对视图进行DML操作：

1. 子查询中使用聚合函数时；

2. 子查询中使用GROUP BY子句时；

3. 子查询中使用DISTINCT关键字时；

4. 子查询中使用伪列rownum时、子查询中使用UNION、UNION ALL、MINUS等集合操作符时

6） Oracle不提供直接修改视图的方法，如果要对视图定义的内容进行修改，可以使用REPLACE关键字，也就是用新的视图定义替换旧的视图定义。

CREATE OR REPLACE VIEW t AS

SELECT * FROM author WHERE a_sex='女';

7） 如果要删除视图，那么需要使用DROP VIEW语句

Drop view t;

8） 和视图相关的数据字典：主要包括视图的名称（view_name）、定义视图文本的长度（text_length）、视图的定义（text）等信息

1. dba_views

2. all_views

3. user_views

例如：SELECT view_name,text FROM user_views WHERE view_name='S_VIEW'

 

二：同义词

1) 如同可以为表和列起表别名和列别名一样，Oracle数据库允许为数据库中的大部分模式对象起别名，并把为数据库对象起的别名称为同义词。

2) 创建同义词

CREATE [OR REPLACE] [PUBLIC] SYNONYM [schema.]synonym_name

FOR object_name;

例如：CREATE OR REPLACE SYNONYM s1 FOR student;

3) 删除私有同义词的语法格式如下：

DROP SYNONYM synonym_name;

删除公有同义词的语法格式如下：

DROP PUBLIC SYNONYM synonym_name;

4）删除同义词中的数据，原表中的数据也会被删除。

5）数据字典：包括同义词名称（synonym_name）、同义词的拥有者（table_owner）、同义词所指代的对象名称（table_name）、数据库链接（db_link）

1. dba_synonyms、

2. all_synonyms

3. user_synonyms

三：序列

1) 创建序列：（修改索引把create改成alter）

CREATE SEQUENCE [schema.]sequence_name

[ INCREMENT BY n ]

[ START WITH n ]

[ MAXVALUE n | NOMAXVALUE ]

[ MINVALUE n | NOMINVALUE ]

[ CYCLE n | NOCYCLE ]

[ CACHE n | NOCACHE ]

[ ORDER | NOORDER ]

例如：

CREATE SEQUENCE seq_test

MINVALUE 0

MAXVALUE 999999

START WITH 0

INCREMENT BY 1

NOCACHE

NOCYCLE

使用序列：

INSERT INTO table_seq VALUES(seq_a_id.NEXTVAL,'aa');

2) 可以通过数据字典视图：主要包括序列的名称（sequence_name）、最小值（min_value）、最大值（max_value）、步长（increment_by ）等信息

(1) dba_sequences、

(2) all_sequences

(3) user_sequences

 

 

 

索引

1. Oracle数据库中的索引是一种建立表或簇基础上的数据对象，和表一样具有独立的段存储结构，需要在表空间中为其分配存储空间。

2. 索引是减少磁盘 I/O的一种重要的手段，通过在表的一个或多个列上创建索引，可以提高查询表中数据的速度。

3. 索引的类型

1). B树索引：B树索引是最常用的一种索引类型，也是Oracle数据库的默认索引类型。B树指的是平衡树（Balanced Tree），它是使用平衡算法来管理索引的。

适合的情况：

（1）表中存储的数据行数很多。

（2）列中存储的数据的不同值很多。

（3）查询的数据量不超过全部数据行的5%，否则应使用全表扫描。

2). 位图索引：当要建立索引列的数据有大量的重复值时,比如性别等

3). 反向键索引：反向键索引是一种B树索引，它在物理上反转索引的列值，但是列的顺序保持不变。反向键索引通常建立在值是连续增长的列上。查询时必须执行全表扫描，因此建立反向键索引的列不适合用于范围查询。比如学号为10001，10002，10003，10004,正向时数值过于密集，反向后变成10001，20001，30001，40001，可以将数据的间距拉大。适用于自增长的列

4). 基于函数的索引：基于函数的索引会先对列的函数或表达式进行计算然后将计算的结果存入索引中。

CREATE INDEX index7 ON course(UPPER(c_ename));

创建这种类型的索引时需要注意以下几点：

(1) 创建时必须具有 QUERY REWRITE系统权限。

(2) 表达式中不能出现聚合函数。

(3) 不能在LOB类型的列上创建。

5). 唯一索引和非唯一索引: 如果根据索引值是否可以相同，可以将索引分为唯一索引和非唯一索引。

6). 单列索引和复合索引: 单列索引就是建立的表的单一列上的索引，大部分的索引是单列索引，如果把表的多个列作为一个整体并在其上建立索引，则将所建的索引称为复合索引。复合索引中的多个列不一定是表中相邻的列，但是由于在索引定义中所使用列的顺序很重要，因此一般地将最常被访问的列放在前面。

CREATE INDEX index_course_cname3 ON course(c_name,c_ename)  TABLESPACE test_tablespace2;

1. 创建索引：先要创建表空间：

create tablespace test datafile 'D:\test.ora' size 100m

CREATE [ UNIQUE | BITMAP ] INDEX [schema.]index_name

ON [schema.] table_name (column_name1 [ ASC | DESC ][, column_name2 [ ASC | DESC ],...])

TABLESPACE tablespace_name

NOCOMPRESS | COMPRESS column_number

LOGGING | NOLOGGING

NOSORT | SORT

REVERSE

例如：CREATE INDEX index2 ON student(s_name) TABLESPACE test;

CREATE BITMAP INDEX index3 ON student(s_political);

CREATE  INDEX index5 ON student(s_language) REVERSE;

1. 修改索引：

将索引修改为反向键索引或普通索引

ALTER INDEX index_name REBUILD NOREVERSE | REBUILD REVERSE

ALTER INDEX index5 REBUILD NOREVERSE;

将索引重命名

ALTER INDEX index_name RENAME TO new_name

ALTER INDEX index5 RENAME TO index6;

 

1. 删除索引：

DROP INDEX index_name

1. 可以通过以下的数据字典视图了解与用户相关的信息：

dba_indexes、all_indexes和user_indexes；

1. 当创建主键约束和唯一约束时，Oracle数据库会自动在该列上创建唯一索引。

不仅是主键约束和唯一约束需要使用唯一索引，外键约束也需要.

1. 索引的缺点：

1). 创建索引和维护索引要耗费一定的时间，而且耗费的时间长度会随着数据量的增加而增加。

2). 索引作为一个独立的数据对象需要占用一定的磁盘空间

3). 当对表执行DML语句的时候，只要执行的SQL语句涉及到索引列，数据库就需要动态的更新索引，必然会降低数据的维护速度。

1. 何时使用索引：

1). 表的数据量一定要很大。

2). 索引列的查询频率一定要高。

3). 满足查询条件记录数不能很多。

4). 在需要排序的列上创建索引。

5). 可以在查询频率高的组合列上创建索引。

6). 在多表连接查询中的连接列上创建索引。

7). 不要在低基数的列上创建索引。

8). 当需要对一个表频繁的进行增删改操作的时候，不应该为该表创建索引。

9). 要限制在表上创建索引的数量。

10). 在向数据库导入数据时，不要创建索引。

事务管理Transaction

1. 事务是一条或多条SQL语句组成的执行序列，这个序列中的所有语句都属于一个工作单元，用于完成一个特定的业务逻辑。数据库对事务的处理方式是要么全部执行完成，要么一条语句也不执行，这样做的目的是保证数据的一致性和完整性。

2. 事务必须同时满足四个特性：ACID

1) 原子性Atomicity

2) 一致性Consistency：执行结果必须使数据库从一个一致性状态变到另一个一致性状态

3) 隔离性Isolation：不能被干扰

4) 持久性Durability：一旦提交，它对数据库中数据的改变应该是永久性的

1. Oracle数据库中所有的事务都是隐式开始的，当执行第一条DML语句或者一些需要进行事务处理的语句时，事务就开始了。

当发生下列情况时，会终止当前事务：

（1）显式地使用提交或回滚语句。

（2）当在DML语句的后面执行DDL语句或DCL语句时。

（3）当用户进程异常终止或系统崩溃时，事务也会回滚。

（4）正常断开数据库连接，结束一个对话

1. COMMIT：提交

2. ROLLBACK：回滚

3. SAVEPOINT：保存点

ROLLBACK TO SAVEPOINT savepoint_name

PL/SQL编程基础

1. Oracle数据库对标准的SQL语言进行了扩展，将SQL语言的非过程化与高级程序开发语言过程化的思想相结合，产生了PL/SQL语言，可以在其内部嵌套普通的SQL语句。

2. 块的基本结构

一个完整的PL/SQL语句块由以下三个部分组成：

[DECLARE]  

/* 声明部分：在此声明PL/SQL用到的变量、类型及游标等 */

BEGIN

/* 执行部分：过程及SQL 语句，即程序的主要部分，实现块的功能*/

[EXCEPTION]

/* 执行异常部分：错误处理 */

END;

1. 标识符：标识符的书写规则如下：

(1) 标识符必须以字母开头。

(2) 标识符可以由一个或多个字母、数字或特殊字符（$、#、_）组成。

(3) 标识符长度不超过30个字符。

(4) 标识符内不能有空格。

1. 数据类型

1) 字符型

2) 数值型

3) 日期时间型：DATE 世纪，年，月，日，时，分，秒

4) 布尔型

5) 参考类型：%TYPE

6) 一行数据的类型：%rowtype

1. 赋值语句

1) 直接赋值  

变量名 := 常量或表达式;

例如：v_num  NUMBER:=5;

2) 通过SELECT..INTO语句赋值

语法格式为：SELECT  列值 INTO  变量名

1. 分支结构

(1) IF-THEN-ELSE

IF condition1 THEN

    statement1;

[ELSIF condition2 THEN statement2;]

    ……

[ELSE

    else_statement;]

END IF;

(2) CASE：表达式结果为true或false

WHEN boolean_expression1 THEN statement1;

WHEN boolean_expression2 THEN statement2;

……

WHEN boolean_expressionN THEN statementN;

[ ELSE

    else_statement ]

END CASE;]

(3) CASE selector：表达式结果为定值

WHEN selector_value1 THEN statement1;

WHEN selector_value2 THEN statement2;

    ……

WHEN selector_valuen THEN statementN

[ ELSE

    else_statement; ]

END CASE;

1. 循环结构

(1) 简单循环

LOOP

statement;

[EXIT;]

END LOOP

(2)  WHILE condition LOOP　

 statement;

END LOOP;

(3) FOR 循环

FOR loop_counter IN [REVERSE] lower_bound .. upper_bound  LOOP

statement;

END LOOP;

无论循环变量是递增还是递减，初始值都必须小于结束值，且增量（减量）必须是1,不用再BEGIN中让i自加自减了

1. 跳转结构: 跳转结构就是指利用GOTO语句实现程序流程的强制跳转。因为GOTO语句是非结构化语句，所以在PL/SQL程序中不推荐使用GOTO语句.

2. 变量的作用域

变量的作用域是指变量在程序中的有效范围。对于一个PL/SQL变量，它的作用域是从该变量被声明开始到变量所在块结束。当变量超过了这个范围，则PL/SQL引擎将释放存放该变量的空间，这个变量就不存在了，也就无法再访问了。

1. 游标：

游标使用的步骤1.定义，2打开，3，遍历，4，关闭

(1) 定义游标：

定义游标时需要定义游标的名字，并将该游标和一个SELECT 语句相关联，这时候相当于给游标所能操作的内存区域做个规划，但数据并没有加载到内存区域。

定义游标的语法格式如下：

CURSOR  cursor_name[(parameter1  datatype[，parameter2  datatype...])]

IS  SELECT  statement;

(2) 打开游标

OPEN  cursor_name  [（parameter1[，parameter2...]）];

(3) 取值到变量

FETCH diploma_cursor1 INTO d_id,d_name;

(4) 关闭游标

CLOSE  cursor_name;

(5) 游标的属性

显式游标有四个属性，它们分别是%ISOPEN、%NOTFOUND、%FOUND和%ROWCOUNT。这些属性将返回游标操作的一些有用信息，但要注意这些属性只能使用在过程性语句中，而不能使用在SQL语句中

例:更新职称表，如果没有找到更新的记录，则向职称表中插入一条记录。

BEGIN

UPDATE title SET title_name = '外聘' WHERE title_id = 10;

IF SQL%NOTFOUND THEN

DBMS_OUTPUT.PUT_LINE('没有找到要更新的记录！');

INSERT INTO title VALUES(10, '外聘');

COMMIT;

END IF;

END;

游标属性	描     述
游标名%ISOPEN	值为布尔型，如果游标已打开，取值为TRUE，否则为FALSE。
游标名%NOTFOUND	值为布尔型，如果最近一次FETCH操作没有返回结果，则取值为TRUE， 否则为FALSE。
游标名%FOUND	值为布尔型，与%NOTFOUND属性相反，如果最近一次FETCH操作没有返回结果， 则取值为FALSE，否则为 TRUE。
游标名%ROWCOUNT	值为数字型，值是到当前为止返回的记录数。

(1)   循环游标（while循环不好用，不建议使用while）

由于FETCH语句每次只能读取一行数据，因此必须使用循环才能遍历更多的数据。循环结构有3种，所以循环检索游标的方法也有3种：简单循环检索游标、WHILE循环检索游标和FOR循环检索游标。

FOR  record_name IN cursor_name LOOP

statement1;

statement2;

...

 END LOOP;

(2)  使用游标进行更新和删除操作

CURSOR  cursor_name IS

SELECT statement FOR UPDATE [OF column] [NOWAIT];

No wait:只如果资源被占用，则弹出提示框，显示资源被占用

CURRENT OF 的使用：从结果集中去除当前行，必须和for update连用

(8)隐式游标：SQL

隐式游标一般用于增删改，因为select语句需要有into关键字

是由PL/SQL自动创建和管理的游标，每次执行SELECT和DML语句时PL/SQL就会打开一个隐式游标，在关联的语句运行结束之后就关闭了(commit之后关闭)，隐式游标又被称为SQL游标。

注意：使用隐式游标的时候，一定要在commit之前使用，因为commit之后游标就关闭了。

 

 

1. 异常：begin和end之间的异常可以被捕获，declare的异常不能被捕获

(1) 异常类型

a) 预定义异常

PL/SQL为一些Oracle公共错误预定义了异常，包括错误编号和错误名称，错误编号用一个负的5位数表示。当PL/SQL违背了Oracle原则或超越了系统依赖的原则时就会隐式地产生预定义异常，这类异常可以由Oracle自动处理，而无需在程序中定义

DUP_VAL_ON_INDEX：重复值

NO_DATA_FOUND：数据找不到

ZERO_DIVIDE:除0

b) 非预定义异常，例如，违法主键

这类异常也是Oracle系统异常的一种，用于处理有一些没有预定义异常与之关联的Oracle错误，也就是Oracle预先定义了错误编号但没有定义名称，对这种异常情况的处理，需要用户在PL/SQL块中声明一个异常名称，然后通过编译指示PRAGMA EXCEPTION_INIT将该异常名称与一个Oracle错误相关联。此后，当执行过程出现该错误时将自动抛出该异常。

c) 用户自定义异常

程序执行过程中，有时会出现编程人员人为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显式地在程序中将其引发。

(2) 异常处理的步骤

a) 定义异常

b) 抛出异常

c) 捕获和处理异常

 

(3) SQLCODE和SQLERRM

SQLCODE函数和SQLERRM函数来获得异常对应的错误代码和错误信息（都可用others替换）

异常种类	SQLCODE	SQLERRM
Oracle错误对应的异常	负数	Oracle错误
NO_DATA_FOUND异常	+100	No data found
用户自定义异常	+1	User-Defined Exception
没有产生异常	0	ORA-0000:normal,successfulcopletion

 

PL/SQL高级编程

一：存储过程（不能被sql语句执行）

存储过程是一种命名的PL/SQL程序块，它既可以没有参数，也可以有参数，也可以有一个或若干个输入输出参数，甚至可以有多个即作为输入也作为输出的参数，但它通常没有返回值。存储过程被保存在数据库中，但它不能被sql语句直接执行或调用，只能用EXCUTE命令执行或在PL/SQL内被被调用。存储过程是被编译好的了，所以效率很高。

 

1. 创建存储过程：

CREATE  [OR REPLACE]  PROCEDURE  procedure_name

[(parameter_name  [ IN | OUT | IN OUT ]  datatype, …)]

IS | AS

    declare_section;---省略DECLARE关键字

BEGIN

    statement;

END  [procedure_name];

注: 1. 如果不需要参数，参数可以省略，小括号也不要

            2 如果在存储过程中的执行代码中要定义变量，要省略DECLARE关键字

1. 存储过程显示错误信息

命令窗口中：show errors procedure 存储过程名称;

如：

show errors procedure pro1;

1. 存储过程的三种参数

IN （默认）	输入参数，用来从调用环境中向存储过程传递值，在过程体内不能给IN参数赋值， 也不能指定参数类型的长度，包括number类型，不能指定整数和小数的位数， 这种模式就是在程序运行的时候已经具有值，在程序体中值不会改变
OUT	输出参数，用来从存储过程中返回值给调用者，在过程体内必须给OUT参数赋值，用来 存储过程外调用和输出参数
IN OUT	输入输出参数，既可以从调用者向存储过程中传递值，也可以从过程中返回可能改变 的值给调用者

 

1. 参数的传值方式：（多参数之间用逗号间隔，而不是使用分号）

1) 位置表示法

a) 如果形式参数是IN模式的参数，实际参数可以是一个具体的值或是一个已经赋值的变量。IN参数不能被赋值。

b) 如果形式参数是OUT模式的参数，实际参数必须是一个变量，而不能是常量。当调用存储过程后，此变量就被赋值了，OUT参数必须被赋值。

c) 如果形式参数是IN OUT模式的参数，则实际参数必须是一个已经赋值的变量。当存储过程完成后，该变量将被重新赋值。

2) 名称表示法：实参与形参的名称进行关联的方法就是名称表示法，这时需要使用关联运算符“=>”实现关联。

1. 查看存储过程的源代码

select text from user_source where name='MYP2';

1. 删除存储过程：DROP PROCEDURE procedure_name;

2. 查看存储过程的参数信息：DESC 过程名（命令模式）

3. 存储函数的调用方法

1) Command窗口下execute或者begin end

2) Sql窗口下，begin end

9.  在sql window 中查看存储过程源码：

    1） 左边object窗口中找到procedure，在相应的存储过程上点右键—view

2）在sql window中存储过程上，按下ctrl键同时点击鼠标左键，即可跳转到相应的存储过程源码中查看。

二：函数（必须有返回值，可被sql语句调用）

1. 函数一般用于计算和返回一个值，可以将经常需要使用的计算或功能写生一个函数，函数必须有返回值。

2. In参数和返回值都不能声明数据的长度。如number（4，1）就是错误的。

3. 创建函数：

CREATE  [OR REPLACE]  FUNCTION  function_name

[(parameter_name  [ IN | OUT | IN OUT ]  datatype, …)] RETURN  data_type

IS | AS

    declare_section;

BEGIN

    statement;

END  [function_name];

1. 调用函数：

1) 通过使用变量接受函数返回值来调用

2) 在SQL语句中直接调用函数

3) 使用包DBMS_PUTPUT.PUT_LINE调用函数

4) 在PL/SQL程序中调用存储函数

四：触发器

1. 所谓触发器就是指执行由某个事件引起或激活操作的对象,通常用于管理复杂的完整性约束，或监控对表的修改。触发器是一种特殊的存储过程，也是由声明部分、语句执行部分和异常处理部分组成的PL/SQL命名块，并存储在数据库数据字典中。

2. 触发器的组成

（1）触发对象：只有在这些对象上发生了符合触发条件的触发事件，才会执行触发操作，包括表、视图、模式、数据库等。

（2）触发事件：引起触发器被触发的事件，比如DML语句、DDL语句、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）等，可以用在触发事件之后，用of关键字指定触发器监控的列名。

（3）触发时间：是指触发器触发的时机，触发器可以在触发事件发生之前或之后触发。（before|after）

（4）触发级别：触发器分语句级触发器和行级发器两个级别。当某触发事件发生时，语句级触发器只执行一次，而行级触发器则对受到影响的每一行数据都单独执行一次。 默认语句级。

（5）触发条件：由WHEN子句指定一个逻辑表达式，只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发器，使其执行触发操作。

1. 触发器的类型

（1）DML触发器：该类型的触发器创建在一个表或视图之上，触发它的事件是DML操作,可以在DML操作之前或操作之后进行触发，并且可以在每个数据行或语句操作上进行触发。

（2）替代触发器：如果一个视图定义中涉及的基表多于一个或子查询中使用了一些无法进行DML操作的语句，则无法使用DML触发器对基表进行操作，此时就需要使用替代触发器，它是Oracle专门为进行视图操作提供的一种触发器。

（3）系统触发器：由Oracle数据库的系统事件触发，如Oracle数据库关闭或打开等。

1. 语法格式如下：

CREATE [ OR REPLACE ] TRIGGER trigger_name

[BEFORE|AFTER ]trigger_event [OF column_name ]  

ON tle_name

[ FOR EACH ROW]

[ WHEN trigger_condition ]

BEGIN

trigger_body;

END [ trigger_name ]

1. 触发时间：before|after 和触发事件INSERT,UPDATE,DELETE

   1. 触发级别：触发器分为语句级触发器和行级触发器，关键字 FOR EACH ROW（行级），行级触发器是不能读本表的
   2. 使用触发器谓词（对增删改进行细化）

触发事件不仅可以是一个DML操作，还可以由多个DML操作组成，当在触发器中包含多个触发事件时，为了分别针对不同的事件进行不同的处理，需要使用条件谓词（ INSERTING，UPDATING及 DELETING）来判断是哪个触发事件触发了触发器。

取值	含义
INSERTING	如果触发事件是INSERT操作，则谓词的值为TRUE，否则为FALSE
UPDATING	如果触发事件是UPDATE操作，则谓词的值为TRUE，否则为FALSE
DELETING	如果触发事件是DELETE操作，则谓词的值为TRUE，否则为FALSE

1. 触发事件中的of关键字

当需要指定只有某列操作了，才激活触发器时，可以使用of关键字

1. 使用行级触发器标识符 （：new，：old，只能用在行级触发器中）

在行级触发器中，如果需要引用操作之前和操作之后的数据，可以使用:old和:new标识符，分别表示该列变化前的值和该列变化后的值。 可用来实现级联更新。

触发事件	:old.列名	:new.列名
INSERT	所有字段都是NULL	当该语句完成时将要插入的数值
UPDATE	在更新之前该列的原始值	当该语句完成时将要更新的新值
DELETE	在删除行之前该列的原始值	所有字段都是NULL

无约束时，可用触发器实现级联更新或删除

1). 没有外键约束时，两个表可以通过触发器实现同时删除或更新

2). 有外键约束时，如果是默认形式，能通过触发器级联更新，不能进行被引用表的删除操作。

3). 如果外键约束设为set null，则可在被引用表删除数据时，引用表该值自动设为null，需要触发器进行级联更新。

4). 如果外键约束为cascade,则会在被引用表删除数据时，引用表自动删除相应数据，不需要触发器，但是级联更新，需要触发器。

5). 但是如果使用了cascade，则当被引用表删除数据时，引用表数据也被自动删除了，所以引用表已经变化，触发器就不能读引用表了，有时会报ora-04901错误（触发器/函数不能读他的错误）

1. When：设计限定条件，当满足条件时，才触发，必须配合FOR EACH ROW

WHEN的条件中，old和new都不加冒号（：）

注：when子句必须和行级触发器一起使用，而行级触发器一般不能查询本表（除非声明触发器为自定事务，通过pragma autonomous_transaction），而且行级触发器中，查询本表数据没有任何意义（因为会影响多行，所以触发多次，查询本表多次，所以，一般when子句都用在有外键，或非外键但只输出，无本表操作的时候）

1. 启用和禁用触发器：

ALTER TRIGGER [schema.]trigger_name ENABLE | DISABLE;

ALTER TRIGGER mytrigger DISABLE;

1. 删除触发器：DROP TRIGGER trigger_name；

   1. PL/SQL包是将相关的变量、子程序、游标和异常组织在一起的模式对象，包经过编译后会存储在数据库中，以供其他应用程序使用
   2. 在Oracle数据库中，包有两类，一类是系统包，它们是由Oracle预先定义的、可以供用户直接使用的包，另一类是根据应用需要由用户创建的包
   3. 包由两个分离的组成部分：包规范（Specification）和包主体（Body）。

包

包规范提供了一个操作接口，规定了包要实现的功能，对应用来说是可见的，包主体则实现了包规范中定义的功能，它类似于黑盒，对应用来说隐藏了实现的细节

1. 创建包规范

包规范用于定义公用的常量、变量、过程和函数等元素，它类似于Java中的接口或抽象类的概念，只规定了要做什么，而没有具体实现如何去做，包规范中定义的元素被称为包的公有元素

CREATE  [OR REPLACE]  PACKAGE  package_name

IS | AS

package_specification;

END  [package_name];

1. 创建包主体

CREATE  [OR REPLACE]  PACKAGE  BODY  package_name

IS | AS

package_body;

END  [package_name];

1. 查看包中的错误

查看包声明错误：

show errors package pack2;

查看包主体错误：

show errors package body pack2;

1. 包的调用

在包规范中声明的任何元素都是公有的，在包的外部都是可见的，可以通过“包名.元素名”的形式进行调用，在包主体中可以通过“元素名”直接进行调用，但是在包主体中定义而没有在包规范中声明的元素是私有的，只能在包主体中被引用。

 

程序包名	说明
standard 和 DBMS_STANDARD	定义和扩展PL/SQL语言环境
DBMS_LOB	提供对LOB数据类型进行操作的功能
DBMS_OUTPUT	处理PL/SQL块和子程序输出调试信息
DBMS_RANDOM	提供随机数生成器
DBMS_SQL	允许用户使用动态SQL
DBMS_XMLDOM	用DOM模型读写XML类型的数据
DBMS_XMLPARSER	xml解析，处理xml文档内容和结构
DBMS_XMLQUERY	提供将数据转换为xml类型的功能
DBMS_XSLPROCESSOR	提供XSLT功能，转换xml文档
UTL_FILE	用PL/SQL程序来读写操作系统文件

 

 

 

备份

一．备份的方式

1. 导入/导出

(1) Import/export

(2) 数据泵

1. 冷备份（非归档模式）

冷备份数据库是将数据库关闭之后备份所有的关键性文件包括数据文件、控制文件、联机REDO LOG文件，将其拷贝到另外的位置。此外冷备份也可以包含对参数文件和口令文件的备份，但是这两种备份是可以根据需要进行选择的。冷备份实际也是一种物理备份，是一个备份数据库物理文件的过程。

1）优点：

 

      <1>只需拷贝文件即可，是非常快速的备份方法。

 

      <2>只需将文件再拷贝回去，就可以恢复到某一时间点上。

 

     <3>与数据库归档的模式相结合可以使数据库很好地恢复。

 

       <4>维护量较少，但安全性确相对较高。

2）缺点：

 

       <1>在进行数据库冷备份的过程中数据库必须处于关闭状态。

 

      <2>单独使用冷备份时，数据库只能完成基于某一时间点上的恢复。

 

       <3>若磁盘空间有限，冷备份只能将备份数据拷贝到磁带等其他外部存储上，速度会更慢。

 

       <4>冷备份不能按表或按用户恢复。

1. 热备份（归档模式）

   热备份是在数据库运行的情况下，采用archive log mode方式备份数据库的方法。。所以，假如你有昨天夜里的一个冷备份而且又有今天的热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在archivelog方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可以做备份了。

二．导入和导出

1．Import和export

（1）Export:

①全库备份：exp system/Admin2@orcl file=d:/demo/bck01.dmp full=y;

②导出制定用户的表：

Exp 用户名/登录密码@全局数据库名  file=备份文件的路径  owner=用户名1，用户名2.....

③导出指定用户的指定表：

Exp 用户名/登录密码@全局数据库名  file=备份文件的路径 tables=表名

注意：表名指的是登录用户具有权限的表

（2）Import

①导入全库：imp  用户名/登录密码@全局数据库名 file=备份文件的路径  full=y

②导入制定用户的表：

Imp用户名/登录密码@全局数据库名  file=备份文件的路径  owner=用户名1，用户名2.....

③导入指定用户的指定表：

Imp 用户名/登录密码@全局数据库名  file=备份文件的路径 tables=表名

2．数据泵(impdp和expdp)

导出

（1）建立一个文件夹   d:/demo

（2）管理员登录sqlplus，创建数据导出目录为一个expnc_dir目录名

Create directory  expnc_dir   as  ‘文件夹路径’

（3）为需要进行导出操作的用户进行赋予权限

    Grant read,write on directory expnc_dir to 用户名

（4）退出sqlplus

（5）在cmd下进行备份

Expdp  用户名/密码@orcl  directory=expnc_dir  dumpfile=文件名.dmp  schemas=用户名  logfile=文件名.log

导入

（1）如果已经存在目录对象，则不用建立，如果备份所在的目录不存在，则进行目录的建立和授权（见导出的（2），（3））

（2）在cmd进行数据库的导入

Impdb 用户名/密码@orcl  directory=expnc_dir  dumpfile=文件名.dmp  schemas=用户名

三．冷备份

1. 查看源主机上orcl数据库需要备份的各个文件的位置，包括：控制文件，参数文件，密码文件，数据文件，联机重做日志文件

控制文件的位置：Select * from V$CONTROLFILE

参数文件：select * from v$parameter where name like '%spfile%';

密码文件：

orcle的装路径\product\11.2.0\dbhome_1\database\PWDorcl.ora

数据文件：select * from v$datafile;

联机重做日志文件：

select * from v$logfile;

1. 停止数据库（恢复）

2. 文件的复制 （备份的文件直接放回原有位置）

3. 数据库启动

四．热备份

1. 确保数据库在归档模式下

(1) 查看数据库的模式

Select log_mode from v$database;

(2) 将数据设置为归档模式

Alter database archivelog;

(3) 查看数据文件的位置

 select file_name from dba_data_files where tablespace_name='USERS';

（3）设置表空间为备份状态

alter tablespace users begin backup;

（4）备份表空间的数据文件

copy f:\oracle\oradata\ebook\tools01.dbf f:\oracle\backuparea 

 ho cp /u01/app/oracle/oradata/orcl/users01.dbf /tmp/re

（5）恢复表空间为正常状态

alter tablespace users end backup;

2）备份归档log文件　　　　

 

       （1） 临时停止归档进程　　

 

SYS@orcl> alter tablespace users offline;

 

Tablespace altered.

 

查看表空间的状态

SYS@orcl> select tablespace_name,online_status from dba_data_files;

 

TABLESPACE_NAME        ONLINE_

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

USERS        OFFLINE

UNDOTBS1        ONLINE

SYSAUX        ONLINE

SYSTEM        SYSTEM

EXAMPLE        ONLINE

       （2） log下那些在archive rede log目标目录中的文件　

 

SYS@orcl> select name from v$archived_log;

       （3） 重新启动archive进程　　

 

SYS@orcl> alter database archivelog;

       （4） 备份归档的redo log文件

 

SYS@orcl> ho cp /u01/app/oracle/oradata/orcl/*.log /tmp

SYS@orcl> alter tablespace users online;

Tablespace altered.

SYS@orcl> select tablespace_name,online_status from dba_data_files;

TABLESPACE_NAME        ONLINE_

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

USERS        ONLINE

UNDOTBS1        ONLINE

SYSAUX        ONLINE

SYSTEM        SYSTEM

EXAMPLE        ONLINE

切换日志，使用当前日志归档

SYS@orcl> alter system switch logfile;

3）用alter database bachup controlfile命令来备份控制文件热备份

 

SYS@orcl> alter database backup controlfile to '/tmp/controlfile';

 

log_archive_start=true --开启自动归档，否则只能手工归档，如果是关闭了归档，则设置该参数为false。10g之后废除该参数，不需要设置。

log_archive_dest = ---归档日志存放路径

log_archive_format ="CX%TS%S.ARC" ---归档日志文件名格式

 

alter system archive log current;        更改归档日志

 

 

DDL

一。SQL语句的分类

1. DDL语句

用于定义和维护数据库对象的结构或模式，主要包括：CREATE语句、ALTER

语句、DROP语句、TRUNCATE语句、COMMENT语句、RENAME语句。

2. DML语句

用于管理数据库对象中的数据，包括对数据进行增删改查等操作，主要包括：

SELECT语句、INSERT语句、UPDATE语句、DELETE语句、CALL语句、

LOCK TABLE语句。

3. DCL语句

用于设置或更改数据库用户或角色权限等功能，主要包括：GRANT语句、

REVOKE语句。

4. TCL语句

事务控制语言用于控制对数据库中事务的提交与回滚，主要包括：COMMIT语

句、ROLLBACK语句、SAVEPOINT语句、SET TRANSACTION语句。

二。DDL语句

1. 表空间

创建表空间的语法：

create tablespace 空间名字

datafile ‘路径/文件名.mdf’

size ?m

autoextend on;

next ?m

maxsize 最大值

设置账户的默认表空间：

新建账户直接使用default tablespace参数指定。

已有账户可使用修改alt user语句指定。

修改表空间：

1. 先离线 表空间

alter tablespace 表空间名 offline

2. 到物理文件那修改名字 (与代码无关)

3. 通知oracle 表空间物理文件更名为什么了

alter tablespace 表空间名 rename datafile 原始名字 to 新的名字

4. 上线

alter tablespace 表空间名 online

删除表空间：

2drop tablespace 空间名

将已有表移动到指定的表空间（要求：账户对表空间有使用权，且被移动的表

都属于账户）

alter table 表名 move tablespace 表空间；

2. 表

创建表的语法：

CREATE TABLE table_name(

column_name type [CONSTRAINT constraint_definition DEFAULT default_exp]

[, column_name type[CONSTRAINT constraint_definition DEFAULT default_exp]

…

)[TABLESPACE tablespace_name];

Oracle数据类型：

1) CHAR(size) 固定长度的字符数据,其长度为size个字节;size的最大值是2000

字节,而最小值和默认值是1;

2) NCHAR(size) 也是固定长度。根据Unicode标准定义；

3) VARCHAR2(size) 可变长度的字符串,其最大长度为size个字节;size的最大

值是4000,而最小值是1;你必须指定一个VARCHAR2的size；

4) NVARCHAR2(size) 可变长度的字符串,依据所选的国家字符集,其最大长度

为size个字符或字节;size的最大值取决于储存每个字符所需的字节数,其上限为

4000;你必须指定一个NVARCHAR2的size;

5) LONG：变长字符数据类型，VARCHAR2最大长度为4000，因此存取4000

字节以上大文本时可以用此数据类型，最大是2GB，但是从Oracle8i开始，不

推荐使用LONG类型，其存在的目的是支持向后兼容，建议使用CLOB和

NCLOB来替代

6) NUMBER(p,s) 精度为p并且数值范围为s的数值;精度p的范围从1到38;数值

范围s的范围是从-84到127;

例如:NUMBER(5,2) 表示整数部分最大3位，小数部分为2位；

NUMBER(5,-2) 表示数的整数部分最大为7其中对整数的倒数2位为0,前面的取

整。

NUMBER 表示使用默认值,即等同于NUMBER(5);

7) FLOAT：NUMBER的子类型，最大精度是38位。

8) BINARY_FLOAT：存储单精度32位浮点数。

9) BINARY_DOUBLE: 存储双精度64位浮点数。

10) DOUBLE PRECISION、REAL、INT、INTEGER和SMALLINT等。

11) DATE ：用于存储日期和时间信息，包括世纪、年、月、日、时、分、秒，其

最小精度为秒。 有效日期范围从公元前4712年1月1日到公元后4712年12月31日

312) TIMESTAMP：TIMESTAMP也是一种存储日期和时间的数据类型，但是它对

DATE类型进行了扩展，所能表示的时间信息比DATE类型更加精确，因为它不仅包

括了世纪、年、月、日、时、分、秒的信息，而且还包括了小数秒的信息。

13) INTERVAL：用于表示时间间隔的数据类型。

14) CLOB： 一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最

大为4G字节，主要存放英文字符。

15) NCLOB： 一个字符大型对象,可容纳单字节的字符;不支持宽度不等的字符集;最

大为4G字节;储存国家字符集

16) BLOB： 一个二进制大型对象;最大4G字节

17) BFILE ：包含一个大型二进制文件的定位器,其储存在数据库的外面；使得可以

以字符流I/O访问存在数据库服务器上的外部LOB;最大大小为4G字节

外键约束三种行为：

Oracle在外键的删除上有NO ACTION(类似RESTRICT)、SET NULL和CASCADE

三种行为：

1）NO ACTION：默认，当删除被引用表时，如果引用表已经引用了数据，则提示

已找到子记录，不能删除

2）SET NULL：SET NULL指当删除主表中被引用列的数据时，将子表中相应引用

列的值设置为NULL值。SET NULL有个前提就是外键引用列必须可以设置为

NULL。

如：

ALTER TABLE TB_STUDENT ADD CONSTRAINT

FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES

TB_CLASS (ID) ON DELETE SET NULL;

3）CASCADE：指当删除主表中被引用列的数据时，级联删除子表中相应的数据

行。

如：

ALTER TABLE TB_STUDENT ADD CONSTRAINT

FK_TB_STUDENT_CLASS_ID FOREIGN KEY (CLASS_ID) REFERENCES

TB_CLASS (ID) ON DELETE CASCADE

4）级联更新：Oracle本身没有提供级联更新的功能，触发器共同完成。（需配合

触发器，详见触发器章节）

与约束有关的数据字典视图：

1) dba_constraints、all_constraints、user_constraints：显示已定义的约束信息。

2) dba_cons_columns、all_cons_columns、user_cons_columns：显示约束相关

列的信息。

3) 例如：查询student表的表结构，注意，表名要大写SELECT * FROM

USER_TAB_COLUMNS WHERE table_name='STUDENT'

增加列或约束：

4 ALTER TABLE table_name ADD

(column_name type [ CONSTRAINT constraint_definition DEFAULT default_exp ]

[ , column_name type [ CONSTRAINT constraint_definition DEFAULT default_exp ]

…

);

修改列modify：

修改列的类型 ALTER TABLE 表名 MODIFY 列名 数据类型

修改列的约束 ALTER TABLE 表名 MODIFY 列名 约束关键字

修改列的默认值 ALTER TABLE 表名 MODIFY 列名 DEFAULT ’值‘

重命名列名：ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名。

删除列drop：ALTER TABLE 表名 DROP COLUMN 列名。

重命名表名：RENAME 原表名 TO 新表名

删除表：DROP TABLE 表名

 

 

DML 一。insert 语句 语法： 单表单条插入 INSERT INTO <source object>[(<column list>)] VALUES(<value list>); 单表多条插入 INSERT INTO <source object> SELECT <column list> FROM <source object list> WHERE <where predicates> INSERT ALL INTO <source object> VALUES(<value list>) ... INTO <source object> VALUES(<value list>) SELECT * FROM dual; 多表插入 INSERT ALL WHEN <when predicates> THEN INTO <source object> ... WHEN <when predicates> THEN INTO <source object> ELSE INTO <source object> SELECT <column list> FROM <source object list> WHERE <where predicates> ... 二。update语句 语法： update 表名 set 列名1=值1,列名2=值2,列名3=值3..... where 条件 2update 利用另外一张表关联更新本表数据的命令结构如下： update 表1 set 列名=（select 列名 from 表2 where 表1.列名=表2.列名） where exists (select 1 from 表2 where 表1.列名=表2.列名) 三。delete 语句 语法： delete from 表名 where 条件 当delete from不加where条件时，表示是把表中的数据全部删除。 四。truncate 语法： truncate table 表名； runcate命令也是数据删除命令，他是直接把Oracle表数据一次删除的命 令，truncate命令是一个DDL命令，不同于delete是DML命令。 五。truncate 和 delete区别 1、TRUNCATE 是 DDL 命令，命令执行完就提交，删除的数据不能恢 复； DELETE 命令是 DML 命令，命令执行完需提交后才能生效，删除后 的数据可以通过日志文件恢复。 2、如果表中的数据量较大，TRUNCATE的速度比DELETE速度快很多。 3、truncate删除将重新设置表索引的初始大小，而delete不能。 4、delete能够触发表上相关的delete触发器，而truncate则不会触发。 5、delete删除的原理是一次一条从表中删除数据，并将删除操作当做事 物记录在数据库的日志当中，以便进行数据回滚。而truncate是一次性进 行数据页的删除，因此执行速度快，但是不能回滚。 总结：truncate命令是属于DDL命令，一次性删除表中所有数据，并且数 据不能恢复，在实际开发过程当中truncate命令慎用。    

dba_users 数据库用户信息
dba_segments 表段信息
dba_extents 数据区信息
dba_objects 数据库对象信息
dba_tablespaces 数据库表空间信息
dba_data_files 数据文件设置信息
dba_temp_files 临时数据文件信息
dba_rollback_segs 回滚段信息
dba_ts_quotas 用户表空间配额信息
dba_free_space数据库空闲空间信息
dba_profiles 数据库用户资源限制信息
dba_sys_privs 用户的系统权限信息
dba_tab_privs用户具有的对象权限信息
dba_col_privs用户具有的列对象权限信息
dba_role_privs用户具有的角色信息
dba_audit_trail审计跟踪记录信息
dba_stmt_audit_opts审计设置信息
dba_audit_object 对象审计结果信息
dba_audit_session会话审计结果信息
dba_indexes用户模式的索引信息
user_开头
user_objects 用户对象信息
user_source 数据库用户的所有资源对象信息
user_segments 用户的表段信息
user_tables 用户的表对象信息
user_tab_columns 用户的表列信息
user_constraints 用户的对象约束信息
user_sys_privs 当前用户的系统权限信息
user_tab_privs 当前用户的对象权限信息
user_col_privs 当前用户的表列权限信息
user_role_privs 当前用户的角色权限信息
user_indexes 用户的索引信息
user_ind_columns用户的索引对应的表列信息
user_cons_columns 用户的约束对应的表列信息
user_clusters 用户的所有簇信息
user_clu_columns 用户的簇所包含的内容信息
user_cluster_hash_expressions 散列簇的信息
v$开头
v$database 数据库信息
v$datafile 数据文件信息
v$controlfile控制文件信息
v$logfile 重做日志信息
v$instance 数据库实例信息
v$log 日志组信息
v$loghist 日志历史信息
v$sga 数据库SGA信息
v$parameter 初始化参数信息
v$process 数据库服务器进程信息
v$bgprocess 数据库后台进程信息
v$controlfile_record_section 控制文件记载的各部分信息
v$thread 线程信息
v$datafile_header 数据文件头所记载的信息
v$archived_log归档日志信息
v$archive_dest 归档日志的设置信息
v$logmnr_contents 归档日志分析的DML DDL结果信息
v$logmnr_dictionary 日志分析的字典文件信息
v$logmnr_logs 日志分析的日志列表信息
v$tablespace 表空间信息
v$tempfile 临时文件信息
v$filestat 数据文件的I/O统计信息
v$undostat Undo数据信息
v$rollname 在线回滚段信息
v$session 会话信息
v$transaction 事务信息
v$rollstat 回滚段统计信息
v$pwfile_users 特权用户信息
v$sqlarea 当前查询过的sql语句访问过的资源及相关的信息
v$sql 与v$sqlarea基本相同的相关信息
v$sysstat 数据库系统状态信息
all_开头
all_users 数据库所有用户的信息
all_objects 数据库所有的对象的信息
all_def_audit_opts 所有默认的审计设置信息
all_tables 所有的表对象信息
all_indexes所有的数据库对象索引的信息
session_开头
session_roles 会话的角色信息
session_privs 会话的权限信息
index_开头
index_stats 索引的设置和存储信息
伪表
dual 系统伪列表信息

 

 

多表联查 1. 内䘎接（INNER JOIN）：只有䘎接列上值相等的记录才会被作为查询结果返 回。 SELECT select_list FROM table1 [INNER] JOIN table2 ON table1. column = table2.column WHERE conditions 或： SELECT select_list FROM table1 JOIN table2 USING（column1, column2…）; 注：using使用时，两个表中相互联系的列，必享具有相同的数据类型和相同 的属性名 例如： 两表联查： SELECT * FROM teacher JOIN diploma ON teacher.t_diplomaid = diploma.diploma_id; 三表联查： SELECT b_id,b_name,a_name,t_name FROM book INNER JOIN BOOKTYPE ON book.B_TYPE_ID=booktype.T_ID INNER JOIN author ON (book.b_author=author.a_id); 2. 外䘎接：左外，右外，全外。“(+)”䜭要放在没有匹䝽记录列值就被设置为空值 的表的一端，+放在䘎接条件䜘分 （+）的例子：Select t_name,b_location from teacher join banji on b_teacher_id(+)=t_id; 外䘎接的语法： 左外： SELECT select_list FROM table1 LEFT [OUTER] JOIN table2 ON table1.column = table2.column; 右外： SELECT select_list FROM table1 RIGHT [OUTER] JOIN table2 ON table1.column = table2.column; 全外：（区别于笛卡尔积） SELECT select_list FROM table1 FULL [OUTER] JOIN table2 ON table1.column = table2.column; 21. 自然䘎接：两个表中必享含有同名的属性，效果和内䘎接一样 SELECT select_list FROM table1 NATURAL JOIN table2; 例如：SELECT * FROM course NATURAL JOIN teacher; 2. 笛卡尔积和交叉䘎接 SELECT select_list FROM table1 CROSS JOIN table2; 与全外䘎接的区别： 全外䘎接是在结果中䲔了显示满足䘎接的条件的行外，还显示了join两侧表中 所有满足检索条件的行。 交叉䘎接返回䘎接表中所有数据行的笛卡尔积，其结果䳶合中的数据行数等于 第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行 数   集运算 一。并集运算：UNION|UNION ALL 对两个或两个以上的结果集进行并集运算需要使用UNION或UNION ALL运算符。 UNION与UNION ALL的差别是前者会自动去掉并集的重复记录，而后者不会。 例如：使用UNION运算符查询工商083班语文成绩大于120分或外语成绩大于130分 的学生信息。 SELECT s_id, s_name, s_classname, s_language, s_chinese, s_foreign FROM student WHERE s_classname = '工商083' AND s_chinese >= 120 UNION SELECT s_id, s_name, s_classname, s_language, s_chinese, s_foreign FROM student WHERE s_classname = '工商083' AND s_foreign >= 130; 结果如下： UNION: UNION ALL： 二。交集运算：INTERSECT 对两个或两个以上的结果集进行交集运算需要使用INTERSECT运算符。 例如：使用INTERSECT运算符查询工商083班语文成绩大于120分且外语成绩大于 130分的学生信息。 SELECT s_id, s_name, s_classname, s_language, s_chinese, s_foreign FROM student WHERE s_classname = '工商083' AND s_chinese >= 120 INTERSECT SELECT s_id, s_name, s_classname, s_language, s_chinese, s_foreign FROM student WHERE s_classname = '工商083' AND s_foreign >= 130; 结果如下： 2三。 差集运算：MINUS 对两个或两个以上的结果集进行差集运算需要使用MINUS运算符。差集只返回第一 个查询结果集的行，如果在第二个查询结果中也存在相同的行，则差集运算返回的 结果中将不包含这些行。      子查询语句 一。子查询出现的位置： 1. 子查䈒出现在where子句䟼 例：查䈒和射䴅英䳴传相同作者的所有图书 SELECT b_name FROM book WHERE b_author=(SELECT b_author FROM book WHERE b_name='射䴅英䳴传'); 例：查䈒与学号为“0807070105”的学生在相同班级且政治䶒䊼相同的学生的学 号、姓名、班级和政治䶒䊼 SELECT s_name, s_classname, s_political FROM student WHERE (s_classname, s_political) =(SELECT s_classname, s_political FROM student WHERE s_id = '0807070105'); 2. 子查䈒出现在from子句䟼 例：实现top功能 SELECT rownum,y.* FROM (SELECT s_name,s_math FROM student ORDER BY s_math DESC ) y WHERE rownum<=2 ORDER BY ROWNUM DESC; 3. 子查䈒出现在select子句䟼 例：在SELECT子句中使用子查䈒，统䇑教师和学生的数䟿。 SELECT (SELECT COUNT() FROM teacher) AS 教师数量, (SELECT COUNT() FROM student) AS 学生数䟿 FROM dual; 二。子查询中使用的运算符 1. IN和NOT IN：在XXX范围内 例：查䈒所有女性作者的图书 SELECT b_name FROM book WHERE b_author IN(SELECT a_id FROM author WHERE a_sex='女'); 例：查䈒外䈝成绩大于等于120分的学生所在班级班䮯的学号、姓名和所在班 级。 SELECT s_id, s_name, s_classname FROM student WHERE s_classname IN (SELECT DISTINCT s_classname FROM student WHERE s_foreign >= 130) AND s_duty = '班䮯'; 2. ANY或SOME：任意一个 Any关䭞字之前必享使用一个单行操作符，如 =,>,>=,<.<= <> 例：查䈒价格大于任意一个类别平均价格的图书 2SELECT b_name,b_price FROM book WHERE b_price > ANY (SELECT AVG(b_price) FROM book GROUP BY b_type_id); 3. ALL： ALL操作符，将一个值与一个列表中的所有值䘋行比䖳，䘉个值䴰㾱匹䝽列表 中的所有值，然后将满䏣条件的数据䘄回。 例：查䈒价格大于所有类别平均价格的图书 SELECT b_name,b_price FROM book WHERE b_price > ALL (SELECT AVG(b_price) FROM book GROUP BY b_type_id); 4. EXISTS和NOT EXISTS 例：查䈒类别为abc的图书是否存在 SELECT B_name,b_type_id FROM book WHERE EXISTS (SELECT t_id FROM booktype WHERE t_name='abc'); 三。子查询出现在insert，delete，update中 1. insert䈝句 例：创建一个新表test，然后向将教师表中的女教师的id和姓名全䜘插入到test 表中。 CREATE TABLE TEST111(t_id VARCHAR2(20), t_name VARCHAR2(20)); INSERT INTO test111 (SELECT t_id,t_name FROM teacher WHERE t_gender='女'); 2. update䈝句 例：将学号为“0807070115”的学生的外䈝成绩修改为所有学生外䈝成绩的平均 值。 UPDATE student SET s_foreign = (SELECT AVG(s_foreign) FROM student)WHERE s_id = '0807070115'; 3. delete䈝句 例：删䲔学生表中学习委员的信息。 DELETE FROM student WHERE s_id IN (SELECT s_id FROM student WHERE s_duty = '学习委员');