ORECALE类型声明及过程中用OR正则验证

--动态执行SQL
--赋值的变量 STD_STANDARD_INFO_COUNT 
--V_SQL := 'SELECT COUNT(1)  FROM  STD_STANDARD_INFO SSI WHERE SSI.STANDARD_NO = ''' || SSS.STANDARD_NO ||'''' ;
EXECUTE IMMEDIATE V_SQL INTO STD_STANDARD_INFO_COUNT;


--ORACLE中正则表达式验证 REGEXP_LIKE()函数
SELECT 1 FROM DUAL  WHERE  REGEXP_LIKE('234234','^\+?[1-9][0-9]*$')
--声明一个为VARCHAR2(32)类型的数组，键值对的都是以VARCHAR2(32)
TYPE ID_LOOKUP IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(32);
ID_TRANSLATE ID_LOOKUP;
--给数组赋值  ID_TRANSLATE(NEW_ROWS(I).ID) := V_MRO_EQU_MODEL_STRUCT_ID;
--取值相同    NEW_ROWS(I).ID := ID_TRANSLATE(NEW_ROWS(I).ID)
--声明一个表类型的数组
TYPE STD_DATA_PUSH_ITEMS IS TABLE OF TABLE_NAME%ROWTYPE; --存放每行记录的类型
NEW_ROWS                   STD_DATA_PUSH_ITEMS; --声明变量
--------------使用start--------------
--给表类型存放数据
SELECT INNERS.* BULK COLLECT
      INTO NEW_ROWS
      FROM (SELECT *
              FROM DUAL ) INNERS
--使用表类型数据
该集合 NEW_ROWS.COUNT() 验证是否存在记录
--如果NEW_ROWS 为null,当执行NEW_ROWS.FIRST()会抛出ORA-06502: PL/SQL: 数字或值错误
 FOR I IN NEW_ROWS.FIRST() .. NEW_ROWS.LAST() LOOP
       NEW_ROWS(I).ID := SYS_GUID();
 END LOOP;
 --批量添加数据
 --批量向型号构型信息表中写入数据
FORALL J IN NEW_ROWS.FIRST() .. NEW_ROWS.LAST() 
INSERT INTO MRO_EQU_MODEL_STRUCT VALUES NEW_ROWS(J);
--------------使用end--------------


--声明一个类型为存放VARCHAR2(32)的数组,单列 //types下面建
CREATE OR REPLACE TYPE NEW_TABLES_TP AS OBJECT(STANDARD_ID VARCHAR2(32))
--声明一个表，类型为存放单列数组的  //types下面建
CREATE OR REPLACE TYPE NEW_TABLES_TABLE AS TABLE OF new_tables_tp




--下面是对上面声明的使用（存储过程中使用）
CREATE OR REPLACE PACKAGE BODY STD_COMMON_P IS
  -- Private type declarations


  /***************************************************************************
  * 功能描述:标准管理模块级联删除所有与标准相关的数据
  * 编写人：SHIC
  * 编写日期: 2012-12-12
  ***************************************************************************/
  PROCEDURE Delete_Standard_Res_Data(i_Standard_Info_Id VARCHAR2,
                                     o_Return_Int       OUT INT,
                                     o_Return_String    OUT VARCHAR2) IS
    V_COUNT number;
    --TYPE ID_LOOKUP IS TABLE OF VARCHAR2(32) INDEX BY VARCHAR2(32);
    --TYPE STD_STANDARD_RESS IS TABLE OF NEW_TABLES_TP; --存放每行记录的类型
    NEW_TABLES   NEW_TABLES_TABLE := NEW_TABLES_TABLE(); --使用
  
  BEGIN
    --给数组赋值
    FOR SSR IN (SELECT t.standard_id
                  FROM Std_Standard_Res t
                 WHERE t.Res_Standard_Id = i_Standard_Info_Id) LOOP
      NEW_TABLES.EXTEND(); --数组赋值之前先 添加(扩展)一条空记录
     NEW_TABLES(NEW_TABLES.COUNT):=NEW_TABLES_TP(SSR.STANDARD_ID);
     
    END LOOP;
    
    --删除本身的标准相关性
    delete from STD_STANDARD_RES where STANDARD_ID = i_Standard_Info_Id;
  
    --删除与此标准相关的标准相关性
    delete from STD_STANDARD_RES
     where Res_Standard_Id = i_Standard_Info_Id;
  
    --删除产品相关性
    delete from std_product_standard
     where STANDARD_ID = i_Standard_Info_Id;
  
    --删除数据推送表中反馈表数据
    DELETE FROM std_data_push_item
     WHERE DATA_ID IN
           (SELECT ID
              FROM std_standard_feedback
             WHERE STD_STANDARD_INFO_ID = i_Standard_Info_Id)
       AND DATA_TABLE = 'STD_STANDARD_FEEDBACK';
    --删除标准反馈表与标准相关
    delete from std_standard_feedback
     where STD_STANDARD_INFO_ID = i_Standard_Info_Id;
  
    --删除数据推送表中有效性数据
    DELETE FROM std_data_push_item
     WHERE DATA_ID IN
           (SELECT ID
              FROM std_standard_valid_apply
             WHERE STD_STANDARD_INFO_ID = i_Standard_Info_Id)
       AND DATA_TABLE = 'STD_STANDARD_VALID_APPLY';
    --删除标准有效性
    delete from std_standard_valid_apply
     where STD_STANDARD_INFO_ID = i_Standard_Info_Id;
  
    --删除体系明细表
    delete from std_structure_standard
     where STD_STANDARD_ID = i_Standard_Info_Id;
  
    --删除数据推送中标准相关
    DELETE FROM std_data_push_item
     WHERE DATA_ID = i_Standard_Info_Id
       AND DATA_TABLE = 'STD_STANDARD_INFO';
  
    --更新相关性标示
    FOR I IN (SELECT STANDARD_ID FROM TABLE(CAST(NEW_TABLES AS NEW_TABLES_TABLE))) LOOP
      
      SELECT count(1)
        INTO v_count
        FROM Std_Standard_Res ssr, STD_STANDARD_INFO ssi
       where ssr.standard_id = ssi.id
         and ssr.standard_id = I.standard_id
         and ssi.is_updated_flag = '1';
      if v_count < 1 then
        UPDATE STD_STANDARD_INFO ssi
           SET ssi.IS_RES_FLAG = '0'
         WHERE ssi.ID = I.standard_id;
      end if;
    END LOOP;
    
    --删除标准本身
    delete from std_standard_info where id = i_Standard_Info_Id;
  
    o_Return_Int    := Codecollection.Success;
    o_Return_String := '删除成功：' || SQLERRM;
  EXCEPTION
    WHEN OTHERS THEN
      o_Return_Int    := Codecollection.Fail;
      o_Return_String := '删除失败：' || SQLERRM;
  END Delete_Standard_Res_Data;


  /*****************************************
     数据推送模块，批量复制已推送部门的数据
     SHIC
     2012.12.12
  ******************************************/
  PROCEDURE Data_Push_Copy_Datas(i_To_Std_Data_Push_Id   VARCHAR2, -- 推送id
                                 i_From_Std_Data_Push_Id VARCHAR2, -- 已推送id
                                 o_Return_Int            OUT INT,
                                 o_Return_String         OUT VARCHAR2) IS
    TYPE STD_DATA_PUSH_ITEMS IS TABLE OF STD_DATA_PUSH_ITEM%ROWTYPE; --存放每行记录的类型
    NEW_ROWS                   STD_DATA_PUSH_ITEMS;
    V_TO_COUNTS                NUMBER;
    V_COPY_DATA_PUSH_ID        STD_DATA_PUSH.COPY_DATA_PUSH_ID%TYPE; --复制主表ID
    V_STD_DATA_PUSH_ITEM_COUNT NUMBER; --集合记录
  Begin
  
    --查询已推送数据集合
    SELECT INNERS.* BULK COLLECT
      INTO NEW_ROWS
      FROM (SELECT SSPI.*
              FROM STD_DATA_PUSH_ITEM SSPI
             WHERE SSPI.STD_DATA_PUSH_ID = I_FROM_STD_DATA_PUSH_ID) INNERS;
    --集合是否为空
    SELECT COUNT(1)
      INTO V_STD_DATA_PUSH_ITEM_COUNT
      FROM STD_DATA_PUSH_ITEM SSPI
     WHERE SSPI.STD_DATA_PUSH_ID = I_FROM_STD_DATA_PUSH_ID;
  
    --判断推送表中是否存在推送数据----
    SELECT SDP.COPY_DATA_PUSH_ID
      INTO V_COPY_DATA_PUSH_ID
      FROM STD_DATA_PUSH SDP
     WHERE SDP.ID = I_TO_STD_DATA_PUSH_ID;
  
    --如果存在数据推送
    IF V_COPY_DATA_PUSH_ID IS NOT NULL THEN
      --删除已有的数据
      DELETE FROM STD_DATA_PUSH_ITEM SDPI
       WHERE SDPI.STD_DATA_PUSH_ID = I_TO_STD_DATA_PUSH_ID;
    
      IF V_STD_DATA_PUSH_ITEM_COUNT > 0 THEN
        -----更新------
        FOR I IN NEW_ROWS.FIRST() .. NEW_ROWS.LAST() LOOP
        
          NEW_ROWS(I).ID := SYS_GUID();
          NEW_ROWS(I).STD_DATA_PUSH_ID := I_TO_STD_DATA_PUSH_ID; --替换新推送id
        
        END LOOP;
      
        --批量向信息推送子表中写入数据
        FORALL J IN NEW_ROWS.FIRST() .. NEW_ROWS.LAST()
          INSERT INTO STD_DATA_PUSH_ITEM VALUES NEW_ROWS (J);
      
      END IF;
    
    ELSE
      ---添加-----------
      IF V_STD_DATA_PUSH_ITEM_COUNT > 0 THEN
        --如果NEW_ROWS 为null,当执行NEW_ROWS.FIRST()会抛出ORA-06502: PL/SQL: 数字或值错误
        --循环进行新值的赋值 
        FOR I IN NEW_ROWS.FIRST() .. NEW_ROWS.LAST() LOOP
        
          SELECT COUNT(1)
            INTO V_TO_COUNTS
            FROM STD_DATA_PUSH_ITEM SSPI
           WHERE SSPI.STD_DATA_PUSH_ID = I_TO_STD_DATA_PUSH_ID
             AND SSPI.DATA_TABLE = NEW_ROWS(I).DATA_TABLE
             AND SSPI.DATA_ID = NEW_ROWS(I).DATA_ID;
        
          IF V_TO_COUNTS <= 0 THEN
            INSERT INTO STD_DATA_PUSH_ITEM
              (ID,
               STD_DATA_PUSH_ID,
               DATA_TABLE,
               DATA_ID,
               CREATED_BY,
               CREATION_DATE,
               LAST_UPDATED_BY,
               LAST_UPDATE_DATE,
               LAST_UPDATE_IP,
               VERSION)
            VALUES
              (SYS_GUID(),
               I_TO_STD_DATA_PUSH_ID,
               NEW_ROWS(I).DATA_TABLE,
               NEW_ROWS(I).DATA_ID,
               NEW_ROWS(I).CREATED_BY,
               NEW_ROWS(I).CREATION_DATE,
               NEW_ROWS(I).LAST_UPDATED_BY,
               NEW_ROWS(I).LAST_UPDATE_DATE,
               NEW_ROWS(I).LAST_UPDATE_IP,
               NEW_ROWS(I).VERSION);
          END IF;
        
        END LOOP;
      END IF;
    END IF;
  
    --更新推送主表 推送id
    UPDATE STD_DATA_PUSH SDP
       SET SDP.COPY_DATA_PUSH_ID = I_FROM_STD_DATA_PUSH_ID
     WHERE SDP.ID = I_TO_STD_DATA_PUSH_ID;
  
    COMMIT;
    O_RETURN_INT    := CODECOLLECTION.SUCCESS;
    O_RETURN_STRING := '数据推送执行成功';
  
  EXCEPTION
    WHEN OTHERS THEN
      ROLLBACK;
      O_RETURN_INT    := CODECOLLECTION.FAIL;
      O_RETURN_STRING := '数据推送执行失败';
    
  END Data_Push_Copy_Datas;


END STD_COMMON_P;