Overview

This topic describes the DDL operations for PL units, including create, alter, and drop operations.

CREATE [OR REPLACE]

The CREATE [OR REPLACE] statement creates PL units.

The following SQL statements can be used: CREATE FUNCTION、 CREATE PACKAGE、 CREATE PACKAGE BODY 、CREATE PROCEDURE、CREATE TRIGGER 、 CREATE TYPE and CREATE TYPE BODY.

Each CREATE statement can have an optional OR REPLACE clause. If you specify OR REPLACE, the PL unit can be re-created based on the existing PL unit, without deleting it. The re-created PL unit will retain the original privileges granted to the original PL unit. If you redefine the PL unit, the database recompiles it.

ALTER

The ALTER statement recompiles the PL unit without recreating it (without modifying its declaration or definition).

The following SQL statements can be used: ALTER FUNCTION、 ALTER PACKAGE、ALTER PROCEDURE、ALTER TRIGGER and ALTER TYPE.

You can run the ALTER statement for the following reasons:

• To explicitly recompile invalidated stored units and avoid implicit recompilation during runtime, which could lead to runtime compilation errors and performance overhead.

• To recompile stored units with different compilation parameters.

• To enable or disable triggers.

DROP

The DROP statement deletes existing PL units.

The following SQL statements can be used: DROP FUNCTION、 DROP PACKAGE、DROP PROCEDURE、DROP TRIGGER 、 DROP TYPE and DROP TYPE BODY.