Oracle学习
命令：
1、数据定义语言DDL：CREATE、ALTER、DROP
2、数据操纵语言DML：INSERT、UPDATE、DELETE
3、数据查询语言DQL：包括基本查询语句、Order By子句、Group By子句等
4、事务控制语言TCL：COMMIT、ROLLBACK
5、数据控制语言DCL：GRANT（授权）命令、REVOKE（撤销）命令
集合运算：union all 不去重效率快(推荐)，其他去重且排序(不推荐)
交集：intersect
并集：union、union all
补集：minus
伪列：
rowid：用于删除重复数据，插入数据时生成，其值是物理地址，新插入的物理地址比旧插入的小，所以去重时用 min()聚合函数
rownum：用于分页，查询数据时生成，每次查询都是1....N。
1、rownum的值是变化的值，不能指定表直接写rownum，例SELECT E.AGE,ROWNUM FROM TABLENAME E，不能 E.ROWNUM
2、rownum只能查询小于或者等于，因为必须从1开始，所以需要查询中间行时需要用到子查询。
SELECT E.*,FROM (SELECT E.*,ROWNUM R FROM EMP E) A WHERE A.R >= 11
数字函数：
1、ABS(X)：绝对值
2、MOD(X,Y)：求余
3、POWER(X,Y)：X的Y次幂
4、TRUNC(X[,Y])：X在第Y处截断小数点，如果不写Y，则默认截断到整数
5、ROUND(X[,Y])：四舍五入
6、CEIL(X)：向上取整，支取到整数
7、FLOOR(X)：向下取整
字符函数：
1、ASCii(X)：ASCII码
2、CONCAT(X,Y)：拼接字符串，| | 字符串的另一种写法。
3、WM_CONCAT(X)：把几行数据合并到一行，值用逗号分隔；SELECT DEPTNO,WM_CONCAT(ENAME) FROM EMP GROUP BY DEPTNO
4、LENGTH(X)：长度
5、LOWER(X) / UPPER(X)：小写/大写
6、REPLACE(X,OLD,NEW)：将X中的OLD字符替换NEW
7、LTRIM(X[,TRIM_STRING])：从字符X的左边截去字符串 TRIM_STRING，如果不写则默认是截去空格
RTRIM(X[,TRIM_STRING])：右边截去
TRIM(TRIM_STRING FROM X)：俩边截取
8、INSTR(X,STR[,START,N])：字符STR在字符串X中从START开始取第N次出现的STR字符的索引；例 instr('xsafdea','a',-1,1)；-1可取倒数且没有匹配成功则返回0
9、SUBSTR(X,START[,LENGTH])：截取字符串，从字符串X中从start开始截取length个字符，length不写则默认取全部，start可取负数。
日期函数：
1、ADD_MONTHS(D,M)：在某个日期D上加上N个月份
2、LAST_DAY(D)：返回指定日期D的当月最后一天
3、与TRUNC搭配使用，截取日期：
SELECT SYSDATE,
TRUNC(SYSDATE), --截取到当天
TRUNC(SYSDATE,'D'), --截取到本周的第一天(星期天)
TRUNC(SYSDATE,'MM'), --截取到本月的第一天
TRUNC(SYSDATE,'Q'), --截取到本季度的第一天
TRUNC(SYSDATE,'Y') --截取到本年的第一天
FROM DUAL
4、日期可以直接加减运算，单位是天
空值转换函数：
1、NVL(字段，默认值)：将NULL转换为默认值
2、NVL2(字段，真，假)：类似if函数，区别是判断NULL
转换函数(数据类型转换函数)：
1、to_char(x[,FMT])：转换为FMT格式的字符串，可以不写
把时间换为字符：
to_char(sysdate,'YYYY') ：转换为年
to_char(sysdate,'YYYYMM') ：转换为年月份
to_char(sysdate,'Q') ：转换为第几季度
to_char(sysdate,'WW') ：转换为第几周
to_char(sysdate,'DDD') ：转换为当年第几天
to_char(sysdate,'DD') ：转换为当月第几天
to_char(sysdate,'D') ：转换为当周的第几天
to_char(sysdate,'HH:MM:SS') ：转换为小时分钟秒
2、to_number(x)：字符转数字，俩边有空格默认可以去掉，日期不可以直接转数字
3、to_date(x)：转日期，to_date('20200503 23:59:59','YYYYMMDD HH24:Mi:DD')，年份月份天必须合法，且如果是字符串转换格式也必须相对应，如果是数字则没有此要求；
to_date('20200102','YYYY/MM/DD')这样是对的，只要不包含其他字符则跟数字形式一样，不需要跟格式一一对应。
条件转换函数：
1、decode(值/字段,判断值1,返回值1,判断值2,返回值2,.....,默认值)，默认值可以不写，默认为null；不是所有数据库都支持此函数。
2、case when 条件1 then 返回值1
when 条件2 then 返回值2
.........
else 默认值 end
分析函数：解决复杂报表统计需求的功能强大的函数，每一组每一行都返回一个统计值
function_name(参数1,...) over (<partition by 表达式> <order by [asc | desc]>)
order by是组内排序，与外部order by独立
1、与聚合函数搭配使用：max、min、count、avg、sum
如果用了order by则是组内累计求值，不加partition by则是不分组
2、与排序函数搭配使用：row_number、rank、dense_rank；必须要有order by参数
row_number：相同的值谁先在前面则谁排前面
rank：相同的值排名相同；例 1,2,2,4，是不连续的
dense_rank：相同的值排名相同；例 1,2,2,3，是连续的
3、与位移函数搭配使用：lead/lag,lead:向上移动，lag：先下移动
lead / lag(x,y,z)：
x：列名
y：偏移的量，即数据向上或者向下移动几行
z：移动后空出来的值的填充值，可以不写，默认用null填充
表操作：
创建表(直接连表、间接连表)：
数字类型：
number：默认是38位长度且最大长度是38
number(8,2)：整数的最大长度是6，小数是2；整数超过报错，小数超过则四舍五入
字符类型：
char：默认长度是1，固定长度
char(N)：最大长度可以输入N位，不满N位，后面空格补齐，固定长度
varchar2(N)：最大长度可以输入N位，不满N位的，不会填充，可变长度
时间类型：
DATE：DATE是不可以有括号的
直接连表： CREATE TABLE T_STU ( STU_NAME VARCHAR2(20), STU_NUMBER NUMBER(10), STU_SEX CHAR(2), STU_BIRTHDAY DATE ); 间接连表：根据查询的结果集来创表 CREATE TABLE EMP_TEST AS SELECT * FROM EMP WHERE 1=2; --where是判断条件，符合条件的才插入数据，不符合条件的不插入，而1=2的条件是只复制表结构
表的约束：
1、主键约束：PRIMARY KEY ---- 非空唯一，只有一个主键
2、外键约束：FOREIGN KEY(外键字段) REFERENCE 主表名(主键) ---- 受外部表主键的约束
3、检查约束：CHECK(条件) ----- 字段只能出现空值或者符合条件的值
4、唯一约束：UNIQUE ------ 值唯一可为空，可以有多个唯一约束
5、非空约束：NOT NULL ------不能出现空值
表结构修改：
1、修改表名字：ALTER TANLE TABLE_NAME RENAME TO NEW_TABLE_NAME;
2、修改字段名字：ALTER TABLE TABLE_NAME RENAME COLUMN OLD_NAME TO NEW_NAME
3、字段数据类型修改：ALTER TABLE TABLE_NAME MODIFY 字段定义 ----- 表有数据则会修改失败
4、增加字段：ALTER TABLE TABLE_NAME ADD 字段定义
5、删除字段：ALTER TABLE TABLE_NAME DROP COLUMN NAME
6、删除表：DROP TABLE TABLE_NAME
7、添加约束：ALTER TABLE TABLE_NAME ADD CONSTRAINT 约束名 约束定义 -------约束名最好自己定义
8、删除约束：ALTER TABLE TABLE_NAME DROP CONSTRAINT 约束名
CREATE TABLE T_STU( T_NUM NUMBER(10) PRIMARY KEY, --主键 CLASS_NUM NUMBER(4) REFERENCES DEPT(DEPTNO),--外键 T_SCORE NUMBER(3) NOT NULL, TEL NUMBER(11) UNIQUE, T_SEX CHAR(2) CHECK(T_SEX='M' OR T_SEX='F') )
表数据操作：
INSERT：INSERT INTO TABLE_NAME(字段名1,字段名2....字段名N) VALUES(值1,值2,......值N) ------ 不能插入多行
INSERT ALL / FIRST：可以同时插入多张表，还可以根据判断条件来决定每条记录插入到哪张或哪几张表中
INSERT FIRST：对于每一行数据，只插入到第一个when条件成立的表，不继续检查其他条件。
INSERT ALL：对于每一行数据，对每一个when条件都进行检查，如果满足条件就执行插入操作。
INSERT ALL INTO EMP1 INTO EMP3 SELECT * FROM EMP INSERT ALL WHEN DEPTNO<20 THEN INTO EMP1 WHEN DEPTNO<40 THEN INTO EMP3 SELECT * FROM EMP; INSERT FIRST WHEN DEPTNO<20 THEN INTO EMP1 WHEN DEPTNO<40 THEN INTO EMP3 SELECT * FROM EMP; -- 无条件插入 insert all into sales_info values(employee_id,week_id,sales_mon) into sales_info values(employee_id,week_id,sales_tue) into sales_info values(employee_id,week_id,sales_wed) into sales_info values(employee_id,week_id,sales_thur) into sales_info values(employee_id,week_id,sales_fri) select employee_id,week_id,sales_mon,sales_tue, sales_wed,sales_thur,sales_fri from sales_source_data; commit;
UPDATE：UPDATE TABLE_NAME SET ....... WHERE 条件
DELETE：DELETE FROM TABLE_NAME WHERE 条件
执行过程中输入数据：
SELECT * FROM EMP WHERE DEPYNO = &输入部门号码 AND JOB = &输入职位
WITH AS：相当于建一个临时表，但是不是实际存在的物理表，只是存在内存中；其实就是把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了。
针对一个别名：WITH TMP AS (SELECT * FROM EMP);
针对多个表：
WITH
TMP1 AS (SELECT * FROM NAME1),
TMP2 AS (SELECT * FROM NAME2),
TMP3 AS (SELECT * FROM NAME3),
.........;
PL/SQL：结构化查询语句与Oracle自身过程控制为一体的强大的语言
1、声明部分
2、执行部分
3、异常处理部分
[ DECLARE -- 声明部分 DECLARATION STATEMENTS ] -- 声明变量，常量CONSTANT，游标；可以声明变量的同时赋值 BEGIN -- 执行部分 -- EXECUTABLE STATEMENYS 执行部分，不能声明变量 [ EXCEPTION -- 异常处理部分，可以不写 -- EXCEPTION STATEMENTS] END; -- 必须写分号，每一条SQL语句都必须写分号
特殊符号：
1、赋值： :=
2、连续值： 1 .. N
3、求幂： X ** Y
4、DBMS_OUTPUT.put_line()：输出到output界面
DECLARE V_SQL VARCHAR(10) := 'HELLO WORLD' BEGIN DBMS_OUTPUT.put_line(V_SQL ) END;
声明属性数据类型：
1、%TYPE：引用表中某个字段的数据类型
所引用的数据库列的数据类型可以不必知道
所引用的数据库列的数据类型可以实时改变
2、%ROWTYPE：引用表中某一行(所有字段)的数据类型
所引用的数据库中列的个数和数据类型可以不必知道
所引用的数据库中列的个数和数据类型可以实时改变
隐式游标：对变量进行赋值，从数据库中查询数据对变量进行赋值，但是查询的结果必须只有一行，不能是0或者多行。
SELECT 字段名 INTO 变量名 FROM ......：字段名和变量名的数量和数据类型必须匹配
declare v_emp_record emp_record; begin --通过 select ... into ... 语句为变量赋值 select last_name, email, salary, job_id into v_emp_record from employees where employee_id = 101; -- 打印变量的值 dbms_output.put_line(v_emp_record.v_name || ', ' || v_emp_record.v_email || ', ' || v_emp_record.v_salary || ', ' || v_emp_record.v_job_id); declare --声明一个记录类型的变量 v_emp_record employees%rowtype; begin --通过 select ... into ... 语句为变量赋值 select * into v_emp_record from employees where employee_id = 101; -- 打印变量的值 dbms_output.put_line(v_emp_record.last_name || ', ' || v_emp_record.email || ', ' || v_emp_record.salary || ', ' || v_emp_record.job_id || ', ' || v_emp_record.hire_date);
循环：LOOP,WHILE,FOR
1、LOOP：无条件循环，必须加退出条件
LOOP
-- 循环体
-- EXIT [WHEN 条件]; -- 不加条件则直接退出
END LOOP;
2、WHILE：
WHILE 条件 LOOP
-- 循环体
END LOOP;
3、FOR：变量名无需定义，但是只能在循环体内使用，REVERSE是倒叙，N <= M
FOR 变量名 IN [REVERSE] N .. M LOOP
-- 循环体
END LOOP;
条件判断：
IF 条件 THEN
SQL语句;
ELSIF 条件 THEN
SQL语句;
ELSE
SQL语句;
END IF;
显式游标：指向查询结果集的有个指针，通过游标可以将查询结果集中的记录逐一取出并在PL/SQL中处理
CURSOR 游标名 IS 查询语句; 游标名一般以 C_NAME 形式。
一般写法： DECLARE CURSOR C_EMP IS SELECT * FROM EMP; BEGIN OPEN C_EMP; LOOP FETCH C_EMP INTO 变量名; EXIT WHEN C_EMP%NOTFOUND; END LOOP; CLOSE C_EMP; END; ---------------------- %FOUND：用于判断游标是否从结果集中提取数据，如果取到数据则返回TRUE，否则返回FALSE； %NOTFOUND：以上相反 FOR 循环写法(推荐)： FOR I IN 游标名 LOOP DBMS_OUTPUT.put_lins(X.字段); END LOOP;
带参数的游标：
DECLARE CIRSOR C_EMP(P_DEPYNO EMP.DEPTNO%TYPE) IS SELECT ENAME,SAL FROM EMP WHERE DEPTNO = P_DEPTNO; BEGIN FOR I IN C_EMP(20) LOOP DBMS_OUTPUT.put_lins(I.ENAME || '---' || I.SAL); END LOOP; END;
动态SQL语句：数据定义和查询语句不能在PL/SQL中执行，增删改可以执行。
EXECUTE IMMEDIATE 动态语句字符串 INTO 变量列表 [USING 参数列表];
动态语句字符串：将要执行的SQL语句
变量列表：如果执行的动态语句有返回值则可以直接INTO到某个变量
参数列表：动态SQL语句中的参数可以通过USING来调用，表名例外
------可以正确执行 DECLARE V_NUM EMP.EMPNO%TYPE := &输入一个员工编号; V_SAL NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT SAL FROM EMP WHERE EMPNO = :1' INTO V_SAL USING V_NUM; END; ------错误信息：无效的表名 DECLARE V_TABLENAME VARCAHR2(20) := 'EMP'; V_SAL NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT SAL FROM :1' INTO V_SAL USING V_TABLENAME ; END;
每一个游标遍历一张表，实现条件筛选 DECLARE CURSOR C_EMP IS SELECT * FROM EMP; CURSOR C_DEPT IS SELECT * FROM DEPT; BEGIN FOR I IN C_DEPT LOOP FOR J IN C_EMP LOOP IF I.DEPTNO = J.DEPTNO THEN DBMS_OUTPUT.put_line(I.DNAME || '---' || J.ENAME); END IF; END LOOP; END LOOP; END;
游标的更新和删除数据：（也可以用ROWID来标示位置）
FOR UPDATE：可以使用行锁锁定你需要改的记录，当遇到下一个commit和rollback语句时会被释放
WHERE CURRENT OF 游标名：标示出游标当前的位置。
------ 用游标更新数据时，更新完后表中的数据不是实时更新的，分为OLD，NEW数据，即1和2打印出来的是一样的 DECLARE CURSOR C_EMP IS SELECT EMPNO,SAL FROM EMP FOR UPDATE; BEGIN FOR I IN C_EMP LOOP DBMS_OUTPUT.PUT_LINE(I.SAL); ------ 1 UPDATE EMP SET SAL = SAL * 1.1 WHERE CURRENT OF C_EMP; DBMS_OUTPUT.PUT_LINE(I.SAL); --------2 END LOOP; END;
存储过程：
CREATE [OR REPLACE] PROCEDURE 过程名(参数1 [IN | OUT | IN OUT] 数据类型)
IS | AS
PL/SQL结构体;
1、OR REPLACE：如果存在此存储过程则替换覆盖
2、IN：表示传入参数，不可以被赋值
OUT：传出参数，可以被赋值
IN OUT：传入传出参数
3、不写默认为IN参数；数据类型不能写长度，例如NUMBER不能写长度
命名规范：
1、存储过程名：SP_目标表名
2、存储过程传入参数名：P_参数名
3、存储过程变量命名规范：V_变量名
CTEATE OR REPLACE PROCEDURE SP_EMP(P_SAL IN OUT NUMBER,P_JOB IN OUT NUMBER) AS BEGIN SELECT ENAME,SAL INTO P_JOB,P_SAL FROM EMP WHERE DEPTNO = P_SAL AND JOB = P_JOB ; EXEPTION WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUTLINE('返回多行数据'); END;
默认值的参数顺序：
------- 带默认值的存储过程 CREATE PROCEDURE SP_1(P_NAEM IN NUMBER := 2,P_SAL IN NUMBER) AS BEGIN .......; END; -------- 不按顺序的传参方式 BEGIN SP_1(P_SAL => V_NUMBER); END;
异常处理：
RAISE：手动发起异常
--自定义异常处理 CREATE OR REPLACE PROCEDURE stu_proc ( v_id IN NUMBER ) IS v_max_id NUMBER; v_name VARCHAR2(20); v_raise EXCEPTION; -- 定义异常类型 BEGIN SELECT MAX(a.id) INTO v_max_id FROM student a; IF v_id>v_max_id THEN RAISE v_raise; END IF; SELECT o.sname INTO v_name FROM student o WHERE o.id=v_id; dbms_output.put_line('学生名称为：'||v_name); EXCEPTION WHEN v_raise THEN -- 捕捉异常 RAISE_APPLICATION_ERROR(-20010, 'v_id not exists!'); WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20011, 'ERROR：不存在！');
WHEN OTHERS THEN -- 放在最后
RAISE_APPLICATION_ERROR(-20012，'ERROR'); END stu_proc;
动态游标：与游标一样，动态游标（游标变量）也是一个指向多行查询结果集合中当前数据行的指针。但与游标不同的是，游标变量是动态的，而游标是静态的。游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连），只要这些查询语句的返回类型兼容即可。
返回游标：返回的游标不能用FOR循环遍历，用LOOP遍历；返回的游标不需要open游标，但是要关闭游标。
游标指向查询：open 游标名 for select .....;
--创建存储过程，返回SYS_REFCURSOR CREATE OR REPLACE PROCEDURE P_GET_AGENT(V_AGENT_CATE IN VARCHAR2, V_PAGE IN INTEGER, O_CURSOR OUT SYS_REFCURSOR) IS BEGIN INSERT INTO TMP_TAB_T_AGENT(AGENT_ID) SELECT AGENT_ID FROM T_AGENT TA WHERE TA.AGENT_CATE = V_AGENT_CATE; OPEN O_CURSOR FOR SELECT AGENT_ID FROM (SELECT ROWNUM AS RN, AGENT_ID FROM (SELECT AGENT_ID FROM TMP_TAB_T_AGENT ORDER BY AGENT_ID) WHERE ROWNUM < V_PAGE * 10) WHERE RN >= (V_PAGE - 1) * 10; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('wrong'); END; --调用存储过程， DECLARE V_CURSOR SYS_REFCURSOR; V_AGENT_ID T_AGENT.AGENT_ID%TYPE; BEGIN -- Call the procedure P_GET_AGENT(V_AGENT_CATE => '5', V_PAGE => 4, O_CURSOR => V_CURSOR); LOOP FETCH V_CURSOR INTO V_AGENT_ID; EXIT WHEN V_CURSOR%NOTFOUND; DBMS_OUTPUT.PUT_LINE(V_AGENT_ID); END LOOP; CLOSE V_CURSOR; COMMIT; END;
注释：
---- 添加表注释 COMMENT ON TABLE 表名 IS '注释'; ---- 添加字段注释 COMMENT ON COLUMN 表名.字段 IS '注释'; ----- 修改、删除注释 COMMENT ON TABLE 表名 IS '新注释'; -- 修改 COMMENT ON COLUMN 表名.字段 IS '新注释'; COMMENT ON TABLE 表名 IS ''; ---- 删除即置为空 COMMENT ON COLUMN 表名.字段 IS ''; ---- 查看表注释 SELECT * FROM USER_TAB_COMMENTS WHERE TABLE_NAME = '表名'; -- 表名是字符串 SELECT * FROM USER_COL_COMMENTS WHERE TABLE_NAME = '表名'; USER_TAB_COMMENTS表： 字段： TABLE_NAME:表名 TABLE_TYPE：表类型 COMMENTS：注释内容 USER_COL_COMMENTS表： 字段： TABLE_NAME：表名 COLUMN_NAME：字段名 COMMENTS：注释内容
数据抽取：
1、新建表结构，确定表名、表注释、字段名，字段数据类型，字段注释，数据结构类型长度尽可能大，因为源数据可能比较“脏”。
2、在数据库中创建表和添加表注释；CREATE TABLE ..... ; COMMENT ON ...... ;
3、运用spoon工具抽取数据到数据库
1、确定输入和输出的文件类型，建立转换。
2、输入设置：数据文件位置、输入的格式、字段和数据类型
3、输出设置：连接数据库 ---- 确定输出到的表、字段和输入源字段和输出表字段的映射
数据清洗合并：
1、建立中间表，把几张表重复的字段合到一个中间表中，去掉重复字段合并不同字段。
2、清洗数据，把多余的字符或者错误的字符去掉。
建立事实表和维度表：
1、根据业务建立事实表和维度表。
2、从中间表中抽取数据插入到事实表和维度表中。
事实表：事实表就是你要关注的内容；
维度表：维表就是你观察该事务的角度，是从哪个角度去观察这个内容的。
例如：
产品维度表：Prod_id, Product_Name, Category, Color, Size, Price
时间维度表：TimeKey, Season, Year, Month, Date
销售事实表：Prod_id(引用产品维度表), TimeKey(引用时间维度表), SalesAmount(销售总量，以货币计), Unit(销售量)
自定义函数：
CREATE OR REPLACE FUNCTION 函数名(参数1 模式 参数类型,参数2 模式 参数类型....) RETURN 返回值类型 AS/IS 变量1 变量类型; 变量2 变量类型; BEGIN 函数体; RETURN 变量; --- 函数必须要有返回值 EXCEPTION WHEN 异常类型 THEN WHEN 异常类型 THEN .... WHEN OTHERS THEN RETURN 变量; ---- 函数必须要有返回值 EDN;
-- 函数调用
1、SELECT 函数名(参数) FROM DUAL;
2、在结构体中调用
函数和存储过程的区别：
1、返回值的区别,函数有1个返回值,而存储过程是通过参数返回的,可以有多个或者没有
2、调用的区别,函数可以在查询语句中直接调用,而存储过程必须单独调用。函数一般情况下是用来计算并返回一个计算结果而存储过程一般是用来完成特定的数据操作（比如修改、插入数据库表或执行某些DDL语句等等）
触发器：触发器的定义就是说某个条件成立的时候，触发器里面所定义的语句就会被自动的执行。因此触发器不需要人为的去调用，也不能调用。
语句级触发器：可以在某些语句执行前或执行后被触发。
在一个表中定义的语句级的触发器，当这个表被删除时，程序就会自动执行触发器里面定义的操作过程。这个就是删除表的操作就是触发器执行的条件了。
行级触发器：定义了触发器的表中的行数据改变时就会被触发一次。
在一个表中定义了行级的触发器，那当这个表中一行数据发生变化的时候，比如删除了一行记录，那触发器也会被自动执行了。
触发器分类：
1.DML触发器： 创建在表上，由DML事件引发
2.instead of触发器： 创建在视图上并且只能在行级上触发，用于替代insert，delete等操作（由于oracle中不能直接对有两个以上的表建立的视图进行DML操作，所以给出替代触发器，它是专门为进行视图操作的一种处理方法）
3.DDL触发器： 触发事件时数据库对象的创建和修改
4.数据库事件触发器：定义在数据库或者模式上，由数据库事件触发
组成：
1.触发事件：引发触发器被触发的事件 DML语句(INSERT, UPDATE, DELETE语句对表或视图执行数据处理操作)、DDL语句（如CREATE、ALTER、DROP语句在数据库中创建、修改、删除模式对象）、数据库系统事件（如系统启动或退出、异常错误）、用户事件（如登录或退出数据库）。
2.触发时间：即该触发器是在触发事件发生之前（BEFORE）还是之后(AFTER)触发
3.触发操作：触发器触发后要完成的事情
4.触发对象：包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件，触发器才会执行触发操作。
5.触发条件：由WHEN子句指定一个逻辑表达式。只有当该表达式的值为TRUE时，遇到触发事件才会自动执行触发操作。
6.触发频率：说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。（比如delete多条数据时，行级触发器可能会执行多次，语句级触发器只会触发一次）
语法：
--- 一般语法 CREATE [OR REPLACE] TIGGER触发器名 触发时间 触发事件 ON表名/视图名 [FOR EACH ROW] //加上FOR EACH ROW 即为行级触发器，不加时为语句级触发器 BEGIN pl/sql语句 END create [or replace] trigger [schema.]trigger_name {before | after | instead of} {delete [or insert][or update [of column,...n]]} on [schema.]table_name | view_name [for each row [when(condition)]] sql_statement[,...n]
new和old：
create or replace trigger TRI_PrintTest before delete or insert or update on TEST_EXAM --触发事件 for each row -- 每修改一行都需要调用此过程 begin dbms_output.put_line(:new.IDNEW); --IDNEW字段更新后的值 dbms_output.put_line(:old.IDNEW); --IDNEW字段更新前的值 end;
注意：
1） 在触发器的执行部分只能用DML语句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL语句（CREATE、ALTER、DROP）
2） 触发器中不能使用commit语句，触发器的操作与触发事件（INSERT,UPDATE,DELETE）一起进行COMMIT和ROLLBACK;
3) 一个表上的触发器越多，对于表的DML操作性能影响越大
4） 触发器最大为32K
触发器博客参考：https://www.cnblogs.com/wishyouhappy/p/3665851.html
SCHEMA模式：schema是数据或模式对象的逻辑结构的集合，由数据库用户拥有，并且与该用户具有相同的名称，也就是说每个用户拥有一个独立的schema。
user：Oracle用户是用连接数据库和访问数据库对象的。（用户是用来连接数据库访问数据库）。
schema：模式是数据库对象的集合。模式对象是数据库数据的逻辑结构。（把数据库对象用模式分开成不同的逻辑结构）。
user和schema区别：用户是用来连接数据库对象。而模式用是用创建管理对象的。(模式跟用户在oracle 是一对一的关系。)
比如我们在访问数据库时，访问scott用户下的emp表，通过select * from emp; 其实，这sql语句的完整写法为select * from scott.emp。在数据库中一个对象的完整名称为schema.object，而不属user.object。类似如果我们在创建对象时不指定该对象的schema，在该对象的schema为用户的缺省schema。这就像一个用户有一个缺省的表空间，但是该用户还可以使用其他的表空间（前提是有权限），如果我们在创建对象时不指定表空间，则对象存储在缺省表空间中，要想让对象存储在其他表空间中，我们需要在创建对象时指定该对象的表空间。
EXISTS：外表字段逐个跟子查询作 = 比较，如果相等则立刻返回TRUE，否则返回False。
NOT EXISTS：外表字段逐个跟子查询作 = 比较，如果相等则立刻返回False，否则返回TRUE。
IN：先执行子查询，原理是 field = field1 OR field = field2 OR field = field3 .....。
NOT IN：先执行子查询，原理是 field ！= field1 AND field ！= field2 AND field ！= field3 .....。
因为涉及到null的处理，所以NOT EXISTS和NOT IN是不同的，null与任何数作任何操作都是null（False）
Select * From A where not exists (Select 1 from B where B.col = A.col)
对于in 和 exists的性能区别:
如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in,反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
其实我们区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了
索引：索引是一个object,在逻辑等级上和表,视图,存储过程,包都是同一层级的概念。所以索引不是表结构的一部分，不同的表不能用相同的索引名。
1、普通索引：默认创建普通索引，允许有重复值出现
2、唯一索引：普通索引 + 唯一约束
3、复合索引：几个字段合在一起的索引，是普通索引的一种
4、函数索引：对函数运算的结果建立索引
创建索引：
CREATE [UNIQUE] INDEX 索引名 ON 表名(字段名);
删除索引：
DROP INDEX
索引执行计划：
1、INDEX UNIQUE SCAN：索引唯一扫描
只适用于唯一索引，等值判断，因为查找到符合的条件就会停止扫描（数据是唯一的，不必扫描全表）
2、INDEX RANGE SCAN：索引范围扫描
用于<,>,=等操作，需要查找在范围内的数据。普通索引的等值操作也是范围查找，因为会有重复的数据。
3、INDEX FULL SCAN、INDEX FAST FULL SCAN：索引全扫描；适用于我们想选择的列都包含在索引里面时，这时候就可以使用INDEX FULL SCAN或者INDEX FAST FULL SCAN来代替全表扫描来得到想要的结果。
俩个都是基于B树的数据结构，因为索引的建立不包含null值，所以索引列上必须有not null约束，否则不会用到这俩个索引。
前提条件：当SELECT和WHERE中出现的列都是发生INDEX FULL SCAN与INDEX FAST FULL SCAN的索引
INDEX FULL SCAN：按照数据的逻辑顺序读取数据块，会发生单块读事件。select的结果是有序的
官方解释：ORACLE定位到索引的根节点，然后到中间节点（如果有的话），再定位到第一个叶子节点, 然后根据叶子节点的双向链表顺序读取。它所读取的块都是有顺序的，也是经过排序的。
INDEX FAST FULL SCAN：按照数据块的物理存储位置读取数据块，会发生多块读事件，理论斯索引快速扫描比索引全扫描要快。select的结果是无序的
官方解释：从段头开始，读取包含位图块，根节点，所有的中间节点，叶子节点，读取的顺序完全有物理存储位置决定，并采取多块读，每次读取DB_FILE_MULTIBLOCK_READ_COUNT个块。查询某个表记录总数的时候，往往基于PRIMARY KEY的INDEX FAST FULL SCAN是最有效的。
4、INDEX SKIP SCAN：索引跳跃扫描；当表有一个复合索引，而在查询中有除了索引中第一列的其他列作为条件，并且优化器模式为CBO（Oracle查询优化器），这时候查询计划就有可能使用到INDEX SKIP SCAN
前提：在前导列唯一值较少的情况下，才会用到index skip can，例如表employees (sex, employee_id, address) ，有一个组合索引(sex, employee_id)，sex只有2种取值。
原理：当前导列值少的时候，会把前导列和后面的字段分成俩个索引，所以查询的时候不必包含第一个字段。
表的连接方式：
1、SORT MERGE JOIN（排序-合并连接）
表连接方式中，排序连接合并个人理解为多数处理非等值连接的表连接情况，例如<,> >=,<=,执行效率上看与HASH JOIN比有所降低（因有排序过程），但是如果两个表的连接列上均有索引，则有些场景下则可以避免排序过程（索引有序）。工作过程原理如下，A,B两表进行排序连接合并：
1、根据谓词条件访问A表，对A表的结果集按照连接列进行排序，形成R1
2、根据谓词条件访问B表，对B表的结果集按照连接列进行排序，形成R2
2、NESTED LOOPS（嵌套循环）
是一种两个表在做表连接时依靠两层嵌套循环（分别为外层循环和内层循环）来得到连接结果集的表连接方法。驱动表（靠前的表）扫描一行，就扫描一次被驱动表（靠后的表）；一般把有索引的大表放在被驱动表上，因为扫描次数多
把两张表最终需要关联的数据对比，大表适合做被驱动表，因为可以减少从硬盘读取扫描表的次数。因为需要不停地从硬盘中读取驱动表，性能不好
适用场景：
1、如果驱动表所对应的驱动表结果集是记录数较少，同时在被驱动表的连接列上又存在唯一性索引（或者在被驱动表的连接列上存在选择性好的非唯一性索引），那么使用嵌套循环连接的执行效率就会很高；
2、大表也可以作为嵌套循环连接的驱动表，关键是看目标SQL中指定的谓语条件（如果有的话，能否将驱动表结果集的记录集数量大幅度的降下来）
3、嵌套循环连接有其他连接方法所没有的一个优点：嵌套循环连接可以实现快速响应。因为排序合并连接需要等到排序完后做合并操作时才能开始返回数据，而哈希连接则需要等到驱动表结果集所对应的HASh TABLE全部构建完后才能开始返回数据。
3、HASH JOIN（哈希连接）
Hash Join连接是借助Hash算法，连带小规模的Nest Loop Join，同时利用内存空间进行高速数据缓存检索的一种算法；只适用于等值连接条件
被驱动表作为哈希表。
1、Hash Join连接对象依然是两个数据表，首先选择出其中一个“小表”。这里的小表，就是参与连接操作的数据集合数据量小。对连接列字段的所有数据值，进行Hash函数操作。Hash函数是计算机科学中经常使用到的一种处理函数，利用Hash值的快速搜索算法已经被认为是成熟的检索手段。Hash函数处理过的数据特征是“相同数据值的Hash函数值一定相同，不同数据值的Hash函数值可能相同”；
2、经过Hash处理过的小表连接列，连同数据一起存放到Oracle PGA（存储空间）空间中。PGA中存在一块空间为hash_area，专门存放此类数据。并且，依据不同的Hash函数值，进行划分Bucket（存储单元）操作。每个Bucket中包括所有相同hash函数值的小表数据。同时建立Hash键值对应位图。
3、之后对进行Hash连接大表数据连接列依次读取，并且将每个Hash值进行Bucket匹配，定位到适当的Bucket上（应用Hash检索算法）；
4、在定位到的Bucket中，进行小规模的精确匹配。因为此时的范围已经缩小，进行匹配的成功率精确度高。同时，匹配操作是在内存中进行，速度较Merge Sort Join时要快很多；
4、CARTESIAN PRODUCT（笛卡尔积）
笛卡尔连接实际上是一种特殊的“合并连接”，类似于排序合并连接(Sort Merge Join)，只是不需要排序，且在执行合并操作室没有连接条件而已。
索引的数据结构：
1、B-tree（B树）：
B树比二叉查找树的优势：深度低，IO次数少
特征：
1、根节点至少有2个子女
2、每一个之间节点都包含k - 1个元素和k个孩子，m/2 <= k <= m
3、每一个叶子节点都包含k - 1 个元素，m/2 <= k <= m
4、所有的叶子节点都位于同一层
5、每一个节点中的元素从小到大排列，节点当中k - 1个元素正好是k个孩子包含的元素的值域划分
2、HASH索引：
Hash根据给定的字段值快速直接访问一个特定的已存储的记录，每一个记录被放置的位置是根据同一个函数计算得出。Hash的逻辑顺序和物理顺序没有任何的关系
3、BITMAP（位图索引）：
位图索引适合只有几个固定值的列，如性别、婚姻状况、行政区等等，而身份证号这种类型不适合用位图索引。
例子：
如果用户查询的列的基数非常的小， 即只有的几个固定值，如性别、婚姻状况、行政区等等。要为这些基数值比较小的列建索引，就需要建立位图索引。对于性别这个列，位图索引形成两个向量，男向量为10100...，向量的每一位表示该行是否是男，如果是则位1，否为0，同理，女向量位01011。
对于婚姻状况这一列，位图索引生成三个向量，已婚为11000...，未婚为00100...，离婚为00010...。
当我们使用查询语句“select * from table where Gender=‘男’ and Marital=“未婚”;”的时候 首先取出男向量10100...，然后取出未婚向量00100...，将两个向量做and操作，这时生成新向量00100...，可以发现第三位为1，表示该表的第三行数据就是我们需要查询的结果。
4、k-d树
用于分割多维空间的数据结构，每一个节点是一个多维的坐标，比如在构建一个2维的k-d树时，一个2维数据根据其与树的根节点进行大小的比较的结果来决定是划分到左子树或者右子树，即每次都选择一个维度Di来对维度进行划分，相当于垂直于该维度Di的超平面将k维数据分开
5、点四叉树：是一种空间索引树，四叉树的每一个节点都代表一块矩形的区域，节点可分裂为4个子节点。
数据同步：不同用户之间的表的数据同步
CREATE USER 用户名 IDENTIFIED BY '密码';
GRANT RESOURCE,CONNECT TO 新用户名;
GRANT SELECT,UPDATE,DELETE TO 新用户名;
GRANT SELECT ON 表名 TO 新用户名;
CREATE ODS.TMP AS SELECT * FROM SCOTT.EMP WHERE 1 = 2; -- 加上schema就指定是那个用户
MERGE：更新和插入数据
MERGE INTO 目标表 USING (增量) ON (匹配字段) WHEN MATCHED THEN UPDATE SET WHEN NOT MATCHED THEN INSERT() VALUES(); MERGE INTO EMP E USING (SELECT * FROM EMP S WHERE S.DEPTNO = 10) S ON (S.EMPNO = E.EMPNO) WHEN MATCHED THEN UPDATE SET E.COMM = 1 DELETE WHERE 1 = 1; --SET随便改，后面接DELETE
CREATE OR REPLACE PROCEDURE SP_EMPC(P_START_DATE VARCHAR2, P_END_DATE VARCHAR2) IS V_START_DATE DATE := TO_DATE(P_START_DATE, 'YYYY-MM-DD HH24:MI:SS'); V_END_DATE DATE := TO_DATE(P_END_DATE, 'YYYY-MM-DD HH24:MI:SS'); BEGIN MERGE INTO EMPEMP A USING (SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO FROM EMP E WHERE E.LAST_UPDATE_DATE >= V_START_DATE AND E.LAST_UPDATE_DATE < V_END_DATE) B ON (A.EMPNO = B.EMPNO) WHEN MATCHED THEN UPDATE SET A.ENAME = B.ENAME, A.JOB = B.JOB, A.MGR = B.MGR, A.HIREDATE = B.HIREDATE, A.SAL = B.SAL, A.COMM = B.COMM, A.DEPTNO = B.DEPTNO, A.LAST_UPDATE_DATE = SYSDATE WHEN NOT MATCHED THEN INSERT (A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO, A.LAST_UPDATE_DATE) VALUES (B.EMPNO, B.ENAME, B.JOB, B.MGR, B.HIREDATE, B.SAL, B.COMM, B.DEPTNO, SYSDATE); COMMIT; END; BEGIN SP_EMPC('2017-04-05 00:00:00','2017-04-06 00:00:00'); END;
包：
--- 包头名和包体名必须一样 --- 创建包头 CREATE [OR REPLACE] PACKAGE 包名 AS/IS 变量、常量、数据类型定义 游标定义头部 函数，存储过程定义，参数列表，返回类型 END; -- 创建包体 CREATE [OR REPLACE] PACKAGE BODY 包名 IS|AS PROCEDURE 过程名(参数) IS|AS BEGIN 过程体; END [过程名]; FUNCTION 函数名(参数) RETURN 类型 IS|AS BEGIN 函数体; END [函数名]; END; --- 调用包 BEGIN 包名.变量名|常量名; 包名.游标名[(参数)]; 包名.函数名[(参数)]|过程名[(参数)]; END;
CREATE OR REPLACE PACKAGE MYPACKAGE AS A NUMBER; PROCEDURE SP_NAME(X IN NUMBER); FUNCTION MY_FUN(F_A NUMBER) RETURN NUMBER; END; CREATE OR REPLACE PACKAGE BODY MYPACKAGE AS ---- PROCEDURE SP_NAME(X IN NUMBER) AS BEGIN DBMS_OUTPUT.PUT_LINE(X); commit; END SP_NAME; ----- END PA_NAME;
表空间和表分区：
表空间：是一个或多个数据文件的集合，所有的数据对象都存放在指定的表空间中，但主要存放的是表，所以称作表空间。
分区表：当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到一个或多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
表分区的具体作用：分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。每个分区有自己的名称，还可以选择自己的存储特性。但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用DML命令访问分区后的表时，无需任何修改。
适用场景：
1、表的数据特别大
2、表中包含历史数据，新的数据被增加到新的分区中
优点：
1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度
2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可以用
3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区可用
4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能
缺点：
1、分区表相关，已经存在的表没有方法可以直接转化为分区表。
2、需要维护
表分区类型：
1、范围分区：RANGE
范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。
1、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
3、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
CREATE TABLE MYEMP
(
EMPNO NUMBER(4) PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(7,2) ,
CONSTRAINT EMP2_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO)
)
PARTITION BY RANGE (HIREDATE)
(
PARTITION part1 VALUES LESS THAN (TO_DATE('1981-1-1','YYYY/MM/DD')) tablespace TBSP_1, -- tablespace 表空间
PARTITION part2 VALUES LESS THAN (TO_DATE('1982-1-1','YYYY/MM/DD')) tablespace TBSP_1,
PARTITION part3 VALUES LESS THAN (TO_DATE('1983-1-1','YYYY/MM/DD')) tablespace TBSP_1,
PARTITION part4 VALUES LESS THAN (TO_DATE('1988-1-1','YYYY/MM/DD')) tablespace TBSP_1,
PARTITION part5 VALUES LESS THAN (MAXVALUE) tablespace TBSP_1
)
2、列表分区：LIST
该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
CREATE TABLE MYEMP2 ( EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(7,2), CONSTRAINT EMP1_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO) ) 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、散列(哈希)分区：HASH
这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。
create table t_test_part( id number, pro_num varchar2(40), app_type varchar2(40)， ............... ) partition by hash(pro_num) ( partition part_01, partition part_02, partition part_03, partition part_04, partition part_05, partition part_06, partition part_07, partition part_08 )
4、组合分区：
这种分区是基于两种分区的组合，分区之中的分区被称为子分区。
CREATE TABLE MYEMP3 ( EMPNO NUMBER(4) PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(7,2) , CONSTRAINT EMP3_FK FOREIGN KEY(DEPTNO) REFERENCES DEPT(DEPTNO) ) 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) ) )
维护性操作：
-- 添加分区：以下添加的分区界限应该高于最后一个分区界限 ALTER TABLE 表名 ADD PARTITION P3 VALUES LESS THEN(TO_DATE('2003-06-01','YYYY-MM-DD')); --删除分区，如果删除的分区是表中唯一的分区，那么此分区将不能被删除，要想删除此分区，必须删除表。 ALTER TABLE SALES DROP PARTITION P3; -- 删除子分区 ALTER TABLE SALES DROP SUBPARTITION P4SUB1; -- 截断分区，删除某个分区中的数据，并不会删除分区，也不会删除其它分区中的数据 -- 截断分区 ALTER TABLE SALES TRUNCATE PARTITION P2; -- 截断子分区 ALTER TABLE SALES TRUNCATE SUBPARTITION P2SUB2; -- 合并分区；合并分区是将相邻的分区合并成一个分区，结果分区将采用较高分区的界限，值得注意的是，不能将分区合并到界限较低的分区。以下代码实现了P1 P2分区的合并： ALTER TABLE SALES MERGE PARTITIONS P1,P2 INTO PARTITION P2; -- 拆分分区；拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。 ALTER TABLE SALES SBLIT PARTITION P2 AT(TO_DATE('2003-02-01','YYYY-MM-DD')) INTO (PARTITION P21,PARTITION P22); -- 接合分区(coalesca)；结合分区是将散列分区中的数据接合到其它分区中，当散列分区中的数据比较大时，可以增加散列分区，然后进行接合，值得注意的是，接合分区只能用于散列分区中。通过以下代码进行接合分区： ALTER TABLE SALES COALESCA PARTITION; -- 重命名表分区 ALTER TABLE SALES RENAME PARTITION P21 TO P2;
序列：
-- 创建语法 CREATE SEQUENCE sequence_name [START WITH num] [INCREMENT BY increment] [MAXVALUE num|NOMAXVALUE] [MINVALUE num|NOMINVALUE] [CYCLE|NOCYCLE] [CACHE num|NOCACHE] START WITH：从某一个整数开始，升序默认值是1，降序默认值是-1。 INCREMENT BY：增长数。如果是正数则升序生成，如果是负数则降序生成。升序默认值是1，降序默认值是-1。 MAXVALUE：指最大值。 NOMAXVALUE：这是最大值的默认选项，升序的最大值是：1027，降序默认值是-1。 MINVALUE：指最小值。 NOMINVALUE：这是默认值选项，升序默认值是1，降序默认值是-1026。 CYCLE：表示如果升序达到最大值后，从最小值重新开始；如果是降序序列，达到最小值后，从最大值重新开始。 NOCYCLE：表示不重新开始，序列升序达到最大值、降序达到最小值后就报错。默认NOCYCLE。 CACHE：使用CACHE选项时，该序列会根据序列规则预生成一组序列号。保留在内存中，当使用下一个序列号时，可以更快的响应。当内存中的序列号用完时，系统再生成一组新的序列号，并保存在缓存中，这样可以提高生成序列号的效率。Oracle默认会生产20个序列号。 NOCACHE：不预先在内存中生成序列号。
创建好序列后，可以通过序列对象的 CURRVAL 和 NEXTVAL 俩个“伪列”分别访问该序列的当前值和下一个值。
-- 访问当前值
SELECT MYSEQ.CURRVAL FROM DUAL;
-- 访问下一个值：
SELECT MYSEQ.NEXTVAL FROM DUAL;
可以修改序列的值，但是有限制：
1、不能修改序列的初始值
2、最大值不能小于当前值
3、最小值不能大于当前值
--序列修改
ALTER SEQUENCE MYSEQ
MAXVALUE 10000
MINVALUE -300;
--删除序列
DROP SEQUENCE MYSEQ;
视图：视图（View）实际上是一张或者多张表上的预定义查询，这些表称为基表。从视图中查询信息与从表中查询信息的方法完全相同。只需要简单的SELECT…FROM即可。
优点：
1、可以限制用户只能通过视图检索数据。这样就可以对最终用户屏蔽建表时底层的基表，具有安 全性。
2、可以将复杂的查询保存为视图，屏蔽复杂性
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
AS
SELECT查询
[WITH READ ONLY CONSTRAINT]
OR REPLACE：如果视图已经存在，则替换旧视图。
FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。
NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。
WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制（比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作），
WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。
-- 删除视图
DROP VIEW 视图名;
创建日志：本质是一张表
日志是用来追溯问题的，记录整个程序的运行情况，知道哪个环节报错了，记录每一步花了多少时间，判断哪一步性能不好，从而对程序进行修改和优化。
人为创建的日志区别于Oracle系统自带的日志，后者调用的成本比较高。
通常报错的时候，会有很多条报错信息，第一条是真正报错的原因。
一般SP里都会有调用日志语句。
日志一般包含的字段：
--创建日志表 CREATE TABLE LOG_RECORD ( LOG_ID NUMBER,SP_NAME VARCHAR2(100),CYCLE_ID NUMBER,STEP NUMBER,FINISH_TIME DATE,REMARKS VARCHAR2(100)) --创建序列用于LOG_ID CREATE SEQUENCE SEQ_LOG_ID --创建序列用于CYCLE_ID CREATE SEQUENCE SEQ_CYCLE_ID --创建存储过程日志记录 CREATE OR REPLACE PROCEDURE SP_LOG(P_SP_NAME VARCHAR2, P_CYCLE_ID NUMBER, P_STEP NUMBER, P_REMARKS VARCHAR2) IS BEGIN INSERT INTO LOG_RECORD (LOG_ID, SP_NAME, CYCLE_ID, STEP, FINISH_TIME, REMARKS) VALUES (SEQ_LOG_ID.NEXTVAL, P_SP_NAME, P_CYCLE_ID, P_STEP, SYSDATE, P_REMARKS); COMMIT; END;
CREATE OR REPLACE PROCEDURE SP_EMPD(P_START_DATE VARCHAR2, P_END_DATE VARCHAR2) IS V_START_DATE DATE := TO_DATE(P_START_DATE, 'YYYY-MM-DD HH24:MI:SS'); V_END_DATE DATE := TO_DATE(P_END_DATE, 'YYYY-MM-DD HH24:MI:SS'); CURSOR C_UPDATE IS SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO FROM EMP E WHERE E.LAST_UPDATE_DATE >= V_START_DATE AND E.LAST_UPDATE_DATE < V_END_DATE; CT NUMBER; CYCLE_ID NUMBER; --定义变量 BEGIN CYCLE_ID := SEQ_CYCLE_ID.NEXTVAL; SP_LOG('SP_EMPD', CYCLE_ID, 1, '程序开始’); --调用日志：程序开始 FOR X IN C_UPDATE LOOP SELECT COUNT(1) INTO CT FROM EMPEMP A WHERE A.EMPNO = X.EMPNO; IF CT = 1 THEN UPDATE EMPEMP A SET A.ENAME = X.ENAME, A.JOB = X.JOB, A.MGR = X.MGR, A.HIREDATE = X.HIREDATE, A.SAL = X.SAL, A.COMM = X.COMM, A.DEPTNO = X.DEPTNO, A.LAST_UPDATE_DATE = SYSDATE WHERE A.EMPNO = X.EMPNO; ELSIF CT = 0 THEN INSERT INTO EMPEMP A (A.EMPNO, A.ENAME, A.JOB, A.MGR, A.HIREDATE, A.SAL, A.COMM, A.DEPTNO, A.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; SP_LOG('SP_EMPD', CYCLE_ID, 2, '同步完成’); --调用日志：同步完成 COMMIT; END; BEGIN SP_EMPD('2017-04-05 00:00:00', '2017-04-06 00:00:00'); END;
数据仓库：
数据仓库（Data Warehouse）是一个面向主题的（Subject Oriented）、集成的（Integrated）、相对稳定的（Non-Volatile）、反映历史变化（Time Variant）的数据集合，用于支持管理决策(Decision Making Support)。
面向主题的：经过ETL抽数、清洗、转换加载后，数据按不同主题存放在同一个库中，梳理归类；
集成的：不来来源的数据的集合。
相对稳定的：不会人为改变任何数据，只同步。如果源系统出故障了，数据仓库的数据依然存在。
反应历史变化：源系统数据库一般只保存几个月，定期删除，数据仓库可保存几年后压缩在硬盘里。
数据仓库分层：
1、ODS层：操作数据存储层
是最接近数据源中数据的一层，数据源中的数据，基本不做清洗数据。
会定时删除数据，时间长短根据业务而定。
2、DW层：数据仓库层，是数据仓库的主题。
数据仓库层，它的数据是干净的数据，是一致的准确的，也就是清洗后的数据，它的数据一般都遵循数据库第三范式（建模），数据粒度和ods的粒度相同，它会保存bi系统中所有历史数据
3、DM层：数据集市层
通常根据业务需求，划分成流量、订单、用户等，生成字段比较多的宽表，用于提供后续的业务查询，OLAP分析，数据分发等。
数据集市层，它是面向主题组织数据的，通常是星状和雪花状数据，从数据粒度来讲，它是轻度汇总级别的数据，已经不存在明细的数据了，从广度来说，它包含了所有业务数量。从分析角度讲，大概就是近几年
4、RPT层：报表层
根据DM层数据进行查询开发报表
雪花模型和星型模型：
星型模型：所有的维度表都能直接跟事实表关联，存在冗余数据，一般来说性能会更好，一般的事实表和维度表。
雪花模型：一个或多个维度表没有直接跟事实表关联，需要通关其他维度表才能关联到事实表，去除了冗余数据，因为跟维度表要关联多次，所以效率不一定有星型模型好。遵循范式原则冗余小但是查询开销大。
优缺点：
星型模型因为数据的冗余所以很多统计查询不需要做外部的连接，因此一般情况下效率比雪花型模型要高。星型结构不用考虑很多正规化的因素，设计与实现都比较简单。
雪花型模型由于去除了冗余，有些统计就需要通过表的联接才能产生，所以效率不一定有星型模型高。正规化也是一种比较复杂的过程，相应的数据库结构设计、数据的 ETL、以及后期的维护都要复杂一些。
范式：数据库只需要满足第三范式就可以
第一范式（1NF）：所有字段值都是不可分解的原子值，即不能同行同列出现两个值。
第二范式（2NF）：满足第一范式为前提，一定要有主属性键，且每一列都和主键相关，而不能只与主键的某一部分相关。（在1NF基础上消除非主属性对主码的部分函数依赖，实体的属性完全依赖于主关键字）
第二范式（3NF）：满足第二范式为前提，每一列数据都和主键直接相关，而不能间接相关。（在2NF基础上消除传递依赖）
代理主键和业务主键：
代理主键（ID列，自增长列）：与业务完全不相关
优点：
1、纯数字，占用空间少，关联性能好
2、在业务属性发生变化时，减少了对系统的影响范围，业务主键容易发生改变时使用代理主键。
缺点：
1、数据迁移比较麻烦，存在重复ID。
2、展现时需要与对应的维表关联，多做一次映射转换的动作。
3、代理主键不能被改变。
业务主键（普通主键）：
优点：
1、具有更好的检索性能
2、直观，更好可读和便于理解。
3、数据迁移更加容易
缺点：
1、关联性能相对不好，占空间。（一些主键时字符串类型）
2、某一业务属性发生变化，会牵连很多表，修改代价大。
保存历史数据的方法：
type1：不保存历史数据，直接更新
type2：保存所有的历史数据，也叫缓慢变化维。
增加一条记录，不修改旧记录，直接插入新数据，如果存在主键的重复冲突，则增加代理主键。
type3：保存当前或者上一次的历史数据
添加历史记录，只会追踪上一次的信息，只增加一个字段来记录上一次的记录。
Hints：指定执行计划
查询优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。
Oracle Hints是一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用Oracle Hints来实现：
1) 使用的优化器的类型
2) 基于代价的优化器的优化目标，是all_rows还是first_rows。
3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。
4) 表之间的连接类型
5) 表之间的连接顺序
6) 语句的并行程度
--常用HINTS 1、/*+ PARALLEL(表名1,并行数)[(表名2,并行数)……] */ --指定开启多少个并行|并发（一般为2、4、8……） 2、/*+ INDEX(表名,索引名) */ --指定索引 3、/*+ FULL(表名) */ --指定全表扫描 4、/*+ USE_NL(表名1，表名2) */ --指定用NESTED LOOP连接 5、/*+ USE_HASH(表名1，表名2) */ --指定用HASH连接 6、/*+ USE_MERGE(表名1，表名2) */ --指定用SORT MERGE JOIN 7、/*+ LEADING(表名1，表名2) */ --指定表1作为驱动表 8、/*+ APPEND */ --数据直接插入到高水位上面(与insert连用)直接往后面插，无视前面的空位置 SELECT * /*+ PARALLEL(E,2)(D,2)*/ FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO
