Oracle数据库从入门到精通,带你轻松入门!

oracle 专栏收录该内容
1 篇文章 0 订阅

1.什么是数据库?

数据库(database,简称DB):用于存放数据的仓库。

数据库管理系统(Database Management System,DBMS):指数据库系统中对数据进行管理的软件系统。

数据库管理员(database administrator,DBA):是负责对数据进行规划、设计、协调、维护和管理的人员。

2.什么是Oracle?

Oracle Database,简称Oracle。是美国ORACLE(甲骨文)公司的一款对象关系型的数据库管理系统(ORDBMS)。目前在数据库市场上占有主要份额。

3.Oracle的优点?

  • 性能优越,大型数据库中的典范
  • 对象关系型的数据库管理系统(ORDBMS)
  • 在数据安全性与数据完整性控制方面性能优越
  • 跨操作系统,跨硬件平台的数据互操作能力
  • 应用广泛,在管理信息方面,企业数据处理,因特网及电子商务等领域使用非常广泛
  • 支持多用户,大事务量的事务处理
  • 可移植性好

4.Oracle数据库

Oracle数据库:相关的操作系统文件(即存储在计算机硬盘上的文件)集合,这些文件组织在一起,成为一个逻辑整体,即为Oracle数据库。

Oracle数据库作用:数据库用来存储数据的集合,Oracle用它来存储和管理相关的信息,注意数据库必须要与内存里的实例合作,才能对外提供数据管理服务。

在这里插入图片描述

5.Oracle实例

Oracle实例:位于物理内存里的数据结构,它由操作系统的多个后台进程和一个共享的内存池所组成,共享的内存池可以被所有进程访问。

Oracle实例 = 进程 + 进程所使用的内存[SGA(System Global Area)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Bk0frogh-1604889345586)(F:\oracle\oracle2.PNG)]

6.Oracle实例和Oracle数据库的关系

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Av8lzpa3-1604889345588)(F:\oracle\oracle3.PNG)]

注意:

  • 可以由一个或多个实例访问一个数据库,但是一个实例一次只能访问一个数据库。
  • Oracle的实例就是Oracle的进程和内存,数据库就是保存数据的物理文件。

7.Oracle常用SQL PLUS命令

命令作用操作示例
Connect切换连接用户,简写connconn 用户名/密码
Show user显示当前登录用户show user
Host <dos命令>执行操作系统命令host mkdir d:/testoracle:创建文件夹
Spool导出记录到文本1.spool d:\testoracle\test.txt:确认文件导入位置
2.spool off:确认导入到文件中
Clear screen清屏clear screen
Start d:\test.sql执行文件系统中的SQL语句(注:start命令等同于@,即:@d:\test.sq;)start d:\test.sql
Desc显示表结构desc 表名
Show error显示错误信息show error
exit退出exit

8.系统用户说明

  • sys:是个超级用户,用于的权限最大,可以完成数据库的所有管理任务
  • system:没有sys的权限大,通常用来创建一些用于查看管理信息的表和视图,但不建议使用system架构来创建一些与管理无关的表和视图
  • sysman:是Oracle数据库中用于EM管理的用户,如果你不用该用户,也可以删除。
  • scott:是Oracle提供的示例用户,里面有一些供初学者学习操作的数据表(emp,dept,salgrade,bonus)

注意:sys和system在登录Oracle工具时,sys只能以系统管理员(sysdba)或系统操作员(sysoper)的权限登录,而system可以直接登录(normal)

9.Oracle用户登录

SQLPLUS中

{<username>[/<password>][@<connect_identifier>]/}[as {sysdba|sysoper}]
# username/password:指定数据库账户用户名和密码
# connect_identifier:数据库连接的连接标识符(服务器名)。如果没有连接标识符,SQL PLUS将连接到默认数据库
# sysdba、sysoper 选项是数据库管理权限
# sysdba:数据库管理员的权限
# sysoper:数据库操作员的权限

sysdba是管理员Oracle实例,它的存在不依赖于整个数据库完全启动了,它已经存在,以sysoper身份登录,装载数据库、打开数据库,只有数据库打开了或者说数据库完全启动后,dba角色才有了存在的基础!

sys语法使用:

SQL> conn sys/密码 [@orcl] as sysdba
已连接。
--注意: 密码安装时未设置,随便输入密码都可以,但一定要身份登录
SQL> conn /as sysdba
已连接。
--注意:也可以登入,默认是sys身份

system语法使用:

SQL> conn system/密码 [@orcl] [as sysdba]
已连接。
--注意: 当system使用sysdba登入时,实际上是sys登录,可以使用命令show user查看到!

只进入sqlplus而不连接数据库:

SQL> sqlplus /nolog

启用sccot用户

给用户解锁

当用户连接数据库出现一下情况时,怎么办?

SQL> conn scott/tiger;
ERROR:
ORA-28000: the account is
警告: 您不再连接到 ORACLE

解决方案如下:

--语法规则
alter user username account unlock;
--操作实例:注意前提条件,使用sys或者system身份登录,才能行使权限
alter user scott account unlock;
--使用scoot用户连接数据库
conn scott/tiger;
--查看表名
select table_name from user_tables;

10.表空间

1.什么是表空间?

  • 数据库与表空间

    • 表空间实际上就是数据库上的逻辑存储结构,可以把表空间理解为在数据库中开辟的一个空间,用于存储我们数据的对象,一个数据库可以由多个表空间构成
  • 表空间与数据文件

    • 表空间实际上是由一个或多个数据文件构成,数据文件的位置和大小可以由我们用户自己定义,我们所操作的一些表,一些其他的数据对象都是存放在数据文件里的,那么数据文件是物理存储结构,真正可以看到的,而表空间是逻辑存储结构

在这里插入图片描述

2.表空间的分类

  • 永久表空间
  • 临时表空间
  • UNDO表空间

3.创建表空间

--语法格式:
create [temporary] TABLESPACE tablespace_name TEMPFILE|DATAFILE 'XX.dbf' SIZE XX;

--操作示例:
--创建一张永久表空间,位置默认
create tablespace test1_tablespace datafile 'test1file.dbf' size 10m;
--查看永久表存在的位置,如果报未选定行错误,则表空间名需要大写
select file_name form dba_data_files where tablespace_name = 'TEST1_TABLESPACE';
--创建一张临时表空间,位置默认
create temporary tablespace temptest1_tablespace tempfile 'tempfile1.dbf' size 10m;
--查看临时表存在的位置,如果报未选定行错误,则表空间名需要大写
select file_name from dba_temp_files where tablespace_name = 'TEMPTEST1_TABLESPACE';

11.Oracel用户管理

a.创建用户

--语法格式:
create user <user_name> identified by <password> default tablesapce <default tablespace> temporary tablespace <temporary tablespace>;

--操作示例:
create user qzp identified by qzpwjw6015 default tablespace test1_tablespace temporary tablespace temptest1_tablespace;

b.查看创建用户的方式

--语法格式:
select username from dba_users;
--注意:此命令是查看所有用户

c.给创建的用户授权

--语法格式:
grant 权限 to 用户名;
--操作示例:
--注意:前提条件是sys或者system用户登录
grant connect to qzp;
授权成功-- 成功授权了可以连接数据库的权限

d.管理用户

--连接用户
connect username/password;
--更改密码
alter user username identified by newpassword;
--锁定用户而不删除其用户
alter user username account lock;
--删除用户
drop user username;--这个用户下没有任何对象才可以使用,否则报错
drop user username cascade;--删除这个用户以及这个用户下的所有对象
# 注意:加上cascade则将用户连同其创建的东西全部删除

12.Oracle的角色管理

1.什么是角色?

  • Oracle角色(role)就是一组权限(或者说是权限的集合)
  • 用户可以给角色赋予指定的权限,然后将角色赋予给相应的用户

2.三种标准的角色

(1)connect(连接角色)
  • 拥有Connect权限的用户只可以登录Oracle,不可以创建实体,不可以创建数据库结构
(2)resource(资源角色)
  • 拥有Resource权限的用户只可以创建实体,不可以创建数据库结构
(3)dba(数据库管理员角色)
  • 拥有全部权限,是系统最高权限,只有DBA才可以创建数据库结构

注意:对于普通用户,授予connect,resource权限;对于DBA管理用户,授予dba权限。

3.创建角色

--语法格式:
create role rolename;
--操作示例:
create role manager;

4.为角色赋予权限

--语法格式:
grant 权限 TO rolename;
--操作示例:
grant create table,create view to manager;

5.将角色赋予给用户

--语法格式:
grant 角色 to 用户;
--操作示例
grant manager to user01,user02;

6.撤销赋予用户的权限

--语法格式:
revoke 角色 from 用户;
--操作示例
revoke manager from user01,user02;

7.删除角色

--语法格式:
drop role rolename;
--操作示例
drop role rolename;

13.Oracle的权限管理

权限的作用

  • 数据库的安全性,即为系统安全性,数据安全性

权限的分类

  • 系统权限:允许用户执行特定的数据库动作,如创建表、创建索引、连接实例等。
  • 对象(实体)权限:允许用户操纵一些特定的对象,如读取视图、可更新某些列、执行存储过程等

系统权限

--查看Oracle所有系统权限
select * from SYSTEM_PRIVILEGE_MAP;


--常用的系统权限如下:
CREATE SESSION	 --创建会话
CREATE SEQUENCE	 --创建序列
CREATE TABLE	 --创建会话
CREATE USER		 --创建用户
ALTER USER 		 --更改用户
DROP USER		 --删除用户
CREATE VIEW 	 --创建视图

--授权系统权限的语法格式:
grant privilege1[,privilege2,...] to user[,user|role,public] [with grant option];
--public :所有用户都有的角色
--with grant option:使用户同样具有其它分配权限的权利,可将此权限授予给别人

注意:

1).如果使用with grant option为某个用户授予系统权限,那么对于被这个用户授予相同权限的所有用户来说,取消该用户的系统权限并不会级联取消这些用户的相同去权限。

2).系统权限无级联,即A授权予B权限,B授权予C权限,如果A收回B的权限,C的权限不会受影响;系统权限可以跨用户回收,即A可以直接收回C用户的权限。

对象权限

--查看Oracle所有对象权限
select * from table_privilege_map;

--常用的对象权限如下:
select --查询
update --更新
insert --插入
delete --删除
all	   --所有对象权限

--授予对象权限的语法格式:
grant object_priv|all [(columns)] on object to {username|rolename,public} [with grant option];
--object_priv:对象权限
--public :指授予给所有的用户
--with grant option :允许用户再次给其它用户授权
--操作示例:
grant select,update,insert on scott.emp to manager;--授权给角色/用户可以对scott.emp表进行查、改、插入
grant manager to user03;--将角色授权给用户
grant all on scott.emp to user04;
grant update(sal,mgr) on scott.emp to test;

--回收对象权限的语法格式:
revoke {object_privilege1 [,object_privilege2,....]|ALL} ON object from {username|rolename|public}
--操作示例:
revoke all on scott.emp form user04;

14.Oracle的表空间管理

1.查看用户的表空间

相关的数据字典:

  • dba_tablespaces:管理员级别
  • user_tablespaces:普通用户
--查看系统级别的表空间名字
select tablespace_name from dba_tablespaces;
--查询结果:
TABLESPACE_NAME
----------------------------
SYSTEM --系统的表、视图等存放处,称为系统表空间
SYSAUX --是example的辅助表空间,称为索引表空间
UNDOTBS1 --用户放入撤销信息的表空间,称为回滚表空间
TEMP --临时表空间
USERS --用于存储数据库用户创建的数据库对象,称为用户表空间
EXAMPLE --存放oracle11g的实例的表空间
TEST1_TABLESPACE --自己创建的表空间
TEMPTEST1_TABLESAPCE --自己创建的表空间

--查看普通用户的表空间名字
select tablespace_name from user_tablespaces;
--查询结果:
TABLESPACE_NAME
----------------------------
SYSTEM --系统的表、视图等存放处,称为系统表空间
SYSAUX --是example的辅助表空间,称为索引表空间
UNDOTBS1 --用户放入撤销信息的表空间,称为回滚表空间
TEMP --临时表空间
USERS --用于存储数据库用户创建的数据库对象,称为用户表空间
EXAMPLE --存放oracle11g的实例的表空间
TEST1_TABLESPACE --自己创建的表空间
TEMPTEST1_TABLESAPCE --自己创建的表空间

注意:普通用户无法查看系统级别的表空间

2.查看系统用户的表空间

相关的数据字典:

  • dba_users:管理员用户级别
  • user_users:普通用户级别
--查看system用户默认表空间和临时表空间信息
select default_tablespace,temporary_tablespace from dba_users where username = 'SYSTEM';
--查看所有系统用户默认表空间和临时表空间信息
select username,default_tablespace,temporary_tablespace from dba_users;

3.设置用户默认或临时表空间

--语法格式:
alter user username default|temporary tablespace tablespace_name;
--操作示例:
alter user user01 default tablespace test1_tablespace temporary tablespace temptest1_tablespace;

4.修改与删除表空间

1.设置联机或脱机状态

特别说明:如果一个表空间设置成脱机状态,表示该表空间暂时不让访问,设置成脱机状态不是删除,当我们需要使用该表空间时还可以将其设置成联机状态,正常使用。

--语法格式:
alter tablespace tablespace_name online|offline;
--操作示例:
--修改表空间 test1_tablespace 为脱机状态
alter tablespace test1_tablespace offline;
--查看修改只有的 test1_tablespace 的状态
select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE';

2.设置只读或可读写状态

特别说明:默认是可读可写状态

--语法格式:
alter tablespace tablespace_name read only|read write;
--操作示例:
--修改为只读
alter tablespace test1_tablespace read only;
--查看修改只有的 test1_tablespace 的状态,状态是read only
select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE';
--修改为可读可写
alter tablespace test1_tablespace read write;
--查看修改只有的 test1_tablespace 的状态,状态是online
select status from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE';

3.增加数据文件

说明:向创建好的表空间里增加数据文件

--语法格式:
alter tablespace tablespace_name add datafile 'filename,dbf' size xx;
--操作示例:
--添加一个test2_datafile.dbf的文件到test1_tablespace表空间中
alter tablespace test1_tablespace add datafile 'test2_datafile.dbf' size 10m;
--查看test1_tablespace表空间中的数据文件
select file_name from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE';

4.删除数据文件

说明:不能删除表空间中的第一个创建的数据文件,如果需要删除的话,我们需要把整个的表空间删掉。

--语法格式:
alter tablespace tablespace_name drop datafile 'filename,dbf';
--操作示例:
--添加一个test2_datafile.dbf的文件到test1_tablespace表空间中
alter tablespace test1_tablespace drop datafile 'test2_datafile.dbf';
--查看test1_tablespace表空间中的数据文件
select file_name from dba_tablespaces where tablespace_name = 'TEST1_TABLESPACE';

5.删除表空间

--语法格式:
drop tablespace tablespace_name [including contents];
--操作示例:
--删除test1_tablespace表空间
drop tablespace test1_tablespace;

说明:

若果删除掉表空间,而不删除表空间中的数据文件,则执行如下命令:

drop tablespace tablespace_name;

若果删除掉表空间及其表空间中的数据文件,则执行如下命令:

drop tablespace tablespace_name including contents;

15.Oracle的SQL

(1)SQL的定义

  • 结构化查询语言(Structured Query Language)简称SQL
  • 是一种特殊的目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统的语言

(2)SQL的分类

  • DDL(Data Definition Language)—数据定义语言
操作解释
create table创建数据库表
create index创建数据库表的索引
drop table删除数据库表
drop index删除数据库表的索引
truncate删除表的所有行
alter table更改表结构,增删改
alter table add consraint在已有的表上增加约束
  • DML(Data Manipulation Language)—数据操纵语言
操作解释
select查询数据
insert添加数据到数据库中
delete删除数据库中表数据
update修改数据库中的数据
  • DCL(Data Control Language)—数据库控制语言
操作解释
grant将权限或角色授予用户或其他角色(授予访问权限)
revoke从用户或数据库角色回收权限(撤销访问权限)
lock对数据库的特定部分进行锁定
  • TCL(Transaction Control Language)—事务控制语言
操作解释
commit提交事务处理
rollback事务处理回退
savepoint设置保存点

(3)字段数据类型

类型含义
CHAR(length)存储固定长度的字符串。参数length指定了长度,如果存储的字符串长度小于length,用空格填充。默认长度是1,最长不超过2000字节。
VARCHAR2(length)存储可变长度的字符串。length指定了该字符串的最大长度。默认长度是1,最长不超过4000字符。
NUMBER(p,s)既可以存储浮点数,也可以存储整数等数值类型,p表示数字的最大位数(如果是小数包括整数部分和小数部分,p默认是38位),s是指小数位数。
CLOB
(Character LOB)
能储存大量字符
BLOB
(Binary LOB)
可存储较大的二进制对象,如图形、视频剪辑和声音
DATE存储日期和时间,存储纪元、4位年、月、日、时、分、秒,存储时间从公元前4712年1月1日到公元后4712年12月31日

操作示例:

number(5,0) --最多可以存储五位整数
number(5,2) --最多可以存储五位数,但最大可以存储999.99的浮点数

(4)SQL创建表语法

1.创建表

语法规则:

CREATE TABLE 表名(
columm_name datatype,...
);

操作示例

-- 创建一个学生表(student_1),表中包括学号(sno)、姓名(sname)、性别(ssex)、年龄(sage)、出生日期(sbirthday)
CREATE TABLE student_1(
sno VARCHAR2(10),
sname VARCHAR2(30),
ssex VARCHAR2(2),
sage NUMBER(3),
sbirthday DATE
);

2.表约束

  • oracle中提供的自动保持数据库完整性的一种方法,它通过限制字段中数据、记录中数据和表之间的数据来确保数据的完整性

完整约束基础语法

[constraint constraint_name(约束名)] <约束类型>

说明:约束不指定名称时,系统会给定一个名称

3.约束分类

  • 主键约束(primary key constraint)
  • 唯一性约束(unique constraint)
  • 默认约束(defatut constraint)
  • 非空约束(not null constraint)
  • 检查约束(check constraint)
  • 外键约束(foreign key constraint)

4.约束类型作用

(1).主键约束
  • 用于定义基本表的主键,它是唯一确定表中每一条记录的标识符,其值不为空,也不重复,每个表中只能有一个主键,但可由多列构成。如:PRIMARY KEY(科目编号,科目名称)

操作示例:

方式一:创建表时添加主键

CREATE TABLE student_1(
sno VARCHAR2(10),
sname VARCHAR2(30),
ssex VARCHAR2(2),
sage NUMBER(3),
sbirthday DATE,
constraint sno_pk primary key(sno)
);

方式二:修改表添加主键约束

alter table student_1 add constraint sno_pk primary key(sno);
(2).非空约束
  • NOT NULL 约束用于确保当前列值不能为NULL,如果在列上定义了NOT NULL约束,那么当插入数据时,必须为该列提供数据;当更新列数据时,不能将其值设置为NULL
  • 非空(not null)约束是列级约束

扩展知识:列级约束与表级约束

  • 列级约束
column [constraint constraint_name] constraint_type

说明:列级约束必须跟在列的定义后面

  • 表级约束
column,
[constraint constraint_name] constraint_type

说明:表级约束不与列在一起,而是单独定义的

操作示例:

方式一:创建表时添加约束

--创建学生表
CREATE TABLE "student"(
"sid" NUMBER(8,0),
"sname" VARCHAR2(20) NOT NULL,
"ssex" CHAR(2) constraint st_sex not null,
"birthday" DATE,
CONSTRAINT S_ID_PK PRIMARY KEY(S_ID)
);

方式二:修改表添加约束

--语法格式:
alter table table_name add [constraint constraint_name] constraint_type (column);
--操作示例:
alter table table_name modify "sname" not null;

删除约束的方式

  • 将约束无效化或激活
--语法规则
ALTER TABLE 表名 DISABLE|ENABLE CONSTRAINT 约束名;
--示例:
ALTER TABLE COURSE DISABLE CONSTRAINT C_ID_PK;
ALTER TABLE COURSE ENABLE CONSTRAINT C_ID_PK;
  • 彻底删除约束
-- 方法一:
ALTER TABLE 表名 DROP CONSTRAINT 约束名;
-- 删除主键约束示例
ALTER TABLE COURSE DROP CONSTRAINT C_ID_PK;
-- 注意:要删除的主键约束必须起名

-- 方法二:
ALTER TABLE 表名 DROP PRIMARY KEY;
-- 删除主键约束示例
ALTER TABLE COURSE DROP PRIMARY KEY;
-- 注意:要删除的主键约束没有起别名,选用此方式
  • 删除非空约束的方式
--语法规则:
alter table table_name modify column_name [datatype] null;
--操作示例:数据类型可以不写
ALTER TABLE COURSE MODIFY C_NAME NULL;
(3).唯一性约束
  • 用于指定一个或者多个列的组合值具有唯一性,以防止在列中输入重复的值

唯一性约束的注意事项

  • 使用唯一性约束的列允许为空值
  • 一个表中可以允许有多个唯一性约束
  • 可以把唯一性约束定义在多个列上

方式一:创建表时添加唯一性约束

--创建学生表
CREATE TABLE STUDENT(
S_ID NUMBER(8,0) CONSTRAINT S_ID_PK PRIMARY KEY,
S_NAME VARCHAR2(20) NOT NULL,
S_SEX CHAR(2),
BIRTHDAY DATE,
ADDRESS VARCHAR2(50),
EMAIL VARCHAR2(50) UNIQUE,
CARDID VARCHAR2(18),
CONSTRAINT S_CARDID_PK UNIQUE(CARDID)
);

方式二:修改表添加唯一性约束

ALTER TABLE STUDENT ADD CONSTRAINT S_CARDID_PK UNIQUE(CARDID);

删除唯一性约束

--禁用约束
ALTER TABLE STUDENT DISABLE CONSTRAINT S_CARDID_PK;
--彻底删除
ALTER TABLE STUDENT DROP CONSTRAINT S_CARDID_PK;
(4).检查约束
  • 对输入列或者整个表的值设置检查条件以限制输入值,保证数据库的完整性

方式一:创建表时添加检查约束

--创建学生表
--1.列级约束
CREATE TABLE STUDENT(
S_ID NUMBER(8,0),
S_NAME VARCHAR2(20) NOT NULL,
S_SEX CHAR(2) CHECK(S_SEX='男' OR S_SEX='女'),
BIRTHDAY DATE,
ADDRESS VARCHAR2(50),
EMAIL VARCHAR2(50),
CARDID VARCHAR2(18)
);

--2.表级约束
CREATE TABLE STUDENT(
S_ID NUMBER(8,0),
S_NAME VARCHAR2(20) NOT NULL,
S_SEX CHAR(2),
BIRTHDAY DATE,
ADDRESS VARCHAR2(50),
EMAIL VARCHAR2(50),
CARDID VARCHAR2(18),
CONSTRAINT CK_SEX CHECK(S_SEX='男' OR S_SEX='女')
);

方式二:修改表添加检查约束

ALTER TABLE STUDENT ADD CONSTRAINT CK_SEX CHECK(S_SEX='男' OR S_SEX='女') ;

删除唯一性约束

--禁用约束
ALTER TABLE STUDENT DISABLE CONSTRAINT CK_SEX;
--彻底删除
ALTER TABLE STUDENT DROP CONSTRAINT CK_SEX;
(5).外键约束
  • 用于加强两个表数据之间的链接的一列或多列,是唯一涉及两个表关系的约束

外键约束的注意事项:

  • 设置外键约束时主表的字段必须是主键列(或唯一列)
  • 主表从表相应字段必须是同一数据类型
  • 从表中外键字段的值必须来自主表的相应字段的值,或者为NULL值。

方式一:创建表时添加外键约束

--列级约束
CREATE TABLE 从表 (Column_name datatype REFERENES)主表 (Column_name) [ON DELETE CASCADE],...);
--级联删除
ON DELETE CASCADE
--主表
CREATE TABLE DEP (
D_ID VARCHAR2(10) PRIMARY KEY,
D_NAME VARCHAR2(30)
);
--插入数据
INSERT INTO DEP VALUES ('744','计算机系');
--从表
CREATE TABLE STUDENT(
S_ID NUMBER(8,0),
S_NAME VARCHAR2(20),
SEX CHAR(2),
SD_ID VARCHAR2(10) REFERENCES DEP(D_ID)
ON DELETE CASCADE
);

--表级约束
CREATE TABLE 约束名 FOREIGN KEY REFERENES 主表 (Column_name) [ON DELETE CASCADE];
--从表
CREATE TABLE STUDENT(
S_ID NUMBER(8,0),
S_NAME VARCHAR2(20),
SEX CHAR(2),
SD_ID   VARCHAR2(10), 
CONSTRAINT fk_cid FOREIGN KEY(SD_ID) REFERENCES DEP(D_ID)
ON DELETE CASCADE
);

方式二:修改表添加约束

ALTER TABLE student ADD CONSTRAINTS fk_cid  FOREIGN KEY(SD_ID) REFERENCES DEP(D_ID) ON DELETE CASCADE;

删除外键约束的方式

--禁用外键约束
ALTER TABLE STUDENT DISABLE CONSTRAINT fk_cid; 
--彻底删除
ALTER TABLE STUDENT DROP CONSTRAINT fk_cid; 

(5)SQL修改和删除表

1.添加列

--语法结构:
ALTER TABLE 表名 ADD 新增例名 数据类型;
--示例
ALTER TABLE STUDENT ADD  S_NUMBER CHAR(11);

2.修改列

--语法结构
ALTER TABLE 表名 MODIFY 列名 新数据类型;
--示例
ALTER TABLE STUDENT MODIFY  S_NUMBER CHAR(12);

3.删除列

--语法结构
ALTER TABLE 表名 DROP 列名 ;
--示例
ALTER TABLE STUDENT DROP COLUMN S_NUMBER;

4.重命名列

--语法结构
ALTER TABLE 表名 RENAME 列名 TO 新列名 ;
--示例
ALTER TABLE STUDENT RENAME COLUMN S_NUMBER TO S_TEL;

5.修改表名

--语法结构
RENAME 表名 TO 新表名;
--示例
RENAME STUDENT TO STU;

6.删除表

--删除表数据
TRUNCATE TABLE 表名;
--示例
TRUNCATE TABLE STU;
--删除表结构
DROP TABLE 表名;
--示例
DROP TABLE STU;

注意:TRUNCATE操作用于删除表中的全部数据,并不是把表删除掉,这种方式要比delete方式删除数据的速度要快,也叫截断表

(6)SQL的DML (增删改查)

1 添加信息 INSERT
#语法结构
#方式一:
INSERT INTO 表名 [(列1,列2,...,列N)] VALUES (值1,值2,...,值N);
#示例
INSERT INTO DEP(D_ID,D_NAME) VALUES ('794','计算机系');
#方式二:
INSERT INTO TABLE_NAME(COLUNMN1,COLUNMN2...) SELECT VALUE1,VALUE2... FROM DUAL;
注意:字段要一一对应,否则报错!
#方式三:
insert into tableName(column1(主键),column2,column3...)
    select tableNames_seq.nextval,,column2,column3... from (
    select value1 column2,value2 column3,value3 column4 from dual
    union
    select value1 column2,value2 column3,value3 column4 from dual
    union
    select value1 column2,value2 column3,value3 column4 from dual
    union
    select value1 column2,value2 column3,value3 column4 from dual
)
2 查询信息 SELECT
#语法格式
SELECT * | COLUMN[,...] FROM 表名;
#示例
SELECT * FROM STU;
SELECT D_ID,D_NAME FROM DEP;
3 修改信息 UPDATE
#语法格式
UPDATE TABLE SET COLUMN=VALUE [,COLUMN=VALUE,...] [WHERE CONDITION];
#示例 更新全部数据
UPDATE STU SET S_ID='781';
#示例 更新条件 S_NAME='李四'的数据
UPDATE STU SET S_ID='781' WHERE S_NAME='李四';
4 删除信息 DELETE
#语法格式
DELETE FROM TABLE [WHERE CONDITION];
#示例 删除全部数据
DELETE FROM STU;
#示例 删除条件 S_ID='781'的数据
DELETE FROM STU WHERE S_ID='781';

(7)oracle事务

1.什么是事务?
  • 事务可以看作是由对数据库的若干操作组成的单元,这些操作要么完成,要么都取消,从而保证数据满足一致性的要求。
2.事务的组成?
  • 一条或者多条DML
  • 一条DDL
  • 一条DCL

DML 语句需要使用COMMIT提交事务或使用ROLLBACK回滚事务

DDL和DCL是自动提交事务的

3.为什么使用事务?
  • 保证数据的安全有效

当执行事务操作(DML语句)时,Oracle会在被作用表上加表锁,以防止其他用户改变结构;同时会在被作用行上加行锁,以防止其他事务在相应行上执行DM操作。

4.事务控制命令
  • 提交事务(COMMIT)

    • 通过COMMIT语句可以提交事务,当执行了COMMIT语句后,会确认事务的变化、结束事务、删除保存点,释放锁
    • 当使用COMMIT语句结束事务之后,其他会话将可以查看到事务变化的新数据。
  • 回滚事务:(ROLLBACK)

    • ROLLBACK只能对未提交的数据撤销,已经commit的数据是无法撤销的,因为comit之后已经持久化到数据库中了。
  • 保存点(SAVEPOINT)

    • 是事务中的一点,用于取消部分事务,当结束事务时,会自动删除该事务所定义的所保存的点。当执行ROLLBACK时,通过指定保存点可以退到指定的点。
    --设置保存点:
    SAVEPOINT a;
    --回滚部分事务:
    ROLLBACK TO a;
    --回滚全部事务:
    ROLLBACK;
    

(8).Oracle的数据字典

1.数据字典

  • 说明:数据字典是ORACLE存放有关数据库信息的地方,其用途是用来描述数据的。数据库数据字典是一组表和视图结构。
  • 数据字典中的表是不能直接被访问的,但是可以访问数据库字典中的视图。

2.数据字典的作用

  • 作用:通过访问数据字典,可查看数据库内部的详细信息,以解决遇到的问题。

3.Oracle中常用的数据字典

常用数据字典分为三类

user_*:
--该视图贮存了关于当前用户所拥有的对象的信息。
all_*:
--该视图贮存了当前用户能够访问的对象的信息。(不需要拥有该对象,有相应权限即可)
dba_*:
--该视图贮存了数据库所有的对象的信息。(前提是当前用户拥有访问这些数据库的权限,一般必须具有管理员权限)
数据字典的名称说明
user_users关于用户的信息
user_tablespaces关于表空间的信息
user_tables关于数据库表的信息
user_views关于视图的信息
user_sequences关于用户序列的信息
user_constraints关于用户表的约束信息
user_triggers关于用户的触发器信息
use_source关于用户储存过程的信息

应用数据字典查看相应的数据库信息

--查看当前用户下的用户信息
SELECT * FROM user_users;
--查看当前用户有权访问的所有用户的基本信息
SELECT * FROM all_users;
--数据库用户的所有信息
SELECT * FROM dba_users;

16.Oracle的基本查询语句

(1).基本查询

--语法格式
SELECT * | {[distinct]} column | expression [alias],...} FROM table [WHERE condition]
--解释
 table  --用于指定表名
 column --用于指定列名
 expression --用于指定表达式
 alias --用于指定列的别名
 condition --用于指定查询条件
 distinct--去除重复行
--示例
SELECT DISTINCT DEPTNO,JOB FROM EMP;

(2).排除重复行distinct

  • 默认情况下,当执行查询操作时,会显示所有满足条件的行,包括重复行。
select distinct deptno,job from emp;

(3).查询日期列

  • 日期列:指数据类型为日期类型的列,如DATE类型
  • 日期列的默认格式为DD-MON-RR(RR:代表年,而且只取年份的后两位数字)

日期格式中RR与YY的区别

RR指定日期当前年份RR格式说明RR结果年份YY格式说明YY结果年份
18-1月-122017指定年份在049之间<br>当前年份在049之间2012当前年份的前两位+指定日期的后两位2012
18-1月-812017指定年份在5099之间<br>当前年份在049之间1981(上一个世纪)同上2081
18-1月-122060指定年份在049之间<br>当前年份在5099之间2112(下一个世纪)同上2012
18-1月-812060指定年份在50~99之间
当前年份在50~99之间
2081同上2081

注意:RR中,指定日期中的年份和当前年份在049之间,表示结果年份和YY结果年份相同;指定日期中的年份在5099之间和当前年份在049之间,表示结果年份比YY结果早一个世纪;指定日期中的年份在049之间和当前年份在50~99之间,表示结果年份比YY结果晚一个世纪。

设置不同语言显示日期结果

--设置简体中文的日期格式
alter session set nls_language = 'SIMPLIFIED CHINESE';
select empno,ename,hiredate from emp;

--设置美国英语的日期格式
alter session set nls_language = 'AMERICAN';
select empno,ename,hiredate from emp;

--设置特定格式
alter session set nls_date_format='YYYY/MM/DD';
select empno,ename,hiredate from emp;

(3).算术运算符

运算符说明
+
-
*
/

算术运算符可应用在数字和日期列上

--示例
select empno,ename,sal,sal*12 from emp;

算术运算符的优先级:

  • 乘除具有相同的优先级,加减具有相同的优先级,相同优先级的运算符从左向右执行
  • 乘除的优先级大于加减,但有括号,不管括号外优先级多高,优先运算括号内

(4).算数运算中的空值NULL

NULL:表示未知值,它既不是空格也不是0。

(1).当算数表达式包含NULL时,其显示结果也为空(NULL)

--例:查询编号、雇员、工资、补助、实发工资的信息。
select empno, ename, sal, comm, sal+comm from emp;

扩展知识:空值与默认值

空值:当插入数据时,如果没有为特定列提供数据,并且该列没有默认值,那么其结果为NULL。

默认值:在创建表时可以使用default关键字为列设置默认值,在表中插入数据时,如果没有为该列提供数据,那么该列将使用默认值。

--示例
create table student2(
sid number(8,0) primary key,
name varchar2(10),
sex char(2) default '男',
age number(2,0) default 20,
address varchar2(50));
SELECT * FROM student2;
insert into student2(SID,NAME) values(20010005,'王五');
insert into student2 values(24010002,'王二',DEFAULT,21,'北京市昌平区');

--查看Oracle的字符编码集
select userenv('language') from dual;
select * from nls_database_parameters WHERE parameter ='NLS_CHARACTERSET'

Oracle字符集知识

Oracle字符集是一个字节数据的解释的符号集合,有大小之分,有相互的包容关系。ORACLE 支持国家语言的体系结构允许你使用本地化语言来存储,处理,检索数据。它使数据库工具,错误消息,排序次序,日期,时间,货币,数字,和日历自动适应本地化语言和平台

--影响Oracle数据库字符集最重要的参数是NLS_LANG参数。
--它的格式如下: 
NLS_LANG = language_territory.charset
--它有三个组成部分(语言、地域和字符集),每个成分控制了NLS子集的特性。
--其中:
--Language: 指定服务器消息的语言, 影响提示信息是中文还是英文
--Territory: 指定服务器的日期和数字格式,
--Charset:  指定字符集。
--如:AMERICAN _ AMERICA. ZHS16GBK
--从NLS_LANG的组成我们可以看出,真正影响数据库字符集的其实是第三部分

--查看数据库版本
select * from v$version;
--包含版本信息,核心版本信息,位数信息(32位或64位)等  至于位数信息,在Linux/unix平台上,可以通过file查看,如file $ORACLE_HOME/bin/oracle

--查看数据库字符集
--1.数据库服务器字符集
--方式一:
select * from nls_database_parameters;
--方式二:
select userenv('language') from dual;
--其来源于props$,是表示数据库的字符集。
--查看本地的字符集
select * from V$NLS_PARAMETERS

--2.客户端字符集
select * from nls_instance_parameters;
--其来源于v$parameter,表示客户端的字符集的设置,可能是参数文件,环境变量或者是注册表

--3.会话字符集
select * from nls_session_parameters;
--其来源于v$nls_parameters,表示会话自己的设置,可能是会话的环境变量或者是alter session完成,如果会话没有特殊的设置,将与nls_instance_parameters一致。

--4.dmp文件的字符集
select nls_charset_name(to_number('0354','xxxx')) from dual;

(5) .查询语句中的别名和连接符的应用

1.列的别名

  • 用于改变列的显示标题,列的别名可以跟在列名的后面,也可以在二者之间间加AS关键字

注意:如果别名中包含空格或特殊的字符或者需要区分大小写,那么需要给别名加上双引号

--不使用列名
select empno,ename,sal*12 from emp;
--使用列名
select empno "雇员编号", ename "雇员名", sal*12 "年收入" from emp;
select empno "雇员编号", ename "雇员名", sal*12  年收入 from emp;
select empno "雇员编号", ename "雇员名", sal*12 as 年收入 from emp;
select empno 雇员编号, ename 雇员名, sal*12 as 年收入 from emp;

2.连接符||

当执行查询结果操作时,为了显示更有意义的结果值,有时需要将多个字符串连接起来,连接字符串可以使用"||"操作符或者concat函数,把列与列,列与字符连接在一起,用“||”表示,可以用来合并列。

--举例:
select  ename||'的月工资是:'||sal||'岗位是:'||job as 雇员职位信息 from emp;

3.字符串

  • 字符串可以是select列表中的字符,数字,日期
  • 日期和字符只能在单引号中出现
  • 每当返回一行,字符串被输出一次
--错误示例
select ename || "1234" || sal from emp;
--正确示例
select ename || '1234' || sal from emp;

(6).过滤数据(Where 比较运算符)

  • 使用WHERE 子句,将不满足条件的行过滤掉
--语法规则:
SELECT * |{[DISTINCT] COLUMN | EXPRESSION [ALIAS],...} FROM TABLE [WHERE CONDITION(S)];

--数学值的情况
--说明:可直接引用数字值,也可以用单引号引注数学值
select * from emp where deptno=20;
select * from emp where deptno='20';

--字符值
--说明:使用字符值,必须给字符值加上单引号,字符值是区分大小写的
select * from emp where job='MANAGER';

--日期值的情况
--说明:使用日起值时,必须给日期值加上单引号,日期值必须符合日期语言和显示格式
select * from emp where hiredate = '02-4月-81';

比较运算符

运算符说明
=等于不是(==)
>大于
<小于
>=大于等于
<=小于等于
!=不等于<>
--操作示例
select * from emp where sal = 3000;
select * from emp where sal >= 3000;
select * from emp where sal < 3000;
select * from emp where sal <> 3000;
select * from emp where sal != 3000;
select * from emp where sal>1000 and sal<3000;
select * from emp where sal<1000 or sal>3000;

(7). 过滤数据(范围、模糊查询等空值)

1.BETWEEN…AND

  • 用于指定特定范围的条件,包含边界值
--用法
BETWEEN 较小值 AND 较大值
--示例
select empno,ename,sal from emp where sal between 1500 and 3000;
--等价于
select empno,ename,sal from emp where sal >= 1500 and sal<=3000;

2.IN

  • 执行列表匹配的操作,列或表达试结果匹配列表中的任意一个值
--语法用法
IN(值1,[值2, ...值n])
--示例
select empno,ename, job from emp where job in ('SALESMAN','MANAGER','CLERK');
--等价于
select empno,ename, job from emp where job ='SALESMAN' OR job ='MANAGER' OR job ='CLERK';

3.LIKE

  • 用于执行模糊查询,当执行模糊查询时,需要使用通配符%和_

通配符知识:

通配符作用
%用于表示0个或多个字符
_用于表示单个字符
--用法
LIKE 执行模糊查询。
--示例
--查询以名字J开头的雇员信息
select * from emp where ename like 'J%';
--查询名字第2-3个字母是AR的雇员信息
select * from emp where ename like '_AR%';
--模糊查询中特殊符号的处理
--如果要查询的字符本身就包含_和%,必须使用escape选项和转义符实现。escape指明"\"说转义字符
--回避特殊符号的方法:使用转义符。例如:将[%]转为[\%]、[_]转为[\],然后再加上[ESCAPE'\']即可。
INSERT INTO emp(empno,ename,job) VALUES (7309,'G_JJJ','CLERK');
--查询以名字以G_开头的雇员信息
select * from emp where ename like 'G\_%' escape '\';

4判断空值 IS NULL

--用法
IS NULL操作符用于检测列或表达式的结果是否为NULL,如果为NULL,则返回true,反之返回false。
判断列或表达式的结果是否为NULL,可用IS NULL 或IS NOT NULL,但是不能=NULL或!=null来判断。
--示例
select empno, ename, sal, comm from emp where comm is null;
select empno, ename, sal, comm from emp where comm is not null;

(8).过滤数据(逻辑运算)

操作符说明
AND逻辑与(并)
OR逻辑或
NOT逻辑否
  • 逻辑操作符 AND
--说明:当执行SQL操作时,如果SQL语句结果必须同时满足多个条件,那么需要使用逻辑操作符AND。
--示例
select empno,ename,job,deptno from emp where job='MANAGER' AND deptno= 10;
  • 逻辑操作符 OR
--说明:当执行SQL操作时,如果SQL语句结果只须同时满足多个条件中的任意一个,那么需要使用逻辑操作符OR。
--示例
select empno, ename, job, sal from emp where job='MANAGER' or sal>2000;
  • 逻辑操作符 NOT
--说明:当执行SQL操作时,如果SQL语句需要返回不满足特定条件的结果,那么需要使用逻辑操作符NOT。
--示例
select empno, ename,job from emp where job not in ('CLERK','SALESMAN','MANAGER');

逻辑操作符的优先级

NOT优先级最高,AND其次,OR优先级最低,如果需要改变优先级,需要使用括号。

select empno,ename,job,sal,deptno from emp where (sal > 2000 or deptno = 30) and job not in('PRESIDENT','MANAGER');

(9).排序数据ORDER BY

--语法规则:
SELECT *|column1[,column2,...] from table_name [where condition] [order by column [ASC|DESC]];
--ASC:升序  DESC:降序
注意:当select语句包含多个子句(where,group by,having,order by)时,order by 必须是最后一条语句。

--单列排序
--单列升序
--说明:如果排序列存在NULL行,那么NULL行会显示在最后面
select ename,sal from emp order by sal ASC;
select ename,sal from emp order by sal;(默认升序)

--单列降序
--说明:如果排序列存在NULL行,那么会显示在最前面
select ename,sal from emp order by sal DESC;

--多列排序
--说明:当执行排序操作时,首先按照第一列进行排序,当第一列存在相同的数据时,以第二列进行排序,以此类推。
select ename,empno,deptno,sal from emp order by depntno asc,sal desc;

--使用列别名排序
select empno,ename,sal*12 年收入 from emp ORDER BY 年收入;

(10)SQL语句与SQLPLUS命令

SQL

  • 一种语言,是关系数据库的基本操作语言,它是应用程序与数据库进行交互操作的接口,从而使得应用开发人员、数据库管理员、最终用户都可以通过SQL语言对数据库进行操作。
  • SQL语句不区分大小写,但关键字不能缩写
  • SQL语句必须用";"结束
  • alter user 用户名 identified by 新密码;

SQLPLUS:

  • 一种环境,是Oracle公司所提供的工具程序,是与oracle数据库进行交互的客户端工具,借助sqlplus工具可以查看、修改数据库记录,在sqlplus中,可以运行SQL语句。
  • SQLPLUS命令不区分大小写,但关键字能缩写,例如:connect->conn
  • SQLPLUS命令可以不用";"
  • passw[ord] [username],username用于指定用户名,注意:任何用户都可使用该命令修改其他自身口令,但如果要修改其他用户口令,则必须以DBA的身份登入(sys/system)

17.Oracle的函数

(1).SQL函数的定义

  • SQL函数是Oracle数据库内置函数,并且可用于各种SQL语句
  • SQL函数包括单行函数和多行函数

(2).单行函数分类

1.字符函数

  • 字符函数的输入参数为字符类型,其返回值是字符类型或数字类型
函数类型作用
大小写控制函数
UPPER(char)将字符串转换为大写格式
LOWER(char)将字符串转换为小写格式
INITCAP(char)将字符串中的每个单词的首字母大写
连接函数
CONCAT(str1,str2)将str1,str2进行拼接成一个新的字符串
截取函数
SUBSTR(char,m[n])用于截取字符串,char指定被截取的字符串,m用于指定从哪个位置开始截取,n用于指定截取字符串的长度,注意:m为-1,代表从尾部开始截取,m为大于0,则从首字符下标对应索引开始,索引默认从1开始,但m=0,则是从首字符开始截取
填充函数
LPAD(char1,n,char2)用于在字符串的左端填充字符,char1用于指定源字符串,char2用于指定被填充的字符,n用于指定填充后的char1的总长度
LPAD(char1,n,char2)用于在字符串的右端填充字符,char1用于指定源字符串,char2用于指定被填充的字符,n用于指定填充后的char1的总长度
长度函数
LENGTH(char)用于返回字符串的长度,字符串中的后缀空格也记作字符串的长度。
查看字符出现的索引位置
INSTR(char1,char2[,n[m]])用于取得子串在字符串中的位置,char1指定源字符串,char2指定子串,n指定起始开始搜索位置(默认值为1),m用于指定子串的第m次出现的次数(默认值为1)
替换字符串函数
REPLACE(char,search_string[,replacement_string])用于替换字符串的子串内容,Char用于指定源字符串,search_string用于指定被替换,replacement_string用于指定替换子串

操作示例:

-- length: 获取参数值的字节个数
SELECT LENGTH('john') FROM dual;

-- concat: 拼接字符串
SELECT CONCAT(empno,ename) FROM emp;

-- upper:大写 lower: 小写
SELECT UPPER('john') FROM dual;
SELECT LOWER('HELLO WORLD') FROM dual;

--initcap:首字母大写
select initcap('initCAP') from dual;

-- substr
-- 注意:索引从1开始
SELECT SUBSTR('李莫愁爱上了陆展元',6) FROM dual; -- 截取从指定索引处后面所有字符

-- instr: 返回子字符串在父字符串的起始索引,如果没有返回0
SELECT INSTR('杨不梅爱上了殷六霞','殷六霞') AS out_put FROM dual;

-- trim: 
注意:只去除字符串中前后的字符或空格,不去除字符串中间的字符或空格
SELECT TRIM('  asdf  ') ,--去两边空空格,
    TRIM('  as df  '), --去两边空空格,不去除中间空格
       LTRIM('  asdf  '), --去左边空格,
       RTRIM('  asdf  '), --去右边空格,
       LTRIM('asddg','as')--去指定字符
  FROM DUAL;

-- lpad: 用指定的字符实现左填充到指定长度
注意:当字符串长度大于给定的指定长度,则会从左边开始截取指定长度
SELECT LPAD('qzp',10,'*') FROM dual;
SELECT LPAD('qzp',2,'*') FROM dual;

-- rpad: 用指定的字符实现右填充指定长度
SELECT RPAD('qzp',12,'*') FROM dual;
SELECT RPAD('qzp',2,'*') FROM dual;

-- replace: 替换,选取需要被替换的字符,用指定的字符代替
SELECT REPLACE('qzpxihuanwjw','qzp','wo') FROM dual;

--TRANSLATE:逐个替换,源字符串出现指定的字符串的字符,换成对应的
SELECT ENAME,
       REPLACE(ENAME, 'A', '000'),--替换,
       TRANSLATE(ENAME, 'ABCD', '0123') --逐个替换
  FROM EMP;

2.数值函数

  • 数值函数的输入参数和返回值都是数字类型
函数类型作用
ROUND(n[,m])用于返回四舍五入的结果,其中n可以任意数,m必须是整数
TRUNC(n[,m])用于截取数字,其中n可以是任意数字,m必须是整数
MOD(m,n)用于取得两个数的相除后的余数,如果数字n为0,则返回结果为m
-- round 四舍五入
-- 诀窍:取绝对值,再添加+-号
SELECT ROUND(1.65) FROM dual;		-- 结果:2
SELECT ROUND(-1.65) FROM dual;	 	-- 结果:-2
-- 保留指定的小数点位数,正数表示小数点向右保留对应位数,负数表示小数点向左保留对应位数,实际小数点不会移动
SELECT ROUND(11.567,1) FROM dual; 	-- 结果:11.6
SELECT ROUND(11.567,-1) FROM dual;	-- 结果:10

-- trunc 截取数字
SELECT TRUNC(25.329) FROM dual; 	--结果是:25
SELECT TRUNC(25.329,2) FROM dual;	--结果是:25.33
SELECT TRUNC(25.329,-1) FROM dual;	--结果是:20


-- mod(被除数,除数) 取余
-- 诀窍:mod(a,b)==>a-a/b*b;被除数为正,结果则为正数,被除数为负数,结果则为负数
SELECT 10%3;
SELECT MOD(10,3) FROM dual;		--结果:1
SELECT MOD(-10,-3) FROM dual;	--结果:-1
SELECT MOD(-10,3) FROM dual;	--结果:-1
SELECT MOD(10,-3) FROM dual;	--结果:1

3.日期函数

  • 用于处理日期时间类型的函数
--SYSDATE:用于返回当前系统日期
select sysdate from dual;--返回当前系统时间
select sysdate-1 昨天,sysdate 今天,sysdate+1 明天 from dual;

--MONTHS_BETWEEN(d1,d2):用于返回日期d1和d2之间相差的月数,d1大于d2结果为正数,否则为负数
select months_between(sysdate,hiredate) from emp;--查询工人的工作总月份
select round(months_between(sysdate,hiredate)/12) from emp;--查询工人的工作年限

--ADD_MONTHS(d,n):用于返回特定日期时间之后或之前的月份对应的日期时间,d用于指定日期时间,n可以是任意整数
注意:n为正整数,为当前月之后的月份,n为负数,为当前月之前的月份
select ename,add_months(hiredate,30*12) from emp;--查询每位员工入职30年后的年份

--NEXT_DAY(d,char):用于返回特定日期之后的第一个工作日多对应的日期,d用于指定日期时间值,char用于指定工作日
注意:当使用该函数时,工作日必须与日期语言匹配,假如日期语言为AMERICAN,那么周一对应于"MONDAY";日期语言为简体中文,那么周一对应于"星期一"
select sysdate,next_day(sysdate,'星期一') from dual;--查询下周一的日期

--LAST_DAY(d):用于返回特定日期所在月份的最后一天
select sysdate,last_day(sysdate) from dual;--查询当前日期所在月份的最后一天

--ROUND(d[,fmt]):用于返回日期时间的四舍五入的结果。d用于指定日期的时间值,fmt用于指定四舍五入的方式
注意:如果设置fmt为YEAR,则71日为分界线,如果设置fmt为MONTH,16日为分界线
--如系统时间是'2020-10-09'
select round(sysdate,'year') from dual;--结果为:2021/1/1
select round(sysdate,'month') from dual;--结果为:2020/10/1

--TRUNC(d[,fmt]):用于截断日期时间数据,d用于指定日期的时间值,fmt用于指定截断日期时间数据的方法
注意:如果设置fmt为YEAR,则结果为本年度的11日,如果设置fmt为MONTH,则结果为本月1SELECT TRUNC(SYSDATE,'year') FROM dual;--结果:2020/1/1
SELECT TRUNC(SYSDATE,'month') FROM dual;--结果:2020/10/1

4.转换函数

  • 用于将数据从一种数据类型转换成另一种数据类型

Oracle可以隐式的(自动的)进行数据类型转换

源数据类型目标数据类型
VARCHAR2或CHARNUMBER
VARCHAR2或CHARDATE
NUMBERVARCHAR2
DATEVARCHAR2
select * from emp where sal > '100';
select * from emp where hiredate = '17-12月-80';

转换格式:

格式字符串含义
YYYY/MM/DD年/月/日
YYYY年(4位)
YYY年(3位)
YY年(2位)
MM月份
DD日期
D星期
DDD一年之第几天
WW一年之第几周
W一月之第几周
YYYY/MM/DD HH24:MI:SS年/月/日时(24小时制):分:秒
YYYY/MM/DD HH:MI:SS年/月/日时(非24小时制):分:秒
TO_CHAR
  • 将日期类型转换为字符类型
--函数格式:
TO_CHAR(d[,fmt[,'nlsparams']])
--d用于指定日期值,fmt用于指定日期格式模型,'nlsparams'用于指定日期显示语言(格式:'NLS_LANGUAUE=language')
--默认日期显示格式为DD-MON-RR
select to_char(hiredate,'DD-MON-RR','NLS_LANGUAUE=AMERICAN') FROM EMP;
注意:当在格式模型中增加字符值时,必须用双引号引注字符值
select to_char(hiredate,'YYYY"年"MM"月"DD"日"') from emp;
  • 将数值类型转换为字符类型
--函数格式:
TO_CHAR(d[,fmt])
--n用于指定数值,fmt用于指定数字格式的模型
格式模型,常用的元素如下:
9:显示数字,并且会忽略前导0
0:显示数字,如果位数不足,则用0补齐
.:在指定位置显示小数点
,:在指定位置显示逗号
$:在数字前加美元符号
L:在数字前加本地货币符号
--数字转换成字符
select sal,to_char(sal,'L0,000,000.00') from emp;--显示本地货币本身,位数不够,补零
select sal,to_char(sal,'L9,999,999.99') from emp;--显示本地货币本身,位数不够,空着
select sal,to_char(sal,'$9,999,999.99') from emp;--显示美元货币本身,位数不够,空着
TO_DATE
  • 用于将字符串转换成日期类型的数据
--语法格式;
TO_DATE(char[,fmt[,'nlsparams']])
--char用于匹配日期数据的字符串,fmt用于指定日期格式模型,'nlsparams'用于指定日期语言
--查看1982之后入职的员工信息
select ename,hiredate from emp where hiredate>to_date('1981-12-31','YYYY-MM-DD');

TO_NUMBER

  • 将包含数字的字符串转换成数值类型
--语法格式:
TO_NUMBER(n[,fmt])
--n是包含数字的字符串,fmt用于指定数字格式模型
--显示工资大于2000的所有员工的姓名和工资
select ename,sal from emp where sal>to_number('¥2000','L99999');

5.通用函数

  • 此函数适用于任何数据类型,同时也适用于空值
NVL
  • 用于处理NULL
--语法格式
NVL(expr1,expr2)
--如果expr1是null,则返回expr2,如果expr1不是null,则返回expr1
select ename,sal,comm,sal+nvl(comm,0) from emp;
NVL2
  • 用于处理NULL
--语法格式
NVL2(expr1,expr2,expr3)
--如果expr1不是null,则返回expr2,如果expr1是null,则返回expr3
select ename,sal,comm,nvl2(comm,sal+comm,sal) from emp;
NULLIF
  • 用于比较两个表达式
--语法格式
NULLIF(expr1,expr2)
--如果表示式expr1不等于表达式expr2,返回expr1,如果两个表达式相等,则返回null
select empno,ename,hiredate,nullif(hiredate,trunc(sysdate,'MONTH')) from emp;
COALESCE
  • 用于返回表达式列表中第一个not null表达式的结果
--语法格式
COALESCE(expr1[,expr2][,...])
--如果表示式expr1不等于表达式expr2,返回expr1,如果两个表达式相等,则返回null
select ename,sal,comm,coalesce(sal+comm,sal) from emp;

(3).行列转换函数

case函数

-- case函数
--case函数的使用方式一:
/*
CASE 要判断的字段或表达式1
WHEN 常量1 THEN 显示的值1或者语句1;
WHEN 常量2 THEN 显示的值2或者语句2;
WHEN 常量3 THEN 显示的值3或者语句3;
....
ELSE 显示的值或者语句n;
END
*/
# 注意:语句需要;,显示值不需要;类似于java中的switch-case用法
# 代码实例
SELECT * ,CASE degree
WHEN 90 THEN '优秀'
WHEN 80 THEN '良好'
WHEN 70 THEN '一般'
WHEN 60 THEN '及格'
ELSE '不合格' 
END FROM score;

# case函数的使用方式二:类似于多重if
/*
CASE 
WHEN 条件1 THEN 显示的值1或者语句1; 
WHEN 条件2 THEN 显示的值1或者语句1;
WHEN 条件3 THEN 显示的值1或者语句1;
WHEN 条件4 THEN 显示的值1或者语句1;
ELSE 显示的值或者语句n;
END 
*/
# 注意:语句需要;,显示值不需要;
# 代码实例:
SELECT * ,CASE 
WHEN degree>=90 THEN '优秀'
WHEN degree>=80 AND degree <90 THEN '良好'
WHEN degree>=70 AND degree <80 THEN '一般'
WHEN degree>60 AND degree <70 THEN '及格'
ELSE '不合格' 
END AS 成绩水平 FROM score;

DECODE函数

--decode函数
--方式一使用语法规则
select 列名,decode(col|expression,search1,result1,[,search2,result2,..][,default])
select empno,ename,job,decode(job,'CLERK','办事员','SALESMAN','销售','MAMAGER','经理','ANALYST','分析员','总裁') from emp;
--方式二使用语法规则
select 列名,decode(列名,值1,数据1,....值n,数据n,默认数据)from table_name;

create table kecheng
(
  id     NUMBER,
  name   VARCHAR2(20),
  course VARCHAR2(20),
  score  NUMBER(3,1) --一位小数的数据
);
--插入数据
insert into kecheng VALUES (1, '张三', '语文', 67); 
insert into kecheng (id, name, course, score)
values (1, '张三', '数学', 76);
insert into kecheng (id, name, course, score)
values (1, '张三', '化学', 45);
insert into kecheng (id, name, course, score)
values (2, '李四', '语文', 54);
insert into kecheng (id, name, course, score)
values (2, '李四', '数学', 81);
insert into kecheng (id, name, course, score)
values (2, '李四', '英语', 64);
insert into kecheng (id, name, course, score)
values (2, '李四', '历史', 93);
insert into kecheng (id, name, course, score)
values (2, '李四', '化学', 27);
insert into kecheng (id, name, course, score)
values (3, '王五', '语文', 24);
insert into kecheng (id, name, course, score)
values (3, '王五', '数学', 25);
insert into kecheng (id, name, course, score)
values (3, '王五', '英语', 8);
insert into kecheng (id, name, course, score)
values (3, '王五', '历史', 45);
insert into kecheng (id, name, course, score)
values (3, '王五', '化学', 1);
insert into kecheng (id, name, course, score)
values (1, '张三', '英语', 43);
insert into kecheng (id, name, course, score)
values (1, '张三', '历史', 56);
commit;--提交事务
SELECT * FROM kecheng;
select ID,NAME,MAX(DECODE(course,'语文',score))语文,
MAX(DECODE(course,'数学',score))数学,
MAX(DECODE(course,'英语',score))英语,
MAX(DECODE(course,'化学',score))化学,
MAX(DECODE(course,'历史',score))历史,
from kecheng group by ID,NAME;

WM_CONCAT函数

  • wm_cnocat行转列(让查询结果行转列),把列值以","号分隔开,并显示一行
select wm_concat(ename) from emp;
--------------------------------行列转换-----------------------------------------
有的时候吧查询结果集做成列的效果,这样展示出来会更加直观;
行转列有四种方法   CASE WHEN 、DECODE 、PIVOT 、wm_concat;
SELECT ID, NAME FROM KECHENG GROUP BY ID, NAME HAVING MIN(SCORE) > 40;
--1.case WHEN 判断条件, 每个case when会在结果集里面多增加一列
SELECT ID,NAME ,max(CASE WHEN course='语文' THEN score END) 语文,
min(CASE WHEN course='数学' THEN score END) 数学,
  sum(CASE WHEN course='化学' THEN score END) 化学,
   SUM( CASE WHEN course='历史' THEN score END) 历史,
     SUM( CASE WHEN course='英语' THEN score END) 英语 FROM kecheng GROUP BY ID,NAME ORDER BY ID; 
--2.decode 判断指定的值,decode能干的时候case when一定可以做;
语法 SELECT  列名,DECODE(列名,值1,数据1,...值n,数据n,默认数据)  FROM  表
SELECT ID,NAME,MAX(DECODE(course,'语文',score))语文,
MAX(DECODE(course,'数学',score))数学,
MAX(DECODE(course,'英语',score))英语,
MAX(DECODE(course,'化学',score))化学,
MAX(DECODE(course,'历史',score))历史 FROM kecheng GROUP BY ID,NAME;

SELECT emp.*,DECODE(job,'CLERK','小助理','MANAGER','经理','SALESMAN','销售','其他工作') 中文名称 FROM emp;
--3.pivot   oracle 11版本才有的 
语法:SELECT * FROM 表 PIVOT( 聚合函数(数据列名) FOR 要转的列名  IN (新列名的集合)   )
语法: SELECT  你需要的列名  FROM 表  UNPIVOT(  数据  FOR  要转的列名  IN (原表老的列名集合)  )
--for是循环的意思,for是遍历的意思。
SELECT * FROM kecheng  PIVOT(SUM(score) FOR course IN ('语文' 语文  ,'数学' 数学 , '英语' 英语  ,'化学' 化学  ,'历史' 历史));
--4.wm_concat  concat拼接的意思
SELECT ID,name,replace(wm_concat(course||score),',','   ') 详细信息 FROM kecheng GROUP BY ID,NAME;

(4).分组函数

1.分组函数定义

  • 在关系数库中,使用数据分组可以取得表数据的汇总信息,数据分组是通过分组函数、group by以及having等字句共同实现

分组函数的语法

select [column,] group function(column)...
from table_name
[where condition]
[group by group_by_expression]
[having group_condition]
[order by column];

常用的分组函数

函数作用支持参数类型
SUM(字段名)求和数值型
AVG(字段名)求平均值数值型
MAX(字段名)求最大值任何类型
MIN(字段名)求最小值任何类型
COUNT(*)
COUNT(1)
COUNT(字段名)
统计,不过滤null值
统计,不过滤null值
统计,过滤null值
任何类型
--求最大值
select max(sal) from emp;
--求最小值
select min(sal) from emp;
--求平均值
select avg(sal) from emp;
--求和
select sum(sal) from emp;

2.聚合函数的特点:

  • 聚合函数忽略null值
  • 可以和distinct搭配使用
  • 和聚合函数一同查询的字段要求是group by后的字段

3.count函数的特点

COUNT(*)和COUNT(1):使用的时候,不会自动过滤null值(简单理解,COUNT(星)是统计行记录,只要这行记录至少有一个字段不为空,就可以+1;COUNT(1)是相当于在表前面加了一列,字段内容为1,然后统计 1 的个数,来统计行数)

COUNT(字段名):使用的时候自动过滤null值

(5).集合运算符

  • 集合操作符专门用于合并多条select语句的结果
set运算符作用
union
union all
并集,去重
并集,不去重
intersect交集
minus差集

union:操作符用于取得两个结果集的并集,当使用该操作符是,会自动去掉结果集中的重复行,并且会以第一列的字段进行升序排序

union all :操作符用于取得两个结果集的并集,但与union操作符不同,该操作符不会取消重复行,并且不会对结果集数据进行排序

intersect:操作符用于取得两个结果集的交集,当使用操作符时,只会显示同时存在于两个结果集中的数据,并且会以第一列的字段进行升序排序。

minus :操作符用于取得两个结果集的差集,当使用该操作符时,只会显示在第一个结果集中存在,在第二个结果集中不存在的数据,并且会以第一列的结果集进行升序排序。

--复制两张表
create table emp01 as select * from emp;
create table emp02 as select * from emp;
--复制两张表结构,自己插入数据
create table emp03 as select * from emp where 1=2;

--union 14条数据
SELECT * FROM emp01 
UNION
SELECT * FROM emp02;
--union all 28条数据
SELECT * FROM emp01 
UNION ALL 
SELECT * FROM emp02;
--intersect 14条数据
INSERT INTO emp01 VALUES(7333,'qzp',NULL,NULL,NULL,null,null,40);
SELECT * FROM emp01 
INTERSECT  
SELECT * FROM emp02;
--minus 1条数据,插入在emp01表中的一条
SELECT * FROM emp01 
MINUS  
SELECT * FROM emp02;


注意:order by 后面可以用数字代表字段的列数
SELECT * FROM emp ORDER BY 6;

18.oracle分析函数

------------------------------rownum------------------------------------
分页查询,从表里面提取指定的数据;mysql也有 limit 0,1
rownum实际上是不存在表里面,是查询出来之后,临时产生的一个序列号而已;rownum只能小于 不能大于
--查询前5条
SELECT emp.*,ROWNUM ru From  emp  WHERE ROWNUM<=5;
--查询3-8条
SELECT emp.*,ROWNUM ru From  emp  WHERE ROWNUM<=8
MINUS
SELECT emp.*,ROWNUM ru From  emp  WHERE ROWNUM<=2;
--子查询也可以
SELECT a.* FROM 
(SELECT emp.*,ROWNUM ru From  emp)A WHERE a.ru BETWEEN 3 AND 8;
------------------------------rownid------------------------------------
ROWID是物理地址,在数据插入的时候就已经写入到磁盘了,删除重复数据的时候使用,术语叫清洗数据; etl工程师  bi工程师 数据库开发工程师
SELECT emp.*,ROWID From  emp ;
CREATE TABLE  SS(ID number,NAME VARCHAR2(10));
INSERT INTO SS VALUES(111,'sadfasd');
INSERT INTO SS VALUES(111,'sadfasd');
INSERT INTO SS VALUES(22,'3333');
INSERT INTO SS VALUES(22,'3333');
COMMIT;--update insert  delete 都要记得提交
SELECT SS.*,ROWID From  SS;
--清洗数据的时候,重复的数据需要保留一条,筛选rowid的时候,选最大或者最小不重要
DELETE FROM SS WHERE  ROWID NOT IN (SELECT  MIN(ROWID)  FROM SS  GROUP BY ID,NAME);
--------------课堂练习-------------------------------
CREATE TABLE table1(编号 char(10),姓名 char(10));
INSERT INTO table1 VALUES('1','a');
INSERT INTO table1 VALUES('6','b');--
INSERT INTO table1 VALUES('8','c');--
INSERT INTO table1 VALUES('3','a');--
INSERT INTO table1 VALUES('3','c');
INSERT INTO table1 VALUES('5','c');
INSERT INTO table1 VALUES('2','b');
INSERT INTO table1 VALUES('3','b');


SELECT * FROM table1;
TRUNCATE TABLE table1;

---删除数据 保留相同姓名的 编号最大的一条数据

DELETE FROM table1 WHERE (编号,姓名) NOT IN (SELECT MAX(编号),姓名 FROM table1 GROUP BY 姓名)
DELETE FROM TABLE1
 WHERE ROWID IN (SELECT a.ROWID
                   FROM TABLE1 A, TABLE1 B
                  WHERE A.姓名 = B.姓名
                    AND A.编号 < B.编号)
--扩展  补充重复数据的情况
INSERT INTO table1 VALUES('8','c');--
INSERT INTO table1 VALUES('3','a');--

DELETE FROM TABLE1
 WHERE ROWID NOT IN
       (SELECT MAX(ROWID)
          FROM TABLE1
         WHERE (编号, 姓名) IN (SELECT MAX(编号), 姓名 FROM TABLE1 GROUP BY 姓名)
         GROUP BY 编号, 姓名)

----------------------------------------个人解答----------------------------------------------------------
--方式一:
SELECT MAX(编号),姓名 FROM table1 GROUP BY 姓名;
DELETE FROM table1 WHERE (编号,姓名) NOT IN(SELECT MAX(编号),姓名 FROM table1 GROUP BY 姓名);
--方式二:
SELECT table1.*,ROWID FROM table1;
SELECT a.ROWID
FROM table1 a,table1 b
WHERE a.姓名=b.姓名 AND a.编号<b.编号 GROUP BY a.ROWID;
DELETE FROM table1 WHERE ROWID IN(SELECT a.ROWID
FROM table1 a,table1 b
WHERE a.姓名=b.姓名 AND a.编号<b.编号 GROUP BY a.ROWID);
------------------------------分析函数-------------------------------------
--五种聚合   字符,日期,数值,转换,分析,自定义函数
--分析函数是oracle强大的统计分析函数,专用做统计分析,好处是不仅可以分组,还可以看到组里面的详细数据
语法:SELECT  列名,...聚合函数(列名)OVER(PARTITION BY 列名 ORDER BY 列名  asc|desc) 别名 FROM  表 WHERE 条件
OVER 是开窗函数,oracle底层做运算的,你看不到;
SELECT EMP.*,
       COUNT(EMPNO) OVER(PARTITION BY DEPTNO) 统计人数,
       round(AVG(SAL) OVER(PARTITION BY DEPTNO),2) 平均工资,
       SUM(SAL) OVER(PARTITION BY DEPTNO) 求和
  FROM EMP;
select deptno,ename,sal,sum(sal) over(order by deptno) 连续求和,sum(sal) over() 总额,100*round(sal/sum(sal) over(),5) "份额%" from emp;
--递增统计分析
SELECT emp.*,COUNT(empno)OVER(ORDER BY empno) 递增统计 FROM emp;
SELECT emp.*,avg(sal)OVER(ORDER BY empno) 递增平均 FROM emp;
SELECT emp.*,avg(sal)OVER(PARTITION BY DEPTNO ORDER BY empno) 递增平均 FROM emp;
---三种排名 排名次 跟order by有区别
--连续排名  123456 ,如果没有具体要求,我们一般采用第一种
SELECT emp.*,row_number()OVER(PARTITION BY DEPTNO ORDER BY sal DESC ) pm FROM emp;
--跳跃排名 ,会有并列的情况  122456
SELECT emp.*,RANK()OVER(PARTITION BY DEPTNO ORDER BY sal DESC ) pm FROM emp;
--连续不跳跃,,会有并列的情况  122345
SELECT emp.*,dense_RANK()OVER(PARTITION BY DEPTNO ORDER BY sal DESC ) pm FROM emp;
---查询每个部门薪水最高的员工信息
SELECT a.* FROM 
(SELECT emp.*,row_number()OVER(PARTITION BY DEPTNO ORDER BY sal DESC ) pm FROM emp)A WHERE a.pm=1;

--计算每个部门工资大于部门平均工资的人数 占部门总人数的百分比? (关联一种,分析函数一种)
SELECT * From  emp ;
10   33.33%
20   60%
30   33.33%
--分析:通过case when
--方式一:
SELECT
	a.deptno,
	round( sum( CASE WHEN b.sal > a.asal THEN 1 ELSE 0 END ) / count( empno ), 4 ) * 100 || '%'百分比 
FROM
	emp b join ( SELECT deptno, avg( sal ) asal FROM emp GROUP BY deptno ) a ON a.deptno = b.deptno 
GROUP BY
	a.deptno 
ORDER BY
	a.DEPTNO;
--分析,首先计算每个部门的平均工资,计算大于平均工资的人数,计算部门的总人数,
--方式二:
SELECT B.DEPTNO, ROUND((B.CT1 / C.CT2), 4) * 100 || '%'
  FROM (SELECT E.DEPTNO, COUNT(E.EMPNO) CT1
          FROM EMP E
          JOIN (SELECT DEPTNO, AVG(SAL) PJ FROM EMP GROUP BY DEPTNO) A
            ON E.DEPTNO = A.DEPTNO
           AND E.SAL > A.PJ
         GROUP BY E.DEPTNO) B
  JOIN (SELECT DEPTNO, COUNT(EMPNO) CT2 FROM EMP GROUP BY DEPTNO) C
    ON B.DEPTNO = C.DEPTNO;
--分析,首先计算每个部门的平均工资,总人数,计算大于平均工资的人数,
SELECT E.DEPTNO, COUNT(*) / MAX(B.CT1)
  FROM EMP E
  JOIN (SELECT DEPTNO, COUNT(EMPNO) CT1, AVG(SAL) PJ
          FROM EMP
         GROUP BY DEPTNO) B
    ON E.DEPTNO = B.DEPTNO
   AND E.SAL > B.PJ
 GROUP BY E.DEPTNO;
--分析函数,直接对比自己的工资和平均工资,如果大于就写个1记录一下
SELECT A.DEPTNO,
       SUM(CASE
             WHEN A.SAL > A.PJ THEN
              1
             ELSE
              0
           END) / COUNT(EMPNO) 百分比
  FROM (SELECT EMP.*, AVG(SAL) OVER(PARTITION BY DEPTNO) PJ FROM EMP) A
 GROUP BY A.DEPTNO;

-----------------------------------练习---------------------------------------------

CREATE TABLE logasset(营业部 NUMBER(2),客户id NUMBER(4),产品 VARCHAR(2),销量 NUMBER(10));
INSERT INTO logasset VALUES (2,201,'A',10000);
INSERT INTO logasset VALUES (2,201,'B',30);
INSERT INTO logasset VALUES (2,203,'C',200);
INSERT INTO logasset VALUES (3,301,'A',200);
INSERT INTO logasset VALUES (3,302,'A',7000);
INSERT INTO logasset VALUES (3,303,'B',10000);

SELECT * FROM logasset; 

1.计算每个营业部有效客户,标准金额>1000
SELECT A.营业部, COUNT(*) 有效客户数量
  FROM (SELECT 营业部, 客户ID, SUM(销量)
          FROM LOGASSET
         GROUP BY 营业部, 客户ID
        HAVING SUM(销量) > 1000) A
 GROUP BY A.营业部;
 
SELECT A.营业部, COUNT(*) 数量
  FROM (SELECT DISTINCT 营业部,
                        客户ID,
                        SUM(销量) OVER(PARTITION BY 营业部, 客户ID) XL
          FROM LOGASSET) A
 WHERE A.XL > 1000 GROUP BY  A.营业部 ;

2.计算哪个产品销量最好,哪个最差
SELECT A.产品,
       CASE
         WHEN A.HIGH = 1 THEN
          '最好'
         else
          '最差'
       END 排名
  FROM (SELECT 产品,
               ROW_NUMBER() OVER(ORDER BY SUM(销量) DESC) HIGH,
               ROW_NUMBER() OVER(ORDER BY SUM(销量)) LOWER
          FROM LOGASSET
         GROUP BY 产品) A
 WHERE A.HIGH = 1
    OR A.LOWER = 1;

3.计算每个营业部销量最好的产品
SELECT A.营业部, A.产品
  FROM (SELECT 营业部,
               产品,
               SUM(销量),
               ROW_NUMBER() OVER(PARTITION BY 营业部 ORDER BY SUM(销量) DESC) PM
          FROM LOGASSET
         GROUP BY 营业部, 产品)A
 WHERE A.PM = 1;

---2种偏移
把指定的一列的数据 往前或者往后移动几位  LAG LEAD 
一般用于单表列与列之间进行计算,对比
语法  SELECT  列名,LAG|LEAD(列名,偏移量,默认值(不写的时候就是null)) FROM 表
SELECT empno,ename,LAG(ename,2)OVER(ORDER BY empno) py FROM emp; 

--查询一下emp里面 ,后面一个员工比前面一个员工晚入职多久?
SELECT EMP.*, LAG(HIREDATE, 1) OVER(ORDER BY EMPNO) - HIREDATE 晚入职
  FROM EMP;

SELECT * From  sc;
1.查询c001比c002成绩高的学号?
分析:首先过滤数据只保留c001 c002的课程信息,然后c001的分数偏移一位,跟c002在同一行了;再对比
SELECT A.SNO
  FROM (SELECT SNO,
               CNO,
               SCORE,
               LAG(SCORE, 1) OVER(PARTITION BY SNO ORDER BY CNO) C001
          FROM SC
         WHERE CNO IN ('c001', 'c002')) A
 WHERE A.C001 > A.SCORE;

SELECT A.SNO
  FROM (SELECT SNO,
               CNO,
               SCORE,
               LEAD(SCORE, 1) OVER(PARTITION BY SNO ORDER BY CNO) C002
          FROM SC
         WHERE CNO IN ('c001', 'c002')) A
 WHERE A.C002 < A.SCORE;


-----------------------------课堂练习----------------------------------------------
 create table earnings -- 打工赚钱表  
(  
  earnmonth varchar2(6), -- 打工月份  
  area varchar2(20), -- 打工地区  
  sno varchar2(10), -- 打工者编号  
  sname varchar2(20), -- 打工者姓名  
  times int, -- 本月打工次数  
  singleincome number(10,2), -- 每次赚多少钱  
  personincome number(10,2) -- 当月总收入  
)  ;

insert into earnings values('200912','北平','511601','大魁',11,30,11*30);  
insert into earnings values('200912','北平','511602','大凯',8,25,8*25);  
insert into earnings values('200912','北平','511603','小东',30,6.25,30*6.25);  
insert into earnings values('200912','北平','511604','大亮',16,8.25,16*8.25);  
insert into earnings values('200912','北平','511605','贱敬',30,11,30*11);  
insert into earnings values('200912','金陵','511301','小玉',15,12.25,15*12.25);  
insert into earnings values('200912','金陵','511302','小凡',27,16.67,27*16.67);  
insert into earnings values('200912','金陵','511303','小妮',7,33.33,7*33.33);  
insert into earnings values('200912','金陵','511304','小俐',0,18,0);  
insert into earnings values('200912','金陵','511305','雪儿',11,9.88,11*9.88);  
insert into earnings values('201001','北平','511601','大魁',0,30,0);  
insert into earnings values('201001','北平','511602','大凯',14,25,14*25);  
insert into earnings values('201001','北平','511603','小东',19,6.25,19*6.25);  
insert into earnings values('201001','北平','511604','大亮',7,8.25,7*8.25);  
insert into earnings values('201001','北平','511605','贱敬',21,11,21*11);  
insert into earnings values('201001','金陵','511301','小玉',6,12.25,6*12.25);  
insert into earnings values('201001','金陵','511302','小凡',17,16.67,17*16.67);  
insert into earnings values('201001','金陵','511303','小妮',27,33.33,27*33.33);  
insert into earnings values('201001','金陵','511304','小俐',16,18,16*18);  
insert into earnings values('201001','金陵','511305','雪儿',11,9.88,11*9.88);  
commit;
select * from earnings;  


---按照月份、地区,求各个打工收入排序

--累计求和根据月份地区求出各个打工者收入总和,按照收入由少到多排序

--按照月份和地区求打工收入最高值,最低值,平均值和总额
   
-- 用lead lag 计算出来每个员工的上个月和下个月有没有赚钱。(personincome大于0就是赚了)
/*月份   姓名   上个月     下个月
200912   大魁              没赚     --相对于12月上个月就是11月,但是11月我们表里面没有数据,所以这个地方计算出来是空值;
201001   大魁     赚了              --相对于1月下个月就是2月,但是2月我们表里面没有数据,所以这个地方计算出来是空值;
200012   大凯              赚了 
201001   大凯     赚了           */


select * from earnings;  


---按照月份、地区,求各个打工收入排序
SELECT e.*,row_number()OVER(PARTITION BY e.earnmonth,e.area ORDER BY e.personincome ) PX FROM  earnings e;

--累计求和根据月份地区求出各个打工者收入总和,按照收入由少到多排序
SELECT e.*,SUM(e.personincome)OVER(PARTITION BY e.earnmonth,e.area ORDER BY e.personincome ) PX FROM  earnings e;

--按照月份和地区求打工收入最高值,最低值,平均值和总额
SELECT e.earnmonth,e.area, MAX(e.personincome) ,min(e.personincome) ,avg(e.personincome) ,sum(e.personincome) FROM  earnings e 
GROUP BY e.earnmonth,e.area;

SELECT e.earnmonth,e.area, 
MAX(e.personincome)OVER(PARTITION BY e.earnmonth,e.area) ,
min(e.personincome)OVER(PARTITION BY e.earnmonth,e.area) ,
avg(e.personincome)OVER(PARTITION BY e.earnmonth,e.area) ,
sum(e.personincome)OVER(PARTITION BY e.earnmonth,e.area) 
FROM  earnings e ;
--用lead lag 计算出来每个员工的上个月和下个月有没有赚钱。(personincome大于0就是赚了)
/*月份   姓名   上个月     下个月
200912   大魁              没赚     --相对于12月上个月就是11月,但是11月我们表里面没有数据,所以这个地方计算出来是空值;
201001   大魁     赚了              --相对于1月下个月就是2月,但是2月我们表里面没有数据,所以这个地方计算出来是空值;
200012   大凯              赚了 
201001   大凯     赚了           */


SELECT EARNMONTH,
       SNAME,
       PERSONINCOME,
       LAG(decode(PERSONINCOME,0,'没赚','赚了'), 1) OVER(PARTITION BY SNAME ORDER BY EARNMONTH) 上个月,
       LEAD(decode(PERSONINCOME,0,'没赚','赚了'), 1) OVER(PARTITION BY SNAME ORDER BY EARNMONTH) 下个月
       FROM earnings;
       
SELECT EARNMONTH,SNAME,
(CASE WHEN 上个月>0  THEN '赚了'  WHEN 上个月=0 THEN '没赚' ELSE NULL END) 上个月,
 (CASE WHEN 下个月>0   THEN '赚了'  WHEN 下个月=0 THEN '没赚' ELSE NULL END) 下个月
   FROM
(SELECT 
       EARNMONTH,
        SNAME,
       PERSONINCOME,
       LAG(PERSONINCOME, 1) OVER(PARTITION BY SNAME ORDER BY EARNMONTH)上个月,
       LEAD(PERSONINCOME, 1) OVER(PARTITION BY SNAME ORDER BY EARNMONTH)下个月
  FROM EARNINGS)

19.Oracle子查询

(1).子查询的概述

  • 子查询是指嵌入在其他SQL语句 SELECT中的语句,也称为嵌套查询。

示例:

SELECT *
 from emp
 where job =(select job
 from emp
 where ename ='SMITH');

(2).为什么使用子查询?

--查询和 SMITH是同一个职位的员工。
--第一步:查询 SMITH的职位
select job from emp where ename='SMITH';
--第二步:查询和 SMITH是同一个职位的员工
select * from emp where job = 'CLERK';

(3).子查询的语法

---语法结构
SELECT  select list
 FROM   table
 WHERE  expr operator--主查询
        (SELECT select_list
         FROM table);--子查询
--可以使用子查询的位置: where, select, having,from

a.在 select子句中使用子查询

--举例:
--查询出每个部门的编号,名称,位置,部门人数
select deptno,dname,loc, (select count(empno) from emp where emp.deptno = dept.deptno) cnt from dept;

b.在having子句中的子查询

--在having子句中的子查询
--查询员工信息表,按部门编号进行分组,要求显示员工的部门编号,平均工资,查询条件是平均工资大于30号部门的最高工资。
select deptno,avg(sal)
from emp
group by deptno
having avg(sal)>(select max(sal) 
                 from emp
                 where deptno = 30);

c.在from子句中的子查询

--查询并显示高于部门平均工资的雇员信息
select ename,job,sal
from emp,(select deptno,avg(sal) avgsal from emp group by deptno) dept
where emp.deptno = dept.deptno and sal>avgsal;

(4).子查询和主查询

什么是主查询和子查询?
---子查询和主查询
SELECT  select_list
 FROM   table
 WHERE   expr operator--主查询
         		(SELECT select_list
         		FROM table);--子查询

一个主查询可以有多个子查询

--显示职位和7521的职位相同并且工资大于7934这个员工工资的员工信息。
select *
from emp
where job = (select job from emp where empno = 7521)
and sal>(select sal from emp where empno = 7934);

(5).子查询的执行顺序

一般先执行子查询,再执行主查询,但相关子查询例外

--查询员工表中小于平均工资的员工信息
select empno,ename,sal
 from emp
 where sal <(select avg(sal) from emp);

主查询和子查询可以不是同一张表

--查询部门名称是 ACCOUNTING的员工信息。
select * 
from emp
where deptno = (select deptno from dept where dname='ACCOUNTING');

(6).单行子查询

只返回一行数据的子查询语句·使用单行比较操作符

操作符含义
=等于
>大于
>=大于等于
<小于
<=小于等于
!=不等于

示例:

--显示与JAMES同部门的所有其他的员工姓名、工资、部门号。
select ename,sal,deptno
from emp
where deptno = (select deptno from emp where ename='JAMES')
and ename<>'JAMES';

--查询大于等于公司平均工资的员工的姓名、职位、工资。
select ename,job,sal
from emp
where sal >=(select avg(sal) from emp);

--查询部门名称不是’SAVE’S的员工。
select * 
from emp
where deptno <> (select deptno from dept where dname = 'SAVES');

--非法使用单行子查询
select ename, job, sal 
from emp 
where sal = (select max(sal) 
		   from emp 
		   group by deptno); 

(7).多行子查询

  • 多行子查询是指返回多行数据的子查询语句。使用多行比较操作符。
运算符含义
in等于列表中的任何一个
not in不等于列表中的任何一个
ALL和子查询返回的所有值进行比较
ANYANY和子查询返回的任一值进行比较
  • in

在多行子查询中使用IN操作符

----查询工作地点在NEW YORK和CHICAGO的部门所对应的员工信息。
select *
from emp
where deptno in
(select deptno from dept where loc = 'NEW YORK' or loc='CHICAGO');
  • not in

在多行子查询中使用not 操作符

----查询工作地点不在NEW YORK和CHICAGO的部门所对应的员工信息。
select *
from emp
where deptno not in
(select deptno from dept where loc = 'NEW YORK' or loc='CHICAGO');
  • all

在多行子查询中使用ALL操作符

--查询高于30号部门所有员工工资的员工名、工资和部门号。
select ename,sal,deptno
from emp
where sal>all(select sal from emp where deptno = 30);

--单行子查询
select ename,sal,deptno
from emp
where sal>(select max(sal) from emp where deptno = 30);
any

在多行子查询中使用ALL操作符

--any操作符
--查询高于10号部门任意一个员工工资的员工名、工资和部门号。
select ename,sal,deptno
from emp
where sal > any(select sal from emp where deptno = 10);

--单行子查询
select ename,sal,deptno
from emp
where sal > (select min(sal) from emp where deptno = 10);

(8).子查询需要注意的问题

1.不可以在 group by子句中使用子查询

--错误示范
select avg(sal) from emp group by (select deptno from emp);

在TOP-N分析问题中,须对子查询排序

--显示工资信息表中工资最高的前五名员工,n就是5,取出最前面的n条数据
--rownum
select rownum, empno,ename from empnew where rownum<=5;

--查询员工的工资
select sal from emp;
--错误的
select empno,ename,sal 
from emp
where rownum<=5 
order by sal desc;

--正确的
--在TOP-N分析问题中,须对子查询排序
select empno,ename,sal
from (select * from emp order by sal desc)
where rownum<=5;
单行子查询和多行子查询中的空值问题

单行子查询:如果子查询返回了一个空值,则主查询将不会查到任何结果。

多行子查询:

--空值问题
select *
from emp
where empno not in(select mgr from emp where mgr is not null);

20.PL/SQL入门

(1)什么是PL/SQL?

  • PL/SQL是一种程序语言,叫做过程化SQL语言(Procedural Language/SQL),简而言之,PL/SQL是面向过程的语言
  • PL/SQL是oracle数据库对SQL语句的扩展,在普通SQL语句的使用上增加了编程语言的特点,简而言之,PL/SQL是对SQL语言的扩展

(2)PL/SQL的特点

  • PL/SQL是Oracle系统的核心语言,现在Oracle的许多部件都是由PL/SQL写成的,PL/SQL具有简单、高效、灵活、实用的特点。

(3)不同数据库对SQL的扩展

  • Oracle:PL/SQL
  • SQL Server:Transac-SQL(T-SQL)

(4)PL/SQL语法结构

1).PL/SQL块

  • 块(black): 是PL/SQL的基本程序单元
  • PL/SQL由三部分构成:
    • 声明部分、执行部分、异常处理部分

2).PL/SQL的基本结构

DECLARE
/*
*声明部分:声明变量、常量、复杂数据类型、游标等
*/
BEGIN
/*
*执行部分:PL/SQL和SQL语句
*/
EXCEPTION
/*
*异常处理部分:处理运行错误
*/
END;--块结束标记

3).第一个PL/SQL程序

打印一句hello world!

BEGIN
  dbms_output.put_line('hello world!');
END;

注意:当Command Window窗口使用中DBMS_OUTPUT包输出信息时,需要设置SQL PLUS环境serveroutput的值为ON

>SQL set serveroutput on
SQL> 
SQL> BEGIN
  2    dbms_output.put_line('hello world');
  3  END;
  4  /--代表执行上述程序

PL/SQL procedure successfully completed

SQL> set serveroutput on--开启DBMS_OUTPUT包
SQL> /

hello world

PL/SQL procedure successfully completed

4).PL/SQL块的分类

  • 匿名块:动态构造、只执行一次
  • 子程序:存储在数据库中的存储过程、函数及包中等。当在数据库上建立好之后可以在其它程序中调用他们
  • 触发器:当数据库发生操作时,会触发一些事件,从而自动执行相应的程序

5).PL/SQL的变量

1.标识符
  • 当编写PL/SQL块时,为了临时存储数据,需要定义变量和常量。变量和常量的定义是需要满足标识符的限制要求如下:
    • 标识符不能超过30个字符
    • 第一个字符必须为字母
    • 不分大小写
    • 不能用‘-’(减号)

注意:尽量不要变量名声明和表字段名一样

2.变量名的命名方法
标识符命名规则例子
程序变量v_namev_sal
程序常量c_namec_pi
游标常量name_curroremp_curror
异常标识e_namee_integrity_error
记录类型name_recordemp_record

为提高代码的可读性,建议使用命令方法。

3.变量的类型
  • 数值类型:NUMBER(P,S)以及子类型INT、FLOAT等
  • 字符类型:CHAR(n)、VARCHAR2(n)
  • 日期类型:DATE
  • 布尔类型:BOOLEAN(true.false.null)
4.变量的大小写规则
  • 当编写sql语句和PL/SQL语句时,既可以采用大写格式,也可以采用小写格式,但是为了代码的可读性,应尽量按照以下的规则:
    • SQL关键字采用大写格式,如: SELECT ,UPDATE等
    • PL/SQL关键字采用大写格式,如; DECLARE,BEGIN,EDN等
    • 数据类型采用大写格式,如: INT、DATE等
    • 标识符和参数采用小写格式,如: v_sal等
    • 数据库对象和采用小写格式,如: emp,sal等

代码示例

DECLARE
  v_name     VARCHAR2(10);--定义一个变量
  v_sal      NUMBER(7.2);
  v_hiredate DATE;
  c_tax_rate CONSTANT NUMBER(3.2) := 0.02;--定义一个常量并赋予初始值
  v_tax_sal NUMBER(7.2);
  v_valid   BOOLEAN DEFAULT TRUE;--定义一个boolean类型的变量
BEGIN
  SELECT ename, sal, hiredate
    INTO v_name, v_sal, v_hiredate
    FROM emp
   WHERE empno = 7369;--将将7369的员工信息通过into一一对应传给相应类型的变量
  v_tax_sal := v_sal * c_tax_rate;--计算所得税
  dbms_output.put_line(v_name || '的工资是:' || v_sal || '雇佣日期是:' ||
                       v_hiredate || '所得税是:' || v_tax_sal);--打印输出变量中的内容
  IF v_valid THEN
    dbms_output.put_line('已核实');
  END IF;
END;

--输出结果:
SMITH的工资是:800雇佣日期是:17-12月-80所得税是:0
已核实
5.注释
  • 单行注释:--注释内容
  • 多行注释:/*注释内容*/

6).PL/SQL引用型变量和记录型变量

  • 在很多情况下,PL/SQL变量可以用来存储在数据库表中的数据,在这种情况下,变量应该拥有与表列相同的类型

代码示例:

--定义变量来存储表中查询字段的结果
DECLARE
  v_name VARCHAR2(10);
  v_sal  NUMBER(7, 2);
BEGIN
  SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7369;
  dbms_output.put_line(v_name || '的工资是:' || v_sal);
END;
引用型变量
  • 引用类型变量的数据类型与已经定义的某个数据变量的类型相同,或者与数据库表的某个列的数据类型形同
DECLARE
--定义引用型变量
  v_name emp.ename%TYPE;--引用型变量的类型和emp表中的ename的字段的数据类型一样
  v_sal  emp.sal%TYPE;
BEGIN
--将7369的员工信息一一对应赋值给定义好的引用型变量
  SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = 7369;
  dbms_output.put_line(v_name || '的工资是:' || v_sal);
END;
记录型变量
  • PL/SQL提供%ROWTYPE操作符,返回一个记录类型,其数据类型和数据库表中的数据结构相一致。
DECLARE
--定义一个记录型变量
  emp_record emp%ROWTYPE;
BEGIN
--将7369的员工信息赋值给记录型变量emp_record
  SELECT * INTO emp_record FROM emp WHERE empno = 7369;
  --记录型变量分量来获取表中每个字段的内容进行打印输出
  dbms_output.put_line(emp_record.ename || '的工资是:' || emp_record.sal);
END;

(5).PL/SQL的运算符

a.算术运算符
运算符含义
+加号
-减号
*乘号
/除号
**乘方

代码示例

DECLARE
  v_num1 NUMBER(3) := 10;
  v_num2 NUMBER(3) := 2;
BEGIN
  dbms_output.put_line(v_num1 + v_num2);--10+2
  dbms_output.put_line(v_num1 - v_num2);--10-2
  dbms_output.put_line(v_num1 * v_num2);--10*2
  dbms_output.put_line(v_num1 / v_num2);--10/2
  dbms_output.put_line(v_num1 ** v_num2);--10^2
END;
b.关系运算符
运算符含义
=等于
<>,!=,~=,^=不等于
<小于
>大于
<=小于等于
>=大于等于

代码示例

DECLARE
  v_num1 NUMBER(3) := &input_n1;--&input_n1是指通过键盘输入值给变量
  v_num2 NUMBER(3) := &input_n2;
BEGIN
  IF (v_num1 = v_num2) THEN
    dbms_output.put_line('v_num1等于v_num2');
  ELSIF (v_num1 < v_num2) THEN
    dbms_output.put_line('v_num1小于v_num2');
  ELSIF (v_num1 > v_num2) THEN
    dbms_output.put_line('v_num1大于v_num2');
  END IF;
  IF (v_num1 <> v_num2) THEN
    dbms_output.put_line('v_num1不等于v_num2');
  END IF;
END;
c.比较运算符
运算符含义
IS NULL是空值
BETWEEN…AND介于两者之间
IN等与列表中的某个值

代码示例

DECLARE
  v_num NUMBER(3) := &input_n1;
BEGIN
  IF (v_num BETWEEN 5 AND 10) THEN
    dbms_output.put_line('v_num在5到10之间');
  ELSE
    dbms_output.put_line('v_num不在5到10之间');
  END IF;
  IF (v_num IN (8, 9.10, 11)) THEN
    dbms_output.put_line('v_num在8,9.10,11中的一个值');
  ELSE
    dbms_output.put_line('v_num不在8,9.10,11中的一个值');
  END IF;
  IF (v_num IS NULL) THEN
    dbms_output.put_line('v_num为空');
  ELSE
    dbms_output.put_line('v_num不为空');
  END IF;
END;
d.逻辑运算符
运算符含义
AND逻辑与
OR逻辑或
NOT取反,如IS NULL,NOT IN
DECLARE
  v_flag1 BOOLEAN := &input_n1;
  v_flag2 BOOLEAN := &input_n2;
BEGIN
  IF (v_flag1 AND v_flag2) THEN
    dbms_output.put_line('and两个条件都为ture,为true');
  ELSE
    dbms_output.put_line('and两个条件都不为ture,则false');
  END IF;
  IF (v_flag1 OR v_flag2) THEN
    dbms_output.put_line('or两个条件都不为false,为true');
  ELSE
    dbms_output.put_line('or两个条件都为false,才为false');
  END IF;
  IF (NOT v_flag1) THEN
    dbms_output.put_line('v_flag1取反为true');
  END IF;
END;

(6).变量赋值

  • 在PL/SQL编程中,变量赋值是一个值得注意的地方。
--语法如下:
variable := expression/value;
--示例
v_var := 5*4;
v_vbr :=20;

字符及数字运算特点:

  • NULL加数字仍是空值:NULL+数字=NULL
  • NULL加(连接)字符,结果为字符:NULL||字符串=字符串

(5)PL/SQL流程控制

1).PL/SQL流程控制分类:

  • 条件控制语句(条件分支语句):if语句和CASE语句
  • 循环语句:LOOP语句
  • 顺序语句:GOTO语句、NULL语句

2).条件控制语句

  • 条件控制语句:用于依据特定情况选择要执行的操作
if语句
a.简单条件判断if-else
--语法格式:
IF condition THEN
	statements;
END IF;

--操作示例:
需求:输入员工编号,判断员工的工资,显示工资小于3000的员工姓名及工资
DECLARE
  v_name emp.ename%TYPE;
  v_sal  emp.sal%TYPE;
BEGIN
  SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = &input_empno;
  IF v_sal < 3000 THEN
    dbms_output.put_line(v_name || '的工资是:' || v_sal);
  END IF;
END;
b.二重条件分支
--语法格式:
IF condition THEN
	statements;
ELSE
	statements;
END IF;

--操作示例:
需求:输入员工编号,判断员工的工资,将工资小于3000的员工工资涨200,并显示涨工资员工姓名及工资,以及其他员工的姓名和工资
DECLARE
  v_name  emp.ename%TYPE;
  v_sal   emp.sal%TYPE;
  v_empno emp.empno%TYPE := &input_empno;--当多次需要使用到员工编号,可以用一个变量来存储输入的员工编号
BEGIN
  SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = v_empno;
  IF v_sal < 3000 THEN
    dbms_output.put_line(v_name || '涨工资前工资是:' || v_sal);
    v_sal := v_sal + 200; --涨工资
    UPDATE emp01 SET sal = v_sal WHERE empno = v_empno;
    COMMIT;--delete insert update 一定要COMMIT;
    dbms_output.put_line(v_name || '涨工资后工资是:' || v_sal);
  ELSE
    dbms_output.put_line(v_name || '的工资是:' || v_sal);
  END IF;
END;
c.多重条件判断
--语法格式:
IF condition THEN
	statements;
ELSIF condition THEN
	statements;
ELSE
	statements;
END IF;

--操作示例:
需求:输入员工编号,判断员工的工资,将工资小于3000,显示低等收入,工资小于6000,显示中等收入,其他显示高等收入
SELECT * FROM emp01;
DECLARE
  v_name  emp.ename%TYPE;
  v_sal   emp.sal%TYPE;
  v_empno emp.empno%TYPE := &input_empno;
BEGIN
  SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = v_empno;
  IF v_sal < 3000 THEN
    dbms_output.put_line(v_name || '的工资是:' || v_sal || '属于低等收入');
  ELSIF v_sal < 5000 THEN
    dbms_output.put_line(v_name || '的工资是:' || v_sal || '属于中等收入');
  ELSE
    dbms_output.put_line(v_name || '的工资是:' || v_sal || '属于高等收入');
  END IF;
END;
CASE语句
  • 使用CASE语句执行多重分支操作,语句更加简洁,执行效率更好

使用CASE语句处理多重条件分支由两种方法

第一种:使用单一选择符进行等值比较
--语法格式:
CASE 要判断的字段或表达式1
WHEN 常量1 THEN 显示的值1或者语句1;
WHEN 常量2 THEN 显示的值2或者语句2;
WHEN 常量3 THEN 显示的值3或者语句3;
....
ELSE 显示的值或者语句n;
END CASE;

--操作示例:
需求:输入成绩等级,判断输入哪个层次,并打印输出。
DECLARE
  v_grade CHAR(1) := UPPER('&n');
BEGIN
  CASE v_grade
    WHEN 'A' THEN
      dbms_output.put_line('优秀');
    WHEN 'B' THEN
      dbms_output.put_line('中等');
    WHEN 'C' THEN
      dbms_output.put_line('一般');
    WHEN 'D' THEN
      dbms_output.put_line('差劲');
    ELSE
      dbms_output.put_line('输入有误!');
  END CASE;
END;
第二种:使用多种条件进行非等值比较
--语法格式:
CASE 
WHEN 条件1 THEN 显示的值1或者语句1; 
WHEN 条件2 THEN 显示的值1或者语句1;
WHEN 条件3 THEN 显示的值1或者语句1;
WHEN 条件4 THEN 显示的值1或者语句1;
ELSE 显示的值或者语句n;
END CASE;

--操作示例:
需求:输入员工编号,获取员工工资,判断工资,如果工资小于1000,补助200;如果工资小于2000,补助100,如果工资小于5000,补助50
--提示:为方便测试,添加数据
CREATE TABLE emp01 as select * from emp;
SELECT * FROM emp01;
INSERT INTO emp01 VALUES(1111,'qzp','MANAGER',NULL,SYSDATE,10000,0,50);
COMMIT;
--开始操作:
DECLARE
  v_name  emp.ename%TYPE;
  v_sal   emp.sal%TYPE;
  v_empno emp.empno%TYPE := &input_empno;
BEGIN
  SELECT ename, sal INTO v_name, v_sal FROM emp01 WHERE empno = v_empno;
  CASE
    WHEN v_sal <= 1000 THEN
      UPDATE emp01 SET comm = NVL(comm, 0) + 200 WHERE empno = v_empno;
      dbms_output.put_line('更新成功!');
    WHEN v_sal <= 2000 THEN
      UPDATE emp01 SET comm = NVL(comm, 0) + 100 WHERE empno = v_empno;
      dbms_output.put_line('更新成功!');
    WHEN v_sal <= 5000 THEN
      UPDATE emp01 SET comm = NVL(comm, 0) + 50 WHERE empno = v_empno;
      dbms_output.put_line('更新成功!');
    ELSE
      dbms_output.put_line('抱歉,你不在我们补助范围之内!');
  END CASE;
  COMMIT;
END;

3).循环语句

a.基本循环LOOP
--语法格式:
LOOP 
	statement1;
	....
END LOOP;

--操作示例:
需求:打印数字1到10
DECLARE
  v_cnt INT := 1;
BEGIN
  LOOP
    dbms_output.put_line(v_cnt);
    EXIT WHEN v_cnt = 10;--跳出循环语句,避免死循环
    v_cnt := v_cnt + 1;
  END LOOP;
END;
b.while循环
--语法格式:
WHILE condition LOOP 
	statement1;
	statement2;
	....
END LOOP;

--操作示例:
需求:打印数字1到10
DECLARE
  v_cnt INT := 1;
BEGIN
  WHILE v_cnt <= 10 LOOP
    dbms_output.put_line(v_cnt);
    v_cnt := v_cnt + 1;
  END LOOP;
END;
c.for循环
--语法格式:
FOR counter in [REVERSE]lower_bound .. upper_bound LOOP
	statements1;
	statements2;
	....
END LOOP;
注意:for默认是自增输出,REVERSE:自减输出

--操作示例:
需求:打印数字1到10
BEGIN
  FOR i IN REVERSE 1..10 LOOP
    dbms_output.put_line(i);
  END LOOP;
END;

4).PL/SQL的嵌套循环

  • 嵌套循环是指在一个循环语句中嵌入另一个循环语句
  • 标号用于标记嵌套块或嵌套循环
  • 使用<<label_name>>定义标号
DECLARE
  v_result INT;
BEGIN
  <<outter>>	--标记外层循环
  FOR i IN 1 .. 5 LOOP
    <<inter>>	--标记内层循环
    FOR j IN 1 .. 5 LOOP
      v_result := i;
      EXIT outter WHEN i = 4;--满足该条件直接退出外循环
    END LOOP inter;
    dbms_output.put_line('内' || v_result);
  END LOOP outter;
  dbms_output.put_line('外' || v_result);
END;

5).退出语句

EXIT:用于直接退出当前循环

EXIT WHEN:用于满足特定条件的情况下退出当前循环

--exit
DECLARE
  v_cnt INT := 1;
BEGIN
  LOOP
    dbms_output.put_line(v_cnt);
    IF v_cnt = 10 THEN
      EXIT;
    END IF;
    v_cnt := v_cnt + 1;
  END LOOP;
END;

--exit when
DECLARE
  v_cnt INT := 1;
BEGIN
  LOOP
    dbms_output.put_line(v_cnt);
    EXIT WHEN v_cnt = 10;
    v_cnt := v_cnt + 1;
  END LOOP;
END;

CONTINUE:用于直接结束当前循环并继续下一组循环

CONTINUE WHEN:用于在满足特定条件时结束当前循环语句并继续下一组循环语句

--continue
DECLARE
  v_cnt INT := 0;
BEGIN
  LOOP
    v_cnt := v_cnt + 1;
    IF v_cnt = 5 THEN
      CONTINUE;--满足条件,退出当前循环,进入下一轮循环
    END IF;
    dbms_output.put_line(v_cnt);
    IF v_cnt = 10 THEN
      EXIT;
    END IF;
  END LOOP;
END;

--continue when
DECLARE
  v_cnt INT := 0;
BEGIN
  LOOP
    v_cnt := v_cnt + 1;
    CONTINUE WHEN v_cnt = 5;--满足条件,退出当前循环,进入下一轮循环
    dbms_output.put_line(v_cnt);
    EXIT WHEN v_cnt = 10;
  END LOOP;
END;

6).PL/SQL的顺序语句

1.GOTO语句
  • GOTO语句用于跳转到特定标号处执行语句
--语法格式:
GOTO label_name;

注意:当使用GOTO跳转到特定标号时,标号后至少要包含一条执行语句

--需求:打印数字1到10,用LOOP语句处理
DECLARE
  v_cnt INT := 1;
BEGIN
  LOOP
    dbms_output.put_line(v_cnt);
    IF v_cnt = 10 THEN
      EXIT;
    END IF;
    v_cnt := v_cnt + 1;
  END LOOP;
END;

--GOTO(不推荐使用)
DECLARE
  v_cnt INT := 1;
BEGIN
  LOOP
    dbms_output.put_line(v_cnt);
    IF v_cnt = 10 THEN
      GOTO end_loop;
    END IF;
    v_cnt := v_cnt + 1;
  END LOOP;
  <<end_loop>>
  dbms_output.put_line('循环结束');--必须存在一条语句
END;
2.NULL语句
  • NULL语句不会执行任何操作,并且会直接将控制传递到下一个语句,使用该语句的主要目的是提高PL/SQL块的可读性
--需求:根据输入的员工编号,判断员工的工资,如果工资小于3000,将员工的补助加工资的2%,并打印输出某员工的奖金更新了
DECLARE
  v_name  emp01.ename%TYPE;
  v_sal   emp01.sal%TYPE;
  v_comm  emp01.comm%TYPE;
  v_empno emp01.empno%TYPE := &input_empno;
BEGIN
  SELECT ename, sal, comm
    INTO v_name, v_sal, v_comm
    FROM emp01
   WHERE empno = v_empno;
  IF v_sal < 3000 THEN
    dbms_output.put_line(v_name || '涨工资前工资是:' || (v_sal + NVL(v_comm, 0)));
    v_comm := NVL(v_comm, 0) + v_sal * 0.2;
    UPDATE emp01 SET comm = v_comm WHERE empno = v_empno;
    COMMIT;
    dbms_output.put_line(v_name || '涨工资后工资是:' || (v_sal + NVL(v_comm, 0)));
  ELSE
    NULL;
  END IF;
END;

7)动态sql

  • 动态SQL是指在PL/SQL编译时SQL语句是不确定的,如根据用户输入的参数的不同来执行不同的操作。编译程序对动态语句部分不进行处理,只是在程序运行时动态创建语句,对语句进行分析,并执行该语句
动态创建SQL有以下几类:

1、DDL语句、DCL语句、非查询的DML语句、单行查询的SELECT语句,这类可以使用EXECUTE IMMEDIATE语句执行。

2、多行查询的SELECT语句可以使用游标来实现。

3、通过DBMS_SQL程序包实现。

使用EXECUTE IMMEDIATE语句结构:
 语法格式:

  EXECUTE IMMEDIATE dynamic_sql_string

  [into define_variable_list]

  [using bind_argument_list];
1.动态创建表t1
DECLARE
  TABLENAME VARCHAR2(20);
  FIELD1    VARCHAR2(20);
  DATATYPE1 VARCHAR2(20);
  FIELD2    VARCHAR2(20);
  DATATYPE2 VARCHAR2(20);
  STR_SQL   VARCHAR2(500);
BEGIN
  TABLENAME := 't1';
  FIELD1    := 'id';
  DATATYPE1 := 'number';
  FIELD2    := 'name';
  DATATYPE2 := 'varchar2(20)';
  STR_SQL   := 'create table ' || TABLENAME || '(' || FIELD1 || ' ' ||
               DATATYPE1 || ',' || FIELD2 || ' ' || DATATYPE2 || ')';
  EXECUTE IMMEDIATE STR_SQL;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(SQLCODE || '  ' || SQLERRM);
    DBMS_OUTPUT.PUT_LINE('操作失败!!!');
END;
2.动态插入数据
--动态处理费查询的DML语句:向刚才创建的表中插入数据
DECLARE
  v_id NUMBER;                    --输入序号;
  v_name VARCHAR(20);             --输入姓名;
  str_sql VARCHAR2(500);          --保存拼接的SQL语句
BEGIN
    v_id := &vid;
    v_name := '&name';
    str_sql := 'insert into t1 values(:1,:2)';      --使用占位符代表变量
    EXECUTE IMMEDIATE str_sql
    USING v_id,v_name;                              --使用变量替换SQL中的占位符,v_id替换:1,v_n
3.查询表中的数据有多少行
--处理单行查询的SELECT举例,查询表中的数据有多少行
DECLARE
    v_count NUMBER;
    str_sql VARCHAR2(500);
BEGIN
    str_sql := 'select count(*) from t1';
    EXECUTE IMMEDIATE str_sql INTO v_count;   --将查询的结果存放到变量v_count中。
    DBMS_OUTPUT.put_line(v_count);
END;

知识链接:https://www.cnblogs.com/zhengcheng/p/4207376.html

21.游标

(1)什么是游

  • 游标是SQL的一个内存工作区,由系统或用户以变量的形式定义。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GCywAHOh-1604889345597)(oracle游标.png)]

(2)游标的类型

  • 显式游标
  • 隐式游标

a.显式游标

显式游标处理的四个步骤

  • 定义游标
CURSOR cursor_name[(parameter_name datatype)] is select_statement;
  • 打开游标
OPEN cursor_name;
  • 提取数据:
FETCH cursor_name INTO variable1[,variable2,...];--注意:每次只能提取一行数据
  • 关闭游标
CLOSE cursor_name;

操作示例

--需求:查询所有员工的员工编号、姓名和职位的信息
DECLARE
  --定义游标
  CURSOR emp_cursor IS
    SELECT empno, ename, job FROM emp;
  --定义引用型变量
  v_empno emp.empno%TYPE;
  v_ename emp.ename%TYPE;
  v_job   emp.job%TYPE;
BEGIN
  --打开游标,执行查询
  OPEN emp_cursor;
  --提取数据
  LOOP
    FETCH emp_cursor
      INTO v_empno, v_ename, v_job;
    dbms_output.put_line('员工编号是:' || rpad(v_empno, 6) || '员工姓名:' ||
                         rpad(v_ename, 10) || '员工职位是:' || v_job);
    --判断游标是否存在数据来退出循环
    IF emp_cursor%NOTFOUND THEN
      EXIT;
    END IF;
  END LOOP;
  --关闭游标
  CLOSE emp_cursor;
END;

显式游标的属性

%FOUND

  • 用于检测游标结果集是否存在数据,如果存在数据,返回TRUE

%NOTFOUND

  • 用于检测游标结果集是否不存在数据,如果不存在数据,返回TRUE

%ISOPEN

  • 用于检测游标是否已经打开,如果已经打开返回TRUE

%ROWCOUNT

  • 用于返回已提取的实际行数

b.游标FOR循环

  • 当使用游标FOR循环时,Oracle会隐含的打开游标,提取数据并关闭游标
--语法如下:
FOR record_name IN cursor_name LOOP
	statements;
END LOOP;

--操作示例:
--需求:查询所有员工的员工编号、姓名和职位的信息
--方式一:使用游标for循环
DECLARE
  CURSOR emp_cursor IS
    SELECT empno, ename, job FROM emp;
BEGIN
  FOR emp_record IN emp_cursor LOOP
    dbms_output.put_line('员工编号是:' || rpad(emp_record.empno, 6) || '员工姓名:' ||
                         rpad(emp_record.ename, 10) || '员工职位是:' ||
                         emp_record.job);
  END LOOP;
END;

--方式二:使用子查询
BEGIN
  FOR emp_record IN (SELECT empno, ename, job FROM emp) LOOP
    dbms_output.put_line('员工编号是:' || rpad(emp_record.empno, 6) || '员工姓名:' ||
                         rpad(emp_record.ename, 10) || '员工职位是:' ||
                         emp_record.job);
  END LOOP;
END;

c.参数游标

  • 参数游标是指带有参数的游标,通过使用参数游标,使用不同参数值可以生成不同的游标结果集
--定义参数游标的语法格式:
CURSOR cursor_name(parameter_name datatype) IS select_statement;
--打开参数游标的语法格式:
OPEN cursor_name(parameter_value);

--操作示例:
DECLARE
  CURSOR emp_cursor(dno NUMBER) IS
    SELECT empno, ename, job FROM emp WHERE deptno = dno;
BEGIN
  FOR emp_record IN emp_cursor(&input_deptno) LOOP
    dbms_output.put_line('员工编号是:' || rpad(emp_record.empno, 6) || '员工姓名:' ||
                         rpad(emp_record.ename, 10) || '员工职位是:' ||
                         emp_record.job);
  END LOOP;
END;

d.隐式游标

显式游标是用户自定义的显式创建的游标,主要是用于对查询语句的处理

隐式游标是由系统隐含创建的游标,主要用于对非查询语句,如修改,删除等操作。则有Oracle系统自动地为这些操作设置游标并创建其工作区,对于隐式游标地操作,如定义、打开、取值及关闭操作,都有Oracle系统自动完成,无需用户进行处理,名字为SQL,这是由Oracle系统定义的。

当系统使用一个隐式游标时,可以通过隐式游标的属性来了解操作的状态和结果,进而控制程序的流程

注意:通过SQL游标名总是只能访问前一个DML操作或单行SELECT操作的游标属性

隐式游标的属性

  • SQL%FOUND
  • SQL%NOTFOUND
  • SQL%ISOPEN
  • SQL%ROWCOUNT
--需求:根据用户输入的员工号,更新指定员工的工资,比如工资涨100
BEGIN
  UPDATE emp01 SET sal = sal + 100 WHERE empno = &input_deptno;
  IF SQL%FOUND THEN
    dbms_output.put_line('更新成功!!!');
    COMMIT;
  ELSE
    dbms_output.put_line('更新失败!!!');
    ROLLBACK;
  END IF;
END;

(3).使用游标修改或删除数据注意点

  • 如果创建的游标需要执行更新或删除的操作必须带有FOR UPDATE子句
CURSOR cursor_name IS select_statement FOR UPDATE [OF column_reference][NOWALT];
UPDATE table_name SET colunm=.. WHERE CURRENT OF cursor_name;
DELETE FROM table_name WHERE CURRENT OF cursor_name;

FOR UPDATE子句会将游标提取出来的数据进行行级锁定,这样在会话更新期间,其他用户的会话就不能对当前游标中的数据行进行更新操作。

--方式一:
DECLARE
  CURSOR emp01_cursor IS
    SELECT empno, ename, job, sal FROM emp01;
BEGIN
  FOR emp_record IN emp01_cursor LOOP
    dbms_output.put_line(emp_record.ename || '的工资是:' || emp_record.sal);
    IF emp_record.job = 'PRESIDENT' THEN
      UPDATE emp01 SET sal = sal + 1000 WHERE empno = emp_record.empno;
    ELSIF emp_record.job = 'MANAGER' THEN
      UPDATE emp01 SET sal = sal + 500 WHERE empno = emp_record.empno;
    ELSIF emp_record.job = 'CLERK' THEN
      UPDATE emp01 SET sal = sal + 300 WHERE empno = emp_record.empno;
    ELSE
      UPDATE emp01 SET sal = sal + 200 WHERE empno = emp_record.empno;
    END IF;
  END LOOP;
  COMMIT;
END;
--方式二:
DECLARE
  CURSOR emp01_cursor IS
    SELECT empno, ename, job, sal FROM emp01 FOR UPDATE;
BEGIN
  FOR emp_record IN emp01_cursor LOOP
    dbms_output.put_line(emp_record.ename || '的工资是:' || emp_record.sal);
    IF emp_record.job = 'PRESIDENT' THEN
      UPDATE emp01 SET sal = sal + 1000 WHERE CURRENT OF emp01_cursor;
    ELSIF emp_record.job = 'MANAGER' THEN
      UPDATE emp01 SET sal = sal + 500 WHERE CURRENT OF emp01_cursor;
    ELSIF emp_record.job = 'CLERK' THEN
      UPDATE emp01 SET sal = sal + 300 WHERE CURRENT OF emp01_cursor;
    ELSE
      UPDATE emp01 SET sal = sal + 200 WHERE CURRENT OF emp01_cursor;
    END IF;
  END LOOP;
  COMMIT;
END;

NOWALT

  • 用于指定不等待锁,如果发现所操作的数据行已经锁定,将不会等待,立即返回
DECLARE
  CURSOR emp01_cursor IS
    SELECT empno, ename, job, sal FROM emp01 FOR UPDATE NOWAIT;
BEGIN
  FOR emp_record IN emp01_cursor LOOP
    dbms_output.put_line(emp_record.ename || '的工资是:' || emp_record.sal);
    IF emp_record.job = 'PRESIDENT' THEN
      UPDATE emp01 SET sal = sal + 1000 WHERE CURRENT OF emp01_cursor;
    ELSIF emp_record.job = 'MANAGER' THEN
      UPDATE emp01 SET sal = sal + 500 WHERE CURRENT OF emp01_cursor;
    ELSIF emp_record.job = 'CLERK' THEN
      UPDATE emp01 SET sal = sal + 300 WHERE CURRENT OF emp01_cursor;
    ELSE
      UPDATE emp01 SET sal = sal + 200 WHERE CURRENT OF emp01_cursor;
    END IF;
  END LOOP;
  COMMIT;
END;

OF column_reference

  • 当游标子查询涉及到多张表时,那么默认情况下会在所有表上加行共享锁,为了只在特定表上加行共享锁,需要在for update子句后带有of子句,of后面跟字段名,如果跟表名或游标名称,则会报错:标示符无效。示例如下:
--需求:输入部门号,显示该部门的部门名称及员工的姓名,并删除该部门下的这些员工
DECLARE
  CURSOR emp01_cursor IS
    SELECT d.dname dname, e.ename ename
      FROM emp01 e
      JOIN dept d
        ON e.deptno = d.deptno
     WHERE e.deptno = &input_deptno
       FOR UPDATE OF e.deptno;
BEGIN
  FOR emp01_record IN emp01_cursor LOOP
    dbms_output.put_line('部门名称:' || emp01_record.dname || '员工姓名是:' ||
                         emp01_record.ename);
    DELETE FROM emp01 WHERE CURRENT OF emp01_cursor;
  END LOOP;
  COMMIT;
END;

22.异常处理

(1).什么是异常?

  • 异常是程序在正常执行过程中发生的未预料的事件
--操作示例:
需求:输入员工编号,判断员工的工资,显示工资小于3000的员工姓名及工资
DECLARE
  v_name emp.ename%TYPE;
  v_sal  emp.sal%TYPE;
BEGIN
  SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = &input_empno;
  IF v_sal < 3000 THEN
    dbms_output.put_line(v_name || '的工资是:' || v_sal);
  END IF;
END;

上述代码有一个很大的问题,如果查找的员工不存在呢,程序会报错,如何解决?

ORA-01403: 未找到任何数据
ORA-06512: 在 line 5

(2).什么是异常处理?

  • 异常处理是为了提高程序的健壮性,使用异常处理部分可以有效地解决程序正常执行过程中可能出现地各种错误,使程序正常运行

(3).异常处理机制

--语法格式:
EXCEPTION
	WHEN first_exception THEN
		statement1;
		....
	WHEN second_exception THEN
		statement1;
		....
	WHEN OTHERS THEN
		statement1;
		....

操作示例:解决上述报的异常问题

DECLARE
  v_name emp.ename%TYPE;
  v_sal  emp.sal%TYPE;
BEGIN
  SELECT ename, sal INTO v_name, v_sal FROM emp WHERE empno = &input_empno;
  IF v_sal < 3000 THEN
    dbms_output.put_line(v_name || '的工资是:' || v_sal);
  END IF;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('员工编号有误');
END;

(4).异常处理地分类

  • 预定义异常
  • 非预定义异常
  • 自定义异常

a.预定义异常

  • 预定义异常是指由PL/SQL所提供地系统异常,Oracle提供了20多个预定义异常,每个预定义异常对应一个特定地Oracle错误,当PL/SQL块出现这些Oracle错误时,会隐含地触发相应地预定义异常
异常码异常名称描述
ORA-00001DUP_VAL_ON_INDEX试图向唯一索引列插入重复值,破环唯一性限制
ORA-00051TIMEOUT_ON_RESOURCE在等待资源时发生超时
ORA-01001INVALID_CURSOR试图使用一个无效地游标
ORA-01012NOT_LOGGED_ON没有连接到ORACLE
ORA-01017LOGIN_DENIED无效地用户名和口令
ORA-01403NO_DATA_FOUNDSELECT INTO 语句中没有返回任何记录。
ORA-01410SYS_INVALID_ROWID从字符串向ROWID转换发生错误
ORA-01422TOO_MANY_ROWSSELECT INTO 语句中返回多于 1 条记录。
ORA-01722INVALID_NUMBER试图将字符串转换为数字,转换失败
ORA-06511CURSOR_ALREADY_OPEN试图打开一个已经打开的游标
ORA-01476ZERO_DIVIDE数字值除零时触发异常
ORA-06500STORAGE_ERROR内存不足引发地内部错误
ORA-06501PROGRAM_ERROR存在PL/SQL内部问题
ORA-06502VALUE_ERROR转换或截断错误
ORA-06504ROWTYPE_MISMATCH宿主游标变量与PL/SQL游标变量地返回类型不兼容
ORA-06530ACCESS_INTO_NULL未定义对象
ORA-06531COLLECTION_IS_NULL集合元素未初始化
ORA-06532SUBSCRITP_OUTSIDE_LIMIT使用嵌套表或VARRAY时,将下标指定为负数
ORA-06533SUBSCRIPT_BEYOND_COUNT元素下标超过嵌套表或VARRAY的最大值
ORA-06592CASE_NOT_FOUNDCASE中若未包含相应的WHEN,并且没有设置
ORA-30625SELF_IS_NULL使用对象类型时,在null对象上调用对象方法
  • 对于预定义异常情况的处理,无需在程序中定义,只需要PL/SQL块的异常部分处理,直接引用相应的异常情况名,并对其完成相应的异常错误处理即可。
--希求:根据输入的工资,查询员工的姓名,并输出员工的姓名及工资
DECLARE
  v_name emp.ename%TYPE;
  v_sal  emp.sal%TYPE := &salary;
BEGIN
  SELECT ename, sal INTO v_name, v_sal FROM emp WHERE sal = v_sal;
  dbms_output.put_line(v_name || '的工资是:' || v_sal);
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('没有该工资的员工');
  WHEN too_many_rows THEN
    dbms_output.put_line('多个员工具有该工资');
  WHEN OTHERS THEN
    dbms_output.put_line('其他异常');
END;

b.非预定义异常

  • 用于处理预定义异常所不能处理的ORACLE错误,此种异常需要在程序中定义

非预定义异常的处理包括3步

1.在PL/SQL块定义部分定义异常情况:

<异常情况> EXCEPTION;

2.将其定义好的异常情况与标准的ORACLE错误联系起来,使用PRAGMA EXCEPTION_INIT语句:

PRAGMA EXCEPTION_INIT(<异常情况>,<错误代码>);

3.在PL/SQL块的异常情况处理部分对异常情况做出相应的处理

--删除dept表中指定部门的信息
BEGIN
  DELETE FROM dept WHERE deptno = &input_deptno;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line('错误代码号:' || SQLCODE || '错误信息:' || SQLERRM);
END;

错误代码号:-2292错误信息:ORA-02292: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 已找到子记录
--解决方法
DECLARE
  --1.定义非预定义异常的标识符
  e_fk EXCEPTION;
  --2.把oracle错误与异常建立关联
  --错误代码号:-2292错误信息:ORA-02292: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 已找到子记录
  PRAGMA EXCEPTION_INIT(e_fk, -2292);
BEGIN
  DELETE FROM dept WHERE deptno = &input_deptno;
  COMMIT;
EXCEPTION
  WHEN e_fk THEN
    --3.捕捉并处理异常
    dbms_output.put_line('此部门下有员工,不能删除');
  WHEN OTHERS THEN
    dbms_output.put_line('错误代码号:' || SQLCODE || '错误信息:' || SQLERRM);
END;

c.用户自定义异常

如果你想在某个特定事件发生时向应用程序的用户发出一些警告信息,而事件本身不会抛出Oracle内部异常,这个异常是属于应用程序的特定异常,那么就需要自定义异常。用户自定义的异常错误是是通过显式使用RALSE语句来触发,当引发一个异常错误时,控制就转向到EXCEPTION块异常错误部分,执行错误处理代码

自定义异常的处理步骤

1.在PL/SQL块的声明部分定义异常情况:

<异常情况> EXCEPTION;

2.使用RALSE触发

RAISE<异常情况>

3.在PL/SQL块的异常情况处理部分对异常情况做出相应的处理

--自定义异常
DECLARE
  v_empno     emp01.empno%TYPE := &input_empno;
  e_no_result EXCEPTION;
BEGIN
  UPDATE emp01 SET sal = sal + 100 WHERE empno = v_empno;
  IF SQL%NOTFOUND THEN
    RAISE e_no_result;
  ELSE
    COMMIT;
  END IF;
EXCEPTION
  WHEN e_no_result THEN
    dbms_output.put_line('数据更新失败!');
  WHEN OTHERS THEN
    dbms_output.put_line('其他异常');
END;

(5).异常处理函数SQLCODE和SQLERRM

  • 异常处理函数用于取得Oracle错误号和错误信息,其中函数SQLCODE是取得错误号,SQLERRM用于取得错误信息,当编写PL/SQL快时,通过在异常处理部分引用函数SQLCODE和SQLERRM,可以取得未预计到的Oracle错误。
  • 通过内置过程RAISE_APPLICATION_ERROR.可以在建立子程序(过程、函数、包)时自定义错误号和错误信息。
DECLARE
  V_EMPNO  EMP.EMPNO%TYPE := &INPUT_EMPNO;
  V_ENAME  EMP.ENAME%TYPE := '&input_ename';
  V_DEPTNO EMP.DEPTNO%TYPE := &DEPTNO;
BEGIN
  INSERT INTO EMP
    (EMPNO, ENAME, DEPTNO)
  VALUES
    (V_EMPNO, V_ENAME, V_DEPTNO);
  IF SQL%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('数据插入成功');
    COMMIT;
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('错误号:' || SQLCODE || '错误信息:' || SQLERRM);
END;

错误号:-2291错误信息:ORA-02291: 违反完整约束条件 (SCOTT.FK_DEPTNO) - 未找到父项关键字

(6).RAISE_APPLICATION_ERROR

  • 该过程用于在PL/SQL子程序中自定义错误信息,测试异常处理,将应用程序专有的错误从服务器端转达到客户端应用程序(其他机器上的SQLPLUS或其他前台开发语言)
--语法格式
raise_application_error(error_number,message);
error_number:用于定义错误号(-2000~-20999)
message:用于指定错误信息,长度不能超过2048个字节

操作示例:

CREATE OR REPLACE PROCEDURE CHANGE_SAL(ENO NUMBER, SALARY NUMBER) IS
BEGIN
  UPDATE EMP02 SET SAL = SALARY WHERE EMPNO = ENO;
  IF SQL%NOTFOUND THEN
    RAISE_APPLICATION_ERROR(-20002, '该员工不存在!!!');
  ELSE
    DBMS_OUTPUT.PUT_LINE('更新成功!!!');
    COMMIT;
  END IF;
END;

ORA-20002: 该员工不存在!!!
ORA-06512: 在 "SCOTT.CHANGE_SAL", line 5
ORA-06512: 在 line 2

23.oracle的存储过程与函数

(1).什么是存储过程和存储函数

相同点:

  • 存储在数据库中的被命名的PL/SQL块,供所有用户程序调用

不同点:

  • 存储过程没有返回值,存储函数有返回值

(2).第一个存储过程与函数的程序

需求:用存储过程或存储函数实现输出"Hello Everyone!"

--创建存储过程
CREATE OR REPLACE PROCEDURE FIRST_PROC IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('我是过程');
  DBMS_OUTPUT.PUT_LINE('Hello world');
END;
--存储过程的调用
BEGIN
  FIRST_PROC;
END;


--创建存储函数
CREATE OR REPLACE FUNCTION FIRST_FUNCTION RETURN VARCHAR2 IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('我是函数');
  RETURN 'Hello world';
END;
--存储函数的调用
BEGIN
  DBMS_OUTPUT.PUT_LINE(FIRST_FUNCTION);
END;

(3).创建存储过程的语法

--语法格式:
CREATE [OR REPLACE] PROCEDURE Procedure_name [(argment1 [{IN | OUT | IN OUT}] type,argment2 [{IN | OUT | IN OUT}] type,....)]
{IS | AS}
	--声明部分:声明变量、常量、复杂数据类型、游标等
BEGIN
	--执行部分
EXCEPTION
	--可选的异常错误处理部分
END;

1.创建一个带有in参数的存储过程

CREATE OR REPLACE PROCEDURE SP_TEST_IN(P_V IN NUMBER) IS
  V_SUM NUMBER;
BEGIN
  -- P_V := 100;--in参数不能赋值,只能使用
  DBMS_OUTPUT.PUT_LINE(P_V);
  V_SUM := P_V + 100;
  DBMS_OUTPUT.PUT_LINE(v_sum);
END;

---调用
BEGIN
  sp_test_in(200);
END;
注意:输入参数,不允许直接给它赋值;可以在程序中使用

2.创建一个带有out参数的存储过程

CREATE OR REPLACE PROCEDURE SP_TEST_OUT(P_V OUT NUMBER) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(P_V);--out输出类型,
  P_V := 100;
  DBMS_OUTPUT.PUT_LINE(P_V);
END;

--调用,out不能直接使用begin end调用;
DECLARE
  V_NUM NUMBER := 50;
BEGIN
  SP_TEST_OUT(V_NUM);
END;
注意:out只能存储赋值之后的数据,入参的数据无法传递,且无法打印

3.创建一个in out的参数存储过程

CREATE OR REPLACE PROCEDURE SP_TEST_IN_OUT(P_V IN OUT NUMBER) IS
BEGIN
  DBMS_OUTPUT.PUT_LINE(P_V);
  P_V := 100;
  DBMS_OUTPUT.PUT_LINE(P_V);
END;

--调用,out不能直接使用begin end调用;
DECLARE
  V_NUM NUMBER := 50;
BEGIN
  SP_TEST_IN_OUT(V_NUM);
END;
注意:in OUT 参数变量既可以传参,又可以被赋值,被修改

(4).存储函数的创建

--语法格式:
CREATE [OR REPLACE] FUNCTION function_name [(argment1 [{IN | OUT | IN OUT}] type,argment2 [{IN | OUT | IN OUT}] type,....)]
RETURN return_type
{IS | AS}
	--声明部分:声明变量、常量、复杂数据类型、游标等
BEGIN
	--执行部分
EXCEPTION
	--可选的异常错误处理部分
END;

1.创建一个带有in参数的存储函数

--根据部门编号返回该部门的总工资
CREATE OR REPLACE FUNCTION ft_first_in(v_deptno IN NUMBER) RETURN NUMBER IS
  v_sumsal NUMBER;
BEGIN
  SELECT SUM(sal) INTO v_sumsal FROM emp WHERE deptno = v_deptno;
  RETURN v_sumsal;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('未找到该部门');
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
--调用
BEGIN
  dbms_output.put_line(ft_first_in(10));
END;

2.创建一个带有out参数的存储函数

--根据员工编号输出员工的姓名和员工的工资,并返回员工的年收入
CREATE OR REPLACE FUNCTION ft_second_out(v_empno IN emp.empno%TYPE,
                                         v_ename OUT emp.ename%TYPE,
                                         v_sal   OUT emp.sal%TYPE)
  RETURN NUMBER IS
  v_salsum NUMBER;
BEGIN
  SELECT ename, sal, (sal + NVL(comm, 0)) * 12
    INTO v_ename, v_sal, v_salsum
    FROM emp
   WHERE empno = v_empno;
   RETURN v_salsum;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('没有此员工');
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;

--------调用
DECLARE
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
BEGIN
  dbms_output.put_line(ft_second_out(7369,v_ename,v_sal));
END;

3.创建一个in out的参数存储函数

--求两个数的平方和,并输出两个数的平方
CREATE OR REPLACE FUNCTION ft_thread_in_out(n1 IN OUT NUMBER,
                                            n2 IN OUT NUMBER)
 RETURN NUMBER IS
BEGIN
  n1 := n1 * n1;
  n2 := n2 * n2;
  RETURN n1 + n2;
END;
--调用
DECLARE
v_n1 NUMBER :=&n1;
v_n2 NUMBER :=&n2;
BEGIN
  dbms_output.put_line(ft_thread_in_out(v_n1,v_n2));
END;

(5).调用存储过程

方法一:Oracle使用EXECUTE语句来实现对存储过程的调用:

EXEC[UTE] Procedure_name(parameter1,parameter2...)

方法二:在PL/SQL代码中直接调用

BEGIN
	Procedure_name(parameter1,parameter2...);
END;

(6).删除存储过程和存储函数

--存储过程
DROP PROCEDURE [user.]Procedure_name;
--存储函数
DROP FUNCTION [user.]Function_name;

注意点:

--根据部门编号返回该部门的总工资
--根据部门编号返回该部门的总工资
CREATE OR REPLACE FUNCTION ft_first_in(v_deptno IN NUMBER DEFAULT 10,v_t IN NUMBER) --使用默认值
RETURN NUMBER IS
  v_sumsal NUMBER;
BEGIN
  SELECT SUM(sal) INTO v_sumsal FROM emp WHERE deptno = v_deptno;
  dbms_output.put_line('测试:'||v_t);
  RETURN v_sumsal;
EXCEPTION
  WHEN no_data_found THEN
    dbms_output.put_line('未找到该部门');
  WHEN OTHERS THEN
    dbms_output.put_line(SQLERRM);
END;
--调用
DECLARE
  v_sum NUMBER;
BEGIN
  v_sum := ft_first_in(v_t => 2);--当参数使用默认值,则用变量名=>来给指定变量赋值
  dbms_output.put_line(v_sum);
END;

24.包

(1)什么是包?

  • 包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合

(2)包的特点

  • 具有面向对象程序设计语言的特点,是对PL/SQL程序设计元素(过程、函数变量等)的封装
  • 它使程序设计模块化

(3).包中的程序元素

  • 公用元素(公用组件)
  • 私用元素(私有组件)

(4).包的组成

一个包由两个分开的部分组成:

  • 包规范(包定义):用于定义包的公用组件,包括常量、变量、游标、过程和函数等
  • 包主体:不仅用于实现包规范所定义的公用过程和函数,而且还可以定义包的私有组件(常量、变量、游标、过程和函数等)

(5).包的语法结构

--创建包的规范
CREATE [OR REPLACE] PACKAGE package_name 
[IS|AS]
  --定义公用常量、变量、游标、过程和函数等
END [package_name];
--创建包体
CREATE [OR REPLACE] PACKAGE BODY package_name 
[IS|AS]
  --定义私有常量、变量、游标、过程和函数等
  --实现公共过程和函数
END first_package;

--包的调用
--对包内共有元素(公用组件)的调用格式为:
包名.元素名称(组件名称)
--包的删除
DROP PACKAGE [BODY] [user.]package_name;

操作示例

--创建包的规范
CREATE OR REPLACE PACKAGE first_package IS
  v_no emp.deptno%TYPE := 10;
  --存储过程
  PROCEDURE query_emp(v_deptno IN NUMBER DEFAULT v_no,
                      v_avgsal OUT NUMBER,
                      v_cnt    OUT NUMBER);
END first_package;

--创建包体
CREATE OR REPLACE PACKAGE BODY first_package IS
  --存储过程
  PROCEDURE query_emp(v_deptno IN NUMBER DEFAULT v_no,
                      v_avgsal OUT NUMBER,
                      v_cnt    OUT NUMBER) IS
  BEGIN
    SELECT AVG(sal), COUNT(*)
      INTO v_avgsal, v_cnt
      FROM emp
     WHERE deptno = v_deptno;
  EXCEPTION
    WHEN no_data_found THEN
      dbms_output.put_line('未找到该部门');
    WHEN OTHERS THEN
      dbms_output.put_line(SQLERRM);
  END;
END first_package;

--调用
DECLARE
  v_avgsal NUMBER;
  v_cnt    NUMBER;
  v_deptno emp.deptno%TYPE := &input_deptno;
BEGIN
  first_package.query_emp(v_deptno, v_avgsal, v_cnt);
  dbms_output.put_line('平均工资' || v_avgsal);
  dbms_output.put_line('总人数' || v_cnt);
END;

(6).子程序重载

  • 所谓重载是指两个或多个子程序有相同的名称,但拥有不同的参数变量、参数顺序或参数数据类型

25.序列

(1).什么时序列?

  • 序列(sequence):是一种用于自动生成唯一数字的数据库对象。主要用于提供主键值

(2).怎么样创建序列及使用?

--创建序列的语法格式:
CREATE SEQUENCE sequence
[START WITH n] --从哪个值开始(初始值)
[INCREMENT BY n]--每次增长的数值(步长)
[{MAXVALUE n | NOMAXVALUE}]--默认是NOMAXVALUE
[{MINVALUE n | NOMINVALUE}]--默认是NOMINVALUE
[{CYCLE | NOCYCLE}] -- 是否需要循环,默认是NOCYCLE
[{CACHE n | NOCACHE}]--是否缓存,默认是NOCACHE

--使用序列
--当使用序列时,必须通过伪列NEXTVAL和CURRVAL来引用序列
--NEXTVAL:用于引用返回下一个序列值
deptno_sequence.nextval
--CURRVAL:用于引用返回当前序列值
deptno_sequence.currval

--查询序列
--查询数据字典视图 USER_SEQUENCES 获取序列定义信息
SELECT * from user_sequences;

--修改序列命令
ALTER SEQUENCE sequence_name
[INCREMENT BY n]--每次增长的数值(步长)
[{MAXVALUE n | NOMAXVALUE}]--默认是NOMAXVALUE
[{MINVALUE n | NOMINVALUE}]--默认是NOMINVALUE
[{CYCLE | NOCYCLE}] -- 是否需要循环,默认是NOCYCLE
[{CACHE n | NOCACHE}]--是否缓存,默认是NOCACHE
注意:序列的初始值不能修改!!!

注意事项:指定cache值,可以提高访问效率,但序列在如下情况会出现序列缺口

  • 回滚
  • 系统异常
  • 多个表同时使用同一序列

操作示例:

--创建序列
CREATE SEQUENCE deptno_sequence
START WITH 50
INCREMENT BY 10
MAXVALUE 70
CACHE 3;

CREATE TABLE dept1 AS SELECT * FROM dept;
SELECT * FROM dept1;
--利用序列来插入值
INSERT INTO dept1 VALUES(deptno_sequence.nextval,'qzp','beijing');
--查看当前的序列值 
SELECT deptno_sequence.currval FROM dual;

27.同义词

(1).什么是同义词

  • 同义词是数据库方案对象的一个别名
    • 方案对象:表、索引、视图、触发器、序列、同义词、存储过程等
    • 非方案对象:表空间、用户、角色等

(2).同义词的作用

  • 简化对象访问
  • 提供对象访问的安全性,多用户协同开发中,可以屏蔽对象名字及其持有者

(3).同义词的分类

  • 公共同义词:指数据库中所有的用户都可以使用
  • 私有同义词:只能被创建它的用户所拥有,其他用户在引用时必须带有方案名

(4).同义词使用

注意:schema指方案名

--创建公共同义词:
CREATE PUBLIC SYNONYM synonym FOR [schema.]object;
--创建私有同义词:
CREATE SYNONYM synonym FOR [schema.]object;

--给权限
GRANT CREATE PUBLIC  SYNONYM TO scott;
GRANT CREATE SYNONYM TO scott;
--创建公共的同义词
CREATE PUBLIC SYNONYM DN FOR scott.emp;
--当前用户下使用公共的同义词
SELECT * FROM DN;
--非当前用户下,对于公共的同义词可以直接使用
SELECT * FROM DN;
--创建私有的同义词
CREATE SYNONYM  ddn FOR scott.emp;
--当前用户下使用私有的同义词
SELECT * FROM ddn;
--非当前用户下,对于公共的同义词可以不可直接使用,必须使用方案名.私有的同义词
SELECT * FROM scott.ddn;

--查看同义词
--当建立同义词时,Oracle会将同义词的信息存放在数据字典里。通过查询数据字典视图USER_SYNONYMS,可以显示当前用户所有同义词的详细信息
SELECT synonym_name,table_owner,table_name FROM user_synonyms;
序列:数据字典:USER_SEQUENCES 对应的同义词:SEQ
索引:数据字典:USER_INDEXES 对应的同义词:IND
同义词:数据字典:USER_SYNONYMS 对应的同义词:SYN

--删除同义词
--删除公共的同义词:
DROP PUBLIC SYNONYM synonym_name;
--删除私有同义词:
DROP SYNONYM synonym_name;

28.索引

(1).什么是索引?

  • 索引是为了加速对表中数据行的检索而创建的一种存储结构

(2).索引的分类

按索引列的个数:

  • 单列索引
  • 复合索引

按索引列值的唯一性:

  • 唯一索引
  • 非唯一索引

(3).索引的创建和使用及删除

--在一个或多个列上创建索引
CREATE INDEX index_name ON table(column[,column]....);

--创建单列索引
CREATE index idx_ename on emp(ename);
--创建复合索引
CREATE index idx_deptno_job on emp(deptno,job);
--创建唯一索引
CREATE unique index index_dname on dept(dname);
--创建非唯一索引
CREATE index idx_job on emp(job);

(4).索引的创建场景

适合创建索引:

  • WHERE子句经常引用的表列上
  • 为了提高多表连接的性能,应该在连接列上建立索引
  • 排序的列上创建索引,可以加快数据排序的速度

不适合创建索引:

  • 表很小
  • 列不经常作为连接条件或出现在WHERE子句中
  • 表经常更新

29.视图

(1).什么是视图?

  • 视图是一个虚拟表,建立在已有表的基础上,以建立视图的表成为基表,向视图提供数据内容的语句为select语句

(2).为什么要使用视图?

  • 安全原因,限制数据访问
  • 视图可使复杂的查询易于理解和使用

(3).视图的分类

  • 简单视图
  • 复杂视图
  • 连接视图
  • 只读视图

(4).视图的创建和使用及删除

--创建视图的语法格式:
CREATE [OR REPLACE] VIEW view_name [(alias[,alias]...)]--alias指字段别名,对应的是子查询中的字段内容
AS subquery--subquery指查询语句
[WITH CHECK OPTION [CONSTRAINT constraint]]--定义约束
[WITH READ ONLY];--是否只读

--创建简单视图:
--建立用于查询员工编号、姓名、工资的视图
CREATE VIEW emp_view AS SELECT empno,ename,sal FROM emp;

--创建连接视图:
--建立用于获得部门号为10的部门号,部门名称及员工信息
CREATE VIEW dept_emp_view AS SELECT d.deptno,d.dname,e.empno,e.ename,e.job,e.sal FROM emp e,dept d where d.deptno = e.deptno and d.deptno = 10;

--创建只读视图:
--建立查看10号部门员工信息的视图
CREATE VIEW emp_view AS SELECT * FROM emp WHERE deptno = 10 WITH READ ONLY;

--复杂视图
--复杂视图是包含函数、表达式或者分组数据的视图,它主要用于执行查询操作
注意:定义复杂视图时,必须要为函数或者表达式定义列别名
--创建用于获得每个岗位平均工资、工资总和、最高工资和最低工资的视图
CREATE VIEW job_view(job,avgsal,sumsal,maxsal,minsal) 
AS SELECT job,avg(sal),sum(sal),max(sal),min(sal)
from emp group by job;

--在创建视图时定义CHECK约束,插入的数据必须时20号部门或者更新的数据不能改变部门号
CREATE VIEW emp_view AS SELECT empno,ename,sal FROM emp WHERE deptno - 20 WITH CHECK OPTION CONSTRAINT chk_view;

--删除视图
DROP VIEW view_name;

--视图上的DML操作:
--查询视图-select
SELECT * FROM emp_view;
--添加数据-insert
insert into emp_view(empno,ename,sal) values(8888,'qzp',6666);
--修改数据-update
UPDATE emp_view set sal = sal + 100 where empno = 8888;
--删除数据-delete
delete from emp_view where empno = 8888;
注意:针对视图的操作(insert、update、delete)实际改变的是基表中的数据
视图中存在 group by 子句,分组函数,distinct关键字,rownum 伪列不能 delete 操作
视图中存在 group by 子句,分组函数,distinct关键字,rownum 伪列、使用表达式定义的列不能 update 操作
视图中存在 group by 子句,分组函数,distinct关键字,rownum 伪列、使用表达式定义的列、视图上没有包含基表的NOT NULL 列不能能 insert 操作

30.触发器

(1)什么是触发器?

  • 触发器是指存放在数据库中,并且被隐含执行的存储过程
  • 当发生特定事件时,Oracle会自动执行触发器的相应代码

(2).触发器的类型

  • DML触发器
  • DDL触发器
  • 替代(instead of)触发器
  • 系统触发器

(3).触发器的组成

触发事件:即在何种情况下触发TRIGGER

触发时间:即该TRIGGER是在触发事件之前(BEFORE)还是之后(AFTER)触发

触发器本身:即该TRIGGER被触发之后的目的和意图,正是触发器本身要做的事情

触发频率:说明触发器内定义的工作被执行的次数

(4).创建第一个触发器

--需求:每次执行删除操作之后,都会信息提示:'这是删除操作!'
SELECT * FROM user_tables;--查看所有用户表
--创建触发器
CREATE TRIGGER first_trigger
  AFTER DELETE 
  ON dept1
BEGIN
  dbms_output.put_line('这是删除的操作!!!');
END;
--删除操作触发触发器
DELETE FROM dept1 WHERE deptno = 10;

(5).DDL触发器

1).什么是DDL触发器?

  • 当创建、修改或者删除数据库对象时,也会引起相应的触发器操作事件,而此时就可以利用触发器来对这些数据库对象的DDL操作进行监控

2).创建DDL触发器语法格式

CREATE [OR REPLACE] TRIGGER tigger_name
[BEFORE | AFTER | INTEAD OF] 
[DDL事件] 
ON [DATABASE | SHCEMA]--DATABASE:针对整个数据库,SHCEMA:针对用户
[WHEN 触发条件]
[DECLARE]
--程序的声明部分;
BEGIN
--程序的代码部分;
END;
NODDL事件触发时机描述
1ALTERBEFORE/AFTER修改对象的结构时触发
2ANALYZEBEFORE/AFTER分析数据库对象时触发
3ASSOCIATE STATISTICSBEFORE/AFTER启动统计数据库对象时触发
4AUDITBEFORE/AFTER开启审核数据库对象时触发
5COMMENTBEFORE/AFTER对数据库对象做注释时触发
6CREATEBEFORE/AFTER创建数据库对象时触发
7DDLBEFORE/AFTER针对出现的所有DDL事件都会触发
8DISASSOCIATE STATISTICSBEFORE/AFTER关闭统计数据库对象时触发
9DROPBEFORE/AFTER删除数据库对象时触发
10GRANTBEFORE/AFTER通过SQL的GRANT命令赋予权限时触发
11NOAUDITBEFORE/AFTER禁用审计数据库对象时触发
12RENAMEBEFORE/AFTER通过SQL的RENAME命令对对象重命名时触发
13REVOKEBEFORE/AFTER通过SQL的REVOKE命令撤销授权时触发
14TRUNCATEBEFORE/AFTER通过SQL的TRUNCATE语句截断表时触发
--需求:禁止SCOTT用户的DDL操作
CREATE OR REPLACE TRIGGER scott_trigger
BEFORE DDL
ON SCHEMA
BEGIN
REAISE_APPLICATION_ERROR(-20005,'SCOTT用户禁止使用所有的DDL操作');
END;

案例分析:

--实现对数据库对象操作的日志记录
--步骤:
--1.创建数据库对象DDL操作日志记录表
--2.创建实现对数据库对象DDL操作记录的触发器
--3.测试

--创建一个日志表
CREATE TABLE object_log(
log_id NUMBER CONSTRAINTS pk_logid PRIMARY KEY,
operatedate DATE NOT NULL,
objecttype VARCHAR2(50) NOT NULL,
objectowner VARCHAR2(50) NOT NULL
);
--创建一个序列
CREATE SEQUENCE object_log_seq;
--创建一个触发器
CREATE OR REPLACE TRIGGER object_trigger
  AFTER CREATE OR DROP OR ALTER ON SCHEMA
BEGIN
  INSERT INTO object_log
    (log_id, operatedate, objecttype, objectowner)
  VALUES
    (object_log_seq.nextval,
     SYSDATE,
     ora_dict_obj_type,--触发DDL数据库对象的用户
     ora_dict_obj_owner);--触发DDL的数据库对象的类型
END;
--测试,创建一个序列
CREATE SEQUENCE test_log;
--查询
SELECT * FROM object_log;

(6).DML触发器

1).什么是DML触发器

  • DML触发器是指基于DML操作所建立的触发器

2).DML触发器的作用

  • DML触发器可用于实现数据安全保护、数据审计、数据完整性、参照完整性、数据复制等功能

3).DML触发器类型

  • 语句触发器

    • 在指定的操作语句之前或之后执行一次,不管这条语句影响了多少行
  • 行触发器

    • 触发语句作用的每一条记录都被触发,在行级触发器中使用 :old和:new伪记录变量,识别值的状态
      • :old 表示操作该行之前,这一行的值
      • :new 表示操作该行之后,这一行的值

4).创建DML触发器语法格式

--语句触发器
CREATE [OR REPLACE] TRIGGER tigger_name
[BEFORE | AFTER] 
[DELETE | INSERT | UPDATE [OF 列名]] 
ON [DATABASE | SHCEMA]
[DECLARE]
--程序的声明部分;
BEGIN
--程序的代码部分;
END;

--行触发器
CREATE [OR REPLACE] TRIGGER tigger_name
[BEFORE | AFTER] 
[DELETE | INSERT | UPDATE [OF 列名]] 
ON [DATABASE | SHCEMA]
[FOR EACH ROW [WHEN 触发条件]]
[DECLARE]
--程序的声明部分;
BEGIN
--程序的代码部分;
END;

5).DML触发器的四个开发示例

--示例一:实现数据安全保护(数据的安全性检查)
--需求;非工作日不可对员工信息进行更改
CREATE OR REPLACE TRIGGER emp_trigger1
BEFORE INSERT OR UPDATE OR DELETE
ON emp
BEGIN
	IF to_char(sysdate,'day') IN ('星期六','星期日')THEN
		RAISE_APPLICATION_ERROR(-20006,'不能在休息日改变员工信息!');
	END IF;
END;
--测试
delete from emp where empno = 7788;
ORA-20006: 不能在休息日改变员工信息!
ORA-06512: 在 "SCOTT.EMP_TRIGGER1", line 3
ORA-04088: 触发器 'SCOTT.EMP_TRIGGER1' 执行过程中出错

--示例二:实现数据审计
--需求:审计员工信息表数据的变化,审计删除时间,以及被删除的雇员名
--创建审计表
CREATE TABLE delete_emp_audit(
       name VARCHAR2(10),
       delete_time DATE
);

--创建触发器
CREATE OR REPLACE TRIGGER del_emp_trigger
AFTER DELETE ON emp
FOR EACH ROW
BEGIN
  INSERT INTO delete_emp_audit VALUES(:old.ename,SYSDATE);
END;

--测试
DELETE FROM emp WHERE empno = 7499;
select * from delete_emp_audit;

--示例三:实现数据完整性(数据确认):数据完整性用于确认数据满足商业逻辑或企业规则,实现数据完整性首选约束,约束无法实现的,可以使用触发器实现数据完整性。
--需求:要求员工涨工资后工作不能低于原来的工资,并且所涨的工资不能超过原工资的50%
CREATE OR REPLACE TRIGGER tr_check_sal
BEFORE UPDATE OF sal ON emp
FOR EACH ROW
WHEN (new.sal<old.sal OR new.sal>old.sal*1.5)
BEGIN
	RAISE_APPLICATION_ERROR(-20028,'工资只升不降,并且升幅不能超过50%');
END;
--测试
UPDATE emp SET sal = sal*1.8 WHERE empno = 7788;
ORA-20028: 工资只升不降,并且升幅不能超过50%
ORA-06512: 在 "SCOTT.TR_CHECK_SAL", line 2
ORA-04088: 触发器 'SCOTT.TR_CHECK_SAL' 执行过程中出错

--示例四:实现参照完整性(比如级联更新)
--需求:级联更新DEPT表的主键列以及EMP表的外部键列
CREATE OR REPLACE TRIGGER upd_cascade_trigger
AFTER UPDATE OF deptno
ON dept
FOR EACH ROW
BEGIN
  UPDATE emp SET deptno = :new.deptno WHERE deptno = :old.deptno;
END;

--测试
UPDATE dept SET deptno = 50 WHERE deptno = 10;

select deptno,ename from emp where deptno = 50;


--案例6
CREATE OR REPLACE TRIGGER modify_stu
AFTER INSERT OR DELETE OR UPDATE OF stu_name  -- 字段
ON student_123                                -- 表
FOR EACH ROW  -- 行级触发
BEGIN 
    IF INSERTING THEN
       INSERT INTO stu_log_12 VALUES(1,'insert',SYSDATE,:NEW.stu_name);
    ELSIF DELETING THEN
       INSERT INTO stu_log_12 VALUES(2,'delete',SYSDATE,:OLD.stu_name);
    ELSIF UPDATING THEN
      INSERT INTO stu_log_12 VALUES(3,'update_old',SYSDATE,:OLD.stu_name);
      INSERT INTO stu_log_12  VALUES(4,'update_new',SYSDATE,:NEW.stu_name);
     END IF;
END;


Select  *  From  stu_log_12 
INSERT INTO student_123 VALUES(1,'NO2','李四',21,'数学系');  --插入一条数据
DELETE student_123 WHERE stu_name='张三';                    --删除一条数据
UPDATE student_123 SET stu_age=19 WHERE stu_name='李四';     --修改李四的年龄
UPDATE student_123 SET stu_name='王二' WHERE stu_name='李四';--修改李四的名称


Select *  From  stu_log_12
Select  *  From  student_123


CREATE OR REPLACE TRIGGER T_NAME
  BEFORE DELETE ON EMP
  FOR EACH ROW
BEGIN
  IF DELETING AND
     SYSDATE < TO_DATE('20201028100000', 'yyyy-mm-dd HH24:mi:ss') THEN
    RAISE_APPLICATION_ERROR(-20001, '该表不允许删除数据');  
  END IF;
END;

Delete  From emp

(7).INSTEAD OF触发器

1).什么是替代触发器?

  • 替代触发器,适用于复杂视图上的一种触发器

2).替代触发器的使用限制

  • 替代触发器只适用于视图
  • 替代触发器不能指定BEFORE和AFGER选项
  • 不能在具有WITH CHECK OPTION选项的视图上建立替代触发器
  • 替代触发器必须包含FOR EACH ROW选项

3).创建替代触发器

--创建视图
CREATE OR REPLACE VIEW emp_dept
AS
SELECT d.deptno,d.dname,e.empno,e.ename
FROM dept d, emp e
WHERE d.deptno = e.deptno;

--创建替代触发器
CREATE OR REPLACE TRIGGER instead_of_trigger
INSTEAD OF
INSERT
ON emp_dept
FOR EACH ROW
DECLARE
    v_temp INT;
BEGIN
    SELECT COUNT(*) INTO v_temp FROM dept WHERE deptno = :new.deptno;
    IF v_temp = 0 THEN
       INSERT INTO dept(deptno,dname)VALUES(:new.deptno,:new.dname);
    END IF;
    SELECT COUNT(*) INTO v_temp FROM emp WHERE empno = :new.empno;
    IF v_temp = 0 THEN
       INSERT INTO emp(empno,ename,deptno) VALUES(:new.empno,:new.ename,:new.deptno);
    END IF;
END;

--测试
INSERT INTO emp_dept VALUES(50,'DEVELOPMENT',2222,'ALICE');

SELECT * FROM EMP_DEPT

(8).系统触发器

  • 系统触发器是由特定系统事件所触发的触发器
  • 系统事件是指与方案相关的数据库事件,它包括startup、shutdown、db_role_change和servererror的四种事件
    • startup事件触发器在启动数据库后触发
    • shutdown事件触发器在关闭数据库之前触发
    • db_role_change事件触发器在改变角色后第一次打开数据库时触发
    • servererror事件触发器在发生Oralce错误时触发
--连接sys用户
con sys/as sysdba;
--创建事件表
CREATE   TABLE   event_table(
	event varchar2(50), 
	event_time date
);

--再创建一个系统触发器
create or replace trigger startup_trigger
after startup on database
begin
  insert into event_table values(ora_sysevent,SYSDATE);
end;

--在SQLPLUS窗口执行下列命令
SHUTDOWN
STARTUP
SELECT  *  FROM event_table;

31.数据备份与恢复

1).数据库的备份的重要性

  • 由于计算机系统的各种软硬件故障,用户的错误操作以及一些恶意破环时不可避免的,因此这就影响到数据的正确性,甚至造成数据损失,服务器崩溃的严重后果,备份可以有效的防止数据丢失,能够把数据库从错误状态恢复到正确状态,恢复是将原来备份的数据信息还原到数据库中

2).数据库备份方案

oracle备份:

  • 物理备份

    • 冷备份(脱机备份):在关闭数据库后进行的完整备份,包括参数文件、所有控制文件、所有数据文件、所有联机重做日志文件,是最快和最安全的方法。
    • 热备份(联机备份)
      • 用户管理备份
      • Oracle管理备份
  • 逻辑备份(导入导出)

--在SQLPLUS中执行如下命令

--导出
--将数据库orcl完全导出
  exp system/oracle@orcl file=c:\oracle_bak\orcl_bak.dmp full=y
  
--将数据库中scott用户的所有对象导出
  exp scott/tiger1@orcl file=c:\oracle_bak\scott_bak.dmp owner=scott
  
--将scott用户中表emp,dept导出
  exp scott/tiger1@orcl file=c:\oracle_bak\table_bak.dmp tables=(emp,dept)
  

--导入
--联机帮助命令:
IMP HELP = Y;
--将备份文件导入到数据库
imp scott/tiger1@orcl file=c:\oracle_bak\scott_bak.dmp ignore=y

--将scott用户的备份文件导入到yanln用户中
imp yanln/yanln@orcl fromuser=scott touser=yanln file=c:\oracle_bak\scott_bak.dmp

触发器导入导出文件

--创建员工表的备份表
CREATE TABLE emp_bak 
AS 
SELECT * FROM emp;

--创建触发器来实现数据的同步备份
--当删除员工后,备份表同步删除
CREATE OR REPLACE TRIGGER syno_bak_trigger
AFTER DELETE
ON emp
FOR EACH ROW
BEGIN
  DELETE FROM emp_bak WHERE empno = :old.empno;
END;

--测试
SELECT * FROM EMP;

DELETE FROM emp WHERE empno = 7499;

select * from emp_bak;

rollback;
  • 4
    点赞
  • 0
    评论
  • 9
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

中文名: Oracle 11g权威指南(第2版) 作者: 谷长勇图书分类: 软件 资源格式: PDF 版本: 扫描版 出版社: 电子工业出版社书号: 9787121145803发行时间: 2011年09月01日 地区: 大陆 语言: 简体中文 简介: 内容简介: 《Oracle 11g权威指南(第2版)》数据库的基础知识入手,全面系统地介绍了Oracle 11g 数据库管理系统的所有特性,并配以翔实的实际用例,论述严谨,深入探讨了这些特性的细节内容,同时具有很强的可操作性和实用性。   《Oracle 11g权威指南(第2版)》内容共37章,分为10大部分。第一部分为Oracle 11g 数据库基础;第二部分为Oracle之SQL与PL/SQL;第三部分为Oracle客户端工具,主要介绍SQL*Plus和SQL Developer;第四部分为Oracle 11g数据库管理,主要介绍管理控制文件/重做日志文件/表空间/数据文件、模式对象管理、表分区和索引分区、用户管理与安全、数据完整性和数据约束等;第五部分为Oracle数据库优化,着重介绍Oracle数据库的系统调整、SQL优化等知识;第六部分为Oracle 11g数据库的备份与恢复;第七部分为Oracle 11g数据库集群技术与高可用性,包括真正应用集群(RAC)、自动存储管理(ASM)、容灾与数据卫士、故障诊断、真正应用测试等内容;第八部分为商业智能与数据仓库,介绍了Oracle 11g数据库在构建企业级数据仓库时的强大功能和诸多新特性;第九部分为非结构化数据库,包括空间数据库和XML数据库内容;第十部分为Oracle 11g数据库的其他新特性。 资源来自网络,如侵犯了您的权利,请点击,谢谢 白盘用户请本页用户评论处1楼找下载链接。 内容截图: 目录: 第一部分 Oracle数据库11g基础 第1章 数据库基础简介 2 本章主要介绍了数据库的基础知识,包括数据库数据库管理系统及关系数据库等基本概念,此外还介绍了目前应用比较广泛的各种关系式数据库系统。 1.1 数据库起源 2 1.2 数据库主要特征 2 1.3 数据库构成 3 1.4 数据库管理系统 3 1.5 关系数据库 4 1.6 本章小结 8 第2章 Oracle 11g概述 9 本章主要介绍了数据库的基础知识、Oracle数据库的特点、发展历史及Oracle的最新数据库版本11g的一些新的特性。 2.1 Oracle数据库发展演进史 9 2.2 Oracle版本号的含义 10 2.3 Oracle网格技术 11 2.3.1 网格概述 11 2.3.2 网格的基本属性 12 2.3.3 Oracle网格基础架构 13 2.4 Oracle 11g新特性 15 2.4.1 企业网格管理的高可用性 15 2.4.2 更加优化的性能 17 2.4.3 简化的信息管理 18 2.4.4 集成的信息 20 2.4.5 内置的业务智能 21 2.4.6 开发平台 22 2.5 本章小结 24 第3章 Oracle 11g数据库的安装、启动及关闭 25 本章详细讲述了Oracle数据库的安装、启动、关闭的整个过程,以及如何使用DBCA创建一个数据库。对数据库管理员而言,这些操作都是最基本的。 3.1 在Windows环境下安装的软硬件要求 25 3.2 在Windows环境下安装Oracle 11g 25 3.3 在Linux环境下安装前的准备 28 3.3.1 安装Oracle 11g对硬件的要求 28 3.3.2 安装Oracle 11g对操作系统的要求 29 3.3.3 安装RedHat AS4操作系统时的注意事项 31 3.3.4 检查安装Oracle 11g所必需的包 33 3.3.5 内核参数配置 33 3.3.6 创建安装数据库所需要的用户(组) 34 3.3.7 添加Oracle用户的限制参数 34 3.3.8 创建安装所需要的目录 34 3.3.9 设置Oracle的环境变量 35 3.3.10 解压缩安装包 35 3.4 在Linux环境下安装Oracle 11g 36 3.4.1 Oracle用户图形终端方式登录 36 3.4.2 安装操作过程 36 3.4.3 登录OEM界面验证安装 40 3.5 Oracle 11g数据库实例的创建 40 3.5.1 在Windows环境下创建数据库 40 3.5.2 在Linux环境下用DBCA创建数据库 41 3.6 Oracle 11g实例的启动和关闭 46 3.6.1 在Windows操作系统下启动和关闭Oracle实例 46 3.6.2 在Linux环境下启动Oracle实例 47 3.6.3 在Linux环境下关闭Oracle实例 49 3.6.4 在Linux环境下启动和关闭Oracle监听进程 49 3.7 本章小结 50 第4章 Oracle数据库结构 51 本章将介绍Oracle数据库的体系结构、服务器结构、Oracle数据库文件及后台进程。 4.1 Oracle体系结构 51 4.1.1 基本术语 51 4.1.2 体系结构图解 52 4.1.3 表空间与数据文件 52 4.1.4 临时表空间与临时文件 54 4.1.5 Oracle存储结构介绍 55 4.1.6 控制文件 55 4.1.7 日志文件 56 4.1.8 服务器参数文件 58 4.1.9 密码文件/跟踪文件/警告日志 59 4.2 Oracle服务器结构 60 4.2.1 Oracle服务器与Oracle实例 60 4.2.2 物理结构与逻辑结构的关系 60 4.2.3 系统全局区(SGA) 61 4.2.4 后台进程 62 4.2.5 程序全局区PGA 64 4.3 数据字典 65 4.3.1 Oracle数据字典的构成 65 4.3.2 Oracle常用的数据字典 65 4.3.3 Oracle常用的动态性能视图 67 4.4 本章小结 67 第二部分 Oracle之SQL&PL/SQL 第5章 SQL语言介绍 70 本章主要介绍了以下内容:SQL语言概述、如何使用简单和复杂的SELECT检索语句、如何使用基本函数、如何使用DDL语句、如何使用DML语句、如何使用描述语句。 5.1 SQL语言概述 70 5.1.1 SQL语言特点 71 5.1.2 SQL语言分类 71 5.1.3 SQL语言的编写规则 71 5.2 使用SELECT检索数据 72 5.2.1 使用FROM子句指定表 73 5.2.2 使用SELECT(必要元素)指定列 73 5.2.3 使用WHERE子句指定行 74 5.2.4 使用ORDER BY子句 79 5.2.5 使用DISTINCT检索唯一的表列值 80 5.2.6 使用算术运算符 81 5.3 基本函数的使用 82 5.3.1 字符函数 82 5.3.2 数字函数 85 5.3.3 日期函数 87 5.3.4 转换函数 87 5.3.5 组函数 88 5.3.6 使用GROUP BY进行数据分组 89 5.3.7 使用HAVING子句限制分组数据 90 5.3.8 Oracle递归函数的使用 91 5.4 复杂SELECT语句的使用 91 5.4.1 集合操作 91 5.4.2 子查询 96 5.4.3 表连接 96 5.4.4 CASE语句的使用 99 5.4.5 强大的DECODE函数 101 5.5 DDL语句的使用 102 5.5.1 常用的数据类型 102 5.5.2 表 104 5.5.3 主键 105 5.5.4 外键 107 5.5.5 约束 108 5.5.6 索引 110 5.5.7 视图 112 5.5.8 序列 113 5.5.9 同义词 114 5.6 DML语句的使用 114 5.6.1 使用INSERT语句插入表数据 115 5.6.2 使用UPDATE语句更新表数据 116 5.6.3 使用DELETE语句删除表数据 117 5.6.4 使用MERGE语句修改表数据 117 5.6.5 TRUNCATE语句的使用 121 5.7 使用描述语句 121 5.8 本章小结 122 第6章 PL/SQL基础编程 123 本章介绍了以下内容:PL/SQL概述、PL/SQL编程、过程和函数、错误处理。 6.1 PL/SQL概述 123 6.1.1 PL/SQL语言 123 6.1.2 PL/SQL主要特性 125 6.1.3 PL/SQL的开发和运行环境 127 6.1.4 运行PL/SQL程序 128 6.2 PL/SQL编程 129 6.2.1 基本语言块 130 6.2.2 字符集和语法注释 130 6.2.3 数据类型和类型转换 132 6.2.4 变量和常量 136 6.2.5 表达式和运算符 138 6.2.6 流程控制 140 6.3 过程和函数 147 6.3.1 过程 148 6.3.2 函数 149 6.4 错误处理 151 6.4.1 预定义异常 151 6.4.2 用户定义异常 153 6.5 本章小结 155 第7章 PL/SQL高级应用 156 本章介绍了以下内容:包的概念和使用、集合的使用及PL/SQL的新特性。 7.1 包 156 7.1.1 包头 156 7.1.2 包体 157 7.1.3 重载 161 7.1.4 包的初始化 163 7.1.5 Oracle内置包 165 7.2 集合 165 7.2.1 index-by表 166 7.2.2 嵌套表 167 7.2.3 可变数组 169 7.2.4 集合的属性和方法 169 7.3 PL/SQL游标 173 7.3.1 创建游标 174 7.3.2 使用游标的FOR循环 178 7.3.3 使用游标变量 180 7.3.4 游标变量实例 187 7.3.5 使用游标表达式 191 7.4 Oracle 11g中PL/SQL的新特性 192 7.5 本章小结 194 第三部分 Oracle客户端工具 第8章 SQL*Plus工具介绍 196 本章介绍了以下内容:如何使用SQL *Plus的与数据库交互、如何使用SQL *Plus的编辑功能、如何使用SQL *Plus格式化查询结果。 8.1 用SQL*Plus与数据库交互 196 8.1.1 SQL*Plus的主要功能 197 8.1.2 启动SQL*Plus连接数据库 197 8.2 使用SQL*Plus的编辑功能 199 8.2.1 SQL语句、PL/SQL块与SQL*Plus命令的区别 199 8.2.2 编辑命令 200 8.2.3 保存命令 202 8.2.4 加入注释 203 8.2.5 运行命令 205 8.2.6 编写交互命令 206 8.2.7 使用绑定变量 213 8.2.8 跟踪语句 216 8.3 使用SQL*Plus格式化查询结果 221 8.3.1 格式化列 221 8.3.2 定义页与报告的标题和维 231 8.3.3 存储和打印结果 237 8.4 本章小结 238 第9章 Oracle SQL Developer工具介绍 239 本章主要介绍了Oracle SQL Developer的使用,包括创建数据库连接,使用SQL Worksheet操作数据库,开发调试PL/SQL语句,运行和创建报表。另外,通过实例演示了如何使用SQL Developer的迁移工作台完成数据库的迁移操作。 9.1 SQL Developer概述 239 9.1.1 SQL Developer版本演化 239 9.1.2 SQL Developer产品特点 239 9.1.3 SQL Developer下载与安装 239 9.2 创建数据库连接 240 9.2.1 连接Oracle数据库 240 9.2.2 连接MySQL数据库 242 9.2.3 连接SQL Server数据库 242 9.3 使用SQL Worksheet操作数据库 242 9.3.1 检索数据 243 9.3.2 插入数据 245 9.3.3 修改数据 246 9.3.4 删除数据 248 9.4 使用SQL Developer开发和调试PL/SQL 249 9.4.1 准备工作 249 9.4.2 创建并编译PL/SQL过程 249 9.4.3 运行PL/SQL过程 254 9.4.4 调试PL/SQL过程 256 9.5 使用SQL Developer运行或创建报表 261 9.5.1 运行预定义报表 261 9.5.2 创建自定义报表 262 9.6 使用SQL Developer导出操作 263 9.6.1 导出数据 264 9.6.2 导出对象定义 265 9.7 使用SQL Developer迁移工作台 266 9.7.1 迁移工作台概述 266 9.7.2 数据迁移实例演示 267 9.8 本章小结 273 第四部分 Oracle 11g数据库管理 第10章 管理控制文件 276 本章主要讲述了Oracle数据库对控制文件的管理。包括控制文件的内容、更新、多路复用及创建和维护。控制文件对数据库的正常运行至关重要,通过本章的学习,读者可以对控制文件有一定的理解。 10.1 控制文件概述 276 10.1.1 控制文件的组成 276 10.1.2 控制文件的大小 277 10.1.3 控制文件更新 277 10.1.4 可复用区与不可复用区 277 10.2 控制文件的多路复用 277 10.2.1 使用init.ora多路复用控制文件 277 10.2.2 使用SPFILE多路复用控制文件 278 10.2.3 添加更多控制文件副本的步骤 278 10.3 查询控制文件信息 279 10.4 控制文件的创建步骤 279 10.5 维护控制文件 281 10.6 本章小结 283 第11章 管理日志文件 284 本章讲述了联机重做日志的作用和管理,联机重做日志是数据库正常运行不可或缺的文件,对于实例出现故障时的正常恢复是十分重要的。 11.1 管理重做日志文件 284 11.2 日志文件组、日志切换和日志归档 284 11.3 了解检查点 285 11.4 查询、新建、删除日志文件 285 11.5 本章小结 287 第12章 管理表空间和数据文件 288 本章主要介绍了Oracle数据库的逻辑结构、默认表空间,以及表空间的创建、设置、删除等管理操作。这些操作在数据库的日常管理中是十分常用的。 12.1 表空间与数据文件的关系 288 12.2 Oracle 11g默认的表空间列表 289 12.2.1 SYSTEM表空间 289 12.2.2 SYSAUX表空间 290 12.3 建立表空间 291 12.3.1 创建表空间的语法 291 12.3.2 选择盘区管理方式建立表空间 292 12.3.3 选择段空间管理方式建立表空间 293 12.3.4 创建非标准块表空间 294 12.3.5 建立大文件表空间 294 12.4 维护表空间与数据文件 295 12.4.1 改变表空间的读写状态 295 12.4.2 改变表空间的名称 296 12.4.3 设置默认表空间 296 12.4.4 删除表空间 297 12.4.5 增建新的数据文件表空间 297 12.4.6 删除表空间中无数据数据文件 298 12.4.7 数据文件的自动扩展设置 298 12.5 管理撤销表空间(UNDO) 299 12.5.1 撤销表空间的作用 299 12.5.2 与撤销表空间有关的参数 300 12.5.3 表空间的基本操作 301 12.6 管理临时表空间 303 12.6.1 临时表空间的概念 303 12.6.2 建立临时表空间 304 12.6.3 查询临时表空间的信息 304 12.6.4 临时表空间组 304 12.7 本章小结 306 第13章 模式对象管理 307 本章讲述了Oracle数据库中最重要的几个模式对象的管理,包括表、索引、视图、同义词、序列等。 13.1 模式与模式对象 307 13.2 管理表 307 13.2.1 建表时的考量 307 13.2.2 列数据类型 308 13.2.3 创建表的语法 309 13.2.4 创建表的实例 310 13.2.5 查看表的信息 311 13.2.6 修改表结构 312 13.2.7 重命名表 313 13.2.8 监控表的存储空间 313 13.2.9 修改表空间的存储设置 314 13.2.10 删除表 314 13.2.11 外部表的管理 314 13.3 索引 316 13.3.1 概念 316 13.3.2 各种类型索引的比较和选择 317 13.3.3 索引的基本操作 317 13.4 视图 320 13.4.1 概念 320 13.4.2 视图的基本操作 320 13.5 同义词 322 13.6 序列 323 13.7 本章小结 323 第14章 表分区与索引分区 324 本章介绍了表分区和索引分区及其在数据库性能优化方面的作用,并通过实例介绍了Oracle 11g中对索引技术最新的增强功能。 14.1 分区技术概述 324 14.2 表分区的方法说明及实例 325 14.2.1 范围分区(Range) 325 14.2.2 散列(Hash)分区 327 14.2.3 列表分区(List) 328 14.2.4 组合分区 328 14.2.5 Interval分区 329 14.2.6 外键分区 330 14.2.7 虚拟列分区 331 14.3 表的分区策略 332 14.4 管理表分区 332 14.4.1 表分区管理的操作列表 332 14.4.2 表分区管理的操作实例 333 14.5 分区表联机重定义 336 14.6 创建索引分区 338 14.6.1 索引分区概念 338 14.6.2 本地索引分区 338 14.6.3 全局索引分区 340 14.7 管理索引分区 340 14.7.1 索引分区管理的操作列表 341 14.7.2 索引分区管理的操作实例 341 14.8 查看分区信息 341 14.9 本章小结 342 第15章 用户管理与安全 343 本章将介绍Oracle系统中的用户管理、权限控制、角色、资源及数据库的审计等方面的内容。 15.1 创建与管理用户账户 343 15.1.1 配置身份验证 343 15.1.2 创建用户的语法 343 15.1.3 创建用户实例 344 15.1.4 修改用户语法与实例 345 15.1.5 删除用户 347 15.2 权限管理 347 15.2.1 权限概述 347 15.2.2 授予系统权限 347 15.2.3 系统权限收回 348 15.2.4 授予对象权限 349 15.2.5 对象权限回收 349 15.2.6 用户与权限查询 350 15.3 角色管理 350 15.3.1 角色概述 351 15.3.2 预定义的Oracle系统角色 351 15.3.3 角色创建与角色授权 353 15.3.4 角色生效/失效 355 15.3.5 角色与权限的查询 355 15.4 资源配置PROFILE 356 15.4.1 PROFILE概述 356 15.4.2 创建PROFILE语法 357 15.4.3 创建PROFILE实例 359 15.4.4 修改资源配置PROFILE 361 15.4.5 显示资源配置PROFILE信息 362 15.5 数据库审计 362 15.5.1 审计概念 362 15.5.2 审计环境设置 362 15.5.3 登录审计 364 15.5.4 数据活动审计 364 15.5.5 对象审计 365 15.5.6 除审计数据 365 15.5.7 查询审计信息 366 15.6 本章小结 366 第16章 数据完整性和数据约束 367 本章介绍了与数据库完整性和一致性有关的重要概念:事务、锁、约束等。 16.1 什么是事务 367 16.2 数据并行性和一致性 368 16.2.1 数据并行性和一致性概述 369 16.2.2 Oracle如何管理数据并行和一致性 369 16.3 锁 372 16.3.1 锁的功能、分类及类型 373 16.3.2 表锁和事务锁 376 16.3.3 检测和解决锁冲突 376 16.3.4 实例分析 379 16.4 数据完整性 381 16.4.1 数据完整性概述 381 16.4.2 完整性约束 382 16.4.3 完整性约束类型 383 16.4.4 约束检验机制 387 16.4.5 约束状态 388 16.5 本章小结 390 第17章 Oracle 11g Enterprise Manager简介 391 Oracle Enterprise Manager是一个功能全面的Oracle图形界面管理工具。自推出现在已经有多个版本了,每个新版本都比前一个版本功能更强。目前Oracle 11g企业管理器简称EM(业内常叫OEM——企业管理器),它是初学者管理Oracle系统的最佳伴侣,本章简单介绍企业管理器的安装配置和使用方法。 17.1 Oracle 11g EM简单介绍 391 17.2 Oracle 11g EM安装配置 392 17.2.1 安装选择说明 392 17.2.2 客户化安装 392 17.2.3 设置与安装数据库控制 393 17.2.4 启动Oracle 11g EM 393 17.2.5 登录Oracle 11g EM 394 17.3 用Oracle 11g EM管理Oracle系统 395 17.3.1 数据库配置管理 396 17.3.2 方案管理 396 17.3.3 数据文件管理 397 17.3.4 数据库用户管理 398 17.4 用EM维护Oracle系统 400 17.4.1 执行导出操作 400 17.4.2 管理(本地)表空间 403 17.4.3 浏览预警文件信息 404 17.5 用Oracle 11g EM进行性能调整 405 17.5.1 查看顶级活动 405 17.5.2 监视Oracle实例是否存在锁 406 17.6 本章小结 406 第五部分 Oracle数据库优化 第18章 Oracle系统调整 408 本章主要介绍了与Oracle性能优化有关的初始化参数的设置及内存区域的调整。Oracle性能优化包括的内容和技巧很多,限于篇幅这里只做简单的介绍。 18.1 Oracle初始化参数文件 408 18.1.1 Oracle的系统参数 408 18.1.2 初始化参数文件SPFILE 408 18.1.3 Oracle 11g新增加和淘汰参数 409 18.2 Oracle动态视图 409 18.3 Oracle SGA调整 412 18.3.1 理解内存分配 412 18.3.2 调整日志缓冲区分配 412 18.3.3 调整共享池分配 414 18.3.4 调整数据库缓冲区 417 18.3.5 SGA调整建议 417 18.4 排序区调整 417 18.4.1 排序区与其他内存区的关系 418 18.4.2 理解排序活动 418 18.4.3 监视排序活动 419 18.4.4 专用模式下排序区的调整 419 18.4.5 共享模式下排序区的调整 421 18.5 Oracle 11g中的自动内存优化功能 422 18.6 本章小结 422 第19章 Oracle SQL语句优化 423 SQL的优化主要与数据库开发人员及应用程序开发人员能否写出高效的SQL语句有关系。其实大多数性能问题往往都是跟SQL效率的低下有很大的关系。本章给大家介绍了编写SQL语句的一些最基本的技巧和需要注意的问题,以及SQL优化器及Oracle 11g中一些SQL优化工具。 19.1 常用的SQL技巧 423 19.1.1 不要用“*”代替所有列名 423 19.1.2 用TRUNCATE代替DELETE 423 19.1.3 在确保完整性的情况下多用COMMIT语句 424 19.1.4 尽量减少表的查询次数 424 19.1.5 用NOT EXISTS替代NOT IN 425 19.1.6 用EXISTS替代IN 425 19.1.7 用EXISTS替代DISTINCT 425 19.1.8 有效利用共享游标 425 19.1.9 以合理的方式使用函数 426 19.2 表的连接方法 427 19.2.1 选择FROM表的顺序 427 19.2.2 驱动表的选择 428 19.2.3 WHERE子句的连接顺序 428 19.3 有效使用索引 428 19.3.1 何时使用索引 429 19.3.2 索引列和表达式的选择 429 19.3.3 选择复合索引主列 429 19.3.4 避免对大表的全表扫描 430 19.3.5 监视索引是否被使用 430 19.3.6 影响索引有效使用的因素 431 19.4 Oracle的优化器与执行计划 431 19.4.1 优化器概念 431 19.4.2 运行EXPLAIN PLAN 432 19.4.3 Oracle 11g中SQL执行计划的管理 432 19.5 Oracle 11g中的执行计划管理 435 19.5.1 执行计划管理的工作原理 435 19.5.2 执行计划管理的实例测试 437 19.6 Oracle 11g中的数据库重演和SQL重演 439 19.6.1 数据库重演 439 19.6.2 SQL重演 439 19.7 Oracle的性能顾问 440 19.7.1 Advisor Central 440 19.7.2 SQL调优顾问 440 19.7.3 SQL访问顾问 440 19.8 本章小结 441 第六部分 Oracle 11g数据库的备份与恢复 第20章 备份与恢复 444 本章介绍了数据库备份与恢复的概念、分类、方法等基本常识。读者通过本章的学习能够对数据库备份和恢复有一个总体的了解,同时还介绍了Oracle 11g中备份和恢复的最新特性。 20.1 备份与恢复概述 444 20.2 备份分类与恢复分类 445 20.2.1 备份分类 445 20.2.2 恢复分类 446 20.3 备份与恢复方法 447 20.4 Oracle 11g的备份和恢复特性 448 20.5 本章小结 449 第21章 RMAN工具的使用 450 本章介绍了Oracle推荐的备份工具RMAN,包括RMAN的基本配置、恢复目录的介绍、通道分配,以及相关的备份和恢复命令、各种不同的备份方法等。RMAN涉及的知识点很多,希望大家多做一些备份和恢复的试验,加深对RMAN的理解。 21.1 RMAN简介 450 21.2 RMAN资料档案库/恢复目录/控制文件 451 21.3 创建恢复目录 451 21.4 RMAN与数据库的连接 453 21.4.1 与目标数据库的连接 453 21.4.2 与恢复目录的连接 453 21.5 注册数据库 454 21.5.1 注册目标数据库 454 21.5.2 实例演示 454 21.6 通道分配 455 21.6.1 通道概述 455 21.6.2 RUN命令介绍 455 21.6.3 自动通道配置 456 21.6.4 手动通道配置 457 21.6.5 显示通道配置参数 458 21.6.6 设置通道控制参数 458 21.7 使用BACKUP命令生成备份集 460 21.7.1 备份集与备份片 460 21.7.2 BACKUP命令语法 460 21.7.3 备份文件的存储格式 461 21.7.4 BACKUP备份实例 462 21.7.5 BACKUP的冷备份与热备份 464 21.8 使用COPY与BACK AS COPY命令 464 21.8.1 COPY命令语法 464 21.8.2 COPY备份实例 465 21.8.3 BACKUP AS COPY命令 466 21.9 备份 467 21.9.1 压缩备份 467 21.9.2 完全备份与增量备份 467 21.9.3 查看备份信息 469 21.10 使用RMAN恢复数据库 470 21.10.1 RESTORE命令 470 21.10.2 RECOVER命令 470 21.10.3 实例解析 470 21.11 本章小结 472 第22章 Oracle闪回技术(Flashback) 473 本章主要讲述了数据库的闪回技术,闪回是数据库进行逻辑恢复的一个快捷工具。本章对Oracle 11g在闪回方面的最新技术,闪回数据归档,也进行了详细介绍。 22.1 闪回技术概述 473 22.2 闪回恢复区 474 22.2.1 闪回恢复区的作用 474 22.2.2 配置闪回恢复区 474 22.2.3 闪回恢复区的文件保留策略 476 22.2.4 使用闪回恢复区 476 22.2.5 与闪回恢复区有关的视图 477 22.3 闪回数据库 477 22.3.1 闪回数据库概述 477 22.3.2 配置闪回数据库 478 22.3.3 使用闪回数据库 480 22.3.4 与闪回数据库有关的视图 481 22.4 闪回表 481 22.4.1 闪回表概念 481 22.4.2 使用闪回表 483 22.5 闪回丢弃 487 22.5.1 回收站概念 487 22.5.2 使用回收站 487 22.5.3 回收站与空间利用 489 22.6 闪回版本查询 491 22.6.1 闪回版本查询概念 491 22.6.2 使用闪回版本查询 491 22.7 闪回事务查询 493 22.7.1 闪回事务查询概念 493 22.7.2 使用闪回事务查询 494 22.8 闪回数据归档 495 22.8.1 闪回数据归档概念 495 22.8.2 闪回数据归档区 496 22.8.3 使用闪回数据归档 497 22.8.4 清除闪回数据归档区数据 500 22.8.5 与闪回数据归档有关的视图 500 22.9 本章小结 501 第23章 使用OEM向导备份和恢复数据库 502 本章介绍了如何使用OEM备份和恢复向导来备份和恢复数据库。对于数据库初学者而言,使用EM备份和恢复向导会使操作更加直观。 23.1 设置首选身份证明 502 23.2 配置备份设置 505 23.3 调度备份 507 23.4 管理当前备份 509 23.5 配置恢复设置 511 23.6 执行恢复 512 23.7 本章小结 513 第24章 逻辑备份及数据导入/导出 514 本章着重介绍了在数据导入/导出时常用的数据泵技术EXPDP/IMPDP,以及传统的EXP/IMP导入/导出工具。 24.1 数据泵技术概述 514 24.2 创建目录对象 515 24.3 使用Data Pump导出数据 516 24.3.1 Data Pump导出说明 516 24.3.2 基于命令行数据泵导出实例 516 24.3.3 基于DBMS_DATAPUMP数据泵导出实例 518 24.4 使用Data Pump导入数据 519 24.4.1 Data Pump导入说明 520 24.4.2 基于命令行数据泵导入实例 521 24.4.3 基于DBMS_DATAPUMP数据泵导入实例 521 24.5 监控Data Pump作业进度 522 24.6 EXP/IMP导出/导入数据 523 24.6.1 EXP/IMP概要说明 523 24.6.2 EXP导出实例 523 24.6.3 IMP导入实例 524 24.6.4 字符集冲突问题 525 24.7 本章小结 526 第七部分 Oracle 11g数据库集群技术与高可用性 第25章 Oracle RAC应用 528 本章主要讲述了RAC的基本概念、系统架构,以及安装、运行、管理RAC所需要的工具,并且通过具体的实例演示了RAC集群数据库的安装。 25.1 RAC概述 528 25.2 Oracle集群件 528 25.3 RAC硬件架构 529 25.4 文件系统和卷管理 529 25.5 OCR与投票磁盘 530 25.6 虚拟IP地址(VIP) 530 25.7 集群校验工具 530 25.8 扩展的远距离集群上的RAC 530 25.9 系统扩展与并行 531 25.10 管理集群数据库 531 25.10.1 企业管理器 531 25.10.2 以滚动的方式给应用打补丁 532 25.11 集群11g R2增强 532 25.12 RAC的安装与配置 534 25.12.1 共享存储方案说明 534 25.12.2 软件包检查 535 25.12.3 创建用户和组及相关配置 536 25.12.4 设置主机名称和网关 538 25.12.5 Linux系统参数配置 539 25.12.6 安装并配置ASM驱动 541 25.12.7 安装cvuqdisk软件包 543 25.12.8 安装grid infrastructure 543 25.12.9 创建ASM磁盘组 555 25.13 安装Oracle数据库软件 558 25.14 使用DBCA创建数据库 563 25.15 TNS配置 569 25.16 检查RAC集群数据库的状态 571 25.17 使用Direct NFS Client 574 25.18 Oracle RAC日常维护 574 25.19 本章小结 575 第26章 自动存储管理(ASM) 576 本章主要介绍了Oracle 11g数据库在ASM方面的新功能。 26.1 ASM概述 576 26.2 Oracle 11g中ASM都有哪些新特征 576 26.2.1 快速重新同步(ASM Fast Mirror Resync) 576 26.2.2 ASM滚动升级 577 26.2.3 为ASM管理员新增了SYSASM权限和OSASM操作系统用户组 579 26.2.4 ASM可扩展性和性能的增强 579 26.2.5 新的ASM命令行(ASMCMD)命令和选项 580 26.3 磁盘组兼容性方面新的属性 580 26.3.1 磁盘组兼容性概要 580 26.3.2 COMPATIBLE.ASM和COMPATIBLE.RDBMS 581 26.3.3 设置磁盘组兼容性的方法 581 26.4 ASM优先镜像读取 582 26.4.1 优先镜像读取新特征概要 582 26.4.2 配置和管理优先读取失败组 582 26.5 ASM快速再平衡 583 26.6 Oracle自动存储管理集群文件系统(ACFS) 583 26.6.1 Oracle ASM动态卷管理器 584 26.6.2 Oralce ACFS快照 585 26.6.3 Oracle ASM配置助手(ASMCA) 585 26.6.4 ASMCMD增强 585 26.6.5 智能数据布局 586 26.6.6 为磁盘驱动器指定扇区大小 586 26.6.7 磁盘组更名 586 26.6.8 Oracle ASM文件访问控制 586 26.6.9 Oracle ASM上的Oracle Cluster Registry和Voting文件 586 26.6.10 Oracle Restart 586 26.6.11 支持Oracle ACFS的Oracle企业管理器 586 26.7 本章小结 587 第27章 容灾与数据卫士 588 本章主要介绍了Oracle 11g Data Guard的概述与入门,如何创建物理与逻辑备用数据库、日志传输服务、日志应用服务、角色管理,以及使用RMAN来备份和恢复文件,在基础知识之上涉及Oracle 11g的一些新的相关特性,如Oracle 数据库企业版新的数据库选件Oracle Active Data Guard、实时查询功能以及压缩的日志传出,同时读取和恢复单个备用数据库,对生产数据库的报告、备份、测试和“滚动”升级。 27.1 Oracle 数据卫士概述 588 27.1.1 数据卫士简介 588 27.1.2 数据卫士体系结构 589 27.1.3 数据卫士配置 590 27.1.4 数据卫士服务 591 27.1.5 数据卫士代理 592 27.1.6 数据卫士保护模式 594 27.1.7 数据卫士进程结构 595 27.1.8 数据卫士功能 596 27.1.9 Oracle 数据卫士提供的好处 597 27.1.10 数据卫士和其他的相关技术 598 27.2 数据卫士入门 599 27.2.1 备用数据库类型 599 27.2.2 管理数据卫士配置的用户接口 602 27.2.3 数据卫士操作的前提条件 606 27.2.4 备用数据库目录结构 607 27.2.5 在线Redo日志、归档Redo日志和备用Redo日志 609 27.3 创建物理备用数据库 610 27.3.1 为备用数据库准备主数据库 610 27.3.2 实施创建操作 613 27.3.3 创建后的步骤 618 27.4 创建逻辑备用数据库 618 27.4.1 准备工作 618 27.4.2 实施创建操作 620 27.4.3 创建后的步骤 624 27.5 数据保护模式 624 27.5.1 数据卫士保护模式 624 27.5.2 设置主数据库的保护模式 625 27.6 日志传输服务 626 27.6.1 概述 626 27.6.2 配置Redo传输服务 627 27.6.3 级联Redo传输目的地 632 27.6.4 监控Redo传输服务 635 27.7 应用服务 639 27.7.1 概述 639 27.7.2 日志应用服务配置选项 642 27.7.3 将Redo数据应用物理备用数据库 644 27.7.4 将Redo数据应用逻辑备用数据库 645 27.8 角色转换 646 27.8.1 角色转换简介 646 27.8.2 包含物理备用数据库的角色转换 651 27.8.3 向逻辑备用数据库的角色转换 656 27.8.4 在故障转移后使用闪回技术 662 27.9 管理物理与快照备用数据库 665 27.9.1 启动与关闭物理备用数据库 665 27.9.2 打开物理备用数据库 666 27.9.3 在物理备用数据库上需要手工干预的主数据库变化 669 27.9.4 通过OPEN RESETLOGS语句进行还原 675 27.9.5 监控主、物理备用和快照备用数据库 676 27.9.6 优化Redo应用 678 27.9.7 管理快照备用数据库 678 27.10 管理逻辑备用数据库 679 27.10.1 概览SQL应用架构 680 27.10.2 控制用户访问逻辑备用数据库上的表格 683 27.10.3 管理与监控和逻辑备用数据库相关的视图 684 27.10.4 监控逻辑备用数据库 689 27.10.5 定制逻辑备用数据库 692 27.10.6 在逻辑备用数据库环境中管理特定工作 698 27.10.7 优化逻辑备用数据库 703 27.10.8 在逻辑备用数据库环境中备份与还原 708 27.11 使用RMAN来备份和恢复文件 709 27.11.1 关于数据卫士配置中的RMAN文件管理 710 27.11.2 关于数据卫士环境中的RMAN配置 711 27.11.3 推荐的RMAN与Oracle数据库配置 711 27.11.4 备份过程 714 27.11.5 在数据卫士环境下注册与注销数据库 717 27.11.6 数据卫士环境下的报告 717 27.11.7 数据卫士环境下的备份维护 717 27.11.8 数据卫士环境下的还原脚本 719 27.11.9 例外的备份环境 723 27.11.10 运用RMAN增量备份来滚动前移物理备用数据库 724 27.12 运用SQL应用升级Oracle 数据库 726 27.12.1 运用SQL应用滚动升级的好处 727 27.12.2 运用SQL应用执行滚动升级的前提条件 727 27.12.3 升级操作中使用的数据与惯例 727 27.12.4 通过创建新逻辑备用数据库执行滚动升级 728 27.12.5 用已有的逻辑备用数据库执行滚动升级 730 27.12.6 用已有的物理备用数据库执行滚动升级 735 27.13 数据卫士案例分享 737 27.13.1 在故障转移之后配置逻辑备用数据库 737 27.13.2 用闪回数据库把失败的主数据库转换成备用数据库 739 27.13.3 在输入OPEN RESETLOGS语句之后使用闪回数据库 742 27.13.4 在指定NOLOGGING句之后进行还原 744 27.13.5 创建使用OMF或Oracle ASM的备用数据库 746 27.13.6 在主数据库Lost-write错误进行还原 748 27.13.7 用RMAN备份把失败的主数据库转换成备用数据库 749 27.13.8 在不重建物理备用数据库的情况下变更主数据库的字符集 753 27.14 本章小结 753 第28章 故障诊断 754 本章主要介绍了故障诊断的一些方法和工具,并提出了相关建议。 28.1 数据库挂死管理器 754 28.1.1 IPS事件打包服务 754 28.1.2 自动诊断知识库(ADR) 755 28.1.3 故障处理的工作流 756 28.1.4 增强网络诊断 756 28.1.5 增强OCI诊断 756 28.1.6 智能解决方案 756 28.2 SQL修复建议器 757 28.3 问题避免 757 28.3.1 自动健康监控 757 28.3.2 健康监控 758 28.4 支撑平台 759 28.5 如何得故障信息 759 28.6 企业管理器Support Workbench支持ASM 760 28.7 本章小结 760 第29章 真正应用测试 761 本章主要介绍了Oracle 11g新增功能——真正应用测试的原理和步骤,包括数据库重放和SQL性能分析器。 29.1 数据库重放 761 29.2 SQL性能分析器 768 29.2.1 SQL性能分析器方法论 769 29.2.2 运行SQL性能分析器 770 29.3 本章小结 772 第八部分 商业智能与数据仓库 第30章 ETL相关的功能 774 本章主要介绍了ETL的基础概念,并描述了与ETL相关的几个Oracle特性,包括变化数据捕捉、物化视图、查询重写、OWB等。 30.1 概 述 774 30.2 变化数据捕捉 774 30.2.1 发布变化数据 778 30.2.2 订阅变化数据 785 30.2.3 Oracle 11g变化数据捕捉功能增强 787 30.3 物化视图 788 30.4 查询重写 790 30.4.1 概述 790 30.4.2 查询重写的先决条件 790 30.4.3 保证查询重写有效 791 30.4.4 与查询重写相关的初始化参数 791 30.4.5 控制查询重写 792 30.4.6 查询重写的准确性 792 30.4.7 激活查询重写的权限 793 30.4.8 示例方案和物化视图 793 30.4.9 如何验证发生过查询重写 794 30.4.10 查询重写示例 795 30.4.11 内嵌视图 795 30.4.12 远程表 796 30.5 Oracle Warehouse Builder 796 30.5.1 OWB方法论 797 30.5.2 OWB的架构图 800 30.5.3 安装与配置 801 30.5.4 数据转换功能 802 30.5.5 缓慢变化维 803 30.6 与数据仓库有关的SQL操作增强 803 30.7 本章小结 804 第31章 多维数据库 805 本章主要介绍了Oracle OLAP多维数据库的基础知识,包括OLAP的访问和创建,并且详细说明了Oracle OLAP的管理工具AWM。本章最后介绍了11g中OLAP新特性。 31.1 OLAP选件概述 805 31.2 OLAP对外接口 806 31.2.1 Java OLAP API 807 31.2.2 关系视图与SQL查询 808 31.2.3 OLAP DML与PL/SQL包 808 31.3 分析工作空间管理器 809 31.3.1 简化维模型的构建 810 31.3.2 实施物理存储模型 810 31.3.3 映射关系源 812 31.3.4 管理生命周期 813 31.3.5 保存模型设计 813 31.4 分析工作区管理器演示 813 31.4.1 授权 814 31.4.2 创建分析工作区 814 31.4.3 创建维 815 31.4.4 创建多维数据集 818 31.4.5 加载和聚合数据 820 31.5 11gr1 OLAP增强功能 821 31.5.1 SQL优化器的增强 821 31.5.2 允许利用Java插件 821 31.5.3 分区和存储顾问(Advisors) 821 31.5.4 基于成本的汇总 822 31.5.5 立方脚本 822 31.5.6 数据库管理的立方刷新 823 31.5.7 自动重写按照立方组织的物化视图 825 31.5.8 数据字典 825 31.5.9 计算模板 825 31.5.10 安全增强 825 31.6 本章小结 827 第32章 数据挖掘 828 本章主要介绍了Oracle数据挖掘的基础知识,包括数据挖掘方法论和Oracle数据库提供的主要挖掘算法,并介绍了11g中数据挖掘的新特性。 32.1 数据挖掘过程 828 32.2 监督式学习算法 830 32.2.1 朴素贝叶斯 830 32.2.2 自适应贝叶斯网络 830 32.2.3 支持向量机 831 32.2.4 属性重要性排序 831 32.2.5 决策树 831 32.3 无监督式学习算法 831 32.3.1 聚类 832 32.3.2 关联规则 832 32.3.3 特征选择 832 32.3.4 文本挖掘和非结构化数据 833 32.4 数据挖掘接口 833 32.5 数据挖掘功能增强 834 32.5.1 自动和嵌入的数据转换 834 32.5.2 数据挖掘方案对象 836 32.5.3 数据挖掘的Java API 837 32.5.4 广义线性模型 837 32.5.5 预测分析:PROFILE 837 32.5.6 SQL预测增强 837 32.5.7 新增视图 838 32.5.8 安全性 839 32.5.9 数据挖掘模型的升级 839 32.5.10 11g中不赞成使用的特性 839 32.5.11 不被支持的特性 840 32.6 本章小结 840 第九部分 非结构化数据库 第33章 空间数据库 842 本章主要介绍了Oracle 空间数据库的基础知识,包括矢量几何体数据和栅格数据的存取方法,并介绍了11g中空间数据库的新特性。 33.1 使空间数据库概述 842 33.2 几何实体数据 843 33.2.1 几何实体类型 843 33.2.2 空间数据模型 843 33.2.3 SDO_GEOMETRY 844 33.2.4 几何实体的元数据 850 33.2.5 空间R树索引 851 33.2.6 过滤和空间关系 852 33.2.7 空间操作符 854 33.2.8 SDO_GEOM包 856 33.2.9 空间聚集函数 857 33.2.10 空间参考坐标系 858 33.2.11 其他 863 33.3 Oracle Spatial 选件 863 33.4 GeoRaster数据 864 33.5 MapViewer 867 33.6 空间数据库11gr1增强特性 868 33.7 空间数据操作演示 870 33.8 本章小结 873 第34章 XML数据库 874 本章主要介绍了Oracle XML数据库的基础知识,包括XMLType的存储方式和创建方法,并介绍了常用的XML存取操作,用示例说明了XML DB的开发。 34.1 Oracle XML DB概述 874 34.1.1 XMLType存储 874 34.1.2 XML DB资料库 875 34.1.3 协议结构 876 34.1.4 API访问 876 34.2 XML DB开发 877 34.2.1 XMLType类型 877 34.2.2 存储索引 880 34.2.3 XML模式 880 34.2.4 注册XML模式 881 34.2.5 XML模式进化 883 34.2.6 XML DB应用开发工具 884 34.3 XML存取操作 884 34.3.1 XQuery 884 34.3.2 SQL/XML二元性 886 34.3.3 XML与关系数据互操作 887 34.3.4 XML运算符 887 34.3.5 XML DB维护工具 888 34.3.6 XML DB全文检索 889 34.3.7 XML DB 11gr1增强 889 34.4 Oracle XML DB示例 892 34.5 本章小结 896 第十部分 Oracle 11g数据库的其他新特性 第35章 Oracle 11g应用增强 898 本章主要介绍了Oracle 11g中一些主要的新增应用特性,包括结果缓存、执行计划管理、高级压缩和SQL方面的一些增强特性。 35.1 结果缓存 898 35.1.1 概述 898 35.1.2 应用演示 900 35.2 执行计划管理 907 35.2.1 概述 907 35.2.2 应用演示 909 35.3 高级压缩 910 35.3.1 概述 910 35.3.2 11g中新压缩特性 911 35.4 数据库控制 914 35.4.1 高级复制变革 915 35.4.2 ASM增强 915 35.4.3 转变管理者-同步与传播 915 35.4.4 增强数据库克隆 915 35.4.5 增强数据库配置(ECM收集) 915 35.4.6 改进数据库主页和性能页 915 35.4.7 增强的自适应测度(metric)阈值 916 35.4.8 融合需求(Fusion) 917 35.4.9 管理Oracle文本索引 917 35.4.10 迁移数据库ASM——企业管理器中的增强 920 35.4.11 增强存储/审计报表和测度 920 35.4.12 增强存储、计划、安全和配置 920 35.4.13 增强等待事件详述 921 35.4.14 工作空间管理 921 35.5 整体数据库管理——真正应用集群的ADDM 921 35.6 LOBs增强 922 35.7 SQL增强 924 35.7.1 SQL指令 924 35.7.2 函数 941 35.7.3 其他增强 944 35.8 本章小结 945 第36章 高级数据管理 946 本章主要介绍了Oracle 11g增强的一些数据库管理和维护特性,并介绍了新增的信息生命周期管理的概念和使用方法。 36.1 增强高级管理特性 946 36.2 信息生命周期管理 949 36.2.1 信息生命周期管理概念 949 36.2.2 Oracle信息生命周期管理方案 950 36.2.3 信息生命周期管理实施 951 36.2.4 信息生命周期管理助手 953 36.2.5 在线数据归档的好处 957 36.3 本章小结 958 第37章 数据库升级 959 本章主要介绍了Oracle数据库升级为11g的方法和步骤,并说明了升级完成后的任务。 37.1 概述 959 37.2 升级过程 960 37.3 11g新增的后台进程 961 37.4 升级准备 962 37.5 升级新版本 963 37.6 升级后的任务 964 37.7 11gr1兼容性和互操作性问题 964 37.8 数据库降级 971 37.9 本章小结 972 参考文献 973
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页

打赏作者

QZP51ZX

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值