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:
🎯 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:
🎯 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:
The table contains the following data:
🎯 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.
🎯 Calling the Procedure
To invoke the "p_getname" procedure and retrieve a name from the "Example" table, we can use the following code:
🎯 Sample Output
Upon executing the above code, the output will be as follows:
🎯 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.