数据库（mysql/oracle） 批量修改数据某字段属性

---

mysql存储过程:

代码如下（示例）：

CREATE OR REPLACE PROCEDURE batchUpdateColumn(columnName in varchar2,itemLength in varchar2) as
declare
  -- 修改表字段长度的字符串
  v_alter_sqlstr  varchar2(500);
  -- 游标
  cursor c_result is 
 SELECT
'alter table ' || a.table_name || ' MODIFY  ' || columnName ||' '|| columnType || '('|| itemLength || ')' AS alter_sqlstr
FROM
	user_tab_columns A 
WHERE
	-- 排除视图
	UPPER(A.COLUMN_NAME) = columnName   AND (TABLE_NAME not like 'V_%' and TABLE_NAME not like 'VQ_%')
	AND A.DATA_LENGTH <> itemLength;
  -- 定义一个与游标一样的变更
  v_result c_result%rowtype;
begin
  open c_result;
    loop
      fetch c_result into v_result;  
      exit when c_result%notfound;
      v_alter_sqlstr := v_result.alter_sqlstr;
      dbms_output.put_line(v_alter_sqlstr || ';'); -- 可单独将SQL从output窗口提取出来执行

      -- 执行修改
      execute immediate v_alter_sqlstr;   
    end loop;
  close c_result;
exception
  when others then
    begin
      dbms_output.put_line('异常:' || 'sqlcode:' || sqlcode || ' sqlerrm : ' ||sqlerrm );
    end;  
end;


// 这种调用方式不好,不能同时跑多个.
//declare begin batchUpdateColumn('INSTITUTIONTYPE','VARCHAR2',3); end;
call BATCHUPDATECOLUMNS('INSTITUTIONTYPE','VARCHAR2','3'); 

oracle存储过程:

代码如下（示例）：

-- columnName 字段名称 columnType 字段类型 itemLength 字段长度 tableSchema 数据源
-- 删除batchUpdateColumn
DROP PROCEDURE IF EXISTS batchUpdateColumn;
-- 创建batchUpdateColumn
CREATE PROCEDURE batchUpdateColumn(IN columnName varchar(64),IN columnType varchar(64), IN itemLength varchar(64), IN tableSchema varchar(64))  
  BEGIN
-- 定义变量，游标
DECLARE tn varchar(64);
DECLARE cn varchar(64);
DECLARE an varchar(200);
DECLARE done INT;
			-- 定义结果集，查出表名与字段
      DECLARE rs CURSOR FOR (SELECT TABLE_NAME as tn,columnName as cn , CONCAT( columnType,'(', itemLength,');') AS an
			FROM INFORMATION_SCHEMA.columns WHERE UPPER(column_name) = columnName AND (TABLE_NAME not like 'V_%' and TABLE_NAME not like 'VQ_%'))
	AND COLUMN_TYPE != CONCAT(  DATA_TYPE,'(',itemLength,')') AND table_schema= tableSchema   ;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
      OPEN rs;  
-- 开始循环
read_loop: LOOP
FETCH  NEXT from rs INTO tn,cn,an;
            IF done THEN
                LEAVE read_loop;
             END IF;
-- 拼接修改字段sql
SET @tempsql = CONCAT('ALTER TABLE ',tn,' MODIFY COLUMN ',cn,' ', an);
-- 执行sql
PREPARE stmt FROM @tempsql; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;
END LOOP;
      CLOSE rs;
END;
-- 调用存储过程  XXXX 是数据源  -- 循环填写 数据源 saleorder salebatch... 
call batchUpdateColumn('INSTITUTIONTYPE','varchar','3','XXXX');