The Project | About Us | Contribute | Donations | License

HOME

Stored procedures are a powerful feature in PL/SQL that allow you to encapsulate and reuse code. They are essentially named PL/SQL blocks that can be stored in the database and executed as needed. This module will cover the basics of stored procedures, how to create them, and how to use them effectively.

What is a Stored Procedure?

A stored procedure is a set of SQL statements and PL/SQL code that is stored in the database and can be executed as a single unit. Stored procedures can accept parameters, perform operations, and return results.

Key Benefits of Stored Procedures:

Reusability: Write once, use many times.
Maintainability: Easier to manage and update code.
Performance: Reduced network traffic and improved execution speed.
Security: Control access to data and operations.

Creating a Stored Procedure

To create a stored procedure, you use the CREATE PROCEDURE statement. Here is the basic syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name 
[ (parameter_name [IN | OUT | IN OUT] data_type [, ...]) ]
IS
BEGIN
  -- PL/SQL code
END procedure_name;

Example: Simple Stored Procedure

Let's create a simple stored procedure that prints "Hello, World!".

CREATE OR REPLACE PROCEDURE say_hello
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, World!');
END say_hello;

Explanation:

CREATE OR REPLACE PROCEDURE say_hello: Creates a new procedure named say_hello or replaces it if it already exists.
IS: Begins the declaration section.
BEGIN ... END: The executable section where the PL/SQL code resides.
DBMS_OUTPUT.PUT_LINE('Hello, World!'): Outputs the string "Hello, World!" to the console.

Executing a Stored Procedure

To execute a stored procedure, you use the EXEC or CALL statement.

EXEC say_hello;

or

CALL say_hello;

Stored Procedures with Parameters

Stored procedures can accept parameters to make them more flexible and reusable.

Example: Stored Procedure with Parameters

Let's create a stored procedure that takes a name as a parameter and prints a greeting.

CREATE OR REPLACE PROCEDURE greet_user(p_name IN VARCHAR2)
IS
BEGIN
  DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
END greet_user;

Explanation:

p_name IN VARCHAR2: Declares an input parameter p_name of type VARCHAR2.
DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!'): Outputs a personalized greeting.

Executing the Procedure with Parameters

EXEC greet_user('Alice');

Practical Exercise

Exercise 1: Create a Procedure to Add Two Numbers

Create a stored procedure named add_numbers that takes two input parameters, adds them, and prints the result.
Execute the procedure with different sets of numbers.

Solution:

CREATE OR REPLACE PROCEDURE add_numbers(p_num1 IN NUMBER, p_num2 IN NUMBER)
IS
  v_sum NUMBER;
BEGIN
  v_sum := p_num1 + p_num2;
  DBMS_OUTPUT.PUT_LINE('The sum is: ' || v_sum);
END add_numbers;

Executing the Solution:

EXEC add_numbers(10, 20);
EXEC add_numbers(5, 15);

Common Mistakes and Tips

Syntax Errors: Ensure you follow the correct syntax for creating and executing procedures.
Parameter Mismatches: Make sure the number and types of parameters match when calling the procedure.
Debugging: Use DBMS_OUTPUT.PUT_LINE to print intermediate results for debugging.

Conclusion

Stored procedures are a fundamental part of PL/SQL programming, providing a way to encapsulate and reuse code efficiently. In this section, you learned how to create and execute stored procedures, both with and without parameters. Practice creating your own stored procedures to become more comfortable with this powerful feature.

Next, we will explore functions, which are similar to stored procedures but are designed to return a value.

PL/SQL Course

Module 1: Introduction to PL/SQL

Module 2: PL/SQL Fundamentals

Module 3: Working with SQL in PL/SQL

Module 4: Advanced PL/SQL Concepts

Module 5: PL/SQL Optimization and Best Practices

Module 6: Real-World Applications

The Project | About Us | Contribute | Donations | License