Procedural extensions to SQL.
Learn PL/SQL to write robust, high-performance blocks of code for Oracle Database applications.
SQL is just asking for data. PL/SQL is asking for data, thinking about it, and making decisions—all without leaving the database.
SQL is a simple language. It can only ask simple questions.
PL/SQL is a full programming language built exactly for Oracle databases.
Why do big companies use PL/SQL? It stops data from traveling back and forth over the network. If your code lives in the database, it processes data instantly.
The app asks for a row, waits, processes it, and sends it back. It repeats this 10,000 times. This is extremely slow.
The app sends one single command: "Do the job." The database does all 10,000 checks internally and instantly.
Every piece of PL/SQL code is built like a sandwich. It has a specific start, middle, and end. We call this a "Block".
(Optional)
Before you do any work, you must tell the database what tools you need. If you need a box to hold a number, you make it here.
(Required)
This is where the actual action happens. You look at data, change data, and do your math here.
(Optional)
If something goes wrong in the "BEGIN" area, the code drops down to here instead of crashing.
(Required)
This simply tells the database that your code block is completely finished.
Variables are like labeled boxes. You put data in them so you can remember it and use it later in your code.
Your code can look at data and choose different paths, just like a car at a fork in the road.
Imagine you have 5,000 users. You need to add $10 to every account. Doing this one by one by hand would take weeks. A PL/SQL "Loop" does this automatically in a fraction of a second.
A saved set of instructions. Once you write the code, you save it inside the database. Anyone can run it later by just calling its name, like pressing a button.
A machine that does math. You give it information, it calculates something, and it returns a final answer back to you.
An invisible tripwire. You tell the database: "If someone deletes a record, automatically copy it to a backup table first."
In standard systems, an error crashes the whole program. PL/SQL uses "Exceptions". When an error drops, the Exception net catches it safely.
Let's look at a real, complete piece of PL/SQL code designed to give a bonus to top employees.
-- 1. Setup Area
DECLARE
v_bonus_amount NUMBER := 500; -- Create a box holding the number 500
v_employee_id NUMBER := 101; -- Create a box holding the ID 101
-- 2. Work Area
BEGIN
-- Update the database directly
UPDATE employees
SET salary = salary + v_bonus_amount
WHERE id = v_employee_id;
-- Check if anything actually changed
IF SQL%NOTFOUND THEN
-- If no employee was found, trigger an error on purpose
RAISE NO_DATA_FOUND;
END IF;
-- Save the changes permanently
COMMIT;
-- 3. Safety Net Area
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('Stop! This employee does not exist.');
WHEN OTHERS THEN
dbms_output.put_line('Something else went completely wrong.');
-- 4. Finish Line
END;If you ask SQL for 1,000 customers, it dumps all 1,000 on your desk at once. This can crash your memory. A Cursor acts like a factory conveyor belt. It holds the data in the database and hands you exactly one row at a time.
Procedures are useless if they always do the exact same thing. You need to give them new instructions. We do this using Parameters (labeled mailboxes attached to your code).
Data you give TO the code.
Calculates total score based on the given user ID.
The final answer it gives back.
If you write 500 different functions, your database becomes a messy junk drawer. A Package is a digital folder that groups related procedures, functions, and variables together.
PL/SQL is very strict. You cannot put a word into a box meant for dates. You must declare the "shape" of your box first.
Words, names, addresses. Always needs a max length.
VARCHAR2(50)Math, prices, IDs. Handles decimals perfectly.
NUMBER(10,2)Stores Year, Month, Day, Hour, Minute, and Second.
DATESimple logic. Can only be TRUE, FALSE, or NULL.
BOOLEAN
If you have 10 different options, writing 10 IF/THEN rules gets messy. A CASE statement acts like a clean train switchboard, routing data down the exact correct track immediately.
Because PL/SQL runs deep inside the database server, you can't normally see what it's doing. If you need to print a message to your screen to test your logic, you use the built-in megaphone: DBMS_OUTPUT.
When you update data, it is only changed in your private memory. No one else can see it yet. COMMIT makes your changes permanent and visible to the whole company.
Did you accidentally delete the entire database instead of one row? Don't panic. As long as you haven't typed COMMIT, you can type ROLLBACK to undo everything like it never happened.
A standard Loop goes back and forth to the data tables 1,000 times to get 1,000 rows. BULK COLLECT takes a giant shovel, scoops up all 1,000 rows at once, and drops them into memory. It is extremely fast.
Hackers use "SQL Injection" to trick your database. If you let users type directly into your SQL queries, they can type "DROP TABLE users" and destroy everything.
Bind Variables act like quarantine zones. They tell the database: "Treat this input as plain text, NOT as code."
You don't have to be at your desk to run PL/SQL. You can use DBMS_SCHEDULER to tell the database to automatically run your procedures at a specific time, like midnight every Sunday.
Master more skills with other tutorials from the Data Management series.