Oracle数据库详解(超详细)

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 要返回的信息
fromjoin...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...
from1,2,...
where 关联条件1/过滤条件 [and/or 关联条件2 and/or ....]
--左外连接
select...
from1,2,...
where1的某个字段 运算符 表2的某个字段(+) --表1是主表,表2是从表
--右外连接
select...
from1,2,...
where1的某个字段(+) 运算符 表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( 记录日志的参数123 ....   );
  
                    /*中间的代码是同步数据的代码 。*/

 -- 在同步数据之前需要记录一个完成日志 
 SP_LOG( 记录日志的参数123 ....   );
 
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. 拉链表和缓慢变化维

主要对历史数进行处理。
评论 12
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

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

余额充值