Oracle数据库笔记汇总

稚名miko

于 2024-05-14 18:33:13 发布

阅读量1k

点赞数 22

分类专栏： Oracle数据库文章标签：数据库 oracle 笔记

本文链接：https://blog.csdn.net/keadou/article/details/138857063

版权

Oracle数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

前言

本文包括Oracle数据库的绝大部分的知识的概要汇总，包含代码部分，全篇超过3w字，如果读者在学习相关内容有不理解的地方，可以将本文内的笔记结合百度上的定义，理解各种知识点（我感觉笔记对照百度的话，我自己理解起来没什么难度），也可以直接将本文作为Oracle数据库的学习笔记（不作商用就行），全文内容仅供学习参考。

此外，如果在学习过程中发现问题且无法解决，可以在留言区留言，（万一有好心大佬愿意帮忙呢？）毕竟数据库这玩意一出问题不是表就是数据库连接，我一个初学者也不一定会。

学完Oracle数据库，笔记前前后后记了有几万字，电脑配置也不足了，担心哪天更新的时候弄丢了，就把自己做的思维导图的大纲导出来做了一份笔记汇总，以防万一。

下面这玩意是思维导图，每个框体内的笔记在文中都有具体写出来。

Oracle数据库

概念

Oracle是一种关系型数据库

关系型：

1.以二维表格的形式存储数据

2.表和表之间可以建立关系

常见版本

8i ; 9i internet 走向网络

10g ; 11g grid 网格

12c cloud 云

主要结构

库——表空间——用户——表——数据

只有一个库

常见关系型数据库

Oracle数据库：特点：性能好；价格贵；安全性高；一般大公司喜欢用

MySQL数据库：特点：性能比不上oracle；开源（开放源代码）、免费（6.0之前）；一般中小型公司使用

SQL server数据库： .net（C#）语言无缝衔接

DB2：一般只使用在银行系统中

用户相关操作

1.oracle数据库的安装

（1）解决本地电脑和虚拟机之间不能相互传文件的问题安装VMwareTools

（2）安装过程见视频

（3）验证是否安装成功

用户名：system

密码：123456

主机字符串：orcl

2.oracle数据库的登录和登出

（1）登录

cmd—>sqlplus system(用户名)/123456(密码)

登录成功：开头变成SQL>

（2）查找表格：select * from tabs；

（3）退出登录：

//了解

①右上角点×（不推荐）

②使用quit命令

③使用exit命令

3.使用远程工具连接oracle

（1）需要安装jdk1.8

（2）使用远程工具连接

起名规则：oracle + 用户名

端口：1521

服务名：orcl

（3）远程连接工具的配置

显示行号、修改字体大小：首选项—>代码编辑器—>行装订线/字体

用户相关说明

sys：超级管理员 oracle工程师

system：系统管理员

dba数据库管理员

scott：普通用户

密码一般默认为：tiger ——我们使用的用户

操作时使用自己创建的数据库

Oracle相关的服务

1.服务的概念

运行在电脑上的程序

2.怎么查看电脑的服务

（1）此电脑—>右键选择管理—>服务与应用程序—>服务

（2）cmd—>services.msc

3.oracle相关服务

查找方式：单击任意一个服务，在键盘中敲入服务首字母，系统自动索引至列表中首字母相同的第一个服务

OracleDBSonsoleord 和控制台相关的服务（不重要，可以不启动）

OracleJobSchedulerORCL 和定时器相关的服务（不重要，可以不启动）

OracleOraDb10g_home1iSQL*Plus 和sqlplus相关的服务（不重要，可以不启动）

OracleOraDb10g_home1TNSListener 监听器的服务（重要，需要启动）

OracleServiceORCL 数据库主服务（重要，需要启动）

SQL语言

概念

1.sql语言：

所有的关系型数据库的通用语法（规则）

2.方言：

每一种关系型数据库特有的规则

3.sql语言的规则

（1）sql语言多个单词之间使用空格隔开

（2）sql语句可以是单行的，也可以是多行的

（3）sql语言不区分大小写

4.sql语言的注释

（1）单行注释

~ 注释的内容

（2）多行注释

/* 注释的内容 */

分类

C —> D —> M —> Q

库 —> 表 —> 数据（增删改） —> 数据（查）

DCL

管理数据库的访问权限

要求：必须使用system或者sys账户来操作选中需要运行的代码，再点击运行（需要运行什么选择什么）

创建表空间

--创建一个表空间 iweb501tp
  
  create tablespace iweb501tp
  datafile 'C:\work\0426\iweb501tp.dbf'
  size 100m
  autoextend on  --自动扩容
  next 10m;

创建一个用户

格式：
CREATE USER <user_name> [profile "DEFAULT"] identified by "<password>" [default tablespace "USERS"]
```
--创建一个名为lisi的用户
create user lisi
identified by "1234"--设置密码为1234
default tablespace iweb501tp;--挂在iweb501tp的表空间内
```
创建完用户，使用工具连接，发现问题：没有权限登录
给用户授权

3.给用户授权格式：

GRANT <权限列表> to <user_name>;

--创建一个名为lisi的用户
create user lisi
identified by "1234"--设置密码为1234
default tablespace iweb501tp;--挂在iweb501tp的表空间内

DDL

用来操作表的操作：crud 增删改查
- create创建新增
- 格式：
  
  CREATE TABLE <table_name> ( column1 DATATYPE [NOT NULL] [PRIMARY KEY], column2 DATATYPE [NOT NULL], ... [constraint <约束名> 约束类型 (要约束的字段) ... ] ）
```
--创建一个学生表
create table stud1(
id number,
name varchar2(20)
);

--创建一个学生比啊，no、name、score、birthdady
create table stu2(
no number(5),
name varchar(20),
score number(3,1),
birthday date
);
```
  复制表：格式：CREATE TABLE <table_name> as <SELECT 语句>
```
--复制表stu2 包括表结构和表数据
create table stu3 as select * from stu2;

--复制表stu2 只复制表结构，不复制表数据
create table stu4 as select * from stu2 where 1=2;
```
  where 1=2指永远不成立的等式，使数据不被复制。
- retrieve查询
- ```
--查看表的结构
desc stu1;
```
- update修改
  
  （1）添加表字段 ALTER TABLE <table_name> ADD (字段1 类型 [NOT NULL], 字段2 类型 [NOT NULL] .... );
```
-- 在stu1中添加一个sex性别列，age年龄列
alter table stu1 add(
sex varchar2(4),
age number
)
```
  （2）修改表中字段 ALTER TABLE <table_name> modify(字段1 类型, 字段2 类型 .... );
```
-- 修改stu1表中的age类型为number(4)，把sex改成varchar2(6)
alter table stu1 modify(
sex varchar2(6),age number(4)
);
```
  （3）修改表中字段名 ALTER TABLE <table_name> rename(字段1, 字段2 .... );
```
-- 修改字段名称sex——>gender
alter table stu1 rename column sex to gender;
```
  （4）删除表中字段 ALTER TABLE <table_name> drop(字段1, 字段2 .... );
```
-- 删除stu1表中的gender字段和age字段
alter table stu1 drop (gender,age);
```
  （5）修改表的名称 RENAME <table_name> to <new table_name>;
```
-- 修改表的名称
rename stu1 to stu;
```
- delete删除
  
  删除表 DROP TABLE <table_name>;
```
-- 删除表stu3
drop table stu3;
```
  - 总结 create drop alter desc

DML

操作数据（增删改）

create增加

insert语句

添加单条数据：

格式：
INSERT INTO table_name (column1,column2,...) values ( value1,value2, ...);

注意事项：

1.给values后的字符串类赋值时，只能使用单引号 ‘ ’
2.修改时，values的数量要对应，字段属性要对应

3.date类赋值时需要符合相应格式

insert into stu2(no,name,birthday)
values(104,'李四','26-4月 -24');-- 不推荐

insert into stu2(no,name,birthday)
values(105,'王五',sysdate);-- 推荐 sysdate-系统当前时间

--向stu2表中添加数据（给所有字段）
insert into stu2(no,name,score,birthday) values(106,'赵六',96.6,sysdate);
insert into stu2 values(106,'赵六',96.6,sysdate);
--若表名后没有小括号，则默认给所有字段赋值

添加多条数据：格式： INSERT INTO <table_name> <SELECT 语句>

-- 复制表数据（注意表的结构一致）
create table stu3(
no number(5),
name varchar(32),
score number(3,1),
birthday date
);
-- 将stu2表中的数据插入到stu3中
insert into stu3 select * from stu2;
-- 将stu2表中no号小于3的插入到stu3中
insert into stu3 select * from stu2 where no<3;

另：复制表也可以完成相关要求

update修改

格式： UPDATE table_name set column1=new valuecolumn2=new value,... WHERE <条件>

-- 修改stu2表，将no=103的学生分数改为93.3
update stu2 set score=93.3 where no=103;

-- 修改no=104的学生 分数=94.4 birthday=当前时间
update stu2 set score=94.4,birthday=sysdate where no=104;

-- 不加where的修改——直接修改所有数据
-- ##！！！高危操作！！！##
update stu2 set score=88.8;

-- 修改score=91.3的李四的no=5
update stu2 set no=5
where name='李四' and score=91.3;

delete删除

DELETE FROM <table_name> WHERE <条件>
```
-- 删除stu2表中no=104的学生信息
delete from stu2 where no=104;
```
- 与truncate的区别
  
  删除表中所有数据的两种方式的区别
```
-- 删除表中所有数据 
truncate table stu3; 
delete from stu2;
```
- delete：一条一条删，数据可以恢复（支持回滚）
- truncate：把整张表直接删除，然后再创建一张和以前结构一模一样的新表（不支持回滚）
总结

DDL： create drop alter desc
DML： insert delete update select

DQL（最重要）

操作数据（查询）（单表查询）
格式： SELECT [DISTINCT] <column1 [as new name] ,columns2,...> FROM <table1> [WHERE <条件>] [[GROUP BY ] [[HAVING <条件>] [[ORDER BY [ASC|DESC]]

分析：[]表示可选

简单查询

前提：解锁scott用户，使用scott用户下面的表，作为查询表 1.查询单个字段

-- 查询emp表所有员工的姓名
select ENAME from emp;

2.查询多个字段

-- 查询emp表中所有员工的编号，姓名，领导编号
select EMPNO,ENAME,MGR from emp;
-- 多个字段之间逗号隔开

3.查询所有字段

-- 查询emp表中所有员工的所有信息 
select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from emp; select * from emp; 
-- * 表示所有 但凡使用 * 效率都偏低（因为*底层是全表扫描，不走索引）

去重查询

关键字：distinct

-- 查询emp表中的部门标号，去重
select distinct deptno from emp;

select distinct mgr,deptno from emp;
-- distinct后面跟多个字段，把多个字段作为一个整体

计算查询

计算方式：四则运算（加减乘除）注意：null参与四则运算，结果为null 解决方案：使用nvl函数
```
-- 计算员工表中的所有员工的总收入（sal+comm）
select ename,sal,comm,sal+nvl(comm,0) from emp;
```
注意：做四则运算时，注意数据类型例：
```
select ename,ename+100 from emp;-- 报错，数据类型错误
```

起别名

关键字 as

1.给字段起别名

-- 查询emp表中的员工编号和薪资
select empno as 部门编号,sal as 薪资 from emp;-- 标准写法
select empno 部门编号,sal 薪资 from emp;-- 省略写法
select sal,comm from emp;-- 查询的是两个字段
select sal comm from emp;-- 查询的是一个字段，comm是sal的别名

2.给表起别名

-- 给emp表起别名
select t.* from emp t;

where查询

运算符

= > >= < <= and or is(null) is not(null) not in between...and...

实例

-- 查询emp表中sal大于1100的员工信息
select * from emp where sal > 1100;

-- 查询emp表中sal大于等于1100的员工信息
select * from emp where sal >= 1100;

-- 查询emp表中sal等于1600的员工信息
select * from emp where sal = 1600;--Oracle中只有=，没有==

-- 查询emp表中sal等于1100或者sal等于1600的员工信息
select * from emp where sal = 1100 or sal = 1600;-- 效率高
select * from emp where sal in(1100,1600);-- in的效率偏低  sql优化

-- 查询emp表中sal=1600 并且 部门编号为30的员工信息
select * from emp where sal = 1600 and deptno = 30;

-- 查询emp表中sal不等于1100的员工信息
select * from emp where sal != 1100;
select * from emp where sal <> 1100;
select * from emp where sal not in(1100);

-- 查询emp表中sal大于等于1000，并且为小于等于2000的员工信息
select * from emp where sal >= 1000 and sal <= 2000;
select * from emp where sal between 1000 and 2000;
-- between后面跟较小的值，and后面跟较大的值

-- 查询emp表中有comm的员工信息
select * from emp where comm is not null;
-- null只能和is或者is not连用，不能和=连用

-- 查询emp表中有comm的员工信息
select * from emp where comm is null;

模糊查询

两个概念
- 精确查询
  
  查询的条件是具体的、精确的，一般用 = 连接
- 例：查询员工的名字为张三的员工
- 模糊查询
  
  查询的条件是不具体的、模糊的，通常是用部分来查询整体
- 例：查询员工名字中带有“三”字的员工
关键字

like 像

占位符

_ ：下划线表示任意单个字符 % ：百分号表示任意多个字符

实例

-- 查询员工表中    名字为WARD    的员工信息
select * from emp where ename = 'WARD';-- 精确查询

-- 查询emp表中名字中以T开头的员工信息
select * from emp where ename like 'T%';
-- 查询emp表中以S结尾的员工信息
select * from emp where ename like '%S';
-- 查询emp表中名字中包含L的员工信息
select * from emp where ename like '%L%';

-- 查询emp表中名字中第二个字符为D的员工信息
select * from emp where ename like '_D%';
-- 查询emp表中名字由四个字符组成的员工信息
select * from emp where ename like '____';

排序查询

语法

语法： order by <排序字段1> <排序方式> , <排序字段2> <排序方式> , ......

排序方式

排序方式：（1）升序：从小到大，ASC 默认值（缺省值）（2）降序：从大到小，DESC

-- 查询emp表中的员工信息，按照sal从小到大排序
select * from emp order by sal asc;-- 标准写法
select * from emp order by sal;-- 简化写法，省略了asc

实例

-- 查询emp表中的员工信息，按照comm从小到大排序
select * from emp order by comm asc;
-- oracle数据库中，null值参与排序，按照最大值计算

-- 查询emp表中的员工信息，按照总收入（sal+comm）的从大到小排序
select t.*,sal+nvl(comm,0) total from emp t order by total desc;

-- 查询emp表中的员工信息，先按照sal从大到小排序，如果sal相同，再按照comm从大到小排序
select * from emp order by sal desc,comm desc;-- 双重排序

聚合函数

函数：就是方法，一般情况下都带有() 作用：对某一列进行纵向计算 max：求最大值 min：求最小值 count：计数 avg：求平均值 sum：求和

实例一

-- 求emp表中员工的最大工资，最小工资，平均工资，总工资，员工数量
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;
select sum(sal) from emp;
select count(sal) from emp;

实例二

-- 求emp表中员工最大奖金，最小奖金，平均奖金，总奖金，员工数量
select max(comm) from emp;
select count(nvl(comm,0)) from emp;
-- null值不参与聚合函数计算
select avg(nvl(comm,0)) from emp;
-- oracle数据库中处理null值使用nvl函数

分组查询

格式

group by 分组字段

实例

-- 查询emp表中所有的员工的sal总和
select sum(sal) from emp;
-- 查询emp表中部门编号为10的员工的sal总和
select sum(sal) from emp where deptno=10;

-- 查询emp表中每个部门的员工的sal总和
select sum(sal) from emp where deptno=10;
select sum(sal) from emp where deptno=20;
select sum(sal) from emp where deptno=30;
-- 或：
select deptno,sum(sal) from emp group by deptno;
-- 一旦分组之后，那么select之后只能跟分组字段和聚合函数

-- 查询emp表中每个部门的员工的sal总和，sal低于1000的不参与计算
select deptno,sum(sal) from emp where sal>=1000 group by deptno;
-- 分组前进行过滤

-- 查询emp表中每个部门的员工的sal总和，分组后sal总和小于9000，则不显示
select deptno,sum(sal) from emp group by deptno having sum(sal)>=9000;
-- 分组之后进行过滤

where与having的区别
where：分组之前进行过滤，where后面不能跟聚合函数
having：分组之后进行过滤，having后面可以跟聚合函数

伪列查询
- 伪列的概念
  
  在建表的时候，没有定义该列的，Oracle自动帮我们创建的列，伪列中的数据也是Oracle帮我们自动生成的。
- 伪列的特点
  
  （1）每个表都有（2）只能查询，不能修改
- Oracle中的两个伪列
- （1）rowid 该行的物理地址，是唯一的例：AAAMfPAAEAAAAAgAAA
- （2）rownum 查询结果中，给每一行标识的行号后期做分页查询
  
  dual 伪表：单行的表格，用于测试结果。

数据类型

字符型
（1） CHAR : 固定长度的字符类型，最多存储 2000 个字节
（2） VARCHAR2 :可变长度的字符类型，最多存储 4000 个字节
（3） LONG : 大文本类型。最大可以存储 2 个 G
数值型

NUMBER : 数值类型，默认为number(5) 例如： NUMBER(5) 最大可以存的数为 99999 NUMBER(5,2) 最大可以存的数为 999.99
日期型
（1） DATE：日期时间型，精确到秒
（2） TIMESTAMP：精确到秒的小数点后 9 位

函数

字符串函数

upper(c1)

【功能】将字符串全部转为大写
```
select upper('helLO') from dual;--dual 伪表
```
lower(c1)

【功能】：将字符串全部转为小写
```
select lower('helLO') from dual;--dual 伪表
```
initcap(c1)

【功能】返回字符串并将字符串的第一个字母变为大写，其它字母小写;
```
-- 将首字母变大写
select initcap('helLO') from dual;
select initcap(ename) from emp;
```

concat(c1,c2)

【功能】连接两个字符串

-- 字符串拼接
select concat('hello','oracle') from dual;
select 'hello'||'oracle'||'nanjing'||'仙林' from dual;

substr(c1,n1[,n2])

【功能】取子字符串

-- 截取字符串
select substr('hellooracle',3,4) from dual;
-- 与Java不同，3：从第3个开始，4：截取4个
select substr('hellooracle',3) from dual;-- 默认截取到结尾

instr(c1,c2[,I[,J]])

【功能】在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

-- 找出字符串中，指定字符的位置
select instr('hellooracle','ll') from dual;
-- 返回指定字符第一次出现的位置，若无，则返回0

length(c1)

LENGTH(c1) 【功能】返回字符串的长度；

lengthB(c1) 【功能】返回字符串的字节数；

-- 获取字符串的长度
select length('hello'),lengthb('hello') from dual;
--oracle中每一个英文字母占一个字节
select length('南京'),lengthb('南京') from dual;
--根据oracle数据库底层的字符编码决定中文字节数

lpad(c1,n[,c2])

【功能】在字符串c1的左边用字符串c2填充，直到长度为n时为止 RPAD(c1,n[,c2]) 【功能】在字符串c1的右边用字符串c2填充，直到长度为n时为止
```
-- 左右填充
select lpad('njxq',10,'#'),rpad('njxq',10,'#') from dual;
```

TRIM(c1 from c2)

【功能】删除左边和右边出现的字符串

-- 去除左边和右边的字符串
select trim('*' from '*****njxq')  from dual;
-- 只能去除左右，不能去除中间
select trim('     nj   xq   ')  from dual;
-- 默认值就是空格

REPLACE(c1,c2[,c3])

【功能】将字符表达式值中，部分相同字符串，替换成新的字符串

-- 字符串的替换功能
select replace ('hello','l','$') from dual;

实例

-- 获取所有员工姓名的第一个字母
select substr(ename,1,1) from emp;
-- 在所有员工的名字前面加上   南京
select concat('南京',ename) from emp;
-- 找出所有员工中，名字为四个字符的员工
select * from emp where length(ename)=4;
select * from emp where ename like '____';-- 模糊查询方法解答

数值函数

round四舍五入

-- 四舍五入
select round(100.456,2) from dual;-- 取小数点后两位
select round(100.456) from dual;-- 取整数

cell向上取整

-- 向上取整
select ceil(3.5678) from dual;-- 取大于其的最小整数

floor向下取整

-- 向下取整
select floor(3.5678) from dual;-- 取小于其的最大整数

trunc截取

-- 直接截取
  select trunc(3.5678,3) from dual;-- 直接取小数点后3位
  select trunc(3.5678,3) from dual;-- 取整数位

mod取余数

-- 取余数
select mod(10,3) from dual;

日期函数

sysdate

【功能】：返回当前日期。

-- 获取当前时间
select sysdate from dual;

扩展

-- 获取昨天和明天的日期
select sysdate-1,sysdate+1 from dual;
-- 时间可以直接计算
-- 计算员工入职的年数
select (sysdate-hiredate)/365 from emp;
select trunc((sysdate-hiredate)/365) from emp;-- 取整

months_between(d1,d2)

【功能】：返回日期d1到日期d2之间的月数。

-- 计算员工入职的月数
select months_between(sysdate,hiredate) from emp;
select trunc(months_between(sysdate,hiredate)) from emp;
-- 取整

add_months(d1,n1)

【功能】：返回在日期d1基础上再加n1个月后新的日期。
```
-- 在当月的基础上，加上6个月
select add_months(sysdate,6) from dual;
```
last_day(d1)

【功能】：返回日期d1所在月份最后一天的日期。
```
-- 获取当前月的最后一天日期
select last_day(sysdate) from dual;
```
next_day(d1[,c1])

【功能】：返回日期d1在下周，星期几(参数c1)的日期
```
-- 获取下一个星期三的日期
select next_day(sysdate,'星期三') from dual;
```

实例

-- 获取员工表中，哪些员工在入职月的月最后三天入职
select * from emp where hiredate between (last_day(hiredate)-2) and last_day(hiredate);
select * from emp where last_day(hiredate)-hiredate<3 ;

转换函数

to_char(x[[,c2],C3])

将日期或数值转换为字符串数据类型日期------->字符串数值--------->字符串

-- 将数值转换为字符串
select to_char(1024) from dual;
-- 将日期转换为字符串
select to_char(sysdate) from dual;
select to_char(sysdate,'yyyy-mm-dd  hh24:mi:ss day') from dual;-- 2024-04-28  09:04:27 星期日

转换为16进制

-- 字符串转换为16进制
select TO_CHAR(100,'XX') from dual;
-- 数字型（数字）可以不加单引号，字符型必须要加单引号

to_date(X[,c2[,c3]])

将字符串X转化为日期型 Oracle数据库中采取用mi代替分钟，mm表示月

-- 将字符串转换为日期
select to_date('2024-04-21','yyyy-mm-dd') from dual;
-- 'yyyy-mm-dd'是为了解析前面输入的字符串

to_number(X[[,c2],c3])

将字符串X转化为数字型

-- 将字符串转换为数字型
select to_number('456'),to_number ('12.345') from dual;

-- 字符串16进制表示出10进制含义
select to_number('f','XX') from dual;-- xx为能表示的位数

其他函数

NVL (expr1, expr2)

NVL (expr1, expr2) 【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。 NVL2 (expr1, expr2, expr3) 【功能】expr1不为NULL，返回expr2；expr1为NULL，返回expr3。
```
-- 求emp表中的员工的总收入
select ename,sal+nvl(comm,0) from emp;
select ename,sal+nvl2(comm,comm,0) from emp;
```
COALESCE(c1, c2, ...,cn)

【功能】返回列表中第一个非空的表达式，如果所有表达式都为空值则返回1个空值 select coalesce(null,null,null,'南京') from dual;
```
-- 求emp表中的员工的总收入
select ename,sal+coalesce(comm,0) from emp;
-- 若comm不为空，则返回它自己；若为空，则返回0
```

case...when...end

case [<表达式>] when <表达式条件值1> then <满足条件时返回值1> [when <表达式条件值2> then <满足条件时返回值2> …… [else <不满足上述条件时返回值>]] end

-- 查询emp表中数据，姓名，所在部门的名称
-- 10----开发部 20----销售部 30----财务部
select ename,
  case(deptno)
    when 10 then '开发部'
    when 20 then '销售部'
    when 30 then '财务部'
    else '未知部门'
  end as 部门
from emp;
-- case对deptno进行一个判断
-- 开发人员通常使用case语句

decode(条件,值1,翻译值1,...值n,翻译值n,缺省值)

【功能】根据条件返回相应值

-- 使用decode实现查询部门名称
select ename,
 decode(deptno,
    10,'开发部',
    20,'销售部',
    30,'财务部'
) 部门
from emp;

------------------ decode判断方法： ---------------------
-- if 条件=值1，then return 翻译值1
-- elseif 条件=值2，then return 翻译值2
-- elseif 条件=值3，then return 翻译值3
-- ......
-- else return 缺省值
-- end if

约束

概念

就是限制，用来限制数据的

非空约束（针对单表）

1.关键字 not null

2.作用限制表中的数据不能为null

创建表时添加非空约束

-- 创建学生表时，添加非空约束
create table stu4(
  id number not null,  -- 在列的数据类型后加上not null就是非空约束
  name varchar2(20)
);

删除表中的非空约束
```
alter table stu4 modify (id null);
```
创建完表后，再添加非空约束
```
alter table stu4 modify (id not null);
```

唯一约束（针对单表）

1.关键字 unique

2.作用

保证该字段各值各不相同

唯一约束是无法约束null值的。

创建表时添加唯一约束

-- 创建表时添加唯一约束
create table stu5(
  id number unique,  -- 在列的数据类型后加上unique就是唯一约束
  name varchar2(20)
);

删除表中的唯一约束

alter table stu5 drop constraint SYS_C005423;

创建完表之后，再添加唯一约束

alter table STU5
add constraint STU5_UK1 unique
(
  id
)
enable;

给一个字段同时添加非空和唯一约束

-- 创建表时，同时添加非空和唯一约束
create table stu6(
  id number not null unique,  -- 给id字段同时添加非空和唯一约束
  name varchar2(20)
);

选择约束（针对单表）

1.关键字 check

2.作用

限制数字的取值范围

选择约束是无法约束null值的

创建表时添加选择约束

-- 创建表时添加选择约束
create table stu7(
  id number,
  name varchar2(20),
  gender varchar2(5) check(gender in('男','女'))-- 给gender字段添加选择约束
);

删除表中的选择约束

ALTER TABLE STU7 DROP CONSTRAINT SYS_C005424;

创建表后，再添加选择约束

ALTER TABLE STU7
ADD CONSTRAINT STU7_CHK1 CHECK 
(gender in('男','女'))
ENABLE;

默认约束（针对单表）

1.关键字 default

2.作用当不给指令的字段赋值时时，会有默认值给指定字段赋值后，以给定值为准

创建表时添加默认约束

-- 创建表时添加默认约束
create table stu9(
  id number,
  name varchar2(20),
  gender varchar2(5),
  age number default 13  -- 给age添加了默认元素（和类型对应）
);

删除表中的默认约束

ALTER TABLE STU9 MODIFY (AGE DEFAULT NULL );

创建表后，再添加默认约束

ALTER TABLE STU9 MODIFY (AGE DEFAULT 13 );

主键约束（针对单表）

1.关键字 primary key

2.作用作为表中记录的唯一标识

3.特点非空且唯一

创建表时添加主键约束

方法一

-- 创建表时添加主键约束
create table stu10(
  id number primary key,-- 给id添加主键约束
  name varchar2(20),
);

方法二

-- 创建表时添加主键约束
create table stu10(
  id number,
  name varchar2(20),
   primary key(id)-- 给id添加主键约束
);

删除表中的主键约束

ALTER TABLE STU10 DROP CONSTRAINT SYS_C005431;

创建表后，再添加主键约束

ALTER TABLE STU10  
MODIFY (ID NOT NULL);

ALTER TABLE STU10
ADD CONSTRAINT STU10_PK PRIMARY KEY 
(
  ID 
)
ENABLE;

一张表能不能有多个主键？不能

create table stu10(
  id number,
  name varchar2(20),
   primary key(id,name)-- 联合主键
);

主键约束和非空且唯一约束有什么区别？
主键约束：一张表只能有一个
非空且唯一约束：一张表可以有多个

外键约束（针对多表）

1.关键字 foreign key

2.作用

让多表之间的数据产生关联，从而使得我们的数据更加准确和有效

3.单表存在的问题

（1）存在大量的冗余数据

（2）删除员工的时候，删除了部门

（例）解决方案：分成两张表

但是，新问题：

（1）添加员工时，可以添加不存在的部门

（2）删除部门时，该部门还留有员工

最终，使用外键解决。

创建表时添加外键

constraint <主表_外表_fk> foreign key(主表关联外表的键) reference <外表(外表外键)>

删除外键关系

alter table employee drop constraint employee_department_fk;

创建表之后，添加外键——推荐使用这种方法，好用

alter table employee add constraint employee_department_fk foreign key(dedp_id) reference department(id);

级联删除（扩展）

删除主表，附表相关的数据会被同时删除 on delete cascade

表设计

表关系

一对一关系

举例：人与身份证一个人只有一个身份证，一个身份证也只能对应一个人
一对一要求：外键唯一
结论：oracle中基本见不到，通常会把一对一的表设计成一张表
一对多关系

举例：部门和员工一个部门可以有多个员工，一个员工只能在一个部门
结论：在多的一方建立外键指向一的一方的主键
多对多关系

举例：学生和课程一个学生可以选择多门课程，一门课程也能被多个学生选择
结论：oracle数据库中无法直接表示多对多关系，必须借助中间表（没有共同字段/关联字段）
- 设计方案
  
  学生表和学生选课表时一对多关系：
  - 一：学生表、多：学生选课表设计方案：在多的一方建立外键指向一的一方的主键
    
    课程表和学生选课表时一对多关系：
  - 一：课程表、多：学生选课表设计方案：在多的一方建立外键指向一的一方的主键

三大范式

第一范式

每一列都是不可再分的原子数据项
第二范式

消除部分依赖，从而消除冗余数据【将部分依赖（可推导关系）提取成另外的表】
第三范式

消除传递依赖（将间接关系提取成另外的表）

备份和还原

原因

防止数据被误删

按照库导入和导出

导出：exp system/123456 file=a.dmp full=yes

exp 管理员/密码 file=输出文件夹名.dmp full=yes（把整个库导出）

导入：imp system/123456 file=a.dmp full=yes

按照用户导入和导出

导出：exp system/123456 owner=lisi file=a.dmp

导入：imp system/123456 fromuser=lisi file=a.dmp

按照表导入和导出

导出：exp lisi/1234 file=a.dmp tables=stu7,stu8 （导出表stu7，stu8）

导入：imp lisi/1234 file=a.dmp tables=stu7,stu8

查询

多表查询（重点）

原因

单表查询不够满足需求
原理

使用表之间的拼接使用笛卡尔积，将两个集合进行拼接

笛卡尔积：集合1：{a,b} 集合2：{1，2，3} 对以上两个集合进行笛卡尔积：{a1,a2,a3,b1,b2,b3}
为什么使用笛卡尔积进行拼接？
- 为了在一行数据中查询出所有我要的信息
使用笛卡尔积的弊端？
- 会产生大量的无效数据
- 怎么解决？
  - 使用条件过滤
- 结论：但凡使用多表查询，必须过滤无效数据
- 例：
- ```
select t1.* , t2.* from employee t1,department t2 where t1.dept_id=t2.id;
```

分类

内连接

隐式内连接（自然连接）

--------------------------隐式内连接--------------------------------
-- 查询emp表中所有的员工信息，对应的部门信息
select
t1.*,t2.*
from
emp t1,dept t2
where
t1.deptno=t2.deptno;

-- 查询emp表中所有的员工信息，对应的工资等级
select
t1.*,t2.*
from
emp t1,salgrade t2
where
t1.sal between t2.losal and t2.hisal;

-- 查询emp表中所有的员工信息，对应的部门信息，对应的工资等级
select
  t1.*,t2.*,t3.*
from
  emp t1,dept t2,salgrade t3
where
  t1.deptno=t2.deptno and t1.sal between t3.losal and t3.hisal;

显式内连接（公式连接）（推荐使用）

语法

select 字段列表 from 表名1 [inner] join 表名2 on 限制条件（去除无效的笛卡尔积数据）

实例

------------------------显式内连接--------------------------
-- 查询emp表中所有的员工信息，对应的部门信息
select
t1.*,t2.*
from emp t1 inner join dept t2 on t1.deptno=t2.deptno;

-- 查询emp表中所有的员工信息，对应的工资等级
select
t1.*,t2.*
from emp t1 join salgrade t2 on t1.sal between t2.losal and t2.hisal;

-- 查询emp表中所有的员工信息，对应的部门信息，对应的工资等级
select
t1.*,t2.*,t3.*
from emp t1 join dept t2 on t1.deptno=t2.deptno
  join salgrade t3 on t1.sal between t3.losal and t3.hisal;

弊端

无法查询出两张表中不是公共数据的部分

外连接

左表、右表：相对于（参照物）的左/右表
左外连接：左表的所有 + 公共部分
右外连接：右表的所有 + 公共部分

满外连接（全连接）：左右表的所有 + 公共部分

左外连接

语法： select 字段列表 from 表名1 left [outer] join 表名2 on 去除无效的笛卡尔积数据

参照物：left [outer] join 左表：表名1 右表：表名2
- 实例
```
-- 使用左外连接来修改需求
select
t1.*,t2.*
from dept t1 left outer join emp t2 on t1.deptno=t2.deptno;
```

右外连接

语法： select 字段列表 from 表名1 right [outer] join 表名2 on 去除无效的笛卡尔积数据

参照物：right [outer] join 左表：表名1 右表：表名2

实例

-- 使用右外连接来查询emp表中所有的部门信息，对应的员工信息
select
t1.*,t2.*
from emp t1 right join dept t2 on t1.deptno=t2.deptno;

左外连接与右外连接的相互转换

-- 查询所有的员工信息和对应的领导信息
-- 自身连接
select
t1.*,t2.*
from emp t1 join emp t2 on t1.mgr=t2.empno;
-- 内连接，缺少部分数据

-- 左外连接
select
t1.*,t2.*
from emp t1 left join emp t2 on t1.mgr=t2.empno;
-- 右外连接
select
t1.*,t2.*
from emp t1 right join emp t2 on t2.mgr=t1.empno;

满外连接/全连接

语法： select 字段列表 from 表名1 full [outer] join 表名2 on 去除无效的笛卡尔积数据

实例

-- 全连接
-- 查询emp表所有的员工信息，所有的部门信息
select
t1.*,t2.*
from emp t1 full join dept t2 on t1.deptno=t2.deptno;

子查询

定义

在一个查询中嵌套另一个查询

分类

where子句子查询

单行单列

使用语句：> < <= <> = >=

-- where子句子查询
select * from emp where sal=(select max(sal) from emp);
-- 子查询先执行子查询语句，在执行外层的查询

-- 查询emp表中sal大于平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp);

多行多列

使用语句：in exists any all

-- where子句子查询 多行单列
-- 查询ACCOUNTING部门和RESEARCH部门中所有的员工信息
select deptno from dept where dname in('ACCOUNTING','RESEARCH');-- 根据部门名称查询部门编号
select * from emp where deptno in(select deptno from dept where dname in('ACCOUNTING','RESEARCH'));

-- 使用exists转换in
select * from emp where 
exists(select deptno from dept where dname in('ACCOUNTING','RESEARCH')and emp.deptno=dept.deptno);

-- 查询emp表中低于其中任意一个职位为CLERK的员工工资的信息
select sal from emp where job='CLERK';-- 查询出CLERK职位的所有员工的工资
select * from emp where sal<any(select sal from emp where job='CLERK') and job<>'CLERK';

-- 查询工资比所有的职位为SALESMAN员工的工资都高的员工信息
select sal from emp where job='SALESMAN';
select * from emp where sal>all(select sal from emp where job='SALESMAN');

in与exists的异同

同：都有select子语句异：in先执行内部子语句；exists先执行外部语句，再代入子语句进行判断。

from子句子查询

-------------------------from子句子查询----------------------------
-- 查询工资低于3000的员工信息，以及对应的部门信息
-- 不适用from语句，多表联查
select
t1.*,t2.*
from emp t1 join dept t2 on t1.deptno=t2.deptno
where t1.sal<3000;

-- 使用from子句
select t1.*,t2.* 
from (select * from emp where sal <3000) t1 
join dept t2 
on t1.deptno=t2.deptno;-- 子查询 + 多表联查

select子句子查询

-------------------------select子查询--------------------------
-- 查询emp表中的员工编号，员工姓名，部门名称
-- 多表联查
select 
t1.empno,t1.ename,t2.dname
from emp t1 join dept t2 on t1.deptno=t2.deptno;

-- 使用select子句
select empno,ename,(select dname from dept where deptno=emp.deptno)from emp;

分页查询

什么是分页分成多页

为什么要分页每次只返回一部分数据

如何实现分页

回顾伪列

rowid rownum

-- 回顾伪列   rowid   rownum
select rowid,rownum,t.* from emp t;

简单查询

-- 简单查询
-- 查询员工信息，取前五条作为第一页
select rownum,t.* from emp t where rownum<=5;-- 第一页

-- select rownum,t.* from emp t where rownum=5;
-- rownum只能是小于等于，不能是=或>（具体看rownum编码原理）

select rownum rn,t.* from emp t;-- 子语句，为了拿到所有的rownum

select * from (select rownum rn,t.* from emp t) where rn>5 and rn<=10;-- 第二页
-- 若rownum不取别名，则where后rownum会根据查询表自动生成，与from子句中rownum不一致

select * from (select rownum rn,t.* from emp t) where rn>10 and rn<=15;-- 第三页

排序查询

-- 查询员工信息，按照sal从大到小的顺序排序，取前五条作为第一页
select rownum,t.* from emp t where rownum<=5 order by sal desc;
-- order by最后执行，导致结果出错

select rownum,t.* 
from 
(
    select * 
    from emp 
    order by sal desc
) t 
where rownum<=5;-- 第一页

select * from 
(
    select rownum rn,t.* 
      from (
          select * 
          from emp 
          order by sal desc
           ) t
)
where rn>5 and rn<=10;-- 第二页

集合运算

定义：把两个或者两个以上的表（集合）的结果，组合成一个新的结果。

并集

关键字

全部 union all：取两个集合的所有记录，包含重复值 union：取两个集合的所有记录，不包含重复值

实例

-- 并集
select * from emp where sal >=1000
union all
select * from emp where sal <=2450;-- 共有21条，有重复数据

select * from emp where sal >=1000
union-- 没有重复数据
select * from emp where sal <=2450;-- 有7条重复数据

交集

关键字

公共部分 intersect

实例

-- 交集运算
select * from emp where sal >=1100-- 12
intersect
select * from emp where sal <=2450;-- 9
-- 7

差集

关键字

左边取右边差集：属于左边但不属于右边的部分 minus

实例

-- 差集运算
select * from emp where sal >=1100-- 12
minus
select * from emp where sal <=2450;-- 9
-- 5

使用差集计算分页

-- 使用差集计算分页
select rownum,t.* from emp t where rownum<=5;-- 第一页

-- 第二页
select rownum,t.* from emp t where rownum<=10
minus
select rownum,t.* from emp t where rownum<=5;

视图

概念

就是一个查询语句的执行结果，被保存下来的对象

称视图为：虚表

为什么使用视图？安全

视图的相关操作

创建视图

create [or replace] view <view_name> as <select语句>;

-- 创建视图
create view emp_view as select * from emp where id>3;

使用视图

-- 查询emp表中id大于3的员工信息
select * from emp where id>3;
select * from emp_view;

修改视图

-- 修改视图
create or replace view emp_view as select * from emp where id>10;

删除视图
```
-- 删除视图
drop view emp_view;
```

根据视图来创建视图

-- 根据一个视图创建另一个视图
create view test_view as select * from emp_view where id>9;

改变视图会改变原表

-- 改变视图，原表也会跟着改变
insert into saltab_view values(38,1004,2000,201612,3);
update saltab_view set salary=2500 where eid=38;
delete from saltab_view where eid=38;

应用

银行数据库程序员修改表数据时不接触原表，而是修改视图

序列

是Oracle数据库用来自动产生唯一数字的数据对象

创建默认序列

语法

CREATE SEQUENCE <sequencen_name> INCREMENT BY n START WITH n MAXVALUE n [CYCLE|NOCYCLE] [CACHE n|NOCACHE];
解释
INCREMENT BY n --表示序列每次增长的幅度;默认值为1.
START WITH n --表示序列开始时的序列号。
默认值为1. MAXVALUE n --表示序列可以生成的最大值(升序).
MINVALUE n --表示序列可以生成的最小值(降序).
CYCLE --表示序列到达最大值后，在重新开始生成序列.默认值为 NOCYCLE。
CACHE --允许更快的生成序列.

实例

-- 创建一个  默认  的序列
create sequence squ1;

序列中的两个伪列

currval

返回序列的当前值. 注意在刚建立序列后,序列的CURRVAL值为NULL，所以不能直接使用。
nextval

返回序列下一个值

使用方法

-- 使用序列的两个伪列
select squ1.currval from dual;
select squ1.nextval from dual;

创建复杂序列

-- 创建复杂序列
create sequence squ2
increment by 2  -- 每次增加2
start with 10086;  -- 从10086开始增加

-- 使用序列向stu3表中添加数据
insert into stu3(no,name) values(squ2.nextval,'王小二');
insert into stu3(no,name) values(squ2.nextval,'王小三');
insert into stu3(no,name) values(squ2.nextval,'王小四');
insert into stu3(no,name) values(squ2.nextval,'王小五');

修改序列

-- 修改序列
alter sequence squ2
--start with 1000; -- start with参数不能修改
increment by -1;

删除序列

-- 删除序列
drop sequence squ2;

事务（重点）

事务其实就是生活中的业务，比如：银行转账（张三给李四转500元）

在Oracle数据库中指的是多条增删改语句组成的整体

提交方式

自动提交

-- 查看Oracle数据库库的提交方式
show autocommit;  -- autocommit OFF表示手动提交
-- 修改Oracle数据库的提交方式为自动提交
set autocommit on;

手动提交

Oracle数据库默认的提交方式

----------------事务的提交方式----------------------
-- 使用insert向stu4中添加数据
insert into stu4 values (1,'王小二')
insert into stu4 values (2,'王小三')
insert into stu4 values (3,'王小四')
commit;

作用

事务内部增删改语句要么同时成功，要么同时失败

事务的三个操作

（1）SAVEPOINT < savepoint > 保存位置标记事务的开始位置，为了回滚操作做铺垫

（2）COMMIT 提交把事务操作中产生的临时值，真正的更新到数据库中为了保证事务的同时成功/失败，必须使用手动提交

（3）ROLLBACK [TO savepoint]　回滚把事务操作中产生的临时值，取消

四大特征

原子性

会把事务当成一个不可再分的整体，要么同时成功，要么同时失败
持久性

持久化（序列化）
- 数据的状态：游离状态（在内存中游离）持久态数据（保存在硬盘中）
- 执行commit操作之后，会将数据保存到硬盘中
一致性

事务执行前后，数据的总和不变
隔离性

多个事务之间，是相互隔离的

事务的隔离级别

可能出现的问题
- 脏读
  
  一个事务读取到了另一个事务还未提交的数据
- 不可重复读（虚读）
  
  一个事物读取到了另一个事务已经提交的update语句 //数量一样内容会变
- 幻读
  
  一个事物读取到了另一个事务已经提交的insert语句 //数量会变
隔离级别
- Read Uncommited
  
  读未提交
  
  可能出现脏读、不可重复读（虚读）、幻读
- Read commited
  
  读已提交
  
  可能出现不可重复读（虚读）、幻读，是Oracle数据库的默认隔离级别
- Repeatable Read
  
  可重复读
  
  可能常出现幻读，是MySQL数据库的默认隔离级别
- Serializable
  
  串行化
  
  不会出现问题
- 安全性从上向下递增，效率从上向下递减

索引

定义：是一种提升查询效率的数据库对象。缺点：增删改的效率会变低

需要使用索引的时候

数据量比较大的时候，查询效率就会偏低，需要使用索引来提升查询效率

测试索引的查询效率

创建一张表

-- 创建一张表
create table person(
  id number,
  name varchar2(32),
  age number(3)
);

向表中添加大量数据——利用循环直接导入500w条数据

-- 向person表中添加500w条数据
begin
  for x in 1..5000000
  loop
    insert into person values(x,'NJ'||x,23);
  end loop;
  commit;
end;

测试使用索引和不适用索引的区别

-------- 测试使用索引和不使用索引的区别 --------
-- 根据name进行条件查询
-- 不使用索引 0.248s    使用索引之后 0.004s
-- 查询一次后产生缓存，以后索引查询速度增快
select * from person where name='NJ431219';

-- 给person表的name字段加一个索引
create index person_name on person(name);

判断查询语句是否走了索引

使用慢查询： explain plan for 一定要加在select前，不能加在增删改语句前。

explain plan for select * from person where name='NJ431219';-- 只会更新计划

-- 查询计划
select * from table(dbms_xplan.display);

走了索引

在查询计划中，会出现index range scan的显示
未走索引

在查询计划中，不显示index range scan 而是显示table access full 说明进行的是全表扫描

判断单个字段索引的执行条件

1.必须有where

2.where后面必须使用索引字段进行条件判断

实例

explain plan for select * from person where name='NJ431219';-- 只会更新计划
explain plan for select * from person where id = 134;-- 不走索引

关于模糊查询是否走索引

占位符在末尾，走索引

占位符在开头，不走索引（全表扫描）

联合索引的执行条件

1.必须有where

2.where后面必须使用索引字段进行条件判断

3.最左匹配原则（最左前缀原则）最左边的字段必须要提供，不提供左边字段查询时不走索引

实例

-- 给person表添加一个联合索引
create index person_name_id on person(name,id);

索引的底层结构

数据结构图示化网站 Data Structure Visualization

数据库中索引的底层结构

B+树
B树和B+树有什么区别

（1）B+树的叶子节点之间是有指针相互指向的

（2）B+树中在叶子节点中冗余了非叶子节点的数据

索引的底层工作原理

索引的底层逻辑

1.对索引字段进行排序

2.提取“目录”——倒排表

在Oracle中的分类

普通索引

一个字段的索引

-- 给person表的name字段加一个索引
create index person_name on person(name);

联合索引

-- 给person表添加一个联合索引
create index person_name_id on person(name,id);

唯一索引

添加唯一索引

-- 给stu13表的id字段加唯一索引
create unique index stu13_id on stu13(id);

可以提供唯一约束条件

位图索引

底层是个图片，适用低基数列

PL/SQL

process language

SQL中的过程语言

语法结构

DECLARE --声明部分声明语句 BEGIN --执行部分执行语句 EXCEPTION --异常处理部分执行语句 END;

基本语法

对变量的声明与赋值

声明： <变量名> 类型;

赋值： <变量名> :=初始值;

特殊类型字段%type

实例

-------------------变量的声明和赋值---------------------
-- 定义变量
declare -- 声明，后面用来声明变量
  v_id number;-- 定义变量不是字段，每次定义后需要使用分号
  v_name varchar2(32);
  v_score number(3,1);
  v_date date;-- 定义格式与定义表相似，但未创建表
begin
  v_id := 10086;
  v_name := '张三';
  v_score := 96.6;
  v_date := to_date('2000-09-10','yyyy-mm-dd');
  dbms_output.put_line(v_id);-- 输出v_id的值
  dbms_output.put_line(v_name);-- 输出v_name的值
  dbms_output.put_line(v_score);-- 输出v_score的值
  dbms_output.put_line(v_date);-- 输出v_date的值
end;

练习

-- 练习：定义两个number类型的值，输出两个数的和
declare
  m number;
  n number;
begin
  m := 10086;
  n := to_number('100', 'xxx');
  dbms_output.put_line(m+n);
end;

重点

1.声明变量中后面使用分号

2.赋值变量时使用 ‘ := ‘

用户输入（&）

-- 用户输入两个数字，求两个数字的和
declare
  num1 number;
  num2 number;
begin
  num1 := &请输入第一个数字; -- 获取用户输入的值（数字类型）
  num2 := &请输入第二个数字;
  dbms_output.put_line((num1+num2));
end;

select into赋值

实例一

-- 输入员工的编号，输出对应的员工的姓名
declare
  v_id number;
  v_name varchar2(32);
begin
  v_id := &请输入员工编号;
  select ename into v_name from emp where empno = v_id;-- select into 赋值
  dbms_output.put_line(v_name);
end;

实例二

-- 输入员工的编号，输出对应的员工的姓名，对应的部门名称
declare
  v_id number;-- 接受用户输入的值
  v_name varchar2(32);
  v_dept varchar2(32);
begin
  v_id := &请输入员工编号;
  select ename,deptno into v_name,v_dept from emp where empno = v_id;-- into前后字段数量相同
dbms_output.put_line(v_name||','||v_dept);-- 使用 ‘ || ’ 拼接
end;

属性类型

%type

-- 属性的类型引用
declare
  v_id number;-- 接受用户输入的值
  v_name emp.ename%type;-- 引用表中  某一字段  的类型
  v_dept emp.deptno%type;
begin
  v_id := &请输入员工编号;
  select ename,deptno into v_name,v_dept from emp where empno = v_id;-- select into 赋值
dbms_output.put_line(v_name||','||v_dept);
end;

%rowtype

-- 查询emp表中empno=7369的员工信息
declare
  v_emp emp%rowtype;-- 将一整行的数据直接赋值给v_emp
begin
  select * into v_emp from emp where empno =7369;
  dbms_output.put_line(v_emp.empno || ',' ||v_emp.ename); -- 查询编号和姓名
end;

异常处理

-------------------------异常--------------------------
declare
  v_id number;
  v_name varchar2(32);
begin
  v_id := &请输入员工编号;
  select ename into v_name from emp where empno = v_id;-- select into 赋值
  dbms_output.put_line(v_name);
exception
  when TOO_MANY_ROWS then
  dbms_output.put_line('查询结果异常，返回了多条信息');
  when NO_DATA_FOUND then
  dbms_output.put_line('查询结果为空');
end;

条件判断

IF <条件1> THEN 语句

end if;

实例

-------------------- 条件判断 --------------------
-- if then 结构
-- 输入一个学生的年龄，如果大于18，则输出已成年
declare
  v_age number;
begin
  v_age :=&请输入一个年龄;
  if v_age >= 18 then
  dbms_output.put_line('已成年');
  end if;
end;

if else

IF <条件1> THEN 语句

ELSE 语句

end if;

实例

-- if......else结构
-- 输入一个用户名和密码，判断是否登录成功
-- lisi  1234
declare
  v_uname varchar2(32);
  v_password varchar2(32);
begin
  v_uname :='&请输入用户名'; -- 只能接受数字，不能接受字符串，需要增加''
  v_password :='&请输入密码';
  if v_uname ='lisi' and v_password='1234' then
    dbms_output.put_line('登录成功');
  else
    dbms_output.put_line('登录失败');
  end if;
end;

if...else if ...else if...else

IF <条件1> THEN 语句 [ELSIF <条件2> THEN 语句 . . . ELSIF <条件n> THEN 语句] [ELSE 语句] END IF;

注意是elsif，与以往不同

实例

-- if多选结构
-- 输入一个学生成绩，如果大于等于90，输出优秀，大于等于70，输出良好，大于等于60，输出及格，否则输出不及格
declare
  v_score number;
begin
  v_score := &请输入一个成绩;
  if v_score >=90 then
  dbms_output.put_line('优秀');
  elsif v_score >=70 then
  dbms_output.put_line('良好');
  elsif v_score >=60 then
  dbms_output.put_line('及格');
  else
  dbms_output.put_line('不及格');
  end if;
end;

循环

loop（do...while）

-- 语法结构 LOOP 语句; EXIT WHEN <条件> END LOOP;

实例

------------------------循环结构---------------------------
-- loop 循环(do...while)
-- 输出1---100
declare
  num number;
begin
  num :=1;
  loop
    dbms_output.put_line (num);
    num := num + 1;
    exit when num > 100;
  end loop;
end;

while

-- 语法结构 WHILE <条件> LOOP 语句; END LOOP;

实例

-- 使用while loop实现输出1---100
declare
  num number;
begin
  num :=1;
  while num<=100
  loop
    dbms_output.put_line (num);
    num := num + 1;
  end loop;
end;

for

FOR <循环变量> IN 下限..上限 LOOP 语句; END LOOP;

实例

-- 使用for实现输出1---100
declare
  num number;
begin
  num :=1;
  for num in 1..100
  loop
    dbms_output.put_line (num);
  end loop;
end;

游标

用来临时存储从数据库中查询出来的数据块

存储函数（函数）

函数：返回结果，做查询操作 Oracle自带的函数（预定义）：字符串函数、日期函数、转换函数.etc 自己定义的函数（自定义）：存储函数

实例

---------------------存储函数-----------------------
-- 定义一个根据员工id查询员工姓名的存储函数
create or replace function fu_stu2_1(v_no number)
return varchar2   -- return 后的数据类型不要写长度
is -- 就相当于declare，可以定义变量
  v_name varchar2(32);
begin
  select name into v_name from stu2 where no = v_no;
  return v_name;-- 返回值
end;

-- 使用存储函数
select sysdate from dual;
select fu_stu2_1(2) from dual;

练习

-- 练习：传入员工id，返回对应的部门名称
-- employee表和department表
create or replace function fu_emp_dept(v_id number)
return varchar2
is
  v_deptname varchar2(32);
begin
  select t2.dep_name into v_deptname
  from employee t1 join department t2 on t1.dep_id=t2.id
  where t1.id=v_id;
  return v_deptname;
end;

select fu_emp_dept(2) from dual;

存储过程

存储函数：包含了一个查询语句的

存储过程：包含了一个事务

触发器（很少使用）

概念：

生活中的触发器：灯的开关——按下开关——灯亮

Oracle中的触发器：执行增删改语句的时候——触发另一个逻辑

注意点：触发器内不能有commit，会产生问题

分类

前置触发器

实例

-- 创建一个前置触发器
create or replace trigger tri_emp_depname
before
update of dep_name on department
for each row
declare
begin
  :new.dep_location :='未知';
end;
-------调用update语句触发的触发器
update emp set depname ='财务部' where id =6;

后置触发器

实例

-- 创建一个后置触发器
create or replace trigger tri_emp_depname1
after
update of dep_name on emp
for each row
declare
begin
  insert into department values(seq1.nextval,:old.dep_name,:old.dep_location);
end;
-------调用update语句触发的触发器
update emp set dep_name ='财务部' where id =3;