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

🎯 Introduction

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.