DECLARE EXISTS_FLAG INT;
BEGIN
SELECT
COUNT( 1 ) INTO EXISTS_FLAG
FROM
USER_TABLES
WHERE
TABLE_NAME = '表名';
IF
EXISTS_FLAG = 1 THEN
EXECUTE IMMEDIATE ' DROP TABLE 表名';
END IF;
COMMIT;
END;
/
DECLARE EXISTS_FLAG INT;
BEGIN
SELECT
COUNT( 1 ) INTO EXISTS_FLAG
FROM
USER_TABLES
WHERE
TABLE_NAME = '表名_备份';
IF
EXISTS_FLAG = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE 表名_备份 AS SELECT * FROM 表名';
END IF;
COMMIT;
END;
/
存储过程判断是否新增这个字段,如果 EXISTS_FLAG = 0,则表示数据库并没有新增这个字段
DECLARE EXISTS_FLAG INT;
BEGIN
SELECT
COUNT(1) INTO EXISTS_FLAG
FROM
USER_TABLES
WHERE
TABLE_NAME = '表名' AND COLUMN_NAME = '字段名';
IF
EXISTS_FLAG = 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE 表名 ADD 字段名 VARCHAR2(46)';
END IF;
COMMIT;
END;
/
学生表:Student 字段:姓名-name,性别-sex
DECLARE EXISTS_FLAG INT;
BEGIN
SELECT
COUNT(1) INTO EXISTS_FLAG
FROM
Student
WHERE
name = '张三' AND sex = '男';
IF
EXISTS_FLAG = 0 THEN
EXECUTE IMMEDIATE 'UPDATE Student SET sex = ''男'' WHERE name = ''张三'' ';
END IF;
COMMIT;
END;
/
存储过程判断是否需要新增表
DECLARE EXISTS_FLAG INT;
BEGIN
SELECT
COUNT( 1 ) INTO EXISTS_FLAG
FROM
USER_TABLES
WHERE
TABLE_NAME = '表名';
IF
EXISTS_FLAG = 0 THEN
EXECUTE IMMEDIATE 'CREATE TABLE 表名(
ID VARCHAR2(64) NOT NULL ENABLE,
CORP_ID VARCHAR2(64),
INVOICE_NAME VARCHAR2(128),
INVOICE_TYPE VARCHAR2(64),
INVOICE_CODE VARCHAR2(64),
CONSTRAINT PK_ECS_INVOICE_MAIN PRIMARY KEY (ID) //设置主键
)';
END IF;
COMMIT;
END;
/
COMMENT ON TABLE 表名 is '表名';
COMMENT ON COLUMN 表名.ID is '主键ID';
EXECUTE IMMEDIATE 'UPDATE Student SET sex = ''男'' WHERE name = ''张三'' ';TABLE_NAME = '表名' AND COLUMN_NAME = '字段名';EXECUTE IMMEDIATE ' DROP TABLE 表名';name = '张三' AND sex = '男';学生表:Student 字段:姓名-name,性别-sex。TABLE_NAME = '表名';