Oracle&&Mysql存储过程实例

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

一、Oracle版

create or replace procedure add_table_view_to_dataObjectIS  --查询数据库中所有的系统  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:LOOPFETCH sysCur INTO sysId;   IF done=1 THEN    LEAVE sysLoop;END IF;             OPEN tabCur;tabLoop:LOOPFETCH 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;-- 若已存在则更新ELSESELECT 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.csdn.net/li_guang/article/details/3995794