Oracle快速入门（1W字）

1.Oracle 11g的简介

什么是数据库

数据库DB：存放数据的仓库

数据库管理系统DBMS：数据库系统中对数据进行管理的软件系统

数据管理员DBA：是负责对数据库进行规划、设计、协调、维护和管理的人员

什么是Oracle

是一款对象关系型数据库

特点

• 性能优越，大型数据库中的典范
• 是对象关系型的数据库管理系统
• 应用广泛
• 在数据安全性与数据完整性控制方面性能优越
• 跨操作平台，跨硬件平台的数据互操作能力

Oracle体系结构

平常所说的Oracle或Oracle数据库指的是Oracle数据库管理系统

那么它有两部分组成：即Oracle数据库和Oracle实例

Oracle数据库

物理存在，就是实时存在的文件

概念：相关的操作系统文件集合，这些文件组织到一起，成为一个逻辑整体，即为Oracle数据库

三种必须的文件

• 数据文件：数据的存储仓库
• 控制文件：记录了数据库的名字。一旦控制文件损毁，数据库会宕机，用户不能编辑，自动生成
• 重做日志文件：事务日志，用于恢复

Oracle数据库必须要与内存里实例合作，才能对外提供数据管理服务

Oracle的实例

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

Oracle实例=进程+进程所使用的内存（SGA）

Oracle数据库和Oracle实例

当某个用户发出更新数据的命令，当数据被修改时

• 系统会产生重做日志，并记录在重做日志缓存区(Redo Log Buffer)中
• 修改后的数据，存在实例中的数据缓冲区(Database Buffer Cache)内
• 重做日志缓存区通过进程LGWR写入到数据的重做日志文件(Redo Log files)
• 数据缓存区的内容通过DBWR写人到数据文件(Data files)中

而DBWR通过控制文件(Control files)找到数据文件

LGWR通过控制文件找到重做日志文件

用户进程和系统进程

当某个用户启动sqlplus时，此时就启动的时用户进程，通过校验用户和密码后，与Oracle服务进程建立连接，才是Oracle后台就启动了服务进程，从而实现数据库的操作。

Oracle数据库与实例区别

实例就是Oracle的进程和内存，数据库就是保存数据的物理文件

一个和多个实例访问一个数据库，但是一个实例一次只能访问一个数据库

Oracle的常用命令

Oracle中常用的SQL Plus命令

• Connect 切换连接用户
• Show user 显示当前登录的用户
• Host<dos命令> 执行操作系统命令
• Spool 路径 导出记录到文本
• Clear screen 清屏
• Desc 显示表结构
• Exit 退出

2.Oracle的用户、权限、角色及表空间的管理

用系统用户登录Oracle

Oracle默认的系统用户

只有合法的用户账号才能访问Oracle数据库

Oracle默认的系统用户

• sys:权限最大的用户，超级用户，只能用sys/dba登录
• system：没有sys权限大，可以直接登录
• sysman:用于管理EM管理的用户
• scott：普通用户，默认密码时tiger

conn sys/oracle as sysdba

conn system/oracle

Oracle用户登录的语法格式

conn sys/oracle @orcl as sysdba

<!--连接默认数据库-->
conn sys/oracle as sysdba 

conn system/oracle

启用Scott用户

给scotty用户解锁

<!--切换到sys用户-->
conn sys/oracle as sysdba

<!--解锁-->
alter user scott account unlock

<!--登录-->
conn scott/tiger

创建表空间

什么时表空间

表空间实际上时数据库上的逻辑存储结构

表空间实际由一个或多个数据文件构成的，数据文件的位置和大小可以由我们用户自己来定义。

表空间的分类

• 永久表空间：系统空间，存储了数据字典，存储了数据信息，用户信息等
• 临时表空间：必须存在，当commit后，会从临时表空间清空，然后存到永久表空间去。
• UNDO表空间：commit后发现搞错了，可以用这个恢复

创建表空间的语法格式

conn sys/oracle as sysdba
<!--创建表空间-->
create tablespace test1_tablespace datafile 'test1file.dbf' size 10m;

<!--创建临时表空间-->
create temporary tablespace temptest1_tablespace tmpfile 'tempfile1.dbf' size 10m;

<!--查询表空间的位置-->
select file_name from dba_data_files where tablespace_name = 'TEST1——TABLESPACE'

Oracle的用户管理

创建用户的语法格式

举例

查看创建的用户

<!--查看所有用户表-->
select username from dba_users;

给创建的用户授权

管理用户

Oracle的角色管理

什么时角色

角色就是一组权限

用户可以给角色赋予指定的权限，然后将角色赋给相应的用户

角色的作用

三种标准的角色

连接
conn /as sysdba
创建用户
create user user01 identified by pass01;
连接（会报错，显示没有连接权限）
conn user user01/pass01
授权
grant conn to user01
连接成功
conn user01/pass01
创建表（会报错，权限不足）
create table test(id number,name varchar2(20));
连接到超级用户
conn /as sysdba
授权资源角色
grant resource to user01
连接子用户
conn user01/pass01
创建表成功
create table test(id number,name varchar2(20));
创建用户2（错误，权限不足）
create user02 identified by pass02
连接超级用户
conn /as sysdba
授权dba角色
grant dba to user01;
创建user02成功
create user02 identified by pass02

为角色赋予权限

• 语法格式

  GRANT 角色 TO 用户；

• 例子

  GRANT manager TO user01,user02;

Oracle用户的权限管理

权限指的时执行特定命令或访问数据库对象的权力

作用：

• 数据库安全性：系统安全性和数据安全性

权限的分类

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

系统权限

授予系统权限的语法格式

回收系统权限的语法格式

查看所有系统权限
select * from system_privilege_map;
创建用户
create user user01 identified by pass01;
进入超级用户
conn /as sysdba
给user01授予连接权限
grant connect to user01;
连接user01
conn user01/pass01
连接超级用户
conn /as sysdba
收回连接权限
revoke connect from user01;
连接失败
conn user01/pass01
连接超级用户
conn /as sysdba
授予权限
grant create session to user01
连接成功
conn user01/pass01

对象权限

授予对象权限的语法格式

回收对象的语法格式

连接超级用户
conn /as sysdba
创建角色
create role manager01;
将权限授予角色
grant select,update,insert on scott.emp to manager01;
将角色授予用户
grant manager01 to user01;
连接用户1
conn user01/pass01
查询scott.emp成功
select * from scott.emp;
查询scott.dept失败
select * from scott.dept;
收回角色权限
revoke select,update,insert on scott.emp from manager01;
连接用户01
conn user01/pass01
查询失败
select * from scott.emp;

Oracle的表空间管理

查看表空间

查看用户的表空间

系统表空间

• SYSTEM 用于存放系统用户的表等的数据信息，系统表空间
• SYSAUX时EXAMPLE的辅助表空间，也是索引表空间
• EXAMPLE安装Oracle11g的实例表空间
• UNDO 回退表空间
• TEMP 临时表空间
• USER 数据库用户创建的数据库对象，和SYSTEM差不多

用户表空间

scott用户不能查看管理权限的表空间

查看系统用户的表空间

系统表空间

如果创建用户没有指定表空间，则系统会归到系统表空间。

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

修改表空间

修改表空间的状态1

修改表空间的状态2

更改表空间为只读状态
alter tablespace test1_tablespace read only;
更改为可读写状态
alter tablespace test1_tablespace read write;

增加数据文件

alter tablespace test1_tablespace add datafile 'test2_datafile.dbf' size 10m;

select file_name from dba_data_files where tablespace_name = 'TEST1_TABLESPACE';

删除表空间

删除数据文件

alter tablespace test1_tablespace drop datafile 'test2_datafile.dbf'

删除表空间

3.Oracle数据库表的管理

Oracle的SQL

SQL

结构化查询语言简称SQL

是一种特殊目的的编程语言

Oracle的SQL

常用的DDL语句

• create table 创建数据库表
• create index 创建数据库表的索引
• drop table 删除数据库表
• drop index 删除数据库表的索引
• truncate 删除表中的所有行
• alter table 更改表结构，增加，修改，删除列
• alter table add constraint 在已有的表上增加约束

常用的DML语句

• insert 添加数据到表中
• update 修改数据库中的数据
• delete 删除数据库中的数据
• select 查询语句

常用的DCL

• grant 将权限或角色授予用户或其他角色
• revoke 撤销访问权限
• lock 对数据的特定部分进行锁定

常用的TCL语句

• commit 提交事务处理
• rollback 事务处理回退
• savepoint 设置保存点

创建表

Oracle中常用的数据类型

• 字符类型

  char 和 varchar

• 数值类型

  例如 number(5,2)最大可存储999.99的浮点数

• 日期时间类型

  DATE类型

• LOB类型

主键约束

约束是Oracle提供的自动保持数据库完整性的一种方法，它通过限制字段中的数据、记录中数据和表之间的数据来保证数据的完整性。

Oracle中的约束

创建表时添加主键约束

修改表主键约束

alter table student 
    add constraint sid_pk primary key(sid);

非空约束

删除约束的方式

唯一性约束

唯一性约束用于指定一个或者多个列的组合值具有唯一性，以防止在列中输入重复的值

唯一性约束的注意事项

创建表时设置唯一性约束

修改表时添加唯一性约束

Oracle的表操作——检查约束

创建表时设置检查（check）约束

外键约束

创建表时设置外键约束

列级设置

表级设置

修改表时添加外键约束

alter table student
    add constraint fk_depid foreign key(depid)
    references department(depid)
    on delete cascade;

修改和删除表

添加列

修改列

删除列

修改表名

删除表

增删改查

都属于DML操作

INSERT 增

DELETE 删

UPDATE 改

SELECT 查

事务

事务可以看作是由对数据库的若干操作组成的一个单元这些操作要么都完成，要么都取消，从而保证数据满足一致性的要求。

事务的组成

为什么使用事务

• 使用事务的原因：保证数据的安全有效
• 当执行事务操作（DML语句）时，Oracle会在被作用表上加表锁，以防止其他用户改变表结构；同时会在作用行上加行锁，以方式其他事务在相应行上执行DML操作。

事务的控制命令

提交事务（COMMIT）

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

回滚事务(ROLLBACK)

• ROLLBACK只能对未提交的数据撤销，已经commit的数据是无法撤销的，因为commit之后已经持久化到数据库中。

检查点（SAVEPOINT）

• 是事务中的一点，用于取消部分,当事务结束时，会自动地删除该事务所定义地所有保存点，当执行ROLLBACK时，通过指定保存点可以回退到指定地点。

设置保存点： SAVEPOINT a;

回滚保存点： ROLLBACK TO a;

回滚全部事务： ROLLBACK;

Oracle数据字典

什么是数据字典

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

数据字典地作用

通过数据，使得我们了解数据库内部地信息。当用户在对数据库中地数据进行操作时遇到困难就可以访问数据字典来查看详细地信息。

Oracle中常用地数据字典

• Oracle中常用地数据字典分为三类，以三种前缀开头：user_* 、 all_* 、dba_*。

Oracle中常用的数据字典

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

4.Oracle的基本查询

Oracle的基本查询语句

基本查询

排除重复行

查询日期列

以简体中文显示日期结果

alter session set nls_language = 'SIMPLE CHINESE';

select empno,ename,hiredate from emp;

以特定格式显示日期结果

Oracle查询语句中算术运算符的使用

使用算数运算符

在算术运算中的控制NULL

当使用例的查询语句查询出来的结果是

空值与默认值

• 空值：当插入数据时，如果没有为特定列提供数据，并且该列没有默认值，那么其结果为NULL
• 在创建表时可以使用default关键字为列设置默认值，在表中插入数据时，如果没有为该列提供关键数据，那么该列将使用默认值。

例子

查询语句中的别名、连接符、字符串

列的别名

举例

连接符

字符串

过滤数据（比较运算）

过滤数据

比较运算符

过滤数据(范围模糊查询)

范围查询 BETWEEN...AND

范围查询 IN

模糊查询LIKE

模糊查询中特殊符号的处理

判断空值IS NULL

过滤数据（逻辑运算）

逻辑运算

逻辑操作符AND

逻辑操作符OR

逻辑运算符NOT

在WHERE子句中混合使用逻辑操作符

排序数据

排序数据ORDER BY 子句

单列升序或降序排序

以升序排序
select ename,sal from emp order by sal asc;
以降序排序
select ename,sal from emp order by sal desc;

使用列别名排序

select empno,ename,sal*12 年收入 from emp order by 年收入 desc;

多列排序

• 当执行排序操作时，不仅可以基于单列进行排序，也可以基于多列进行排序，当以多列进行排序时，首先按照第一列进行排序，当第一列存在相同数据时，以第二列进行排序，以此类推。

select empno,ename,deptno,sal from emp 
    order by deptno asc,sal desc;

5.Oracle的单行函数

字符函数

SQL函数

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

字符函数

统一变成大写
select * from emp where job = upper('salesman');
首字母变成大写，其他变成小写
select emp,initcap(ename) from emp;

数值函数

四舍五入函数ROUND()

截取数字函数TRUNC()

求余数函数MOD()

日期和时间函数

SYSDATE

MONTH_BETWEEN

ADD_MONTHS

NEXT_DAY

LAST_DAY

查询每月最后一天入职的员工有那些

select empno,ename,hiredate from emp where hiredate = last_day(hiredate);

ROUND

TRUNC

转换函数

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

隐式数据类型转换

虽然时字符型但是能够查询出来，因为Oracle隐式转换了
select * from emp where sal>'8000';

显式的数据类型转换

TO_CHAR

TO_DATE

TO_NUMBER

通用函数

处理NULL

NVL

NVL2

NULLIF

COALESCE

条件表达式

CASE表达式

select empno,ename,
case job
when 'CLERK' then '办事员'
when 'SALESMAN' then '销售'
when 'MANAGE' then '经理'
when 'ANALYST' then '分析员'
else '总裁'
end
from emp;

Decode函数

select empno,ename,job,
decode(job,'CLERK','办事员','SALESMAN','销售','MANAGER','经理','总裁') from emp;

嵌套函数

实例1

实例2

6.Oracle的分组函数和数据函数

Oracle的分组函数

分组函数

AVG

MAX

COUNT

WM_CONCAT

distinct 关键字

GROUP BY子句

分组数据

GROUP BY

例子：
求出每个部门的平均工资，要求显示：部门号，部门的平均工资
select deptno,avg(sal) from emp group by deptno;
报错，因为deptno未包含在组函数中
select deptno,avg(sal) from emp

select deptno,job,avg(sal) from emp group by deptno order by deptno;

在Oracle中，在select列表中，所有未包含在组函数当中的列都应当包含在GROUP BY子句中

例子：

求出每个部门的平均工资，要求显示：每个部门的平均工资
select avg(sal) from emp group by deptno;

包含在group by子句中的列不必包含在select列表中

例子：
求出每个部门的员工姓名，要求显示：部门编号、员工姓名
select deptno,wm_concat(ename) from emp group by deptno;

结果

having

having子句

错误，where子句中不允许使用分组函数
select deptno,avg(sal) from emp
where avg(sal)>2500 group by deptno;

通过having子句处理
select deptno,avg(sal) from emp 
group by deptno having avg(sal)>2500;

having子句的语法

上述例子可用以下语句
select deptno,avg(sal) from emp where deptno=10 group by deptno;

分组函数的嵌套

求部门最大工资的最大值

select max(avg(sal)) from emp order by deptno;

7.Oracle的多表查询

笛卡尔集

什么是多表查询

笛卡尔集

• 笛卡尔集是集合的一种
• 假设A和B都是集合，A和B的笛卡尔积用A X B表示
• AXB所形成的集合叫笛卡尔集。

等值与非等值连接

等值连接

等值连接实例
select empno,empno,job,dname from emp,deptno where emp.deptno = dept.deptno;

区分重复的列名

表的别名

多个连接条件时使用AND操作符

不等值连接

上述例子，通过between and实现非等值连接
工资等级表描述了工资范围的等级，比如700-1200属于第一等级

select e.emp,e.ename,e.sal,e.grade from emp e,salgrade s where e.sal between losal and hisal;

外连接

查询上述示例
select d.deptno,d.dname,count(e.empno) from dept d,emp e 
where d.deptno = e.deptno 
group by d.deptno,d.dname;

结果显示中未体现40号部门员工

外连接的语法

• +放在较少的一端

查询上述示例
select d.deptno,d.dname,count(e.empno) from dept d,emp e 
where d.deptno = e.deptno(+) 
group by d.deptno,d.dname;

此时就显示出来第40号员工了

自连接

select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno;

Oracle的set运算符

Set运算符

UNION

去重排序

首先创建两张表
create table emp as select * from emp where deptno in(10,20);

create table emp as select * from emp where deptno in (20,30);

合并显示emp01和emp02表所有雇员的部门编号、员工号、员工姓名

select deptno,empno,ename from emp01
union 
select deptno,empno,ename form emp02;

UNION ALL

不去重不排序

合并显示emp01和emp02表所有雇员的部门编号、员工号、员工姓名

select deptno,empno,ename from emp01
union all
select deptno,empno,ename form emp02;

共19条记录，未去重，未排序

INTERSECT

只要重复的

合并显示emp01和emp02表所有雇员的部门编号、员工号、员工姓名

select deptno,empno,ename from emp01
intersect
select deptno,empno,ename form emp02;

MINUS

差集

合并显示emp01和emp02表所有雇员的部门编号、员工号、员工姓名

select deptno,empno,ename from emp01
minus
select deptno,empno,ename form emp02;

控制结果的排序

• 1代表列位置表示第一列

select deptno,empno,ename from emp01
union all
select deptno,empno,ename form emp02
order by deptno;

升序

使用set表达式的注意事项

8.Oracle的子查询

概述

什么是子查询

• 子查询可以嵌入在DML和DDL语句中

as后面就是子查询
create table empnew
as 
select * from emp;

为什么使用子查询

子查询的语法

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

在Select子句中使用子查询

select deptno,dname,loc,(
                        select count(empno) 
                        from emp 
                        where emp.depno = dept.deptno) cnt 
from dept; 

在having子句中使用子查询

select deptno,avg(sal) from emp 
group by deptno 
having avg(sal)>(
                select max(sal) from emp 
                where deptno =30);

在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;

Oracle的主查询和子查询

什么是主查询和子查询

select * from emp
where job > (
            select job from emp where empno = 7521) 
            and
            (select sal from emp
            where empno = 7934
            )

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

子查询的执行顺序

相关子查询

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

select * 
from emp
where deptno = (
                select deptno from dept
                where dname='ACCOUNTING');
                
select e.*
from emp e,dept d
where e.deptno=d.deptno and d.dname='ACCOUNDTING';

单行子查询

什么是单行子查询

例子

select ename,sal,deptno from emp
where deptno = (
                select deptno from emp 
                where ename = 'JAMES')
        and ename<>'JAMES';
        
ename<>'JAMES'是不包含JAMES

select ename,job,sal from emp 
where sal >= (
            select avg(sal) from emp;

• 单行子查询不能使用group by 子句

多行子查询

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

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

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

• 10号部门最低的工资是1300

Oracle的子查询需要注意的问题

• 不可以在group by子句中使用子查询
• 在TOP-N分析问题中，须对子查询排序
• 单行子查询和多行子查询中的空值问题

问题一

问题二

显示工资信息表中工资最高的前五名员工
select empno,ename,sal from emp 
where rownum <=5 
order by sal desc;

发现结果错误，未对结果排序，因为rownum是最后附加上去的编号。

更改为
显示工资信息表中工资最高的前五名员工
select empno,ename,sal from (
    select * from emp order by sal desc)
    where rownum<=5;

结果正确

问题三

9.Oracle数据库对象——视图

Oracle的视图介绍与创建

常见的数据库对象

什么是视图

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

为什么要使用视图

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

视图的分类

• 简单视图：基于单个表建立的，不包含函数表达式等
• 复杂视图：包含函数，表达式，分组等
• 连接视图：基于多个表建立的视图，简化连接查询
• 只读视图:只能看

怎么创建视图

简单视图

scott没有创建视图的权限

连接超级用户
conn /as sysdba
授权
grant create view to scott;
连接scott
conn scott/tiger
创建视图
create view emp_view
as
select empno,ename,sal from emp;
查询视图
select * from emp_view;
自定义视图
create view emp_view2(员工号，姓名，工资)
as
select empno,ename,sal from emp;
查询视图
select * from emp_view2;

连接视图

create view dept_emp_view
as
select d.deptno,d.dname,e.empno,e.ename,e.job from dept d,emp e
where d.deptno = e.deptno and d.deptno =10;

select * from dept_emp_view;

只读视图

Oracle视图的修改和删除

视图上的DML操作

在创建视图时定义check约束

create view empnew_view2
as 
select * from empnew where deptno = 20
with check option constraint ck_view;

select * from empnew_view2;

• 加了check约束后，当给视图DML时，必须保证deptno=20,也就是满足where条件;

修改视图

删除视图

Oracle的复杂视图

什么是复杂视图

创建复杂视图

create view job_view(job,avgsal,maxsal,minsal)
as
select job,avg(sal),max(sal) from emp group by job;

创建复杂视图必须指定列别名

select * from job_view;

复杂视图上执行DML操作的原则

10.Oracle的其他数据库对象

序列

什么是序列

怎样创建序列

创建序列
    create sequence deptno_seq 
    start with 50
    increment by 10
    maxvalue 70
    cache 3;

怎样使用序列

先创建一个表
create table deptnew
as 
select * from dept;

insert into deptnew(deptno,dname,loc)
values(deptno_seq.nextval,'test_dname','test_loc');

select * from deptnew;

如果重复上述操作将会报错，因为指定序列最大为70,并且没有循环。

查询当前序列值
select deptno_seq.currval from dual;

怎样查询序列

注意事项

• 如果指定cache值，Oracle会先在内存内放入序号，此时速度更快。

修改序列

删除序列

索引

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

为什么使用索引

索引的分类

怎样创建索引

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

什么时候创建索引

什么时候不能创建索引

怎样查询索引

select uic.index_name,uic.column_name,uic_column_position,ui.uniquenses 
where uic.index_name = ui.index_name 
and ui.table_name ="EMP";

怎样删除索引

同义词

什么是同义词

同义词的作用

同义词的分类

创建同义词

授权
conn /sys as sysdba
grant create public synoym to scott;
grant create synoym to scott;

conn scott/tiger
创建同义词
create public synonym dn for scott.dpartnew;
以前使用查询
select * from deptnew;
使用同义词
select * from dn;

查看同义词

Oracle对下列数据字典视图提供的同义词

select synonym_name,table_owner,table_name 
from syn
where synonym_name = 'EN';

select synonym_name,table_owner,table_name 
from ind
where synonym_name = 'EN';

删除同义词

11.PlSql入门

PL/SQL简介

什么是PL/SQL

为什么用PL/SQL

PLSQL特点

PLSQL语法结构

PL/SQL块

PL/SQL块的基本结构

第一个PL/SQL程序

设置在窗口里输出服务器信息
SET SERVEROUTPUT ON

BEGIN
    dbms_output.put_line('hello everyone');
END;
/

PL/SQL块的分类

PL/SQL的变量类型

标识符

变量的命名方法

变量的类型

变量的大小写规则

注释

示例

DECLARE
    v_name VARCHAR(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;
BEGIN
    SELECT ename,sal,hiredate
    INTO v_name,v_sal,v_hiredate
    FROM emp
    WHERE empno = 7369;
    -- 计算所得税
    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;

PLSQL的引用型变量和记录型变量

为什么使用引用型变量和记录型变量

引用型变量

示例

记录型变量

• 可以把记录型变量理解成一个数组

示例

PLSQL的运算符

算术运算符

示例

关系运算符

示例

比较运算符

示例

• &n1为输入参数

变量赋值

字符及数字运算符

PLSQL的条件控制语句——IF语句

PL/SQL流程控制语句分类

条件控制语句

IF语句

简单条件判断

示例

• empno = &no员工号等于输入的员工号

二重条件分支

示例

多重条件分支

示例

PL/SQL的条件控制语句——CASE语句

CASE语句

在CASE语句中使用单一选择符进行等值比较

示例

在CASE语句中使用多种条件比较

示例

• 需手动提交commit

PLSQL的循环语句

循环语句的分类

基本循环

示例

WHILE循环

示例

for循环

示例

• in指定下限和上限

PLSQL的嵌套循环与退出循环

PLSQL的嵌套循环

输出 1，2，3，4

EXIT和EXIT WHEN语句

CONTINUE和CONTINUE WHEN语句

PLSQL的顺序语句

GOTO语句

示例

NULL语句

示例

12.游标

显示游标的处理

什么是游标

• 游标就是一个结果集

游标的类型

• 显示游标
• 隐式游标

显示游标处理的四个步骤

显示游标处理的语法

FETCH语句的说明

示例

-- 查询所有员工的员工号，姓名和职位的信息
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('员工号：'||v_empno||',姓名：'||v_ename||',职位：'||v_job);
        -- 什么时候退出循环
        EXIT WHEN emp_cursor%NOTFOUND;
    END LOOP;
    -- 关闭游标
    CLOSE emp_cursor;
END;

显示游标的四个属性

显示游标的属性

-- 查询所有员工的员工号，姓名和职位的信息
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('员工号：'||v_empno||',姓名：'||v_ename||',职位：'||v_job);
        -- 什么时候退出循环 使用FOUND
        EXIT WHEN NOT emp_cursor%FOUND;
    END LOOP;
    -- 关闭游标
    CLOSE emp_cursor;
END;

-- 查询所有员工的员工号，姓名和职位的信息
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;
    -- 检测游标是否打开
    IF emp_cursor%ISOPEN THEN
        DBMS_OUTPUT.PUT_LINE('游标已打开');
    ELSE
        DBMS_OUTPUT.PUT_LINE('游标没有打开')
    END IF
END;

-- 查询所有员工的员工号，姓名和职位的信息
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('员工号：'||v_empno||',姓名：'||v_ename||',职位：'||v_job);
        -- 提取五行之后退出循环 
        EXIT WHEN NOT emp_cursor%ROWCOUNT=5;
    END LOOP;
    -- 关闭游标
    CLOSE emp_cursor;
END;

案例

游标的FOR循环

游标for循环的语法

示例

-- 查询所有员工的员工号，姓名和职位的信息
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;
    -- 使用for不需要打开和关闭游标，因为隐含打开了
    -- 从记录中提取一行给emp_record记录变量
    FOR emp_record IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('员工号：'||emp_record.v_empno||',姓名：'||emp_record.v_ename||',职位：'||emp_record.v_job);
    END LOOP
END;

示例

-- 查询所有员工的员工号，姓名和职位的信息
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
    -- 使用for不需要打开和关闭游标，因为隐含打开了
    -- 从记录中提取一行给emp_record记录变量
    FOR emp_record IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('员工号：'||emp_record.v_empno||',姓名：'||emp_record.v_ename||',职位：'||emp_record.v_job);
    END LOOP
END;

-- 查询所有员工的员工号，姓名和职位的信息
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
    -- 在for循环中用子查询
    -- 从记录中提取一行给emp_record记录变量
    FOR emp_record IN (SELECT empno,ename,job FROM emp) LOOP
        DBMS_OUTPUT.PUT_LINE('员工号：'||emp_record.v_empno||',姓名：'||emp_record.v_ename||',职位：'||emp_record.v_job);
    END LOOP
END;

参数游标

处理成用户可输入的

隐式游标的处理

显示游标和隐式游标

隐式游标

使用游标修改或删除数据

使用游标修改或删除数据

• 当A用户修改数据1未提交时，B游标此时会更新A之前提交的结果。
• 使用FOR UPDATE B后，A更新只能在等待，直到B完成

语法格式

NOWAIT

防止死锁

• 在for update 后添加了nowait

使用of子句在特定表上加行共享锁

• 在特定表上加上锁

13.异常错误处理

什么是异常

• 如果输入一个不存在的员工号，则会报错

什么时异常处理

异常处理的格式

异常的分类

• 上述代码加入异常处理

预定义异常处理

预定义异常处理

示例

非预定义异常处理

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

示例

• SQLCODE 错误代码
• SQLERRM 错误信息

给用户添加预定义的异常

用户自定义的异常处理

自定义异常的处理步骤

• 上述过程如果输入一个不存在的员工empno号（第六行），触发第七行未找到之后RAISE 自己定义的异常，之后跳到12行自定义异常从而输出数据更新失败

在PLSQL中使用SQLCODE、SQLERRM

异常处理函数

示例

RAISE_APPLICATION_ERROR

示例

14.存储过程与函数

概述

数据库对象

• 以及过程和函数

存储过程和存储函数

存储过程与存储函数的区别

存储过程

• 相当于java中void的处理

存储函数

• 相当于java中非 void的处理

区别就是是否有return

第一个存储过程与函数的程序

-- 创建存储过程
CREATE OR REPLACE PROCEDURE first_proc
IS
BEGIN
    dbms_output.put_line('我是过程');
    dbms_output.put_line('hello everyone');
END;

-- 创建函数
CREATE OR REPLACE FUNCTION first_func
RETURN VARCHAR2
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('我是函数');
    RETURN 'HELLO everyone';
END;

-- 调用存储过程
BEGIN 
    first_proc;
END;

-- 调用存储函数 只能定义变量接收返回或者用以下方式
BEGIN
    dbms_output.put_line(first_func);
END;

存储过程的创建

创建存储过程的语法

存储函数的创建

创建存储函数的语法

-- 创建带有输入参数的存储函数
-- 根据部门编号返回该部门下的总工资
CREATE OR REPLACE FUNCTION func1
(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;


-- 创建带有输出参数的存储函数
-- 根据员工号输出员工的姓名和员工的工资并且返回员工的年收入

CREATE OR REPLACE FUNCTION func2
(v_empno IN emp.empno%TYPE,v_name OUT emp.ename%TYPE,v_sal OUT emp.sal%TYPE)
RETURN NUMBER
IS
    v_salsum NUMBER;
BEGIN
    -- nvl处理空值为0
    SELECT ename,sal,(sal+nvl(comm,0))*12 
    INTO v_name,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;

-- 创建带有输入输出参数的存储函数
-- 求两个树的平方和，并输出两个数的平方
CREATE OR REPLACE FUCTION func3
(n1 IN OUT NUMBER,n2 IN OUT NUMBER)
RETURN NUMBER
AS
BEGIN
    n1 := n1*n1;
    n2 := n2*n2;
    RETURN n1+n2;
END;
    

存储过程的调用与删除

存储过程的调用

调用存储过程

删除存储过程

存储过程的调用和删除

调用存储函数

删除函数

存储过程和函数的比较

15.触发器

触发器的概述

触发器

触发器的类型

触发器的组成

创建第一个触发器

-- 当我们对empnew执行删除操作之后，它就会出现一个提示信息，提示删除操作
CREATE TRIGGER first_trigger
AFTER DELETE 
ON empnew
BEGIN
    DBMS_OUTPUT.put_line('这是删除的操作');
END;
/

DDL触发器

什么是DDL触发器

• 对表，视图等，创建删除等进行监控

创建DDL触发器的语法格式

示例：禁止scott用户的DDL操作

CREATE OR REPLACE TRIGGER scott_trigger
BEFORE DDL
ON SCHEMA
BEGIN
    RAISE_APPLICATION_ERROR(-20005,'scott用户所有的DDL操作');
END;
/

CREATE SEQUENCE test_seq;

• SCHEMA是用户相关

RAISE_APPLICATION_ERROR

示例：实现对数据库对象操作的日志记录

-- 首先切换到system用户
-- 创建日志表
CREATE TABLE object_log(
logid number constraint pk_logid primary key,
operatedate date not null,
objecttype varchar2(50) not null,
objectowner varchar(50) not null
);
-- 创建序列
create SEQUENCE object_log_seq;
-- 创建触发器
CREATE OR REPLACE TRIGGER object_trigger
AFTER CREATE OR DROP OR ALTER
ON DATABASE
BEGIN
    INSERT INTO object_log(logid,operatedate,objecttype,objectowner) values (object_log_seq.nextval,sysdate,ORA_DICT_obj_type,ora_dict_obj_owner);
END;

DDl触发器事件的属性函数

DML触发器

DML触发器

DML触发器的作用

DML触发器类型

• 行级触发器针对的是行
• 语句触发器针对的是表

创建DML触发器的语法格式

DML触发器的四个开发示例

CREATE OR REPLACE TRIGGER emp_trigger1
BEFORE INSERT OR UPDATE OR DELETE 
ON emp
BEGIN
    IF to_char(sysdate,'day') IN ('星期六','星期日') THEN
        RASE_APPLICATION_ERROR(-20006,'不能在休息日改变员工信息');
    END IF;
END;
/

-- 创建触发器
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;

CREATE OR REPLACE TRIGGER tri_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(-20028,'工资只升不降，工资升幅不能超过50%');
END;
/

CREATE OR REPLACE TRIGGER update_cascade_trigger
AFTER UPDATE OF deptno
ON DEPT
FOR EACH ROW 
BEGIN
    -- 当更新部门表的部门编号时也会更新emp中的部门编号
    UPDATE emp SET deptno = :new.deptno WHERE deptno = :old.deptno;
END;

系统触发器

系统事件

16.Oracle的数据备份与恢复

概述

备份和恶恢复的必要性

备份与恢复

‘

备份方案

冷备份（脱机备份）

脱机备份

脱机备份的优点

脱机备份的不足

恢复

脱机恢复

数据的导入和导出

数据导入导出的两种方式

最后

如果这篇文章对你有帮助，请我喝杯咖啡，您的支持就是我的动力