Exploring PL/SQL Subprograms: Creating and Calling Procedures in Oracle

In this article, we will delve into PL/SQL subprograms, specifically focusing on procedures in Oracle. We’ll cover the structure of Oracle procedures, their syntax, parameter modes, and demonstrate a simple procedure to retrieve a name from an example table. Let’s get started!

Understanding Subprograms in PL/SQL

Subprograms are named PL/SQL blocks that can be invoked by passing a set of parameters. There are two types of subprograms in PL/SQL: procedures and functions. In this article, we will primarily focus on procedures.

Structure of Oracle Procedures (Anonymous Block)

An anonymous block is a type of procedure in PL/SQL that doesn’t have a specific name. It follows the following structure:

sql

DECLARE         — Optional: Variable Block

BEGIN             — Mandatory: Executable Statements / Queries

    — Executable Statements / Queries

EXCEPTION    — Optional: Exception Handling

END;                — Mandatory

/

Syntax of Oracle Procedures

A named block is a procedure in PL/SQL that has a specific name. The syntax for creating a named procedure is as follows:

sql

CREATE [OR REPLACE] PROCEDURE procedure_name

[(parameter1 [mode1] datatype1,

 parameter2 [mode2] datatype2,

 …)]

IS|AS

PL/SQL Block;

Understanding Parameter Modes

When defining parameters for procedures, we can specify different modes to control their behavior. The available modes are:

IN: The procedure must be called with a value for the parameter, and the value cannot be changed within the procedure.

OUT: The procedure must be called with a variable for the parameter. Changes made to the parameter within the procedure are visible to the calling program (call by reference).

IN OUT: The value can be sent to the procedure, and changes made to the parameter within the procedure are visible to the calling program.

By default, if no mode is specified, the parameter is treated as IN.

Sample Scenario: Working with the Example Table

For the purpose of demonstration, let’s consider a table named “Example” with the following structure:

sql

CREATE TABLE Example (

  NAME VARCHAR2(10),

  NUM NUMBER(3)

);

The table contains the following data:

bash

NAME       NUM                    

---------- ----------------------

NAMEONE    1                      

NAMETWO    2                      

NAMETHREE  3                      

NAMEFOUR   4                      

NAMEFIVE   5                      

NAMESIX    6                      

NAMESEVEN  7                      

test       -100                   

test       -200                   

test       -300                   

test       -500     

Creating a Simple Procedure to Retrieve a Name

Let’s create a procedure called “p_getname” that takes an input parameter “v_num” and an output parameter “v_name”. This procedure retrieves the name from the “Example” table based on the provided number.

sql

CREATE OR REPLACE PROCEDURE p_getname

(v_num IN example.num%TYPE,

 v_name OUT example.name%TYPE)

IS

BEGIN

    SELECT name

    INTO v_name

    FROM example

    WHERE num = v_num;

END;

/

Calling the Procedure

To invoke the “p_getname” procedure and retrieve a name from the “Example” table, we can use the following code:

sql

SET SERVEROUTPUT ON;

DECLARE

    getname example.name%TYPE;

BEGIN

    p_getname(1, getname);

    DBMS_OUTPUT.PUT_LINE(’-----------’);

    DBMS_OUTPUT.PUT_LINE(getname);

END;

/

Sample Output

Upon executing the above code, the output will be as follows:
anonymous block completed
\-----------
NAMEONE

Summary and Key Points

In this article, we explored the concept of subprograms in PL/SQL, focusing on procedures in Oracle. We covered the structure of Oracle procedures, their syntax, and the different parameter modes available. Additionally, we created a simple procedure that retrieves a name from the “Example” table based on a provided number. Understanding subprograms and their implementation in Oracle can greatly enhance the functionality and organization of your PL/SQL code.

• Subprograms in PL/SQL are named blocks that can be called with parameters. There are two types of subprograms: procedures and functions.

• The structure of an Oracle procedure includes a declaration section (optional), a begin block (mandatory), an exception handling section (optional), and an ending slash (/) to terminate the procedure.

• The syntax for creating an Oracle procedure involves using the CREATE PROCEDURE statement, specifying the procedure name, parameters (optional), and the PL/SQL block.

• Parameter modes in procedures allow control over how parameters are passed and modified. The available modes are IN, OUT, and IN OUT.

• IN mode parameters are passed with a value and cannot be changed within the procedure.

• OUT mode parameters must be passed with a variable and can be modified within the procedure, with changes visible outside.

• IN OUT mode parameters can be passed with a value or variable, and changes made within the procedure are reflected outside.

• The default mode for parameters is IN.

• Practical implementation of procedures involves understanding the table structure and using appropriate SQL statements to manipulate data.

• The provided example demonstrates a simple procedure (p_getname) that retrieves a name from the “Example” table based on a given number.

• To call a procedure, it can be invoked within an anonymous block using the procedure name and providing values/variables for the parameters.

• The DBMS_OUTPUT package can be used to display output within the PL/SQL block.

• Understanding subprograms and utilizing procedures in Oracle can enhance code modularity, reusability, and maintainability.