PL/SQL - Procedures: A Beginner's Guide

Hello there, future PL/SQL wizards! Today, we're going to embark on an exciting journey into the world of PL/SQL procedures. Don't worry if you're new to programming – I'll be your friendly guide, and we'll take this step-by-step. By the end of this tutorial, you'll be creating your own procedures like a pro!

What is a PL/SQL Procedure?

Before we dive in, let's understand what a procedure is. Think of a procedure as a reusable recipe in cooking. Just as you might have a recipe for your favorite chocolate chip cookies that you use over and over, a procedure is a set of SQL and PL/SQL statements that you can call whenever you need to perform a specific task.

Parts of a PL/SQL Subprogram

Now, let's break down the anatomy of a PL/SQL subprogram (which includes procedures). It's like understanding the different parts of a sandwich – each component has its role!

1. Declaration Section: This is where we introduce our ingredients (variables, constants, etc.).
2. Executable Section: Here's where the magic happens – our actual code goes here.
3. Exception-Handling Section: This is our safety net, catching any errors that might occur.

Here's a simple visual representation:

DECLARE
   -- Declaration section
BEGIN
   -- Executable section
EXCEPTION
   -- Exception-handling section
END;

Creating a Procedure

Let's roll up our sleeves and create our first procedure! We'll start with something simple – a procedure that says "Hello, World!".

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

Let's break this down:

• CREATE OR REPLACE PROCEDURE: This tells Oracle to create a new procedure or replace an existing one with the same name.
• say_hello: This is the name we're giving our procedure.
• IS: This keyword separates the procedure header from its body.
• BEGIN and END: These keywords wrap around the executable section.
• DBMS_OUTPUT.PUT_LINE: This is a built-in procedure that prints text.
• /: This slash executes the entire CREATE PROCEDURE statement.

Executing a Standalone Procedure

Great! We've created our procedure, but how do we run it? It's as easy as pie:

BEGIN
   say_hello;
END;
/

When you run this, you should see "Hello, World!" printed out. Congratulations! You've just created and executed your first PL/SQL procedure!

Deleting a Standalone Procedure

Sometimes, we need to clean house. If you want to remove a procedure, it's straightforward:

DROP PROCEDURE say_hello;

This command will delete our say_hello procedure. Be careful with this power – with great power comes great responsibility!

Parameter Modes in PL/SQL Subprograms

Now, let's make our procedures more flexible by adding parameters. In PL/SQL, parameters can have different modes:

Mode	Description
IN	Pass values into the procedure
OUT	Return values from the procedure
IN OUT	Both pass values in and return values out

Let's create a procedure with an IN parameter:

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

Now we can greet different users:

BEGIN
   greet_user('Alice');
   greet_user('Bob');
END;
/

This will output:

Hello, Alice!
Hello, Bob!

Methods for Passing Parameters

When calling procedures with parameters, we have two main methods:

1. Positional Notation: We pass parameters in the order they're defined.
2. Named Notation: We specify which parameter each value corresponds to.

Let's see both in action with a new procedure:

CREATE OR REPLACE PROCEDURE calculate_rectangle(
   p_length IN NUMBER,
   p_width IN NUMBER,
   p_area OUT NUMBER,
   p_perimeter OUT NUMBER
) IS
BEGIN
   p_area := p_length * p_width;
   p_perimeter := 2 * (p_length + p_width);
END;
/

Now, let's call this procedure using both notations:

DECLARE
   v_area NUMBER;
   v_perimeter NUMBER;
BEGIN
   -- Positional notation
   calculate_rectangle(5, 3, v_area, v_perimeter);
   DBMS_OUTPUT.PUT_LINE('Positional: Area = ' || v_area || ', Perimeter = ' || v_perimeter);

   -- Named notation
   calculate_rectangle(p_width => 3, p_length => 5, p_perimeter => v_perimeter, p_area => v_area);
   DBMS_OUTPUT.PUT_LINE('Named: Area = ' || v_area || ', Perimeter = ' || v_perimeter);
END;
/

Both calls will produce the same result:

Positional: Area = 15, Perimeter = 16
Named: Area = 15, Perimeter = 16

The named notation is particularly useful when you have many parameters or want to skip optional ones.

And there you have it, folks! We've covered the basics of PL/SQL procedures, from creation to execution, and even touched on parameters and how to pass them. Remember, practice makes perfect, so don't be afraid to experiment with your own procedures.

Before we wrap up, here's a quick joke: Why did the database administrator leave his wife? She had too many views and wasn't normalized!

Happy coding, and may your procedures always execute successfully!

Credits: Image by storyset