JAVA EE: Oracle : Stored Procedure with Input and Output Parameters

Friday, 10 July 2015

Oracle : Stored Procedure with Input and Output Parameters

🔔 Enjoying this tutorial?

Subscribe to our channel to learn Java, Oracle DB, SQL, and practical backend programming step-by-step!

Introduction: What is a Stored Procedure?

A Stored Procedure in Oracle is a precompiled collection of SQL statements and PL/SQL code stored directly inside the database. Instead of executing queries repeatedly from your application, you can execute a stored procedure with parameters to perform database tasks securely and efficiently.

In this guide, you will learn how to create and execute a stored procedure named get_country_name that accepts a Country ID as an IN parameter and returns the corresponding Country Name as an OUT parameter.

Step 1: Defining Input and Output Parameters

When creating a stored procedure in Oracle SQL Developer or a SQL console, parameters define the data flowing in and out:

country_id_input (IN): The identifier you pass to search for the country.
country_name_output (OUT): The variable that holds and returns the fetched country name.

Step 2: Stored Procedure SQL Logic

Here is the SQL query written inside the body of the procedure to retrieve the value:

SELECT country_name 
INTO country_name_output 
FROM countries 
WHERE country_id = country_id_input;

After writing the query, compile the procedure to ensure there are no syntax or semantic errors.

Step 3: Running and Testing the Procedure

Testing the stored procedure in Oracle SQL Developer is straightforward:

Click on the Run button for the procedure.
Enter the input value for country_id_input (e.g., passing a country code).
Execute and inspect the Output Variables tab.
You will see the result returned (such as Argentina or India) in the output field.

Step 4: Dropping the Stored Procedure

If you no longer need the procedure or wish to re-create it from scratch, you can right-click the procedure name in the tree view and select Drop, or execute:

DROP PROCEDURE get_country_name;

📺 Recommended Next Tutorials from Ram N Java

Click here to watch in Youtube :
https://www.youtube.com/watch?v=QZmZ8hYBJgk&list=UUhwKlOVR041tngjerWxVccw

Stored Procedure

CREATE OR REPLACE PROCEDURE GET_COUNTRY_NAME 
(
  COUNTRY_ID_INPUT IN VARCHAR2 
, COUNTRY_NAME_OUTPUT OUT VARCHAR2 
) AS 
BEGIN
  SELECT COUNTRY_NAME INTO COUNTRY_NAME_OUTPUT FROM COUNTRIES WHERE COUNTRY_ID=COUNTRY_ID_INPUT;
END GET_COUNTRY_NAME;