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;