[一、数据类型](
oracle中数据类型分三类：数字型、字符型、日期型。
[1.数字型]number
长度不限，只要是数字就行
-
number(x)为整数，并且长度不超过x位
-
number(x,y)存在小数部分，总长度不超过x位，小数部分为y位
[2.字符型]
varchar2(x)
长度不超过x位
char(x)固定长度是x位，不够则补空格
[3.日期型] date
-
[二、运算部分]
[1. 关系运算]
= --等号 != <> --不等 < <= --小于[等于] > > = --大于[等于] > > between...and... > > in() > > --in (值1[,值2,.....]) 等同于 过滤的对象 = 值1 or 过滤的对象 = 值2 or .... > > distinct --去重
[2. 集合运算]
-
intersect --交集 union --并集，去重 union all --并集，不去重。性能最好 minus --补集
-
[4. 数据类型间相互转换]
--转换日期型 to_date(x,y) --y的长度要和x保持一致 eg: to_date(sysdate,'yyyy-mm-dd hh24:mi:ss') --转换字符型 to_char() --可以单独去'yyyy''mm''dd'... --转换数字型 to_number()
-
is null is not null like --涉及到两个占位符： --_：表示具体的一个位 --%：表示不确定的位数
三、DQL
oracle中基本的查询语句和sql通用语句基本一致，简单介绍下八！
-
通用模板
-
select 要返回的信息 from 表 join...on... where 过滤条件 group by 分组字段 having 分组后的过滤条件 order by 排序字段
2.运行顺序
from where group by having select order by
注意：在oracle中没有mysql中的limit,当我们需要找某列的最大值或者最小值我们需要借助函数实现。
-
子查询
子查询的结果可以作为值、条件、表等使用
--子查询优化sql /* 子查询使用的场景/条件： 1.当条件不明确时，使用子查询查出具体的值，再进行过滤 2.当我们需要条件不满足时，通过子查询构造出我们需要的结果集再进行查询 子查询的返回值： 1 子查询返回单行结果。 一个值 用 等号 进行过滤。 2 子查询返回多行结果集。 多个值 用 IN 进行过滤。 3 子查询返回多行多列结果集 。用 IN 进行过滤。 */ /* 优先将查询结果的数据返回过滤到最小，然后再对结果集进行关联查询。 相当于对两个小表进行关联查询，效果更好。 */-
连接查询
1. 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是从表
-
伪列(oracle独有)
1. rownum --对某列进行排序 rowid --返回当前列的ID值
-
删除重复行的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, 字段名2, ……) SELECT (字段名1, 字段名2, ……) FROM 另外的表名; --在这种语法下，要求结果集中每一列的数据类型必须与表中的每一列的数据类型一致， --结果集中的列的数量与表中的列的数量一致。 字符串类型的字段值必须用单引号括起来, 例如: ’GOOD DAY’ 如果字段值里包含单引号’ 需要进行字符串转换, 我们把它替换成两个单引号''. 字符串类型的字段值超过定义的长度会出错, 最好在插入前进行长度校验. --语法结构：数据更新操作 UPDATE 表名 SET 列名1=值，列名2=值…… WHERE 条件; （WHERE 条件没有的话则是全表更新） --语法结构：数据删除操作 DELETE FROM 表名 WHERE 条件; --语法结构：TRUNCATE（也是删除数据，把表中的数据全部删除---- 清空数据） （数据定义语言 DDL） TRUNCATE TABLE 表名; 日期字段的字段值可以用当前数据库的系统时间SYSDATE, 精确到秒 或者用字符串转换成日期型函数TO_DATE(‘2001-08-01’,’YYYY-MM-DD’) TO_DATE()还有很多种日期格式, 可以参看ORACLE DOC. 年-月-日小时:分钟:秒的格式YYYY-MM-DDHH24:MI:SS INSERT时最大可操作的字符串长度小于等于4000个单字节, 如果要插入更长的字符串, 请考虑字段用CLOB类型,
2，DELETE (删除数据表里记录的语句)
DELETE FROM表名 WHERE 条件;
注意：删除记录并不能释放ORACLE里被占用的数据块表空间. 它只把那些被删除的数据块标成unused.
如果确实要删除一个大表里的全部记录, 可以用 TRUNCATE 命令, 它可以释放占用的数据块表空间
TRUNCATE TABLE 表名;
此操作不可回退.truncate table
3，UPDATE (修改数据表里记录的语句)
UPDATE表名 SET 字段名1=值1, 字段名2=值2, …… WHERE 条件;
如果修改的值N没有赋值或定义时, 将把原来的记录内容清为NULL, 最好在修改前进行非空校验;
值N超过定义的长度会出错, 最好在插入前进行长度校验..
注意事项:
A，以上SQL语句对表都加上了行级锁,
确认完成后, 必须加上事物处理结束的命令 COMMIT 才能正式生效，否则改变不一定写入数据库里。
如果想撤回这些操作，可以用命令 ROLLBACK 复原。
B，在运行INSERT, DELETE 和 UPDATE 语句前最好估算一下可能操作的记录范围，应该把它限定在较小 (一万条记录) 范围内，否则ORACLE处理这个事物用到很大的回退段。
程序响应慢甚至失去响应. 如果记录数上十万以上这些操作，可以把这些SQL语句分段分次完成，其间加上COMMIT 确认事物处理。
二，数据定义 (DDL) 部分
1.CREATE (创建表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)
ORACLE常用的字段类型有
CHAR 固定长度的字符串
VARCHAR2 可变长度的字符串
NUMBER(M,N) 数字型M是位数总长度, N是小数的长度
DATE 日期类型
创建表时要把较小的不为空的字段放在前面, 可能为空的字段放在后面
创建表时可以用中文的字段名, 但最好还是用英文的字段名
创建表时可以给字段加上默认值, 例如 DEFAULT SYSDATE
这样每次插入和修改时, 不用程序操作这个字段都能得到动作的时间
创建表时可以给字段加上约束条件
例如不允许重复 UNIQUE, 关键字 PRIMARY KEY
2，ALTER (改变表, 索引, 视图等)
改变表的名称
ALTER TABLE 表名1 TO 表名2;
在表的后面增加一个字段
ALTER TABLE表名 ADD 字段名字段名描述;
修改表里字段的定义描述
ALTER TABLE表名 MODIFY字段名字段名描述;
给表里的字段加上约束条件
ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (字段名);
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (字段名);
把表放在或取出数据库的内存区
ALTER TABLE 表名 CACHE;
ALTER TABLE 表名 NOCACHE;
3，DROP (删除表, 索引, 视图, 同义词, 过程, 函数, 数据库链接等)
删除表和它所有的约束条件
DROP TABLE 表名 CASCADE CONSTRAINTS;
4，TRUNCATE (清空表里的所有记录, 保留表的结构)
TRUNCATE 表名;
oracle中nvl()函数
函数声明：nvl(col,val)
说明：当col为空时取val作为返回值，当col不为空时取col值。
用处：最主要的是格式化数据，比如计算金额时，不想出现空数据，可以使用nvl(JINE,0)来得到0。由于null+(或-,*,/）数字等于null，所以在表达式中对可能为空的值要使用nvl由于null!=null，有时对可能为空的列进行条件查询时，可能出现结果集丢失数据问题，加上nvl就不会了
oracle中nvl()函数 oracle的nvl函数的使用方法 通过查询获得某个字段的合计值,假设这个值位null将给出一个预设的默认值 select nvl(sum(t.dwxhl),1) f ...
oracle中nvl函数用法
1.返回两个字段中非空字段的值,第一个字段非空,返回第一个字段的值,第二个字段值为null,则返回第二个字段的值,如果都为null 则返回null. select nvl(a,b) from tabl ...
trim一般都是用在删除字符串两边的空格。实际上，trim也可以用来删除字符串两边的指定字符。并且trim指定删除的字符串只能是单个字符
trim('字符1' from '字符串2') ，字符1只能是单个字符。
\1. trim()删除字符串两边的空格。 \2. ltrim()删除字符串左边的空格。 \3. rtrim()删除字符串右边的空格。 \4. trim('字符1' from '字符串2') 分别从字符2串的两边开始，删除指定的字符1。 \5. trim([leading | trailing | both] trim_char from string) 从字符串String中删除指定的字符trim_char。 leading:从字符串的头开始删除。 trailing:从字符串的尾部开始删除。 borth:从字符串的两边删除。 \6. tim()只能删除半角空格。
五、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;
-
表的约束
rimary key --主键约束 oreign key --外键约束 heck --检查约束 nique --唯一约束 ot null --非空约束
1. ### lter 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 约束名; --删除约束
-
对表的操作
1. reate table table_name(row_name_01 datatype[,row_name_02 datatype...]); --创建表 create table table_name_01 s select * from table_name_02 where 1=2; --复制表结构 create table table_name_01 s select * from table_name_02 where...; --复制表结构和表里的数据 rop table table_name; --删除表 lter table table_name add row_name datatype; --添加列 lter table table_name modify row_name datatype; --修改列类型 lter table table_name rename column old_row_name to new_row_name; --修改列名 lter table table_name drop column row_name; --删除列 lter 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; --删除视图
-
对用户的操作
--语法结构：创建用户 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;
-
建表的其他操作
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 ;
2、创建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名;
六、函数
-
数字函数
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)
-
字符函数 dd_months(d,n) --返回日期d加上n个月后的日期 ast_day(d) --返回d所处月份的最后一天的日期 onths_between(d1,d2) --返回两个日期之间相隔几个月 ound(d[,fmt]) --对日期d按照不同维度(fmt:yyyy/mm/dd/day[星期]/q[季度]/w[当月第几周]/iw[今年第几周1]/ww[今年第几周2])来进行四舍五入 runc(d[,fmt]) --对日期d按照不同维度(fmt)来截断,维度参照fmt
-
日期函数
dd_months(d,n) --返回日期d加上n个月后的日期 ast_day(d) --返回d所处月份的最后一天的日期 onths_between(d1,d2) --返回两个日期之间相隔几个月 ound(d[,fmt]) --对日期d按照不同维度(fmt:yyyy/mm/dd/day[星期]/q[季度]/w[当月第几周]/iw[今年第几周1]/ww[今年第几周2])来进行四舍五入 runc(d[,fmt]) --对日期d按照不同维度(fmt)来截断,维度参照fmt
-
聚合函数
max() min() avg() sum() count()
-
分析函数
1. 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...) --向下位移
可以实现行列转换
-
几个判断函数
vl(目标字段,默认值) --判断目标字段是否为空，如果不为空，则取目标字段的值，如果为空，则赋默认值 nvl2(目标字段,默认值1,默认值2) --判断目标字段是否为空，如果不为空，则取默认值1，为空取默认值2 ase when...then...else...end --对某一列数据进行判断，返回一列数据 ecode(目标字段,值1,结果1,值2,结果2...,其他结果) --oracle独有。作用类似case...when xists(x) --判断x是否有数据返回，如果有则成立，没有则不成立。一般x为子查询结果 not exists(x) in() not in() exists和in可以相互转换 sign() ---判断结果集，返回0,1，-1
七、PLSQL
-
语法结构
declare
--声明变量
变量 数据类型 := 初始值;
变量 数据类型 := &提示字段;--自定义输入数据
变量 表名.列名%type := 初始值;--使用某表的列定义数据类型
变量 表名%type := 初始值;--将整个表的数据拿到自身，需要使用其中的数据类型时，表名.列名调用即可。
begin
select...--sql取数语句
into...--对应变量
```
dbms_output.put_line();--数据输出格式
```
end;
plsql的数据类型参照sql，仅有三种：varchar2、number、date
-
条件判断
1. 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;
-
循环控制
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;
-
游标
游标就是一个指向结果集的指针，通过这个指针，可以获取到指向的结果集中的数据。
a. 隐式游标
select...into...
1
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;
-
存储过程
create [or replace] procedure 存储过程名[(参数1 [参数类型] 数据类型[,参数2 [参数类型] 数据类型,...])] is/* / as*/ --声明/定义部分 begin --操作/逻辑部分 end; --开发规范：存储过程名以 sp_ 开头
-
自定义函数
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 表示传入传出参数，可以传入值，可以被 重新 赋值，可以返回值。
-
packagea. 创建包头
-- 创建包头的语法 ,代码的目录 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_包名;
-
常见应用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 ; -- 开始同步时间
1199
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
