在修改表结构时,往往需要先判断一下要修改的内容是否已经按要求修改过了,若已经修改过,则不再执行修改操作,如给表新增一个字段,最简单的办法是先执行一下drop语句,将字段删除一下,此时会出现两种情况:
第一:字段还未创建,执行drop语句时报“字段不存在”错,若该语句是放在sql文件中执行自动升级,往往会导致升级失败;
第二:字段已经创建,drop语句能够正常执行,但是,在测试环境很容易存在重复升级的情况,在上一次升级时,已经创建了该字段,并做了大量的测试工作并产生了测试数据,在本次升级时,并不想丢失这些数据,因此想要的升级结果是,其它新加的sql能正常执行,已经执行过的sql语句不再执行。
正确的解决方案:
使用存储过程判断后再执行sql,先使用如下语句查询是否存在表字段:
select count(1) from USER_TAB_COLUMNS where table_name='TEST' and column_name='field'
若结果为1,则不执行新增语句,若结果为0,则新增字段。但是,该逻辑不够通用,假如下一次不是新增字段,而是修改字段的数据类型或长度,则该存储过程就得重写了。做如下改进后,可以适应任意修改:
CREATE OR REPLACE PROCEDURE EXE_SQL_BY_COND(condSql VARCHAR, trueSql VARCHAR, falseSql VARCHAR)
AS num int:=0; ind int:=0; tmpCondSql text:=''; exeSql text:=''; sqlStr text:='';
BEGIN
tmpCondSql := TRIM(condSql);
IF INSTR(tmpCondSql, ';', LENGTH(tmpCondSql)) > 0 THEN
tmpCondSql := SUBSTR(tmpCondSql, 0, LENGTH(tmpCondSql) - 1);
END IF;
EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM (' || tmpCondSql || ') AS TAB' INTO num;
IF num = 0 THEN
exeSql := falseSql;
ELSE
exeSql := trueSql;
END IF;
exeSql := TRIM(exeSql);
ind := INSTR(exeSql, ';', 1);
WHILE ind > 0 LOOP
sqlStr := SUBSTR(exeSql, 0, ind - 1);
EXECUTE IMMEDIATE sqlStr;
exeSql := SUBSTR(exeSql, ind + 1, LENGTH(exeSql));
ind := INSTR(exeSql, ';', 1);
END LOOP;
IF LENGTH(exeSql) > 0 THEN
EXECUTE IMMEDIATE exeSql;
END IF;
END;
condSql参数:条件语句,如上文中查询表字段是否存在的语句,只是不需要使用count函数计算条数,直接“*”就可以。
trueSql参数:condSql参数的查询sql能查出数据,则为true,执行该参数传入的sql语句,可以为多条语句,以“;”分隔即可。
falseSql参数:condSql参数的查询sql不能查出数据,则为false,执行该参数传入的sql语句,可以为多条语句,以“;”分隔即可。
创建好存储过程后,使用call调用,例:
call EXE_SQL_BY_COND('select * from USER_TAB_COLUMNS where table_name=''TEST'' and column_name=''field''', '', '创建字段的语句');
上面这个语句的含义是,如果TEST表中不存在field字段,则执行创建字段的语句,存在则不做任何操作。
注意:单引号的转义是再在前面加上一个单引号。