Change: ‘createProcedure’
Defines the definition for a stored procedure. This command is better to use for creating procedures than the raw sql command because it will not attempt to strip comments or break up lines.
Often times it is best to use the CREATE OR REPLACE syntax along with setting runOnChange=’true’ on the enclosing changeSet tag. That way if you need to make a change to your procedure you can simply change your existing code rather than creating a new REPLACE PROCEDURE call. The advantage to this approach is that it keeps your change log smaller and allows you to more easily see what has changed in your procedure code through your source control system’s diff command.
Available Attributes
| Name | Description |
|---|---|
| catalogName | Name of the catalogE.g. 'cat' |
| dbms | Logical expression of database type(s) on which the change must be applied. Valid database type names are listed on the supported databases page
It can be a comma separated list of multiple databases.
Or You can also specify that a change is NOT applicable to a particular database type by prefixing with !. The keywords all and none are also available.@ v3.1E.g. 'h2, !oracle, mysql' |
| encoding | Name of the encoding (as specified in java.nio.Charset javadoc) used in the file defined in the `path` attributeDefault: 'utf-8' |
| path | File containing the procedure text. Either this attribute or a nested procedure text is required.E.g. 'com/example/my-logic.sql' |
| [XML: text content] / procedureText | The SQL creating the procedure. Either this or the path attribute needs to be defined.E.g. 'CREATE OR REPLACE PROCEDURE testHello
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello From The Database!');
END;'Note: the content of the tag in XML |
| procedureName | Name of the procedure. Required if replaceIfExists = trueE.g. 'new_customer'Supported by: mssql |
| relativeToChangelogFile | booleanWhether the file path is relative to the root changelog file rather than to the classpath. |
| replaceIfExists | booleanIf the stored procedure defined by procedureName already exits alter it instead of creating it.Supported by: mssql |
| schemaName | Name of the schemaE.g. 'public' |
<changeSet author="liquibase-docs" id="createProcedure-example">
<createProcedure catalogName="cat"
dbms="h2, !oracle, mysql"
encoding="utf8"
path="com/example/my-logic.sql"
procedureName="new_customer"
relativeToChangelogFile="true"
replaceIfExists="false"
schemaName="public">CREATE OR REPLACE PROCEDURE testHello
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello From The Database!');
END;</createProcedure>
</changeSet>changeSet:
id: createProcedure-example
author: liquibase-docs
changes:
- createProcedure:
catalogName: cat
dbms: h2, !oracle, mysql
encoding: utf8
path: com/example/my-logic.sql
procedureText: |-
CREATE OR REPLACE PROCEDURE testHello
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello From The Database!');
END;
procedureName: new_customer
relativeToChangelogFile: true
replaceIfExists: false
schemaName: public{
"changeSet": {
"id": "createProcedure-example",
"author": "liquibase-docs",
"changes": [
{
"createProcedure": {
"catalogName": "cat",
"dbms": "h2, !oracle, mysql",
"encoding": "utf8",
"path": "com/example/my-logic.sql",
"procedureText": "CREATE OR REPLACE PROCEDURE testHello\n IS\n BEGIN\n DBMS_OUTPUT.PUT_LINE('Hello From The Database!');\n END;",
"procedureName": "new_customer",
"relativeToChangelogFile": true,
"replaceIfExists": false,
"schemaName": "public"
}
}]
}
}Database Support
| Database | Notes | Auto Rollback |
|---|---|---|
| DB2/LUW | Supported | No |
| DB2/z | Supported | No |
| Derby | Supported | No |
| Firebird | Supported | No |
| H2 | Supported | No |
| HyperSQL | Supported | No |
| INGRES | Supported | No |
| Informix | Supported | No |
| MariaDB | Supported | No |
| MySQL | Supported | No |
| Oracle | Supported | No |
| PostgreSQL | Supported | No |
| SQL Server | Supported | No |
| SQLite | Supported | No |
| Sybase | Supported | No |
| Sybase Anywhere | Supported | No |