ORACLE操作列

--学生表  STUDENT
CREATE TABLE STUDENT(
      ID NUMBER(18) NOT NULL,
      NAME VARCHAR2(20),
      NO  VARCHAR2(12),
      AGE NUMBER(3)
);

-- 添加学生表 CLASS学生所在的班级字段
DECLARE
  AN_FLAG NUMBER DEFAULT 0;
BEGIN
  SELECT COUNT(1)
  INTO AN_FLAG
   FROM USER_TAB_COLUMNS
   WHERE TABLE_NAME=‘STUDENT‘
   AND COLUMN_NAME=‘CLASS‘;
  IF AN_FLAG = 0 THEN
    EXECUTE IMMEDIATE ‘ALTER TABLE STUDENT ADD CLASS VARCHAR2(30)‘;
    EXECUTE IMMEDIATE ‘COMMENT ON COLUMN STUDENT.CLASS IS ‘‘学生所在的班级‘‘‘;
  END IF;
END;
/

-- 修改学生表 AGE字段的类型
DECLARE
  AN_FLAG NUMBER DEFAULT 0;
BEGIN
  SELECT COUNT(1)
  INTO AN_FLAG
   FROM USER_TAB_COLUMNS
   WHERE TABLE_NAME=‘STUDENT‘
   AND COLUMN_NAME=‘AGE‘;
  IF AN_FLAG = 1 THEN
    EXECUTE IMMEDIATE ‘ALTER TABLE STUDENT MODIFY AGE CHAR(3)‘;
  END IF;
END;
/

-- 修改学生表 AGE字段的名称
DECLARE
  AN_FLAG NUMBER DEFAULT 0;
BEGIN
  SELECT COUNT(1)
  INTO AN_FLAG
   FROM USER_TAB_COLUMNS
   WHERE TABLE_NAME=‘STUDENT‘
   AND COLUMN_NAME=‘AGE‘;
  IF AN_FLAG = 1 THEN
    EXECUTE IMMEDIATE ‘ALTER TABLE STUDENT RENAME COLUMN AGE TO AGE_TO‘;
  END IF;
END;
/　

-- 修改学生表 删除AGE字段
DECLARE
  AN_FLAG NUMBER DEFAULT 0;
BEGIN
  SELECT COUNT(1)
  INTO AN_FLAG
   FROM USER_TAB_COLUMNS
   WHERE TABLE_NAME=‘STUDENT‘
   AND COLUMN_NAME=‘AGE‘;
  IF AN_FLAG = 1 THEN
    EXECUTE IMMEDIATE ‘ALTER TABLE STUDENT DROP COLUMN AGE‘;
  END IF;
END;
/