createProcedure

The createProcedure Change Type defines the definition for a stored procedure. This Change Type 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, it is best to use the CREATE OR REPLACE syntax along with setting runOnChange to true on the enclosing changeset tag. That way if you need to make a change to your procedure, you can change your existing code rather than creating a new REPLACE PROCEDURE call. The advantage to this approach is that it keeps your changelog smaller and allows you to more easily see what has changed in your procedure code through your source control system's diff command.

Run createProcedure

To run this Change Type, follow these steps:

  1. Add the Change Type to your changeset, as shown in the examples on this page.
  2. Specify any required attributes. Use the table on this page to see which ones your database requires.
  3. Deploy your changeset by running the update command:
    4. liquibase update

Available attributes

Name Description Required for Supports
catalogName

Name of the catalog

 all
dbms

Specifies which database type(s) a changeset is to be used for. See valid database type names on dbms. Separate multiple databases with commas. Specify that a changeset is not applicable to a particular database type by prefixing with !. The keywords all and none are also available.

 all
encoding

Encoding used in the file specified in the path attribute. Default: UTF-8.

 all
path

File containing the procedure text. Either this attribute or a nested procedure text is required.

 all all
procedureText

The SQL creating the procedure. You need to define either this attribute or the path attribute. The procedureText attribute is not supported in the XML format; however, you can specify the procedure SQL in a nested format. See the XML example.

 all all
procedureName Name of the stored procedure. Required if replaceIfExists=true.
 mssql
relativeToChangelogFile

Specifies whether the file path is relative to the changelog file rather than looked up in the search path. Default: false.

 all
replaceIfExists

Boolean. If the stored procedure defined by procedureName already exists, alter it instead of creating it. Default: false.

Available in Liquibase 3.3 and later

 mssql
schemaName

Name of the schema

 all

Examples

databaseChangeLog:
-  changeSet:
    id:  createProcedure-example
    author:  liquibase-docs
    changes:
    -  createProcedure:
        catalogName:  cat
        dbms:  postgresql, !oracle, mysql
        encoding:  UTF-8
        path:  com/example/my-logic.sql
        procedureBody:  |-
          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

Note: In the example, the deprecated attribute procedureBody is used instead of procedureText as the YAML format may not recognize the procedureText attribute.

{
    "databaseChangeLog": [
        {
            "changeSet": {
                "id": "createProcedure-example",
                "author": "liquibase-docs",
                "changes": [
                    {
                        "createProcedure": {
                            "catalogName": "cat",
                            "dbms": "postgresql, !oracle, mysql",
                            "encoding": "UTF-8",
                            "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"
                        }
                    }
                ]
            }
        }
    ]
}
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xmlns:pro="http://www.liquibase.org/xml/ns/pro"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
        http://www.liquibase.org/xml/ns/dbchangelog-ext
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
        http://www.liquibase.org/xml/ns/pro
        http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">

    <changeSet author="liquibase-docs" id="createProcedure-example">
        <createProcedure catalogName="cat"
           dbms="postgresql, !oracle, mysql"
           encoding="UTF-8"
           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>

</databaseChangeLog>

Note: The procedureText attribute is not supported in the XML format.

Database support

Database Notes Auto Rollback
DB2/LUW Supported No
DB2/z Supported No
Derby Supported No
Firebird Supported No
Google BigQuery Supported No
H2 Not Supported No
HyperSQL Supported No
INGRES Supported No
Informix Supported No
MariaDB Supported No
MySQL Supported No
Oracle Supported No
PostgreSQL Supported No
Snowflake Supported No
SQL Server Supported No
SQLite Not Supported No
Sybase Supported No
Sybase Anywhere Supported No

Related links