可重复执行SQL语句|建表、插入默认值、增加字段、删除字段、修改字段可重复执行SQL语句|oracle|mysql

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;
/

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字段
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字段
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;
/

-- 修改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;
/

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;

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字段
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字段
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;

-- 修改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;