Oracle数据库详解
文章目录
一、数据类型
oracle中数据类型分三类：数字型、字符型、日期型。
1.数字型
1. number
长度不限，只要是数字就性
2. number(x)
为整数，并且长度不超过x位
3. number(x,y)
存在小数部分，总长度不超过x位，小数部分为y位
2.字符型
1. varchar2(x)
长度不超过x位
2. char(x)
固定长度是x位，不够则补空格
3.日期型
date
二、运算部分
1. 关系运算
= --等号
!= <> --不等
< <= --小于[等于]
> >= --大于[等于]
between...and...
in()
--in (值1[,值2,.....]) 等同于 过滤的对象 = 值1 or 过滤的对象 = 值2 or ....
distinct --去重
2. 集合运算
intersect --交集
union --并集，去重
union all --并集，不去重。性能最好
minus --补集
3. 算术运算
+ - * /
4. 数据类型间相互转换
--转换日期型
to_date(x,y) --y的长度要和x保持一致
eg: to_date(sysdate,'yyyy-mm-dd hh24:mi:ss')
--转换字符型
to_char() --可以单独去'yyyy''mm''dd'...
--转换数字型
to_number()
5. 特殊字符查询运算
is null
is not null
like
--涉及到两个占位符：
--_：表示具体的一个位
--%：表示不确定的位数
三、DQL
oracle中基本的查询语句和sql通用语句基本一致，简单介绍下八！
1. 通用模板
select 要返回的信息
from 表
join...on...
where 过滤条件
group by 分组字段
having 分组后的过滤条件
order by 排序字段
2. 运行顺序
from
where
group by
having
select
order by
注意：在oracle中没有mysql中的limit,当我们需要找某列的最大值或者最小值我们需要借助函数实现。
3. 子查询
子查询的结果可以作为值、条件、表等使用
--子查询优化sql
/*
子查询使用的场景/条件：
1.当条件不明确时，使用子查询查出具体的值，再进行过滤
2.当我们需要条件不满足时，通过子查询构造出我们需要的结果集再进行查询
子查询的返回值：
1 子查询返回单行结果。 一个值 用 等号 进行过滤。
2 子查询返回多行结果集。 多个值 用 IN 进行过滤。
3 子查询返回多行多列结果集 。用 IN 进行过滤。
*/
/*
优先将查询结果的数据返回过滤到最小，然后再对结果集进行关联查询。
相当于对两个小表进行关联查询，效果更好。
*/
4. 连接查询
a.连接查询的sql写法
[inner] join...on... --内连接/自然连接
--外连接
left [outer] join...on... --左外连接
right [outer] join...on... --右外连接
full [outer] join...on... --全外连接
左/右外连接的结果集是显示查询的主表的所有数据，以及从表跟主表相交的部分，从表不足的部分(行)补NULL。
全连接的结果集是两个表相交的部分正常显示，不同的部分互补NULL。
b.连接查询的oracle写法[oracle独有]
--内连接
select...
from 表1,表2,...
where 关联条件1/过滤条件 [and/or 关联条件2 and/or ....]
--左外连接
select...
from 表1,表2,...
where 表1的某个字段 运算符 表2的某个字段(+) --表1是主表，表2是从表
--右外连接
select...
from 表1,表2,...
where 表1的某个字段(+) 运算符 表2的某个字段 --表2是主表，表1是从表
5. 伪列(oracle独有)
rownum --对某列进行排序
rowid --返回当前列的ID值
1. 删除重复行的rowid写法
delete from grade t2
where rowid != (select max(rowid)
from grade t
where t.name = '张三'
and t.subject = 'chinese')
and t2.name = '张三'
and t2.subject = 'chinese';
commit;
四、DML
数据操纵语言DML，该部分语言在执行完语句后需要添加commit语句进行提交的操作。
--常见的语法结构
--语法结构：数据插入
INSERT INTO 表名(列名1，列名2……) VALUES (值1，值2……);
--列名可以省略。当省略列名时，默认是表中的所有列名，列名顺序为表定义中列的先后顺序。
--值的数量和顺序要与列名的数量和顺序一致。值的类型与列名的类型一致。
--语法结构：INSERT向表中插入一个结果集
INSERT INTO 表名1(列名1，列名2……) 查询结果集;
--在这种语法下，要求结果集中每一列的数据类型必须与表中的每一列的数据类型一致，
--结果集中的列的数量与表中的列的数量一致。
--语法结构：数据更新操作
UPDATE 表名 SET 列名1=值，列名2=值…… WHERE 条件; （WHERE 条件没有的话则是全表更新）
--语法结构：数据删除操作
DELETE FROM 表名 WHERE 条件;
--语法结构：TRUNCATE（也是删除数据，把表中的数据全部删除---- 清空数据） （数据定义语言 DDL）
TRUNCATE TABLE 表名;
五、DDL
1. 建表语法
create table table_name();
delete from table_name;
commit;
insert into table_name(列名...)select...;
--insert into table_name(列名...)values(值...);
commit;
select * from table_name;
2. 表的约束
primary key --主键约束
foreign key --外键约束
check --检查约束
unique --唯一约束
not null --非空约束
alter table table_name add constraint 约束名 约束内容; --基本命令
ALTER TABLE 表名 ADD CONSTRAINT 约束名
PRIMARY KEY(列名1[,列名2...]); --添加主键约束
ALTER TABLE 主表名 ADD CONSTRAINT 约束名
FOREIGN KEY(列名1[,列名2...]) REFERENCES 从表名(列名1[,列名2...]);
--添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 CHECK(条件);--添加check约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE(列名);--添加唯一约束
ALTER TABLE 表名 MODIFY 列名 NOT NULL; --添加非空约束
ALTER TABLE 表名 DROP CONSTRAINT 约束名; --删除约束
3. 对表的操作
create table table_name(row_name_01 datatype[,row_name_02 datatype...]); --创建表
create table table_name_01
as select * from table_name_02 where 1=2; --复制表结构
create table table_name_01
as select * from table_name_02 where...; --复制表结构和表里的数据
drop table table_name; --删除表
alter table table_name add row_name datatype; --添加列
alter table table_name modify row_name datatype; --修改列类型
alter table table_name rename column old_row_name to new_row_name;
--修改列名
alter table table_name drop column row_name; --删除列
alter table table_name rename to new_table_name; --修改表名
[注释部分]
comment on table table_name is notes; --给表添加注释
comment on column table_name.row_name is 'notes_information';
--对列添加注释信息
[索引部分]--提高效率
create [unique] index index_name on table_name(row_name_01[,row_name_02]); --创建索引
drop index index_name; --删除索引
[序列部分]
create sequence squence_name
[start with num] --从哪里开始，一般是1
[increment by increment] --每次增加多少
[maxvalue num|nomaxvalue] --最大值
[minvalue num|nominvalue] --最小值
[cycle|nocycle] --是否循环
[cache num|no cache] --在内存中保留多个序号
---完整的创建序列
create sequence sequence_name; --简易的创建序列
drop sequence sequence_name; --删除序列。序列不能更改，只能删除重建
[视图部分]
create or replace[{force|noforce}]view view_name; --创建视图
--force:即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成---功后，视图才能正常使用。
--noforce:如果基表不存在，无法创建视图，该项是默认选项。
as
select 查询
[with read only constraint] --表示视图只读，不加则表示视图可以进行增删改
--创建视图
drop view view_name; --删除视图
4. 对用户的操作
--语法结构：创建用户
CREATE USER 用户名 IDENTIFIED BY password [ACCOUNT LOCK|UNLOCK];
--语法结构：给用户授权
GRANT CONNECT，RESOURCE TO 用户名;
--- 直接将connect角色，--resource角色授予用户。
--- CONNECT角色:连接数据库。RESOURCE:正常使用数据库
GRANT CREATE TABLE TO 用户名; --- 给用户创建表的权限
GRANT CREATE VIEW TO 用户名; --- 给用户创建视图的权限
--语法结构：收回用户权限
REVOKE 角色|权限 FROM 用户（角色）
--语法结构：修改用户的密码
ALTER USER 用户名 IDENTIFIED BY 新密码;
--语法结构：给用户加锁/解锁
ALTER USER 用户名 ACCOUNT LOCK/UNLOCK;
5. 建表的其他操作
1、创建同义词
CREATE [OR REPLACE] [PUBLIC] SYNONYM 同义词名 FOR 用户名.对象名;
--对象包括：表、视图、序列、过程、函数、程序包等
--默认情况下创建私有同义词，只能被当前创建用户使用
CREATE OR REPLACE PUBLIC SYNONYM EMP FOR SCOTT.EMP; ---创建公有同义词，这样的话其他的用户直接访问EMP就是访问了scott用户下的emp表了
2、删除同义词
DROP SYNONYM 同义词名;
3、给用户授权 创建/删除 同义词
GRANT
CREATE PUBLIC SYNONYM ,
DROP PUBLIC SYNONYM
TO SCOTT ;
******************************************************
1、创建DBlink
create [public] database link dblink名
connect to 数据库用户名 identified by "数据库密码"
using 'TNS连接串信息';
--TNS连接串信息：可以使用整串信息，也可以使用代表这串信息的别名
create public database link TESTLINK1 connect to scott identified by "123456" USING 'ORCL2';
2、查看用户是否具备创建dblink 权限
select * from user_sys_privs where privilege like upper('%DATABASE LINK%') AND USERNAME='用户名';
3、授权用户 创建dblink 的权限
GRANT
CREATE PUBLIC DATABASE LINK ,
DROP PUBLIC DATABASE LINK
TO 用户名;
4、给dblink创建同义词
create synonym 同义词名 FOR dblink连接的数据库里的对象@dblink名;
比如：create synonym TESTSYNONYM FOR company@TESTLINK1;
--company：上面的ORCL2数据库里的一张表
--TESTLINK1：创建的连接到ORCL2数据库的dblink名
5、删除dblink
DROP [PUBLIC] DATABASE LINK DBLINK名;
六、函数
1. 数字函数
nvl(目标字段,默认值)
--判断目标字段里面是否为空，如果不为空，则取目标字段的值，如果为空，则赋一个默认值
abs(x) --返回绝对值
mod(x,y) --返回x除以y的余数
power(x,y) --返回x的y次方
ceil(x) --向上取整
floor(x) --向下取整
round(x,y) --
trunc(x,y) --y的界限为小数点...断x在第y后的数字，把y位之后的数字全干掉(全换成0)
2. 字符函数
add_months(d,n) --返回日期d加上n个月后的日期
last_day(d) --返回d所处月份的最后一天的日期
months_between(d1,d2) --返回两个日期之间相隔几个月
round(d[,fmt])
--对日期d按照不同维度(fmt:yyyy/mm/dd/day[星期]/q[季度]/w[当月第几周]/iw[今年第几周1]/ww[今年第几周2])来进行四舍五入
trunc(d[,fmt]) --对日期d按照不同维度(fmt)来截断,维度参照fmt
3. 日期函数
add_months(d,n) --返回日期d加上n个月后的日期
last_day(d) --返回d所处月份的最后一天的日期
months_between(d1,d2) --返回两个日期之间相隔几个月
round(d[,fmt])
--对日期d按照不同维度(fmt:yyyy/mm/dd/day[星期]/q[季度]/w[当月第几周]/iw[今年第几周1]/ww[今年第几周2])来进行四舍五入
trunc(d[,fmt]) --对日期d按照不同维度(fmt)来截断,维度参照fmt
4. 聚合函数
max()
min()
avg()
sum()
count()
5. 分析函数
a. 聚合类分析函数
聚合函数名（目标字段）over(partition by...order by...desc/asc)
b. 排名类分析函数
row_number()over() --不计算相同数据，排名依次往后。1234
rank()over() --计算想要的数据，相同的数据排名一样，并空出后继续排名。1224
dense_rank()over()--计算相同的数据，相同的数据排名一样，但是不空出后续排名。1223
order by 后面的字段就是排名的依据，所以它一定有 order by
c. 位移类分析函数
lag()over([partition by...]order by...) --向上位移
lead()over([partition by...]order by...) --向下位移
可以实现行列转换
6. 几个判断函数
nvl(目标字段,默认值)
--判断目标字段是否为空，如果不为空，则取目标字段的值，如果为空，则赋默认值
nvl2(目标字段,默认值1,默认值2)
--判断目标字段是否为空，如果不为空，则取默认值1，为空取默认值2
case when...then...else...end --对某一列数据进行判断，返回一列数据
decode(目标字段,值1,结果1,值2,结果2...,其他结果)
--oracle独有。作用类似case...when
exists(x)
--判断x是否有数据返回，如果有则成立，没有则不成立。一般x为子查询结果
not exists(x)
in()
not in()
#exists和in可以相互转换
sign() ---判断结果集，返回0,1，-1
七、PLSQL
1. 语法结构
declare
--声明变量
变量 数据类型 := 初始值;
变量 数据类型 := &提示字段;--自定义输入数据
变量 表名.列名%type := 初始值;--使用某表的列定义数据类型
变量 表名%type := 初始值;--将整个表的数据拿到自身，需要使用其中的数据类型时，表名.列名调用即可。
begin
select...--sql取数语句
into...--对应变量
dbms_output.put_line();--数据输出格式
end;
plsql的数据类型参照sql，仅有三种：varchar2、number、date
2. 条件判断
a. if…else…
declare
--声明部分
begin
--逻辑/操作部分
select...
if 条件1 and/or 条件2 then
操作1;
elsif 条件3 and/OR 条件4 then
操作2;
...
else
其他操作;
end if;
end;
b. case…when…
declare
--声明部分
begin
--逻辑/操作部分
case
when 条件1 and/or 条件2 then
操作1;
when 条件3 and/OR 条件4 then
操作2;
...
else
其他操作;
end case;
end;
3. 循环控制
a. loop
declare
--声明部分
begin
--逻辑部分
loop
--循环体
if 退出循环的条件 then
exit;
end if;
--退出循环的简写
exit when 退出循环的条件;
end loop;
end;
b. while
declare
--声明部分
begin
while 进入循环的条件 loop
--循环体
end loop;
end;
c. for
declare
--声明部分
begin
--逻辑部分
for 循环变量 in 循环下限 .. 循环上限 loop
--循环体
end loop;
end;
4. 游标
游标就是一个指向结果集的指针，通过这个指针，可以获取到指向的结果集中的数据。
a. 隐式游标
select...into...
b. 显示游标
--声明显示游标的语法结构
declare
--声明部分
cursor 游标名 is
select查询结果集;
begin
--使用显示游标（其实就是 使用这个游标中的数据）：结合循环，依次 获取到游标指向的结果集的每一行
for 循环变量 in 游标名 loop
--循环体
--在循环体内可以依次使用结果集的每一行数据，使用的方式就是： 循环变量.游标指向的结果集的列名
end loop;
end;
--开发规范：游标名以 c_ 开头
c. 参数游标
declare
--声明/定义游标
cursor 游标名(参数1 数据类型[, 参数2 数据类型,...]) is
select查询结果集;
begin
--使用游标
for 循环变量 in 游标名(具体的值1,具体的值2,....) loop
--循环体
end loop;
end;
--开发规范：参数名以 p_ 开头
d. 手动管理游标
declare
--声明/定义游标
cursor 游标名[(参数1 数据类型[, 参数2 数据类型,...])] is
select查询结果集;
--需要定义个游标变量
变量名 游标名%rowtype;
begin
--使用游标
open 游标名[(实际的值1[,实际的值2,....])]; --打开游标
loop
--提取数据：将获取到的数据分别赋值给变量
fetch 游标名
into 游标变量
--退出循环
if 游标名%notfound then
exit;
end if;
--退出循环也可以简写成
exit when 游标名%notfound;
--操作...(输出，增删改，.....)
end loop;
close 游标名; -- 关闭游标
end;
5. 存储过程
create [or replace] procedure 存储过程名[(参数1 [参数类型] 数据类型[,参数2 [参数类型] 数据类型,...])]
is/* / as*/
--声明/定义部分
begin
--操作/逻辑部分
end;
--开发规范：存储过程名以 sp_ 开头
6. 自定义函数
CREATE OR REPLACE FUNCTION FUN_函数名(参数1 [IN|OUT|IN OUT] 数据类型，参数2 [IN|OUT|IN OUT] 数据类型……)
RETURN 返回结果的数据类型
IS /*|AS*/
BEGIN
自定义函数的的计算逻辑。
RETURN 计算结果 ; -- 函数的执行体中一定要包含 返回值
END ;
参数解释
存储过程和自定义函数有三种类型参数
IN 表示传入参数，不可以被 重新 赋值，
OUT 表示传出参数，可以被 重新 赋值，
IN OUT 表示传入传出参数，可以传入值，可以被 重新 赋值，可以返回值。
7. package
a. 创建包头
-- 创建包头的语法 ,代码的目录
CREATE [OR REPLACE] PACKAGE PKG_包名 -- 包的包头和包体的名称必须相同
IS /*|AS*/
-- 过程
PROCEDURE SP_过程名1 ;
PROCEDURE SP_过程名2(P_参数名 参数类型 数据类型) ;
-- 函数
FUNCTION FUN_函数名1 RETURN 计算结果的数据类型 ;
FUNCTION FUN_函数名2(P_参数名 参数类型 数据类型) RETURN 计算结果的数据类型 ;
END PKG_包名;
b. 创建包体
-- 创建包体的语法 ，代码的明细部分，包含的过程或者函数的 "完整" 代码
CREATE [OR REPLACE] PACKAGE BODY PKG_包名 -- 包的包头和包体的名称必须相同
IS /*|AS*/
-- 过程
PROCEDURE SP_过程名1
IS
过程的完整代码
END SP_过程名1 ;
-- 函数
FUNCTION FUN_函数名1
RETURN 计算结果的数据类型
IS
函数的完整代码
END FUN_函数名1 ;
END PKG_包名;
8. 常见应用
a. 全量同步数据
CREATE OR REPLACE PROCEDURE SP_EMP_BAK1
IS
/*全量同步的逻辑：
1 先删除目标表的数据
2 将源表的数据全量插入到目标表中 */
BEGIN
/* 1 先删除目标表的数据*/
-- 每次更新目标表之前，先清空目标表中的原有数据 ，这样可以支持重跑
DELETE FROM EMP_BAK1 WHERE 1 = 1 ; -- 如果没有这一步，那么表中有主键的话就会报 主键冲突/ 违反唯一约束 。
/*2 将源表的数据全量插入到目标表中*/
INSERT INTO EMP_BAK1(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
LAST_UPDATE_DATE)
SELECT
EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
SYSDATE
FROM EMP ;
COMMIT ; -- TCL事务控制语言 ，对 DML语句（增，删，改）的操作都需要做 TCL的操作
END SP_EMP_BAK1 ;
--3 开发完成以后 ，调用 并测试逻辑是否正确 。
BEGIN
SP_EMP_BAK1 ;
END ;
SELECT * FROM EMP_BAK1 ;
b. 增量同步数据
1）游标实现
CREATE OR REPLACE PROCEDURE SP_EMP_BAK1_CURSOR
IS
CURSOR C_EMP IS
SELECT * FROM EMP ;
/* 增量同步的逻辑：
1 先判断目标表中是否包含 源表中的数据 ，一般是通过 主键字段比较的。
2 如果源表的数据在目标表中 存在 ，那么就用 源表的数据 更新 目标表的数据 。
如果源表的数据在目标表中 不存在 ，，那么就将 源表的数据 直接插入到目标表中。*/
V_CT NUMBER(1) ;
BEGIN
FOR X IN C_EMP LOOP
/*1 先判断目标表中是否包含 源表中的数据 ，一般是通过 主键字段比较的。*/
SELECT COUNT(*) INTO V_CT FROM EMP_BAK1 WHERE EMPNO = X.EMPNO ;
/*2 如果源表的数据在目标表中 存在 ，那么就用 源表的数据 更新 目标表的数据 。
*/
IF V_CT = 1 THEN
UPDATE EMP_BAK1 M
SET -- 更新的字段中 不包括 比较字段 EMPNO ，这里不更新
M.ENAME = X.ENAME ,
M.JOB = X.JOB ,
M.MGR = X.MGR ,
M.HIREDATE = X.HIREDATE ,
M.SAL = X.SAL ,
M.COMM = X.COMM ,
M.DEPTNO = X.DEPTNO ,
M.LAST_UPDATE_DATE = SYSDATE
WHERE M.EMPNO = X.EMPNO ;
/* 如果源表的数据在目标表中 不存在 ，那么就将 源表的数据 直接插入到目标表中。 */
ELSIF V_CT = 0 THEN
INSERT INTO EMP_BAK1(EMPNO,
ENAME,
JOB,
MGR,
HIREDATE,
SAL,
COMM,
DEPTNO,
LAST_UPDATE_DATE)
VALUES(
X.EMPNO ,
X.ENAME ,
X.JOB ,
X.MGR ,
X.HIREDATE ,
X.SAL ,
X.COMM ,
X.DEPTNO ,
SYSDATE
);
END IF ;
END LOOP;
COMMIT ;
END SP_EMP_BAK1_CURSOR;
2）MERGE INTO 实现
CREATE OR REPLACE PROCEDURE SP_EMP_BAK1_MERGE
IS
/* 增量同步的逻辑：
1 先判断目标表中是否包含 源表中的数据 ，一般是通过 主键字段比较的。
2 如果源表的数据在目标表中 存在 ，那么就用 源表的数据 更新 目标表的数据 。
如果源表的数据在目标表中 不存在 ，，那么就将 源表的数据 直接插入到目标表中。*/
BEGIN
MERGE INTO EMP_BAK1 M -- 通过 MERGE 方法更新 EMP_BAK1 表
USING ( SELECT * FROM EMP ) X -- 使用 X 这个结果集
/*1 先判断目标表中是否包含 源表中的数据 ，一般是通过 主键字段比较的。 */
ON (M.EMPNO = X.EMPNO) -- 比较字段是2个表的主键字段 EMPNO
/*2 如果源表的数据在目标表中 存在 ，那么就用 源表的数据 更新 目标表的数据 。*/
WHEN MATCHED THEN
-- 这里 UPDATE SET 中间没有表名 ，因为要更新的目标表在上面 MERGE 后面已经指定了
UPDATE SET
M.ENAME = X.ENAME ,
M.JOB = X.JOB ,
M.MGR = X.MGR ,
M.HIREDATE = X.HIREDATE ,
M.SAL = X.SAL ,
M.COMM = X.COMM ,
M.DEPTNO = X.DEPTNO ,
M.LAST_UPDATE_DATE = SYSDATE
-- 这个后面不用写 WHERE 条件 ，同时也不能写 分号 ;
/* 如果源表的数据在目标表中 不存在 ，，那么就将 源表的数据 直接插入到目标表中。 */
WHEN NOT MATCHED THEN
-- 这里的 INSERT 后面不跟表名，因为上面已经指定了。
INSERT (
M.EMPNO ,
M.ENAME ,
M.JOB ,
M.MGR ,
M.HIREDATE ,
M.SAL ,
M.COMM ,
M.DEPTNO ,
M.LAST_UPDATE_DATE
)
VALUES(
X.EMPNO ,
X.ENAME ,
X.JOB ,
X.MGR ,
X.HIREDATE ,
X.SAL ,
X.COMM ,
X.DEPTNO ,
SYSDATE
);
COMMIT ;
END SP_EMP_BAK1_MERGE ;
c. 日志创建
1）日志创建逻辑
--记录日志的逻辑 ：
CREATE OR REPLACE PROCEDURE SP_TEST
IS
BEGIN
-- 在同步数据之前需要记录一个开始日志
SP_LOG( 记录日志的参数1 ， 2 ， 3 .... );
/*中间的代码是同步数据的代码 。*/
-- 在同步数据之前需要记录一个完成日志
SP_LOG( 记录日志的参数1 ， 2 ， 3 .... );
END SP_TEST;
2）创建日志常用方式
--日志创建模板
-- 创建日志表
CREATE TABLE T_LOG(
LOG_ID VARCHAR2(32) , -- 默认取 SYS_GUID() 的值作为日志表的主键
SP_NAME VARCHAR2(30) , -- 过程名 或者 对象名称的长度 不允许超过 30个字符
SP_STATUS VARCHAR2(12) , -- 执行状态
SP_START_DATE DATE , -- 开始时间
SP_FINISH_DATE DATE , -- 完成时间
SP_DESC VARCHAR2(50) -- 描述信息
);
select * from t_log;
-------------------------------- 基于日志表 创建一个 记录日志的存储过程
CREATE OR REPLACE PROCEDURE SP_T_LOG(
P_SP_NAME IN VARCHAR2 , -- 过程名称
P_SP_STATUS IN VARCHAR2 , -- 执行状态
P_START_DATE IN DATE , -- 开始同步时间
P_FINISH_DATE IN OUT DATE , -- 完成同步时间
P_SP_DESC IN OUT VARCHAR2 -- 描述
)
IS
BEGIN
INSERT INTO T_LOG(LOG_ID, -- 日志ID 主键
SP_NAME, -- 过程名称
SP_STATUS, -- 执行状态
SP_START_DATE, -- 开始同步时间
SP_FINISH_DATE, -- 完成同步时间
SP_DESC) -- 描述
VALUES(
SYS_GUID() , -- 日志ID 主键
P_SP_NAME , -- 过程名称
P_SP_STATUS , -- 执行状态
P_START_DATE , -- 开始同步时间
P_FINISH_DATE , -- 完成同步时间
P_SP_DESC -- 描述
);
COMMIT ;
END SP_T_LOG;
--------------------------- 调用记录日志的存储过程
DECLARE
V_FINISH_DATE DATE := SYSDATE ;
V_SP_DESE VARCHAR2(50) ;
BEGIN
SP_T_LOG('TEST1',
'完成' ,
SYSDATE ,
V_FINISH_DATE ,
V_SP_DESE) ;
END ;
SELECT * FROM T_LOG ;
c. 添加异常日志
-- 存储过程同步数的 异常日志
CREATE OR REPLACE PROCEDURE SP_EMP_0615
IS
---- 定义记录日志所需的变量
V_SP_NAME VARCHAR2(30) ; -- 过程名称
V_SP_STATUS VARCHAR2(12) ; -- 执行状态
V_START_DATE DATE ; -- 开始同步时间
V_FINSH_DATE DATE ; -- 完成同步时间
V_SP_DESC VARCHAR2(50) ; -- 描述
BEGIN
--------- 初始化变量
V_SP_NAME := 'SP_EMP_0615' ; -- 过程名称
V_SP_STATUS := '开始' ; -- 执行状态
V_START_DATE := SYSDATE ; -- 开始同步时间
-- 记录开始日志
SP_T_LOG( V_SP_NAME, -- 过程名称
V_SP_STATUS, -- 执行状态
V_START_DATE , -- 开始同步时间
V_FINSH_DATE, -- 完成同步时间
V_SP_DESC); -- 描述
---------------------------------------- 开始同步数据 ------------------------------------------
DELETE FROM EMP_0615 WHERE 1 = 1 ;
INSERT INTO EMP_0615(EMPNO,
ENAME,
SAL,
HIREDATE,
LAST_UPDATE_DATE)
SELECT EMPNO ,
ENAME ,
SAL ,
HIREDATE ,
SYSDATE
FROM EMP ;
COMMIT ;
---------------------------------------- 同步数据完成 ------------------------------------------
----------- 再次初始化变量
V_SP_STATUS := '完成' ; -- 执行状态
V_FINSH_DATE := SYSDATE ; -- 完成同步时间
-- 记录完成日志
SP_T_LOG( V_SP_NAME, -- 过程名称
V_SP_STATUS, -- 执行状态
V_START_DATE , -- 开始同步时间
V_FINSH_DATE, -- 完成同步时间
V_SP_DESC); -- 描述
---- 异常处理部分 ，如果存储过程执行过程中没有出现异常，这部分代码是不会执行的
EXCEPTION
WHEN OTHERS THEN -- 判断只要出现异常 ，那么执行下面的动作
-- 动作1 回滚数据
ROLLBACK ;
-- 动作2 记录异常日志
V_SP_STATUS := '错误' ; -- 执行状态
V_SP_DESC := SQLERRM ; -- 异常信息
SP_T_LOG( V_SP_NAME, -- 过程名称
V_SP_STATUS, -- 执行状态
V_START_DATE , -- 开始同步时间
V_FINSH_DATE, -- 完成同步时间
V_SP_DESC); -- 描述
END SP_EMP_0615;
八、SQL优化
1. 索引
索引我们可以看作是目录来理解。
a. 索引的使用方法
--创建索引
create [unique] index index_name on
table_name (column_name [,column_name...])
--索引类别
/*
1. UNIQUE:指定索引列上的值必须是唯一的。称为唯一索引。
2. index_name：指定索引名。
3. tabl_name：指定要为哪个表创建索引。
4. column_name：指定要对哪个列创建索引。我们也可以对多列创建索引；这种索引称为组合索引。
*/
--删除索引
DROP INDEX index_name
b. 索引失效
索引失效的原理，就是无法在某列中找到唯一的值。如：使用like进行模糊查询、使用不等号......
2. 表分区
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。
表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到一个或多个表空间，这样查询数据时，不至于每次都扫描整张表。
1、范围分区
【例】按入职日期进行范围分区
CREATE TABLE 表名 (列名 数据类型 [,列名 数据类型]...)
PARTITION BY RANGE (HIREDATE)
(
PARTITION part1 VALUES LESS THAN (TO_DATE('1981-1-1','YYYY/MM/DD')) TABLESPACE CUS_TS01, --①
PARTITION part2 VALUES LESS THAN (TO_DATE('1982-1-1','YYYY/MM/DD')) TABLESPACE CUS_TS02,
PARTITION part3 VALUES LESS THAN (TO_DATE('1983-1-1','YYYY/MM/DD')) TABLESPACE CUS_TS03,
PARTITION part4 VALUES LESS THAN (TO_DATE('1988-1-1','YYYY/MM/DD')) TABLESPACE CUS_TS04,
PARTITION part5 VALUES LESS THAN (MAXVALUE) TABLESPACE CUS_TS05
)
2.列表分区：
【例】按DEPTNO进行LIST分区
CREATE TABLE 表名 (列名 数据类型 [,列名 数据类型]...)
PARTITION BY LIST (DEPTNO)
(
PARTITION MYEMP_DEPTNO_10 VALUES (10),
PARTITION MYEMP_DEPTNO_20 VALUES (20) ,
PARTITION MYEMP_DEPTNO_30 VALUES (30) ,
PARTITION MYEMP_DEPTNO_40 VALUES (40)
)
3.散列分区:
CREATE TABLE 表名 (列名 数据类型 [,列名 数据类型]...)
PARTITION BY HASH (COL)
(
PARTITION PART01 TABLESPACE HASH_TS01,
PARTITION PART02 TABLESPACE HASH_TS02,
PARTITION PART03 TABLESPACE HASH_TS03
)
简写：
CREATE TABLE 表名 (列名 数据类型 [,列名 数据类型]...)
PARTITION BY HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);
4.组合分区(组合范围散列分区)
基于 范围分区 和 列表分区，表首先按某列进行范围分区，然后再按某列进行列表分区，分区之中的分区被称为子分区
【例】按入职日期进行范围分区，再按DEPTNO进行LIST子分区
CREATE TABLE 表名 (列名 数据类型 [,列名 数据类型]...)
PARTITION BY RANGE(HIREDATE) SUBPARTITION BY LIST (DEPTNO)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('1981-01-01','YYYY-MM-DD'))
(
SUBPARTITION P1A VALUES (10) ,
SUBPARTITION P1B VALUES (20),
SUBPARTITION P1C VALUES (30),
SUBPARTITION P1D VALUES (40)
),
PARTITION P2 VALUES LESS THAN (TO_DATE('1982-01-01','YYYY-MM-DD'))
(
SUBPARTITION P2A VALUES (10) ,
SUBPARTITION P2B VALUES (20),
SUBPARTITION P2C VALUES (30),
SUBPARTITION P2D VALUES (40)
),
PARTITION P3 VALUES LESS THAN (TO_DATE('1983-01-01','YYYY-MM-DD'))
(
SUBPARTITION P3A VALUES (10) ,
SUBPARTITION P3B VALUES (20),
SUBPARTITION P3C VALUES (30),
SUBPARTITION P3D VALUES (40)
),
PARTITION P4 VALUES LESS THAN (TO_DATE('1988-01-01','YYYY-MM-DD'))
(
SUBPARTITION P4A VALUES (10) ,
SUBPARTITION P4B VALUES (20),
SUBPARTITION P4C VALUES (30),
SUBPARTITION P4D VALUES (40)
)
)
5.复合分区(复合范围散列分区)
基于 范围分区 和 散列分区，表首先按某列进行范围分区，然后再按某列进行散列分区
CREATE TABLE 表名 (列名 数据类型 [,列名 数据类型]...)
partition by range(transaction_date) subpartition by hash(transaction_id)
subpartitions 3 store in (dinya_space01,dinya_space02,dinya_space03)
(
partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)),
partition part_03 values less than(maxvalue)
);
1)添加分区
以下代码给表添加了一个P3分区
ALTER TABLE 表名 ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD'));
注意：以上添加的分区界限应该高于最后一个分区界限。
以下代码给表的P3分区添加了一个P3SUB1子分区
ALTER TABLE 表名 MODIFY PARTITION P3 ADD SUBPARTITION P3SUB1 VALUES('COMPLETE');
2)删除分区
以下代码删除了P3表分区：
ALTER TABLE 表名 DROP PARTITION P3;
在以下代码删除了P4SUB1子分区：
ALTER TABLE 表名 DROP SUBPARTITION P4SUB1;
注意：如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。
3)截断分区
截断某个分区是指删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据。
当表中即使只有一个分区时，也可以截断该分区。
通过以下代码截断分区：
ALTER TABLE 表名 TRUNCATE PARTITION P2;
通过以下代码截断子分区：
ALTER TABLE 表名 TRUNCATE SUBPARTITION P2SUB2;
4)合并分区
合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，
注意的是，不能将分区合并到界限较低的分区。
以下代码实现了P1 P2分区的合并：
ALTER TABLE 表名 MERGE PARTITIONS P1,P2 INTO PARTITION P2;
5)拆分分区
拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
ALTER TABLE 表名 SBLIT PARTITION 分区名 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22);
6)接合分区(coalesca)
结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，
注意的是，接合分区只能用于散列分区中。
通过以下代码进行接合分区：
ALTER TABLE 表名 COALESCA PARTITION;
7)重命名表分区
以下代码将P21更改为P2
ALTER TABLE 表名 RENAME PARTITION P21 TO P2;
3. 执行计划
1、耗费和基数
2、看执行顺序
方法1： 提早过滤->避免全表扫描，先过滤在关联，用where代替having，避免多次扫表
方法2： 建索引索引->能走索引尽量走索引，注意不要写不规范的SQL导致索引失效
方法3：数据量大考虑表分区
方法4：hints命令修改执行计划（慎用）
4. 拉链表和缓慢变化维
主要对历史数进行处理。