--
1.
2.
3.
4.
, , . .
ETL
---
delete from ods_table;
insert into v_table
select * from db_table;
commit;
---
insert into v_table
select * from db_table t
where t.id not in (select id from v_table);
commit;
---
update ods_table t
set t.c_number = (select db.c_number from db_table db where db.id = t.id)
where t.c_number != (select db.c_number from db_table db where db.id = t.id);
commit;
---
CREATE SEQUENCE SEQ_ETL_INCREASE_ID
INCREMENT BY 1
START WITH 1
NOCACHE;
/*==============================================================*/
/* Table: ETL_TABLES */
/*==============================================================*/
CREATE TABLE ETL_TABLES (
"ID" NUMBER DEFAULT -1 NOT NULL,
"TABLE_NAME" VARCHAR2(100) NOT NULL,
"TABLE_TYPE" VARCHAR2(30) NOT NULL,
"TABLE_ROOT_IN" VARCHAR2(30),
"TABLE_NEED_CREATE_VIEW" NUMBER DEFAULT 1,
"TABLE_CREATE_VIEW_NAME_PREFIX" VARCHAR2(30) DEFAULT 'v',
"DB_LINK_NAME" VARCHAR2(100),
"CURRENT_VERSION" NUMBER DEFAULT 1 NOT NULL,
"VERSION_HISTORY" VARCHAR2(3000) DEFAULT 'init input' NOT NULL,
"DEVELOP_DATE" DATE DEFAULT SYSDATE NOT NULL,
"DEVELOP_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,
"LAST_MAINTAIN_DATE" DATE DEFAULT SYSDATE NOT NULL,
"LAST_MAINTAIN_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,
"MEMO" VARCHAR2(500),
"STATUS" NUMBER DEFAULT 1,
CONSTRAINT PK_ETL_TABLES PRIMARY KEY ("ID")
);
COMMENT ON TABLE ETL_TABLES IS
' ETL , :
1, db---
2, ods---
3, dw--- ';
/*==============================================================*/
/* Table: ETL_VIEWS */
/*==============================================================*/
CREATE TABLE ETL_VIEWS (
"ID" NUMBER DEFAULT -1 NOT NULL,
"VIEW_NAME" VARCHAR2(100) NOT NULL,
"VIEW_TYPE" VARCHAR2(30) NOT NULL,
"VIEW_ROOT_IN" VARCHAR2(30),
"VIEW_SELECT" VARCHAR2(4000) NOT NULL,
"VIEW_FROM" VARCHAR2(600) NOT NULL,
"VIEW_WHERE" VARCHAR2(2000),
"VIEW_ORDER_BY" VARCHAR2(600),
"VIEW_GROUP_BY" VARCHAR2(600),
"VIEW_HAVING" VARCHAR2(600),
"VIEW_DB_LINK_NAME" VARCHAR2(100),
"CURRENT_VERSION" NUMBER DEFAULT 1 NOT NULL,
"VERSION_HISTORY" VARCHAR2(3000) DEFAULT 'init input' NOT NULL,
"DEVELOP_DATE" DATE DEFAULT SYSDATE NOT NULL,
"DEVELOP_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,
"LAST_MAINTAIN_DATE" DATE DEFAULT SYSDATE NOT NULL,
"LAST_MAINTAIN_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,
"MEMO" VARCHAR2(500),
"STATUS" NUMBER DEFAULT 1,
CONSTRAINT PK_ETL_VIEWS PRIMARY KEY ("ID")
);
COMMENT ON TABLE ETL_VIEWS IS
' ETL , :
1, v1---db ods
2, v2---ods db
3, v3---ods dw
4, v4---dw ods ';
/*==============================================================*/
/* Table: ETLS */
/*==============================================================*/
CREATE TABLE ETLS (
"ID" NUMBER NOT NULL,
"ETL_NAME" VARCHAR2(300) NOT NULL,
"ETL_TYPE" VARCHAR2(30) NOT NULL,
"ETL_SRC_VIEW_OR_TABLE" NUMBER NOT NULL,
"ETL_DES_VIEW_OR_TABLE" NUMBER NOT NULL,
"ETL_INIT_ENABLE" NUMBER(1) DEFAULT 1 NOT NULL,
"ETL_ADD_ENABLE" NUMBER(1) DEFAULT 1 NOT NULL,
"ETL_CHARGE_ENABLE" NUMBER(1) DEFAULT 1 NOT NULL,
"CURRENT_VERSION" NUMBER DEFAULT 1 NOT NULL,
"VERSION_HISTORY" VARCHAR2(3000) DEFAULT 'init input' NOT NULL,
"DEVELOP_DATE" DATE DEFAULT SYSDATE NOT NULL,
"DEVELOP_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,
"LAST_MAINTAIN_DATE" DATE DEFAULT SYSDATE NOT NULL,
"LAST_MAINTAIN_BY" VARCHAR2(100) DEFAULT 'cyyan@isoftstone' NOT NULL,
"MEMO" VARCHAR2(500),
"STATUS" NUMBER DEFAULT 1,
CONSTRAINT PK_ETLS PRIMARY KEY ("ID")
);
COMMENT ON TABLE ETLS IS
' ETL
( )---> ( )-
( , , )
( )
:
1, ( )- ( )-
2, ';
--
/*==============================================================*/
/* Database name: %DATABASE% */
/* DBMS name: ORACLE Version 10g */
/* Created on: 2009-2-1 23:29:27 */
/*==============================================================*/
-- INTEGRITY PACKAGE DECLARATION
CREATE OR REPLACE PACKAGE INTEGRITYPACKAGE AS
PROCEDURE INITNESTLEVEL;
FUNCTION GETNESTLEVEL RETURN NUMBER;
PROCEDURE NEXTNESTLEVEL;
PROCEDURE PREVIOUSNESTLEVEL;
END INTEGRITYPACKAGE;
/
-- INTEGRITY PACKAGE DEFINITION
CREATE OR REPLACE PACKAGE BODY INTEGRITYPACKAGE AS
NESTLEVEL NUMBER;
-- PROCEDURE TO INITIALIZE THE TRIGGER NEST LEVEL
PROCEDURE INITNESTLEVEL IS
BEGIN
NESTLEVEL := 0;
END;
-- FUNCTION TO RETURN THE TRIGGER NEST LEVEL
FUNCTION GETNESTLEVEL RETURN NUMBER IS
BEGIN
IF NESTLEVEL IS NULL THEN
NESTLEVEL := 0;
END IF;
RETURN(NESTLEVEL);
END;
-- PROCEDURE TO INCREASE THE TRIGGER NEST LEVEL
PROCEDURE NEXTNESTLEVEL IS
BEGIN
IF NESTLEVEL IS NULL THEN
NESTLEVEL := 0;
END IF;
NESTLEVEL := NESTLEVEL + 1;
END;
-- PROCEDURE TO DECREASE THE TRIGGER NEST LEVEL
PROCEDURE PREVIOUSNESTLEVEL IS
BEGIN
NESTLEVEL := NESTLEVEL - 1;
END;
END INTEGRITYPACKAGE;
/
CREATE OR REPLACE PROCEDURE PRO_CREATE_VIEW_BY_ETL_VIEWS
AS
--------------PRO_CREATE_VIEW_BY_ETL_VIEWS------------------------
-- CREATED ON 2009-2-1 BY CYYAN@ISOFTSTONE
-- : ETL_VIEWS
------------------------------------------------------------------------------
VIEW_CREATE_CODE VARCHAR2(10000); --
VIEW_NAME VARCHAR2(100); --
VIEW_SELECT VARCHAR2(4000); -- SELECT
VIEW_FROM VARCHAR2(300); -- FROM
VIEW_WHERE VARCHAR2(3000); -- WHERE
VIEW_ORDER_BY VARCHAR2(600); -- ORDER BY
VIEW_GROUP_BY VARCHAR2(600); -- GROUP BY
VIEW_HAVING VARCHAR2(600); -- HAVING
VIEW_DB_LINK_NAME VARCHAR2(100); -- DB LINK
ROW_COUNT NUMBER; --
CURSOR ETL_VIEWS_CURSOR IS --
SELECT VIEW_NAME, VIEW_SELECT, VIEW_FROM, VIEW_WHERE, VIEW_ORDER_BY, VIEW_GROUP_BY, VIEW_HAVING, VIEW_DB_LINK_NAME FROM ETL_VIEWS T WHERE T.CURRENT_VERSION = (SELECT MAX(T2.CURRENT_VERSION) FROM ETL_VIEWS T2 WHERE T.VIEW_NAME = T2.VIEW_NAME);
BEGIN
--
SELECT COUNT(*) INTO ROW_COUNT FROM ETL_VIEWS T WHERE T.CURRENT_VERSION = (SELECT MAX(T2.CURRENT_VERSION) FROM ETL_VIEWS T2 WHERE T.VIEW_NAME = T2.VIEW_NAME);
OPEN ETL_VIEWS_CURSOR; --
FOR I IN 1 .. ROW_COUNT LOOP --
FETCH ETL_VIEWS_CURSOR
INTO VIEW_NAME, VIEW_SELECT, VIEW_FROM, VIEW_WHERE, VIEW_ORDER_BY, VIEW_GROUP_BY, VIEW_HAVING, VIEW_DB_LINK_NAME;
---
VIEW_CREATE_CODE := 'create or replace view ' || VIEW_NAME || ' as select ' || VIEW_SELECT || ' from ' || VIEW_FROM;
IF VIEW_DB_LINK_NAME IS NOT NULL THEN
VIEW_CREATE_CODE := VIEW_CREATE_CODE || '@' || VIEW_DB_LINK_NAME;
END IF;
IF VIEW_WHERE IS NOT NULL THEN
VIEW_CREATE_CODE := VIEW_CREATE_CODE || ' where ' || VIEW_WHERE;
END IF;
IF VIEW_ORDER_BY IS NOT NULL THEN
VIEW_CREATE_CODE := VIEW_CREATE_CODE || ' order by ' || VIEW_ORDER_BY;
END IF;
IF VIEW_GROUP_BY IS NOT NULL THEN
VIEW_CREATE_CODE := VIEW_CREATE_CODE || ' group by ' || VIEW_GROUP_BY;
END IF;
IF VIEW_HAVING IS NOT NULL THEN
VIEW_CREATE_CODE := VIEW_CREATE_CODE || ' having ' || VIEW_HAVING;
END IF;
--
--DBMS_OUTPUT.PUT_LINE(VIEW_CREATE_CODE);
--DBMS_OUTPUT.PUT_LINE('');
--
EXECUTE IMMEDIATE VIEW_CREATE_CODE;
END LOOP;
CLOSE ETL_VIEWS_CURSOR; --
END;
/
CREATE OR REPLACE PROCEDURE PRO_INSERT_INTO_ETL_VIEWS
AS
--ADD BY CYYAN@ISOFTSTONE
--2009 2 1 21:33:37
--- ETL_TABLE VIEW TABLE, ETL_VIEWS .
-- COL
TABLE_NAME VARCHAR2(100); --
COL_NAME VARCHAR2(100); --
TABLE_COUNT NUMBER; --
--COL_COUNT NUMBER; --
ETL_VIEWS_INSERT_CODE VARCHAR2(600); -- INSERT
ETL_VIEWS_VALUES_CODE VARCHAR2(16000); -- VALUES
--ETL_VIEWS
VIEW_NAME_PREFIX VARCHAR2(30);--
TABLE_TYPE VARCHAR2(30); -- DB, ODS, DW
TABLE_ROOT_IN VARCHAR2(30); -- , , "NHZJ", "NHCW"
VIEW_SELECT VARCHAR2(10000); --VIEW SELECT ,
DB_LINK_NAME VARCHAR2(100);
CURRENT_VERSION VARCHAR2(600); -- , , , , , ,
CURSOR_NUMBER NUMBER;
COL_SELECT_SQL VARCHAR2(100);
RETURN_VALUE NUMBER;
-- ETL_TABLES
CURSOR DB_TABLES_CURSOR IS
SELECT UPPER(TABLE_NAME), T.TABLE_TYPE, T.TABLE_ROOT_IN, T.TABLE_CREATE_VIEW_NAME_PREFIX, DB_LINK_NAME FROM ETL_TABLES T WHERE (UPPER(T.TABLE_TYPE) = 'DB' OR UPPER(T.TABLE_TYPE) = 'DW' ) AND T.TABLE_NEED_CREATE_VIEW = 1;
--CURSOR_NUMBER NUMBER; -- OLD WAY , NEW WAY
--RETURN_VALUE NUMBER; -- OLD WAY , NEW WAY
BEGIN
-- TEST STATEMENTS HERE
SELECT COUNT(*) INTO TABLE_COUNT FROM ETL_TABLES T WHERE (UPPER(T.TABLE_TYPE) = 'DB' OR UPPER(T.TABLE_TYPE) = 'DW' ) AND T.TABLE_NEED_CREATE_VIEW = 1;
-- INSERT
ETL_VIEWS_INSERT_CODE := 'insert into etl_views(view_name, view_type, view_root_in, view_select, view_from, current_version, VIEW_DB_LINK_NAME) ';
OPEN DB_TABLES_CURSOR;
FOR I IN 1 .. TABLE_COUNT LOOP --
FETCH DB_TABLES_CURSOR
INTO TABLE_NAME, TABLE_TYPE, TABLE_ROOT_IN, VIEW_NAME_PREFIX, DB_LINK_NAME;
-- VALUES
ETL_VIEWS_VALUES_CODE := 'values(''' || VIEW_NAME_PREFIX || TABLE_NAME || ''', ''' || TABLE_TYPE || ''', ''' || TABLE_ROOT_IN || '''';
DBMS_OUTPUT.PUT(TABLE_NAME);
/* CURSOR DB_LINK
--
SELECT COUNT(*) INTO COL_COUNT FROM COL@DB_LINK_NHZJ WHERE COL.TNAME = UPPER(TABLE_NAME);
DBMS_OUTPUT.PUT_LINE(' table has ' || COL_COUNT || ' cols');
DECLARE
CURSOR COLS_CURSOR IS
SELECT C.CNAME FROM COL@DB_LINK_NHZJ C WHERE C.TNAME = UPPER(TABLE_NAME);
BEGIN
OPEN COLS_CURSOR;
VIEW_SELECT := '';
--
FETCH COLS_CURSOR --
INTO COL_NAME;
VIEW_SELECT := VIEW_SELECT || COL_NAME;
FOR J IN 2 .. COL_COUNT LOOP --
FETCH COLS_CURSOR
INTO COL_NAME;
DBMS_OUTPUT.PUT_LINE(' ' || COL_NAME);
VIEW_SELECT := VIEW_SELECT || ', ' || COL_NAME;
END LOOP;
CLOSE COLS_CURSOR;
END;
--DBMS_OUTPUT.PUT_LINE(VIEW_SELECT);
-- DBMS_OUTPUT.PUT_LINE(ETL_VIEWS_VALUES_CODE);
*/
/* DBMS_SQL */
-- ANOTHER WAY USER DBMS_SQL PACKAGE
COL_SELECT_SQL := 'select t.cname from sys.col@' || DB_LINK_NAME || ' T where T.tname = ''' || TABLE_NAME || '''';
--SQL_CODE := 'select t.cname from sys.col T where T.tname = ''' || TABLE_NAME || '''';
CURSOR_NUMBER := DBMS_SQL.OPEN_CURSOR();
DBMS_SQL.PARSE(CURSOR_NUMBER, COL_SELECT_SQL, DBMS_SQL.NATIVE);
DBMS_SQL.DEFINE_COLUMN(CURSOR_NUMBER,1,COL_NAME, 100);
RETURN_VALUE := DBMS_SQL.EXECUTE(CURSOR_NUMBER);
DBMS_OUTPUT.PUT_LINE(' RETURN_VALUE = ' || RETURN_VALUE);
RETURN_VALUE := DBMS_SQL.FETCH_ROWS(CURSOR_NUMBER); --
DBMS_SQL.COLUMN_VALUE(CURSOR_NUMBER,1,COL_NAME);
VIEW_SELECT := COL_NAME;
WHILE DBMS_SQL.FETCH_ROWS(CURSOR_NUMBER)<>0 LOOP ---
DBMS_SQL.COLUMN_VALUE(CURSOR_NUMBER,1,COL_NAME);
DBMS_OUTPUT.PUT_LINE(COL_NAME);
VIEW_SELECT := VIEW_SELECT || ', ' || COL_NAME;
END LOOP;
-- DBMS_OUTPUT.PUT_LINE('VIEW_SELECT : ' || VIEW_SELECT);
DBMS_SQL.CLOSE_CURSOR(CURSOR_NUMBER);
-- :
SELECT NVL(MAX(CURRENT_VERSION),0) + 1 INTO CURRENT_VERSION FROM ETL_VIEWS V WHERE V.VIEW_NAME = VIEW_NAME_PREFIX || TABLE_NAME;
ETL_VIEWS_VALUES_CODE := ETL_VIEWS_VALUES_CODE || CHR(10) || ', ''' || VIEW_SELECT || '''' || CHR(10) || ', ''' || TABLE_NAME || ''', ''' || CURRENT_VERSION || ''', ''' || DB_LINK_NAME || ''')';
--
--DBMS_OUTPUT.PUT_LINE(ETL_VIEWS_INSERT_CODE);
--DBMS_OUTPUT.PUT_LINE(ETL_VIEWS_VALUES_CODE);
--DBMS_OUTPUT.PUT_LINE('');
--DBMS_STANDARD.
--
-- NEW WAY
EXECUTE IMMEDIATE ETL_VIEWS_INSERT_CODE || ETL_VIEWS_VALUES_CODE;
/*
-- OLD WAY
CURSOR_NUMBER := DBMS_SQL.OPEN_CURSOR();
DBMS_SQL.PARSE(CURSOR_NUMBER, ETL_VIEWS_INSERT_CODE, DBMS_SQL.NATIVE);
RETURN_VALUE := DBMS_SQL.EXECUTE(CURSOR_NUMBER);
DBMS_SQL.CLOSE_CURSOR(CURSOR_NUMBER);
*/
END LOOP;
COMMIT; --
CLOSE DB_TABLES_CURSOR; --
--EXCEPTION
--ROLLBACK;
END;
/
CREATE OR REPLACE TRIGGER TRG_ID_ON_ETLS
BEFORE INSERT ON ETLS
FOR EACH ROW
DECLARE
-- NOTHING
BEGIN
SELECT SEQ_ETL_INCREASE_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END TRIGGER_ID_INCREASE;
/
CREATE OR REPLACE TRIGGER TRG_ID_ON_ETL_TABLES
BEFORE INSERT ON ETL_TABLES
FOR EACH ROW
DECLARE
-- NOTHING
BEGIN
SELECT SEQ_ETL_INCREASE_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END TRIGGER_ID_INCREASE;
/
CREATE OR REPLACE TRIGGER TRG_ID_ON_ETL_VIEWS
BEFORE INSERT ON ETL_VIEWS
FOR EACH ROW
DECLARE
-- NOTHING
BEGIN
SELECT SEQ_ETL_INCREASE_ID.NEXTVAL INTO :NEW.ID FROM DUAL;
END TRIGGER_ID_INCREASE;
/