plsql

Aron.Tang

浏览: 4947 次

最近访客更多访客>>

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2013-04 ( 6)
更多存档...

博客分类：

(一)：循环内的异常处理

SET SERVEROUTPUT ON;
DECLARE
SELECT_SQL1 VARCHAR2(1000);
SELECT_SQL2 VARCHAR2(1000);
UPDATE_SQL VARCHAR2(1000);
TABLE_NAME VARCHAR2(100);
UNIT_ID VARCHAR2(100);
BEGIN
    FOR C1 IN (SELECT DC.ID, DC.DOC_ID, DC.REF_ENTITY_NAME, DC.DOC_VERSION, DC.DOMAIN_ID FROM CNT_DOC_UNIT DC WHERE DC.UNIT_ID IS NULL) LOOP
        -- WHEN EXCEPTION OCCURED, CONTINUE THE LOOP 
        BEGIN
        SELECT_SQL1 := 'SELECT PRODUCT_TABLE_NAME FROM CNT_ENTITY_DEFINITION WHERE ENTITY_NAME = ' || '''' || C1.REF_ENTITY_NAME || '''';
        EXECUTE IMMEDIATE SELECT_SQL1 INTO TABLE_NAME;
        --DBMS_OUTPUT.PUT_LINE(C1.DOMAIN_ID);
        SELECT_SQL2 := 'SELECT ID FROM ' || TABLE_NAME || ' WHERE REF_NO = (SELECT REF_NO FROM ' || TABLE_NAME || ' WHERE ID = ' || 
            '''' || C1.DOC_ID || '''' || ') AND VERSION = ' || C1.DOC_VERSION || ' AND DOMAIN_ID = ' || '''' || C1.DOMAIN_ID || ''''
            || ' AND IS_LATEST = 0 AND ROWNUM = 1 ORDER BY REVISION DESC';
        --DBMS_OUTPUT.PUT_LINE(SELECT_SQL2);
        UNIT_ID := NULL;
        EXECUTE IMMEDIATE SELECT_SQL2 INTO UNIT_ID;
        UPDATE_SQL := 'UPDATE CNT_DOC_UNIT SET UNIT_ID = ' || '''' || UNIT_ID || '''' || ' WHERE ID = ' || '''' || C1.ID || '''';
        --DBMS_OUTPUT.PUT_LINE(UPDATE_SQL); 
        EXECUTE IMMEDIATE UPDATE_SQL;
        EXCEPTION WHEN OTHERS THEN NULL;
        END;
        -- WHEN EXCEPTION OCCURED, CONTINUE THE LOOP 
    END LOOP;    
END; 
/

(二)：显式游标

set serveroutput on;
DECLARE
   -- define cursor c_cursor
   CURSOR c_cursor 
   IS SELECT DISTINCT ENTITY_NAME, PRODUCT_TABLE_NAME
   FROM cnt_entity_definition 
   WHERE system_entity = 0;
   v_tablename cnt_entity_definition.product_table_name%TYPE; 
   V_entityname cnt_entity_definition.entity_name%TYPE;
   v_dropcolumnsql varchar2(4000);
   BEGIN 
  OPEN c_cursor;
  FETCH c_cursor INTO v_entityname,v_tablename;
  WHILE c_cursor%FOUND LOOP
   v_dropcolumnsql := 'ALTER TABLE ' || v_tablename || ' DROP COLUMN TEMP_NUM1';
  -- execute immediate v_dropcolumnsql;
  DBMS_OUTPUT.PUT_LINE(v_dropcolumnsql);
  FETCH c_cursor INTO v_entityname,v_tablename;
  END LOOP;
   CLOSE c_cursor;
END;
/

(三):

DECLARE
   v_add_sql varchar2(1000);
   v_update_sql varchar2(1000);
   v_number NUMBER(20, 0) :=0;
   v_set_null_sql varchar2(1000);
   v_alter_sql varchar2(1000);
   v_set_value_sql varchar2(1000);
   v_drop_column_sql varchar2(1000);
BEGIN
  FOR t in (SELECT DISTINCT ed.ENTITY_NAME, ed.PRODUCT_TABLE_NAME
      FROM CNT_ENTITY_DEFINITION ed
      JOIN USER_TABLES u 
            ON ed.PRODUCT_TABLE_NAME=u.TABLE_NAME
      WHERE ed.SYSTEM_ENTITY = 0) LOOP
        -- ADD TEMP COLUMN
        v_add_sql := 'ALTER TABLE ' || t.PRODUCT_TABLE_NAME || ' ADD(TEMP_NUM NUMBER(20,0))';
        EXECUTE IMMEDIATE v_add_sql;
        FOR v_number IN 1..6 LOOP
          v_update_sql := 'UPDATE ' || t.PRODUCT_TABLE_NAME || ' SET TEMP_NUM=CUST_NUMBER' || v_number;
          -- update TEMP_NUM value to CUST_NUMBER's value
          EXECUTE IMMEDIATE v_update_sql;
          v_set_null_sql := 'UPDATE ' || t.PRODUCT_TABLE_NAME || ' SET CUST_NUMBER' || v_number || '=NULL';
          --set CUST_NUMER to null
          EXECUTE IMMEDIATE v_set_null_sql;
          v_alter_sql := 'ALTER TABLE ' || t.PRODUCT_TABLE_NAME || ' MODIFY(CUST_NUMBER' || v_number || ' NUMBER(20,0))';
          --Alter CUST_NUMBER column TYPE to NUMBER(20, 0).
          EXECUTE IMMEDIATE v_alter_sql;
          v_set_value_sql := 'UPDATE ' || t.PRODUCT_TABLE_NAME || ' SET CUST_NUMBER' || v_number || '=TEMP_NUM' ;
          --Set value of CUST_NUMBER = value in the TEMP_NUM.
          EXECUTE IMMEDIATE v_set_value_sql;
        END LOOP;
        --end for loop 
        v_drop_column_sql := 'ALTER TABLE ' || t.PRODUCT_TABLE_NAME || ' DROP COLUMN TEMP_NUM';
      EXECUTE IMMEDIATE v_drop_column_sql;
  END LOOP;
END;
/

(四):

set serveroutput on;
DECLARE
TYPE CUR_TYPE IS REF CURSOR;
CUR CUR_TYPE;
V_SELECT_RECORED_SQL VARCHAR2(1000);
V_UPDATE_NEW_COLUMN_SQL VARCHAR2(1000);
V_LOOKUP_KEY VARCHAR2(100);
BEGIN
    FOR C1 IN (SELECT DISTINCT FD1.ENTITY_LOOKUP, FD1.ENTITY_LOOKUP_KEY, ED.PRODUCT_TABLE_NAME, FD2.COLUMN_NAME
                         FROM CNT_FIELD_DEFINITION FD1 JOIN CNT_ENTITY_DEFINITION ED ON ED.ENTITY_NAME = ENTITY_LOOKUP 
                     JOIN USER_TAB_COLUMNS UTC ON ED.PRODUCT_TABLE_NAME = UTC.TABLE_NAME 
                     JOIN CNT_FIELD_DEFINITION FD2 ON FD2.PARENT_ID = ED.ID 
                WHERE FD1.FIELD_TYPE = 'collection' AND ED.TYPE <> 'main' AND FD1.ENTITY_LOOKUP NOT IN ('VReportUserRole') 
                AND utc.COLUMN_NAME = 'INTERNAL_SEQ_NO' AND FD2.FIELD_ID = FD1.ENTITY_LOOKUP_KEY) LOOP
          -- begin the loop c1   
          V_SELECT_RECORED_SQL := 'SELECT DISTINCT ' || C1.COLUMN_NAME || ' FROM ' || C1.PRODUCT_TABLE_NAME;
              OPEN CUR FOR V_SELECT_RECORED_SQL;
                  LOOP
                  FETCH CUR INTO V_LOOKUP_KEY;
                  EXIT WHEN CUR%NOTFOUND;
          
                  V_UPDATE_NEW_COLUMN_SQL := 'UPDATE ' || C1.PRODUCT_TABLE_NAME || ' SET INTERNAL_SEQ_NO = ROWNUM ' || ' WHERE ' || C1.COLUMN_NAME || ' = ''' || V_LOOKUP_KEY || '''';
                  --assign the value to new column
                  EXECUTE IMMEDIATE V_UPDATE_NEW_COLUMN_SQL;
              --end the cursor loop CUR
              END LOOP;
    --end the loop c1.      
    END LOOP;    
END; 
/

(五):

DECLARE 
TYPE CUR_TYPE IS REF CURSOR;
CUR CUR_TYPE;
V_ID VARCHAR2(100);
V_PARENT_ID VARCHAR2(100);
V_FIELD_ID VARCHAR2(100);
V_CREATED_ON TIMESTAMP(6);
V_UPDATE_SQL VARCHAR2(1000);
V_SELECT_SQL VARCHAR2(1000);
V_COUNT NUMBER;
BEGIN
    FOR C1 IN (SELECT PARENT_ID,FIELD_ID FROM CNT_SELECTION GROUP BY PARENT_ID,FIELD_ID) LOOP
    -- begin the C1 loop
    V_SELECT_SQL := 'SELECT ID,CREATED_ON FROM CNT_SELECTION WHERE PARENT_ID = ''' || C1.PARENT_ID || ''' AND FIELD_ID = ''' || C1.FIELD_ID || ''' ORDER BY CREATED_ON';

    V_COUNT := 1;
        OPEN CUR FOR V_SELECT_SQL;
            LOOP
            FETCH CUR INTO V_ID,V_CREATED_ON;
            EXIT WHEN CUR%NOTFOUND; 
        
            V_UPDATE_SQL := 'UPDATE CNT_SELECTION SET INTERNAL_SEQ_NO = ' || V_COUNT || ' WHERE ID = ''' || V_ID || '''';
            -- assign value to new column
            EXECUTE IMMEDIATE V_UPDATE_SQL;
            V_COUNT := V_COUNT + 1;
            END LOOP;
            -- end the loop C1    
    END LOOP;
END; 
/

(六):

DECLARE 
TYPE DOMAINS_TYPE IS TABLE OF CNT_DOMAIN.DOMAIN_ID%TYPE INDEX BY PLS_INTEGER;
V_DOMAIN_IDS DOMAINS_TYPE;
V_UPDATE_SQL VARCHAR2(1000);
V_INSERT_SQL VARCHAR2(1000);
V_UPDATED_ON TIMESTAMP;
V_DOMAIN_ID VARCHAR2(100);
BEGIN
    UPDATE CNT_SEQ_DEF SET CYCLE_STARTED_ON = UPDATED_ON;
    -- update cycleStartedOn to updatedOn
    SELECT DOMAIN_ID BULK COLLECT INTO V_DOMAIN_IDS FROM CNT_DOMAIN WHERE DOMAIN_ID <> '/' AND DOMAIN_ID <> 'backend';
    
    FOR C1 IN (SELECT ID FROM CNT_SEQ_DEF) LOOP
    -- begin the C1 looP      
        FOR I IN V_DOMAIN_IDS.FIRST..V_DOMAIN_IDS.LAST LOOP
            V_DOMAIN_ID := V_DOMAIN_IDS(I);
            V_INSERT_SQL := 'INSERT INTO CNT_SEQ_DEF(REVISION, ENTITY_VERSION, DOMAIN_ID, ID, START_WITH, MAX_VALUE, INCREMENT_BY, CYCLE, CACHE_SIZE, UPDATED_ON, INTERNAL_SEQ_NO,
                                   SEQ_ID, NEXT_VAL, CYCLE_STARTED_ON) SELECT REVISION, ENTITY_VERSION, ''' || V_DOMAIN_ID || ''', SYS_GUID(), START_WITH, MAX_VALUE, INCREMENT_BY, CYCLE, CACHE_SIZE, UPDATED_ON, INTERNAL_SEQ_NO,
                                   SEQ_ID, NEXT_VAL, CYCLE_STARTED_ON FROM CNT_SEQ_DEF WHERE ID = ''' || C1.ID || '''';
            -- duplicate a new record for domainId
            EXECUTE IMMEDIATE V_INSERT_SQL;
        END LOOP;
    -- end the loop C1    
    END LOOP;
END; 
/

（七）

SET SERVEROUTPUT ON;
DECLARE
         V_ID VARCHAR(200);
         S_ID VARCHAR2(200);
         ROW_DATA1 CNT_CODELIST_BOOK%ROWTYPE;
         ROW_DATA2 CTM_CODELIST_BOOK%ROWTYPE;
         ROW_DATA3 CNT_CODELIST%ROWTYPE;
         BEGIN
             FOR ROW_DATA1 IN (SELECT CLB.* FROM CNT_CODELIST_BOOK CLB,
                 (SELECT ID, NAME FROM CNT_DATA_LIST_TYPE WHERE DOMAIN_ID = 'SGS' AND IS_LATEST = 1) DLT
                  WHERE CLB.NAME = DLT.NAME(+) AND CLB.DOMAIN_ID ='SGS' AND CLB.IS_LATEST = 1
                  AND CLB.VERSION = (SELECT MAX(CCLB.VERSION) FROM CNT_CODELIST_BOOK CCLB WHERE CCLB.NAME = CLB.NAME AND CCLB.DOMAIN_ID = CLB.DOMAIN_ID AND CCLB.IS_LATEST = 1)) LOOP

                  V_ID := ROW_DATA1.ID;

                  ROW_DATA1.ID := SYS_GUID();
                  ROW_DATA1.DOMAIN_ID := 'ARONTEST1';
                  ROW_DATA1.HUB_DOMAIN_ID := 'ARONTEST1';
                  ROW_DATA1.REVISION := 1;
                  ROW_DATA1.ENTITY_VERSION := 1;
                  ROW_DATA1.VERSION := 1;
                  ROW_DATA1.CREATE_USER := 'system';
                  ROW_DATA1.IS_FOR_REFERENCE := 0;
                  INSERT INTO CNT_CODELIST_BOOK VALUES row_data1;

                  SELECT * INTO ROW_DATA2 FROM CTM_CODELIST_BOOK WHERE ID = V_ID;
                  ROW_DATA2.ID := ROW_DATA1.ID;
                  ROW_DATA2.DOMAIN_ID := 'ARONTEST1';
                  INSERT INTO CTM_CODELIST_BOOK VALUES ROW_DATA2;

                  FOR ROW_DATA3 IN (SELECT *  FROM CNT_CODELIST WHERE PARENT_ID = V_ID) LOOP

                      S_ID := ROW_DATA3.ID;

                      ROW_DATA3.ID := SYS_GUID();
                      ROW_DATA3.PARENT_ID := ROW_DATA1.ID;
                      ROW_DATA3.DOMAIN_ID := 'ARONTEST1';
                      ROW_DATA3.HUB_DOMAIN_ID := 'ARONTEST1';
                      ROW_DATA3.REVISION := 1;
                      ROW_DATA3.ENTITY_VERSION := 1;
                      ROW_DATA3.IS_FOR_REFERENCE := 0;
                      INSERT INTO CNT_CODELIST VALUES ROW_DATA3;

                      SELECT * INTO ROW_DATA2 FROM CTM_CODELIST_BOOK WHERE ID = S_ID;
                      ROW_DATA2.ID := ROW_DATA3.ID;
                      ROW_DATA2.DOMAIN_ID := 'ARONTEST1';
                      INSERT INTO CTM_CODELIST_BOOK VALUES ROW_DATA2;

                END LOOP;
            
            END LOOP;
            
        END;
/

分享到：