PL/SQL (Procedural Language/SQL) is Oracle’s powerful procedural language designed to handle complex processing directly within the database. In this article, we will introduce the basic structure of PL/SQL blocks, control syntax, error handling, and provide practical examples with command explanations for beginners.
🔰 What is PL/SQL?
PL/SQL is a language designed by Oracle to enable procedural programming within the database. It extends SQL with control structures, error handling, and variable declarations, making it ideal for writing business logic that runs close to the data.
📦 Basic Structure of a PL/SQL Block
DECLARE
-- Variable declaration section (optional)
BEGIN
-- Main processing section (SQL statements and logic)
EXCEPTION
-- Error handling section (optional)
END;
/
🔍 Command Purpose Overview
| Command / Syntax | Description |
|---|---|
DECLARE | Used to declare variables or cursors. This section is optional. |
BEGIN | Indicates the start of the main execution section. |
EXCEPTION | Handles errors if they occur during execution. This section is optional. |
END; | Marks the end of the PL/SQL block. |
/ | Executes the block in SQL*Plus or SQL Developer. |
DBMS_OUTPUT.PUT_LINE | Outputs text to the console for debugging or display. |
SET SERVEROUTPUT ON | Enables the display of DBMS_OUTPUT text in the session. |
✅ Enable Output Display
SET SERVEROUTPUT ON
Before using DBMS_OUTPUT.PUT_LINE, enable this setting to view the output in SQL*Plus or SQL Developer.
🧪 PL/SQL Examples with Command Explanation
1. Displaying a String
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
END;
/
➡ Displays Hello, PL/SQL! to the console.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE('Hello, PL/SQL!');
3 END;
4 /
Hello, PL/SQL! ★
PL/SQL procedure successfully completed.
2. Performing a Numeric Calculation
DECLARE
v_num1 NUMBER := 10;
v_num2 NUMBER := 5;
v_sum NUMBER;
BEGIN
v_sum := v_num1 + v_num2;
DBMS_OUTPUT.PUT_LINE('Sum is ' || v_sum);
END;
/
➡ Declares 3 variables, adds two of them, and outputs the result Sum is 15.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_num1 NUMBER := 10;
3 v_num2 NUMBER := 5;
4 v_sum NUMBER;
5 BEGIN
6 v_sum := v_num1 + v_num2;
7 DBMS_OUTPUT.PUT_LINE('Sum is ' || v_sum);
8 END;
9 /
Sum is 15 ★
PL/SQL procedure successfully completed.
3. Using IF Statement for Conditional Logic
DECLARE
v_score NUMBER := 75;
BEGIN
IF v_score >= 60 THEN
DBMS_OUTPUT.PUT_LINE('Pass');
ELSE
DBMS_OUTPUT.PUT_LINE('Fail');
END IF;
END;
/
➡ Outputs Pass if the score is 60 or above.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_score NUMBER := 75;
3 BEGIN
4 IF v_score >= 60 THEN
5 DBMS_OUTPUT.PUT_LINE('Pass');
6 ELSE
7 DBMS_OUTPUT.PUT_LINE('Fail');
8 END IF;
9 END;
10 /
Pass ★
PL/SQL procedure successfully completed.
4. Using a FOR Loop
BEGIN
FOR i IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE('Loop count: ' || i);
END LOOP;
END;
/
➡ Outputs the loop counter values from 1 to 3.
SQL> SET SERVEROUTPUT ON
SQL> BEGIN
2 FOR i IN 1..3 LOOP
3 DBMS_OUTPUT.PUT_LINE('Loop count: ' || i);
4 END LOOP;
5 END;
6 /
Loop count: 1 ★
Loop count: 2 ★
Loop count: 3 ★
PL/SQL procedure successfully completed.
5. Using a WHILE Loop
DECLARE
v_counter NUMBER := 1;
BEGIN
WHILE v_counter <= 3 LOOP
DBMS_OUTPUT.PUT_LINE('Count: ' || v_counter);
v_counter := v_counter + 1;
END LOOP;
END;
/
➡ Repeats the loop while the condition v_counter <= 3 holds true.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_counter NUMBER := 1;
3 BEGIN
4 WHILE v_counter <= 3 LOOP
5 DBMS_OUTPUT.PUT_LINE('Count: ' || v_counter);
6 v_counter := v_counter + 1;
7 END LOOP;
8 END;
9 /
Count: 1 ★
Count: 2 ★
Count: 3 ★
PL/SQL procedure successfully completed.
6. Handling Division by Zero (Exception Handling)
DECLARE
v_result NUMBER;
BEGIN
v_result := 10 / 0;
DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Divide by zero error');
END;
/
➡ Catches the division by zero error and displays a friendly message.
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_result NUMBER;
3 BEGIN
4 v_result := 10 / 0;
5 DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
6 EXCEPTION
7 WHEN ZERO_DIVIDE THEN
8 DBMS_OUTPUT.PUT_LINE('Divide by zero error');
9 END;
10 /
Divide by zero error ★
PL/SQL procedure successfully completed.
📌 Diagram: PL/SQL Block Structure
┌────────────────────────────┐
│ DECLARE section │ ← Variable/cursor declarations
├────────────────────────────┤
│ BEGIN section │ ← Main logic and SQL execution
├────────────────────────────┤
│ EXCEPTION section │ ← Error handling
└────────────────────────────┘
END;
🧠 PL/SQL Use Cases and Benefits
| Use Case | Purpose / Benefit |
|---|---|
| Batch job automation | Ideal for executing repeated or conditional logic |
| Error handling | Graceful error management via EXCEPTION block |
| Reusability | Procedures and functions can be reused |
| Triggers | Automatically execute logic on DML events |
📝 Summary
PL/SQL is an essential skill for Oracle database users. It allows you to embed logic directly in the database, improving performance and maintainability. By mastering BEGIN ~ END blocks and control statements, you can take full advantage of Oracle’s capabilities.
[reference]
Database PL/SQL Language Reference
コメント