数据库 存储过程 循环

Oracle数据库

场景：使用SQL为多家单位初始化目录模板，使用存储过程-循环

-------------------------------------------------------------------------------------------------------------------------
------------------------------------------请修改下方语句中的CORP_LIST为要初始化的单位列表----------------------------------
DECLARE TYPE CORP_LIST IS VARRAY(100) OF NUMBER(12);
XZ CORP_LIST := CORP_LIST(450200,450202,450203,450204,450205,450206,450209,450221,450222,450223,450224,450225,450226);
-------------------------------------------------------------------------------------------------------------------------



-----------------------------------------------下面的内容请勿修改！！！----------------------------------------------------
--开始初始化
BEGIN

-- 初始化目录表数据
FOR I IN 1..XZ.COUNT 
LOOP
	FOR L_RECORD IN (SELECT * FROM JCSZ.T_YWGY_DZJZ_ML WHERE N_BH_DW IS NULL ORDER BY C_BH) 
	LOOP
	    INSERT INTO "JCSZ"."T_YWGY_DZJZ_ML" ("C_BH","N_BH_DW","N_BH_YW","C_MC","N_XSSX","C_FML","N_YX","N_MLLX","N_DOC","N_SUBDIC","N_MBLX","C_MBBS","N_YWLX","C_YWLX","C_YWZLX","C_XTBS","C_MRMBML","N_SFGK","N_SFGD","N_MARKSTAR","C_CLLY","N_SFYJML","N_MLLB","C_CLSX","N_SFQY","N_SYCX","C_SYCXNAME") 
	    VALUES (MD5(L_RECORD.C_BH||XZ(I)), XZ(I), L_RECORD.N_BH_YW, L_RECORD.C_MC, L_RECORD.N_XSSX, CASE WHEN L_RECORD.C_FML IS NULL THEN NULL ELSE MD5(L_RECORD.C_FML||XZ(I)) END, L_RECORD.N_YX, L_RECORD.N_MLLX, L_RECORD.N_DOC, L_RECORD.N_SUBDIC, L_RECORD.N_MBLX, MD5(L_RECORD.C_MBBS||XZ(I)), L_RECORD.N_YWLX, L_RECORD.C_YWLX, L_RECORD.C_YWZLX, L_RECORD.C_XTBS, L_RECORD.C_MRMBML, L_RECORD.N_SFGK, L_RECORD.N_SFGD, L_RECORD.N_MARKSTAR, L_RECORD.C_CLLY, L_RECORD.N_SFYJML, L_RECORD.N_MLLB, L_RECORD.C_CLSX, L_RECORD.N_SFQY, L_RECORD.N_SYCX, L_RECORD.C_SYCXNAME);
	END LOOP;
END LOOP;
COMMIT;

-- 初始化监管表数据
FOR L_RECORD IN (SELECT * FROM jcsz.t_ywgy_dzjz_ml WHERE C_FML IN (SELECT C_BH FROM jcsz.t_ywgy_dzjz_ml WHERE C_FML IN (SELECT C_BH FROM jcsz.t_ywgy_dzjz_ml WHERE C_FML IS NULL)) ORDER BY C_BH) 
LOOP
	INSERT INTO JCSZ.T_YWGY_DZJZ_JG ("C_BH","C_BH_ML","N_YWLB","C_YWBH","N_SYJD","N_JAFS","N_YWBS","N_YX") values (sys_guid(), L_RECORD.C_BH, 2, L_RECORD.C_BH, null, null, null, 1);
END LOOP;
COMMIT;

-- 初始化映射表数据
FOR I IN 1..XZ.COUNT 
LOOP
	FOR L_RECORD IN (SELECT * FROM JCSZ.T_YWGY_DZJZ_YS WHERE N_XH = -1 ORDER BY C_BH) 
	LOOP
		INSERT INTO "JCSZ"."T_YWGY_DZJZ_YS" ("C_BH","C_BH_ML","C_BH_YS","C_MC_YS","N_PZ","N_XH","C_BEANNAME","N_YX","N_YSLB","N_SFYJYS","DT_YSSJ","N_SFQY") 
		VALUES (MD5(L_RECORD.C_BH||XZ(I)), MD5(L_RECORD.C_BH_ML||XZ(I)), L_RECORD.C_BH_YS, L_RECORD.C_MC_YS, L_RECORD.N_PZ, L_RECORD.C_BEANNAME, null, L_RECORD.N_YX, L_RECORD.N_YSLB, L_RECORD.N_SFYJYS, L_RECORD.DT_YSSJ, L_RECORD.N_SFQY);
	END LOOP;
END LOOP;
COMMIT;
--初始化完成
END;

/

达梦数据库

场景：使用SQL为数据库中已有的系统添加新的元数据节点属性

DECLARE
BEGIN
FOR SYSTEM_DATA IN (select * from DB_GLFX.T_Basedata_Data where c_infoid like '%-system' AND C_KEY = 'NAME') LOOP
    INSERT INTO DB_GLFX.T_Basedata_Data (C_DataID, C_Key, C_Value, C_InfoID, N_Valid, C_Creator, C_Modifier, DT_CreateTime, DT_LastModifyTime, C_SubtypeID)
    VALUES (SYSTEM_DATA.C_DataID, 'beginnerNumber', '3', SYSTEM_DATA.C_INFOID, 1, SYSTEM_DATA.C_Creator, NULL, SYSTEM_DATA.DT_CreateTime, SYSTEM_DATA.DT_LastModifyTime, SYSTEM_DATA.C_SubtypeID);
    INSERT INTO DB_GLFX.T_Basedata_Data (C_DataID, C_Key, C_Value, C_InfoID, N_Valid, C_Creator, C_Modifier, DT_CreateTime, DT_LastModifyTime, C_SubtypeID)
    VALUES (SYSTEM_DATA.C_DataID, 'intermediateNumber', '2', SYSTEM_DATA.C_INFOID, 1, SYSTEM_DATA.C_Creator,NULL, SYSTEM_DATA.DT_CreateTime, SYSTEM_DATA.DT_LastModifyTime, SYSTEM_DATA.C_SubtypeID);
    INSERT INTO DB_GLFX.T_Basedata_Data (C_DataID, C_Key, C_Value, C_InfoID, N_Valid, C_Creator, C_Modifier, DT_CreateTime, DT_LastModifyTime, C_SubtypeID)
    VALUES (SYSTEM_DATA.C_DataID, 'advancedNumber', '1', SYSTEM_DATA.C_INFOID, 1, SYSTEM_DATA.C_Creator, NULL, SYSTEM_DATA.DT_CreateTime, SYSTEM_DATA.DT_LastModifyTime, SYSTEM_DATA.C_SubtypeID);
END LOOP;
END;
COMMIT;