Updated on 2025-09-22 GMT+08:00

Introduction to PL/SQL

PL/SQL provides a server-side stored procedure language, which is easy to use, secure, and portable. With this language, you can create objects to access and operate database data.

PL/SQL Subprogram

A PL/SQL subprogram is a named PL/SQL block that allows the caller to provide input-only, output-only, or input and output parameters. There are four types of subprograms: stored procedure, function, package, and trigger.

  • Creating a subprogram

    The subprogram structure is as follows:

    • (Optional) Declaration

      The declaration section includes the declaration of types, variables, constants, explicit cursors, and nested subprograms. These items are local to subprograms and do not exist after the subprograms are executed.

    • (Optional) Execution

      The execution section includes assignment statements, control execution statements, and database operation statements.

    • (Optional) Exception handling

      The exception handling section includes the code for handling exceptions.

    Adding comments to a subprogram increases the readability of the subprogram. A single-line comment starts with a double minus sign (--) and ends at the end of the line. A multi-line comment starts with /* and ends with */.

  • Executing a subprogram

    You can execute a subprogram in any of the following methods:

    • Directly call the subprogram in the application.
    • Call the subprogram in a subprogram or trigger.
    • Use the client tool.