可重复执行的SQL语句写法

mysql可重复执行语句

-- 插入字段
set @sql = 'select 1 from dual;';
select 'ALTER TABLE 表名 ADD 字段名 类型 COMMENT ''英文名称'';' into @sql from dual where (select count(1) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA=SCHEMA() AND TABLE_NAME='表名' AND COLUMN_NAME='字段名')=0;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE tmt;

-- 插入数据
set @sql = 'select 1 from dual;';
select 'INSERT INTO 表名(字段1，字段2)  VALUES (值,值);' into @sql from dual where (select count(1) FROM 表名 where 字段名 = 值)=0;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 创建表
CREATE TABLE IF NOT EXISTS 表名 (字段名 类型);

oracle可重复执行

-- 插入数据
DECLARE
 VN_COUNT INT;
BEGIN
SELECT COUNT(1) INTO VN_COUNT FROM 表名 where 字段名=值 ;
 IF VN_COUNT = 0 THEN
    INSERT INTO 表名(字段名) VALUES (值);
 END IF;
END;
/
commit;

-- 插入字段
DECLARE
  VN_COUNT INT;
BEGIN
SELECT count(column_name) into VN_COUNT from cols where table_name = upper('表名') and  column_name = upper('字段名');
  IF VN_COUNT = 0 THEN
    execute immediate 'ALTER TABLE 表名 ADD 字段名 类型';
  END IF;
END;
/
commit;

-- 新增表
DECLARE
  VN_COUNT INT;
BEGIN
SELECT count(1) into VN_COUNT from user_tables where table_name = upper('表名');
  IF VN_COUNT > 0 THEN
        Execute immediate '建表语句';
  END IF;
END;
/
commit;

-- 新增索引
DECLARE  
  VN_COUNT INT;  
BEGIN  
  SELECT COUNT(1)  INTO VN_COUNT FROM user_indexes WHERE index_name = upper('索引名');  
  IF VN_COUNT = 0 THEN  
   EXECUTE immediate 'CREATE UNIQUE INDEX 索引名 ON 表名(字段名,字段名,...)';  
  END IF;  
END;  
/
commit;