如何自动执行SQL文件？

Question

从长时间的应用程序开始，我们就拥有CICD。现在，我们也计划自动执行DB脚本执行。 PFB要求。

例如，我们有create_tables.sql和alter_tables.sql文件。

create_tables.sql包含：

  CREATE TABLE EMPLOYEE_DETAILS (
  EMP_ID     VARCHAR2(128 CHAR) NOT NULL,
  FIRSTNAME    VARCHAR2(128 CHAR),
  LASTNAME    VARCHAR2(128 CHAR),
  CONSTRAINT REQUESTSUBMITTERS_PK PRIMARY KEY ( EMP_ID )
);

alter_tables.sql包含：

--v0.1
ALTER TABLE EMPLOYEE_DETAILS MODIFY FIRSTNAME VARCHAR2(256);
COMMIT;

--v0.2
ALTER TABLE EMPLOYEE_DETAILS MODIFY FIRSTNAME VARCHAR2(256) NOT NULL;
ALTER TABLE EMPLOYEE_DETAILS MODIFY LASTNAME VARCHAR2(256) NOT NULL;
COMMIT;

--v0.3
ALTER TABLE EMPLOYEE_DETAILS ADD EMAIL VARCHAR2(256) NOT NULL;
COMMIT;

假设我们有一个环境ABC。此环境具有create_tables.sql文件中可用的所有更改，但仅-v0.1从alter_tables.sql文件更改。 现在，我们只想促进 Just -v0.2从Alter_tables.sql文件更改为此环境。请注意，我们不想从Alter_tables.sql文件促进更改-v0.1和-v0.3。 因此，通常，每个SQL文件中都会有-v0.xxx（一个唯一的增量指示器），以指定要执行的SQL语句。

您能否让我知道可以自动使用这种方法的方法。 另外，请随时提出您认为比以上更好的其他解决方案。

Answer 1

首先，alter是DDL，它在执行该命令之前和之后隐含地提交，因此 - 明确commit ting几乎是没有用的。

---

从我的角度来看，您的方法太难应用了，因为您将所有的更改存储在同一文件中，因此您必须对其进行解析，并仅提取一些（所需的部分）要执行的部分。

如何创建一组表包含什么应用？以下是一个示例：

SQL> create table t_change_master
  2    (change       number constraint pk_cha primary key,
  3     change_date  date,
  4     cb_apply     number(1) default 0 not null,
  5     applied_date date
  6    );

Table created.

SQL> create table t_change_detail
  2    (id           number constraint pk_chadet primary key,
  3     change       number constraint fk_chadet_cha references t_change_master (change),
  4     command      varchar2(200),
  5     cb_ok        number(1),
  6     error        varchar2(200)
  7    );

Table created.

一些示例数据：

SQL> insert into t_change_master (change, change_date, cb_apply)
  2    select 1, date '2022-06-25', 1 from dual union all
  3    select 2, date '2022-06-26', 0 from dual union all
  4    select 3, date '2022-06-28', 0 from dual;

3 rows created.

请注意，ID = 1和4包含重复命令：

SQL> insert into t_change_detail (id, change, command)
  2    select 1, 1, 'alter table test add firstname varchar2(20)' from dual union all
  3    select 2, 1, 'alter table test modify firstname varchar2(15)' from dual union all
  4    select 3, 2, 'alter table test add lastname varchar2(20)' from dual union all
  5    select 4, 2, 'alter table test add firstname varchar2(30)' from dual union all
  6    select 5, 3, 'alter table test add address varchar2(30)' from dual;

5 rows created.

SQL>

这是将在此操场上使用的表：

SQL> create table test (id number);

Table created.

完成工作的过程；在两个嵌套循环中，它读取计划运行的 master 表（cb_apply = 1），但尚未运行（applied_date为null <<） /代码>）。内部循环只是读取该怎么做，执行它并记录是否成功。

基本上，您使用 schedule 使用dbms_scheduler（或dbms_job，取决于您的数据库版本）：

SQL> create or replace procedure p_change is
  2    l_err varchar2(200);
  3  begin
  4    for cur_m in (select change
  5                  from t_change_master
  6                  where cb_apply = 1
  7                    and applied_date is null
  8                 )
  9    loop
 10      for cur_d in (select id, command
 11                    from t_change_detail
 12                    where change = cur_m.change
 13                   )
 14      loop
 15        begin
 16          dbms_output.put_line(cur_d.command);
 17          execute immediate cur_d.command;
 18
 19          update t_change_detail set
 20            cb_ok = 1
 21            where id = cur_d.id;
 22        exception
 23          when others then
 24            l_err := sqlerrm;
 25            update t_change_detail set
 26              cb_ok = 0,
 27              error = l_err
 28              where id = cur_d.id;
 29        end;
 30      end loop;
 31      update t_change_master set
 32        applied_date = sysdate
 33        where change = cur_m.change;
 34    end loop;
 35  end;
 36  /

Procedure created.

SQL>

好的，让我们尝试一下。设置日期格式，只是要知道什么是：

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> set serveroutput on;
SQL> begin
  2    p_change;
  3  end;
  4  /
alter table test add firstname varchar2(20)
alter table test modify firstname varchar2(15)

PL/SQL procedure successfully completed.

SQL> select * from t_change_master;

 CHANGE CHANGE_DATE           CB_APPLY APPLIED_DATE
------- ------------------- ---------- -------------------
      1 25.06.2022 00:00:00          1 05.07.2022 17:50:01
      2 26.06.2022 00:00:00          0
      3 28.06.2022 00:00:00          0

SQL> select * from t_change_detail;

 ID  CHANGE COMMAND                                         CB_OK ERROR
--- ------- ---------------------------------------------- ------ ----------------------------------------
  1       1 alter table test add firstname varchar2(20)         1
  2       1 alter table test modify firstname varchar2(15)      1
  3       2 alter table test add lastname varchar2(20)
  4       2 alter table test add firstname varchar2(30)
  5       3 alter table test add address varchar2(30)

SQL>

现在，让我们运行change = 2：

SQL> update t_change_master set cb_apply = 1 where change = 2;

1 row updated.

SQL> begin
  2    p_change;
  3  end;
  4  /
alter table test add lastname varchar2(20)
alter table test add firstname varchar2(30)

PL/SQL procedure successfully completed.

SQL> select * from t_change_master;

 CHANGE CHANGE_DATE           CB_APPLY APPLIED_DATE
------- ------------------- ---------- -------------------
      1 25.06.2022 00:00:00          1 05.07.2022 17:50:01
      2 26.06.2022 00:00:00          1 05.07.2022 17:50:58
      3 28.06.2022 00:00:00          0

SQL> select * from t_change_detail;

 ID  CHANGE COMMAND                                         CB_OK ERROR
--- ------- ---------------------------------------------- ------ ----------------------------------------
  1       1 alter table test add firstname varchar2(20)         1
  2       1 alter table test modify firstname varchar2(15)      1
  3       2 alter table test add lastname varchar2(20)          1
  4       2 alter table test add firstname varchar2(30)         0 ORA-01430: column being added already
                                                                  exists in table

  5       3 alter table test add address varchar2(30)

SQL>

right;这是一种作品。

当然，应该/应该/应该改进那件代码（我在几分钟之内写），但这只是一般的想法。

---

另一方面，为什么不使用版本控制系统（例如git或subversion）这样做？好人开发了这些工具，这些工具强大得多，以至于我（或您）在这么短的时间内都可以“发明”任何工具。我想检查这些产品是值得的。