LANGCHI_数仓作业

最新推荐文章于 2024-06-15 21:40:09 发布
JUMPLANB
最新推荐文章于 2024-06-15 21:40:09 发布
阅读量158
点赞数
文章标签：数据库 oracle dba
本文链接：https://blog.csdn.net/B_planzp/article/details/127877025
版权
1、系统准备
 -- 在 sysdba 下操作
 --创建用户并赋予相关权限
create user STG  identified by "123456"  ;
create user ODS  identified by "123456"  ;
create user DWD  identified by "123456"  ;
create user DWA  identified by "123456"  ;
create user DM   identified by "123456"  ;
create user DIM   identified by "123456"  ;
-- 给予权限，创建数据库、存储过程、用户之间的连接、建表的权限
grant resource,connect ,dba to STG     ;
grant resource,connect ,dba to ODS     ;
grant resource,connect ,dba to DWD     ;
grant resource,connect ,dba to DWA     ;
grant resource,connect ,dba to DM      ;
grant resource,connect ,dba to DIM      ;
-- 删除用户测试____ 不用管
drop user DWD cascade; 
drop user DM cascade; 
drop user DWA cascade; 
drop user DIM cascade; 
-- 查看当前的表空间基本情况——————表空间的默认路径
select tablespace_name ,file_name,bytes/1024/1024 as MB from dba_data_files;
-- 创建表空间
-- 默认100M。不够的话自动增加100M
create tablespace TBS_DWD logging datafile '/oracle/app/oradata/ecom/DWD.dbf' size 100m autoextend on next 100mmaxsize unlimited extent management local;
create tablespace TBS_DWA logging datafile '/oracle/app/oradata/ecom/DWA.dbf' size 100m autoextend on next 100m maxsize unlimited extent management local;
create tablespace TBS_DM logging datafile '/oracle/app/oradata/ecom/DM.dbf' size 100m autoextend on next 100m maxsize unlimited extent management local;
create tablespace TBS_ODS logging datafile '/oracle/app/oradata/ecom/ODS.dbf' size 100m autoextend on next 100m maxsize unlimited extent management local;
create tablespace TBS_STG logging datafile '/oracle/app/oradata/ecom/STG.dbf' size 100m autoextend on next 100m maxsize unlimited extent management local;
create tablespace TBS_DIM logging datafile '/oracle/app/oradata/ecom/DIM.dbf' size 100m autoextend on next 100m maxsize unlimited extent management local;

select * from all_users; 查看所有用户
drop tablespace TBS_DWD including contents cascade constraints;
drop tablespace TBS_DWA including contents cascade constraints;
drop tablespace TBS_DM including contents cascade constraints;
drop tablespace TBS_ODS including contents cascade constraints;
drop tablespace TBS_STG including contents cascade constraints;
drop tablespace TBS_DIM including contents cascade constraints;
=============================================================================
2、进入STG用户，新建用户基础信息表
create table STG.STG_D_USE_BASE_INFO
(					
date_id      VARCHAR2(8),
area_id      VARCHAR2(10),
user_id      VARCHAR2(20),
device_number VARCHAR2(11),
service_type VARCHAR2(8),
innet_date  VARCHAR2(8),
innet_months NUMBER
)
TABLESPACE TBS_STG
PARTITION BY RANGE(date_id)
SUBPARTITION BY LIST(area_id)
(
 PARTITION PART_20190401 VALUES LESS THAN ('20190402')
 TABLESPACE TBS_STG
 (
  subpartition PART_20190401_SUBPART_01 values ('01') tablespace TBS_STG,
  subpartition PART_20190401_SUBPART_02 values ('02') tablespace TBS_STG,
  subpartition PART_20190401_SUBPART_03 values ('03') tablespace TBS_STG,
  subpartition PART_20190401_SUBPART_04 values ('04') tablespace TBS_STG,	
  subpartition PART_20190401_SUBPART_05 values ('05') tablespace TBS_STG,	
  subpartition PART_20190401_SUBPART_06 values ('06') tablespace TBS_STG,
  subpartition PART_20190401_SUBPART_07 values ('07') tablespace TBS_STG,
  subpartition PART_20190401_SUBPART_08 values ('08') tablespace TBS_STG,
  subpartition PART_20190401_SUBPART_09 values ('09') tablespace TBS_STG,
  subpartition PART_20190401_SUBPART_10 values ('10') tablespace TBS_STG,
  subpartition PART_20190401_SUBPART_11 values ('11') tablespace TBS_STG
 )
);
-- 创建索引
CREATE INDEX STG.IDX_SDUBI_DATE_ID ON STG.STG_D_USE_BASE_INFO (DATE_ID) TABLESPACE TBS_STG;
CREATE INDEX STG.IDX_SDUBI_AREA_ID ON STG.STG_D_USE_BASE_INFO (AREA_ID) TABLESPACE TBS_STG;
CREATE INDEX STG.IDX_SDUBI_USER_ID ON STG.STG_D_USE_BASE_INFO (USER_ID) TABLESPACE TBS_STG;
CREATE INDEX STG.IDX_SDUBI_DN ON STG.STG_D_USE_BASE_INFO (DEVICE_NUMBER) TABLESPACE TBS_STG;	
CREATE INDEX STG.IDX_SDUBI_ST ON STG.STG_D_USE_BASE_INFO (SERVICE_TYPE) TABLESPACE TBS_STG;
-- 在表上面，列上面的索引

COMMENT ON TABLE  STG.STG_D_USE_BASE_INFO IS '用户基础信息';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.date_id is '日期ID';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.area_id is '地市ID';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.user_id is '用户ID';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.device_number is '手机号码';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.service_type is '业务类型';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.innet_date is '入网日期';
COMMENT ON COLUMN STG.STG_D_USE_BASE_INFO.innet_months is '在网时长';				
-- 更改表上属性的数据类型来适应数据
alter table STG_D_USE_BASE_INFO  modify user_id VARCHAR2(50);
-- 重新修改表分区
ALTER TABLE STG_D_USE_BASE_INFO ADD PARTITION  PART_20190402 VALUES LESS THAN ('20190403')
 TABLESPACE TBS_STG					
 (
  subpartition PART_20190402_SUBPART_01 values ('01') tablespace TBS_STG,
  subpartition PART_20190402_SUBPART_02 values ('02') tablespace TBS_STG,
  subpartition PART_20190402_SUBPART_03 values ('03') tablespace TBS_STG,
  subpartition PART_20190402_SUBPART_04 values ('04') tablespace TBS_STG,
  subpartition PART_20190402_SUBPART_05 values ('05') tablespace TBS_STG,
  subpartition PART_20190402_SUBPART_06 values ('06') tablespace TBS_STG,
  subpartition PART_20190402_SUBPART_07 values ('07') tablespace TBS_STG,
  subpartition PART_20190402_SUBPART_08 values ('08') tablespace TBS_STG,
  subpartition PART_20190402_SUBPART_09 values ('09') tablespace TBS_STG,
  subpartition PART_20190402_SUBPART_10 values ('10') tablespace TBS_STG,
  subpartition PART_20190402_SUBPART_11 values ('11') tablespace TBS_STG
 );
 ALTER TABLE STG_D_USE_BASE_INFO ADD PARTITION  PART_20200301 VALUES LESS THAN ('20200302')
 TABLESPACE TBS_STG					
 (
  subpartition PART_20200301_SUBPART_01 values ('01') tablespace TBS_STG,
  subpartition PART_20200301_SUBPART_02 values ('02') tablespace TBS_STG,
  subpartition PART_20200301_SUBPART_03 values ('03') tablespace TBS_STG,
  subpartition PART_20200301_SUBPART_04 values ('04') tablespace TBS_STG,
  subpartition PART_20200301_SUBPART_05 values ('05') tablespace TBS_STG,
  subpartition PART_20200301_SUBPART_06 values ('06') tablespace TBS_STG,
  subpartition PART_20200301_SUBPART_07 values ('07') tablespace TBS_STG,
  subpartition PART_20200301_SUBPART_08 values ('08') tablespace TBS_STG,
  subpartition PART_20200301_SUBPART_09 values ('09') tablespace TBS_STG,
  subpartition PART_20200301_SUBPART_10 values ('10') tablespace TBS_STG,
  subpartition PART_20200301_SUBPART_11 values ('11') tablespace TBS_STG
 );
 ALTER TABLE STG_D_USE_BASE_INFO ADD PARTITION  PART_20200302 VALUES LESS THAN ('20200303')
 TABLESPACE TBS_STG					
 (
  subpartition PART_20200302_SUBPART_01 values ('01') tablespace TBS_STG,
  subpartition PART_20200302_SUBPART_02 values ('02') tablespace TBS_STG,
  subpartition PART_20200302_SUBPART_03 values ('03') tablespace TBS_STG,
  subpartition PART_20200302_SUBPART_04 values ('04') tablespace TBS_STG,
  subpartition PART_20200302_SUBPART_05 values ('05') tablespace TBS_STG,
  subpartition PART_20200302_SUBPART_06 values ('06') tablespace TBS_STG,
  subpartition PART_20200302_SUBPART_07 values ('07') tablespace TBS_STG,
  subpartition PART_20200302_SUBPART_08 values ('08') tablespace TBS_STG,
  subpartition PART_20200302_SUBPART_09 values ('09') tablespace TBS_STG,
  subpartition PART_20200302_SUBPART_10 values ('10') tablespace TBS_STG,
  subpartition PART_20200302_SUBPART_11 values ('11') tablespace TBS_STG
 );
 ALTER TABLE STG_D_USE_BASE_INFO ADD PARTITION  PART_20200401 VALUES LESS THAN ('20200402')
 TABLESPACE TBS_STG					
 (
  subpartition PART_20200401_SUBPART_01 values ('01') tablespace TBS_STG,
  subpartition PART_20200401_SUBPART_02 values ('02') tablespace TBS_STG,
  subpartition PART_20200401_SUBPART_03 values ('03') tablespace TBS_STG,
  subpartition PART_20200401_SUBPART_04 values ('04') tablespace TBS_STG,
  subpartition PART_20200401_SUBPART_05 values ('05') tablespace TBS_STG,
  subpartition PART_20200401_SUBPART_06 values ('06') tablespace TBS_STG,
  subpartition PART_20200401_SUBPART_07 values ('07') tablespace TBS_STG,
  subpartition PART_20200401_SUBPART_08 values ('08') tablespace TBS_STG,
  subpartition PART_20200401_SUBPART_09 values ('09') tablespace TBS_STG,
  subpartition PART_20200401_SUBPART_10 values ('10') tablespace TBS_STG,
  subpartition PART_20200401_SUBPART_11 values ('11') tablespace TBS_STG
 );
 ALTER TABLE STG_D_USE_BASE_INFO ADD PARTITION  PART_20200402 VALUES LESS THAN ('20200403')
 TABLESPACE TBS_STG					
 (
  subpartition PART_20200402_SUBPART_01 values ('01') tablespace TBS_STG,
  subpartition PART_20200402_SUBPART_02 values ('02') tablespace TBS_STG,
  subpartition PART_20200402_SUBPART_03 values ('03') tablespace TBS_STG,
  subpartition PART_20200402_SUBPART_04 values ('04') tablespace TBS_STG,
  subpartition PART_20200402_SUBPART_05 values ('05') tablespace TBS_STG,
  subpartition PART_20200402_SUBPART_06 values ('06') tablespace TBS_STG,
  subpartition PART_20200402_SUBPART_07 values ('07') tablespace TBS_STG,
  subpartition PART_20200402_SUBPART_08 values ('08') tablespace TBS_STG,
  subpartition PART_20200402_SUBPART_09 values ('09') tablespace TBS_STG,
  subpartition PART_20200402_SUBPART_10 values ('10') tablespace TBS_STG,
  subpartition PART_20200402_SUBPART_11 values ('11') tablespace TBS_STG
 );
 -- 删除之前的错误分区
ALTER TABLE STG_D_USE_BASE_INFO DROP PARTITION PART_20190402;
-- 进入dim用户
2.地市码表
create table DIM.DIM_AREA_ID            
(          
area_id varchar2(10) ,          
area_desc varchar2(100)          
)          
tablespace TBS_DIM;          
COMMENT ON TABLE DIM.DIM_AREA_ID IS '地市码表';          
COMMENT ON COLUMN DIM.DIM_AREA_ID.area_id is '地市ID';          
COMMENT ON COLUMN DIM.DIM_AREA_ID.area_desc is '地市描述';          
CREATE INDEX DIM.IDX_DAI_AREA_ID ON DIM.DIM_AREA_ID (AREA_ID) TABLESPACE TBS_DIM;
3.业务类型码表					
create TABLE  DIM.DIM_SERVICE_TYPE					
(					
service_type varchar2(10),					
service_type_desc  varchar2(100)					
)					
tablespace TBS_DIM;					
COMMENT ON TABLE DIM.DIM_SERVICE_TYPE IS '业务类型码表'; 					
COMMENT ON COLUMN DIM.DIM_SERVICE_TYPE.service_type is '业务类型';					
COMMENT ON COLUMN DIM.DIM_SERVICE_TYPE.service_type_desc is '业务类型描述';					
CREATE INDEX DIM.IDX_DST_SERVICE_TYPE ON DIM.DIM_SERVICE_TYPE (service_type) TABLESPACE TBS_DIM;	

select * from DIM.DIM_SERVICE_TYPE;
-- 插入数据
          
insert into DIM.DIM_SERVICE_TYPE values('10AAAAAA','宽带');
insert all
into DIM.DIM_SERVICE_TYPE values('50AAAAAA','固定电话')
into DIM.DIM_SERVICE_TYPE values('40AAAAAA','4G业务')
into DIM.DIM_SERVICE_TYPE values('30AAAAAA','3G业务')
into DIM.DIM_SERVICE_TYPE values('20AAAAAA','2G业务')
select 1 from dual;


-- Linux终端连接检测 ods 是否连接
-- dim用户
2.地市码表          
create table DIM.DIM_AREA_ID            
(          
area_id varchar2(10) ,          
area_desc varchar2(100)          
)          
tablespace TBS_DIM;          
COMMENT ON TABLE DIM.DIM_AREA_ID IS '地市码表';          
COMMENT ON COLUMN DIM.DIM_AREA_ID.area_id is '地市ID';          
COMMENT ON COLUMN DIM.DIM_AREA_ID.area_desc is '地市描述';          
CREATE INDEX DIM.IDX_DAI_AREA_ID ON DIM.DIM_AREA_ID (AREA_ID) TABLESPACE TBS_DIM;

select * from DIM.DIM_AREA_ID for update;    
3.业务类型码表          
create TABLE  DIM.DIM_SERVICE_TYPE          
(          
service_type varchar2(10),          
service_type_desc  varchar2(100)          
)          
tablespace TBS_DIM;          
COMMENT ON TABLE DIM.DIM_SERVICE_TYPE IS '业务类型码表';           
COMMENT ON COLUMN DIM.DIM_SERVICE_TYPE.service_type is '业务类型';          
COMMENT ON COLUMN DIM.DIM_SERVICE_TYPE.service_type_desc is '业务类型描述';          
CREATE INDEX DIM.IDX_DST_SERVICE_TYPE ON DIM.DIM_SERVICE_TYPE (service_type) TABLESPACE TBS_DIM;  

select * from DIM.DIM_SERVICE_TYPE;
-- 插入数据
insert into DIM.DIM_SERVICE_TYPE values('10AAAAAA','宽带');
insert all
into DIM.DIM_SERVICE_TYPE values('50AAAAAA','固定电话')
into DIM.DIM_SERVICE_TYPE values('40AAAAAA','4G业务')
into DIM.DIM_SERVICE_TYPE values('30AAAAAA','3G业务')
into DIM.DIM_SERVICE_TYPE values('20AAAAAA','2G业务')
select 1 from dual;
commit
-- ods 用户
日志： 为了定义错误

--SEGMENT CREATION DEFERRED Oracle_11g 特性

-- 创建日志表以及索引
CREATE TABLE "ODS"."ODS_EXECUTE_LOG" 					
   (ACCT_MONTH VARCHAR2(16), 				
	PKG_NAME VARCHAR2(30), 				
	PROCNAME VARCHAR2(100), 				
	PROV_ID VARCHAR2(10), 				
	STARTDATE DATE, 				
	ENDDATE DATE, 				
	RESULT VARCHAR2(4000), 				
	DURATION NUMBER, 				
	NOTE VARCHAR2(4000), 				
	ROW_COUNT NUMBER, 				
	TABLE_NAME VARCHAR2(60)				
   )  					
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING					
  STORAGE( INITIAL 163840 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 2147483645)					
  TABLESPACE "TBS_ODS" ;					
 	-- 创建索引： 数据量过大时，创建B树索引查询加快
  CREATE INDEX "ODS"."IDX_ODS_EXECUTE_LOG" ON "ODS"."ODS_EXECUTE_LOG" ("ACCT_MONTH", "PROCNAME", "PROV_ID") 					
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOCOMPRESS LOGGING					
  STORAGE( INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645)					
  TABLESPACE "TBS_ODS" ;
  
  -- 创建历史日志表
  CREATE TABLE "ODS"."ODS_EXECUTE_LOG_HIS" 		                        --HIS:历史日志表			
   (	ACCT_MONTH VARCHAR2(16), 				
	PKG_NAME VARCHAR2(30), 				
	PROCNAME VARCHAR2(100), 				
	PROV_ID VARCHAR2(10), 				
	STARTDATE DATE, 				
	ENDDATE DATE, 				
	RESULT VARCHAR2(4000), 				
	DURATION NUMBER, 				
	NOTE VARCHAR2(4000), 				
	ROW_COUNT NUMBER, 				
	TABLE_NAME VARCHAR2(196), 				
	INSERT_DATE DATE				
   )                                                           -- SEGMENT CREATION DEFERRED 					
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING					
  STORAGE( INITIAL 163840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645)					
  TABLESPACE "TBS_ODS" ;
  
  CREATE TABLE "ODS"."SQLPARSER_LOG_GENERAL" 					
   (LOG_SN NUMBER, 				
	ACCT_MONTH VARCHAR2(10), 				
	OWNER VARCHAR2(20), 				
	PROG_NAME VARCHAR2(1000), 				
	PROV_ID VARCHAR2(10), 				
	PROG_VERSION VARCHAR2(100), 				
	MODIFY_DATE DATE, 				
	INPUT_PARA VARCHAR2(200), 				
	BEGIN_TIME DATE, 				
	END_TIME DATE, 				
	RESULT VARCHAR2(4000), 				
	DURATION NUMBER, 				
	NOTE VARCHAR2(4000), 				
	TABLE_NAME VARCHAR2(60)				
   )                                                                               --SEGMENT CREATION DEFERRED 					
  PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING					
  STORAGE( INITIAL 163840 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 2147483645)					
  TABLESPACE "TBS_ODS" ;
  
  
    CREATE TABLE "ODS"."SQLPARSER_LOG_GENERAL" 					
   (LOG_SN NUMBER, 				
	ACCT_MONTH VARCHAR2(10), 				
	OWNER VARCHAR2(20), 				
	PROG_NAME VARCHAR2(1000), 				
	PROV_ID VARCHAR2(10), 				
	PROG_VERSION VARCHAR2(100), 				
	MODIFY_DATE DATE, 				
	INPUT_PARA VARCHAR2(200), 				
	BEGIN_TIME DATE, 				
	END_TIME DATE, 				
	RESULT VARCHAR2(4000), 				
	DURATION NUMBER, 				
	NOTE VARCHAR2(4000), 				
	TABLE_NAME VARCHAR2(60)				
   )                                                                               --SEGMENT CREATION DEFERRED 					
  PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING					
  STORAGE( INITIAL 163840 NEXT 32768 MINEXTENTS 1 MAXEXTENTS 2147483645)					
  TABLESPACE "TBS_ODS" ;
  
  -- 序列号：类似mysql 随机数生成，生成整数
  create sequence ODS.SEQ_ODS_SQLPARSER					
minvalue 1					
maxvalue 999999999999999999999999999					
start with 1					
increment by 1					
cache 20;
 

-- 日志---> 存储过程

-- 创建存储过程

CREATE OR REPLACE PROCEDURE P_INSERT_LOG(					
   ACCT_MONTH#  VARCHAR2,					
   PKG_NAME#   VARCHAR2,							-- 定义字段			
   PROCNAME#    VARCHAR2,					
   PROV_ID#      VARCHAR2,					
   STARTDATE#   DATE,					
   TAB_NAME      VARCHAR2 DEFAULT NULL					
   ) IS					
  /*-------------------------------------------------------------------------------------------					
     过 程 名 : 生成存储过程日志信息					
     生成时间 ：20071126					
     编 写 人 ：KANGTAO					
     生成周期 ：					
     执行时间 : ( 分钟)					
     使用参数 ：					
     修改记录 :					
  -----------------------------------------------------------------------------------------------*/					
   V_TAB_NAME VARCHAR2(60);					
BEGIN					
  --日志部分, 把重复执行的过程的日志记录到日志历史表中					
  INSERT INTO ODS_EXECUTE_LOG_HIS					
      SELECT A.*, SYSDATE FROM ODS_EXECUTE_LOG A					
      WHERE ACCT_MONTH = ACCT_MONTH# --AND PKG_NAME=UPPER(PKG_NAME#)					
          AND PROCNAME = UPPER(PROCNAME#)  AND PROV_ID=PROV_ID# ;					
  DELETE ODS_EXECUTE_LOG					
   WHERE ACCT_MONTH = ACCT_MONTH# AND PKG_NAME=UPPER(PKG_NAME#)					
     AND PROCNAME = UPPER(PROCNAME#)  AND PROV_ID=PROV_ID#  ;					
  V_TAB_NAME := UPPER(NVL(TAB_NAME, SUBSTR(PROCNAME#,3)));					
  INSERT INTO ODS_EXECUTE_LOG					
    (ACCT_MONTH, PKG_NAME, PROCNAME, PROV_ID,  STARTDATE ,NOTE, TABLE_NAME)					
  VALUES					
    (ACCT_MONTH#, UPPER(PKG_NAME#), UPPER(PROCNAME#), PROV_ID#, STARTDATE# ,'开始', V_TAB_NAME);					
  COMMIT;					
END P_INSERT_LOG;		
commit
select * from user_procedures;

CREATE OR REPLACE PROCEDURE P_INSERT_SQLPARSER_LOG_GENERAL(					
   LOG_SN#      NUMBER, --运行日志序号					
   ACCT_MONTH#  VARCHAR2, --执行帐期					
   PROV_ID#     VARCHAR2, --执行省分代码					
   OWNER#       VARCHAR2, --脚本所在的用户					
   PROCNAME#    VARCHAR2, --脚本名称					
   V_PARA_STR#  VARCHAR2, --脚本程序输入参数  ,例如 V_MONTH=201103;V_PROV=010					
   STARTDATE#   Date , --脚本开始执行时间					
   table_name#  varchar2  --目标表					
					
   ) IS									
  /*-------------------------------------------------------------------------------------------					
     过 程 名 : 运行日志总体表					
     生成时间 ：20110425					
     编 写 人 ：JCM					
     生成周期 ：					
     执行时间 :					
     使用参数 ：					
     修改记录 :					
  -----------------------------------------------------------------------------------------------*/									
   V_MODIFY_DATE DATE; --脚本程序最近修改时间					
					
					
BEGIN									
 delete from SQLPARSER_LOG_GENERAL					
 where log_sn=LOG_SN#;					
 commit;					
								
 --获取脚本最近一次修改时间					
 SELECT max(LAST_DDL_TIME)					
     INTO V_MODIFY_DATE					
   FROM SYS.ALL_OBJECTS T					
  WHERE UPPER(OBJECT_NAME)=UPPER(PROCNAME#)					
    AND OWNER=upper(OWNER#); --					
							
        insert/*+append*/ into SQLPARSER_LOG_GENERAL nologging					
           (  LOG_SN  ,--  运行日志序号					
              ACCT_MONTH  ,--  脚本运行的帐期					
              OWNER  ,--  脚本所在的用户					
              PROG_NAME  ,--  脚本程序名					
              PROV_ID  ,--  脚本运行的省分代码					
              PROG_VERSION  ,--  脚本程序版本号					
              MODIFY_DATE  ,--  脚本程序最近修改时间					
              INPUT_PARA  ,--  脚本程序输入参数					
              BEGIN_TIME  ,--  脚本程序启动时间					
              END_TIME,  --  程序脚本结束时间					
              result ,					
              DURATION,  --脚本执行时长（秒)					
              note,					
              table_name					
          )					
					
        VALUES( LOG_SN# ,--运行日志序号					
                ACCT_MONTH#   ,--  脚本运行的帐期					
                upper(OWNER#)  ,--  脚本所在的用户					
                PROCNAME#   ,--  脚本程序名					
                PROV_ID#  ,--  脚本运行的省分代码					
                '1.0'  ,--  脚本程序版本号, 默认版本为 1.0					
                V_MODIFY_DATE  ,--  脚本程序最近修改时间					
                V_PARA_STR#  ,--  脚本程序输入参数					
                STARTDATE#  ,--  脚本程序启动时间					
                NULL  ,--  程序脚本结束时间					
                NULL, --运行结果					
                NULL, --脚本执行时长（秒)					
                NULL,					
                table_name# --目标表					
              );					
					
        COMMIT;					
					
					
					
END ;	

CREATE OR REPLACE PROCEDURE ODS.P_UPDATE_LOG(ACCT_MONTH#    VARCHAR2,					
                                         PKGNAME#       VARCHAR2,					
                                         PROCNAME#      VARCHAR2,					
                                         PROV_ID#       VARCHAR2,					
                                         NOTE#          VARCHAR2,					
                                         RESULT#        VARCHAR2,					
                                         ENDDATE#       DATE,					
                                         ROWLINE#       Number)					
IS					
  /*-------------------------------------------------------------------------------------------					
     过 程 名 : 生成更新日志信息					
     生成时间 ：20100724					
     编 写 人 ：TAOK					
     生成周期 ：					
     执行时间 : (分钟)					
     使用参数 ：日期					
  -----------------------------------------------------------------------------------------------*/					
BEGIN					
  UPDATE ODS_EXECUTE_LOG					
     SET ENDDATE  = ENDDATE#,					
         RESULT   = RESULT#,					
         DURATION = (ENDDATE# - STARTDATE) * 24 * 3600,					
         NOTE     = NOTE#,					
         ROW_COUNT = ROWLINE#					
   WHERE ACCT_MONTH = ACCT_MONTH#					
     AND PROCNAME = PROCNAME#					
     AND PROV_ID = PROV_ID# ;					
  COMMIT;					
					
END;

CREATE OR REPLACE PROCEDURE ODS.P_UPDATE_SQLPARSER_LOG_GENERAL(					
   LOG_SN#      NUMBER, --运行日志序号					
   result#      varchar2, --运行结果 SUCCESS /FAIL					
   note#        varchar2 --备注					
					
   ) IS					
					
  /*-------------------------------------------------------------------------------------------					
     过 程 名 : 运行日志总体表					
     生成时间 ：20110425					
     编 写 人 ：JCM					
     生成周期 ：					
     执行时间 :					
     使用参数 ：					
     修改记录 :					
  -----------------------------------------------------------------------------------------------*/					
					
					
					
BEGIN					
					
  UPDATE SQLPARSER_LOG_GENERAL  T					
  SET t.END_TIME=sysdate, --程序脚本结束时间					
      t.DURATION=(sysdate - t.BEGIN_TIME) * 24 * 3600, --脚本执行时长（秒)					
      t.result =result#, --					
      t.note=note#					
  where t.log_sn=LOG_SN# ;					
					
        COMMIT;					
END;					


-- 日志表，三个表，四个存储过程


-- stg 的数据加工到 ods

CREATE OR REPLACE PROCEDURE P_ODS_D_USE_BASE_INFO(
       V_DATE   IN  VARCHAR2,
       V_AREA   IN  VARCHAR2,
       V_RETCODE OUT VARCHAR2,
       V_RETINFO OUT VARCHAR2) AS

  /*@
  ****************************************************************
  *???? --%@NAME:   P_DWD_D_USE_BASE_INFO
  *???????? --%@COMMENT:????????????????????????
  *???????? --%@PERIOD:??
  *???? --%@PARAM:V_DATE ????,????YYYYMM
  *???? --%@PARAM:V_RETCODE  ????????????????????????
  *???? --%@PARAM:V_RETINFO  ????????????????????????
  *?????? --%@CREATOR:  liston
  *???????? --%@CREATED_TIME:2021-11-19
  *???? --%@REMARK:
  *???????? --%@MODIFY:
  *?????? --%@FROM:
  *?????? --%@TO:
  *???????? --%@MODIFY:
  ******************************************************************
  @*/

        V_PKG          VARCHAR2(30);
        V_TAB          VARCHAR2(300);
        V_PROCNAME     VARCHAR2(300);
        V_ROWLINE      NUMBER;
        V_COUNT        NUMBER;
       -- V_SQL          CLOB;
        V_LOG_SN       NUMBER;
        V_DATE_A1      VARCHAR2(8);
BEGIN
        V_PKG        := 'ODS_D_USE_BASE_INFO';                    -- ????????
        V_TAB        := 'ODS_D_USE_BASE_INFO';       -- ????
        V_PROCNAME   := 'P_ODS_D_USE_BASE_INFO';     -- ????????
        V_DATE_A1 :=TO_CHAR(TO_DATE(V_DATE,'YYYYMMDD')+1,'YYYYMMDD');
        SELECT SEQ_ODS_SQLPARSER.NEXTVAL
        INTO V_LOG_SN                                   --????????????
        FROM DUAL;

        
		--更新历史日志
        P_INSERT_SQLPARSER_LOG_GENERAL(
             V_LOG_SN,
             V_DATE,
             V_AREA,
             'ODS',
             V_PROCNAME,
             'V_DATE='|| V_DATE ||';V_PROV='|| V_AREA,
             SYSDATE,
             V_TAB);
-- 插入当前日志
        P_INSERT_LOG(
             V_DATE,
             V_PKG,
             V_PROCNAME,
             V_AREA,
             SYSDATE,
             V_TAB);
        -- ????????1  ????????????????
        EXECUTE IMMEDIATE '
        SELECT   count(1)
        FROM     ALL_TAB_SUBPARTITIONS									--建表时加了11个子分区
        WHERE    table_name = ''ODS_D_USE_BASE_INFO''					-- 范围分区里面加了list子分区
        AND      table_owner = ''ODS''
        AND      partition_name = ''PART_'||V_DATE||'''' INTO V_COUNT;

        IF V_COUNT != 11 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE ODS.ODS_D_USE_BASE_INFO ADD PARTITION  PART_'||V_DATE||' VALUES LESS THAN ('''||V_DATE_A1||''')
                 TABLESPACE TBS_ODS
                 (
                  subpartition PART_'||V_DATE||'_SUBPART_01 values (''01'') tablespace TBS_ODS,
                  subpartition PART_'||V_DATE||'_SUBPART_02 values (''02'') tablespace TBS_ODS,
                  subpartition PART_'||V_DATE||'_SUBPART_03 values (''03'') tablespace TBS_ODS,
                  subpartition PART_'||V_DATE||'_SUBPART_04 values (''04'') tablespace TBS_ODS,
                  subpartition PART_'||V_DATE||'_SUBPART_05 values (''05'') tablespace TBS_ODS,
                  subpartition PART_'||V_DATE||'_SUBPART_06 values (''06'') tablespace TBS_ODS,
                  subpartition PART_'||V_DATE||'_SUBPART_07 values (''07'') tablespace TBS_ODS,
                  subpartition PART_'||V_DATE||'_SUBPART_08 values (''08'') tablespace TBS_ODS,
                  subpartition PART_'||V_DATE||'_SUBPART_09 values (''09'') tablespace TBS_ODS,
                  subpartition PART_'||V_DATE||'_SUBPART_10 values (''10'') tablespace TBS_ODS,
                  subpartition PART_'||V_DATE||'_SUBPART_11 values (''11'') tablespace TBS_ODS
 )
  ';
       -- EXECUTE IMMEDIATE V_SQL;
        END IF;

        -- 原始分区是否存在
        EXECUTE IMMEDIATE  '
        SELECT  COUNT(1)  CNT
        FROM    STG.STG_D_USE_BASE_INFO
        WHERE   DATE_ID ='''|| V_DATE ||'''						--转义
        AND     area_id ='''|| V_AREA ||'''
        AND      rownum < 2
        '
        into
        V_COUNT;

IF V_COUNT = 1
					--清空分区不怕数据重复，存储过程多次测试，执行首先清空分区
THEN
        EXECUTE IMMEDIATE 'ALTER TABLE ODS.ODS_D_USE_BASE_INFO TRUNCATE SUBPARTITION PART_'|| V_DATE ||'_SUBPART_'|| V_AREA ;
       -- EXECUTE IMMEDIATE V_SQL;
        EXECUTE IMMEDIATE 'INSERT INTO  ODS.ODS_D_USE_BASE_INFO NOLOGGING
                  SELECT DATE_ID,
                         AREA_ID,
                         USER_ID,
                         DEVICE_NUMBER,
                         SERVICE_TYPE,
                         INNET_DATE,
                         ROUND(MONTHS_BETWEEN(TO_DATE(DATE_ID,''YYYY-MM-DD''),TO_DATE(INNET_DATE,''YYYY-MM-DD'')),2) INNET_MONTHS
                  FROM   STG.STG_D_USE_BASE_INFO
                   WHERE  USER_ID IS NOT NULL
                  AND  TO_NUMBER(INNET_DATE)<=TO_NUMBER(DATE_ID)
                  AND  DATE_ID='''|| V_DATE ||'''
                  AND  AREA_ID='''|| V_AREA ||'''';

      --EXECUTE IMMEDIATE V_SQL;

         V_ROWLINE := SQL%ROWCOUNT;				--ROWCOUNT：数据行数
         COMMIT;
         V_RETCODE := 'SUCCESS';
         V_RETINFO := '????';

ELSE
         V_RETCODE := 'WAIT';
         V_RETINFO := '????????';
END IF;

         -- ????????????
         P_UPDATE_LOG(
                V_DATE,
                V_PKG,
                V_PROCNAME,
                V_AREA,
                V_RETINFO,
                V_RETCODE,
                SYSDATE,
                V_ROWLINE);

         P_UPDATE_SQLPARSER_LOG_GENERAL(
                V_LOG_SN,
                V_RETCODE,
                V_RETINFO);

EXCEPTION WHEN OTHERS THEN
         V_RETCODE := 'FAIL';
         V_RETINFO := SQLERRM;

         P_UPDATE_LOG(
                V_DATE,
                V_PKG,
                V_PROCNAME,
                V_AREA,
                V_RETINFO,
                V_RETCODE,
                SYSDATE,
                V_ROWLINE);

         P_UPDATE_SQLPARSER_LOG_GENERAL(
                V_LOG_SN,
                V_RETCODE,
                V_RETINFO);
END;
-- ods
grant all privileges to ods;、
--stg
-- 创建表
-- ods 用户下，新建表及对应存储过程，（清洗数据，沉淀有用的数据)

create table ODS.ODS_D_USE_BASE_INFO
(					
date_id      VARCHAR2(8),
area_id      VARCHAR2(10),
user_id      VARCHAR2(50),
device_number VARCHAR2(11),
service_type VARCHAR2(8),
innet_date  VARCHAR2(8),
innet_months NUMBER
)
TABLESPACE TBS_ODS
PARTITION BY RANGE(date_id)
SUBPARTITION BY LIST(area_id)
(
 PARTITION PART_20190401 VALUES LESS THAN ('20190402')
 TABLESPACE TBS_ODS
 (
  subpartition PART_20190401_SUBPART_01 values ('01') tablespace TBS_ODS,
  subpartition PART_20190401_SUBPART_02 values ('02') tablespace TBS_ODS,
  subpartition PART_20190401_SUBPART_03 values ('03') tablespace TBS_ODS,
  subpartition PART_20190401_SUBPART_04 values ('04') tablespace TBS_ODS,	
  subpartition PART_20190401_SUBPART_05 values ('05') tablespace TBS_ODS,	
  subpartition PART_20190401_SUBPART_06 values ('06') tablespace TBS_ODS,
  subpartition PART_20190401_SUBPART_07 values ('07') tablespace TBS_ODS,
  subpartition PART_20190401_SUBPART_08 values ('08') tablespace TBS_ODS,
  subpartition PART_20190401_SUBPART_09 values ('09') tablespace TBS_ODS,
  subpartition PART_20190401_SUBPART_10 values ('10') tablespace TBS_ODS,
  subpartition PART_20190401_SUBPART_11 values ('11') tablespace TBS_ODS
 )
);
-- 创建索引
CREATE INDEX ODS.IDX_SDUBI_DATE_ID ON ODS.ODS_D_USE_BASE_INFO (DATE_ID) TABLESPACE TBS_ODS;
CREATE INDEX ODS.IDX_SDUBI_AREA_ID ON ODS.ODS_D_USE_BASE_INFO (AREA_ID) TABLESPACE TBS_ODS;
CREATE INDEX ODS.IDX_SDUBI_USER_ID ON ODS.ODS_D_USE_BASE_INFO (USER_ID) TABLESPACE TBS_ODS;
CREATE INDEX ODS.IDX_SDUBI_DN ON ODS.ODS_D_USE_BASE_INFO (DEVICE_NUMBER) TABLESPACE TBS_ODS;	
CREATE INDEX ODS.IDX_SDUBI_ST ON ODS.ODS_D_USE_BASE_INFO (SERVICE_TYPE) TABLESPACE TBS_ODS;
-- 在表上面，列上面的索引

COMMENT ON TABLE  ODS.ODS_D_USE_BASE_INFO IS '用户基础信息';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.date_id is '日期ID';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.area_id is '地市ID';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.user_id is '用户ID';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.device_number is '手机号码';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.service_type is '业务类型';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.innet_date is '入网日期';
COMMENT ON COLUMN ODS.ODS_D_USE_BASE_INFO.innet_months is '在网时长';

commit;

select date_id,count(*) from stg.stg_d_use_base_info group by date_id;
select date_id,count(*) from ods.ods_d_use_base_info group by date_id;
select * from ods.ods_execute_log;
select * from ods.ods_execute_log_his;
-- 存储过程完了之后关闭任务 
-- 创建函数
create or replace function F_LOAD_MONTH_INFO(
          v_date varchar2
          )
          RETURN varchar2
as
          -- v_date varchar2(50） := '20190401';
          -- v_area varchar2(10) := '07' ;
          v_retcode varchar2(100);
          v_retinfo varchar2(100);
          v_area varchar2(2);
          i number;
     begin
       for t in 1..11 loop
         v_area:= to_char(t);
         i := length(v_area);
         if i = 1 then
           v_area := '0'||v_area;
           end if;
           p_ods_d_use_base_info(v_date,v_area,v_retcode,v_retinfo);
           end loop;
           return v_retcode||v_retinfo;
           --p_ods_d_use_base_info(v_date,v_retcode,v_retinfo);
           end;
 -- 调用函数
 
declare
    v_date varchar2(50);
    v_area varchar2(10):= '07';
    v_retcode varchar2(100);
    -- v retinfo varchar2(100);
    -- v area varchar2(2);
    --i number ;

BEGIN
v_retcode:= F_LOAD_MONTH_INFO('20190401');
v_retcode:= F_LOAD_MONTH_INFO('20190402');
v_retcode:= F_LOAD_MONTH_INFO('20200301');
v_retcode:= F_LOAD_MONTH_INFO('20200302');
v_retcode:= F_LOAD_MONTH_INFO('20200401');
v_retcode:= F_LOAD_MONTH_INFO('20200402');
dbms_output.put_line(v_retcode);
END;
--
declare
v_area varchar2(2);
i number;
begin
       for t in 1..11 loop 
         v_area:= to_char(t);
         i := length(v_area);
         if i = 1 then
         v_area := '0'||v_area;
         end if;
         dbms_output.put_line(v_area);
         end loop;
         commit;
end;
--存储过程调用存储过程
declare
         v_date varchar2(8);
         v_area varchar2(2);
         v_retcode varchar2(100);
         v_retinfo varchar2(100);
         i number;
begin
          for t in 1..11 loop
          v_area:= to_char(t);
          i := length(v_area);
          if i = 1 then
          v_area :='0'||v_area;
          end if;
          ODS.P_ODS_D_USE_BASE_INFO('20190401',v_area,v_retcode,v_retinfo);
          ODS.P_ODS_D_USE_BASE_INFO('20190402',v_area,v_retcode,v_retinfo);
          ODS.P_ODS_D_USE_BASE_INFO('20200301',v_area,v_retcode,v_retinfo);
          ODS.P_ODS_D_USE_BASE_INFO('20200302',v_area,v_retcode,v_retinfo);
          ODS.P_ODS_D_USE_BASE_INFO('20200401',v_area,v_retcode,v_retinfo);
          ODS.P_ODS_D_USE_BASE_INFO('20200402',v_area,v_retcode,v_retinfo);
          end loop;
          dbms_output.put_line(v_retinfo);
end;