PL/SQL, Oracle’s proprietary procedural extension to SQL, stands out for its seamless SQL integration and features that prioritize performance, productivity, and scalability. While rooted in procedural design, it also embraces Object-Oriented Programming. Its resemblance to ADA isn’t coincidental, as ADA significantly influences its structure.
Exclusive to Oracle, PL/SQL isn’t found in third-party applications. Fortunately, newcomers find its learning curve gentle. Familiarity with SQL syntax and data gives learners a head start, and even its advanced features, such as conditional processing, packages, and triggers, are straightforward to grasp.
Below are some of the main features of PL/SQL.
- Error Handling
- Blocks
- Variables and Constants
- Subprograms
- Packages
- Triggers
- Input and Output
- Data Abstraction
- Control Statements
- Conditional Compilation
- Processing a Query Result Set One Row at a Time
Architecture of PL/SQL
The processing of PL/SQL and SQL is handled by distinct engines known as the PL/SQL Engine and the SQL Engine. These engines are intrinsic components of the Oracle ecosystem. Specifically, the PL/SQL engine comes pre-packaged not only within the database but also within Oracle’s suite of application development tools, such as Oracle Forms and Reports.
When executing code, the PL/SQL engine handles the procedural elements and delegates the SQL statements to the SQL engine located in the database. This transition between engines is executed with remarkable speed, ensuring that the overall performance remains optimal.
Basic Structure of PL/SQL program
PL/SQL is procedural, with blocks that start with DECLARE and finish with END.
[DECLARE
-- Declaration of variables, types, cursors, etc.
variable_name data_type [:= default_value];
...
]
BEGIN
-- Executable statements
...
[EXCEPTION
-- Exception handling
WHEN exception_name THEN
-- actions to perform when this exception is raised
...
]
END;
/
DECLARE (optional) – This section is where you declare all the variables, constants, cursors, user-defined exceptions, etc. The DECLARE keyword is not necessary if you have nothing to declare, such as in anonymous blocks without declarations.
BEGIN (mandatory) – This is the main section of the PL/SQL block where you put your procedural code and SQL statements. The BEGIN keyword indicates the start of this section.
EXCEPTION (optional) – This section is used for handling exceptions (errors) that might arise during the execution of the PL/SQL block. Exception handling is crucial for gracefully handling and reporting errors, ensuring your program doesn’t crash unexpectedly.
END (mandatory) – This keyword marks the end of the PL/SQL block.
/ (mandatory for executing the block) – The / character is used to execute the PL/SQL block in tools like SQL*Plus. It tells the interpreter that the block is complete and ready for execution.
It is also possible to nest one block in another in the Begin section. You can have another declare.. begin..exception and end in it.
Now, let’s write some basic PL/SQL program.
Example 1 – This program simply used dbms_output API to print a ‘Hellow, World’ message on the SQL * Plus console.
BEGIN
dbms_output.put_line('Hello, World');
END;
Example 2 – Sum 2 numbers and print the result
DECLARE
a number := 0;
b number := 0;
c number := 0;
BEGIN
c := a+b;
dbms_output.put_line('Sum ' || c);
EXCEPTION
WHERE OTHERS THEN
dbms_output.put_line('Error ' || SQLERRM);
END;
Summary
This was a brief introduction to PL/SQL. It provides robust tools to help you build scalable, portable applications that automate the efficient processing of SQL commands. Keep practicing with different codes and queries to get a stronger hold on PL/SQL.
Happy coding!
