oracle、mysql、sqlserver可重复执行SQL语句

Oracle

一、增删表语句

1、以创建LF_MIDTBNAME_P1表为例

DECLARE TBCOUNT NUMBER; 
BEGIN 
--从系统表中查询当表是否存在 
SELECT COUNT(1)INTO TBCOUNT FROM USER_TABLES T WHERE T.TABLE_NAME = UPPER('LF_MIDTBNAME_P1'); 
IF TBCOUNT = 0 THEN 
--如果不存在，使用快速执行语句创建新表
 EXECUTE IMMEDIATE ' CREATE TABLE LF_MIDTBNAME_P1 ( ID NUMBER(22) NOT NULL, MIDTBNAME VARCHAR2(64) DEFAULT '' '' NOT NULL, MIDTBTYPE NUMBER(2) DEFAULT ''0'' NOT NULL )'; 
END IF; 
END; /

2、以删除LF_MIDTBNAME_P1表为例：

DECLARE TBCOUNT NUMBER; BEGIN 
--从系统表中查询当表是否存在 
SELECT COUNT(1)INTO TBCOUNT FROM USER_TABLES T WHERE T.TABLE_NAME = UPPER('LF_MIDTBNAME_P1'); 
IF TBCOUNT = 1 THEN 
--如果存在，使用快速执行语句删除表 EXECUTE IMMEDIATE ' DROP TABLE LF_MIDTBNAME_P1'; 
END IF; 
END; /

二、增删表序列语句

1、以创建LF_MIDTBNAME_P1表的SEQ_LF_MIDTBNAME_P1序列为例

DECLARE TBCOUNT NUMBER;
 BEGIN 
--从系统表中查询当序列是否存在 
SELECT COUNT(1) INTO TBCOUNT FROM USER_SEQUENCES T WHERE T.SEQUENCE_NAME = UPPER('SEQ_LF_MIDTBNAME_P1'); 
IF TBCOUNT = 0 THEN
 --如果不存在，使用快速执行语句创建新序列 
EXECUTE IMMEDIATE 'CREATE SEQUENCE SEQ_LF_MIDTBNAME_P1 MINVALUE 1 MAXVALUE 99999999999 START WITH 1 INCREMENT BY 1 CACHE 200 ORDER';
 END IF;
 END; /

2、以删除LF_MIDTBNAME_P1表的SEQ_LF_MIDTBNAME_P1序列为例

DECLARE TBCOUNT NUMBER; 
BEGIN 
--从系统表中查询当序列是否存在 
SELECT COUNT(1) INTO TBCOUNT FROM USER_SEQUENCES T WHERE T.SEQUENCE_NAME = UPPER('SEQ_LF_MIDTBNAME_P1'); 
IF TBCOUNT = 1 THEN
 --如果存在，使用快速执行语句删除序列 
EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_LF_MIDTBNAME_P1'; 
END IF;
 END; /

三、增删表触发器

1、以创建LF_MIDTBNAME_P1表的TIG_LF_TRANS_LOG_P1触发器为例

 DECLARE TBCOUNT NUMBER;
 BEGIN 
--从系统表中查询当触发器是否存在
 SELECT COUNT(1) INTO TBCOUNT FROM USER_TRIGGERS T WHERE T.TRIGGER_NAME = UPPER('TIG_LF_MIDTBNAME_P1'); 
IF TBCOUNT = 0 THEN 
--如果不存在，使用快速执行语句创建新触发器 
EXECUTE IMMEDIATE ' CREATE OR REPLACE TRIGGER TIG_LF_MIDTBNAME_P1 BEFORE INSERT ON LF_MIDTBNAME_P1 FOR EACH ROW BEGIN IF (:NEW.ID IS NULL) THEN SELECT SEQ_LF_MIDTBNAME_P1.NEXTVAL INTO :NEW.ID FROM DUAL; 
END IF; END;';
 END IF; 
END; /

2、以删除LF_MIDTBNAME_P1表的TIG_LF_TRANS_LOG_P1触发器为例

 DECLARE TBCOUNT NUMBER; 
BEGIN 
--从系统表中查询当触发器是否存在
 SELECT COUNT(1) INTO TBCOUNT FROM USER_TRIGGERS T WHERE T.TRIGGER_NAME = UPPER('TIG_LF_MIDTBNAME_P1'); 
IF TBCOUNT = 1 THEN
 --如果存在，使用快速执行语句删除触发器 
EXECUTE IMMEDIATE ' DROP TRIGGER TIG_LF_MIDTBNAME_P1 ';
 END IF; 
END; /

四、增删表索引

1、以增加LF_MIDTBNAME_P1表索引为例

DECLARE TBCOUNT NUMBER;
 BEGIN 
--从系统表中查询当索引是否存在
 SELECT COUNT(1) INTO TBCOUNT FROM USER_IND_COLUMNS WHERE INDEX_NAME = UPPER("ID"); 
IF TBCOUNT = 0 THEN 
--如果不存在，使用快速执行语句创建新索引 
EXECUTE IMMEDIATE 'CREATE INDEX IX_LF_MIDTBNAME_P1_ID ON LF_MIDTBNAME_P1(ID)'; 
END IF; 
END; /

2、以删除LF_MIDTBNAME_P1表索引为例

DECLARE TBCOUNT NUMBER; 
BEGIN 
--从系统表中查询当索引是否存在 
SELECT COUNT(1) INTO TBCOUNT FROM USER_IND_COLUMNS WHERE INDEX_NAME = UPPER("ID"); 
IF TBCOUNT = 1 THEN 
--如果不存在，使用快速执行语句删除索引 
EXECUTE IMMEDIATE 'CREATE INDEX IX_LF_MIDTBNAME_P1_ID ON LF_MIDTBNAME_P1(ID)'; 
--创建唯一索引使用： 
EXECUTE IMMEDIATE 'CREATE UNIQUE INDEX IX_LF_MIDTBNAME_P1_ID ON LF_MIDTBNAME_P1(ID)';
 END IF;
 END; /

五、增删主键

1、以增加LF_MIDTBNAME_P1表主键为例

DECLARE TBCOUNT NUMBER; 
BEGIN 
--从系统表中查询当主键是否存在 
SELECT COUNT(1) INTO TBCOUNT FROM USER_CONSTRAINTS T WHERE T.TABLE_NAME = UPPER('LF_MIDTBNAME_P1') AND T.CONSTRAINT_TYPE = 'P'; 
IF TBCOUNT = 0 THEN 
--如果不存在，使用快速执行语句创建新主键
 EXECUTE IMMEDIATE 'ALTER TABLE LF_MIDTBNAME_P1 ADD CONSTRAINT LF_MIDTBNAME_P1_PRIMARY PRIMARY KEY (ID)'; 
END IF; 
END; /

2、以删除LF_MIDTBNAME_P1表索引为例

 

DECLARE TBCOUNT NUMBER;
 BEGIN 
--从系统表中查询当主键是否存在 
SELECT COUNT(1) INTO TBCOUNT FROM USER_CONSTRAINTS T WHERE T.TABLE_NAME = UPPER('LF_MIDTBNAME_P1') AND T.CONSTRAINT_TYPE = 'P'; 
IF TBCOUNT = 1 THEN 
--如果存在，使用快速执行语句删除主键 
EXECUTE IMMEDIATE 'ALTER TABLE LF_MIDTBNAME_P1 DROP CONSTRAINT LF_MIDTBNAME_P1_PRIMARY'; END IF;
END; /

六、增加表字段存储过程

 CREATE OR REPLACE PROCEDURE COLUMN_ADD( TBNAME IN VARCHAR2 DEFAULT 0, COLNAME IN VARCHAR2 DEFAULT 0, COLTYPE IN VARCHAR2 DEFAULT 0 )
 AS PICNT NUMBER; 
BEGIN SELECT COUNT(1) INTO PICNT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = TBNAME AND COLUMN_NAME = COLNAME; 
IF PICNT=0 THEN EXECUTE IMMEDIATE 'ALTER TABLE '||TBNAME||' ADD '||COLNAME||' '||COLTYPE||' '; 
END IF; 
END;
 /

七、修改表字段存储过程

CREATE OR REPLACE PROCEDURE COLUMN_UPDATE( TBNAME IN VARCHAR2 DEFAULT 0, COLNAME IN VARCHAR2 DEFAULT 0, COLTYPE IN VARCHAR2 DEFAULT 0 )
 AS PICNT NUMBER;
 BEGIN SELECT COUNT(1) INTO PICNT FROM USER_TAB_COLUMNS WHERE TABLE_NAME = TBNAME AND COLUMN_NAME = COLNAME; 
IF PICNT>0 THEN EXECUTE IMMEDIATE 'ALTER TABLE '||TBNAME||' MODIFY( '||COLNAME||' '||COLTYPE||' )'; 
END IF; 
END; 
/

Mysql

一、增删表语句

1、以创建LF_VERSION表为例：

--如果不存在则创建
CREATE TABLE IF NOT EXISTS `LF_VERSION`(
	ID INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	PRODUCT_ID INT DEFAULT 0 NOT NULL,	
	PROCESS_ID INT DEFAULT 0 NOT NULL,	
	MODULE_ID INT DEFAULT 0	NOT NULL,		
	MODULE_NAME VARCHAR(32) DEFAULT '' NOT NULL,
	VERSION VARCHAR(16) DEFAULT '0' NOT NULL,
	UPDATETIME TIMESTAMP NOT NULL DEFAULT '1970-01-01 10:00:00',	
	CREATETIME TIMESTAMP NOT NULL DEFAULT '1970-01-01 10:00:00',		
	MEMO VARCHAR(128) DEFAULT '' NOT NULL 
	) ENGINE=INNODB DEFAULT CHARSET=UTF8;

2、以删除LF_VERSION表为例：

DROP TABLE IF EXISTS `LF_VERSION`;

二、增删表触发器

1、以创建触发器TIG_TRANS_LOG为例：

DELIMITER ;;
DROP TRIGGER IF EXISTS `TIG_TRANS_LOG`;
	CREATE TRIGGER `TIG_TRANS_LOG`  BEFORE INSERT ON `TRANS_LOG` FOR EACH ROW BEGIN
	SET @P_CURTIME = NOW();
	IF DATE_FORMAT(NEW.CREATETIME, '%Y-%m-%d') = '0000-00-00' THEN
		SET NEW.CREATETIME = @P_CURTIME;
	END IF;
END;;
DELIMITER ;

2、以删除触发器TIG_TRANS_LOG为例：

DROP TRIGGER IF EXISTS `TIG_TRANS_LOG`;

三、增删表索引

1、以创建索引IX_MMS_MO_US为例：

-- 判断索引是否存在，不存在则创建索引
DROP PROCEDURE IF EXISTS add_index;  
DELIMITER ;;
	CREATE PROCEDURE add_index() 
	BEGIN 
	DECLARE  target_database VARCHAR(100);
	DECLARE  target_table_name VARCHAR(100);
	DECLARE  target_column_name VARCHAR(100);
	DECLARE  target_index_name VARCHAR(100);
	set target_table_name = 'TRANS_LOG';	--表名
	set target_column_name = 'ID,USETYPE,TRANSNAME';  --字段名
	set target_index_name = 'IX_MMS_MO_US';   --索引名
	SELECT DATABASE() INTO target_database;  --数据库名
	IF NOT EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema =
	target_database AND table_name = target_table_name AND index_name = 
	target_index_name)THEN
		set @statement = CONCAT("ALTER TABLE ", target_table_name, " ADD INDEX ",
		target_index_name, "(", target_column_name, " )");	--注意CONCAT拼串里面的空格
		PREPARE STMT FROM @statement;
		EXECUTE STMT;
	END IF;
	END;;
DELIMITER ;   -- 注意 DELIMITER 与分号之间有空格
CALL add_index();

2、以删除索引IX_MMS_MO_US为例：

-- 判断索引是否存在，存在则删除索引
DROP PROCEDURE IF EXISTS del_index;  
DELIMITER ;;
	CREATE PROCEDURE del_index() BEGIN 
	DECLARE  target_database VARCHAR(100);
	DECLARE  target_table_name VARCHAR(100);
	DECLARE  target_column_name VARCHAR(100);
	DECLARE  target_index_name VARCHAR(100);
	set target_table_name = 'TRANS_LOG';
	set target_column_name = 'ID,USETYPE,TRANSNAME';
	set target_index_name = 'IX_MMS_MO_US';
	SELECT DATABASE() INTO target_database;
	IF EXISTS (SELECT * FROM information_schema.statistics WHERE table_schema =
	target_database AND table_name = target_table_name AND index_name = 
	target_index_name) THEN
		set @statement = CONCAT("DROP INDEX ", target_index_name, " ON ", 
		target_table_name);	--注意CONCAT拼串里面的空格
		PREPARE STMT FROM @statement;
		EXECUTE STMT;
	END IF;
	END;;
DELIMITER ;   -- 注意 DELIMITER 与分号之间有空格
CALL del_index();

四、增删表主键

1、以增加表主键id为例：

-- 判断表的主键是否存在，不存在则创建主键
DROP PROCEDURE IF EXISTS add_primary;  
DELIMITER ;;
	CREATE PROCEDURE add_primary() 
	BEGIN 
	DECLARE  target_database VARCHAR(100);		--数据库名
	DECLARE  target_table_name VARCHAR(100);	--表名
	DECLARE  target_column_name VARCHAR(100);  --主键字段名
	set target_table_name = 'my_test';	
	set target_column_name = 'id';
	SELECT DATABASE() INTO target_database;
	IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE 
	table_schema = target_database AND table_name=target_table_name AND 
	constraint_name='PRIMARY') THEN
		set @statement = CONCAT("ALTER TABLE ", target_table_name, " ADD PRIMARY KEY(",
		target_column_name, ")");	--注意CONCAT拼串里面的空格
		PREPARE STMT FROM @statement;
		EXECUTE STMT;
	END IF;
	END;;
DELIMITER ;   -- 注意 DELIMITER 与分号之间有空格
CALL add_primary();

2、以删除表主键ID为例：

-- 判断表的主键是否存在，存在则删除
DROP PROCEDURE IF EXISTS del_primary;  
DELIMITER ;;
	CREATE PROCEDURE del_primary() 
	BEGIN 
	DECLARE  target_database VARCHAR(100);
	DECLARE  target_table_name VARCHAR(100);
	set target_table_name = 'my_test';
	SELECT DATABASE() INTO target_database;
	IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.`KEY_COLUMN_USAGE` WHERE table_schema =
	target_database AND table_name=target_table_name AND constraint_name='PRIMARY') THEN
		set @statement = CONCAT("ALTER TABLE ", target_table_name, " DROP PRIMARY KEY");
		--注意CONCAT拼串里面的空格
		PREPARE STMT FROM @statement;
		EXECUTE STMT;
	END IF;
	END;;
DELIMITER ;   -- 注意 DELIMITER 与分号之间有空格
CALL del_primary();

五、增加修改表字段

1、以增加表字段age为例：

-- 判断表字段是否存在，不存在则添加
DROP PROCEDURE IF EXISTS add_column;  
DELIMITER ;;
	CREATE PROCEDURE add_column(IN COLTYPE varchar(200)) 
	BEGIN 
	DECLARE  target_table_name VARCHAR(100);		--表名
	DECLARE  target_column_name VARCHAR(100);		--字段名
	set target_table_name = 'my_test';
	set target_column_name = 'age';
	IF NOT EXISTS (select * from information_schema.columns where table_name =
	target_table_name and column_name = target_column_name) THEN
		set @statement = CONCAT("ALTER TABLE ", target_table_name, " add ", 
		target_column_name,COLTYPE);	--注意CONCAT拼串里面的空格
		PREPARE STMT FROM @statement;
		EXECUTE STMT;
	END IF;
	END;;
DELIMITER ;   -- 注意 DELIMITER 与分号之间有空格
CALL add_column(' int(6)'); 	-- 注意入参前面有空格

2、以修改表字段age为例：

-- 判断表的字段是否存在，存在则修改
DROP PROCEDURE IF EXISTS modify_column;  
DELIMITER ;;
	CREATE PROCEDURE modify_column(IN COLTYPE varchar(200)) 
	BEGIN 
	DECLARE  target_table_name VARCHAR(100);
	DECLARE  target_column_name VARCHAR(100);
	set target_table_name = 'my_test';
	set target_column_name = 'age';
	IF EXISTS (select * from information_schema.columns where table_name = 	
	target_table_name and column_name = target_column_name) THEN
		set @statement = CONCAT("ALTER TABLE ", target_table_name, " MODIFY ",
		target_column_name,COLTYPE);	--注意CONCAT拼串里面的空格
		PREPARE STMT FROM @statement;
		EXECUTE STMT;
	END IF;
	END;;
DELIMITER ;   -- 注意 DELIMITER 与分号之间有空格
CALL modify_column(' int(100)');	-- 注意入参前面有空格

 

SqlServer

一、增删表语句

1、以创建LF_VERSION表为例：

--如果不存在，则新建
IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'LF_VERSION' AND TYPE = 'U')
BEGIN
	--EMP产品版本版本信息表
	CREATE TABLE LF_VERSION
	(
		ID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,		
		PRODUCT_ID INT DEFAULT 0 NOT NULL,	
		PROCESS_ID INT DEFAULT 0 NOT NULL,	
		MODULE_ID INT DEFAULT 0 NOT NULL,	
		MODULE_NAME VARCHAR(32) DEFAULT '' NOT NULL,
		VERSION VARCHAR(16) DEFAULT '0' NOT NULL,
		UPDATETIME DATETIME DEFAULT GETDATE() NOT NULL,
		CREATETIME DATETIME DEFAULT GETDATE() NOT NULL,
		MEMO VARCHAR(128) DEFAULT '' NOT NULL
	); 
END

2、以删除LF_VERSION表为例：

--如果存在，则删除
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'LF_VERSION' AND TYPE = 'U')
BEGIN
	DROP TABLE LF_VERSION;
END

二、增删表触发器

1、以创建触发器LF_BUSMON_Q为例：

--如果存在，则先删除，再新建
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='LF_BUSMON_Q' AND TYPE ='TR')
BEGIN
	DROP TRIGGER LF_BUSMON_Q
END
GO
	CREATE TRIGGER LF_BUSMON_Q ON LF_BUSMANAGER AFTER UPDATE
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @BUSNAME VARCHAR(32)
	DECLARE @BUSCODE VARCHAR(64)
	DECLARE @CORPCODE VARCHAR(64)
	SELECT @BUSNAME=BUS_NAME,@BUSCODE=BUS_CODE,@CORPCODE=CORP_CODE FROM INSERTED
	UPDATE LF_MON_BUSBASE SET BUS_NAME=@BUSNAME WHERE BUS_CODE=@BUSCODE AND
	CORP_CODE=@CORPCODE
END
GO

2、以删除触发器LF_BUSMON_Q为例：

--如果存在，则删除
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME='LF_BUSMON_Q' AND TYPE ='TR')
BEGIN
	DROP TRIGGER LF_BUSMON_Q
END

三、增删表索引

1、以创建索引IX_MT_WAIT_A_LOGINUID为例：

--如果不存在，则新建索引
IF NOT EXISTS(select * from sysindexes where id=object_id('MT_WAIT_A') and 	name='IX_MT_WAIT_A_LOGINUID')
BEGIN
	CREATE NONCLUSTERED INDEX [IX_MT_WAIT_A_LOGINUID] ON [DBO].[MT_WAIT_A]
	(
		[LOGINUID] ASC
	)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
END
GO

2、以删除索引IX_MT_WAIT_A_LOGINUID为例：

--如果存在，则删除索引
IF EXISTS(select * from sysindexes where id=object_id('MT_WAIT_A') and 	name='IX_MT_WAIT_A_LOGINUID')
BEGIN
	drop index [IX_MT_WAIT_A_LOGINUID] on [MT_WAIT_A]
END
GO

四、增删表主键

1、以增加表主键DVID为例：

--如果不存在，则新增表主键
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE 	TABLE_NAME='LF_VERSION_EMPDB')
BEGIN
	alter table LF_VERSION_EMPDB add  primary key(DVID)
END

2、以删除表主键DVID为例

--如果存在，则删除表主键
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE 	TABLE_NAME='LF_VERSION_EMPDB')
BEGIN
	DECLARE @SQL VARCHAR(4000)
	SET @SQL = 'ALTER TABLE LF_VERSION_EMPDB DROP CONSTRAINT |ConstraintName| '
	SET @SQL = REPLACE(@SQL, '|ConstraintName|', ( SELECT name FROM sysobjects WHERE 
	xtype = 'PK' AND parent_obj = OBJECT_ID('LF_VERSION_EMPDB')))
	EXEC (@SQL)
END

五、增加修改表字段

1、以增加表字段age为例：

--如果不存在，则新增表字段
IF NOT EXISTS (select * from syscolumns where id=object_id
('LF_VERSION_EMPDB') and name='age')
BEGIN
	ALTER TABLE [LF_VERSION_EMPDB] ADD [age] INT NULL
END

2、以修改表字段age为例：

--如果存在，则修改表字段
IF EXISTS (select *from syscolumns where id=object_id('LF_VERSION_EMPDB') and name='age')
BEGIN
	ALTER TABLE [LF_VERSION_EMPDB] ALTER COLUMN [age] NVARCHAR (100) NULL
END