目录
前言
oracle脚本：
建表语句
插入默认值语句
删除某个字段
增加某个字段
有数据情况下修改某个字段为另外的名称
mysql：
建表语句
插入默认值
删除某个字段
增加某个字段
表有数据情况下将某个字段修改为另外的名称
前言
在真实生产环境过程中，我们会用到表，但是随着后面功能的迭代以及更新，会对老表进行一些更新，比如加字段，修改字段类型等，那么随着越来越多的脚本更新，以及同一个项目在不同甲方中，为了保证项目的稳定性，我们需要对一些sql语句实现可重复执行的操作。
比如甲方A的进展已经到3.0阶段了，表需要加A字段，修改B字段为字符串；甲方B进展到2.0字段，只需要表加A字段，这时候如果你的表不是可重复执行的，你越到后面你就维护不清楚到底这张表哪些字段甲方A有，哪些甲方B有，但是当你的脚本是可重复执行的时候，你只需要将2.0的脚本都执行一遍，然后如果是3.0版本的就将3.0的所有脚本都执行一遍就都可以解决了
下面将介绍oracle和mysql的可重复执行脚本
oracle脚本：
建表语句
我们需要创建一张学生表，有id，name，sex，adress，phone字段
代码语言：javascript复制declare v_rowcount number(10);
begin
select count(1) into v_rowcount from user_tables where table_name = upper('z_student');
if v_rowcount = 0 then
execute immediate '
CREATE TABLE z_student
(
id varchar2(64) NOT NULL,
name varchar2(64) NOT NULL,
sex varchar2(64) ,
adress varchar2(64) ,
phone number
) ';
execute immediate 'alter table z_student add constraint PK_z_student primary key (id)';
execute immediate 'comment on table z_student is ''学生表''';
execute immediate 'comment on column z_student.id is ''唯一编码''';
execute immediate 'comment on column z_student.name is ''学生姓名''';
execute immediate 'comment on column z_student.sex is ''性别''';
execute immediate 'comment on column z_student.adress is ''地址''';
execute immediate 'comment on column z_student.phone is ''电话号码''';
commit;
end if;
end;
/插入默认值语句
有时候表里有一些初始值，我们创建几条默认值，这里创建两个学生，一个是张三，一个是李四
id | name | sex | adress | phone |
|---|---|---|---|---|
001 | 张三 | 男 | 杭州市 | 13888888888 |
002 | 李四 | 女 | 北京市 | 15666666666 |
declare v_rowcount number(5);
begin
select count(*) into v_rowcount from dual
where exists (select 1 from z_student where id='001' and name='张三');
if v_rowcount = 0 then
INSERT INTO z_student (id, name, sex, adress, phone) VALUES ('001', '张三', '男', '杭州市', 13888888888);
end if;
commit;
end;
/
declare v_rowcount number(5);
begin
select count(*) into v_rowcount from dual
where exists (select 1 from z_student where id='002' and name='李四');
if v_rowcount = 0 then
INSERT INTO z_student (id, name, sex, adress, phone) VALUES ('002', '李四', '女', '北京市', 15666666666);
end if;
commit;
end;
/删除某个字段
比如不需要phone这个字段
代码语言：javascript复制-- 删除phone字段
declare row_count integer;
begin
select count(*) into row_count from user_tab_cols where upper(table_name) = UPPER('z_student') and upper(column_name) = upper('phone');
if row_count > 0 then
execute immediate 'alter table z_student drop column phone';
commit;
end if;
end;
/增加某个字段
比如增加一个班级class字段
代码语言：javascript复制-- 增加class字段
declare row_count integer;
begin
select count(1) into row_count from user_tab_cols where upper(table_name) = UPPER('z_student') and upper(column_name) = upper('class');
if row_count = 0 then
execute immediate 'alter table z_student add class varchar2 default null';
execute immediate 'comment on column z_student.class is ''班级''';
commit;
end if;
end;
/有数据情况下修改某个字段为另外的名称
比如电话号码我一开始定义的是number，但是实际上有可能有0791-1111111这种，就是字符串类型，那我将phone字段变成pno 且是字符串类型
代码语言：javascript复制-- 修改phone字段变为pno字段
declare
cnt integer;
begin
select count(*) into cnt
from user_tab_cols utc
where upper(table_name) = upper('z_student')
and upper(column_name) = upper('phone');
--and utc.DATA_TYPE <> 'varchar2';
if cnt > 0 then
execute immediate 'alter table z_student add pno varchar2(32) null';
execute immediate 'comment on column z_student.pno is ''电话号码''';
execute immediate 'update z_student ibd set ibd.pno = ibd.phone where 1 = 1';
execute immediate 'alter table z_student drop column phone';
end if;
commit;
end;
/mysql：
建表语句
我们需要创建一张学生表，有id，name，sex，adress，phone字段
代码语言：javascript复制create table if not exists `z_student` (
`id` varchar(64) not null comment '唯一编码',
`name` varchar(64) not null comment '学生姓名',
`sex` varchar(64) comment '性别',
`adress` varchar(64) comment '地址',
`phone` decimal(38,16) comment '电话',
primary key(`guid_no`)
) comment = '学生表';
commit;插入默认值
有时候表里有一些初始值，我们创建几条默认值，这里创建两个学生，一个是张三，一个是李四
id | name | sex | adress | phone |
|---|---|---|---|---|
001 | 张三 | 男 | 杭州市 | 13888888888 |
002 | 李四 | 女 | 北京市 | 15666666666 |
select '表z_student数据变更...';
set @v_rowcount = 0;
select count(*) into @v_rowcount from dual
where exists (select * from z_student where `id`='001' and `name`='张三');
set @sql = if(@v_rowcount = 0, "INSERT INTO z_student (id, name, sex, adress, phone) VALUES ('001', '张三', '男', '杭州市', 13888888888);", "select 1 from dual");
prepare stmt from @sql;
execute stmt;
select '表z_student数据变更...';
set @v_rowcount = 0;
select count(*) into @v_rowcount from dual
where exists (select * from z_student where `id`='002' and `name`='李四');
set @sql = if(@v_rowcount = 0, "INSERT INTO z_student (id, name, sex, adress, phone) VALUES ('002', '李四', '女', '北京市', 15666666666);", "select 1 from dual");
prepare stmt from @sql;
execute stmt;删除某个字段
比如不需要phone这个字段
代码语言：javascript复制-- 删除phone字段
drop procedure if exists sq_db_mysql;
delimiter $$
create procedure sq_db_mysql()
begin
declare v_rowcount int;
declare database_name varchar(100);
select database() into database_name;
select count(*) into v_rowcount from information_schema.columns where table_schema = database_name and table_name = 'z_student' and column_name = 'phone';
if v_rowcount = 1 then
alter table z_student drop column phone;
end if;
end $$
delimiter ;
call sq_db_mysql();
drop procedure if exists sq_db_mysql;增加某个字段
比如增加一个班级class字段
代码语言：javascript复制-- 增加class字段
drop procedure if exists sq_db_mysql;
delimiter $$
create procedure sq_db_mysql()
begin
declare v_rowcount int;
declare database_name varchar(100);
select database() into database_name;
select count(*) into v_rowcount from information_schema.columns where table_schema = database_name and table_name = 'z_student' and column_name = 'class';
if v_rowcount = 0 then
alter table z_student add column class varchar(64) null comment '班级';
end if;
end $$
delimiter ;
call sq_db_mysql();
drop procedure if exists sq_db_mysql;表有数据情况下将某个字段修改为另外的名称
比如电话号码我一开始定义的是number，但是实际上有可能有0791-1111111这种，就是字符串类型，那我将phone字段变成pno 且是字符串类型
代码语言：javascript复制-- 修改phone字段变为pno字段
drop procedure if EXISTS sp_db_mysql;
delimiter $$
create procedure sp_db_mysql()
begin
declare v_rowcount int;
declare database_name varchar(100);
select database() into database_name;
select count(1) into v_rowcount from information_schema.columns where table_schema = database_name and table_name = 'z_student' and column_name = 'phone';
if v_rowcount = 1 then
ALTER TABLE `z_student` CHANGE COLUMN `phone` `pno` varchar(64) NULL DEFAULT NULL COMMENT '电话号码' ;
end if;
end$$
delimiter ;
call sp_db_mysql();
drop procedure if exists sp_db_mysql;以上就是常见的几种情况，包括建表、插入默认值、增加字段、删除字段、修改字段等操作，如果还有其他的，欢迎大家补充更新
