首页 > 数据库技术 > 详细

Oracle 判断表或字段是否存在新增/修改表结构可重复执行sql

时间:2020-07-06 16:50:14      阅读:110      评论:0      收藏:0      [点我收藏+]
DECLARE

  num NUMBER;
BEGIN

	-- 新增学生表 student
	SELECT
		COUNT (1) INTO num
	FROM
		cols
	WHERE
		table_name = UPPER (‘student‘) ;
	IF num > 0 THEN
		EXECUTE IMMEDIATE ‘DROP TABLE student‘ ; 
		EXECUTE IMMEDIATE ‘CREATE TABLE student (
							id NUMBER NOT NULL,
							name VARCHAR2(40) NULL ,
							xb VARCHAR2(40) NULL ,
							age int(3) NULL ,
							birthday DATE NULL ,
							className VARCHAR2(80) NULL
							)‘ ;
		EXECUTE IMMEDIATE ‘ALTER TABLE student ADD PRIMARY KEY (id)‘ ;
	ELSE
		EXECUTE IMMEDIATE ‘CREATE TABLE student (
							id NUMBER NOT NULL,
							name VARCHAR2(40) NULL ,
							xb VARCHAR2(40) NULL ,
							age int(3) NULL ,
							birthday DATE NULL ,
							className VARCHAR2(80) NULL
							)‘ ;
		EXECUTE IMMEDIATE ‘ALTER TABLE student ADD CHECK (ID IS NOT NULL)‘ ;
		EXECUTE IMMEDIATE ‘ALTER TABLE student ADD PRIMARY KEY (ID)‘ ;
	END IF ;
	
	-- 学生表新增班级 className 字段
	SELECT COUNT(1)
		INTO num
		from cols
		where table_name = upper(‘student‘)
		 and column_name = upper(‘className‘);
		IF num > 0 THEN
	execute immediate ‘alter table student MODIFY (className varchar2(20))‘;
	ELSE
	execute immediate ‘alter table student add className varchar2(40)‘;
	END IF;
	
END;

  

Oracle 判断表或字段是否存在新增/修改表结构可重复执行sql

原文:https://www.cnblogs.com/Big-Boss/p/13255130.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!