Oracle&&Mysql存储过程实例-优快云博客

本文链接：https://blog.youkuaiyun.com/nimeijian/article/details/50484579

功能：将数据库中所有的用户表和视图的表名和表注释读取后插入到fbs_dataObject表，（若已存在则更新）并读取这些用户表的所有字段及字段注释后插入到fbs_dataobject_field_label表。（若已存在则更新）

一、Oracle版

create or replace procedure add_table_view_to_dataObject
IS
  --查询数据库中所有的系统
  CURSOR sysCur IS select sys_id from FBS_SYSTEM;
  --查询数据库中所有的用户表名
  CURSOR tabCur IS select TABLE_NAME from user_tables;
  --查询数据库中所有的用户视图名
  CURSOR viewCur IS select VIEW_NAME from user_views;
  dataObjNum INTEGER;
  note VARCHAR(100);
  dataObjectId VARCHAR(100);
  dataObjectIds VARCHAR(10000);
BEGIN
  -- 删除fbs_dataObject/fbs_dataobject_field_label中实际不存在的表/视图
  SELECT t1.ids || ',' || t2.ids INTO dataObjectIds
  FROM (SELECT wm_concat(''''||id||'''') ids FROM fbs_dataObject WHERE type = '1' AND object_name NOT IN (SELECT TABLE_NAME from user_tables)) t1,
       (SELECT wm_concat(''''||id||'''') ids FROM fbs_dataObject WHERE type = '2' AND object_name NOT IN (select VIEW_NAME from user_views)) t2;
  
  IF dataObjectIds != ',' THEN
    EXECUTE IMMEDIATE 'BEGIN 
                          DELETE FROM fbs_dataobject_field_label WHERE dataobject_id IN ('||dataObjectIds||');
                          DELETE FROM fbs_dataObject WHERE id IN ('||dataObjectIds||'); 
                       END;';
  END IF;
  
  --遍历所有的系统
  FOR sysId in sysCur LOOP
    --遍历所有的用户表
    FOR tabName in tabCur LOOP
        -- 判断该表是否已在fbs_dataObject中
        select COUNT(1) INTO dataObjNum from fbs_dataObject where sys_id=sysId.Sys_Id and type='1' and object_name = tabName.Table_Name;
        -- 获取表注释
        SELECT c.comments INTO note from user_tab_comments c WHERE c.table_type = 'TABLE' AND c.table_name = tabName.Table_Name;
        -- 若不存在则新增          
        IF dataObjNum = 0 THEN
          SELECT sys_guid() INTO dataObjectId FROM dual;


          -- 插入主表
          insert into fbs_dataObject(id,sys_id,object_name,type,fbs_sql,note,created_time)
            select dataObjectId,sysId.Sys_Id,tabName.Table_Name,'1','select * from '|| tabName.Table_Name, note, sysdate
            from dual;
          
          -- 插入子表
          INSERT INTO fbs_dataobject_field_label(id,sys_id,dataobject_id,property_name,display_title,created_time) 
             SELECT sys_guid(),sysId.Sys_Id,dataObjectId, t.column_name, t.comments,SYSDATE 
             FROM (select t.column_name,t.comments from user_col_comments t WHERE t.table_name = tabName.Table_Name) t;
        -- 若已存在则更新
        ELSE
            SELECT id INTO dataObjectId FROM fbs_dataObject WHERE sys_id=sysId.Sys_Id and type='1' and object_name = tabName.Table_Name;
            --更新主表
            UPDATE fbs_dataObject SET note=note, updated_time=sysdate WHERE id = dataObjectId;
            -- 更新子表
            DELETE FROM fbs_dataobject_field_label WHERE dataobject_id = dataObjectId;
            INSERT INTO fbs_dataobject_field_label(id,sys_id,dataobject_id,property_name,display_title,created_time) 
                SELECT sys_guid(),sysId.Sys_Id,dataObjectId, t.column_name, t.comments,SYSDATE 
                FROM (select t.column_name,t.comments from user_col_comments t WHERE t.table_name = tabName.Table_Name) t;
        END IF;
    END LOOP;


    --遍历所有的用户视图
    FOR viewName in viewCur LOOP
        -- 判断该视图是否已在fbs_dataObject中
        select COUNT(1) INTO dataObjNum from fbs_dataObject where sys_id=sysId.Sys_Id and type='2' and object_name = viewName.View_Name;
        -- 获取视图注释
        SELECT c.comments INTO note from user_tab_comments c WHERE c.table_type = 'VIEW' AND c.table_name = viewName.View_Name;
        -- 若不存在则新增         
        IF dataObjNum = 0 THEN
          SELECT sys_guid() INTO dataObjectId FROM dual;
          -- 插入主表
          insert into fbs_dataObject(id,sys_id,object_name,type,fbs_sql,note,created_time)
            SELECT dataObjectId,sysId.Sys_Id,viewName.View_Name,'2','select * from '|| viewName.View_Name,note,sysdate
            from dual;
          -- 插入子表
          INSERT INTO fbs_dataobject_field_label(id,sys_id,dataobject_id,property_name,display_title,created_time) 
             SELECT sys_guid(),sysId.Sys_Id,dataObjectId, t.column_name, t.comments,SYSDATE 
             FROM (select t.column_name,t.comments from user_col_comments t WHERE t.table_name = viewName.View_Name) t;
        -- 若已存在则更新
        ELSE
            SELECT id INTO dataObjectId FROM fbs_dataObject WHERE sys_id=sysId.Sys_Id and type='2' and object_name = viewName.View_Name;
            --更新主表
            UPDATE fbs_dataObject SET note=note, updated_time=sysdate WHERE id = dataObjectId;
            -- 更新子表
            DELETE FROM fbs_dataobject_field_label WHERE dataobject_id = dataObjectId;
            INSERT INTO fbs_dataobject_field_label(id,sys_id,dataobject_id,property_name,display_title,created_time) 
                SELECT sys_guid(),sysId.Sys_Id,dataObjectId, t.column_name, t.comments,SYSDATE 
                FROM (select t.column_name,t.comments from user_col_comments t WHERE t.table_name = viewName.View_Name) t;
        END IF;
    END LOOP;
  END LOOP;
  COMMIT;
END;

二、Mysql版

DELIMITER $$

USE `fbsys`$$

DROP PROCEDURE IF EXISTS `add_table_view_to_dataObject`$$

CREATE DEFINER=`fbsys`@`%` PROCEDURE `add_table_view_to_dataObject`()
BEGIN
  DECLARE sysId VARCHAR(100);
  DECLARE tabName VARCHAR(100);
  DECLARE tabComment VARCHAR(100);
  DECLARE tabType VARCHAR(10);
  DECLARE dataObjNum INT;
  DECLARE dataObjectId VARCHAR(100);
  DECLARE dataObjectIds VARCHAR(10000);
  DECLARE done INT;
  
  -- 查询数据库中所有的系统
  DECLARE sysCur CURSOR FOR SELECT sys_id FROM FBS_SYSTEM;
  -- 查询数据库中所有的用户表/视图的名称、注释及表类型
  DECLARE tabCur CURSOR FOR SELECT TABLE_NAME,table_comment,table_type FROM information_schema.tables WHERE TABLE_SCHEMA='fbsys';
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
  
  -- 删除fbs_dataObject/fbs_dataobject_field_label中实际不存在的表/视图
  SELECT group_concat(ID) INTO dataObjectIds
  FROM fbs_dataObject
  WHERE TYPE IN ('1','2') 
		AND id NOT IN(SELECT o.id
					  FROM fbs_dataObject o,(SELECT TABLE_NAME, CASE table_type WHEN 'BASE TABLE' THEN '1' WHEN 'VIEW' THEN '2' ELSE table_type END tableType FROM information_schema.tables WHERE TABLE_SCHEMA='fbsys') t
					  WHERE o.OBJECT_NAME =  t.TABLE_NAME AND o.TYPE = t.tableType);
	
  DELETE FROM fbs_dataobject_field_label WHERE find_in_set(dataobject_id, dataObjectIds);
  DELETE FROM fbs_dataObject WHERE find_in_set(id, dataObjectIds);

   --  遍历所有的系统
   OPEN sysCur;
   sysLoop:LOOP
	FETCH sysCur INTO sysId;   
	IF done=1 THEN
	    LEAVE sysLoop;
	END IF;
         
    OPEN tabCur;
	tabLoop:LOOP
		FETCH tabCur INTO tabName,tabComment,tabType;
		IF done=1 THEN
		  SET done = 0;
		  LEAVE tabLoop;
		END IF;
			
	    SET tabType = CASE tabType WHEN 'BASE TABLE' THEN '1' WHEN 'VIEW' THEN '2' ELSE tabType END;
	
	    -- 判断该表是否已在fbs_dataObject中
	    SELECT count(1) INTO dataObjNum FROM fbs_dataObject WHERE sys_id=sysId AND TYPE = tabType AND object_name = tabName;
	    -- 若不存在则新增
	    IF dataObjNum = 0 THEN
		  
		  SELECT replace(uuid(),'-','') INTO dataObjectId;
		  -- 插入主表
		  INSERT INTO fbs_dataObject(id,sys_id,object_name,TYPE,fbs_sql,note,created_time)
			VALUES(dataObjectId,sysId,tabName,tabType,concat('select * from ', tabName), tabComment, now());
		  
		  -- 插入从表
		  INSERT INTO fbs_dataobject_field_label(id,sys_id,dataobject_id,property_name,display_title,created_time) 
			 SELECT replace(uuid(),'-',''),sysId,dataObjectId, t.column_name, t.column_comment,now() 
			 FROM (SELECT column_name, column_comment FROM information_schema.columns WHERE table_name = tabName) t;
		-- 若已存在则更新
		ELSE
			SELECT id INTO dataObjectId FROM fbs_dataObject WHERE sys_id=sysId AND TYPE=tabType AND object_name = tabName;
            -- 更新主表
            UPDATE fbs_dataObject SET note=tabComment, updated_time=now() WHERE id = dataObjectId;
            -- 更新子表
            DELETE FROM fbs_dataobject_field_label WHERE dataobject_id = dataObjectId;
            INSERT INTO fbs_dataobject_field_label(id,sys_id,dataobject_id,property_name,display_title,created_time) 
			 SELECT replace(uuid(),'-',''),sysId,dataObjectId, t.column_name, t.column_comment,now() 
			 FROM (SELECT column_name, column_comment FROM information_schema.columns WHERE table_name = tabName) t;
	    END IF;
	END LOOP tabLoop;
	CLOSE tabCur;	
   END LOOP sysLoop;
   CLOSE sysCur;
END$$

DELIMITER ;

Oracle动态sql可参考链接： http://my.oschina.net/u/1458120/blog/225922 或 http://blog.youkuaiyun.com/li_guang/article/details/3995794