本文主要是介绍Liquibase（Oracle SQLcl集成版）简明示例，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
本文使用的是Oracle SQLcl中集成的Liquibase，而非开源版Liquibase。
Liquibase的快速入门可以参见Liquibase Core Concepts。需要了解一下概念：
- Change log：基于文本的更改日志文件按顺序列出对数据库所做的所有更改
- Change set：变更日志中的单个变更
- Tracking tables
- DATABASECHANGELOG ：跟踪已运行的变更集
- DATABASECHANGELOGLOCK ：确保一次只运行一个 Liquibase 实例
- DATABASECHANGELOGHISTORY ：记录对数据库所做的所有更改的历史记录
下面是典型的Liquibase工作流程，图来自这里：
Liquibase的文档参见这里。
下面来看几个例子，使用的Schema是Oracle官方的Sample schema: HR。部署的目标Schema是HR2，位于同一个数据库中。
这几个例子，也可以从帮助中看到：
SQL> help lb examplesExample: Review SQL.
--------------------To review SQL before running maintenance commands:-- Optionally setup to save sqlcd <lb-changes-directory>spool update.sql-- Connect to HR and capture the object.connect <db-connect1-string>lb update-sqlspool offExample: Capture and Deploy an Object.
--------------------------------------To deploy the EMPLOYEES table from HR to HR2:-- Set default output path.cd <output-files-path>-- Connect to HR and capture the object.connect <db-connect1-string>lb generate-object -object-type table -object-name employees-- Connect to HR2 and ensure the object does not exist.connect <db-connect2-string>drop table employees-- Create the object in HR2 and verify that it was created.lb update -changelog-file employees_table.xmldesc employeesExample: Capture and Deploy a Schema.
--------------------------------------To capture HR schema and reproduce it in HR2 schema:-- Set default output path.cd <output-files-path>-- Connect to HR and capture the schema.connect <db-connect1-string>lb generate-schema-- Setup the HR2 user.connect <db-connect-dba-string>drop user hr2 cascade;create user hr2 identified by hr2;grant connect,resource, create view to hr2;alter user hr2 quota unlimited on users;alter user hr2 quota unlimited on sysaux;-- Create the schema objects deployed from HR in HR2 and verify.lb update -changelog-file controller.xmltablesExample: Execute Custom SQL with RunOracleScript.
-------------------------------------------------Create a RunOracleScript changeset to create a table and use PL/SQL variables in the script.See Oracle SQLcl User's Guide for examples using files and urls.
#### SCRIPT - STRING EXAMPLE ####
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLogxmlns="http://www.liquibase.org/xml/ns/dbchangelog"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.6.xsd"><changeSet id="runScriptString" author="jdoe"><n0:runOracleScript objectName="myScript" ownerName="JDOE" sourceType="STRING"><n0:source><![CDATA[DEFINE table-name = RUNNERSTRING;create table &&table_name (id number);]]></n0:source></n0:runOracleScript></changeSet>
</databaseChangeLog>See additional examples and details in Oracle SQLcl User's Guide, Examples Using Liquibase.捕获并部署整个Schema
连接到 HR
SQL> connect hr@orclpdb1
Password? (**********?) ********
Connected.
为整个HR schema生成Change log：
SQL> pwd
/home/oracle/lb/
SQL> lb generate-schema
--Starting Liquibase at 2024-06-14T09:07:40.040925 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)Export Flags Used:Export Grants false
Export Synonyms false[Method loadCaptureTable]:[Type - TYPE_SPEC]: 544 ms[Type - TYPE_BODY]: 165 ms[Type - SEQUENCE]: 386 ms[Type - DIRECTORY]: 53 ms[Type - CLUSTER]: 3515 ms[Type - TABLE]: 36869 ms[Type - MATERIALIZED_VIEW_LOG]: 58 ms[Type - MATERIALIZED_VIEW]: 40 ms[Type - VIEW]: 2822 ms[Type - REF_CONSTRAINT]: 414 ms[Type - DIMENSION]: 58 ms[Type - PACKAGE_SPEC]: 94 ms[Type - FUNCTION]: 97 ms[Type - PROCEDURE]: 179 ms[Type - DB_LINK]: 48 ms[Type - SYNONYM]: 57 ms[Type - INDEX]: 2494 ms[Type - TRIGGER]: 390 ms[Type - PACKAGE_BODY]: 127 ms[Type - JOB]: 70 ms[Method loadCaptureTable]: 48481 ms
[Method sortCaptureTable]: 21 ms
[Method writeChangeLogs]: 192 ms
Changelog created and written out to file controller.xmlOperation completed successfully.
可以看到在目录下生成的文件：
[oracle@oracle-19c-vagrant lb]$ ls -1l
total 208
-rw-r--r--. 1 oracle oinstall 1283 Jun 14 09:08 add_job_history_procedure.xml
-rw-r--r--. 1 oracle oinstall 2570 Jun 14 09:08 controller.xml
-rw-r--r--. 1 oracle oinstall 1133 Jun 14 09:08 countries_comment.xml
-rw-r--r--. 1 oracle oinstall 3032 Jun 14 09:08 countries_table.xml
-rw-r--r--. 1 oracle oinstall 899 Jun 14 09:08 countr_reg_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1073 Jun 14 09:08 dbtools$execution_history_seq_sequence.xml
-rw-r--r--. 1 oracle oinstall 5047 Jun 14 09:08 dbtools$execution_history_table.xml
-rw-r--r--. 1 oracle oinstall 1634 Jun 14 09:08 departments_comment.xml
-rw-r--r--. 1 oracle oinstall 1023 Jun 14 09:08 departments_seq_sequence.xml
-rw-r--r--. 1 oracle oinstall 4203 Jun 14 09:08 departments_table.xml
-rw-r--r--. 1 oracle oinstall 1811 Jun 14 09:08 dept_location_ix_index.xml
-rw-r--r--. 1 oracle oinstall 903 Jun 14 09:08 dept_loc_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 902 Jun 14 09:08 dept_mgr_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1813 Jun 14 09:08 emp_department_ix_index.xml
-rw-r--r--. 1 oracle oinstall 907 Jun 14 09:08 emp_dept_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 2818 Jun 14 09:08 emp_details_view_view.xml
-rw-r--r--. 1 oracle oinstall 1816 Jun 14 09:08 emp_email_uk_index.xml
-rw-r--r--. 1 oracle oinstall 884 Jun 14 09:08 emp_job_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1792 Jun 14 09:08 emp_job_ix_index.xml
-rw-r--r--. 1 oracle oinstall 2327 Jun 14 09:08 employees_comment.xml
-rw-r--r--. 1 oracle oinstall 1042 Jun 14 09:08 employees_seq_sequence.xml
-rw-r--r--. 1 oracle oinstall 7318 Jun 14 09:08 employees_table.xml
-rw-r--r--. 1 oracle oinstall 906 Jun 14 09:08 emp_manager_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1804 Jun 14 09:08 emp_manager_ix_index.xml
-rw-r--r--. 1 oracle oinstall 1884 Jun 14 09:08 emp_name_ix_index.xml
-rw-r--r--. 1 oracle oinstall 1819 Jun 14 09:08 jhist_department_ix_index.xml
-rw-r--r--. 1 oracle oinstall 913 Jun 14 09:08 jhist_dept_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 905 Jun 14 09:08 jhist_emp_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1813 Jun 14 09:08 jhist_employee_ix_index.xml
-rw-r--r--. 1 oracle oinstall 890 Jun 14 09:08 jhist_job_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1798 Jun 14 09:08 jhist_job_ix_index.xml
-rw-r--r--. 1 oracle oinstall 2099 Jun 14 09:08 job_history_comment.xml
-rw-r--r--. 1 oracle oinstall 4886 Jun 14 09:08 job_history_table.xml
-rw-r--r--. 1 oracle oinstall 1222 Jun 14 09:08 jobs_comment.xml
-rw-r--r--. 1 oracle oinstall 4191 Jun 14 09:08 jobs_table.xml
-rw-r--r--. 1 oracle oinstall 1903 Jun 14 09:08 locations_comment.xml
-rw-r--r--. 1 oracle oinstall 1021 Jun 14 09:08 locations_seq_sequence.xml
-rw-r--r--. 1 oracle oinstall 4667 Jun 14 09:08 locations_table.xml
-rw-r--r--. 1 oracle oinstall 899 Jun 14 09:08 loc_c_id_fk_ref_constraint.xml
-rw-r--r--. 1 oracle oinstall 1792 Jun 14 09:08 loc_city_ix_index.xml
-rw-r--r--. 1 oracle oinstall 1804 Jun 14 09:08 loc_country_ix_index.xml
-rw-r--r--. 1 oracle oinstall 1822 Jun 14 09:08 loc_state_province_ix_index.xml
-rw-r--r--. 1 oracle oinstall 3729 Jun 14 09:08 regions_table.xml
-rw-r--r--. 1 oracle oinstall 1050 Jun 14 09:08 secure_dml_procedure.xml
-rw-r--r--. 1 oracle oinstall 894 Jun 14 09:08 secure_employees_trigger.xml
-rw-r--r--. 1 oracle oinstall 999 Jun 14 09:08 update_job_history_trigger.xml
控制文件controller.xml，即Change log：
[oracle@oracle-19c-vagrant lb]$ cat controller.xml
<?xml version="1.0" encoding="UTF-8"?>
<!---->
<databaseChangeLogxmlns="http://www.liquibase.org/xml/ns/dbchangelog"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangeloghttp://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd"><include file="dbtools$execution_history_seq_sequence.xml"/><include file="departments_seq_sequence.xml"/><include file="employees_seq_sequence.xml"/><include file="locations_seq_sequence.xml"/><include file="data_file_dir_directory.xml"/><include file="log_file_dir_directory.xml"/><include file="media_dir_directory.xml"/><include file="ss_oe_xmldir_directory.xml"/><include file="subdir_directory.xml"/><include file="countries_table.xml"/><include file="departments_table.xml"/><include file="employees_table.xml"/><include file="jobs_table.xml"/><include file="job_history_table.xml"/><include file="locations_table.xml"/><include file="regions_table.xml"/><include file="emp_details_view_view.xml"/><include file="countr_reg_fk_ref_constraint.xml"/><include file="dept_loc_fk_ref_constraint.xml"/><include file="dept_mgr_fk_ref_constraint.xml"/><include file="emp_dept_fk_ref_constraint.xml"/><include file="emp_job_fk_ref_constraint.xml"/><include file="emp_manager_fk_ref_constraint.xml"/><include file="jhist_dept_fk_ref_constraint.xml"/><include file="jhist_emp_fk_ref_constraint.xml"/><include file="jhist_job_fk_ref_constraint.xml"/><include file="loc_c_id_fk_ref_constraint.xml"/><include file="add_job_history_procedure.xml"/><include file="secure_dml_procedure.xml"/><include file="dept_location_ix_index.xml"/><include file="emp_department_ix_index.xml"/><include file="emp_email_uk_index.xml"/><include file="emp_job_ix_index.xml"/><include file="emp_manager_ix_index.xml"/><include file="emp_name_ix_index.xml"/><include file="jhist_department_ix_index.xml"/><include file="jhist_employee_ix_index.xml"/><include file="jhist_job_ix_index.xml"/><include file="loc_city_ix_index.xml"/><include file="loc_country_ix_index.xml"/><include file="loc_state_province_ix_index.xml"/><include file="secure_employees_trigger.xml"/><include file="update_job_history_trigger.xml"/><include file="countries_comment.xml"/><include file="departments_comment.xml"/><include file="employees_comment.xml"/><include file="jobs_comment.xml"/><include file="job_history_comment.xml"/><include file="locations_comment.xml"/>
</databaseChangeLog>
创建另一个用户hr2：
connect sys@orclpdb1 as sysdba
drop user hr2 cascade;
create user hr2 identified by hr2;
grant connect,resource, create view to hr2;
grant create any directory, drop any directory to hr2;
alter user hr2 quota unlimited on users;
alter user hr2 quota unlimited on sysaux;
以hr2登录，然后部署Change log：
SQL> connect hr2@orclpdb1
Connected.
SQL> pwd
/home/oracle/lb/
SQL> show user
USER is "HR2"
SQL> lb update -changelog-file controller.xml
--Starting Liquibase at 2024-06-14T09:11:41.583359 (version 4.25.0 #3966 built at 2023-11-10 23:07:29 UTC)
Running Changeset: dbtools$execution_history_seq_sequence.xml::814db839d56f18388eba43012512b4c29af368d4::(HR)-Generated
Sequence "DBTOOLS$EXECUTION_HISTORY_SEQ" created.
Running Changeset: departments_seq_sequence.xml::c2559d5eb079f42af8fc57b00c9812e6c8c2d2e0::(HR)-Generated
Sequence "DEPARTMENTS_SEQ" created.
Running Changeset: employees_seq_sequence.xml::3a376736162b5480bc023782459b2938821546fe::(HR)-Generated
Sequence "EMPLOYEES_SEQ" created.
Running Changeset: locations_seq_sequence.xml::d42e90d6488ef61a66ab85db1f851adae60bc8dd::(HR)-Generated
Sequence "LOCATIONS_SEQ" created.
Running Changeset: countries_table.xml::3b240552bee3c11f1818587a788cda621b6518ab::(HR)-Generated
Table "COUNTRIES" created.
Running Changeset: dbtools$execution_history_table.xml::e66e9a73fcd5013144987ac69ba38f689227e5ae::(HR)-Generated
Table "DBTOOLS$EXECUTION_HISTORY" created.
Running Changeset: departments_table.xml::93dac2aceee230b182ade406fcb7b69ca9643a30::(HR)-Generated
Table "DEPARTMENTS" created.
Running Changeset: employees_table.xml::19cbcaa1f90c2b4b6503af1aa3a6c8841ece9b8b::(HR)-Generated
Table "EMPLOYEES" created.
Running Changeset: jobs_table.xml::2b683f16417957d9c74e454cbb547b905a5718d8::(HR)-Generated
Table "JOBS" created.
Running Changeset: job_history_table.xml::841721d5dd15086e36463a952e618e3bb9403700::(HR)-Generated
Table "JOB_HISTORY" created.
Running Changeset: locations_table.xml::d947b88fe783235b92608b81d588d2a7202b90b8::(HR)-Generated
Table "LOCATIONS" created.
Running Changeset: regions_table.xml::6544c454a28fe6c03a8cde376a08b590875e0bd1::(HR)-Generated
Table "REGIONS" created.
Running Changeset: emp_details_view_view.xml::c233b8bd8e55fbce0db20919556c600ba65e5849::(HR)-Generated
View "EMP_DETAILS_VIEW" created.
Running Changeset: countr_reg_fk_ref_constraint.xml::c0048786c0431718640f15fcc3b3bb092ed5b93e::(HR)-Generated
Table "COUNTRIES" altered.
Running Changeset: dept_loc_fk_ref_constraint.xml::c42fcba72f585a4bb59168d9165c99c2e2924dd8::(HR)-Generated
Table "DEPARTMENTS" altered.
Running Changeset: dept_mgr_fk_ref_constraint.xml::cf00968893cf4776ad3dbd1768c61a91b0745caa::(HR)-Generated
Table "DEPARTMENTS" altered.
Running Changeset: emp_dept_fk_ref_constraint.xml::51b234beb2e02aaa07df633a6cd7a0b635e6759d::(HR)-Generated
Table "EMPLOYEES" altered.
Running Changeset: emp_job_fk_ref_constraint.xml::f1b1bf677cf990caa0147f863ea18486cdf23815::(HR)-Generated
Table "EMPLOYEES" altered.
Running Changeset: emp_manager_fk_ref_constraint.xml::814e7d7ac4b4c64539619ab0cd3831258cf332e3::(HR)-Generated
Table "EMPLOYEES" altered.
Running Changeset: jhist_dept_fk_ref_constraint.xml::b3caf52e71d5935a8ef7582648f411545153719f::(HR)-Generated
Table "JOB_HISTORY" altered.
Running Changeset: jhist_emp_fk_ref_constraint.xml::03b746867c321c670ead34253ecf27589a19e61a::(HR)-Generated
Table "JOB_HISTORY" altered.
Running Changeset: jhist_job_fk_ref_constraint.xml::326c90769136eea29c73d734b3885efba66454d3::(HR)-Generated
Table "JOB_HISTORY" altered.
Running Changeset: loc_c_id_fk_ref_constraint.xml::d1f8fa1c0b75582a1608d1da0fcea148c6586cc8::(HR)-Generated
Table "LOCATIONS" altered.
Running Changeset: add_job_history_procedure.xml::9093001d3dfcc15939a63e7a92538c6a6639d13e::(HR)-Generated
Procedure ADD_JOB_HISTORY compiled
Running Changeset: secure_dml_procedure.xml::8ae0ea62a872b93d0519fba9556af9d749de6aaf::(HR)-Generated
Procedure SECURE_DML compiled
Running Changeset: dept_location_ix_index.xml::e7c4b4679b0d80ff3e6605a7515b290c50667b9e::(HR)-Generated
Index "DEPT_LOCATION_IX" created.
Running Changeset: emp_department_ix_index.xml::573196c006c4e9499cff9445eddeb54cc7485dde::(HR)-Generated
Index "EMP_DEPARTMENT_IX" created.
Running Changeset: emp_email_uk_index.xml::d25fa5625174785655c8598dc36f2e853794e29e::(HR)-Generated
Statement executed successfully
Running Changeset: emp_job_ix_index.xml::cc00bac7181ed6105f30cb576ecb9f393929cbec::(HR)-Generated
Index "EMP_JOB_IX" created.
Running Changeset: emp_manager_ix_index.xml::ffdb74dd53e9811e7041a46a2aae8b181846b93f::(HR)-Generated
Index "EMP_MANAGER_IX" created.
Running Changeset: emp_name_ix_index.xml::05522ad046b07feb94a87c9e163bec1ebf169d34::(HR)-Generated
Index "EMP_NAME_IX" created.
Running Changeset: jhist_department_ix_index.xml::9017439312d03d5aea5fbb80b3a92065a42691e2::(HR)-Generated
Index "JHIST_DEPARTMENT_IX" created.
Running Changeset: jhist_employee_ix_index.xml::61ab9d9ae857d008a542d7115eee314347b5bc9c::(HR)-Generated
Index "JHIST_EMPLOYEE_IX" created.
Running Changeset: jhist_job_ix_index.xml::4d619ba4fc8851943d45d0ca45bc70d366d066eb::(HR)-Generated
Index "JHIST_JOB_IX" created.
Running Changeset: loc_city_ix_index.xml::f3bc4d402cbc571efce7d3bdbe3c349f3b196abc::(HR)-Generated
Index "LOC_CITY_IX" created.
Running Changeset: loc_country_ix_index.xml::155bbc5ff67237cbb1df9a7573b336a1199ab5ea::(HR)-Generated
Index "LOC_COUNTRY_IX" created.
Running Changeset: loc_state_province_ix_index.xml::8920e05d5b78961426f6f34ed459c15ea1c03245::(HR)-Generated
Index "LOC_STATE_PROVINCE_IX" created.
Running Changeset: secure_employees_trigger.xml::57907483a823d299399df6f738ce7d16cc68a4a8::(HR)-Generated
Trigger SECURE_EMPLOYEES compiled
Running Changeset: update_job_history_trigger.xml::cf43ba994dc6cafc54d74db26a89250d9e0ae9bb::(HR)-Generated
Trigger UPDATE_JOB_HISTORY compiled
Running Changeset: countries_comment.xml::6f4ef85df7284635afaec34851709f369cd1c9c5::(HR)-Generated
Comment created.Comment created.Comment created.Comment created.
Running Changeset: departments_comment.xml::c6d474a20a01dd0163a25528a30e8eb4ccce33dc::(HR)-Generated
Comment created.Comment created.Comment created.Comment created.Comment created.
Running Changeset: employees_comment.xml::c1d0d2d54cec45336ffa66cda58f117411d49b93::(HR)-Generated
Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.
Running Changeset: jobs_comment.xml::2e08c5b3c0348b1bd28f22742f09a1d8bc6c496e::(HR)-Generated
Comment created.Comment created.Comment created.Comment created.Comment created.
Running Changeset: job_history_comment.xml::24e849b5ecbe23f580568579ba5c85a5ca5f305e::(HR)-Generated
Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.
Running Changeset: locations_comment.xml::10610e892c98d4f6d3484894f38587ab22684503::(HR)-Generated
Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.Comment created.UPDATE SUMMARY
Run: 45
Previously run: 0
Filtered out: 0
-------------------------------
Total change sets: 45Liquibase: Update has been successful. Rows affected: 45Operation completed successfully.然后在hr2中就可以看到表和其他对象了：
SQL> tablesTABLES
____________________________
DATABASECHANGELOG_ACTIONS
DATABASECHANGELOG
DATABASECHANGELOGLOCK
COUNTRIES
DBTOOLS$EXECUTION_HISTORY
DEPARTMENTS
EMPLOYEES
JOBS
JOB_HISTORY
LOCATIONS
REGIONS11 rows selected.
以DATABASECHANGELOG开头的表是Liquidbase建立的内部表。
SQL> desc DATABASECHANGELOG_DETAILSName Null? Type
________________ ___________ ________________
DEPLOYMENT_ID VARCHAR2(10)
ID VARCHAR2(255)
AUTHOR VARCHAR2(255)
FILENAME VARCHAR2(255)
SQL CLOB
SXML CLOB
DATEEXECUTED NOT NULL TIMESTAMP(6)
EXECTYPE NOT NULL VARCHAR2(10)
MD5SUM VARCHAR2(35)
DESCRIPTION VARCHAR2(255)
COMMENTS VARCHAR2(255)
LIQUIBASE VARCHAR2(20)
CONTEXTS VARCHAR2(255)
LABELS VARCHAR2(255)
还有很重要的一点，Liquibase只跟踪schema变化，而非数据变化：
SQL> select count(*) from employees;COUNT(*)
___________0
捕获并部署单个对象
捕获的工作在上一个例子中已经做过了，例如employees表：
$ cat employees_table.xml
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLogxmlns="http://www.liquibase.org/xml/ns/dbchangelog"xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext"xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangeloghttp://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd"><changeSet id="19cbcaa1f90c2b4b6503af1aa3a6c8841ece9b8b" author="(HR)-Generated" failOnError="false" runOnChange="false" runAlways="false" ><n0:createSxmlObject objectName="EMPLOYEES" objectType="TABLE" ownerName="HR" replaceIfExists="false" ><n0:source><
