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, MIDTBTY 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 20 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触发器为例
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 (:N 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表主键为例
SELECT COUNT(1) INTO TBCOUNT FROM USER_CONSTRAINTS T WHERE T.TABLE_NAME = UPPER('LF_MIDTBNAME_P1') AND T.CONSTRAINT_TYPE = 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 = IF TBCOUNT = 1 THEN
--如果存在，使⽤快速执⾏语句删除主键
EXECUTE IMMEDIATE 'ALTER TABLE LF_MIDTBNAME_P1 DROP CONSTRAINT LF_MIDTBNAME_P1_PRIMARY'; END IF;
END; /
javabean的属性可以使用六、增加表字段存储过程
CREATE OR REPLACE PROCEDURE COLUMN_ADD( TBNAME IN VARCHAR2 DEFAULT 0, COLNAME IN VARCHAR2 DEFAULT 0, COLTYPE IN VARC
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 V
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;
/jsp的本质是servlet
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,
mysql语句转oracle
php命名空间的作用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为例：

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。