PL/SQL is the Procedural Language extensions to the Structured Query Language (SQL). It is known for it’s tight integration with SQL High Performance and productivity, portability, scalability, manageability, etc. Although it says procedural extension, it does support Object-Oriented Programming.
Does that mean that you need to learn entire new language? No.
The learning curve is small and easy as PL/SQL supports syntax, datatype of SQL. There are new features available like conditional processing, Package, Triggers, etc. But, those are easy to learn.
PL SQL is highly inspired by ADA, that is reason you may find a lot of similarity between them. One thing to remember, it is proprietary language by Oracle, so not available in any other 3rd party application.
Below are some of main featured 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
There is separate engine to process PL/SQL and SQL called as PL/SQL Engine and SQL Engine. PL/SQL engine is pre-installed in the database or in and application development tool, such as Oracle Forms, Reports, etc.
Credit- Oracle.com
PL/SQL engine process the PL/SQL statements and sends the SQL statement to the SQL engine in the database. This switch does not impact the performance of language.
Basic Structure of PL/SQL program
PL/SQL is procedural, with blocs which starts with DECLARE and finishes with END.
DECLARE – This block is optional and meant for declaration. Declare variable, cursor, type you are going to use.
BEGIN – This is mandatory block and contains executable statements like if, else, logical operation etc.
EXCEPTION – This block is option and does error handling. All errors encountered during the execution of the command can be captured here to take necessary action.
END – This ends the block.
It is also possible to nest one block in another,
First PL/SQL Program
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 SQL * Plus console.
BEGIN
dbms_output.put_line('Hello, World');
END;
Example 2 – Sum 2 number and print 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
We covered basic overview of the PL SQL/. Just head on to next to learn more.
