Oracle数据库工作常用知识点

所有Oracle常用函数讲解，工作常用ORACLE知识点，分别有 排序、复制表结构和数据、连接方式、删除方式、常用函数、常用的字符函数、求时间区间和值、数据泵的导入导出、游标的使用、Oracle迁移的存储过程... ...

Oracle数据库

• 排序
  • select A.*,row_number()over(order by age) from A; 1.select * from A order by age; 等同于 select A.*,row_number()over(order by age) from A; 2.select A.*,row_number()over(partition by name order by age) from A;---根据name分组，再对age进行排序（重复值序号不一样）。 3.select A.*,rank()over(partition by name order by age) from A;---根据name分组，再对age进行排序（重复值序号一样）。 ！！！另外： sum() over()是分组求和 first_value() over()是求分组第一条 last_value() over()是求分组最后一条 其中用row_number() over()取编号第一条的也可以实现first_value() over()的效果
• 复制表结构和数据
  • create table A as select * from B where 1=1;-复制表结构和数据 create table A as select * from B where 1=2;--只复制表结构
• 连接方式
  • 内连接 交叉连接
    • 语法一：select A.*,B.* from A,B where A.id = B.id 语法二：select A.*, B.* from A inner join B on A.id = B.id 语法三：select * from A cross join B on A.id = B.id
  • 外连接 （完全连接）
    • select * from A full join B where a.id =b.id
  • 笛卡尔积连接
    • 语法一：select * from a , b ； 语法二：select * from a join b ； 两个表连接没有where条件的就叫笛卡尔连接
  • 左连接
    • 语法一：select A.*,B.* from A,B where A.id= B.id(+) 语法二：select A.*,B.* from B left join B on A.id = B.id
  • 右连接
    • 语法一：select A.*,B.* from A,B where A.id(+)= B.id 语法二：select A.*,B.* from B right join B on A.id = B.id
• 删除方式
  • 逐条删除表数据不删除表结构：delete from
    • 语法一：delete from A 语法二：delete from A where A.id 删除A表里的id字段 （delete 可加where条件）
  • 删除表数据不删除表结构不产生回退信息：truncate table
    • 语法一： truncate table A ; truncate table A drop storage; 删除所有记录不保留占用空间:由于默认使用drop storage关键字，所以可以省略 drop storage; 语法二： truncate table A reuse storage; 删除A表中的所有数据并保存占用空间： truncate table A reuse storage;
  • 删除整个表结构以及表数据：drop table
    • drop table A 删除A表数据以及表结构释放空间
• 常用函数
  • exists
    • 有两个简单例子，以说明 “exists”和“in”的效率问题(也可以用not exists ) 1) select * from Ta where exists(select 1 from Tb where Ta.aid = Tb.bid) ; Ta数据量小而Tb数据量非常大时，Ta<<Tb 时，1) 的查询效率高。 2) select * from Ta where Ta.aid in (select Tb.bid from Tb) ; Ta数据量非常大而Tb数据量小时，Ta>>Tb 时，2) 的查询效率高。
  • distinct
    • SELECT DISTINCT 列名称 FROM 表名称 去除重复值
  • like
    • Like 模糊查询 占位符 % 任意个数字符 _ 一个字符 查询 用户名以‘S’开头的员工信息 Select * from emp where ename like 'S%' 查询用户名第二个字母是‘A’的员工信息 select * from emp where ename like '_A%' 查询用户名第三个字母是‘A’的员工信息 select * from emp where ename like '__A%' 包含A select * from emp where ename like '%A%'
  • isge
    • sign 函数返回一个数字的正负标志. 语法如下：sign( number ) number 要测试标志的数字. If number < 0, then sign returns -1. If number = 0, then sign returns 0. If number > 0, then sign returns 1.
  • connect by prior...start with...
    • --创建一个表，举个例子 create table TREE ( ID varchar2(10) primary key, DSC varchar2(100), PID varchar2(10) ) --插入几条数据 SELECT * FROM TREE Insert Into TREE values ('孙子SB', '儿子'); Insert Into TREE values ('孙子NB ', '儿子'); Insert Into TREE values ('儿子', '爸爸 '); Insert Into TREE values ('爸爸 ', '爷爷'); ----以下开始对比四条sql查询的结果 SELECT * FROM tree; SELECT son FROM tree CONNECT BY PRIOR son = father; SELECT son FROM tree START WITH father = '爷爷' CONNECT BY PRIOR son = father ; SELECT son FROM tree CONNECT BY PRIOR son = father START WITH father = '爷爷'; --以SON=“爸爸”为起始点，向上查询： select * from tree start with son = '爸爸' connect by prior father = son; --以SON=“爸爸”为起始点，向下查询： select * from tree start with son = '爸爸' connect by prior son = father;
  • insert all into
    • INSERT ALL WHEN s_id <= 2 THEN ----满足条件插入 INTO stu1 (s_id, s_xm) WHEN s_id >= 2 THEN ----满足条件重复插入 INTO stu2 (s_id, s_xm) SELECT * FROM stu; INSERT FIRST WHEN s_id <= 2 THEN ---满足条件插入 INTO stu1 (s_id, s_xm) WHEN s_id >= 2 THEN ---满足条件如果上面已经插入，这个表不会插入 INTO stu2 (s_id, s_xm) SELECT * FROM stu;
• 常用的字符函数
  • ltrim截去左面空格
    • 语法一：select ltrim (X) from A; 在A表中查出X字段，并截掉字段最前面的空格 语法二：select ltrim(rtrim('xyxxDWEYExyyx','xy'),'xy') "e.g." from dual; xyxxDWEYExyyx 最后结果只剩 DWEYE 使用RTRIM和LTRIM函数时的注意事项：“xy”不表示整个“xy”字符串进行匹配，而是发现任意的字符“x”或字符“y”均做删除操作。
  • rtrim截去右面空格
    • 语法一：select rtrim (X) from A; 在A表中查出X字段，并截掉字段最后面的空格 语法二：select ltrim(rtrim('xyxxDWEYExyyx','xy'),'xy') "e.g." from dual; xyxxDWEYExyyx 最后结果只剩 DWEYE 使用RTRIM和LTRIM函数时的注意事项：“xy”不表示整个“xy”字符串进行匹配，而是发现任意的字符“x”或字符“y”均做删除操作。
  • 截取的两种方式
    • select 字段1 ,regexp_substr(字段1, '[^\.]+', 1, 1) --通过模糊匹配进行字段的切割 ,substr(字段1,1,(instr(字段1,'.',1)-1)) --通过精准定位进行字段的切割 from table_name
  • trim截去两边空格
    • 语法一：select trim (X) from A; 在A表中查出X字段，并截掉字段最前和最后的空字符 语法二：select trim(0 from 7500) from dual; 截去了数字7500的后面的两个0 语法三： select trim(leading ‘半‘ from ‘半夜二更半‘) as 诗 from dual; 截去了' 半夜二更半'前面的一个'半'字
  • trim用0填充位数
    • select trim (to_char(字段，‘00’)) 表名; 可以把字段中一位数字填充为两位，例如：4 变为04
  • substr（字符串截取）
    • 按字符串截取：substr SELECT SUBSTR('Hello SQL!', 1) FROM dual --截取所有字符串，返回'Hello SQL!' SELECT SUBSTR('Hello SQL!', 2) FROM dual --从第2个字符开始，截取到末尾。返回'ello SQL!' SELECT SUBSTR('Hello SQL!', -4) FROM dual --从倒数第4个字符开始，截取到末尾。返回'SQL!' SELECT SUBSTR('Hello SQL!', 3, 6) FROM dual --从第3个字符开始，截取6个字符。返回'llo SQ' SELECT SUBSTR('Hello SQL!', -4, 3) FROM dual --从倒数第4个字符开始，截取3个字符。返回'SQL' 按字节的截取：substrb
  • instr
    • 格式一 1 select instr('helloworld','l') from dual; --返回结果：3 默认第一次出现“l”的位置 2 select instr('helloworld','lo') from dual; --返回结果：4 即“lo”同时出现，第一个字母“l”出现的位置 3 select instr('helloworld','wo') from dual; --返回结果：6 即“wo”同时出现，第一个字母“w”出现的位置 格式二 复制代码 1 select instr('helloworld','l',2,2) from dual; --返回结果：4 也就是说：在"helloworld"的第2(e)号位置开始，查找第二次出现的“l”的位置 2 select instr('helloworld','l',3,2) from dual; --返回结果：4 也就是说：在"helloworld"的第3(l)号位置开始，查找第二次出现的“l”的位置 3 select instr('helloworld','l',4,2) from dual; --返回结果：9 也就是说：在"helloworld"的第4(l)号位置开始，查找第二次出现的“l”的位置 4 select instr('helloworld','l',-1,1) from dual; --返回结果：9 也就是说：在"helloworld"的倒数第1(d)号位置开始，往回查找第一次出现的“l”的位置 5 select instr('helloworld','l',-2,2) from dual; --返回结果：4 也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第二次出现的“l”的位置 6 select instr('helloworld','l',2,3) from dual; --返回结果：9 也就是说：在"helloworld"的第2(e)号位置开始，查找第三次出现的“l”的位置 7 select instr('helloworld','l',-2,3) from dual; --返回结果：3 也就是说：在"helloworld"的倒数第2(l)号位置开始，往回查找第三次出现的“l”的位置 复制代码
  • 截取字符串
    • 1、substr函数格式 (俗称：字符截取函数) 　　格式1： substr(string , int a, int b); 　　格式2：substr(string , int a) ; 解析： 格式1： 1、string 需要截取的字符串 2、a 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取） 3、b 要截取的字符串的长度 格式2： 1、string 需要截取的字符串 2、a 可以理解为从第a个字符开始截取后面所有的字符串。
  • replace(X,old,new)更新数据内容
    • 语句： update A set X = replace(X,'old','new') 在A表里的X字段中查找old，并替换成new
  • lower转换小写
    • select lower(user_name) from user 将 user表里的user_name字段信息中含有字母的全部转成小写
  • upper转换大写
    • select upper(user_name) from user 将 user表里的user_name字段信息中含有字母的全部转成大写
  • concat（拼接）
    • 把表A中的X字段和Z字段拼接查询 方法一：select concat(X,Z) from A; 方法二：select X||Z from A;
  • length(字符长度)
    • select length(X) from A; 查询A表中每一个X字段的字符串长度（一个中文按一个字符串计算） select lengthb(X) from A; 查询A表中每一个X字段的字符串长度（一个中文按两个字符串计算）
  • to_char
    • select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss’) time from dual; 时间类型转换成字符串类型 详细讲解网站：https://www.jb51.net/article/53639.htm
  • to_date
    • select to_date(‘2003-10-17 21:15:37’,‘yyyy-mm-dd hh24:mi:ss’) from dual 字符串类型转换成时间类型
  • lpad
    • pad翻译：填充 lpad函数，在字符串的左侧添加指定字符串，用法： lpad(String ,截取长度，添加的字符串)。 说是添加字符串也不准确，比较准确的说法是对String进行截取字符串，如果截取长度大于String的长度，则在 String的左侧添加字符串进行填补，如果第三个参数未指定，则用空格进行填补。例如： select lpad('test',10) from dual; 将返回“ test”---如果数据不够指定字符长度会用空格填满 select lpad('test',10,'ee') from dual; 结果将返回eeeeeetest。 还有一个rpad 函数，表示向右拼接到指定字符串长度 --下面两条语句结果数据一样（也就是说lpad是拼接符‘||’的代替品） select lpad('test',14,sysdate) from dual; select (sysdate||'test') from dual;
  • table(split())一个字段里有多个值用逗号分割，需要拆分多条数据
    • 例： select * from A ---------------------------------------- ID | 需要分割的字段 ---------------------------------------- 10001 | 01 ---------------------------------------- 10002 | 02,03 ---------------------------------------- 10003 | 03,04， ---------------------------------------- select * from A , table(split(需要分割的字段)) where column_value is not null 在最后一列会新增一个分割好的字段。 ---------------------------------------- ID | 需要分割的字段 ---------------------------------------- 10001 | 01 ---------------------------------------- 10002 | 02 ---------------------------------------- 10002 | 03 ---------------------------------------- 10003 | 03 ---------------------------------------- 10003 | 04 ---------------------------------------- 如果没有split函数，则需要创建 创建语句： 1.创建TYPE类型 CREATE OR REPLACE TYPE TYPE_SPLIT AS TABLE OF VARCHAR2 (4000); 2.创建函数spit（） CREATE OR REPLACE FUNCTION SPLIT(SPLIT_STRING VARCHAR2,SPLIT_DELIMITER VARCHAR2:=',') RETURN TYPE_SPLIT PIPELINED IS IDX PLS_INTEGER; V_STRING VARCHAR2(4000):=SPLIT_STRING; BEGIN LOOP IDX:=INSTR(V_STRING,SPLIT_DELIMITER); IF IDX>0 THEN PIPE ROW(SUBSTR(V_STRING,1,IDX-1)); V_STRING:= SUBSTR(V_STRING,IDX + LENGTH(SPLIT_DELIMITER)); ELSE PIPE ROW(V_STRING); EXIT; END IF; END LOOP; END;
  • listagg(多行转一列，加逗号)
    • listagg 函数是 Oracle 11.2 推出的新特性。 其主要功能类似于 wmsys.wm_concat 函数， 即将数据分组后， 把指定列的数据再通过指定符号合并。 listagg 函数有两个参数： 1、 要合并的列名 2、 自定义连接符号 ☆LISTAGG 函数既是分析函数，也是聚合函数 所以，它有两种用法： 1、分析函数，如： row_number()、rank()、dense_rank() 等，用法相似 listagg(合并字段, 连接符) within group(order by 合并的字段的排序) over(partition by 分组字段) 2、聚合函数，如：sum()、count()、avg()等，用法相似 listagg(合并字段, 连接符) within group(order by 合并字段排序) --后面跟 group by 语句  
  • merge into
    • 语法： merge into table_name alias1 --目标表 可以用别名 using (table|view|sub_query) alias2 --数据源表 可以是表、视图、子查询 on (join condition) --关联条件 when matched then --当关联条件成立时 更新，删除，插入的where部分为可选 --更新 update table_name set col1=colvalue where…… --删除 delete from table_name where col2=colvalue where…… --可以只更新不删除 也可以只删除不更新。 --如果更新和删除同时存在，删除的条件一定要在更新的条件内，否则数据不能删除。 when not matched then --当关联条件不成立时 --插入 insert (col3) values (col3values) where…… when not matched by source then --当源表不存在，目标表存在的数据删除 delete;
• 求时间区间和值
  • -- 取上个月第一天 SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -2)) + 1,'YYYYMMDD') FROM DUAL; -- 取上个月最后一天 SELECT TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE, -1)),'YYYYMMDD') FROM DUAL; --取指定一年内所有日期 select to_char(everyDay, 'yyyy-mm-dd hh:mm:ss') as dt from (select to_date('20190101', 'yyyymmdd') + level - 1 as everyDay from dual connect by level <= (last_day(to_date('20191201', 'yyyymmdd')) - to_date('20190101', 'yyyymmdd') + 1));
• 数据泵的导入导出
  • Oracle数据泵DMP文件的导入及导出 导入操作（本次示例是多个数据泵文件的导入COSPACE.DMP、WISDOMGOV.DMP） 0、数据库登陆 plsql 使用sys账号，as sysdba身份登录orcl实例； 1、在被导入的数据库创建操作目录 1.1、在E盘根目录下创建dump文件夹(将数据泵文件都放到此目录(e:\dump)下) 1.2、用系统管理员登录数据库，在SQL执行器中执行如下命令 create directory dump_dir as 'e:\dump'; 2、创建表空间(sql执行器中执行如下命令) 2.1、创建COSPACE表空间 create tablespace COSPACE logging datafile 'E:\app\Administrator\oradata\orclWG\COSPACE.DBF' --注：数据库实例当时创建时指定的路径 size 50m autoextend on next 50m maxsize 20480m extent management local; 2.2、创建WISDOMGOV表空间 create tablespace WISDOMGOV logging datafile 'E:\app\Administrator\oradata\orcl\WISDOMGOV.DBF' --注：数据库实例当时创建时指定的路径 size 50m autoextend on next 50m maxsize 20480m extent management local; 3、创建用户(sql执行器中执行如下命令) 3.1、创建COSPACE用户 create user COSPACE identified by lilo123 default tablespace COSPACE; 3.2、创建WISDOMGOV用户 create user WISDOMGOV identified by lilo123 default tablespace WISDOMGOV; 4、用户赋权(sql执行器中执行如下命令) 4.1、赋权给COSPACE grant connect,resource,dba to COSPACE; 4.2、赋权给WISDOMGOV grant connect,resource,dba to WISDOMGOV; 5、将数据泵使用目录赋值给用户(sql执行器中执行如下命令) 5.1、grant read,write on directory dump_dir to COSPACE; 5.2、grant read,write on directory dump_dir to WISDOMGOV; 6、执行导入数据（切换到数据泵存放的路径下，执行cmd命令行如下） 6.1、impdp COSPACE/123456@orclWG directory=dump_dir dumpfile=COSPACE.DMP 6.2、impdp WISDOMGOV/123456@orcl directory=dump_dir dumpfile=WISDOMGOV.DMP 7、执行创建同义词脚本 导出操作(导出多个数据泵文件) 0、数据库登陆 plsql 使用sys账号，as sysdba身份登录orcl实例； 1、创建操作目录 1.1、在E盘根目录下创建dump文件夹(数据泵导出存放的路径) 1.2、用系统管理员登录数据库，在SQL执行器中执行如下命令 create directory dump_dir as 'e:\dump'; 1.3、将该目录的使用权限赋给用户，执行如下命令（SQL执行器执行） 1.3.1、赋值给cospace用户 grant read,write on directory dump_dir to cospace; 1.3.2、赋值给wisdomgov用户 grant read,write on directory dump_dir to wisdomgov; 2、导出数据(切换到数据泵存放的路径下，执行cmd命令行如下) 2.1、导出cospace数据 expdp cospace/123456@orclWG directory=dump_dir file=COSPACE.dmp schemas=cospace 2.1、导出wisdomgov数据 expdp wisdomgov/123456@orcl directory=dump_dir file=WISDOMGOV.dmp schemas=wisdomgov
  • 数据泵导入导出的步骤
    • Oracle数据泵DMP文件的导入及导出 导入操作（本次示例是多个数据泵文件的导入COSPACE.DMP、WISDOMGOV.DMP） 0、数据库登陆 plsql 使用sys账号，as sysdba身份登录orcl实例； 1、在被导入的数据库创建操作目录 1.1、在E盘根目录下创建dump文件夹(将数据泵文件都放到此目录(e:\dump)下) 1.2、用系统管理员登录数据库，在SQL执行器中执行如下命令 create directory dump_dir as 'e:\dump'; 2、创建表空间(sql执行器中执行如下命令) 2.1、创建COSPACE表空间 create tablespace COSPACE logging datafile 'E:\app\Administrator\oradata\orclWG\COSPACE.DBF' --注：数据库实例当时创建时指定的路径 size 50m autoextend on next 50m maxsize 20480m extent management local; 2.2、创建WISDOMGOV表空间 create tablespace WISDOMGOV logging datafile 'E:\app\Administrator\oradata\orcl\WISDOMGOV.DBF' --注：数据库实例当时创建时指定的路径 size 50m autoextend on next 50m maxsize 20480m extent management local; 3、创建用户(sql执行器中执行如下命令) 3.1、创建COSPACE用户 create user COSPACE identified by lilo123 default tablespace COSPACE; 3.2、创建WISDOMGOV用户 create user WISDOMGOV identified by lilo123 default tablespace WISDOMGOV; 4、用户赋权(sql执行器中执行如下命令) 4.1、赋权给COSPACE grant connect,resource,dba to COSPACE; 4.2、赋权给WISDOMGOV grant connect,resource,dba to WISDOMGOV; 5、将数据泵使用目录赋值给用户(sql执行器中执行如下命令) 5.1、grant read,write on directory dump_dir to COSPACE; 5.2、grant read,write on directory dump_dir to WISDOMGOV; 6、执行导入数据（切换到数据泵存放的路径下，执行cmd命令行如下） 6.1、impdp COSPACE/123456@orclWG directory=dump_dir dumpfile=COSPACE.DMP 6.2、impdp WISDOMGOV/123456@orcl directory=dump_dir dumpfile=WISDOMGOV.DMP 7、执行创建同义词脚本 导出操作(导出多个数据泵文件) 0、数据库登陆 plsql 使用sys账号，as sysdba身份登录orcl实例； 1、创建操作目录 1.1、在E盘根目录下创建dump文件夹(数据泵导出存放的路径) 1.2、用系统管理员登录数据库，在SQL执行器中执行如下命令 create directory dump_dir as 'e:\dump'; 1.3、将该目录的使用权限赋给用户，执行如下命令（SQL执行器执行） 1.3.1、赋值给cospace用户 grant read,write on directory dump_dir to cospace; 1.3.2、赋值给wisdomgov用户 grant read,write on directory dump_dir to wisdomgov; 2、导出数据(切换到数据泵存放的路径下，执行cmd命令行如下) 2.1、导出cospace数据 expdp cospace/123456@orclWG directory=dump_dir file=COSPACE.dmp schemas=cospace 2.1、导出wisdomgov数据 expdp wisdomgov/123456@orcl directory=dump_dir file=WISDOMGOV.dmp schemas=wisdomgov
  • 工作时导入导出的整个步骤实例
    • su - oracle cd /oradata/ora_backup/ vim expdp_ll_0907.par directory=ORA_EXP dumpfile=expdp_gc3_syp_hx_10_0907_%U.dmp logfile=expdp_gc3_syp_hx_10_0907.log filesize=4G TABLES=( EMSS_CUC.INST_ELEC_CONS_tm20210907, EMSS_ASC.ACQ_TRML_tm20210907, EMSS_ASC.ELEC_METER_tm20210907 ) EXCLUDE=STATISTICS CONTENT=DATA_ONLY compression=ALL cluster=n parallel=4 chomd 775 * expdp \'/ as sysdba \' parfile=expdp_ll_0907.par 切换工程3 su - oracle cd /oradata/ora_backup/ scp oracle@25.218.124.1:/oradata/ora_backup/expdp_gc3_syp_hx_10_0907* ./ vim impdp_table_ll_0907.par directory=ORA_DUMP dumpfile=expdp_gc3_syp_hx_10_0907_%U.dmp logfile=impdp_ll_0907.log TABLES=( EMSS_CUC.INST_ELEC_CONS_tm20210907, EMSS_ASC.ACQ_TRML_tm20210907, EMSS_ASC.ELEC_METER_tm20210907 ) #access_method=direct_path table_exists_action=append #remap_table=(SGPM_OUT.ARC_E_SP_CAP_CHG_SNAP202105:ARC_E_SP_CAP_CHG_SNAP, #content=METADATA_ONLY remap_table= ( EMSS_CUC.INST_ELEC_CONS_tm20210907:INST_ELEC_CONS, EMSS_ASC.ACQ_TRML_tm20210907:ACQ_TRML, EMSS_ASC.ELEC_METER_tm20210907:ELEC_METER ) #version=11.2.0.4.0 exclude=statistics cluster=n parallel=4 --truncate --append impdp \'sys/zpepc001-@CSDB02 as sysdba \' parfile=impdp_table_ll_0907.par impdp \'SYS/Zpepc001_#@HXPDB as sysdba \' parfile=impdp_table_ll_0907.par impdp \'/ as sysdba \' parfile=impdp_table_ll_0910_1.par ----------------------------------------------- 如有以下报错： ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39087: directory name ORA_DUMP is invalid 去plsql查看存放文件的路径:select * from dba_directories; ---报错排查------------------------------------------------------- Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production ORA-39001: invalid argument value ORA-39000: bad dump file specification ORA-31640: unable to open dump file "/oradata/ora_backup/expdp_gc3_syp_hx_10_0830_01.dmp" for read ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7 看看日期参数的日期是否写正确！！ ---------------------------------- 可以登录plsql 查看表：select * from dba_directories; select * from dba_directories@cshxdb; ----------------------------- 如果是报错以下代码 ORA-39882 ORA-39166 输入：md5sum 查看导出的文件是否损坏 ，然后再看日志，是否导出成功，并且看复制过来的dom包大小是否一致，来确定复制来的文件没有被损坏。 --------------------------- 如果报违反主键唯一约束，删除导的表之后只能truncate导入，不过需要询问需求是否可以
• 游标的使用
  • 游标四大属性
    • 属性 返回值类型 作用 sql%isopen 布尔型 判断游标是否 '开启' sql%found 布尔型 判断游标是否 '获取' 到值 sql%notfound 布尔型 判断游标是否 '没有获取' 到值（常用于 "退出循环"） sql%rowcount 整型 '当前' 成功执行的数据行数（非 "总记录数"） sql%rowcount sql%rowtype定义一个变量，其数据类型与已经定义的某个 数据变量的类型相同，或者与数据库表的某个列的数据类型相同，这时可以使用%TYPE。
  • declare游标删除指定数据
    • ------------------使用游标写一个数据块，使每删除20000条数据提交一次。 --方法一（好理解，但是日志表不完全） declare cursor cur is select rowid row_id from EMSS_ASC.DEV a where a.dev_cls = '02'; v_count number; begin v_count := 0; for pp in cur loop delete/*+PARALLEL(16)*/ from EMSS_ASC.DEV b where rowid= pp.row_id; v_count := v_count + 1; if (v_count >= 20000) then commit; v_count := 0; end if; end loop; insert into dec_log values('EMSS_ASC','DEV',v_count); commit; end; ----方法二（ 日志表较完整） declare v_index number:=0; out_msg varchar2(3000); begin for i in (select rownum row_id from sgpm.test_s) loop BEGIN v_index:=v_index+1; delete from EMSS_ASC.DEV where dev_cls = '02' and rowid=i.row_id; if v_index=20000 then insert into dec_log values('EMSS_ASC','DEV',v_index); v_index:=0; commit; end if; EXCEPTION WHEN OTHERS THEN out_msg := substr(SQLERRM,1,256); INSERT INTO DEC_ERR_LOG VALUES('EMSS_ASC','DEV',out_msg); END; end loop; insert into dec_log values('EMSS_ASC','DEV',v_index); commit; end;
  • 展示游标在库中最大的占用
    • show parameter open
• Oracle迁移的存储过程
  • create or replace package body PKG_DB1_CUST_SER_Lilei is /***************************************************** #procedure: 业扩与计量DB4-业扩-电能替代设备/Connection #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: #CREATEDATE: 2021-03-22 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KH_E_SAVE_PROJ_TECH_DTL(v_part varchar2) as v_pkg_name varchar2(256) := 'PKG_DB1_CUST_SER_Lilei'; --包名 v_proc_name varchar2(256) := 'P_DB1_KH_E_SAVE_PROJ_TECH_DTL'; --过程名 v_tab_name varchar2(256) := 'E_SAVE_PROJ_TECH_DTL'; --表名 out_code varchar2(512); out_msg varchar2(512); IN_DATE varchar2(64); IN_ORG_NO varchar2(64); v_bgn_date date := sysdate; v_end_date date; v_count number; v_num number(16); v_index number; v_cust_id number; begin out_code := 1; out_msg := 'ok'; EXECUTE IMMEDIATE 'TRUNCATE TABLE EMSS_PJC.E_SAVE_PROJ_TECH_DTL'; INSERT INTO EMSS_PJC.E_SAVE_PROJ_TECH_DTL ( E_SAVE_PROJ_TECH_DTL_ID ,-- 属性描述：技术明细标识是指本实体记录的唯一标识，产生规则为流水号 PROJ_ID_TYPE ,-- 属性描述：项目标识类型是指节能项目技术明细列表的项目标识类型，引用国家电网公司营销管理代码类集：项目标识类型/PROJ_ID_TYPE，枚举如：节能潜力项目、节能项目 E_SAVE_PROJ_ID,-- 属性描述：项目标识是指节能项目技术明细列表对应类型的项目标识 PROJ_NO ,-- 属性描述：项目编号是指节能项目技术明细列表对应项目的唯一编号 DTL_TYPE -- "属性描述：技术明细类别是指节能项目技术明细列表对应项目的技术类别，引用国家电网公司营销管理代码类集：技术明细类/proj_type，枚举如： ) SELECT detail_id ,--技术明细标识：本实体记录的唯一标识，产生规则为流水号 proj_id_type ,-- 项目标识类型：01潜力项目 02节能项目 03自身及推动项目 04被测评项目 proj_id ,-- 对应类型的项目标识 proj_no ,--项目编号 detail_type -- "技术明细类 FROM WW11.S_PROJ_TECHG_DETAIL; COMMIT; --日志 v_count := sql%rowcount; v_end_date := sysdate; pkg_db_trans_pub_log.p_db_log_pDtLog(v_pkg_name, v_proc_name, v_tab_name, IN_ORG_NO, IN_DATE, out_code, v_count, out_msg, v_bgn_date, v_end_date); COMMIT; exception when others then out_code := 0; out_msg := '失败！' || SQLERRM; v_end_date := sysdate; v_count := 0; pkg_db_trans_pub_log.p_db_log_pDtLog(v_pkg_name, v_proc_name, v_tab_name, IN_ORG_NO, IN_DATE, out_code, v_count, out_msg, v_bgn_date, v_end_date); END P_DB1_KH_E_SAVE_PROJ_TECH_DTL; /***************************************************** #procedure: 业扩与计量DB4-业扩-电能替代设备/Connection #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: #CREATEDATE: 2021-03-22 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KH_E_SAVE_PROJ_THEO(v_part varchar2) as v_pkg_name varchar2(256) := 'PKG_DB1_CUST_SER_Lilei'; --包名 v_proc_name varchar2(256) := 'P_DB1_KH_E_SAVE_PROJ_THEO'; --过程名 v_tab_name varchar2(256) := 'E_SAVE_PROJ_THEO'; --表名 out_code varchar2(512); out_msg varchar2(512); IN_DATE varchar2(64); IN_ORG_NO varchar2(64); v_bgn_date date := sysdate; v_end_date date; v_count number; v_num number(16); v_index number; v_cust_id number; begin out_code := 1; out_msg := 'ok'; EXECUTE IMMEDIATE 'TRUNCATE TABLE EMSS_PJC.E_SAVE_PROJ_TECH_DTL'; INSERT INTO EMSS_PJC.E_SAVE_PROJ_THEO ( e_save_proj_theo_id ,--属性描述：理论标识是指本实体记录的唯一标识，产生规则为流水号 e_save_proj_id ,--属性描述：项目标识是指本实体记录的唯一标识，产生规则为流水号 e_save_proj_no,-- 属性描述：项目编号是指节能项目理论值的唯一编号 dtl_type,-- "属性描述：技术明细类是指节能项目使用的技术类型， para_id,-- 属性描述：参数id是指节能项目理论值参数的id para_stat,-- 属性描述：参数状态是指节能项目理论值参数的状态，引用国家电网公司营销管理代码类集：参数状态/para_stat，枚举如基期（改造前）、报告期（改造后）、统计参数、项目年节约电量、项目年节约电力 para_name,-- 属性描述：参数名称是指节能项目理论值参数的名称 para_val,-- 属性描述：参数值是指节能项目理论值参数的值 para_type-- 属性描述：参数类型是指节能项目理论值参数的类型，引用国家电网公司营销管理代码类集：参数类型/para_type，枚举如必填项、选填项、计算项、合计项、系数 ) SELECT theory_id ,--理论标识：本实体记录的唯一标识，产生规则为流水号 proj_id ,--项目标识：本实体记录的唯一标识，产生规则为流水号 proj_no ,--项目编号 detail_type,-- "技术明细类包括：如果项目类别为“供配电节能类”，则技术明细类包括：无功电压优化调节、变电站无功补偿项目、变压器经济运行、高效变压器应用、线路改造、节能金具应用、升压改造、电能质量治理项目； para_id,--参数id para_status,-- 参数状态包括：基期（改造前）、报告期（改造后）、统计参数、项目年节约电量、项目年节约电力 para_name,-- 参数名称 para_value ,--参数值 para_type--参数类型包括：必填项、选填项、计算项、合计项、系数 FROM WW11.S_PROJ_THEORY; COMMIT; --日志 v_count := sql%rowcount; v_end_date := sysdate; pkg_db_trans_pub_log.p_db_log_pDtLog(v_pkg_name, v_proc_name, v_tab_name, IN_ORG_NO, IN_DATE, out_code, v_count, out_msg, v_bgn_date, v_end_date); COMMIT; exception when others then out_code := 0; out_msg := '失败！' || SQLERRM; v_end_date := sysdate; v_count := 0; pkg_db_trans_pub_log.p_db_log_pDtLog(v_pkg_name, v_proc_name, v_tab_name, IN_ORG_NO, IN_DATE, out_code, v_count, out_msg, v_bgn_date, v_end_date); END P_DB1_KH_E_SAVE_PROJ_THEO; end PKG_DB1_CUST_SER_Lilei;
• 工作中多个过程放在一个包体里的实例
  • create or replace package body PKG_DB1_KF_WULI is function f_db1_kf_getMonth(IN_DATE varchar2) return varchar2 is /***************************************************** #procedure: 客户与综合db1-客服-根据日期返回月份 #param IN_DATE 需要进行转换时间戳 #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_函数名 *****************************************************/ rtn_month varchar2(6) := ''; begin select substr(IN_DATE, 1, 6) into rtn_month from dual; return rtn_month; end f_db1_kf_getMonth; function f_db1_kf_getLogService(IN_PKG_NAME varchar2, IN_proc_name varchar2, IN_tab_name varchar2, in_org_no varchar2, IN_YM varchar2) return number is /***************************************************** #procedure: 获取数据转接的日志ID #param IN_PKG_NAME 包名 #param IN_proc_name 过程名 #param IN_tab_name 表名 #param in_org_no 单位代码 #param IN_YM 年月或者日期 #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_函数名 *****************************************************/ V_logID number(24); begin select nvl(max(F.EDIT_ID), 0) INTO V_logID from SJGC.p_dt_log f where f.PKG_NAME = in_pkg_name AND F.PROC_NAME = in_proc_name AND F.TAB_NAME = in_tab_name AND (F.ORG_NO = in_org_no or F.ORG_NO is null) AND (F.YM = IN_YM or F.YM is null) AND F.SUCC_FLAG IS NULL; return V_logID; end f_db1_kf_getLogService; procedure p_db1_kf_pDtLog_beg(IN_PKG_NAME varchar2, IN_proc_name varchar2, IN_tab_name varchar2, in_org_no varchar2, IN_YM varchar2) is /******************************************************************** #procedure: 保存开始执行日志 #param IN_PKG_NAME 包名 #param IN_proc_name 过程名 #param IN_tab_name 表名 #param in_org_no 单位代码 #param IN_YM 年月或者日期 #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 ********************************************************************/ begin INSERT INTO SJGC.p_dt_log (edit_id, pkg_name, proc_name, tab_name, org_no, ym, begin_time) select SEQ_P_DT_LOG_ID.NEXTVAL, in_pkg_name, in_proc_name, in_tab_name, in_org_no, IN_YM, SYSDATE FROM DUAL; commit; end p_db1_kf_pDtLog_beg; procedure p_db1_kf_pDtLog_end(in_logID NUMBER, in_SUCC_FLAG varchar2, in_DATA_NUM number, in_out_desc varchar2) is /******************************************************************** #procedure: 保存结束执行日志 #param in_logID 日志ID #param in_SUCC_FLAG 成功标志 #param in_DATA_NUM 数据量 #param in_out_desc 执行结果说明 #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 ********************************************************************/ begin UPDATE SJGC.p_dt_log F SET F.END_TIME = SYSDATE, F.SUCC_FLAG = in_SUCC_FLAG, F.DATA_NUM = in_DATA_NUM, F.OUT_DESC = in_out_desc WHERE F.EDIT_ID = in_logID; COMMIT; end p_db1_kf_pDtLog_end; /******************************************************************** #procedure: 无入参迁移 #VERSION : V1.0 #AUTHOR: CHENYINGJUN #CREATEDATE: 2021-03-30 #modifyexplain:无任何入参 ********************************************************************/ procedure P_TRANS_JOB_NULL(out_code out number, out_msg out varchar2) is begin pkg_db5_kf_chenyingjun.p_db5_kf_impt_srv_rpt_rec(in_org_no => '', in_date => '', out_code => out_code, out_msg => out_msg); END; /***************************************************** #procedure: 客户与综合DB1-客服-订阅单 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_SUB_FORM(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_SUB_FORM'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'SUB_FORM'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- -- execute immediate 'TRUNCATE TABLE EMSS_CUC.SUB_FORM'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ INSERT INTO emss_cuc.SUB_FORM /*订阅单*/ (SUB_FORM_ID, SUB_ITEM, SUB_MODE, RCV_MODE, ACPT_CONT, SUB_TYPE, SEND_TIME_SEC, SUB_TIME, SEND_FREQ, SUB_FORM_NO, SUB_FORM_NAME, SUB_PERSON, UNSUBSCRIBE_FLAG, SUB_RCV_TRGT, END_DATE, BEG_DATE, SUB_VALID_FLAG, BP_BUS_NO, CUST_NO, CTLG_NO, REG_DATE, APP_NO) select /*+ parallel(16)*/ SR_SUBSCRIBE_ID as SUB_FORM_ID, --订阅单标识 pkg_db5_kf_chenyingjun.F_P_CODE('subscibeCont', ITEM_CODE) as SUB_ITEM, --订阅项目 null as SUB_MODE, --订阅方式 pkg_db5_kf_chenyingjun.F_P_CODE('rcvMode', RCV_MODE) as RCV_MODE, --接收方式 null as ACPT_CONT, --受理内容 null as SUB_TYPE, --订阅类型 pkg_db5_kf_chenyingjun.F_P_CODE('timeInterval', SEND_TS) as SEND_TIME_SEC, --发送时段 SUBSCRIBE_DATE as SUB_TIME, --订阅时间 null as SEND_FREQ, --发送频度 null as SUB_FORM_NO, --订阅单编号 null as SUB_FORM_NAME, --订阅单名称 null as SUB_PERSON, --订阅人员 decode(VALID_FLAG, '03', '1', '0') as UNSUBSCRIBE_FLAG, --退订标志 RCV_OBJ as SUB_RCV_TRGT, --订阅接收目标 END_DATE as END_DATE, --订阅结束日期 BGN_DATE as BEG_DATE, --订阅开始日期 decode(VALID_FLAG, '01', '1', '02', '0', '0') SUB_VALID_FLAG, --订阅有效标志 null as BP_BUS_NO, --业务伙伴编号 CUST_NO as CUST_NO, --客户编号 null as CTLG_NO, --目录编号 REG_DATE as REG_DATE, --退订时间 APP_NO as APP_NO --申请编号 FROM S_SUBSCIBE_CONT; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-订阅发送记录 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_SUB_SEND_REC(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_SUB_SEND_REC'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'SUB_SEND_REC'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.SUB_SEND_REC'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ INSERT INTO emss_cuc.SUB_SEND_REC /*订阅发送记录*/ (SUB_INFO_ID, SEND_TIME, RCV_MODE, RCV_TRGT, SUB_ITEM, SEND_CONT, MGT_ORG_CODE, SEND_TIME_SEC, SEND_STAT, BP_BUS_NO, FAIL_REASON, SUCC_FLAG, SENDER_NO, SEND_ID) select /*+ parallel(16)*/ SEND_RSLT_ID as SUB_INFO_ID, --订阅信息标识 SEND_DATE as SEND_TIME, --发送时间 null as RCV_MODE, --接收方式 null as RCV_TRGT, --接收目标 null as SUB_ITEM, --订阅项目 null as SEND_CONT, --发送内容 null as MGT_ORG_CODE, --管理单位编码 null as SEND_TIME_SEC, --发送时段 null as SEND_STAT, --发送状态 null as BP_BUS_NO, --业务伙伴编号 FAIL_REASON as FAIL_REASON, --发送失败原因 decode(SUCC_FLAG, 'true', 'true', 'false') as SUCC_FLAG, --发送成功标志 SENDER_NO as SENDER_NO, --订阅发送人员 SEND_ID AS SEMD_ID --订阅发送标识 FROM S_SUBSCIBE_SEND_RSLT; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-服务客户清单 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_SRV_CUST_FORM(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_SRV_CUST_FORM'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'SRV_CUST_FORM'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.SRV_CUST_FORM'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ INSERT INTO EMSS_OPC.SRV_CUST_FORM /*服务客户清单*/ (SRV_CONS_FORM_ID, SAFETY_SRV_PLAN_ID, CUST_NO, CUST_NAME, SRV_CYC, LAST_CHAN_SRV_TIME, PLAN_CHK_DATE, EC_ADDR, EC_CHKR, EC_CATEG, VOLT, CTRT_CAP, MGT_ORG_CODE, CHK_STAT ) select /*+ parallel(16)*/ cons_id as SRV_CONS_FORM_ID, --服务用户单标识 null as SAFETY_SRV_PLAN_ID, --安全服务计划标识 null as CUST_NO, -- 客户编号 null as CUST_NAME, --客户名称 CHK_CYCLE as SRV_CYC, --服务周期 LAST_CHK_DATE as LAST_CHAN_SRV_TIME, --最后一次服务时间 null as PLAN_CHK_DATE, --计划检查日期 null as EC_ADDR, --用能地址 null as EC_CHKR, --用能检查员 null as EC_CATEG, --用能类别 null as VOLT, --承压 null as CTRT_CAP, --合同容量 null as MGT_ORG_CODE, --管理单位编码 null as CHK_STAT --检查状态 FROM C_CONS; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-互联网用户 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_NET_USER(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_NET_USER'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'NET_USER'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- -- execute immediate 'TRUNCATE TABLE EMSS_CUC.NET_USER' EMSS_CUC.NET_USER WHERE 1 = 1; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ execute immediate 'TRUNCATE TABLE EMSS_CUC.NET_USER'; insert into EMSS_CUC.NET_USER (NET_USER_ID, /*互联网用户标识*/ NET_USER_NO, /*互联网用户编号*/ NET_USER_NAME, /*互联网用户名称*/ MOBILE_NUMBER, /*手机号码*/ EMAIL_ADDR, /*邮箱地址*/ PWD, /*加密密码*/ LOCK_END_TIME, /*锁定结束时间*/ ID_CARD_NUMBER, /*身份证号码*/ ENTP_USERS_FLAG, /*企事业用户标志*/ UNIFIED_SOC_CR_CODE, /*统一社会信用代码*/ REAL_NAME_AUTH_FLAG, /*实名认证标志*/ BP_ID, /*业务伙伴标识*/ CUST_ID, /*客户标识*/ REG_TIME, /*注册时间*/ REG_CHAN, /*注册渠道*/ ELEC_SALE_CO_FLAG, /*售电公司标志*/ THIRD_ACC_NO ， --第三方账号 CONS_REG_STAT ， --用户注册状态 REMARK ， --备注 PROV_CODE ， --省码 CITY_CODE ， --市码 CNTY_CODE ， --县码 PW_UPD_TIME ， --密码更新时间 LAST_UPD_TIME ， --最后更新时间 LOGIN_ACCT ， --账户登录名 USED_ADDR ， --常用地址 ACCESS_NO ， --接入方编号 TENANT_NO ， --租户编号 DEVCICE_NO ， --设备编号 SCORE ， --成长值 MEMBER_LEVEL_CODE ， --会员等级编码 POINT ， --积分 MEMBER_SIGN ， --会员状态 USER_SRC ， --用户来源 PHOTO) select /*+ parallel(16)*/ /*a.CUST_ID*/ lrs_sequence.nextval as NET_USER_ID, /*互联网用户标识*/ a.CUST_NO as NET_USER_NO, /*互联网用户编号*/ a.NAME as NET_USER_NAME, /*互联网用户名称*/ substr(c.MOBILE, 1, 5) as MOBILE_NUMBER, /*手机号码*/ substr(c.EMAIL, 1, 5) as EMAIL_ADDR, /*邮箱地址*/ a.QUERY_PWD as PWD, /*加密密码*/ null as LOCK_END_TIME, /*锁定结束时间*/ b.cert_no as ID_CARD_NUMBER, /*身份证号码*/ null as ENTP_USERS_FLAG, /*企事业用户标志*/ b.USCI as UNIFIED_SOC_CR_CODE, /*统一社会信用代码*/ null as REAL_NAME_AUTH_FLAG, /*实名认证标志*/ a.CUST_ID as BP_ID, /*业务伙伴标识*/ null as CUST_ID, /*客户标识*/ null as REG_TIME, /*注册时间*/ null as REG_CHAN, /*注册渠道*/ null as ELEC_SALE_CO_FLAG, /*售电公司标志*/ null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null from C_CUST a, C_CERT b, C_CONTACT c where A.CUST_ID = B.CUST_ID(+) and b.CUST_ID = c.CUST_ID(+) and B.CERT_TYPE_CODE = '01'; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-检查结果单 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_CHK_RSLT_FORM(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_CHK_RSLT_FORM'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'CHK_RSLT_FORM'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.CHK_RSLT_FORM'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert into EMSS_OPC.CHK_RSLT_FORM (CHK_RSLT_LIST_ID, /*检查结果单标识*/ SAFETY_SRV_PLAN_ID, /*安全服务计划标识*/ SRV_CONS_FORM_ID, /*服务用户单标识*/ PLAN_NO, /*计划编号*/ CUST_NO, /*客户编号*/ CHK_CATEG, /*检查类别*/ EXIST_PROB, /*存在问题*/ CHK_RSLT, /*检查结果*/ CHKR, /*检查人员*/ OTHER_STF, /*其他人员*/ CHK_DATE, /*检查日期*/ E_C_ADDR, /*用能地址*/ CHK_RSLT_CATEG, /*检查结果类别*/ RECT_SUG, /*整改建议*/ VERI_OPN, /*核定意见*/ RELA_APP_NO, /*关联申请编号*/ ATTACH_ID, /*附件标识*/ exist_prob_attach, /*存在问题图片*/ breach_ctrt_ea_flag, /*违约用电标志*/ meter_prob_flag, /*计量异常标志*/ price_exec_prob_flag, /*电价执行错误标志*/ dev_dfct_flag, /*设备缺陷标志*/ breach_ctrt_ea_attach, /*违约用电附件*/ meter_prob_attach, /*计量异常附件*/ price_exec_prob_attach, /*电价执行错误附件*/ exist_prob_flag, /*存在问题标志*/ cust_sign_attach, /*用户签字附件*/ EQUIPMENT_DEFECT_ATTACH_ID, /*设备缺陷图片标识*/ FIELD_INSPECTION_ATTACH_ID, /*现场检查图片标识*/ BREACH_CTRT_ACTN, --违约行为 BREACH_CTRT_ELEC_STEAL_ACTN_FLAG, --违约用电窃电行为标志 BREACH_CTRT_TYPE --违约类型 ) select /*+ parallel(16)*/ SEQUENCE_WULI_KEFU2.Nextval as CHK_RSLT_LIST_ID, /*检查结果单标识*/ null as SAFETY_SRV_PLAN_ID, /*安全服务计划标识*/ null as SRV_CONS_FORM_ID, /*服务用户单标识*/ null as PLAN_NO, /*计划编号*/ null as CUST_NO, /*客户编号*/ null as CHK_CATEG, /*检查类别*/ substr(a.PROBLEM, 1, 84) as EXIST_PROB, /*存在问题*/ a.CHK_RSLT as CHK_RSLT, /*检查结果*/ /*CHECKER_NAME*/ null as CHKR, /*检查人员*/ a.OTHERPERSON_NAME as OTHER_STF, /*其他人员*/ TO_DATE(a.CHK_DATE, 'yyyy-mm-dd hh24:mi:ss') as CHK_DATE, /*检查日期*/ null as E_C_ADDR, /*用能地址*/ a.MR_EXCP_FLAG || a.PRC_MISTAKE_FLAG as CHK_RSLT_CATEG, /*检查结果类别*/ null as RECT_SUG, /*整改建议*/ null as VERI_OPN, /*核定意见*/ null as RELA_APP_NO, /*关联申请编号*/ null as ATTACH_ID, /*附件标识*/ null as exist_prob_attach, /*存在问题图片*/ null as breach_ctrt_ea_flag, /*违约用电标志*/ null as meter_prob_flag, /*计量异常标志*/ null as price_exec_prob_flag, /*电价执行错误标志*/ null as dev_dfct_flag, /*设备缺陷标志*/ null as breach_ctrt_ea_attach, /*违约用电附件*/ null as meter_prob_attach, /*计量异常附件*/ null as aprice_exec_prob_attach, /*电价执行错误附件*/ null as exist_prob_flag, /*存在问题标志*/ null as cust_sign_attach, /*用户签字附件*/ null as EQUIPMENT_DEFECT_ATTACH_ID, /*设备缺陷图片标识*/ null as FIELD_INSPECTION_ATTACH_ID, /*现场检查图片标识*/ b.TYPE_CODE, a.VIOLATE_FLAG, b.ATTR_CODE from S_INSPECT_RSLT a, SGPM.S_POWER_VIOLATE_STEAL b where a.id = b.id(+); ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-能源事故档案 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_ENERGY_ACDNT_ARCH(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_ENERGY_ACDNT_ARCH'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'ENERGY_ACDNT_ARCH'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.ENERGY_ACDNT_ARCH'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_OPC.ENERGY_ACDNT_ARCH (ACDNT_ARCH_ID, /*事故档案标识*/ LINE_ID, /*线路标识*/ CNTRL_STA_ID, /*枢纽站标识*/ FAULT_TYPE, /*故障类型*/ FAULT_REASON, /*故障原因*/ FAULT_TIME, /*故障时间*/ RCVR_ES_TIME, /*恢复供能时间*/ DED_LINE_CONS_FLAG, /*专线用户标志*/ RECLOSING_SUCC_FLAG, /*重合成功标志*/ EVNT_LV, /*事件等级*/ EVNT_CLS, /*事件分类*/ REASON_DESC, /*原因描述*/ MAINTR, /*维护人*/ MAINT_TIME, /*维护时间*/ CUST_NO, /*客户编号*/ BF_RUN_MODE, /*事故前运行方式*/ LOC, /*事故地点*/ ACCI_DESC, /*事故情况*/ ACDNT_ID, /*客户事故编号*/ LPQ, /*损失电量*/ CHK_ID, /*检查结果标识*/ ECONOMIC_LOSS, /*经济损失*/ ENERGY_ACDNT_ARCH_ATTACH_ID, /*附件标识*/ PLAN_NO, /*计划编号*/ OCCUR_TIME, /*事故发生时间*/ ENERGY_ACDNT_ARCH_ATTACH_DESC /*附件描述*/ ) select ID AS ACDNT_ARCH_ID, /*事故档案标识*/ NULL AS LINE_ID, /*线路标识*/ NULL AS CNTRL_STA_ID, /*枢纽站标识*/ null AS FAULT_TYPE, /*故障类型*/ /*substr(REASON, 1, 5)*/ substr(REASON, 1, 5) AS FAULT_REASON, /*故障原因*/ TO_DATE(OCCUR_DATE, 'yyyy-mm-dd hh24:mi:ss') AS FAULT_TIME, /*故障时间*/ TO_DATE(POWERON_DATE, 'yyyy-mm-dd hh24:mi:ss') AS RCVR_ES_TIME, /*恢复供能时间*/ NULL AS DED_LINE_CONS_FLAG, /*专线用户标志*/ NULL AS RECLOSING_SUCC_FLAG, /*重合成功标志*/ /*substr(LEVEL_CODE, 1, 2)*/ substr(pkg_db5_kf_chenyingjun.F_P_CODE('AuditEventLevel', LEVEL_CODE), 1, 2) AS EVNT_LV, /*事件等级*/ pkg_db5_kf_chenyingjun.F_P_CODE('teEventSortCode', TYPE_CODE) AS EVNT_CLS, /*事件分类*/ NULL AS REASON_DESC, /*原因描述*/ NULL AS MAINTR, /*维护人*/ NULL AS MAINT_TIME, /*维护时间*/ NULL AS CUST_NO, /*客户编号*/ BF_RUN_MODE AS BF_RUN_MODE, /*事故前运行方式*/ LOC AS LOC, /*事故地点*/ ACCI_DESC AS ACCI_DESC, /*事故情况*/ ID AS ACDNT_ID, /*客户事故编号*/ LPQ AS LPQ, /*损失电量*/ ID2 AS CHK_ID, /*检查结果标识*/ /*TO_CHAR(ECONOMIC_LOSS)*/ ECONOMIC_LOSS AS ECONOMIC_LOSS, /*经济损失*/ NULL AS ENERGY_ACDNT_ARCH_ATTACH_ID, /*附件标识*/ NULL AS PLAN_NO, /*计划编号*/ OCCUR_TIME AS OCCUR_TIME, /*事故发生时间*/ NULL AS ENERGY_ACDNT_ARCH_ATTACH_DESC /*附件描述*/ FROM S_POWER_ACCI; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-派工明细 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_DISP_DTL(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_DISP_DTL'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'DISP_DTL'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.DISP_DTL'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_OPC.DISP_DTL (ASGN_DTL_ID, /*派工明细标识*/ CUST_NO, /*客户编号*/ APP_NO, /*申请编号*/ INSTANCE, /*活动实例标识*/ STEP_NO, /*环节编号*/ ASGN_TIME, /*派工时间*/ E_SAFE_STAFF, /*保能人员*/ ASGN_NO, /*派工人员编号*/ ASGN_NAME, /*派工人员名称*/ ASGN_DATE, /*派工日期*/ ASGN_DEPT_NO, /*派工部门编号*/ ASGN_DEPT_NAME, /*派工部门名称*/ MGT_ORG_CODE, /*管理单位编码*/ E_SAFE_TEAM, /*保能班组*/ E_SAFE_LEADER /*保能班长*/) select ASSGIGN_ID as ASGN_DTL_ID, /*派工明细标识*/ null as CUST_NO, /*客户编号*/ APP_NO as APP_NO, /*申请编号*/ INSTANCE_ID as INSTANCE, /*活动实例标识*/ null as STEP_NO, /*环节编号*/ ASSIGN_DATE as ASGN_TIME, /*派工时间*/ null as E_SAFE_STAFF, /*保能人员*/ ASSIGNER as ASGN_NO, /*派工人员编号*/ null as ASGN_NAME, /*派工人员名称*/ null as ASGN_DATE, /*派工日期*/ DEPT_NO as ASGN_DEPT_NO, /*派工部门编号*/ null as ASGN_DEPT_NAME, /*派工部门名称*/ ORG_NO as MGT_ORG_CODE, /*管理单位编码*/ null as E_SAFE_TEAM, /*保能班组*/ null as E_SAFE_LEADER /*保能班长*/ from P_TASK_ASSIGN; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-信用等级 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_CR_LV_RULE(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_CR_LV_RULE'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'CR_LV_RULE'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.CR_LV_RULE'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.CR_LV_RULE (CR_LV_ID, /*信用等级标识*/ CR_LV_NO, /*信用等级编号*/ DIS_CR_EXEC_DATE, /*失信执行期*/ CR_SCORE_UL, /*信用上限分*/ CR_SCORE_LL, /*信用下限分*/ CR_LV /*信用等级*/) select PRIO_ID as CR_LV_ID, /*信用等级标识*/ null as CR_LV_NO, /*信用等级编号*/ null as DIS_CR_EXEC_DATE, /*失信执行期*/ INTERVAL_UL as CR_SCORE_UL, /*信用上限分*/ INTERVAL_LL as CR_SCORE_LL, /*信用下限分*/ EVAL_LEVEL_CODE as CR_LV /*信用等级*/ from S_CR_GRADE_STD; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-信用规则 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_CR_RULE(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_CR_RULE'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'CR_RULE'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.CR_RULE'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.CR_RULE (CR_RULE_ID, /*信用规则标识*/ CR_RULE_NO, /*信用规则编号*/ CR_RULE_NAME, /*信用规则名称*/ RULE_STAT, /*规则状态*/ RULE_CONT_DESC, /*规则内容描述*/ ADJ_CR_SCORE, /*信用增减分*/ DATA_GEN_MODE, /*数据生成方式*/ RULE_TYPE, /*规则类型*/ RULE_CLS, /*规则分类*/ RULE_FOR_CROWD, /*规则适用人群*/ MGT_ORG_CODE, /*管理单位编码*/ VAL_DATA_GEN_TIME, /*验证数据生成时间*/ VAL_DATA_GEN_STAT, /*验证数据生成状态*/ CR_LV_ID, /*信用等级标识*/ VALID_DATE, /*生效日期*/ INVALID_DATE, /*失效日期*/ APP_NO, /*申请编号*/ APP_TIME, /*申请时间*/ APP_DESC, /*申请说明*/ APPLNT, /*申请人*/ ALG_SRV_ID /*算法服务配置标识*/) select ID as CR_RULE_ID, /*信用规则标识*/ null as CR_RULE_NO, /*信用规则编号*/ NAME as CR_RULE_NAME, /*信用规则名称*/ pkg_db5_kf_chenyingjun.F_P_CODE('measRuleStatus', STATUS) as RULE_STAT, /*规则状态*/ RULE_DESC as RULE_CONT_DESC, /*规则内容描述*/ null as ADJ_CR_SCORE, /*信用增减分*/ null as DATA_GEN_MODE, /*数据生成方式*/ null as RULE_TYPE, /*规则类型*/ pkg_db5_kf_chenyingjun.F_P_CODE('ruleTypeCode', SORT_CODE) as RULE_CLS, /*规则分类*/ /*SUBSTR(CUST_GROUP_ID, 1, 8)*/ SUBSTR(CUST_GROUP_ID, 1, 8) as RULE_FOR_CROWD, /*规则适用人群*/ PS_ORG_NO as MGT_ORG_CODE, /*管理单位编码*/ null as VAL_DATA_GEN_TIME, /*验证数据生成时间*/ null as VAL_DATA_GEN_STAT, /*验证数据生成状态*/ null as CR_LV_ID, /*信用等级标识*/ START_DATE as VALID_DATE, /*生效日期*/ EXPIRE_DATE as INVALID_DATE, /*失效日期*/ APP_NO as APP_NO, /*申请编号*/ CREATE_TIME as APP_TIME, /*申请时间*/ null as APP_DESC, /*申请说明*/ CREATE_TIME as APPLNT, /*申请人*/ null as ALG_SRV_ID /*算法服务配置标识*/ from S_CR_EVAL_RULE; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-预防性试验档案 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_PRVNT_TRIAL_ARCH(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_PRVNT_TRIAL_ARCH'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'PRVNT_TRIAL_ARCH'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.PRVNT_TRIAL_ARCH'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_OPC.PRVNT_TRIAL_ARCH (PRVNT_TRIAL_ID, /*预防性试验标识*/ CHK_ID, /*检查结果标识*/ TEST_ID, /*试验编号*/ CREATION_DATE, /*创建日期*/ CREATOR, /*创建人*/ TRIAL_PROJ, /*试验项目*/ TRIAL_ORG, /*试验单位*/ TRIAL_STAFF, /*试验人员*/ TRIAL_DATE, /*试验日期*/ TRIAL_CONT, /*试验内容*/ TRIAL_RSLT, /*试验结果*/ EQPT_ID, /*设备标识*/ TRIAL_RPT, /*试验报告*/ DFCT_SITU /*缺陷情况*/) select TEST_ID as PRVNT_TRIAL_ID, /*预防性试验标识*/ ID as CHK_ID, /*检查结果标识*/ TEST_ID as TEST_ID, /*试验编号*/ null as CREATION_DATE, /*创建日期*/ null as CREATOR, /*创建人*/ ITEM_CODE as TRIAL_PROJ, /*试验项目*/ TEST_ORG as TRIAL_ORG, /*试验单位*/ TESTER as TRIAL_STAFF, /*试验人员*/ TEST_DATE as TRIAL_DATE, /*试验日期*/ CONTENT as TRIAL_CONT, /*试验内容*/ TEST_RSLT as TRIAL_RSLT, /*试验结果*/ EQUIP_ID as EQPT_ID, /*设备标识*/ null as TRIAL_RPT, /*试验报告*/ null as DFCT_SITU /*缺陷情况*/ from S_PREVENTIVE_TEST; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-业务伙伴信用 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_BP_CR(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_BP_CR'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'BP_CR'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.BP_CR'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.BP_CR (BP_CR_ID, /*业务伙伴信用标识*/ BP_ID, /*业务伙伴标识*/ CR_LV_ID, /*信用等级标识*/ CR_SCORE, /*信用分*/ STATS_YM, /*统计年月*/ MGT_ORG_CODE, /*管理单位编码*/ DIS_CR_START_TIME, /*失信开始时间*/ DIS_CR_EXPR_TIME, /*失信到期时间*/ NOT_RPT_REASON, /*不上报原因*/ RPT_FLAG, /*上报标志*/ VALID_FLAG, /*有效标志*/ CR_REF_LV /*参考信用等级*/) select null as BP_CR_ID, /*业务伙伴信用标识*/ null as BP_ID, /*业务伙伴标识*/ ID as CR_LV_ID, /*信用等级标识*/ SCORE as CR_SCORE, /*信用分*/ null as STATS_YM, /*统计年月*/ null as MGT_ORG_CODE, /*管理单位编码*/ null as DIS_CR_START_TIME, /*失信开始时间*/ null as DIS_CR_EXPR_TIME, /*失信到期时间*/ null as NOT_RPT_REASON, /*不上报原因*/ null as RPT_FLAG, /*上报标志*/ null as VALID_FLAG, /*有效标志*/ EVAL_LEVEL_CODE as CR_REF_LV /*参考信用等级*/ from S_BUSI_PARTNER_EVAL; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-户主认证申请单 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_PPY_AUTH_APP(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_PPY_AUTH_APP'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'PPY_AUTH_APP'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.PPY_AUTH_APP'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.PPY_AUTH_APP (PPY_AUTH_APP_ID, /*户主认证申请单标识*/ CSC_SRV_CODE, /*国网客户服务中心服务编码*/ CSC_RQST_NO, /*国网客户服务中心请求编号*/ AUTH_APP_CHAN, /*认证申请渠道*/ AUTH_CHAN_CODE, /*认证渠道编码*/ AUTH_FUNC_CODE, /*认证功能编码*/ NET_USER_ID, /*互联网用户标识*/ BP_ID, /*业务伙伴标识*/ CHAN_ACCT_ID, /*渠道账户标识*/ USER_NAME, /*账户名称*/ PROM_TYPE, /*推广类型*/ PROM_CHAN_CODE, /*推广渠道编码*/ BUS_PRE_APP_NO, /*预申请编号*/ APP_NO, /*申请编号*/ APP_YM, /*申请年月*/ APP_TIME, /*申请时间*/ MGT_ORG_CODE, /*管理单位编码*/ MGT_ORG_NAME, /*管理单位名称*/ CUST_ID, /*客户标识*/ CUST_NO, /*客户编号*/ CUST_CATEG, /*客户类别*/ CUST_CLS, /*客户分类*/ CONTACT_TYPE, /*联系人类型*/ CONTACT, /*联系人*/ ID_CARD_NUMBER, /*身份证号码*/ MOBILE_NUMBER, /*手机号码*/ HNDL_FLAG, /*处理标志*/ VERIFIER, /*审核人*/ VERF_ORG, /*审核单位*/ VERF_TIME, /*审核时间*/ VERF_CONT, /*审核意见*/ VERF_RSLT, /*审核结果*/ DISQUAL_CODE, /*不通过原因代码*/ DISQUAL_NAME, /*不通过原因名称*/ MSG_CONT, /*消息内容*/ PROC_STAT /*流程状态*/) select a.APP_RNRG_ID || b.app_rnrg_id as PPY_AUTH_APP_ID, /*户主认证申请单标识*/ null as CSC_SRV_CODE, /*国网客户服务中心服务编码*/ null as CSC_RQST_NO, /*国网客户服务中心请求编号*/ null as AUTH_APP_CHAN, /*认证申请渠道*/ a.CHANNEL_CODE as AUTH_CHAN_CODE, /*认证渠道编码*/ null as AUTH_FUNC_CODE, /*认证功能编码*/ null as NET_USER_ID, /*互联网用户标识*/ null as BP_ID, /*业务伙伴标识*/ a.ACCT_ID as CHAN_ACCT_ID, /*渠道账户标识*/ null as USER_NAME, /*账户名称*/ null as PROM_TYPE, /*推广类型*/ null as PROM_CHAN_CODE, /*推广渠道编码*/ a.PRE_APP_NO as BUS_PRE_APP_NO, /*预申请编号*/ null as APP_NO, /*申请编号*/ null as APP_YM, /*申请年月*/ null as APP_TIME, /*申请时间*/ null as MGT_ORG_CODE, /*管理单位编码*/ null as MGT_ORG_NAME, /*管理单位名称*/ a.CONS_ID as CUST_ID, /*客户标识*/ a.CONS_NO as CUST_NO, /*客户编号*/ null as CUST_CATEG, /*客户类别*/ null as CUST_CLS, /*客户分类*/ null as CONTACT_TYPE, /*联系人类型*/ null as CONTACT, /*联系人*/ null as ID_CARD_NUMBER, /*身份证号码*/ a.MOBILE as MOBILE_NUMBER, /*手机号码*/ a.STATUS_CODE as HNDL_FLAG, /*处理标志*/ null as VERIFIER, /*审核人*/ null as VERF_ORG, /*审核单位*/ null as VERF_TIME, /*审核时间*/ null as VERF_CONT, /*审核意见*/ null as VERF_RSLT, /*审核结果*/ null as DISQUAL_CODE, /*不通过原因代码*/ null as DISQUAL_NAME, /*不通过原因名称*/ null as MSG_CONT, /*消息内容*/ null as PROC_STAT /*流程状态*/ from SGPM.ARC_S_RNRG_APP a, SGPM.ARC_S_RNRG_APP_DATA b where a.APP_RNRG_ID = b.APP_RNRG_ID; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-户主认证申请明细 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_PPY_AUTH_DTL(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_PPY_AUTH_DTL'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'PPY_AUTH_DTL'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.PPY_AUTH_DTL'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.PPY_AUTH_DTL (PPY_AUTH_DTL_ID, /*户主认证申请明细标识*/ PPY_AUTH_APP_ID, /*户主认证申请单标识*/ BUS_PRE_APP_NO, /*预申请编号*/ MGT_ORG_CODE, /*管理单位编码*/ APP_YM, /*申请年月*/ APP_TIME, /*申请时间*/ CONTACT_TYPE, /*联系人类型*/ ATTACH_SN, /*附件序号*/ ATTACH_TYPE, /*附件类型*/ AUTO_CHECK_FLAG, /*自动校核标志*/ CERT_NAME, /*证件名称*/ CERT_NUMBER, /*证件号码*/ FILE_ID, /*文件标识*/ FILE_NAME, /*文件名称*/ ATTACH_ID /*附件标识*/) select DATA_ID as PPY_AUTH_DTL_ID, /*户主认证申请明细标识*/ APP_RNRG_ID as PPY_AUTH_APP_ID, /*户主认证申请单标识*/ null as BUS_PRE_APP_NO, /*预申请编号*/ null as MGT_ORG_CODE, /*管理单位编码*/ null as APP_YM, /*申请年月*/ null as APP_TIME, /*申请时间*/ pkg_db5_kf_chenyingjun.F_P_CODE('contactMode', CONTACT_MODE) as CONTACT_TYPE, /*联系人类型*/ SEQUENCE_ID as ATTACH_SN, /*附件序号*/ pkg_db5_kf_chenyingjun.F_P_CODE('qrFileType', FILE_TYPE) as ATTACH_TYPE, /*附件类型*/ IS_AUTO_CHECK as AUTO_CHECK_FLAG, /*自动校核标志*/ CERT_NAME as CERT_NAME, /*证件名称*/ CERT_NO as CERT_NUMBER, /*证件号码*/ FILE_ID as FILE_ID, /*文件标识*/ FILE_NAME as FILE_NAME, /*文件名称*/ null as ATTACH_ID /*附件标识*/ from SGPM.ARC_S_RNRG_APP_DATA; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-评价结果单 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_EVAL_RSLT(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_EVAL_RSLT'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'EVAL_RSLT'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.EVAL_RSLT'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_OPC.EVAL_RSLT (EVAL_RSLT_ID, /*评价结果单标识*/ EVAL_SYS_ID, /*评价体系标识*/ EVAL_BEG_TIME, /*评价开始时间*/ EVAL_END_TIME, /*评价结束时间*/ EVAL_CYC, /*评价周期*/ EXECUTOR, /*执行人*/ EXEC_TIME, /*执行时间*/ APP_NO, /*申请编号*/ CTC_ORG_ID, /*业务联系单位标识*/ SCORE, /*评价得分*/ EVALUATION_CODE, /*评价等级*/ ID3 /*评估日志标识*/) select id as EVAL_RSLT_ID, /*评价结果单标识*/ null as EVAL_SYS_ID, /*评价体系标识*/ null as EVAL_BEG_TIME, /*评价开始时间*/ null as EVAL_END_TIME, /*评价结束时间*/ null as EVAL_CYC, /*评价周期*/ EVAL_EMP_NO as EXECUTOR, /*执行人*/ EVAL_DATE as EXEC_TIME, /*执行时间*/ null as APP_NO, /*申请编号*/ ID2 as CTC_ORG_ID, /*业务联系单位标识*/ SCORE as SCORE, /*评价得分*/ pkg_db5_kf_chenyingjun.F_P_CODE('evalGrade', EVAL_LEVEL_CODE) as EVALUATION_CODE, /*评价等级*/ ID3 as ID3 /*评估日志标识*/ from S_BUSI_PARTNER_EVAL; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-安全服务计划 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_SAFETY_SRV_PLAN(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_SAFETY_SRV_PLAN'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'SAFETY_SRV_PLAN'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.SAFETY_SRV_PLAN'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_OPC.SAFETY_SRV_PLAN (SAFETY_SRV_PLAN_ID, /*安全服务计划标识*/ PLAN_NO, /*计划编号*/ CHK_CATEG, /*检查类别*/ PLAN_TYPE, /*计划类型*/ MKT_MGT_DIST, /*营销管理区域*/ TEAM_NO, /*班组编号*/ PLAN_MAKER, /*计划制定人*/ PLAN_MADE_TIME, /*计划制定时间*/ PLAN_DESC, /*计划描述*/ PLAN_SRV_TIME, /*计划服务时间*/ APP_NO, /*申请编号*/ PLAN_YEAR, /*年度*/ MON, /*月度*/ SPCL_CHK_TASK_SRC, --专项检查任务来源 SAFETY_SRV_CHK_CONT --安全服务检查内容 ) select a.PLAN_NO as SAFETY_SRV_PLAN_ID, /*安全服务计划标识*/ a.PLAN_NO as PLAN_NO, /*计划编号*/ null as CHK_CATEG, /*检查类别*/ pkg_db5_kf_chenyingjun.F_P_CODE('chkPlanType', a.TYPE_CODE) as PLAN_TYPE, /*计划类型*/ a.ORG_NO as MKT_MGT_DIST, /*营销管理区域*/ null as TEAM_NO, /*班组编号*/ null as PLAN_MAKER, /*计划制定人*/ null as PLAN_MADE_TIME, /*计划制定时间*/ a.PLAN_DESC as PLAN_DESC, /*计划描述*/ null as PLAN_SRV_TIME, /*计划服务时间*/ a.APP_NO as APP_NO, /*申请编号*/ a.PLAN_YEAR as PLAN_YEAR, /*年度*/ a.CHK_MONTH as MON, /*月度*/ b.TAST_SRC, c.CONTENT_CODE from S_CHK_PLAN a, SGPM.S_CHK_PLAN_DET b, SGPM.S_CHK_PLAN c where a.plan_no = b.plan_no(+) and a.plan_no = c.plan_no(+); ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-停复供客户明细方案 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: WULI #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_CUST_DTL_SCH(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_CUST_DTL_SCH'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'CUST_DTL_SCH'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.CUST_DTL_SCH'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_OPC.CUST_DTL_SCH (SCH_ID, /*方案标识*/ USER_DTL_SCH_ID, /*客户明细方案标识*/ MGT_ORG_CODE, /*管理单位编码*/ CUST_NAME, /*用电客户名称*/ E_C_ADDR, /*用能地址*/ CONTACT, /*联系人*/ MOBILE, /*移动电话*/ IMP_LV, /*重要性等级*/ VOLT, /*承压*/ ACTL_POWEROFF_DATE, /*实际停用日期*/ ACTL_RCVR_PWR_TIME, /*实际复供日期*/ CUST_NO, /*用电客户编号*/ STRT_STOP_PHOTO_ATTACH_ID, /*启停照片附件标识*/ METER_PHOTO_ATTACH_ID, /*计量表照片附件标识*/ UNIFIED_SOC_CR_CODE, /*统一信用社会代码*/ BP_NAME, /*客户名称*/ BP_NO, /*客户编号*/ STATUS /*停复电状态*/) select NULL AS SCH_ID, /*方案标识*/ NULL AS USER_DTL_SCH_ID, /*客户明细方案标识*/ NULL AS MGT_ORG_CODE, /*管理单位编码*/ NULL AS CUST_NAME, /*用电客户名称*/ NULL AS E_C_ADDR, /*用能地址*/ NULL AS CONTACT, /*联系人*/ NULL AS MOBILE, /*移动电话*/ NULL AS IMP_LV, /*重要性等级*/ NULL AS VOLT, /*承压*/ FSTART_TIME AS ACTL_POWEROFF_DATE, /*实际停用日期*/ FSTOP_TIME AS ACTL_RCVR_PWR_TIME, /*实际复供日期*/ NULL AS CUST_NO, /*用电客户编号*/ NULL AS STRT_STOP_PHOTO_ATTACH_ID, /*启停照片附件标识*/ NULL AS METER_PHOTO_ATTACH_ID, /*计量表照片附件标识*/ NULL AS UNIFIED_SOC_CR_CODE, /*统一信用社会代码*/ NULL AS BP_NAME, /*客户名称*/ NULL AS BP_NO, /*客户编号*/ NULL AS STATUS /*停复电状态*/ from sgpm_out.POWEROFF_PLAN_RETURN; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-互联网用户与客户绑定申请单 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_NET_USER_BIND_CUST_APP(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_NET_USER_BIND_CUST_APP'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'NET_USER_BIND_CUST_APP'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.NET_USER_BIND_CUST_APP'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.NET_USER_BIND_CUST_APP (NET_USER_BIND_CUST_APP_ID, /*互联网用户与客户绑定申请单标识*/ APP_TYPE, /*申请类型*/ APP_TIME, /*申请时间*/ CUST_CATEG, /*客户类别*/ CUST_CLS, /*客户分类*/ MGT_ORG_CODE, /*管理单位编码*/ BIND_ID, /*绑定身份*/ BIND_MODE, /*绑定方式*/ HNDL_FLAG, /*处理标志*/ BIND_RSLT, /*绑定结果*/ CUST_NO, /*客户编号*/ APP_NO, /*申请编号*/ NET_USER_ID, /*互联网用户标识*/ BP_ID, /*业务伙伴标识*/ CUST_ID, /*客户标识*/ ATTACH_ID, /*证件附件标识*/ BUS_PRE_APP_NO, /*预申请编号*/ BIND_APP_CHAN, /*绑定申请渠道*/ BIND_CHAN_CODE, /*绑定渠道编码*/ CONTACT_TYPE, /*联系人类型*/ CONTACT, /*联系人*/ CSC_SRV_CODE, /*国网客户服务中心服务编码*/ CSC_RQST_NO, /*国网客户服务中心请求编号*/ BIND_FUNC_CODE, /*绑定功能编码*/ PROM_TYPE, /*推广类型*/ PROM_CHAN_CODE /*推广渠道编码*/) select APP_ID as NET_USER_BIND_CUST_APP_ID, /*互联网用户与客户绑定申请单标识*/ null as APP_TYPE, /*申请类型*/ null as APP_TIME, /*申请时间*/ null as CUST_CATEG, /*客户类别*/ null as CUST_CLS, /*客户分类*/ null as MGT_ORG_CODE, /*管理单位编码*/ null as BIND_ID, /*绑定身份*/ null as BIND_MODE, /*绑定方式*/ null as HNDL_FLAG, /*处理标志*/ null as BIND_RSLT, /*绑定结果*/ null as CUST_NO, /*客户编号*/ null as APP_NO, /*申请编号*/ null as NET_USER_ID, /*互联网用户标识*/ null as BP_ID, /*业务伙伴标识*/ CONS_NO as CUST_ID, /*客户标识*/ null as ATTACH_ID, /*证件附件标识*/ PRE_APP_NO as BUS_PRE_APP_NO, /*预申请编号*/ null as BIND_APP_CHAN, /*绑定申请渠道*/ CHANNEL_NO as BIND_CHAN_CODE, /*绑定渠道编码*/ CONTACT_TYPE as CONTACT_TYPE, /*联系人类型*/ CONTACT_NAME as CONTACT, /*联系人*/ null as CSC_SRV_CODE, /*国网客户服务中心服务编码*/ null as CSC_RQST_NO, /*国网客户服务中心请求编号*/ null as BIND_FUNC_CODE, /*绑定功能编码*/ null as PROM_TYPE, /*推广类型*/ null as PROM_CHAN_CODE /*推广渠道编码*/ from SGPM.ARC_S_APP_CONSDBIND; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-二次回路 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_Scnd_Crct(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_Scnd_Crct'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'Scnd_Crct'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.Scnd_Crct'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.Scnd_Crct (crct_ID, attach_ID, crct_Categ, crct_Name, wire_Sec, wire_Spec, wire_Len, wire_Model, scnd_Cur_Crct_Wire_Mode, scnd_Volt_Crct_Wire_Mode, intmd_Contact_Flag, scnd_Load_Chk_Cyc, last_Load_Test_Date, last_Volt_Drop_Test_Date, volt_Drop_Test_Cyc, it_Logic_ID) select CIRCUIT_ID as crct_ID, ---回路标识 NULL as attach_ID, ---二次回路接线图附件标识 CIRCUIT_TYPE_CODE as crct_Categ, ---回路类别 CIRCUIT_NAME as crct_Name, ---回路名称 WIRE_SECTION as wire_Sec, ---导线截面 LINE_SPEC as wire_Spec, ---导线规格 WIRE_LEN as wire_Len, ---导线长度 WIRE_MODEL_CODE as wire_Model, ---导线型号 substr(CURRENT_WIRING, 1, 2) as scnd_Cur_Crct_Wire_Mode, ---电流二次回路接线方式 VOLT_WIRING as scnd_Volt_Crct_Wire_Mode, ---电压二次回路接线方式 MID_POINT as intmd_Contact_Flag, ---电压二次回路有无中间接点 SND_LOADTEST_CYCLE as scnd_Load_Chk_Cyc, ---二次负荷检验周期 LAST_LOADTEST_DATE as last_Load_Test_Date, ---上次负荷测试日期 LAST_VOLTTEST_DATE as last_Volt_Drop_Test_Date, ---上次压降测试日期 VOLT_TEST_CYCLE as volt_Drop_Test_Cyc, ---压降测试周期 NULL as it_Logic_ID ---互感器逻辑标识 FROM C_SND_CIRCUIT; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-证件申请凭证 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_Cert_App_Vou(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_Cert_App_Vou'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'Cert_App_Vou'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.Cert_App_Vou'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.Cert_App_Vou (cert_App_Vou_ID, cert_ID, ipt_Sorc_ID, ipt_Sorc_Type, cert_Name, cert_No, cert_Type, cert_Number, cert_Valid_Time, cert_Invalid_Time, cert_Dept, attach_ID, attr1, attr2, attr3, attr4, attr5, attr6, attr7, attr8, attr9, attr10, attr11, attr12, attr13, attr14, attr15, chg_Date, data_Insert_Time, chg_Desc, valid_Status) select ACT_ID as cert_App_Vou_ID, ---证件申请凭证标识 CERT_ID as cert_ID, ---证件标识 NULL as ipt_Sorc_ID, ---录入来源标识 NULL as ipt_Sorc_Type, ---录入来源类型 CERT_NAME as cert_Name, ---证件名称 NULL as cert_No, ---证件编号 CERT_TYPE_CODE as cert_Type, ---证件类型 CERT_NO as cert_Number, ---证件号码 CERT_EFFECT_DATE as cert_Valid_Time, ---证件生效时间 CERT_EXPIRE_DATE as cert_Invalid_Time, ---证件失效时间 NULL as cert_Dept, ---发证部门 NULL as attach_ID, ---证件附件标识 NULL as attr1, ---自定义属性1 NULL as attr2, ---自定义属性2 NULL as attr3, ---自定义属性3 NULL as attr4, ---自定义属性4 NULL as attr5, ---自定义属性5 NULL as attr6, ---自定义属性6 NULL as attr7, ---自定义属性7 NULL as attr8, ---自定义属性8 NULL as attr9, ---自定义属性9 NULL as attr10, ---自定义属性10 NULL as attr11, ---自定义属性11 NULL as attr12, ---自定义属性12 NULL as attr13, ---自定义属性13 NULL as attr14, ---自定义属性14 NULL as attr15, ---自定义属性15 NULL as chg_Date, ---变更日期 NULL as data_Insert_Time, ---变更数据插入时间 NULL as chg_Desc, ---变更说明 NULL as valid_Status ---生效状态 FROM ARC_S_APP_CERT; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客户角色地址 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_Bp_Role_Addr(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_Bp_Role_Addr'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'Bp_Role_Addr'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.Bp_Role_Addr'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.Bp_Role_Addr (bp_Role_Addr_ID, bp_Role_ID, bus_Srv_Addr_ID, invalid_Date, valid_Date, valid_Flag, srv_Whl_Addr, bp_Role_Obj_No, bp_Role_Obj_Name, bp_Role_Obj_ID) select d.CSA_ID as bp_Role_Addr_ID, ---客户角色地址标识 b.RELA_ID as bp_Role_ID, ---客户角色标识 d.CSA_ID as bus_Srv_Addr_ID, ---业务服务地址标识 NULL as invalid_Date, ---失效日期 NULL as valid_Date, ---生效日期 NULL as valid_Flag, ---有效标志 a.ELEC_ADDR as srv_Whl_Addr, ---服务完整地址 b.RELA_ID as bp_Role_Obj_No, ---客户角色对象编号 c.NAME as bp_Role_Obj_Name, ---客户角色对象名称 b.RELA_ID as bp_Role_Obj_ID ---客户角色对象标识 FROM C_CONS a, C_CUST_CONS_RELA b, C_CUST c, C_ELEC_ADDR d where a.cons_id = b.cons_id and b.cust_id = c.cust_id and a.cons_id = d.cons_id and c.name is not null; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-安装点与连接对象关系 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure p_db1_kf_INST_CONN_RELA(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_db1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_db1_KF_INST_CONN_RELA'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'INST_CONN_RELA'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.INST_CONN_RELA'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.INST_CONN_RELA (INST_CONN_RELA_ID, INST_ID, CONN_ID, CONN_CATEG) select b.MP_ID as INST_CONN_RELA_ID, ---属性描述：本实体记录的唯一标识，产生规则为流水号 b.MP_ID as INST_ID, ---属性描述：安装点的唯一标识 a.CSA_ID as CONN_ID, ---属性描述：连接对象的唯一标识 NULL as CONN_CATEG ---属性描述：连接类别是指安装点与连接对象的连接类别，如：用电户、发电户 FROM C_ELEC_ADDR a join C_MP b on b.SP_ID = a.CSA_ID; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-安装点与连接对象关系 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KF_BP_DIS_CR_REC(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_BP_DIS_CR_REC'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'BP_DIS_CR_REC'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.BP_DIS_CR_REC'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.BP_DIS_CR_REC (bp_dis_cr_rec_id, --- 业务伙伴定性失信记录标识 stats_ym, --- 统计年月 mgt_org_code, --- 管理单位编码 dis_cr_start_time, --- 失信开始时间 dis_cr_expr_time, --- 失信到期时间 bp_id, --- 业务伙伴标识 cr_lv_id, --- 信用等级标识 cr_score --- 信用分 ) select /*+ parallel(16)*/ a.id, '', a.PS_ORG_NO, a.EFFECT_DATE, a.CANCEL_DATE, a.CUST_ID, '', '' from sgpm.S_DISCREDIT_CUST a; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-安装点与连接对象关系 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KF_QRY_PWD_RSET_REC(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_QRY_PWD_RSET_REC'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'QRY_PWD_RSET_REC'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- insert into EMSS_CUC.QRY_PWD_RSET_REC (QRY_PWD_RSET_REC_ID, --查询密码重置记录标识 CUST_ID, --客户标识*\ CUST_NO, --\*客户编号*\ PRTR_NO, --\*伙伴编号*\ MGT_ORG_CODE, -- \*管理单位编码*\ RSET_TIME, --\*密码重置时间*\ RSET_RSLT, --\*密码重置结果*\ OPR, --\*操作人*\ OP_ORG_CODE, --\*操作人所属单位*\ MOBILE_NUMBER, --\*客户手机号码*\ SEND_STAT, --\*短信发送状态*\ SRV_CHAN, --\*服务渠道*\ CHAN_ACCT, --\*渠道账号*\ CHAN_SRV_CODE, -- \*渠道服务编码*\ CHAN_SN, --\*渠道交易流水号*\ CHAN_CODE, --\*渠道编码*\ CHAN_FUNC_CODE, --\*渠道功能项编码*\ CHAN_ACCT_ID, --\*渠道账户标识*\ CHAN_USER_NAME --\*渠道账户名称*\ ) select A.ID, B.CUST_ID, A.CONS_NO, NULL, --\*伙伴编号*\ NULL, --\*管理单位编码*\ A.OPERA_TIME, NULL, --\*密码重置结果*\ A.OPERATOR, NULL, --\*操作人所属单位*\ NULL, --\*客户手机号码*\ NULL, --\*短信发送状态*\ A.CHANNEL_TYPE, NULL, -- \*渠道账号*\ NULL, --\*渠道服务编码*\ NULL, --\*渠道交易流水号*\ NULL, --\*渠道编码*\ NULL, --\*渠道功能项编码*\ NULL, --\*渠道账户标识*\ NULL --\*渠道账户名称*\ from sgpm.PWD_MODIFY_RECORD A, SGPM.C_CONS B WHERE A.CONS_NO = B.CONS_NO(+); ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-安装点与连接对象关系 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KF_IMPT_CUST_ARCH(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_IMPT_CUST_ARCH'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'IMPT_CUST_ARCH'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.IMPT_CUST_ARCH'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.IMPT_CUST_ARCH (impt_cust_arch_id, --- 重要客户档案标识 cust_no, --- 客户编号 cust_impt_lv, --- 客户重要性等级 impt_cust_categ, --- 重要客户类别 impt_cust_ind_cls, --- 重要客户行业分类 imp_desc, --- 重要性描述 creation_time, --- 创建时间 last_maint_time, --- 上次维护时间 maint_time, --- 维护时间 maintr, --- 维护人 emer_plan_file_attach_id, --- 应急预案文件附件标识 remark, --- 重要客户标志 app_no, --- 申请编号 checker_name, --- 检查人员 chk_cycle, --- 检查周期 chk_date, --- 检查日期 manage_perfect_flag, --- 管理制度完善标志 oper_status, --- 运行管理情况 NON_ELEC_SCY_MEAS --非电性质保安措施 ) select /*+ parallel(16)*/ a.CUST_NO, --序列 a.CUST_NO, PKG_DB5_KF_CHENYINGJUN.F_P_CODE('custPrioLevel', a.CUST_PRIO_CODE), --客户重要性等级标准代码 PKG_DB5_KF_CHENYINGJUN.F_P_CODE('imptCustType', a.IMPORTANT_TYOE_CODE), --重要客户类别标准代码 PKG_DB5_KF_CHENYINGJUN.F_P_CODE('hrCustType', a.HR_TYPE_CODE), --重要客户行业分类标准代码 '', '', '', '', '', '', '', '', a.CHECKER_NAME, a.CHK_CYCLE, to_date(a.CHK_DATE, 'YYYY/MM/DD'), decode(a.MANAGE_PERFECT_FLAG, '0', '01', '1', '02', a.MANAGE_PERFECT_FLAG), --管理制度完善标志 a.OPER__STATUS, null from sgpm.S_HR_IMPORTANT_CUST a; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-安装点与连接对象关系 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KF_IMPT_ELEC_CUST_ARCH(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_IMPT_ELEC_CUST_ARCH'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'IMPT_ELEC_CUST_ARCH'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.IMPT_ELEC_CUST_ARCH'; /*这里的删除脚本是保证重复转接,条件需要根据后面的INSERT INTO条件替换*/ insert /*+ parallel(16)*/ into EMSS_CUC.IMPT_ELEC_CUST_ARCH (impt_elec_cust_arch_id, --- 重要用电客户档案标识 impt_cust_arch_id, --- 重要客户档案标识 power_supply_mode, --- 电源方式 ps_meet_dmd_flag, --- 电源满足需求标志 need_sp_flag, --- 自备应急电源需要配置标志 sp_desc_actl_config, --- 自备应急电源实际配置标志 sp_meet_req_flag, --- 自备应急电源满足要求标志 scy_flag, --- 有无保安负荷 scy_cap, --- 保安负荷容量 sp_desc, --- 自备应急电源备注说明 eletrc_meet_req_flag, --- 进网电工满足要求标志 gen_accs_cond_flag, --- 发电车具有备接入条件标志 gen_accs_cond_desc, --- 发电车接入条件描述 equip_status_code, --- 受电设备状况 safety_tool, --- 安全工器具 safety_meas, --- 安全措施 safety_equip_status, --- 继电保护及安全自动装置情况 ne_safety_meas, --- 非电性质保安措施 ps_type, --- sp_cap, --- sp_lock_flag, --- sp_sw_dev_flag, --- SP_FLAG, RELAY_PROTECT_SAFETY_AUTO_DEV_SITU, ARCH_ATTACH_ID ) SELECT /*+ parallel(16)*/ a.CUST_NO, --主键 '', '', decode(b.PS_FLAG, '0', '01', '1', '02', b.PS_FLAG), --电源满足需求标志 '', '', decode(b.EMERGENCY_PS_FLAG, '0', '01', '1', '02', b.EMERGENCY_PS_FLAG), --自备应急电源满足要求标志 '', a.SEC_LOAD_CAP, '', '', '', '', PKG_DB5_KF_CHENYINGJUN.F_P_CODE('runStatusCode', a.EQUIP_STATUS_CODE), --受电设备状况标准代码 a.SAFETY_TOOL, substr(a.SAFETY_MEAS, 1, 256), --字段超长截取 a.SAFETY_EQUIP_STATUS, a.NE_SAFETY_MEAS, PKG_DB5_KF_CHENYINGJUN.F_P_CODE('psTypeCode', b.OWN_SP_TYPE), --电源类型标准代码 decode(a.SPARE_SWITCH_FLAG, '0', '01', '1', '02', a.SPARE_SWITCH_FLAG), --自备电源投入切换装置标志 '', decode(a.SPARE_SWITCH_FLAG, '0', '01', '1', '02', a.SPARE_SWITCH_FLAG), --自备电源投入切换装置标志 a.SPARE_POWER_FLAG, a.SAFETY_EQUIP_STATUS, null from sgpm.S_HR_IMPORTANT_CUST a, sgpm.S_HR_IMPORTANT_CUST_EXT b where a.cust_no = b.cust_no(+); ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-安装点与连接对象关系 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KF_SRV_CYC_ST(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_SRV_CYC_ST'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'SRV_CYC_ST'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.SRV_CYC_ST'; insert /*+ parallel(16)*/ into EMSS_OPC.SRV_CYC_ST (ST_ID, /*SRV_CYC, /*服务周期*/ PRI /*优先级*/) select lrs_sequence.nextval, /* CYCLE_VALUE,*/ PRIO from SGPM.C_CHK_CYC_RULE CR; /*, SGPM.C_CHK_CYC_DIC CD*/ ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-安装点与连接对象关系 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KF_SRV_CYC_ST_DTL(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_SRV_CYC_ST_DTL'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'SRV_CYC_ST_DTL'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.SRV_CYC_ST_DTL'; insert /*+ parallel(16)*/ into EMSS_OPC.SRV_CYC_ST_DTL (ST_DTL_ID, COND_RELA /*条件关系*/) select lrs_sequence.nextval, COND_RELA from C_CHK_CYC_RULE; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-安装点与连接对象关系 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KF_TPCL_CASE_APP_FORM(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_TPCL_CASE_APP_FORM'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'TPCL_CASE_APP_FORM'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.TPCL_CASE_APP_FORM'; insert /*+ parallel(16)*/ into EMSS_OPC.TPCL_CASE_APP_FORM (TPCL_CASE_APP_FORM_ID, CTLG_NO, CASE_NAME, INFO_TYPE, CASE_DESC, MGT_ORG, PROV_ORG, CREATOR, CREAT_DATE, P_N_CASE, CASE_TYPE, QUERY_RNG, MAINT_TYPE, TPCL_CASE_ID, CASE_NO, TPCL_CASE_APP_FORM_ATTACH_ID, APP_NO) select TPL_ID as TPCL_CASE_APP_FORM_ID, ---典型案例申请单标识 NULL as CTLG_NO, ---目录编号 NULL as CASE_NAME, ---案例名称 NULL as INFO_TYPE, ---信息类型 NULL as CASE_DESC, ---案例描述 NULL as MGT_ORG, ---管理单位 NULL as PROV_ORG, ---省级单位 NULL as CREATOR, ---创建人 NULL as CREAT_DATE, ---创建日期 NULL as P_N_CASE, ---正负案例 BUSI_TYPE as CASE_TYPE, ---案例类型 NULL as QUERY_RNG, ---查阅范围 NULL as MAINT_TYPE, ---维护类型 NULL as TPCL_CASE_ID, ---典型案例标识 NULL as CASE_NO, ---案例编号 NULL as TPCL_CASE_APP_FORM_ATTACH_ID, ---典型案例申请单附件标识 APP_NO as APP_NO ---申请编号 FROM SGPM.ARC_S_APP_TPL; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-安装点与连接对象关系 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KF_CUST_CONTACT(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_CUST_CONTACT'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'CUST_CONTACT'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_OPC.CUST_CONTACT'; insert /*+ parallel(16)*/ into EMSS_OPC.CUST_CONTACT (CUST_CONTACT_ID, /*客户联系信息标识*/ CUST_ID, /*客户标识*/ CUST_NO, /*客户编号*/ PRTR_RELA_TYPE, /*联系人类型*/ CONTACT_NAME, /*联系人*/ HOME_TEL, /*移动电话*/ MOBILE_FRST, /*第一联系电话*/ MOBILE_SCND /*第二联系电话*/) select a.CONTACT_ID, a.CUST_ID, b.CUST_NO, a.CONTACT_MODE, a.CONTACT_NAME, substrb(a.HOMEPHONE, 1, 32), substrb(a.MOBILE, 1, 32), substrb(a.OFFICE_TEL, 1, 32) from C_CONTACT a, C_CUST b where a.cust_id = b.cust_id; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-安装点与连接对象关系 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB5_KF_SUPL_STOP_CUST_DTL(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB5_KF_SUPL_STOP_CUST_DTL'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'SUPL_STOP_CUST_DTL'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- insert /*+ parallel(16)*/ into EMSS_CSC.SUPL_STOP_CUST_DTL (supl_stop_cust_dtl_id, supl_stop_id, mgt_org_code, cust_id, cust_no, cust_name, voltage, rela_adj_volt_dev_id, chg_type, chg_time, chg_stat, rela_adj_volt_dev_name, rela_dist_sta_id, rela_meter_id, pms_supl_stop_no, cust_cls, mobile_number) select USER_LIST_ID as supl_stop_cust_dtl_id, --停供影响客户明细标识 CMS_ID as supl_stop_id, --区域停供记录标识 null as mgt_org_code, --管理单位编码 null as CUST_ID, --客户标识 CONS_NO as CUST_NO, --客户编号 CONS_NAME as CUST_NAME, --客户名称 VOLT_CODE as VOLTAGE, --承压 null as rela_adj_volt_dev_id, substr(RECORDSTATUS, 1, 8) as CHG_TYPE, --变更类型 RECORDTIMESTAMP as CHG_TIME, --变更时间 CHG_STATUS as CHG_STAT, --变更状态 EQUIP_NAME as RELA_ADJ_VOLT_DEV_NAME, --关联调压设备名称 TG_ID as RELA_DIST_STA_ID, --关联配送站标识 null as RELA_METER_ID, --关联计量表计标识 null as pms_supl_stop_no, --推送消息手机号 null as cust_cls, --客户分类 null as mobile_number --客户服务中心停供记录编码 from SGPM.T_PMS_POWEROFF_USER_LIST_HIS a; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-积分利率 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KF_POINT_RATE(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_POINT_RATE'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'POINT_RATE'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.POINT_RATE'; insert /*+ parallel(16)*/ into EMSS_CUC.POINT_RATE (POINT_RATE_ID, --积分利率标识 MGT_ORG, --管理单位 REF_BANK, --参考银行 RATE_TYPE, --利率类型 BANK_RATE, --银行利率 VALID_TIME, --生效时间 INVLD_TIME, --失效时间 MAINT_TIME, --维护时间 MAINTR, --维护人 POINT_RATE_STAT --积分利率状态 ) select A_RATE_INFO.RATE_ID ， A_RATE_INFO.ORG_NO ， A_RATE_INFO.BANK_NAME ， A_RATE_INFO.RATE_TYPE ， A_RATE_INFO.INTEGRAL_BASE / 200 ， A_RATE_INFO.START_DATE ， A_RATE_INFO.END_DATE ， A_RATE_INFO.MAINT_DATE ， A_RATE_INFO.MAINT_STAFF ， A_RATE_INFO.STATUS from sgpm.A_RATE_INFO; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-积分定价 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ procedure P_DB1_KF_POINT_PRC(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_POINT_PRC'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'POINT_PRC'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.POINT_PRC'; insert /*+ parallel(16)*/ into EMSS_CUC.POINT_PRC (MGT_ORG, --管理单位 POINT_PRC, --积分定价 VALID_TIME, --生效时间 INVLD_TIME, --失效时间 MAINT_TIME, --维护时间 MAINTR, --维护人 POINT_PRC_ID, --积分定价标识 PRC_STAT --定价状态 ) select A_INTEGRAL_PRICE.ORG_NO, A_INTEGRAL_PRICE.INTEGRAL_PRICE, A_INTEGRAL_PRICE.START_DATE, A_INTEGRAL_PRICE.END_DATE, A_INTEGRAL_PRICE.MAINT_DATE, A_INTEGRAL_PRICE.MAINT_STAFF, A_INTEGRAL_PRICE.PRICE_ID, A_INTEGRAL_PRICE.STATUS from sgpm.A_INTEGRAL_PRICE; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-用户积分消费记录 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ /*procedure P_DB1_KF_CUST_POINT_CONSP_REC(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_CUST_POINT_CONSP_REC'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'CUST_POINT_CONSP_REC'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ /* select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ /* PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CSC.CUST_POINT_CONSP_REC'; insert /*+ parallel(16)*/ /* into EMSS_CSC.CUST_POINT_CONSP_REC (CUST_POINT_CONSP_REC_ID, --用户积分消费记录标识 CUST_ID, --用户标识 CUST_NO, --用户编号 CUST_CLS, --用户分类 PS_ORG, --供电单位 CONSP_TYPE, --消费类型 CONSP_POINT, --消费积分 CONSP_TIME, --消费时间 CONSP_CHAN, --消费渠道 OP_BATCH_NO, --操作批次号 CONVERT_RATE, --积分划转比例 CONSP_RUN_ID, --消费流水标识 CHAN_ACCT_ID, --渠道账号标识 CHAN_ACCT_NAME, --渠道账户名称 PROV_GRID_CODE, --网省代码 CONSP_SUCC_FLAG, --消费成功标志 CHAN_CODE, --渠道编码 CONSP_CHAN_POINT --消费渠道积分 ) select null, null, IM_CONS_EX.CONS_NO, null, IM_CONS_EX.ORG_NO, 05, IM_CONS_EX.AMT, IM_CONS_EX.EXCHANGE_DATE, null, null, IM_CONS_EX.ACCT_NO, null, null, IM_CONS_EX.ACCT_ID, 无, 33, 1, null, null from sgpm_out.IM_CONS_EX; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ /*select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ /* pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ /*select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-用户积分流水 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ /*procedure P_DB1_KF_CUST_POINT_RUN_ACCT(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_CUST_POINT_RUN_ACCT'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'CUST_POINT_RUN_ACCT'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ /* select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ /* PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CSC.CUST_POINT_RUN_ACCT'; insert /*+ parallel(16)*/ /* into EMSS_CSC.CUST_POINT_RUN_ACCT (CUST_POINT_RUN_ACCT_ID, --用户积分流水标识 CUST_ID, --用户标识 CUST_NO, --用户编号 CUST_CLS, --用户分类 POINT_CATEG, --积分类别 POINT_RULE_ID, --积分规则标识 POINT_VAL, --积分值 POINT_CHG_TYPE, --积分变更类型 CUST_POINT_OP_ID, --用户积分操作标识 POINT_OP_TIME, --积分操作时间 PS_ORG, --供电单位 POINT_OBT_PLFM --积分获取平台 ) select null, A_INTEGRAL_DET.cons_id, A_INTEGRAL_DET.cons_no, IM_CONS_INTEGRAL.cons_no, EMSS_CSC.CUST_POINT_RUN_ACCT.CUST_CLS, 1, IM_CONS_INTEGRAL.INTEGRAL_ITEM, A_INTEGRAL_DET.CHANGE_NUM, A_INTEGRAL_DET.CHANGE_TYPE, IM_CONS_INTEGRAL.RELEASE_DATE, EMSS_CSC.CUST_POINT_RUN_ACCT.POINT_OP_TIME, 1 from sgpm_out.IM_CONS_INTEGRAL; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ /* select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ /*pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ /* select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-非居用户积分记录 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ /*procedure P_DB1_KF_NHSHD_POINT_REC(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_NHSHD_POINT_REC'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'NHSHD_POINT_REC'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ /* select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ /* PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CSC.NHSHD_POINT_REC'; insert /*+ parallel(16)*/ /*into EMSS_CSC.NHSHD_POINT_REC (NHSHD_POINT_REC_ID, --非居用户积分记录标识 CUST_ID, --用户标识 CUST_NO, --用户编号 PS_ORG, --供电单位 CUST_CLS, --用户分类 RCVD_ADV_AMT, --有效预收金额 RATE_AMT, --利息金额 POINT_RULE_ID, --积分规则标识 CHG_POINT_NUM, --变动积分数 POINT_BAL, --积分余额 CREATE_TIME, --创建时间 CHG_TIME, --变更时间 POINT_CHG_TYPE, --积分变更类型 INVALID_DATE, --失效日期 CUST_POINT_OP_ID, --用户积分操作标识 EXT_FLD1, --扩展字段1 EXT_FLD2 --扩展字段2 ) select null, A_INTEGRAL_DET.cons_id, A_INTEGRAL_DET.cons_no, A_INTEGRAL_DET.ORG_NO, EMSS_CSC.NHSHD_POINT_REC.CUST_CLS, A_INTEGRAL_DET.EFFECT_BAL, A_INTEGRAL_DET.INTEREST, POINT_RULE_ID, A_INTEGRAL_DET.CHANGE_NUM, A_INTEGRAL_DET.INTEGRAL_BAL, A_INTEGRAL_DET.CHANGE_DATE, A_INTEGRAL_DET.CHANGE_DATE, A_INTEGRAL_DET.CHANGE_TYPE, EMSS_CSC.NHSHD_POINT_REC.INVALID_DATE, null, null, null from sgpm.A_INTEGRAL_DET; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ /* select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ /* pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ /* select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-渠道用户注册 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ /* procedure P_DB1_KF_CHAN_USER_REG(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_CHAN_USER_REG'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'CHAN_USER_REG'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ /*select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ /* PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.CHAN_USER_REG'; insert /*+ parallel(16)*/ /* into EMSS_CUC.CHAN_USER_REG (CHAN_USER_REG_ID ， --渠道用户注册标识 HEAD_PHOTO ， --头像 EMAIL ， --邮箱 LOGIN_ACCT ， --登录账号 PASSWORD ， --登录密码 NET_USER_ID ， --互联网用户标识 NICK_NAME ， --昵称 SG_USER_TYPE ， --网上国网用户类型 VALID_CHAN ， --生效渠道 REG_TIME ， --注册时间 LAST_UPD_TIME ， --最后更新时间 REMARK ， --备注 CHAN_USER_STAT ， --渠道用户状态 THIRD_ACC_NO --第三方账号 ) select ID, HEAD_PHOTO, EMAIL, LOGIN_ACCOUNT, PASSWORD, USER_ID, NICK_NAME, SG_USER_TYPE, CHANNEL, REGISTER_TIME, LAST_UPDATE_TIME, REMARK, SG_USER_STATUS, OPEN_ID from sgpm_out.+; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ /* select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ /* pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ /* select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-实名认证 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ /* procedure P_DB1_KF_REAL_NAME_AUTH(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_REAL_NAME_AUTH'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'REAL_NAME_AUTH'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ /* select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ /* PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.REAL_NAME_AUTH'; insert /*+ parallel(16)*/ /* into EMSS_CUC.REAL_NAME_AUTH (REAL_NAME_AUTH_ID, --实名认证标识 NET_USER_ID, --互联网用户标识 REAL_NAME, --真实姓名 CERT_TYPE, --证件类型 CERT_NO, --证件编号 AUTH_TIME, --认证时间 MOBILE, --手机号 VALID_CHAN, --生效渠道 ATTACH_ID --附件标识 ) select ID， USER_ID， REAL_NAME， CERT_TYPE， CERT_NO， MOBILE， CHANNEL， AUTH_TIME， ID from sgpm_out.U_REAL_NAME_AUTH; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ /*select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ /* pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ /* select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end; /***************************************************** #procedure: 客户与综合DB1-客服-用户绑定 #param in_org_no 单位编号 #param IN_DATE 转接周期时间戳(年、月、日) #VERSION : V1.0 #AUTHOR: wuli #CREATEDATE: 2021-03-19 #modifyexplain:包体命名规则：PKG_数据库名称_业务名称_表名 *****************************************************/ /*procedure P_DB1_KF_USER_BIND(in_org_no varchar2, IN_DATE varchar2, out_code out number, out_msg out varchar2) is --包名 v_pkg_name varchar2(256) := 'PKG_DB1_KF_WULI'; --过程名 v_proc_name varchar2(256) := 'P_DB1_KF_USER_BIND'; --表名 v_tab_name varchar2(256); v_count number(16); V_logID number(24); begin out_code := 1; out_msg := 'ok'; v_tab_name := 'USER_BIND'; /*初始化日志数据 1.如果存在正在执行的情况,需要跳出 2.考虑二次转接,需要删除数据再转*/ /*select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; IF V_logID = 0 THEN /*为0,表示该单位该月份该过程的表未参与转接,可以进行转接*/ /* PKG_db1_kf_wuli.p_db1_kf_pDtLog_beg(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE); ------------------------------------------------------以下插入脚本需要按需修改------------------------------------------- execute immediate 'TRUNCATE TABLE EMSS_CUC.USER_BIND'; insert /*+ parallel(16)*/ /* into EMSS_CUC.USER_BIND (USER_BIND_ID, --用户绑定标识 CONS_NO, --用户编号 CONS_NAME, --用户名称 ELEC_ADDR, --用电地址 BIND_FLAG, --绑定标志 DFLT_FLAG, --默认标志 UPD_TIME, --更新时间 NET_USER_ID, --互联网用户标识 PS_ORG_NO, --供电单位编号 UNBIND_TIME, --解绑时间 OWNER_FLAG, --户主认证标志 BIND_TIME, --绑定时间 BIND_CHAN, --绑定渠道 REMARK, --备注 BIND_CONS_TYPE, --绑定用户类型 THIRD_ACC_NO --第三方账号 ) select ID CONS_NO， CONS_NAME， ELEC_ADDR， BIND_STATUS， IS_DEFAULT， ST_UPDATE_TIME， USER_ID， ORG_NO， UNBIND_TIME， IS_OWNER， BIND_TIME， CHANNEL， REMARK， CONS_SORT_CODE， OPEN_ID from sgpm_out.U_USER_BIND; ------------------------------------------------------以上插入脚本需要按需修改------------------------------------------- v_count := sql%rowcount; ---记录插入数量 /*在执行数据转接结束后,需要更改日志的结束事件相关信息*/ /*select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; if V_logID <> 0 then /* select nvl(count(*), 0) into v_count from D_METER_SITECHK where 1 = 1; /*条件需要根据后面的INSERT INTO条件替换*/ /* pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, v_count, out_msg); end if; END IF; exception when others then rollback; out_code := 0; out_msg := '失败！' || SQLERRM; /*数据转接发生异常时,需要关闭本次转接日志事件*/ /*select pkg_db1_kf_wuli.f_db1_kf_getLogService(v_pkg_name, v_proc_name, v_tab_name, in_org_no, IN_DATE) INTO V_logID from dual; pkg_db1_kf_wuli.p_db1_kf_pDtLog_end(V_logID, out_code, 0, out_msg); RETURN; end;*/ END PKG_DB1_KF_WULI;
• 触发器的创建
  • ----创建 yx_2_sjqy_prc_log 表, 用于记录对yx_2_sjqy_prc表的操作日志 CREATE TABLE yx_2_sjqy_prc_log ( LOG_ID NUMBER ,--日志ID LOG_ACTION VARCHAR2(100) , --操作名称 LOG_DATE DATE,--操作时间 LOG_MESSAGE VARCHAR2(32) ) SELECT * FROM yx_2_sjqy_prc_log; --创建触发器：将对student表的操作都记录到stu_log表中（update of 用于指定一个或多个字段，指定字段被更新时才会触发触发器） create or replace trigger modify_yx_2_sjqy_prc after insert or delete or update ON yx_2_sjqy_prc for each row begin if inserting then insert into yx_2_sjqy_prc_log values(1,'insert',sysdate,:new.TABLE_NAME_2); elsif deleting then insert into yx_2_sjqy_prc_log values(2,'delete',sysdate,:old.TABLE_NAME_2); elsif updating then insert into yx_2_sjqy_prc_log values(3,'update_old',sysdate,:old.TABLE_NAME_2); insert into yx_2_sjqy_prc_log values(4,'update_new',sysdate,:new.TABLE_NAME_2); end if; end;
• 查锁表的ip
  • 首先要了解查看自己电脑ip的sql ： select sys_context('userenv','ip_adderss') from dual; --查锁表的ip SELECT object_name 对象名称, machine as 主机,-----查不到具体主机是因为主机名字没有改 s.program 程序, s.sid, s.serial#, p.spid as OS进程号,s.CLIENT_INFO s.client_info, s.terminal, s.ouser from Gv$locked_object l, dba_objects o, Gv$session s, Gv$process p where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr; -------- Gv$locked_object 和v$locked_object 是实例的区别，Gv$locked_object是查整个库中所有实例的锁表，范围比v$locked_object大 --杀会话 alter system kill session '5570,47316'immediate; # 缺省从 v$session 中不能直接获得客户端 IP，可以在数据库中创建一个追踪客户端IP地址的触发器：以dba的身份登陆至系统，创建登陆触发器 SQL> create or replace trigger on_logon_trigger after logon on database begin dbms_application_info.set_client_info(sys_context('userenv', 'ip_address')); end; / # 通过上面创建的触发器，显示客户端信息的IP SELECT object_name 对象名称, machine as 主机,-----查不到具体主机是因为主机名字没有改 s.program 程序, s.sid, s.serial#, p.spid as OS进程号,s.CLIENT_INFO s.client_info, s.terminal, s.ouser from Gv$locked_object l, dba_objects o, Gv$session s, Gv$process p where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr; #查询v$session ,你会发现 v$session 多了一列 记录客户端的IP. select client_info from v$session; select username,sid,serial#,client_info,client_identifier from v$sessionwhere sid=(select sys_context('userenv','sid') from dual); oracle数据库使用plsql的时候有种锁表的方式是使用 select * from xxxx for update; 但我在plsql里面使用该方法锁住表以后，我的程序还是可以往里面插数据。弄了半天不明白，到网上搜了半天，也都是这个方法锁表。后来请教高手才发现还有另外一种方法 lock table xxxxx in exclusive mode; --查回滚 SELECT object_name 对象名称, machine as 主机,-----查不到具体主机是因为主机名字没有改 s.program 程序, s.sid, s.serial#, p.spid as OS进程号,s.CLIENT_INFO from Gv$locked_object l, dba_objects o, Gv$session s, Gv$process p where l.object_id = o.object_id and l.session_id = s.sid and s.paddr = p.addr; -------- Gv$locked_object 和v$locked_object 是实例的区别，Gv$locked_object是查整个库中所有实例的锁表，范围比v$locked_object大 --杀会话 alter system kill session '5570,47316'immediate; 在登陆时被告知test用户被锁 1、用dba角色的用户登陆，进行解锁，先设置具体时间格式，以便查看具体时间 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. 2、查看具体的被锁时间 SQL> select username,lock_date from dba_users where username='TEST'; USERNAME LOCK_DATE ------------------------------ ------------------- TEST 2009-03-10 08:51:03 3、解锁 SQL> alter user test account unlock; User altered. 4、查看是那个ip造成的test用户被锁 查看$ORACLE_HOME/network/admin/log/listener.log日志 10-MAR-2009 08:51:03 * (CONNECT_DATA=(SID=lhoms)(SERVER=DEDICATED)(CID=(PROGRAM=oracle)(HOST=omstestdb)(USER=oraoms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.69.1.11)(PORT=49434)) * establish * lhoms * 0 10-MAR-2009 08:51:03 * (CONNECT_DATA=(SID=lhoms)(SERVER=DEDICATED)(CID=(PROGRAM=oracle)(HOST=omstestdb)(USER=oraoms))) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.69.1.11)(PORT=49435)) * establish * lhoms * 0 这样可知是上面10.69.1.11的ip尝试多次失败登陆造成的被锁
• 取模插入数据
  • declare v_count number :=0; begin for i in ( select /*+parallel(a,26) use_hash*/ countact_id as cust_contact_id, mobile as mobile_frst, office_tel as mobile_scond from c_contact where mod(contact_id , 10) = 1 ) loop -----重点：此处是取模的方法，按照id的唯一性取值插入，开5个窗口每个窗口的值要换。 loop v_count := v_count+1; insert into cust_contact ( cust_contact_id, mobile_frst, mobile_scond )values ( i.cust_contact_id, i.mobile_frst, i.mobile_scond); if v_count = '200000' then v_count := 0; commit ; end if ; end loop ; commit; end ;
• 正则表达式
  • 正则语句怎么写
    • SELECT /* regexp_substr参数用法解释 srcstr : 源字符串 pattern : 正则表达式 position : 起始位置，默认 1 occurrence : 第几次出现，默认 1（匹配成功的次数，依次递增） modifier : 模式（'i': 不区分大小写，'c': 区分大小写。 默认 'c'） subexpression: 含有子表达式 0-9, 默认 0：不含子表达式，1：第一个子表达式，以此类推 */ regexp_substr('regexp_substr', 'regexp_substr', 1, 1, 'i', 0) regexp_substr,--支持正则表达式的模糊搜索 REGEXP_INSTR('abc', 'a') --regexp_like('abc',1,'a')--模糊搜索 from dual where regexp_like('abc', '[a-z]')
  • 【】字符组
    • [jJ]ava 可以匹配 java 或者 Java 
  • 直接写值
    • 例子： 2020 匹配所有含有2020的字段 
  • 区间
    • 正则表达式引擎在字符组中使用连字符(-)代表区间，依照这个规则，我们可以总结出三点： 要匹配任意数字可以使用[0-9]； 如果想要匹配所有小写字母，可以写成[a-z]； 想要匹配所有大写字母可以写成[A-Z]。 
  • 正则表达式常用的函数
    • 分类：与下列相似，但功能更加强大（'支持正则表达式'） (1) regexp_like : 同 like 功能相似（模糊 '匹配'） (2) regexp_instr : 同 instr 功能相似（返回字符所在 '下标'） (3) regexp_substr : 同 substr 功能相似（'截取' 字符串） (4) regexp_replace: 同 replace 功能相似（ '替换' 字符串） -- 使用了 '正则表达式' 替代了老的 '百分比 %' 和 '通配符 _' -- 上述 四个 函数，正则用法相似，知晓一个，其它皆可参考 
  • 把字段中所有汉字的数据转化成null
    • select REGEXP_REPLACE(要转换的字段，'[^0-9]') from 表名
• 聚合函数
  • avg(平均值)
    • select avg(X) from A; 求A表中字段X的平均值
  • sum(求和)
    • select sum(X) from A; 求A表中字段X的和
  • min,max(最小值，最大值)
    • select max(X) from A; 求A表中字段X的最大值 select min(X) from A; 求A表中字段X的最小值
  • count（数据统计）
    • 语法一：select count(*) from A; 语法二：select count(1) from A; 求A表中有几条数据 语法三：select count(distinct X) from A; 查询去重后的个数
• 创建语句
  • 创建视图
    • create view A(X) as select * from dual; 创建一个视图只显示X字段，试图名字叫A 创建表空间 create temporary tablespace 表空间名称 tempfile "位置" size 50m autoextend on next 50m maxsize 201150m extent managenent local
• 判断语句
  • case when then
    • 举例： select case when sex=1 then '男' when sex=-1 then '女' else null end from student ; 嵌套式case when : select emp_no, case when deptno = '01' then (case when pre >= 0.42 then '高' else '低' end) when deptno = '02' then (case when pre >= 0.6 then '高' else '低' end) end as pre_mark from emp
  • decode
    • decode用法很多， 1. select name, decode (sign (score-90), 1,'优秀,,0,'优秀,,-1, decode (sign (score-80) ,1,‘良好',0,'良好,-1, decode (sign (score-70) ,1, '中等',0,'等',-1, decode (sign (score-60) ,1,'及格',0,'及格',-1,'不及格'))) from grade 2. decode (判断字段，判断,替换，判断，替换，默认）数据量大的时候还是用case when好
  • nvl
    • NVL(E1, E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身
• 查询结果的处理
  • intersect
    • 在Oracle中，“A minus B”用于将结果集B中含有的记录从结果集A中移除，即用于获取存在于结果集A中而不存在于结果集B中的记录；“A union B”和“A union all B”用于获取结果集A和结果集B的并集，其中前者将只保留重复记录中的一条，而后者将保留所有的记录；“A intersect B”用于获取结果集A和结果集B共有的记录，即它们的交集。在本文中将简要的介绍一下intersect的用法。 intersect和minus、union一样，它们都需要结果集A和结果集B拥有相同的结构，即它们的列数要一致，且每列的数据类型也需要一致，否则没法进行比较。
  • minus
• 时间的处理
  • 查询某时间段的数据
    • 例子： 1）select * from tableName t where start_times > to_date('2015/6/26 10:00:00','yyyy/mm/dd hh24:mi:ss'); 2）select * from tableName t where start_times < to_date('2015/6/26 10:00:00','yyyy/mm/dd hh24:mi:ss'); 3）select * from tableName t where start_times between to_date('2015/6/26 10:00:00','yyyy/mm/dd hh24:mi:ss') and to_date('2015/6/26 11:00:00','yyyy/mm/dd hh24:mi:ss');
  • 查询某天的数据
    • select * from GATEWAY_LOG t where trunc(create_at)=trunc(sysdate)-7 查询 表：GATEWAY_LOG 中 字段：create_at 时间的前七天的数据
• 数据脱敏
  • 手机号脱敏
    • 方法一：SELECT substr(列名,1,3)||'****'||substr(列名,-4,4) from 表名 方法二：UPDATE table SET 列名 = ( CASE WHEN 列名 IS NOT NULL THEN SUBSTR (列名, 1, 3) || '*****' || SUBSTR (列名, 9) ELSE NULL END )
  • 身份证脱敏：
    • UPDATE table SET 列 = ( CASE WHEN 列 IS NOT NULL THEN SUBSTR (列, 1, 3) || '***********' || SUBSTR (列, 15) ELSE NULL END )
  • 姓名脱敏：
    • update table set 列 = REPLACE(列,SUBSTR(列,2,1),'*')
  • 邮箱脱敏：
    • UPDATE table SET 列 = ( CASE WHEN 列 IS NOT NULL THEN SUBSTR (列, 1, 3) || '*****' || SUBSTR (列, 9)ELSE NULL END )
  • 地址脱敏；
    • UPDATE table SET 列 = ( CASE WHEN 列 IS NOT NULL THEN SUBSTR (列, 1, 3) || '******' ELSE NULL END )
  • 工作中数据自动脱敏的存储过程
    • CREATE OR REPLACE PROCEDURE PRC_DESEN_SYP (I_FLAG NUMBER,V_ORG VARCHAR2,V_DATE VARCHAR2) IS /* I_FLAG =0 DESENSITIZATION ALL DATA I_FLAG=1 DESENSITIZATION ORG DATA */ V_SQL VARCHAR2(4000); v_block varchar2(4000); v_columnb_id number; v_index number; v_count number; out_msg VARCHAR2(256); V_NUM NUMBER; begin v_block:=null; for i in ( /*select * from DBA_TABLES t where t.TABLE_NAME in ( select table_name from DESENSITIZE_TABLE a) and owner in ('EMSS_ATC', 'EMSS_MTC', 'EMSS_TCC', 'EMSS_CMC', 'EMSS_PFC', 'EMSS_BLC', 'EMSS_BIC', 'EMSS_CTC', 'EMSS_WOC', 'EMSS_PJC', 'EMSS_ASC', 'EMSS_CUC', 'EMSS_BAC') */ select * from DBA_TABLES t where (T.OWNER,T.TABLE_NAME) IN (select A.OWNER,table_name from DESENSITIZE_TABLE a WHERE A.V_DATE IS NULL ) ) loop v_block :=null; v_index :=0; for a in (select * from dba_tab_columns WHERE OWNER=I.OWNER AND TABLE_NAME=I.TABLE_NAME order by column_id desc) loop v_index := v_index +1; if a.column_name in ('CERT_NAME','CONTACT_NAME','CUST_NAME','ACCT_HOLDER_NAME','BANK_ACCT_NAME','USER_NAME','LOGIN_NAME','SETTLE_ACCT_NAME','TAX_MAIN_NAME') then if v_index =1 then v_block:='pkg_desensit_common.f_get_name2_info('||a.column_name||') as '||a.column_name; else v_block:='pkg_desensit_common.f_get_name2_info('||a.column_name||') as '||a.column_name||','||v_block; end if; elsif a.column_name in ('INST_ADDR','EC_ADDR','TAX_MAIN_ADDR','SRV_LOC_ADDR','SRV_LOC_NAME') then if v_index =1 then v_block:='pkg_desensit_common.f_get_elecaddr_info('||a.column_name||') as '||a.column_name; else v_block:='pkg_desensit_common.f_get_elecaddr_info('||a.column_name||') as '||a.column_name||','||v_block; end if; elsif a.column_name in ('E_MAIL','EMAIL') then if v_index =1 then v_block:='pkg_desensit_common.f_get_mail_info('||a.column_name||') as '||a.column_name; else v_block:='pkg_desensit_common.f_get_mail_info('||a.column_name||') as '||a.column_name||','||v_block; end if; elsif a.column_name in ('MOBLIE','CONTACT_TEL','CUST_TEL','TELEPHONE','USER_TEL','TAX_MAIN_TEL') then if v_index =1 then v_block:='pkg_desensit_common.f_mask_phone_code('||a.column_name||') as '||a.column_name; else v_block:='pkg_desensit_common.f_mask_phone_code('||a.column_name||') as '||a.column_name||','||v_block; end if; elsif a.column_name in ('CERT_NUMBER') then if v_index =1 then v_block:='pkg_desensit_common.f_get_cert_info('||a.column_name||') as '||a.column_name; else v_block:='pkg_desensit_common.f_get_cert_info('||a.column_name||') as '||a.column_name||','||v_block; end if; elsif a.column_name in ('BANK_ACCT','CHAN_ACCT','BANK_ACCT_NO','OPEN_ACCT') then if v_index =1 then v_block:='pkg_desensit_common.f_get_bankacct_info('||a.column_name||') as '||a.column_name; else v_block:='pkg_desensit_common.f_get_bankacct_info('||a.column_name||') as '||a.column_name||','||v_block; end if; else if v_index =1 then v_block:=a.column_name; else v_block:=a.column_name||','||v_block; end if; end if; end loop; IF I_FLAG = 1 THEN begin select count(1) into v_count from dba_tab_columns t where t.OWNER=i.owner and t.TABLE_NAME=i.table_name and t.COLUMN_NAME='MGT_ORG_CODE'; exception when others then v_count:=0; end; if v_count >=1 then v_sql:='create table '||i.owner||'.'||i.table_name||'_TM'||V_DATE||' as select /*+ full(a) parallel(32)*/'||v_block||' from '||i.owner||'.'||i.table_name||' a where MGT_ORG_CODE like '''||V_ORG||'%''' ; else v_sql:='create table '||i.owner||'.'||i.table_name||'_TM' ||V_DATE||' as select /*+ full(a) parallel(32)*/'||v_block||' from '||i.owner||'.'||i.table_name||' a' ; end if; ELSIF I_FLAG = 0 THEN v_sql:='create table '||i.owner||'.'||i.table_name||'_TM'||V_DATE||' as select /*+ full(a) parallel(32)*/'||v_block||' from '||i.owner||'.'||i.table_name||' a' ; END IF; BEGIN EXECUTE IMMEDIATE V_SQL; EXCEPTION WHEN OTHERS THEN out_msg := substr(SQLERRM,1,256); insert into PRC_DESEN_SYP_error(owner,table_name,Msg_Out) values(i.owner,i.table_name,out_msg); --DBMS_OUTPUT.put_line(i.table_name||':'||out_msg); END ; BEGIN EXECUTE IMMEDIATE 'SELECT /*+ parallel(16)*/COUNT(1) FROM '||i.owner||'.'||i.table_name||'_TM'||V_DATE INTO V_NUM; EXCEPTION WHEN OTHERS THEN out_msg := substr(SQLERRM,1,256); insert into PRC_DESEN_SYP_error(owner,table_name,Msg_Out) values(i.owner,i.table_name,out_msg); --DBMS_OUTPUT.put_line(i.table_name||':'||out_msg); END ; BEGIN --dbms_output.put_line('UPDATE DESENSITIZE_TABLE SET V_DATE='''||V_DATE||''','||'V_COUNT='''||V_NUM||''','||'V_ORG='''||V_ORG||''' WHERE OWNER='''||i.owner||''' and table_name='''||i.table_name||''' and v_date is null'); EXECUTE IMMEDIATE 'UPDATE DESENSITIZE_TABLE SET V_DATE='''||V_DATE||''','||'V_COUNT='''||V_NUM||''','||'V_ORG='''||V_ORG||''' WHERE OWNER='''||i.owner||''' and table_name='''||i.table_name||''' and v_date is null'; EXCEPTION WHEN OTHERS THEN out_msg := substr(SQLERRM,1,256); insert into PRC_DESEN_SYP_error(owner,table_name,Msg_Out) values(i.owner,i.table_name,out_msg); --DBMS_OUTPUT.put_line(i.table_name||':'||out_msg); END ; end loop; COMMIT; end;
• 用户，权限
  • --创建TEST用户设置密码为password01！ create user TEST identified by password01！; --给TEST用户授予DBA权限 grant DBA to TEST; ----给TEST用户设置授予一个查询A表的权限 grant select on A to TEST; ----撤销TEST用户对A表的查询权限 revoke select on A from TEST;
• 优化。
  • count(*)
    • 若列名为主键，count(列名)会比count(1)快 若列名不为主键，count(1)会比count(列名)快 若表多个列并且没有主键，则 count（1） 的执行效率优于 count（*） 若表有主键，则 select count（主键）的执行效率是最优的 若表只有一个字段，则 select count（*）最优。
  • 如何加并发
    • ②　利用/*+ parallel(16)*/ 并行查询 并行查询适用于大部分SELECT语句，例如大表查询、多表连接查询、计算量较大的查询。对于非常短的查询，效果不太显著。  ③　append与nologging方式联用更高效 使用append，一是减少对空间的搜索；二是有可能减少redolog的产生。所以append方式会快很多，一般用于大数据量的处理。nologging模式不是不记录日志，而是最小化日志产生的数量。append 属于direct insert,归档模式下append+table nologging会大量减少日志,非归档模式append会大量减少日志,append方式插入只会产生很少的undo。(insert /*+append nologing*/ into A select * from A)。 优化前，耗时3’56’’：  ④create /*+enable_parallel_ddl* / table A as select /*+full(a) parallel(32)*/ from a where B like '%AL%' 根据语句的属性使用并发。 ⑤可以自行对比以下几个并行的速度。 /*+enable_parallel_dml prallel(32)*/ /*+enable_parallel_ddl prallel(32)*/ /*+enable_parallel_dml*/
  • 索引
    • 1. 查询一张表里面索引 select * from dba_indexes where table_name=upper('bills'); 2. 查询被索引字段 select * from dba_ind_columns where index_name=('in_bills'); 3. 给某一字段创建索引 create index in_bills on bills(account_id);
• 数据备份
  • 全量备份：对数据库完整的备份
    • 优点：可实现数据100%恢复，恢复时间短，操作方便 缺点：备份数据量大，存储空间需求大
  • 增量备份：从最后一次备份后改变的文件
    • 优点：备份数据量小，备份时间短； 缺点：恢复时依赖之前的备份记录，出现问题的风险比较大
  • 差异备份：在最后一次完整后被修改的文件
    • 优点：恢复数据时间短--因此只需要两份数据,最后一次完全备份和最后一次差异备份 缺点：每次备份需要的时间长
• 更新数据
  • 复制A.ID到B.ID
    • update A set a.id = (select id from b where a.code_id = b.code_id ) 更新的是A表中的ID ，但是需要用A.CODE_ID =B.CODE_ID来关联确保数据更新过去一 一对应
• sql知识题库
  • 一套Oracle面试题笔试题及参考答案 nvl(name,0) 空则取0。 sustrb（manth，1,4）第一位开始取4位。 Decode（je，1，1，2），je为1的时候显示1，否则显示2. select classno, avg(score) from student group by classno having avg(score) = (select max(avg(score)) from student group by classno); 创建表空间neuspace，数据文件命名为neudata.dbf，存放在d:\data目录下，文件大小为200MB，设为自动增长，增量5MB，文件最大为500MB。(8分) 答：create tablespace neuspace datafile ‘d:\data\neudata.dbf’ size 200m auto extend on next 5m maxsize 500m; 2. 假设表空间neuspace已用尽500MB空间，现要求增加一个数据文件，存放在e:\appdata目录下，文件名为appneudata，大小为500MB，不自动增长。（5分） 答：alter tablespace neuspace add datafile ‘e:\appdata\appneudata.dbf’ size 500m; 3. 以系统管理员身份登录，创建账号tom，设置tom的默认表空间为neuspace。为tom分配connect和resource系统角色，获取基本的系统权限。然后为tom分配对用户scott的表emp的select权限和对SALARY, MGR属性的update权限。(8分) 答：create user tom identified by jack default tablespace neuspace; Grant connect, resource to tom; Grant select, update(salary, mgr) on scott.emp to tom; 4. 按如下要求创建表class和student。（15分） 属性 类型(长度) 默认值 约束 含义 CLASSNO 数值 (2) 无 主键 班级编号 CNAME 变长字符 (10) 无 非空 班级名称 属性 类型(长度) 默认值 约束 含义 STUNO 数值 (8) 无 主键 学号 SNAME 变长字符 (12) 无 非空 姓名 SEX 字符 (2) 男 无 性别 BIRTHDAY 日期 无 无 生日 EMAIL 变长字符 (20) 无 唯一 电子邮件 SCORE 数值 (5, 2) 无 检查 成绩 CLASSNO 数值 (2) 无 外键，关联到表CLASS的CLASSNO主键 班级编号 答：create table class (classno number(2) constraint class_classno_pk primary key, cname varchar2(10) not null); create table student (stuno number(8) constraint student_stuno_pk primary key, sname varchar2(12) not null, sex char(2) default ‘男’, birthday date, email varchar2(20) constraint student_email_uk unique, score number(5,2) constraint student_score_ck check(score>=0 and score<=100), classno number(2) constraint student_classno_fk references class(classno) ); 5. 在表student的SNAME属性上创建索引student_sname_idx（5分） 答：create index student_sname_idx on student(sname); 6. 创建序列stuseq，要求初值为20050001，增量为1，最大值为20059999。（6分） 答：create sequence stuseq increment by 1 start with 20050001 maxvalue 20059999 nocache nocycle; 7. 向表student中插入如下2行。（5分） STUNO SNAME SEX BIRTHDAY EMAIL SCORE CLASSNO 从stuseq取值 tom 男 1979-2-3 14:30:25 [e-mail] 89.50 1 从stuseq取值 jerry 默认值 空 空 空 2 答：insert into student values(stuseq.nextval, ’tom’, ’男’, to_date(‘1979-2-3 14:30:25’, ’yyyy-mm-dd fmhh24:mi:ss’), ’[e-mail]’, 89.50, 1); insert into student (stuno, sname, classno) values(stuseq.nextval, ’jerry’, 2); 8. 修改表student的数据，将所有一班的学生成绩加10分。（4分） 答：update student set score=score+10 where classno=1; 9. 删除表student的数据，将所有3班出生日期小于1981年5月12日的记录删除。（4分） 答：delete from student where classno=3 and birthday > ’12-5月-81’; 10. 完成以下SQL语句。(40分) (1) 按班级升序排序，成绩降序排序，查询student表的所有记录。 答：select * from student order by classno, score desc; (2) 查询student表中所有二班的成绩大于85.50分且出生日期大于1982-10-31日的男生的记录。 答：select * from student where classno=2 and score>85.50 and birthday < ’31-10月-82’ and sex=’男’; (3) 查询student表中所有三班成绩为空的学生记录。 答：select * from student where classno=3 and score is null; (4) 表student与class联合查询，要求查询所有学生的学号，姓名，成绩，班级名称。（使用oracle与SQL 99两种格式） 答：select s.stuno, s.sname, s.score, c.cname from student s, class c where s.classno=c.classno; (5) 按班级编号分组统计每个班的人数，最高分，最低分，平均分，并按平均分降序排序。 答：select classno, count(*), max(score), min(score), avg(score) from student group by classno order by avg(score) desc; (6) 查询一班学生记录中所有成绩高于本班学生平均分的记录。 答：select * from student where classno=1 and score > (select avg(score) from student where classno=1); (7) 统计二班学生中所有成绩大于所有班级平均分的人数。 答：select count(*) from student where classno=2 and score > all (select avg(socre) from student group by classno); (8) 查询平均分最高的班级编号与分数。 答：select classno, avg(score) from student group by classno having avg(score) = (select max(avg(score)) from student group by classno); (9) 查询所有学生记录中成绩前十名的学生的学号、姓名、成绩、班级编号。 答：select stuno, sname, score, classno from (select * from student order by score desc) where rownum<=10; (10) 创建视图stuvu，要求视图中包含student表中所有一班学生的stuno, sname, score, classno四个属性，并具有with check option限制。 答：create view stuvu as select stuno, sname,score,classno from student where classno=1 with check option; 1、比较大小 select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; –取较小值 sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1 例如： 变量1=10，变量2=20 则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。 2、表、视图结构转化 现有一个商品销售表sale，表结构为： month　　　 char(6)　　　　　 –月份 sell　　　　number(10,2)　　　–月销售金额 现有数据为： 200001　　1000 200002　　1100 200003　　1200 200004　　1300 200005　　1400 200006　　1500 200007　　1600 200101　　1100 200202　　1200 200301　　1300 想要转化为以下结构的数据： year　　　char(4)　　　　　 –年份 month1　　number(10,2)　　　–1月销售金额 month2　　number(10,2)　　　–2月销售金额 month3　　number(10,2)　　　–3月销售金额 month4　　number(10,2)　　　–4月销售金额 month5　　number(10,2)　　　–5月销售金额 month6　　number(10,2)　　　–6月销售金额 month7　　number(10,2)　　　–7月销售金额 month8　　number(10,2)　　　–8月销售金额 month9　　number(10,2)　　　–9月销售金额 month10　　number(10,2)　　　–10月销售金额 month11　　number(10,2)　　　–11月销售金额 month12　　number(10,2)　　　–12月销售金额 结构转化的SQL语句为： create or replace view v_sale(year,month1,month2,month3,month4,month5,month6,month7,month8,month9,month10,month11,month12) as 　　　　select 　　　　substrb(month,1,4), 　　　　sum(decode(substrb(month,5,2),’01′,sell,0)), 　　　　sum(decode(substrb(month,5,2),’02′,sell,0)), 　　　　sum(decode(substrb(month,5,2),’03′,sell,0)), 　　　　sum(decode(substrb(month,5,2),’04′,sell,0)), 　　　　sum(decode(substrb(month,5,2),’05′,sell,0)), 　　　　sum(decode(substrb(month,5,2),’06′,sell,0)), 　　　　sum(decode(substrb(month,5,2),’07′,sell,0)), 　　　　sum(decode(substrb(month,5,2),’08′,sell,0)), 　　　　sum(decode(substrb(month,5,2),’09′,sell,0)), 　　　　sum(decode(substrb(month,5,2),’10′,sell,0)), 　　　　sum(decode(substrb(month,5,2),’11′,sell,0)), 　　　　sum(decode(substrb(month,5,2),’12′,sell,0)) 　　　　from sale 　　　　group by substrb(month,1,4); 79、CASE语句的用法？ Oracle用法很简单： SELECT last_name, job_id, salary CASE job_id WHEN ‘IT_PROG’ THEN 1.10*salary WHEN ‘ST_CLERK’ THEN 1.15*salary WHEN ‘SA_REP’ THEN 1.20*salary ELSE salary END “REVISED_SALARY” FROM employees 80、 truncate和delete的区别？ 　1、TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令DELETE将被撤销，而TRUNCATE则不会被撤销。 　　2、TRUNCATE是一个DDL语言而DELETE是DML语句，向其他所有的DDL语言一样，他将被隐式提交，不能对TRUNCATE使用ROLLBACK命令。 　　3、TRUNCATE将重新设置高水平线和所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。 　　4、TRUNCATE不能触发触发器，DELETE会触发触发器。 　　5、不能授予任何人清空他人的表的权限。 　　6、当表被清空后表和表的索引讲重新设置成初始大小，而delete则不能。 　　7、不能清空父表。 81、 表空间如何扩展？并用语句写出？ 两种扩展方式： a) 增加数据文件 alter tablespace tablespace_name add datafile ‘’ xxMB b) 扩展数据文件大小 alter database datafile ‘’ resize newMB 82、 表空间区管理方式？哪种方式现在是推荐使用的？ a) 字典管理方式 extent management dictionary;默认方式 b) 本地管理方式 extent management local[autoallocate/uniform xxmb]; 83、 用什么函数获得日期？和日期中的月，日，年 to_char(sysdate,’year’):tow thsound six to_char(sysdate,’yyyy’) :2006 to_char(sysdate,’month’):8月 to_char(sysdate,’mm’):08 to_char(sysdate,’day’):星期4 to_char(sysdate,’dd’):22 84、 分区表的应用？ a) 一个分区表有一个或多个分区，每个分区通过使用范围分区、散列分区、或组合分区分区的行 b) 分区表中的每一个分区为一个段，可各自位于不同的表空间中 c) 对于同时能够使用几个进程进行查询或操作的大型表分区非常有用 85、 谈谈索引的用法及原理？ 索引是若干数据行的关键字的列表，查询数据时，通过索引中的关键字可以快速定位到要访问的记录所在的数据块，从而大大减少读取数据块的I/O次数，因此可以显著提高性能。 86、 存储过程的应用，如何既有输入又有输出？ Create procedure pro_name (xxxx in/out type; yyyy in/out/inout type; ) is/as zzzz type; begin sqlpro; exception exceptionxxxxx; commit; end; 87、 常发生的异常有哪些？ 常用预定义例外 CURSOR_ALREADY_OPEN — ORA-06511 SQLCODE = -6511 游标已经打开 DUP_VAL_ON_INDEX — ORA-00001 SQLCODE = -1 违反唯一性约束 INVALID_CURSOR — ORA-01001 SQLCODE = -1001 非法游标操作 INVALID_NUMBER — ORA-01722 SQLCODE = -1722 字符向数字转换失败 LOGIN_DENIED — ORA-01017 SQLCODE = -1017 NO_DATA_FOUND — ORA-01403 SQLCODE = +100 没有找到数据 NOT_LOGGED_ON — ORA-01012 SQLCODE = -1012 没有连接到数据库 PROGRAM_ERROR — ORA-06501 SQLCODE = -6501 内部错误 STORAGE_ERROR — ORA-06500 SQLCODE = -6500 TIMEOUT_ON_RESOURCE — ORA-00051 SQLCODE = -51 TOO_MANY_ROWS — ORA-01422 SQLCODE = -1422 返回多行 TRANSACTION_BACKED_OUT — ORA-00061 SQLCODE = -61 VALUE_ERROR — ORA-06502 SQLCODE = -6502 数值转换错误 ACCESS_INTO_NULL试图为NULL对象的属性赋值 ZERO_DIVIDE — ORA-01476 SQLCODE = -1476 被零除 OTHERS — 其它任何错误的处理 88、 如何使用异常？ 在oracle中有三种类型的异常。预定义的异常 非预定义的异常 用户定义的异常 第二种非预定义的异常是与特定的oracle错误关联。并且用PRAGM EXCEPTION_INIT(EXCEPTION_NAME,ERROR_NUMBER)关联一起的。但是到底有什么用啊？ 例如：declare dup_primary_key exception; pragma exception_init(dup_primary_key,-1); begin insert into itemfile values(‘i201′,’washer’,'spares’,100,50,250,12,30); exception when dup_primary_key then dbms_output.put_line(‘重复项编号-主键冲突’); end 第一种的使用方法：exception when 异常名称 then 异常处理代码; 第三种的用法：if 条件 then raise_application_error(-20000“““`-20999,提示信息)； end if; 89、优化的策略一般包括: • 内存优化 • 操作系统优化 • 数据存储的优化 • 网络优化等方法 具体到不同的数据库涉及到要调整不同的数据库配置文件、不同的操作系统参数、网络参数等等, 不同的数据库不同
• Oracle的脚本优化
  • 执行计划
    • 本文全面详细介绍oracle执行计划的相关的概念，访问数据的存取方法，表之间的连接等内容。 并有总结和概述，便于理解与记忆! +++ 目录 --- 一．相关的概念 Rowid的概念 Recursive Sql概念 Predicate(谓词) DRiving Table(驱动表) Probed Table(被探查表) 组合索引(concatenated index) 可选择性(selectivity) 二．oracle访问数据的存取方法 1) 全表扫描(Full Table Scans， FTS) 2) 通过ROWID的表存取(Table Access by ROWID或rowid lookup) 3)索引扫描(Index Scan或index lookup)有4种类型的索引扫描： (1) 索引唯一扫描(index unique scan) (2) 索引范围扫描(index range scan) 在非唯一索引上都使用索引范围扫描。使用index rang scan的3种情况： (a) 在唯一索引列上使用了range操作符(> < <> >= <= between) (b) 在组合索引上，只使用部分列进行查询，导致查询出多行 (c) 对非唯一索引列上进行的任何查询。 (3) 索引全扫描(index full scan) (4) 索引快速扫描(index fast full scan) 三、表之间的连接 1，排序 - - 合并连接(Sort Merge Join， SMJ) 2，嵌套循环(Nested Loops， NL) 3，哈希连接(Hash Join， HJ) 另外，笛卡儿乘积(Cartesian Product) 总结Oracle连接方法 Oracle执行计划总结概述 +++ 一．相关的概念 Rowid的概念：rowid是一个伪列，既然是伪列，那么这个列就不是用户定义，而是系统自己给加上的。 对每个表都有一个rowid的伪列，但是表中并不物理存储ROWID列的值。不过你可以像使用其它列那样使用它，但是不能删除改列，也不能对该列的值进行 修改、插入。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即即使该行产生行迁移，行的rowid也不会改变。 Recursive SQL概念：有时为了执行用户发出的一个sql语句，Oracle必须执行一些额外的语句，我们将这些额外的语句称之为''recursive calls''或''recursive SQL statements''.如当一个DDL语句发出后，ORACLE总是隐含的发出一些recursive SQL语句，来修改数据字典信息，以便用户可以成功的执行该DDL语句。当需要的数据字典信息没有在共享内存中时，经常会发生Recursive calls，这些Recursive calls会将数据字典信息从硬盘读入内存中。用户不必关心这些recursive SQL语句的执行情况，在需要的时候，ORACLE会自动的在内部执行这些语句。当然DML语句与SELECT都可能引起recursive SQL.简单的说，我们可以将触发器视为recursive SQL. Row Source(行源)：用在查询中，由上一操作返回的符合条件的行的集合，即可以是表的全部行数据的集合；也可以是表的部分行数据的集合；也可以为对上2个row source进行连接操作(如join连接)后得到的行数据集合。 Predicate(谓词)：一个查询中的WHERE限制条件 Driving Table(驱动表)：该表又称为外层表(OUTER TABLE)。这个概念用于嵌套与HASH连接中。如果该row source返回较多的行数据，则对所有的后续操作有负面影响。注意此处虽然翻译为驱动表，但实际上翻译为驱动行源(driving row source)更为确切。一般说来，是应用查询的限制条件后，返回较少行源的表作为驱动表，所以如果一个大表在WHERE条件有有限制条件(如等值限 制)，则该大表作为驱动表也是合适的，所以并不是只有较小的表可以作为驱动表，正确说法应该为应用查询的限制条件后，返回较少行源的表作为驱动表。在执行 计划中，应该为靠上的那个row source，后面会给出具体说明。在我们后面的描述中，一般将该表称为连接操作的row source 1. Probed Table(被探查表)：该表又称为内层表(INNER TABLE)。在我们从驱动表中得到具体一行的数据后，在该表中寻找符合连接条件的行。所以该表应当为大表(实际上应该为返回较大row source的表)且相应的列上应该有索引。在我们后面的描述中，一般将该表称为连接操作的row source 2. 组合索引(concatenated index)：由多个列构成的索引，如create index idx_emp on emp(col1， col2， col3， ……)，则我们称idx_emp索引为组合索引。在组合索引中有一个重要的概念：引导列(leading column)，在上面的例子中，col1列为引导列。当我们进行查询时可以使用“where col1 = ？ ”，也可以使用“where col1 = ？ and col2 = ？”，这样的限制条件都会使用索引，但是“where col2 = ？ ”查询就不会使用该索引。所以限制条件中包含先导列时，该限制条件才会使用该组合索引。 可选择性(selectivity)：比较一下列中唯一键的数量和表中的行数，就可以判断该列的可选择性。 如果该列的“唯一键的数量/表中的行数”的比值越接近1，则该列的可选择性越高，该列就越适合创建索引，同样索引的可选择性也越高。在可选择性高的列上进 行查询时，返回的数据就较少，比较适合使用索引查询。 二．oracle访问数据的存取方法 1) 全表扫描(Full Table Scans， FTS) 为实现全表扫描，Oracle读取表中所有的行，并检查每一行是否满足语句的WHERE限制条件一个多块读操作可以使一次I/O能读取多块数据块(db_block_multiblock_read_count参数设定)，而不是只读取一个数据块，这极大的减 少了I/O总次数，提高了系统的吞吐量，所以利用多块读的方法可以十分高效地实现全表扫描，而且只有在全表扫描的情况下才能使用多块读操作。在这种访问模 式下，每个数据块只被读一次。 使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5% —— 10%，或你想使用并行查询功能时。 使用全表扫描的例子： SQL> explain plan for select * from dual; Query Plan ----------------------------------------- SELECT STATEMENT[CHOOSE] Cost= TABLE ACCESS FULL DUAL 2) 通过ROWID的表存取(Table Access by ROWID或rowid lookup) 行的ROWID指出了该行所在的数据文件、数据块以及行在该块中的位置，所以通过ROWID来存取数据可以快速定位到目标数据上，是Oracle存取单行数据的最快方法。 这种存取方法不会用到多块读操作，一次I/O只能读取一个数据块。我们会经常在执行计划中看到该存取方法，如通过索引查询数据。 使用ROWID存取的方法： SQL> explain plan for select * from dept where rowid = ''AAAAyGAADAAAAATAAF''； Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID DEPT [ANALYZED] 3)索引扫描(Index Scan或index lookup) 我们先通过index查找到数据对应的rowid值(对于非唯一索引可能返回多个rowid值)，然后根据rowid直接从表中得到具体的数据，这 种查找方式称为索引扫描或索引查找(index lookup)。一个rowid唯一的表示一行数据，该行对应的数据块是通过一次i/o得到的，在此情况下该次i/o只会读取一个数据库块。 在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的ROWID值。 索引扫描可以由2步组成： (1) 扫描索引得到对应的rowid值。 (2) 通过找到的rowid从表中读出具体的数据。 每步都是单独的一次I/O，但是对于索引，由于经常使用，绝大多数都已经CACHE到内存中，所以第1步的 I/O经常是逻辑I/O，即数据可以从内存中得到。但是对于第2步来说，如果表比较大，则其数据不可能全在内存中，所以其I/O很有可能是物理I/O，这 是一个机械操作，相对逻辑I/O来说，是极其费时间的。所以如果多大表进行索引扫描，取出的数据如果大于总量的5% —— 10%，使用索引扫描会效率下降很多。如下列所示： SQL> explain plan for select empno， ename from emp where empno=10； Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX UNIQUE SCAN EMP_I1 但是如果查询的数据能全在索引中找到，就可以避免进行第2步操作，避免了不必要的I/O，此时即使通过索引扫描取出的数据比较多，效率还是很高的 SQL> explain plan for select empno from emp where empno=10;-- 只查询empno列值 Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 INDEX UNIQUE SCAN EMP_I1 进一步讲，如果sql语句中对索引列进行排序，因为索引已经预先排序好了，所以在执行计划中不需要再对索引列进行排序 SQL> explain plan for select empno, ename from emp where empno > 7876 order by empno; Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT[CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX RANGE SCAN EMP_I1 [ANALYZED] 从这个例子中可以看到：因为索引是已经排序了的，所以将按照索引的顺序查询出符合条件的行，因此避免了进一步排序操作。 根据索引的类型与where限制条件的不同，有4种类型的索引扫描： 索引唯一扫描(index unique scan) 索引范围扫描(index range scan) 索引全扫描(index full scan) 索引快速扫描(index fast full scan) (1) 索引唯一扫描(index unique scan) 通过唯一索引查找一个数值经常返回单个ROWID.如果存在UNIQUE 或PRIMARY KEY 约束(它保证了语句只存取单行)的话，Oracle经常实现唯一性扫描。 使用唯一性约束的例子： SQL> explain plan for select empno，ename from emp where empno=10； Query Plan ------------------------------------ SELECT STATEMENT [CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX UNIQUE SCAN EMP_I1 (2) 索引范围扫描(index range scan) 使用一个索引存取多行数据，在唯一索引上使用索引范围扫描的典型情况下是在谓词(where限制条件)中使用了范围操作符(如>、、>=、<=、between) 使用索引范围扫描的例子： SQL> explain plan for select empno，ename from emp where empno > 7876 order by empno； Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT[CHOOSE] Cost=1 TABLE ACCESS BY ROWID EMP [ANALYZED] INDEX RANGE SCAN EMP_I1 [ANALYZED] 在非唯一索引上，谓词col = 5可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。 使用index rang scan的3种情况： (a) 在唯一索引列上使用了range操作符(> < <> >= <= between) (b) 在组合索引上，只使用部分列进行查询，导致查询出多行 (c) 对非唯一索引列上进行的任何查询。 (3) 索引全扫描(index full scan) 与全表扫描对应，也有相应的全索引扫描。而且此时查询出的数据都必须从索引中可以直接得到。 全索引扫描的例子： An Index full scan will not perform. single block i/o''s and so it may prove to be inefficient. e.g. Index BE_IX is a concatenated index on big_emp (empno， ename) SQL> explain plan for select empno， ename from big_emp order by empno，ename； Query Plan -------------------------------------------------------------------------------- SELECT STATEMENT[CHOOSE] Cost=26 INDEX FULL SCAN BE_IX [ANALYZED] (4) 索引快速扫描(index fast full scan) 扫描索引中的所有的数据块，与 index full scan很类似，但是一个显著的区别就是它不对查询出的数据进行排序，即数据不是以排序顺序被返回。在这种存取方法中，可以使用多块读功能，也可以使用并行读入，以便获得最大吞吐量与缩短执行时间。 索引快速扫描的例子： BE_IX索引是一个多列索引： big_emp (empno，ename) SQL> explain plan for select empno，ename from big_emp； Query Plan ------------------------------------------ SELECT STATEMENT[CHOOSE] Cost=1 INDEX FAST FULL SCAN BE_IX [ANALYZED] 只选择多列索引的第2列： SQL> explain plan for select ename from big_emp； Query Plan ------------------------------------------ SELECT STATEMENT[CHOOSE] Cost=1 INDEX FAST FULL SCAN BE_IX [ANALYZED] 三、表之间的连接 Join是一种试图将两个表结合在一起的谓词，一次只能连接2个表，表连接也可以被称为表关联。在后面的叙 述中，我们将会使用“row source”来代替“表”，因为使用row source更严谨一些，并且将参与连接的2个row source分别称为row source1和row source 2.Join过程的各个步骤经常是串行操作，即使相关的row source可以被并行访问，即可以并行的读取做join连接的两个row source的数据，但是在将表中符合限制条件的数据读入到内存形成row source后，join的其它步骤一般是串行的。有多种方法可以将2个表连接起来，当然每种方法都有自己的优缺点，每种连接类型只有在特定的条件下才会 发挥出其最大优势。 row source(表)之间的连接顺序对于查询的效率有非常大的影响。通过首先存取特定的表，即将该表作为驱动表，这样可以先应用某些限制条件，从而得到一个 较小的row source，使连接的效率较高，这也就是我们常说的要先执行限制条件的原因。一般是在将表读入内存时，应用where子句中对该表的限制条件。 根据2个row source的连接条件的中操作符的不同，可以将连接分为等值连接(如WHERE A.COL3 = B.COL4)、非等值连接(WHERE A.COL3 > B.COL4)、外连接(WHERE A.COL3 = B.COL4(+))。上面的各个连接的连接原理都基本一样，所以为了简单期间，下面以等值连接为例进行介绍。 在后面的介绍中，都以以下Sql为例进行说明： SELECT A.COL1， B.COL2 FROM A， B WHERE A.COL3 = B.COL4； 假设A表为Row Soruce1，则其对应的连接操作关联列为COL 3； B表为Row Soruce2，则其对应的连接操作关联列为COL 4； 连接类型： 目前为止，无论连接操作符如何，典型的连接类型共有3种： 排序 - - 合并连接(Sort Merge Join (SMJ) ) 嵌套循环(Nested Loops (NL) ) 哈希连接(Hash Join) 另外，还有一种Cartesian product(笛卡尔积)，一般情况下，尽量避免使用。 1，排序 - - 合并连接(Sort Merge Join， SMJ) 内部连接过程： 1) 首先生成row source1需要的数据，然后对这些数据按照连接操作关联列(如A.col3)进行排序。 2) 随后生成row source2需要的数据，然后对这些数据按照与sort source1对应的连接操作关联列(如B.col4)进行排序。 3) 最后两边已排序的行被放在一起执行合并操作，即将2个row source按照连接条件连接起来 下面是连接步骤的图形表示： MERGE /\ SORTSORT || Row Source 1Row Source 2 如果row source已经在连接关联列上被排序，则该连接操作就不需要再进行sort操作，这样可以大大提高这种连接操作的连接速度，因为排序是个极其费资源的操 作，特别是对于较大的表。预先排序的row source包括已经被索引的列(如a.col3或b.col4上有索引)或row source已经在前面的步骤中被排序了。尽管合并两个row source的过程是串行的，但是可以并行访问这两个row source(如并行读入数据，并行排序)。 SMJ连接的例子： SQL> explain plan for select /*+ ordered */ e.deptno， d.deptno from emp e， dept d where e.deptno = d.deptno order by e.deptno， d.deptno； Query Plan ------------------------------------- SELECT STATEMENT [CHOOSE] Cost=17 MERGE JOIN SORT JOIN TABLE ACCESS FULL EMP [ANALYZED] SORT JOIN TABLE ACCESS FULL DEPT [ANALYZED] 排序是一个费时、费资源的操作，特别对于大表。基于这个原因，SMJ经常不是一个特别有效的连接方法，但是如果2个row source都已经预先排序，则这种连接方法的效率也是蛮高的。 2，嵌套循环(Nested Loops， NL) 这个连接方法有驱动表(外部表)的概念。其实，该连接过程就是一个2层嵌套循环，所以外层循环的次数越少越好，这也就是我们为什么将小表或返回较小 row source的表作为驱动表(用于外层循环)的理论依据。但是这个理论只是一般指导原则，因为遵循这个理论并不能总保证使语句产生的I/O次数最少。有时 不遵守这个理论依据，反而会获得更好的效率。如果使用这种方法，决定使用哪个表作为驱动表很重要。有时如果驱动表选择不正确，将会导致语句的性能很差、很差。 内部连接过程： Row source1的Row 1 —— Probe ->Row source 2 Row source1的Row 2 —— Probe ->Row source 2 Row source1的Row 3 —— Probe ->Row source 2 ……。 Row source1的Row n —— Probe ->Row source 2 从内部连接过程来看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此时保持row source1尽可能的小与高效的访问row source2(一般通过索引实现)是影响这个连接效率的关键问题。这只是理论指导原则，目的是使整个连接操作产生最少的物理I/O次数，而且如果遵守这 个原则，一般也会使总的物理I/O数最少。但是如果不遵从这个指导原则，反而能用更少的物理I/O实现连接操作，那尽管违反指导原则吧！因为最少的物理 I/O次数才是我们应该遵从的真正的指导原则，在后面的具体案例分析中就给出这样的例子。 在上面的连接过程中，我们称Row source1为驱动表或外部表。Row Source2被称为被探查表或内部表。 在NESTED LOOPS连接中，Oracle读取row source1中的每一行，然后在row sourc2中检查是否有匹配的行，所有被匹配的行都被放到结果集中，然后处理row source1中的下一行。这个过程一直继续，直到row source1中的所有行都被处理。这是从连接操作中可以得到第一个匹配行的最快的方法之一，这种类型的连接可以用在需要快速响应的语句中，以响应速度为 主要目标。 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。 如果不使用并行操作，最好的驱动表是那些应用了where 限制条件后，可以返回较少行数据的的表，所以大表也可能称为驱动表，关键看限制条件。对于并行查询，我们经常选择大表作为驱动表，因为大表可以充分利用并 行功能。当然，有时对查询使用并行操作并不一定会比查询不使用并行操作效率高，因为最后可能每个表只有很少的行符合限制条件，而且还要看你的硬件配置是否 可以支持并行(如是否有多个CPU，多个硬盘控制器)，所以要具体问题具体对待。 NL连接的例子： SQL> explain plan for select a.dname，b.sql from dept a，emp b where a.deptno = b.deptno； Query Plan ------------------------- SELECT STATEMENT [CHOOSE] Cost=5 NESTED LOOPS TABLE ACCESS FULL DEPT [ANALYZED] TABLE ACCESS FULL EMP [ANALYZED] 3，哈希连接(Hash Join， HJ) 这种连接是在oracle 7.3以后引入的，从理论上来说比NL与SMJ更高效，而且只用在CBO优化器中。 较小的row source被用来构建hash table与bitmap，第2个row source被用来被hansed，并与第一个row source生成的hash table进行匹配，以便进行进一步的连接。Bitmap被用来作为一种比较快的查找方法，来检查在hash table中是否有匹配的行。特别的，当hash table比较大而不能全部容纳在内存中时，这种查找方法更为有用。这种连接方法也有NL连接中所谓的驱动表的概念，被构建为hash table与bitmap的表为驱动表，当被构建的hash table与bitmap能被容纳在内存中时，这种连接方式的效率极高。 HASH连接的例子： SQL> explain plan for select /*+ use_hash(emp) */ empno from emp， dept where emp.deptno = dept.deptno； Query Plan ---------------------------- SELECT STATEMENT[CHOOSE] Cost=3 HASH JOIN TABLE ACCESS FULL DEPT TABLE ACCESS FULL EMP 要使哈希连接有效，需要设置HASH_JOIN_ENABLED=TRUE，缺省情况下该参数为TRUE，另外，不要忘了还要设置 hash_area_size参数，以使哈希连接高效运行，因为哈希连接会在该参数指定大小的内存中运行，过小的参数会使哈希连接的性能比其他连接方式还 要低。 另外，笛卡儿乘积(Cartesian Product) 当两个row source做连接，但是它们之间没有关联条件时，就会在两个row source中做笛卡儿乘积，这通常由编写代码疏漏造成(即程序员忘了写关联条件)。笛卡尔乘积是一个表的每一行依次与另一个表中的所有行匹配。在特殊情况下我们可以使用笛卡儿乘积，如在星形连接中，除此之外，我们要尽量不使用笛卡儿乘积，否则，自己想结果是什么吧！ 注意在下面的语句中，在2个表之间没有连接。 SQL> explain plan for select emp.deptno，dept，deptno from emp，dept Query Plan ------------------------ SLECT STATEMENT [CHOOSE] Cost=5 MERGE JOIN CARTESIAN TABLE ACCESS FULL DEPT SORT JOIN TABLE ACCESS FULL EMP CARTESIAN关键字指出了在2个表之间做笛卡尔乘积。假如表emp有n行，dept表有m行，笛卡尔乘积的结果就是得到n * m行结果。 最后，总结一下，在哪种情况下用哪种连接方法比较好： 排序 - - 合并连接(Sort Merge Join， SMJ)： a) 对于非等值连接，这种连接方式的效率是比较高的。 b) 如果在关联的列上都有索引，效果更好。 c) 对于将2个较大的row source做连接，该连接方法比NL连接要好一些。 d) 但是如果sort merge返回的row source过大，则又会导致使用过多的rowid在表中查询数据时，数据库性能下降，因为过多的I/O. 嵌套循环(Nested Loops， NL)： a) 如果driving row source(外部表)比较小，并且在inner row source(内部表)上有唯一索引，或有高选择性非唯一索引时，使用这种方法可以得到较好的效率。 b) NESTED LOOPS有其它连接方法没有的的一个优点是：可以先返回已经连接的行，而不必等待所有的连接操作处理完才返回数据，这可以实现快速的响应时间。 哈希连接(Hash Join， HJ)： a) 这种方法是在oracle7后来引入的，使用了比较先进的连接理论，一般来说，其效率应该好于其它2种连接，但是这种连接只能用在CBO优化器中，而且需要设置合适的hash_area_size参数，才能取得较好的性能。 b) 在2个较大的row source之间连接时会取得相对较好的效率，在一个row source较小时则能取得更好的效率。 c) 只能用于等值连接中 +++ Oracle执行计划的概述 --- Oracle执行计划的相关概念： Rowid：系统给oracle数据的每行附加的一个伪列，包含数据表名称，数据库id，存储数据库id以及一个流水号等信息，rowid在行的生命周期内唯一。 Recursive sql：为了执行用户语句，系统附加执行的额外操作语句，譬如对数据字典的维护等。 Row source(行源)：oracle执行步骤过程中，由上一个操作返回的符合条件的行的集合。 Predicate(谓词)：where后的限制条件。 Driving table(驱动表)：又称为连接的外层表，主要用于嵌套与hash连接中。一般来说是将应用限制条件后，返回较少行源的表作为驱动表。在后面的描述中，将driving table称为连接操作的row source 1。 Probed table(被探查表)：连接的内层表，在我们从driving table得到具体的一行数据后，在probed table中寻找符合条件的行，所以该表应该为较大的row source，并且对应连接条件的列上应该有索引。在后面的描述中，一般将该表称为连接操作的row source 2. Concatenated index(组合索引)：一个索引如果由多列构成，那么就称为组合索引，组合索引的第一列为引导列，只有谓词中包含引导列时，索引才可用。 可选择性：表中某列的不同数值数量/表的总行数如果接近于1，则列的可选择性为高。 Oracle访问数据的存取方法： Full table scans, FTS(全表扫描)：通过设置db_block_multiblock_read_count可以设置一次IO能读取的数据块个数，从而有效减少全表扫描时的IO总次数，也就是通过预读机制将将要访问的数据块预先读入内存中。只有在全表扫描情况下才能使用多块读操作。 Table Access by rowed(通过rowid存取表，rowid lookup)：由于rowid中记录了行存储的位置，所以这是oracle存取单行数据的最快方法。 Index scan(索引扫描index lookup)：在索引中，除了存储每个索引的值外，索引还存储具有此值的行对应的rowid值，索引扫描分两步1，扫描索引得到rowid；2，通过 rowid读取具体数据。每步都是单独的一次IO，所以如果数据经限制条件过滤后的总量大于原表总行数的5%－10％,则使用索引扫描效率下降很多。而如果结果数据能够全部在索引中找到，则可以避免第二步操作，从而加快检索速度。 根据索引类型与where限制条件的不同，有4种类型的索引扫描： Index unique scan(索引唯一扫描)：存在unique或者primary key的情况下，返回单个rowid数据内容。 Index range scan(索引范围扫描)：1，在唯一索引上使用了range操作符(>,,>=,<=,between)；2，在组合索引上，只使用部分列进行查询；3，对非唯一索引上的列进行的查询。 Index full scan(索引全扫描)：需要查询的数据从索引中可以全部得到。 Index fast full scan(索引快速扫描)：与index full scan类似，但是这种方式下不对结果进行排序。 目前为止，典型的连接类型有3种： Sort merge join(SMJ排序－合并连接)：首先生产driving table需要的数据，然后对这些数据按照连接操作关联列进行排序；然后生产probed table需要的数据，然后对这些数据按照与driving table对应的连接操作列进行排序；最后两边已经排序的行被放在一起执行合并操作。排序是一个费时、费资源的操作，特别对于大表。所以smj通常不是一个特别有效的连接方法，但是如果driving table和probed table都已经预先排序，则这种连接方法的效率也比较高。 Nested loops(NL嵌套循环)：连接过程就是将driving table和probed table进行一次嵌套循环的过程。就是用driving table的每一行去匹配probed table 的所有行。Nested loops可以先返回已经连接的行，而不必等待所有的连接操作处理完成才返回数据，这可以实现快速的响应时间。 Hash join(哈希连接)：较小的row source被用来构建hash table与bitmap，第二个row source用来被hashed，并与第一个row source生产的hash table进行匹配。以便进行进一步的连接。当被构建的hash table与bitmap能被容纳在内存中时，这种连接方式的效率极高。但需要设置合适的hash_area_size参数且只能用于等值连接中。 另外，还有一种连接类型：Cartesian product(笛卡尔积)：表的每一行依次与另外一表的所有行匹配，一般情况下，尽量避免使用。
  • 回表
    • 当对一个列创建索引之后，索引会包含该列的键值以及键值对应行所在的rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表一般是单块读，回表次数太多会严重影响SQL性能，如果回表次数太多，就不应该走索引扫描了，应该直接走全表扫描。 在进行SQL优化的时候，一定要注意回表次数！特别是要注意回表的物理I/O次数！ SQL> select * from test where owner = 'SYS'; 30812 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 3932013684 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2499 | 236K| 73 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST | 2499 | 236K| 73 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_OWNER | 2499 | | 6 (0)| 00:00:01 | ----------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OWNER"='SYS') Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4927 consistent gets 69 physical reads 0 redo size 3502852 bytes sent via SQL*Net to client 23117 bytes received via SQL*Net from client 2056 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 30812 rows processed SQL> 执行计划中加粗部分（TABLE ACCESS BY INDEX ROWID）就是回表。索引返回多少行数据。回表就要回多少次，每次回表都是单块读（因为一个rowid对应一个数据块）该SQL返回了30812行数据，那么回表一共就需要30812次。 请思考：上面执行计划的性能是耗费在索引扫描中还是耗费在回表中？ 为了得到答案，请大家在SQLPLUS中进行实验。为了消除arraysize 参数对逻辑读的影响，设置arraysize=5000。arraysize表示Oracle服务器每次传输多少行数据到客户端，默认为15。如果一个块有150行数据，那么这个块就会被读10次，因为每次只传输15行数据到客户端，逻辑读会被放大。设置了arraysize=5000之后，就不会发生一个块被读n次的问题了。 SQL> set pagesize 100 SQL> set line 200 SQL> set arraysize 5000 SQL> set autot trace SQL> select owner from test where owner='SYS'; 30812 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1086061979 ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 30812 | 180K| 48 (0)| 00:00:01 | |* 1 | INDEX FAST FULL SCAN| IDX_OWNER | 30812 | 180K| 48 (0)| 00:00:01 | ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("OWNER"='SYS') Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 186 consistent gets 0 physical reads 0 redo size 155727 bytes sent via SQL*Net to client 589 bytes received via SQL*Net from client 8 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 30812 rows processed SQL> 从上面的实验可见，索引扫描只耗费了186个逻辑读。 SQL> set autot off SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks from test where owner = 'SYS'; BLOCKS ---------- 798 SQL> 该SQL的性能确实绝大部分损失在回表中。 更糟糕的是：假设30812 条数据都在不同的数据块中，表也没有被缓存在buffer cache中，那么回表一共需要耗费30812个物理I/O，这太可怕了。 在无法避免回表的情况下，走索引如果返回数据量太多，必然会导致回表的次数太多，从而导致性能严重性能严重下降。 什么样的SQL必须要回表？ select * from table where ... 这样的SQL就必须回表，所以我们必须严禁使用select * 。那什么样的SQL不需要回表？ select count(*) from table 这样的SQL就不需要回表。 当要查询的列也包含在索引中，这个时候就不需要回表中了，所以我们往往会建立组合索引来消除回表，从而提升查询性能。 当一个SQL有多个过滤条件但是只在一个列或者部分列建立了索引，这个时候会发生回表再过滤（TABLE ACCESS BY INDEX ROWID 前面有“*”） 也需要创建组合索引，进而消除回表再过滤，从而提升查询性能。