Oracle入门_oracle那个版本最经典-CSDN博客

标量（Scalar）类型：字符、数值、日期、布尔、行、行标识等
复合（Composite）类型：记录、集合、变长数组、嵌套表、关联数组、记录表
引用（Reference）类型：用于存储对另一个数据类型的引用。
LOB（Large Object）类型：CLOB：用于存储大量的字符数据，如文本文件或XML文档；BLOB：用于存储大量的二进制数据，如图片、音频、视频或Office文档。
自定义类型

类型转换

类型转换有两种：显式转换、隐式转换。

显式转换：使用 cast 函数或使用to_number、to_char、to_date函数实现

隐式转换：隐式转换是数据库自动进行的，通常不需要用户干预。隐式转换发生在数据类型兼容并且转换操作是安全的前提下。在使用函数和操作符时，其中包括算术操作、字符串连接、比较等

数字类型（比如：NUMBER, INT）可以隐式转换为更大的数字类型。
字符串可以转换为数字，前提是字符串仅包含数字，并且符合数字的格式。
日期类型可以转换为字符串，反之亦然，根据会话的NLS_DATE_FORMAT参数。
对于TO_DATE函数，字符串会根据NLS_DATE_FORMAT参数转换为日期类型。

关键字

EXISTS

主要是对外表（即主查询的表）做 LOOP 循环。用于检查子查询是否返回任何结果。它只关心是否存在至少一行数据满足条件，而不关心具体返回了多少行

select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;

exists的子查询一定要是等值关联：where T1.a=T2.a

操作符实际上是把外表和内表（子查询的表）做 hash 连接。用于检查某个值是否存在于子查询返回的结果集中。它实际上会评估子查询返回的所有结果，并检查外部查询中的值是否在这些结果中。

在某些情况下，数据库优化器可能会对查询进行优化，使得两者的性能差异变得不那么明显。但在理论上由于 EXISTS 只需确认子查询是否至少返回一行结果，因此它在找到第一行符合条件的结果时就会停止搜索，所有当子查询可能返回大量数据时，EXISTS通常会比IN更快。使用 NOT IN 时需要特别注意，因为如果子查询中有 NULL 值，它可能导致全表扫描，从而降低查询效率。在这种情况下，使用 NOT EXISTS 或其他方法可能更为高效

SELECT ename FROM emp WHERE empno IN (value1, value2, value3);

SELECT ename FROM emp WHERE deptno IN (select deptno from 表2);

使用场景：

EXISTS：当你只关心是否存在满足条件的记录，而不关心具体是哪些记录时，使用EXISTS是合适的。例如，检查某个用户是否存在于用户表中。
IN：当你需要从一个集合中筛选出存在于另一个集合中的元素时，使用IN是合适的。例如，从订单表中筛选出属于某个客户ID的所有订单。

总结：

EXISTS 适用于外表小而内表大的情况，因为它通过 LOOP 循环外表，并对每个外表行执行内表查询，循环次数越少，速度越快。
IN 适用于外表大而内表小的情况，因为它通过 hash 连接外表和内表，内表越小，查询范围越小，效率越高。

SQL语法

SQL规范

注释
单行注释: -- 注释内容(这种方式是通用的)
多行注释: /* 注释 */

连接运算符 ||

select '名字为：' || value || '职位为：' value from 表名；

dbms_output.put_line(v_a||'-'||v_b)；

别名

可以给表、字段起别名，格式：

字段名 as "别名"

字段名 as 别名

字段名 "别名"

字段名别名

别名不能以数字开头也不能有除了下划线以外的特殊字符。""可以让别名里有空格，视为整体。

SQL分类

DDL：数据库定义语言(常指建表语句)、DML数据库管理语言、DQL数据库查询语言、DCL数据库控制语言、TCL事务控制语言。有时候会把DQL算进DML中。

伪表

dual是一个伪表，用来构成select的语法规则。

常用操作

--cmd超级管理员登录
sqlplus / as 
--切换连接
conn 用户名/密码
--或者
sqlplus / as sysdba
--执行sql脚本
@脚本绝对路径
--查询所有用户
select username from dba_users

-- 设置控制台显示

set serveroutput on

对象

-- 同义词是数据库的一个别名

-- 管理员授予用户A创建同义词的权限

grant create synonym to A用户

-- 创建私有同义词

create synonym 同义词名 for 表名

-- 为视图创建同义词

create synonym 同义词名 for 视图名

-- 查询同义词

select * from 同义词名

-- 删除同义词

drop synonym 同义词名

-- sys用户赋予用户A创建删除公有同义词的权限

grant create public synonym,drop public synonym to 用户A

-- sys用户回收用户A创建删除公有同义词的权限

revoke create public synonym,drop public synonym from 用户A

-- 创建公有同义词

create public synonym 同义词名 for 表名

CREATE 创建用户
CREATE USER 用户名 IDENTIFIED BY 密码
DEFAULT TABLESPACE 表空间的名字 -- 指定用户空间
TEMPORARY TABLESPACE 临时表空间的空间名 -- 指定临时表空间
QUOTA UNLIMITED ON 用户空间名字 -- 指定配额

alter user 用户名 identified by 新密码" --修改用户密码

-- 创建角色

create role 角色名

DDL

DDL语句是隐式事务。有时DDL专指建表语句。

-- 查看系统所有变量值：
show all：
-- 显示当前连接用户：
show user；
-- 显示表结构：
desc 表名；
-- 显示错误：
show error；
-- 退出：
exit;

CREATE

创建表有三种方式

1.普通创建

create table 表名 (

字段名数据类型 [not null] [primary key],

字段名数据类型

);

2.根据查询结果创建

create table 表名 as select * from 被复制数据库.表名

3.从现有的表中创建表并复制其表结构，不包括数据

create table 表名 as select * from 被复制数据库.表名 where 1=2

给表添加注释

COMMENT ON TABLE 表名 is '';

给字段添加注释

COMMENT ON COLUMN 表名.字段 is '';

ALTER

给表添加列

ALTER TABLE 表名 ADD(列名数据类型 );

修改表中字段(字段的属性修改)

alter table 表名 modify(字段1 字段类型, 字段2 字段类型)

删除表中字段(字段是要删除的字段)

alter table 表名 drop (字段1，字段2)

修改表名

ALTER TABLE 表名 RENAME TO 新的表名;

修改表字段的名称

alter table 表名 rename column 原字段名 to 新字段名

修改用户对象

ALTER USER SCOTT IDENTIFIED BY 新的密码;

DROP

DROP 删除

DROP TABLE 表名;-- 删除表(删除后可回收站查看并恢复)

drop table 表名-- 删除表(删除后不可查看和恢复)

删除表后在创建一个和原有表结构相同的表

truncate table 表名

DML

INSERT INTO

1.插入数据

INSERT INTO 表名(字段,字段2...) VALUES (值1,值2...)

2.根据查询结果插入数据

INSERT INTO 表名(字段,字段2...)
SELECT 查询 ;

注意：值个数和数据类型要和前面字段保持一致；不指定列名就需要每个字段都写上

DELETE

DELETE FROM 表 WHERE 过滤条件;

不加where条件就是删除全表

执行的原理

where字句的评估：根据where 确定要删除的数据
行级锁定,表的锁定：根据where 过滤出来的数据,对相应行进行锁定,同时,会锁表
行删除：会逐行的删除数据,并且将删除的数据放入undo表空间中
触发器执行：如果设置了触发器,并且和对于表进行绑定,就会触发,触发器,执行对应的操作
事务的提交：事务可以提交,也可以回滚,如果提交,数据就会持久化到数据库中,比如永久的删除改

delete与truncate区别：

delete是根据where条件删除一条或多条数据徐，需要提交事务，有表的锁定和触发器执行。truncate是清空表，原理是删除表后新建一个数据类型、约束一致的新表。

UPDATE

UPDATE 表名 SET 字段1= 值1,字段2 = 值2 WHERE 过滤条件;

不加where条件就是修改全表

DQL

基础查询

数据库查询语句

select 字段 from 表名

where 查询条件

group by 分组条件 having 分组过滤条件

order by 排序规则

查询语句执行顺序：

from 》连表(join on)》where 》 group by 》having 》 select 》distinct》order by

模糊查询：关键字like，通配符%：匹配任意个任意字符，占位符_：匹配一个任意字符。

分组查询：关键字group by 字段/函数/表达式，使用分组后查询的字段只能为分组字段或聚合函数。having 是分组过滤，查询时遵循先过滤的原则，能提前过滤就先提前，谓词下推，提交效率。

排序：order by 字段/表达式/函数排序规则，排序规则默认为asc(升序)，降序为desc；在排序中，null值会被视为无穷大，可使用 nulls first/nulls last排序，将null值放在最前或最后。多个字段排序使用"，"隔开，

子查询

子查询允许在主查询中临时获取查询结果，以实现更精细的查询。子查询可以出现在WHERE、HAVING和FROM子句中。

单行子查询(返回单行数据的子查询语句)：使用单行比较符(>、=、<、<>)

多行子查询(返回多行数据的子查询语句)：可以使用单行比较符，也可以使用多行比较符

多列子查询()：子查询返回多列时，主查询必须要对应：

SELECT column1, column2
FROM table1
WHERE (column1, column2) IN (SELECT columnA, columnB FROM table2);

标量子查询：是一个返回单一值的子查询(一行一列)

select deptno,dname,(select sum(sal) from emp where deptno = d.deptno) from dept d;

子查询如果出现在HAVING中，只有一种情况，即子查询返回单行单列数据，且需要进行统计函数计算。

注意：

子查询必须用括号括起来。
子查询必须放在主查询的条件表达式中，如SELECT、WHERE、HAVING、FROM子句中。
子查询必须在主查询语句执行之前执行，并将查询结果传递给主查询
子查询里的where条件能和主查询起到类似连表作用

select deptno,dname,(select sum(sal) from emp where deptno = d.deptno) from dept d;

with as（子查询部分）

优点：1.结构清晰，提高了sql的易读性；2.一次构建，多次使用。如果with as定义的表名被调用两次以上，那么优化器会自动放入临时表中(只有一次就不会)，提高效率，避免多次执行。

多表查询

交叉连接(笛卡尔积)

select 字段 from A，B where 连接条件

内连接查询

select 字段 from A [inner] join B on 连接条件

左外连接查询

以左表为基准关联右表，右表里对应关联没有的数据显示null

select 字段 from A left [outer] join B on 连接条件

右外连接查询

以右表为基准关联左表

select 字段 from A right [outer] join B on 连接条件

全外连接查询

结合左外，右外连接两者，两个表都显示完全

select 字段 from A full [outer] join B on 连接条件

自然连接

查询两个表相同的字段

select 字段 from A natural join B on 连接条件

自连接

指同一个表通过别名创建表的副本，并使用这些副本与原始表进行连接。自连接必须起别名

等值连接

等值连接是最常见的连接类型，它基于两个表之间的相等条件来连接行

不等值连接

不等值连接是基于两个表之间的不等条件来连接行，使用比较运算符（如<、>、<=、>=、<>或BETWEEN ... AND ...）来指定连接条件。

on和where区别：

ON关键字主要用于连接操作，特别是在执行JOIN操作时，它定义了如何连接两个或多个表。ON条件在连接操作之前执行，用于筛选哪些行应该被连接。
WHERE关键字用于对查询结果进行过滤。它通常在JOIN操作之后执行，对生成的临时表进行筛选，只返回满足条件的行。
外连接里过滤条件写在on上会比where查询条数多，如果过滤条件是从表的就过滤掉，主表的被过滤数据虽然不会过滤但不会被join

DCL

授权权限

语法：GRANT 权限 ON 对象 TO 用户/角色

权限的分类

操纵数据类：在ORACLE表是属于用户,需要授权才访问SELECT INSERT UPDATE DELETE
对象管理类：CREATE TABLE : 建表权限 CREATE VIEW : 创建视图权限 CREATE PROCEDURAL : 创建存储过程 ALTER TABLE : 修改 ALTER对象 DROP TABLE : 删表 DROP 对象
系统类的CREATE SESSION : 创建会话 CONNECT : 创建连接 RESOURCE: 创建资源
其他类EXECUTE : 允许其他用户去执行对象的权限 SELECT ANY TABLE : 允许用户查看不是自己的表

查看权限

select * from user_sys_privs

授予权限：GTRANT 权限 ON 表名 TO 用户

撤销授权：REVOKE 权限 ON 对象 FROM 用户;

授予表A的 select 操作权限给用户B

grant select on 表A to 用户B

撤销表A的update操作权限给用户B

revoke update on 表A from 用户B

TCL

事务控制语言：commit 提交、rollback 撤回

Oracle与MySQL不同的是：MySQL是隐式事务，Oracle是显式事务，增删改操作都要commit

函数

聚合函数

max，min，avg，sum，count等等，其中avg和sum只能处理数值型数据

聚合函数对null值的处理一般有两种：1.无视null值：avg、max、min、count；2.视为0：sum；

极端情况(处理的全为null)：除count返回0外，其他返回null。

聚合函数可以单独使用，也可以配合分组字段使用，但不能和未分组字段一起使用；聚合函数只能在group by 之后使用，之前或group by 中都不能使用(子查询除外)；

count(1)、count(*)、count(列名)效率区别

有主键：count(主键)>count(1)>count(*)
没有主键：count(1)>count(*)>count(字段)
只有一个字段，count(*)最优

常见函数

Oracle常见函数类型有单行函数、聚合函数、条件控制函数等，单行函数里有数值函数、字符串函数、日期函数等。

数值函数

函数名	描述
ABS(X)	取绝对值
POWER(X,Y)	求X的Y幂次方
MOD(X)	求余
ROUND(X,Y)	对X四舍五入，Y是精确的位数
TRUNC(X,Y)	对X截取，Y是精确的位数
CEIL(X)	向上取整
FLOOR(X)	向下取整

字符串函数

函数名	描述
CONCAT(X,Y)	字符串的拼接
WM_CONCAT(X)	将X 放到一个字段中
SUBSTR(X,Y,Z)	对X进行切割,Y代表从哪里开始切,Z代表切的长度
LENGTH(X)	获取字符串的X字符长度
LENGTHB(X)	获取字符串的X字节长度
INSTR(STR,TAG,字符位,次数)	在字符串中查找目标字符串,返回目标字符串的字符位，不存在则返回0，缺省的写法默认,从第一位,找第一次出现的位置
ASCII(X)	返回 x的十进制的ASCII 码值
CHR(X)	接受十进制的ASCII 码值 ,返回对应的字符
REPLACE(X,查找值,替换值)	字符串的替换
UPPER(X)	小写转大写
LOWER(X)	大写转小写
LTRIM(X,切割集合)	从左边切，缺省的写法 , 默认切空格
RTRIM(X,切割集合)	从右边切，缺省的写法 , 默认切空格
TRIM(X,切割集合)	从两边切，缺省写法 ,默认截取两边的空格
LPAD(string1, length, [pad_string])	在字符串的左侧填充指定的字符，以达到指定的长度。如果这个长度比原字符串的长度要短，‌lpad函数将会把字符串截取成从左到右的n个字符；填充字符串默认空格；如果填充字符串+原字符串长度大于指定长度那么会截取填充字符串
RPAD(string, length, pad_string)	在字符串的右侧填充指定的字符，以达到指定的长度。
NVL(expression1,expression2)	根据参数1是否为null返回参数1或参数2的值
NVL2(expression1,expression2,expression3)	根据参数1是否为null返回参数2或参数3的值

日期函数

日期运算：日期可以相减不能相加，日期可以加日期间隔。

  -- 单种日期间隔 
  SELECT SYSDATE + INTERVAL '2' YEAR FROM DUAL;
  SELECT SYSDATE + INTERVAL '2' MONTH FROM DUAL;
  SELECT SYSDATE + INTERVAL '2' DAY FROM DUAL;
  SELECT SYSDATE + INTERVAL '2' HOUR FROM DUAL;
  SELECT SYSDATE + INTERVAL '2' MINUTE FROM DUAL;
  SELECT SYSDATE + INTERVAL '2' SECOND FROM DUAL;
  -- 日期间隔的相减
  SELECT SYSDATE - INTERVAL '2' YEAR FROM DUAL;
  SELECT SYSDATE - INTERVAL '2' MONTH FROM DUAL;  
-- 组合类型的时间间隔
  INTERVAL DAY TO SECOND
  INTERVAL YEAR TO MONTH
  --  INTERVAL DAY TO SECOND
  SELECT SYSDATE + INTERVAL '1 05:10:10' DAY TO SECOND FROM DUAL;
  -- INTERVAL YEAR TO MONTH
  SELECT SYSDATE + INTERVAL '2-02' YEAR TO MONTH FROM DUAL;
-- 日期相减 返回天数 
  SELECT SYSDATE - (SYSDATE -1)     FROM DUAL;
  -- 日期直接加上数值 
  SELECT SYSDATE + 100     FROM DUAL;

函数	描述
ADD_MONTHS(DATE,NUM)	给日期加上num个月，num为负数时为减
MONTHS_BETWEEN(DATE1,DATE2)	date1，date2相差的月份数
ROUND(DATE,DATE_FORMAT)	对日期进行四舍五入标准为年：七月1号；月：16号；日：中午12点；季度：第二月的16号；星期：星期三的中午12点
TRUNC(DATE,DATE_FORMAT)	对日期进行截取
LAST_DAY(DATE)	获取DATE 当月的最后一天
TO_CHAR(DATE,DATE_FORMAT)	按日期格式返回，注意：返回值是字符串
EXTRACT(YEAR/MONTHDAY from 日期列)	返回年/月/日

分析函数

使用 over(c) 进行开窗，和group by一起使用时所有字段都在group by里才可以。开窗函数是在数据排序之后，按照指定的分区和排序规则对数据进行处理并返回结果。因此，Oracle开窗函数的执行顺序是在ORDER BY之后进行的。

窗口范围

窗口函数通过OVER()子句来指定函数执行的窗口范围。‌如果OVER()子句为空，‌则窗口包含满足WHERE条件的所有行。‌窗口函数可以基于所有行进行计算，‌也可以通过PARTITION BY子句将数据分成不同的分区，‌在每个分区内分别执行计算。‌

PARTITION BY：‌用于定义窗口的分区，‌窗口函数将分别对每个分区执行计算。‌
ORDER BY：‌用于定义窗口内行的排序顺序。‌
ROWS/RANGE BETWEEN：‌用于定义窗口的大小，‌即包含哪些行进行聚合计算。‌ROWS表示物理行范围，‌RANGE表示逻辑范围。‌例如，‌ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING表示当前行前后各移动3行；‌RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING表示当前行数据幅度减3加3后的范围内的数据。‌

窗口边界：

unbounded：无界限
current row：当前行，偏移量为0，一般和其他范围关键字一起使用，默认值
unbounded preceding 边界是分区中的第一行
unbounded following 边界是分区中的最后一行
N preceding 边界是当前行减去N的值,N为相对当前行向前的偏移量.从分区第一行头开始，则为 unbounded.
N following 边界是当前行加上N的值,N为相对当前行向后的偏移量.与preceding相反,到该分区结束，则为 unbounded.

聚合类

聚合函数名(字段) over(partition by 分组条件 order by 排序条件)，over参数不写则是把聚合结果放入每一行中。

聚合类分析函数可以避免使用聚合函数要分组的条件，将聚合结果在每一行上显示。

注意：使用order by会有累加效果

排名类

row_number()/rank()/dense_rank() over(partition by 分组条件 order by 排序条件)，此处排序条件必须填写。

三种排名函数的区别：

ROW_NUMBER()：这个函数会给每个分区的行分配一个唯一的序号。序号在分区内是连续的，例如1,2,3,...N。
RANK()：这个函数会给每个分区的行分配一个唯一的排名。如果有两个或更多具有相同值的行，则它们将接收相同的排名，并且下一个排名将跳过这些值。例如1,2,2,4,5,...
DENSE_RANK()：这个函数会给每个分区的行分配一个唯一的排名。如果有两个或更多具有相同值的行，则它们将接收相同的排名。下一个排名将不会跳过这些值。例如1,2,2,3,4,...

偏移类

lag()/lead(偏移列,偏移行数,默认值) over(partition by 字段 order by 必须字段 ),，此处排序条件必须填写。默认值：当偏移量超出范围时返回的默认值

LAG()：这个函数返回当前行的上一行的指定列的值，即向下偏移。
LEAD()：这个函数返回当前行的下一行的指定列的值，即向上偏移。

其他：

ntilenum) over(order by 字段)：根据指定字段排序顺序进行分桶，分桶大小为num
FIRST_VALUE 获取窗口的第一个值
LASY_VALUE 获取窗口的最后一个值

条件函数

decode语法：

DECODE(匹配字段,
匹配值,返回值,
匹配值,返回值,
...
默认值)

匹配值是固定值，返回值可以是固定值也可以是表达式

不写匹配值的返回默认值，不写默认值则默认返回null

case when语法：

CASE WHEN 判断条件 THEN 返回值
WHEN 判断条件 THEN 返回值
....
ELSE 默认值 -- 默认可以不写,返回空值
END

如果在case when 中符合第一个条件,其他满足的条件就不会执行了

自定义函数

在PL/SQL自行定义函数使用，在PL/SQL里会详细说明

行列相关

伪列

ROWID 唯一物理地址

ROWNUM 行数，在order by 之后生效

rownum：根据查询结果生成行号,只能=1,或者< 某个值,或者小于等于某个值,但是不能大于或者是=非1的值

ROWNUM 三个用处: 1.返回固定的数据 2.做排名(不如row_number) 3.做分页查询

-- 排名取前三
  SELECT E.*,ROWNUM 
  FROM EMP E 
  WHERE ROWNUM < 4 
  ORDER BY E.SAL;

 SELECT *
    FROM (
          -- 4
          SELECT E.*,ROWNUM RN 
          FROM (SELECT *
                FROM EMP 
                ORDER BY SAL DESC) E
          WHERE ROWNUM < 5)
    WHERE RN>1;
    -- 方式二:
    SELECT *
    FROM (
          -- 14
          SELECT E.*,ROWNUM RN 
          FROM (SELECT *
                FROM EMP 
                ORDER BY SAL DESC) E
           )
    WHERE RN BETWEEN 2 AND 4;

行列转换

行转列

四种方法:DECODE,CASE WHEN,偏移类函数,PIVOT。

使用 DECODE 实现

SELECT T.Y 年,
       DECODE(T.Q,1,T.AMT ) Q1, 
       DECODE(T.Q,2,T.AMT ) Q2,
       DECODE(T.Q,3,T.AMT ) Q3,
       DECODE(T.Q,4,T.AMT ) Q4 
FROM TEST_TABLE T;

CASE WHEN 实现

SELECT T.Y 年,
       MAX(CASE WHEN T.Q = 1 THEN T.AMT END) Q1, 
       MAX(CASE WHEN T.Q = 2 THEN T.AMT END) Q2,
       MAX(CASE WHEN T.Q = 3 THEN T.AMT END) Q3,
       MAX(CASE WHEN T.Q = 4 THEN T.AMT END) Q4 
FROM TEST_TABLE T
GROUP BY T.Y;

偏移函数

SELECT S.Y,S.Q1,S.Q2,S.Q3,S.Q4
FROM (
 SELECT T.Y,T.Q,T.AMT Q1,
        LEAD(T.AMT,1,0)OVER(PARTITION BY T.Y ORDER BY T.Q) Q2,
        LEAD(T.AMT,2,0)OVER(PARTITION BY T.Y ORDER BY T.Q) Q3,
        LEAD(T.AMT,3,0)OVER(PARTITION BY T.Y ORDER BY T.Q) Q4 
 FROM TEST_TABLE T) S 
 WHERE S.Q = 1;

PIVOT

--语法: SELECT * FROM (查询结果集)
PIVOT (聚合函数(需要转换的列) FOR 判断列 IN (判断值 AS 别名,判断值 AS 别名))

列转行

两种：union all和unpivot

union all 完成列转行

 SELECT T.Y, 1 Q ,T.Q1 FROM TEST_TABLE2 T 
 UNION ALL 
 SELECT T.Y, 2 Q ,T.Q2 FROM TEST_TABLE2 T 
  UNION ALL 
 SELECT T.Y, 3 Q,T.Q3 FROM TEST_TABLE2 T 
  UNION ALL 
 SELECT T.Y, 4 Q,T.Q4 FROM TEST_TABLE2 T
 ORDER BY Y ,Q  ;

UNPIVOT

SELECT 字段
FROM 表 UNPIVOT
( (需要逆转的字段)
FOR 需要新增的列 IN ( 表里的字段(Q1) AS 新增的值,表里的字段(Q1) AS 新增的值,表里的字段(Q1) AS 新增的值,表里的字段(Q1) AS 新增的值 ));

行拼接

注意：上下结果集的字段数量和数据类型要一致

UNION ALL

上下结果集取并集,不去重

UNION

上下结果集取并集,去重

MINUS

减集：上下结果集取补集返回第一个查询结果存在,但是在第二个查询结果中不存在的数据

intersect

交集：返回两个或更多结果集的公共部分

多列比较

使用CASE语句或DECODE函数
使用GREATEST和LEAST函数：Oracle提供了GREATEST和LEAST函数，用于返回一组值中的最大或最小值。但是，这些函数通常用于比较同一数据类型的值，并且是在同一行内的多个列或表达式之间进行比较，而不是跨行的多个列
行列转换
使用窗口函数
使用子查询或连接

事务

事务：是一个逻辑工作单元。由一系列的sql组成，这些语句要么同时成功，要么同时失败。
事务特性:ACID

原子性: 事务要么同时成功，要么同时失败
一致性: 事务从一个状态到另外一个状态，数据库的约束和完整性都要得到保障
隔离性: 事务和事务之间是隔离的，互不干涉
持久性:事务一旦提交成功，就永久改变数据库，即使数据库故障,也不会导致数据丢失

insert into a values(1);
begin
  sp_a;
end;

create or replace procedure sp_a
is
--开启自治事务:自己的事情自己做,外面的逻辑影响不到事务里面的逻辑
pragma autonomous_transaction;
begin
  insert into a values(2);
  commit;
  exception
    when others then
      rollback;
end;

一个会话里不加commit会提交所有语句；进入事务由于隔离性，只对事务里的语句提交，所有事务必须加commit(不加事务里的无法提交)。

PL/SQL

基础语法

语法：

DECLARE  --可以没有
--声明变量
BEGIN  --必须
 --逻辑代码块,必须，里面的每句都要加;
 EXCEPTION --异常处理
  --异常类型
END; --结束必须加;

输出语句

单行输出：dbms_output.out('内容')；

换行输出：dbms_output.out_line('内容')；

注意：单行输出在控制台是没有内容的，必须靠换行输出显示

变量

变量名规范上以v_开头

格式：v_变量名变量类型；可以赋初始值，变量赋值使用:=。

定义从外界获取值的变量，使用&

数值类型：v_变量名变量类型 := &名称；

字符类型：v_变量名变量类型 := '&名称'；

日期类型：v_变量名变量类型 := to_date('&名称','日期格式')；

引用定义

数据类型引用表字段

列字段使用%type：v_变量名表名.字段名%type := 值；

行字段使用%rowtype：v_变量名表名%rowtype；

引用行字段时不能赋初始值，必须在逻辑中.字段赋值。

变量类型

定义字符类型时，必须加上长度；数值类型可以加也可以不加长度，不加默认38位(计算时可达到40位)；日期类型无长度。

常量

常量在声明时赋初始值，后续逻辑中不能再赋值。

格式：常量名 constant 数据类型 := 值；

异常

当PL/SQL里出现报错时执行。系统的预定义异常：数据库给某个错误赋予了错误的编号和错误的名字（21种）。
常见的异常类型:

NO_DATA_FOUND :没有找到该数据
DUP_VAL_ON_INDEX:唯一索引对应的列上有重复值
ZERO_DIVIDE:除数为0
TIMEOUT_ON_RESOURCE:Oracle等待资源超市
too_many_rows 查询数据太多
value_error 赋值的错误
others 其他异常

自定义异常

1.declare声明异常；2.逻辑中reise异常；3.exception中接收

--自定义异常
declare
v_a number:=&1;
--1.定义异常变量
v_b exception;
begin
  for i in 1..v_a loop
    --2.在逻辑中写什么条件下去触发异常变量
    if i=5 then
      raise v_b;
    end if;
    dbms_output.put_line(i);
  end loop;
  exception
    --3.接受异常变量(不写第三步就进入其他异常)
    when v_b then
      dbms_output.put_line('进入了自定义异常');
    when others then
      dbms_output.put_line(sqlerrm);
end;

更改原有异常的报错信息

declare
v_ename number;
--1.定义异常变量
v_a exception;
--2.把报错通过报错编号绑定到异常变量上
pragma exception_init(v_a,-6502);
begin
  v_ename:='a';
  exception
    --3.接受异常变量
    when v_a then
      dbms_output.put_line('触发异常变量');
    when others then
      dbms_output.put_line(sqlerrm);
      dbms_output.put_line(sqlcode);
end;

条件判断

格式：

if 条件1 then 逻辑1;
elsif 条件2 then 逻辑2;
...
else 逻辑;
end if;

循环

loop循环

loop
  循环逻辑;
  exit when 条件;
end loop;

while循环

--语法:
while 条件 loop
  循环逻辑
end loop;

for循环

for 循环变量 in 下限..上限 loop
  循环逻辑;
end loop;
--for循环的上下限不能颠倒

for循环如果要上下限相反顺序循环，需要使用reverse关键字

begin
  for i in reverse 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;

跳出循环

跳出循环:exit
跳过本次循环:continue

begin
  for i in 1..10 loop
    if i=5 then
      continue;
    end if;
     dbms_output.put_line(i);
  end loop;
end;

游标

游标分为 显式游标 和 隐式游标(Oracle为所有sql操作声明一个游标,包括已返回一条数据的查询)。

游标一般配合循环使用。

显式游标：

declare
--创建游标c_emp,并把下面查询出的结果放进该游标中
cursor c_emp
is
select empno from emp;
--定义变量用来接收游标中的数据
v_empno number;
begin
  --打开游标
  open c_emp;
  loop
    --从游标中进行取数据,并把取到的数据放进相对应的变量中
    --游标中有多少个字段into后就需要有多少个变量进行接收,并且相对应的数据类型必须一致
    fetch c_emp into v_empno;
    dbms_output.put_line(v_empno);
    exit when v_empno=7934;
  end loop;
  --关闭游标
  close c_emp;
end;

带参数的游标

declare
cursor c_emp(p_deptno number,p_sal number)
is
select * from emp where deptno=p_deptno and sal>p_sal;
v_count number;
v_cs number:=0;
v_emp emp%rowtype;
begin
  select count(*) into v_count from emp where deptno=&部门编号 and sal>&工资;
  open c_emp(&请输入部门编号,&请输入工资);
  loop
    fetch c_emp into v_emp;
    dbms_output.put_line(v_emp.empno||' '||v_emp.deptno||' '||v_emp.sal);
    v_cs:=v_cs+1;
    exit when v_cs=v_count;
  end loop;
  close c_emp;
end;

隐式游标

--临时游标
begin 
  --for循环的上下限由子查询代替
  for i in (select * from test) loop
    dbms_output.put_line(i.n);
  end loop;
  commit;
end;

游标属性

%FOUND 布尔型属性，当最近一次读取记录时成功返回，则值为TRUE，否则为FALSE。
%NOTFOUND 布尔型属性，与%FOUND相反。
%ISOPEN 布尔型属性，当游标已打开时返回TRUE，否则返回FALSE。
%ROWCOUNT 数字型属性，返回已从游标中读取的记录数。

declare
cursor c_emp(p_deptno number,p_sal number)
is
select * from emp where deptno=p_deptno and sal>p_sal;
v_emp emp%rowtype;
begin
  open c_emp(&部门编号,&工资);
  fetch c_emp into v_emp;
  while c_emp%found loop
    dbms_output.put_line(v_emp.empno||' '||v_emp.deptno||' '||v_emp.sal);
    fetch c_emp into v_emp;
  end loop;
  close c_emp;
end;

PL/SQL语言提供了游标FOR循环语句，自动执行游标OPEN、FETCH、CLOSE语句和循环语句的功能；当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据，当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理，当提取完结果集中的所有数据行后结束循环，并自动关闭游标。

进阶

动态sql

在PL/SQL程序开发中，可以使用DML语句，但是很多语句(如DDL，有变量的DML等等) 不能直接在PL/SQL中执行，这些语句可以使用动态SQL来实现。

语法格式：EXECUTE IMMEDIATE 动态语句字符串
[INTO 变量列表]
[USING 参数列表]

游标属性在动态sql里也可以使用，使用sql%属性

如果动态sql是select语句，就需要把查询出的内容保存到into后的变量中；如果动态SQL中存在参数，using可以为参数传值，使用：参数名接受。

declare
v_sql varchar(200);
v_empno emp1.empno%type;
v_ename emp1.ename%type;
v_sal emp1.sal%type;
begin
  v_sql:='select empno,ename,sal from emp1 where empno= :p_empno';
  execute immediate v_sql
  into v_empno,v_ename,v_sal
  using &1;
  dbms_output.put_line(v_empno||'-'||v_ename||'-'||v_sal);
end;

函数

即之前的自定义函数，函数有且只有一个返回值。存储过程和函数统称为PL/SQL子程序，他们是被命名的PL/SQL块，均存储在数据库中，并通过输入、输出参数或输入/输出参数与其调用者交换信息。

--创建函数,需要运行后才会存储,才能在对象里查看
create or replace function fun_a --右键函数名查看是否报错
return number --返回值类型,不用写长度
is --相当于declare
v_sum number:=0;
begin
  for i in 1..10 loop
    v_sum:=v_sum+i;
  end loop;
  return v_sum;
end;

--调用函数：查询或者代码块
select fun_a from dual;

begin
  dbms_output.put_line(fun_a);
end;

函数可以有参也可以无参，函数参数有入参(in 默认可不写)、出参(out)、出入参(in out)之分。有出参的函数只能用代码块调用,不能用sql调用，出参不能赋值。

出参一般用在想要的数据不止返回值的情况下：

create or replace function fun_a(p_empno varchar,p_v out varchar)
return varchar
is
begin
  select dname into p_v from dept right join 
  (select deptno from emp where empno=p_empno) s on s.deptno=dept.deptno;
  return 1;
  exception
    when others then
      return 0;
end;
declare
v_fhz varchar(200);
v_v varchar(200);
begin
  v_fhz:=fun_a('7788',v_v);
  dbms_output.put_line(v_fhz||'-'||v_v);
end;

当入参和出参一个数据类型,就可以使用出入参。出入参兼具入参和出参特性。

存储过程

存储过程可以有返回值,也可以没有，和函数一样要先运行保存在procedures里再调用,也可选中名字查询是否报错，存储过程只能通过代码块或call调用,不能用select调用。

create or replace procedure sp_a
is
begin
  dbms_output.put_line('阿珍爱上阿强');
end;
--代码块调用不用(),call调用需要()
begin
  sp_a;
end;
call sp_a();

存储过程的参数和函数一样有入参、出参、出入参之分，使用也和函数里的一样。

函数和存储过程的区别：

定义的关键字不同，一个是function一个是procedure；
函数必须要有一个return，存储过程不需要；
函数可以通过select语句和代码块调用；存储过程只能通过代码块或call调用，不能使用select调用

数据同步

数据同步分为全量同步和增量同步。

全量同步:使用源表直接覆盖掉目标表
增量同步:先判断源表数据是否在目标表中,有就更新没有就插入

全量同步

同步时要考虑源表数据的可靠性和避免同步重复数据

create or replace procedure sp_a(p_deptno number)
is
v_c number:=0;
begin
  select count(*) into v_c from emp1 where deptno=p_deptno;--检查源表数据
  if v_c=0 then
    dbms_output.put_line('数据源为空');
  else
    delete from emp2 where deptno=p_deptno;--去重，使用delete或truncate
    insert into emp2 select * from emp1 where deptno=p_deptno;
    --commit;
  end if;
  exception
    when others then
      rollback;
      dbms_output.put_line(sqlerrm);
end;

增量同步

1.merge into

merge into 目标表
using (数据源中需要做数据同步数据的结果集)
on(匹配字段)
when matched then
update set --update 与 set 之间不写表名
when not matched then
insert values ; --insert 与 values 之间不写into 表名

merge into 语法下的update和insert语句都有省略，update时不能set匹配字段(会报错)

2.游标

游标通过逻辑实现，比较通用

--把数据源中数据同步到目标表中
create or replace procedure sp_emp1
is
--游标中存放数据源需要做数据同步的数据
cursor c_emp
is
select * from emp1;
v_count number;
begin
  --循环游标相当于循环数据源需要做数据同步的数据
  for i in c_emp loop
    --查询本次循环的数据是否在目标表中存在
    select count(*) into v_count from emp2 where empno=i.empno;
    --等于0就证明目标表中没有本次循环的员工信息
    if v_count=0 then
      insert into emp2 values(i.empno,i.ename,i.job,i.mgr,i.hiredate,i.sal,i.comm,
i.deptno);
    else
      update emp2 e2 set e2.ename  = i.ename,
                         e2.job    = i.job,
                         e2.mgr    = i.mgr,
                         e2.hiredate=i.hiredate,
                         e2.sal    = i.sal,
                         e2.comm   = i.comm,
                         e2.deptno = i.deptno
                   where e2.empno  = i.empno;
    end if;
  end loop;
  --commit;
  exception
    when others then
      rollback;
      dbms_output.put_line(sqlerrm);
end;

序列

序列是oracle提供的一个产生唯一数值型值的机制，通常用于主键，能保证唯一，不保证联系

--新建序列，CREATE SEQUENCE 后面加上新建的序列名
CREATE SEQUENCE LOG_SEQUENCE
--序列号从一开始
START WITH   1
--每个相隔为1，数据格式为1，2，3，4...
INCREMENT BY 5
--最大值为10
MAXVALUE     25
--循环CYCLE，不循环NOCYCLE，
--如果设置不循环的话当达到最大值时会报错
--如果设置循环的话当达到最大值时会重头来
CYCLE
--缓存几条数据
CACHE        5;

序列循环时,必须要设置缓存,且要大于1
序列缓存值要小于等于cycle值((最大值-最小值+1)/间隔)
序列初始值仅在第一次生效,循环后不遵循初始值
序列默认的开始值、间隔是1，最大值无限、不循环
序列必须先生成才能查询；过程调用序列时，初始值为2(Oracle优化才不报错)，如果想从1开始必须删除后重新调用

CREATE SEQUENCE LOG_SEQUENCE；最常使用，创建一个以1开头间隔1的无限不循环序列

-- 查看序列的下一个值
select 序列名.nextval from dual
--查看序列当前值
select 序列名.currval from dual
--删除序列
drop sequence 序列名;

拉链表

拉链表：记录数据的生命周期和缓慢变化，拉链表一般包括源表所有字段和状态、时间字段

数据变化时把改变的数据插入到拉链表中
1.源表和拉链表对比,插入修改了的数据

insert into emp1_ll
select e.*,sysdate,to_date('99991231','YYYYMMDD') from emp1 e where not exists
(select * from emp1_ll l where l.empno=e.empno and l.ename=e.ename and l.sal=e.sal);

2.将拉链表中老数据改为失效状态

update emp1_ll l set jssj=sysdate where not exists
(select * from emp1 e where l.empno=e.empno and l.ename=e.ename and l.sal=e.sal)
and jssj=to_date('99991231','YYYYMMDD');

--使用游标修改拉链表
create or replace procedure sp_a
is
v_c number;
cursor c_emp
is
select e.* from emp1 e where not exists
(select * from emp1_ll l 
where l.empno=e.empno and l.ename=e.ename and l.sal=e.sal);
begin
  for i in c_emp loop
    select count(*) into v_c from emp1_ll where empno=i.empno;
    if v_c!=0 then
      update emp1_ll set jssj=sysdate 
where empno=i.empno and jssj=to_date('99991231','YYYYMMDD');
      insert into emp1_ll values 
(i.empno,i.ename,i.sal,sysdate,to_date('99991231','YYYYMMDD'));
    else 
      insert into emp1_ll values 
(i.empno,i.ename,i.sal,sysdate,to_date('99991231','YYYYMMDD'));
    end if;
  end loop;
  commit;
  sp_log_table('sp_a','',sysdate,'成功','');
  exception
    when others then
      rollback;
      sp_log_table('sp_a','',sysdate,'失败',substr(sqlerrm,1,500));
end;

拉链表问题：断链、重复

使用偏移函数向上偏移一位，计算前一个的结束时间和下一个的开始时间的差值，如果大于0，则出现了重复情况；如果小于0，则出现了断链情况；如果等于0，说明正常。

包

包就是把相关的存储过程、函数、变量、常量和游标等PL/SQL程序组合在一起，并赋予一定的管理功能的程序块。一个程序包由两部分组成：包定义和包体。其中包定义部分声明包内数据类型、变量、常量、游标、子程序和函数等元素，这些元素为包的共有元素，包体则定义了包定义部分的具体实现。一个包分为包头和包体。

--创建包定义：
CREATE [OR REPLACE] PACKAGE 包名
IS/AS
  [公有数据类型定义]
  [公有游标声明]
  [公有变量、常量声明]
  [公有子程序声明]
END;
      
--创建包体:
CREATE [OR REPLACE] PACKAGE BODY 包名
IS/AS
-- [私有数据类型定义]
-- [公有游标声明]
-- [私有变量、常量声明]
-- [私有子程序声明]
BEGIN
  PL/SQL语句;
END;

CREATE OR REPLACE PACKAGE DEMO_PACK
IS
--定义一个公有变量
DEPTREC DEPT%ROWTYPE;
--定义一个函数,返回值类型为数字,有一个入参
FUNCTION ADD_DEPT(DEPT_NO NUMBER)
  RETURN NUMBER;
--定义一个存储过程,有一个入参
PROCEDURE QUERY_DEPT(DEPT_NO NUMBER);
END;    
--创建包体：
--创建或者覆盖一个包体
CREATE OR REPLACE PACKAGE BODY DEMO_PACK
IS
--开始给包头中ADD_DEPT函数写逻辑
FUNCTION ADD_DEPT(DEPT_NO NUMBER)
  RETURN NUMBER
IS
BEGIN
逻辑
END ADD_DEPT;   
--给存储过程写逻辑
PROCEDURE QUERY_DEPT(DEPT_NO NUMBER)
IS
BEGIN
逻辑
END QUERY_DEPT;
--给包体写逻辑
BEGIN
  DBMS_OUTPUT.PUT_LINE('包执行');
END DEMO_PACK;

包调用：包名打点调用要使用的程序

正则表达式

语法：

^：在[]外面表示匹配一个字符串的开始，在[]里面表示非
$：匹配字符串的结尾
[]：用于指定一个匹配列表
+ ：匹配一个或多个出现
* ：匹配0个或多个出现
{M}：匹配m次。
{M，}：至少匹配m次。
{M，N}：至少匹配m次，但不多于n次。

Oracle正则表达式函数：

REGEXP_SUBSTR 正则截取
REGEXP_REPLACE 正则替换
REGEXP_INSTR 或者正则的位置
REGEXP_LIKE 正则模糊查询(重点)

/*常用正则表达式
正整数：^\d+$
负整数：^-\d+$
电话号码：^+?[\d\s]{3,}$
电话代码：^+?[\d\s]+(?[\d\s]{10,}$
整数：^-?\d+$
用户名：^[\w\d_.]{4,16}$
字母数字字符：^[a-zA-Z0-9]*$
带空格的字母数字字符：^[a-zA-Z0-9 ]*$
密码：^(?=^.{6,}$)((?=.*[A-Za-z0-9])(?=.*[A-Z])(?=.*[a-z]))^.*$
电子邮件：^([a-zA-Z0-9._%-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,4})*$
IPv4 地址：^((?:(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(?:25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?))*$
小写字母：^([a-z])*$
大写字母：^([A-Z])*$
网址：^(((http|https|ftp):\/\/)?([[a-zA-Z0-9]\-\.])+(\.)([[a-zA-Z0-9]]){2,4}([[a-zA-Z0-9]\/+=%&_\.~?\-]*))*$
VISA 信用卡号码：^(4[0-9]{12}(?:[0-9]{3})?)*$
日期（MM/DD/YYYY）：^(0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01])[- /.](19|20)?[0-9]{2}$
日期（YYYY/MM/DD）：^(19|20)?[0-9]{2}[- /.](0?[1-9]|1[012])[- /.](0?[1-9]|[12][0-9]|3[01])$
万事达信用卡号码：^(5[1-5][0-9]{14})*$   */

表分区

分区表就是将一个表分成多个区，可以分别对不同的区进行操作，不同的区也可以保存在不同的位置，但实际还是一张表。

范围分区

create table empp
partition by range(sal)
(partition a values less than (1500),
partition b values less than (3000),
partition c values less than (6000))
as select * from emp;

select * from empp partition (c);
drop table empp;

列表分区

create table empp
partition by list(deptno)
(partition a values (10),
partition b values (20),
partition c values (30))
as select * from emp;

select * from empp partition (b);
drop table empp;

组合分区

create table empp
partition by range(sal)
subpartition by list(deptno)
(partition a values less than (2000)
(subpartition a1 values (10),
subpartition a2 values (20),
subpartition a3 values (30)),
partition b values less than (maxvalue)
(subpartition b1 values (10),
subpartition b2 values (20),
subpartition b3 values (30)))
as select * from emp;

select * from empp partition(a);
select * from empp subpartition(a1);

删除分区

drop from empp;
alter table empp drop partition (a);
alter table empp drop subpartition (a1);

添加分区

--添加分区
--只能添加已存分区规则之外的分区
/*ALTER TABLE 表名 ADD PARTITION 分区名 VALUES LESS THAN(上限值);*/
alter table empp add partition d values less than 6000;
alter table empp add partition d values (40);

其他操作

--截断分区
/*ALTER TABLE 表名 TRUNCATE PARTITION 分区名;
ALTER TABLE 表名 TRUNCATE SUBPARTITION 子分区名;*/
alter table empp truncate partition ;
alter table empp truncate subpartition ;
ALTER TABLE EMP_RANGE3 TRUNCATE PARTITION RANGE_1981;
ALTER TABLE EMP_RANGE_LIST3 TRUNCATE SUBPARTITION P1A;

--合并分区
/*ALTER TABLE 表名 MERGE PARTITIONS 分区名1,分区名2 INTO PARTITION 新分区名;*/
alter table empp merge partitions c,d into partition newc;

--拆分分区,只能拆分范围分区,不能拆分列表分区
/*ALTER TABLE 表名 SPLIT PARTITION 分区名 AT (分割值) 
INTO (PARTITION 新分区名1,PARTITION 新分区名2);*/
alter table empp split partition c at(5000) into (partition c, partition d);
select * from empp partition (d);

--重命名分区
/*ALTER TABLE 表名 RENAME PARTITION 原分区名 TO 新分区名;*/
alter table empp rename partition bb to b;

哈希分区

--哈希分区
--根据计算的哈希值分区
create table empp2(n number)
partition by hash(n)
(partition a,
partition b,
partition c);

begin 
  for i in 1..10 loop
    insert into empp2 values (i);
  end loop;
  commit;
  exception
    when others then
      rollback;
end;
select * from empp2;
select * from empp2 partition (c);

间隔分区

--自增分区
--天
create table test_3(name varchar2(20),sal number,hiredate date)
partition by range(hiredate)
interval(numtodsinterval(7,'day')) --[1,30)天
(partition a values less than(to_date('20220202','yyyymmdd')));
--月
drop table test_3;
create table test_3(name varchar2(20),sal number,hiredate date)
partition by range(hiredate)
interval(numtoyminterval(1,'month') ) --[1,12] 月
(partition a values less than(to_date('20220202','yyyymmdd')));

分区表索引

Oracle提供了两种主要的分区索引类型：本地索引和全局索引。

本地索引：

定义：
- 本地索引与分区表紧密相关，其索引分区与表分区一一对应。
- 本地索引可以是B树索引或位图索引。
特点：
- 自动维护：在添加、删除、分割或截断表分区时，本地索引的相应分区也会自动进行相应的维护操作，不需要用户手动干预。
- 分区独立性：对于单独的分区增加、删除、分割等操作，本地索引可以独立处理，无需同时删除或重建整个索引。
- 应用场景：本地索引多应用于数据仓库环境（DSS），适用于用户对吞吐量更感兴趣的情况1234。
子类型：
- 本地前缀分区索引：索引列包含分区键。
- 本地非前缀分区索引：索引列不包含分区键，但仅支持B树索引12。

全局索引：

定义：
- 全局索引的分区与表分区不一定一一对应，它可以跨多个表分区。
- 全局索引在创建时可以指定为分区索引或非分区索引，但全局分区索引必须是前缀索引。
特点：
- 手动维护：在添加、删除、分割或截断表分区时，全局索引不会自动进行维护，可能导致索引失效，除非指定了UPDATE GLOBAL INDEXES选项。
- 灵活性：全局索引的分区可以根据需要自定义，不依赖于表的分区结构。
- 应用场景：全局索引多应用于OLTP系统，适用于需要快速响应时间的应用场景1234。
子类型：
- 全局非分区索引：索引本身不进行分区。
- 全局分区索引（全局前缀分区索引）：索引被分区，且索引列包含分区键

表分区列与索引键的关系：
- 如果表分区列是索引键的子集，则使用本地索引。
- 如果索引是唯一索引且不包括分区键列，则使用全局索引。
应用程序类型：
- 如果应用程序是OLTP类型且需要快速响应时间，则使用全局索引。
- 如果应用程序是DSS类型且对吞吐量更感兴趣，则使用本地索引。

索引

索引是建立在数据库表中的某些列上面，是与表关联的，可提供快速访问数据的方式，但会影响增删改的效率。

数据库中索引(index)的概念与目录的概念非常类似。如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，oracle会比较全表扫描与索引扫描的代价，如果索引扫描代价小，那oracle会自动引用该索引，先从索引表中查询出符合条件记录的ROWID,由于ROWID是记录的物理地址，因此可以根据ROWID快速定位到具体的记录，表中的数据非常多时，引用索引带来的查询效率非常可观。

什么时候应该用索引：
1.如果表中的某些字段经常被查询，并且作为查询条件出现，可以考虑为该列创建索引。
2.经常作为关联条件的字段，也可以考虑为这些字段创建索引
基本原则：当任何单个查询(sql)要查询的行数少于或等于整个表行数的10%时，索引非常有用。

索引种类

B树索引
唯一索引：主键索引是一种特殊的唯一索引
位图索引
函数索引
空间索引
全文索引
Hash索引
XML索引
LOB索引
分区索引
聚集索引

创建索引

create [unique] index 索引名 on 表名(字段名 [字数是升序还是降序]);

create [unique] index 索引名 on 表名(字段名，字段名) --组合索引

更新索引

alter index 索引名 rebuild

删除索引

drop index 索引名

索引失效情况

隐式转换会导致索引失效(开发中常犯的错误)
对索引列进行运算会导致索引失效(+ - * / != > <)
NOT IN,NOT EXISTS会导致索引失效(not in有回表操作，底层就与索引冲突)
组合索引的时候，单独引用索引中非第一位置的索引，会导致索引失效。
使用orcale函数也会导致索引失效，此时应创建基于函数的索引。

索引不是越多越好

存储开销：索引本身需要占用存储空间，而且是随着数据量的增加而增加的。如果表的数据量非常大，那么索引可能会占用大量的磁盘空间。
插入、删除和更新的开销：在表中插入、删除或更新数据时，索引也需要进行相应的维护，这可能会增加这些操作的开销。特别是当插入、删除或更新的数据量很大时，这种开销可能会更加明显。
可能降低查询性能：虽然索引可以提高查询性能，但在某些情况下，它可能会降低性能。例如，如果查询没有使用索引，或者查询条件不能充分利用索引，那么查询可能需要扫描整个表，这时索引不仅不能提高性能，反而可能成为负担。

约束

表的约束是oracle数据库中应用在表数据上的一系列强制性规则。当向已创建约束的表中插入数据或修改表中的数据时，必须满足表的完整性约束所规定的条件。
按照约束用途分类：

PRIMARY KEY：主键约束：自动添加索引，数据不可以重复,
FOREIGN KEY：外键约束：使用另外一张表的主键来现在本表的外键列的数据
CHECK：检查约束:必须满足定义的条件，可以为空
UNIQUE：唯一约束:自动添加索引，数据可以重复
NOT NULL：非空约束：不能为空

添加约束

ALTER TABLE 表名 ADD CONSTRAINT 约束名约束内容

删除约束

ALTER TABLE 表名 DROP CONSTRAINT 约束名

建表时添加约束

CREATE TABLE STUDENTS(
SNO NUMBER PRIMARY KEY,
SNAME VARCHAR2(50) NOT NULL,
SEX VARCHAR2(4) CHECK(SEX='男' OR SEX='女'),
PNUMBER NUMBER UNIQUE);

锁

死锁问题

它发生在两个或多个事务在执行过程中因争夺资源而造成的一种相互等待的现象，若无外力作用，这些事务都将无法向前推进。

解决方案：

避免循环依赖：通过合理设计数据库表结构和索引，避免事务在执行过程中形成循环依赖的关系。例如，对于经常并发操作的表，可以考虑进行拆分或分区，以减少锁的竞争。
缩短事务执行时间：尽可能缩短事务的执行时间，减少事务持有锁的时间，从而降低产生死锁的可能性。可以通过将复杂事务分解为多个简单事务来执行，或者使用存储过程来优化事务逻辑。
减少锁的数量和持续时间：通过优化查询语句，减少事务在执行时需要获取的锁的数量和持续时间。例如，尽量避免对大表的全表扫描，使用合适的索引来加快查询速度，从而减少锁的需求。
降低隔离级别：将事务隔离级别设置为较低的级别可以减少死锁的发生。例如，在MySQL中，将隔离级别设置为“READ COMMITTED”可以减少锁定持续时间，从而降低死锁的风险。但需要注意的是，降低隔离级别可能会增加脏读、不可重复读和幻读等问题。
及时发现和处理：使用数据库提供的监控工具和诊断方法可以及时发现和处理死锁问题。例如，在MySQL中，可以使用SHOW ENGINE INNODB STATUS命令查看当前InnoDB引擎的状态信息，包括死锁信息。此外，还可以查看错误日志或使用第三方监控工具来辅助诊断。
设置超时时间：设置合适的超时时间可以避免长时间等待导致的死锁。例如，在MySQL中，可以设置innodb_lock_wait_timeout参数来控制事务等待锁的最长时间。当等待时间超过设定的阈值时，事务将自动回滚，从而避免死锁的发生。

触发器

当某个条件成立时就执行触发器里面的语句,触发器不可以被人为调用。语句级触发器:可以在语句执行前或者后进行触发，行级触发器:在使用行级触发器时,执行触发器时可以获取到本次执行的数据。

两种触发器区别在于：有无for each row

--在emp1表上创建一个触发器实现星期一不可以更改emp1表
create or replace trigger tri_a
before insert or update or delete
on emp1
begin
  if to_char(sysdate,'day')='星期一' then
    --自定义报错,报错编号范围:20000-20999
    raise_application_error(-20001,'星期一不可以更改emp1表');
  end if;
end;

create or replace trigger tri_a
before update
on emp1
for each row --行级触发器
begin
  if :new.sal<:old.sal or :new.sal>:old.sal*1.3 then
    raise_application_error(-20001,'涨工资不能低于原工资,涨薪幅度不能高于30%');
  end if;
end;

连接方式

排序合并连接(sort merge join,SMJ)

对于排序合并连接，排序的开销特别大，但一旦排序完成，合并就会特别快
针对排序合并连接，两个表直接的联结字段建议由索引，这样性能会更好
针对非等值连接，这种连接方式的效率比较高(数据量较小)
对于两个比较大的表做连接，排序合并连接(SMJ)的性能比循环嵌套(NL)的性能好。

循环嵌套连接(nested loop,nl)
驱动表：最先加载的表，一般会进行全表扫描
被驱动的表：数据量较大的表，如果数据量过大，则会放入磁盘中，每次读取都会产生磁盘I/O

建议小表关联大表的时候采用，小表作为驱动表，会放入内存中，作为外层循环，大表作为被驱动的表，作为内层循环
循环嵌套(NL)有一个其他连接方式都没有的优点：可以先返回已连接的行，不等所有连接完成才返回数据，可以实现快速响应。

哈希连接(hash join,hj)

一般来说哈希连接的性能会优于其他两种连接方式，但是这种连接方式只能用在CBO优化器中，同时需要设置合适的hash_area_size参数，才能得到较好的性能
两个较大的表之间的连接，会取得较好的效率
只能用在等值连接中

表空间

表空间是数据库中最大的逻辑单位，一个Oracle中至少有一个表空间，varchar2是oracle独有的.每个数据库中都有一个名为system的表空间，即系统表空间，用于存放数据库字典表、程序单元、过程、函数包和触发器等。一个数据库文件只能与一个表空间相联系，每个表空间由一个或多个数据文件组成。

-- 创建表空间
create tablespace 表空间名
-- 删除表空间
drop tablespace 表空间名
-- 修改表空间
alter tablespace 表空间名 add datafile '文件路径' size 大小

视图

视图(View)实际上是一张或者多张表上的预定义查询，这些表称为基表，从视图中查询信息与从表中查询信息的方法完全相同。只需要SELECT FROM即可。

视图优点：

可以限制用户只能通过视图检索数据，这样就可以对最终用户屏蔽建表时底层的基表，具有安全性。
可以将复杂的查询保存为视图，屏蔽复杂性。
视图可以独立于底层表结构。即使底层表结构发生变化，只要视图的定义不变，依赖于视图的查询和应用程序通常不需要做出修改，促进了数据独立性
视图可以根据不同用户的需求和角色设置不同的过滤条件和访问控制，使权限管理更加灵活和精确，简化权限管理

管理员账户授予创建视图权限

grant create view to scott;

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW VIEW_NAME
AS
SELECT语句
[WITH READ ONLY]

FORCE：即使基表不存在，也可以创建视图，但该视图不能正常使用，当基表创建成功后，视图才能正常使用。

WITH READ ONLY：说明视图是只读视图，不能通过该视图进行增删改操作

视图可以嵌套视图,视图中使用函数要起别名

-- 查询视图
select * from 视图名
-- 视图中插入值，插入的值是放到基表中，视图是虚拟表，并不存在数据
insert into 视图名(字段名) values(插入的值)
-- 更新视图值
update 视图名 set 字段名=字段值 where 条件
-- 删除
elete from 视图名 where 条件
-- 删除一个视图
drop view 视图名

物化视图

是预先计算的查询结果的物理存储，允许快速访问数据，特别是在复杂查询和聚合操作中。它们存储查询的结果集，并且可以定期刷新以保持数据的最新状态.物化视图可以显著提高查询性能，特别是当涉及到大数据量或复杂计算时。在Oracle数据库中，物化视图通常用于优化数据仓库和报表应用程序的性能。

管理员账户授予创建物化视图权限

grant create materialized view to scott

--创建雾化视图,不生成数据,在提交时进行更新
create materialized view v_m_dept
build deferred    --只创建视图不生产数据
refresh complete  --全量更新
on commit         --在提交时触发全量更新
as
select * from dept;
--向dept表插入数据
insert into dept values(1,'a','b');
commit;
select * from dept;
--查询视图(提交并没有触发全量更新,原因:雾化视图第一次更新数据必须为手动刷新)
select * from v_m_dept;
--手动刷新视图
begin
  dbms_mview.refresh('v_m_dept');
end;

--定时刷新
--1.创建一个定时刷新的视图
create materialized view v_m_dept_2
build immediate   --按照时间生成数据    
refresh complete  --全量更新
start with sysdate next sysdate+1/24/60 /*下次执行时间为1分钟以后*/
as
select * from dept;

物化视图和普通视图区别：

数据存储方式：
- 普通视图：仅保存查询的定义，不保存实际数据。每次查询视图时，都会动态计算结果集。
- 物化视图：保存查询结果的实际数据集。数据在物化视图中被预先计算和存储，因此访问物化视图时不需要再次执行查询，可以直接从物化视图中获取数据。
性能影响：
- 普通视图：每次查询视图时都要重新计算结果，可能会消耗较多的计算资源和时间，特别是对于复杂查询。
- 物化视图：提前计算并存储了结果集，因此查询性能通常更高，特别是在复杂查询和聚合操作中，可以显著减少查询的执行时间和资源消耗。
用途和优化：
- 普通视图：用于封装复杂的查询逻辑，简化查询语句，但不提供性能优化。
- 物化视图：用于优化频繁查询的性能，特别是对于大数据量和复杂计算的场景，可以提高查询效率和响应时间。

SQL优化

SQL调优的核心：空间和时间平衡
1.常规语句优化：执行步骤的优化
2.索引的优化：空间换时间的优化
3.表结构的优化(分区)
4.优化器：oracle在执行SQL之前会生成执行计划，优化器会去选择一条系统最优的路线
(主要是从内存和CPU消耗作为判断)
RBO：基于规则优化
CBO：基于代价优化

常规语句的优化
1.少用SELECT * FROM
原因：SELECT *的时候，会先去查询数据字典的所有列，再去返回对应的字段。
建议：能列出具体字段的时候就列出具体的字段
2.经常COMMIT
在PL/SQL中，逻辑很长，回滚段和临时空间段中消耗了资源
建议：分段COMMIT
案例：一亿条数据，如何提交(面试宝典有)
3.慎用NOT IN，建议用NOT EXISTS
原因：NOT IN效率会更低
4.用TRUNCATE代替DELETE
原因：DELETE是逐行删除，性能会更低一些
5.表关联
驱动表：最先加载的表，一般会进行全表扫描
小表作为驱动表，小表JOIN大表，内存加载数据会更小些
6.谓词下推
能在WHERE条件过滤，就不要在HAVING过滤
错误示范：SELECT DEPTNO,SUM(SAL) FROM EMP GROUP BY DEPTNO HAVING DEPTNO>10;
正确示范：SELECT DEPTNO,SUM(SAL) FROM EMP WHERE DEPTNO>10 GROUP BY DEPTNO;

索引的优化(避免出现索引失效)
强制走索引
/*+INDEX(表名或表别名,索引名称)*/
不起别名的情况：
SELECT
/*+INDEX(EMP_1010,INDEX_DEPTNO)*/
* FROM EMP_1010 WHERE DEPTNO=20;
起别名的情况：
SELECT
/*+INDEX(E1,INDEX_DEPTNO)*/
* FROM EMP_1010 E1 WHERE E1.DEPTNO=20;

有时候索引扫描不一定比全表扫描快
走索引扫描实际是两步:1.先查索引。2.查数据
当需要找数据量的10%以下时，用索引比较快，但是达到50%时，索引不一定是快的。
10.5.3对数据去重GROUP BY效率比DISTINCT好

常用hinst(提示符)
1./*+INDEX(表名或表别名,索引名称)*/ --指定索引
2./*+full(表名)*/ --指定全表扫描
3./*+leading(表名1,表名2)*/ --指定1作为驱动表
4./*+use_merge(表名1,表名2)*/ --指定使用sort merge join排序合并关联机制
5./*+use_NL(表名1,表名2)*/ --指定使用循环嵌套关联nested loop关联
6./*+use_hash(表名1,表名2)*/ --指定使用hash哈希关联

执行计划

explain plan for 查询语句

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);