PL/SQL Procedures Tutorial

Welcome to CodeYourCraft's PL/SQL Procedures tutorial! In this lesson, we'll learn about PL/SQL procedures, a powerful feature in Oracle Database that allows you to create reusable blocks of code. 🎯

What are PL/SQL Procedures?

PL/SQL procedures are a collection of PL/SQL statements that perform a specific task. They are similar to functions, but unlike functions, procedures do not return a value. Procedures are particularly useful for tasks that require multiple SQL statements, such as inserting data into multiple tables, or for encapsulating complex logic. 📝

Creating a PL/SQL Procedure

To create a PL/SQL procedure, follow these steps:

Define the procedure with the PROCEDURE keyword and give it a name.
Declare any input and output parameters, if needed.
Define the procedure body with a BEGIN and END block.
Write your PL/SQL code between the BEGIN and END blocks.

Here's a simple example of a PL/SQL procedure that inserts a record into the employees table.

sql

CREATE OR REPLACE PROCEDURE insert_employee (
    p_first_name VARCHAR2,
    p_last_name VARCHAR2,
    p_salary NUMBER
) AS
BEGIN
    INSERT INTO employees (first_name, last_name, salary)
    VALUES (p_first_name, p_last_name, p_salary);
    COMMIT;
END;
/

In this example, we've created a procedure named insert_employee with three parameters: p_first_name, p_last_name, and p_salary. Inside the procedure, we've inserted a new record into the employees table using the provided parameters.

Calling a PL/SQL Procedure

To call a PL/SQL procedure, you use the / command followed by the procedure name and any necessary arguments.

sql

BEGIN
    insert_employee('John', 'Doe', 50000);
END;
/

Procedure Types 📝

There are two types of PL/SQL procedures:

Standalone Procedures: These are self-contained procedures that can be run independently.
Packaged Procedures: These are procedures that belong to a package. Packages allow you to group related procedures and functions together.

Quiz

Stay tuned for more on PL/SQL procedures, including how to create packaged procedures and handle exceptions! 🎉