Basic Introduction to PL/SQL with examples.
PL/SQL basically stands for “Procedural Language extensions to SQL”. This is the extension of Structured Query Language (SQL) that is used in Oracle. Unlike SQL, PL/SQL allows the programmer to write code in procedural format.
It combines the data manipulation power of SQL with the processing power of procedural language to create a super powerful SQL queries.
It allows the programmers to instruct the compiler ‘what to do’ through SQL and ‘how to do’ through its procedural way.
Similar to other database languages, it gives more control to the programmers by the use of loops, conditions and object oriented concepts.
Advantage of Using PL/SQL
1. Better performance, as SQL is executed in bulk rather than a single statement
2. High Productivity
3. Tight integration with SQL
4. Full Portability
5. Tight Security
6. Support Object Oriented Programming concepts
Disadvantages of using PL/SQL
1. The debugger never worked.
2. Ant syntax error is just reported by a general error message. Without any
indication for its location. The only way to find it is to comment out parts
of the code until it disappear and work the way out to find what caused it.
3. The syntax is awkward, complex and inconsistent.
Basic Difference between SQL and PL/SQL
|SQL is a single query that is used to perform DML and DDL operations.||PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc.|
|It is declarative, that defines what needs to be done, rather than how things need to be done.||PL/SQL is procedural that defines how the things needs to be done.|
|Execute as a single statement.||Execute as a whole block.|
|Mainly used to manipulate data.||Mainly used to create an application.|
|Interaction with Database server.||Interaction with Database server.|
|Cannot contain PL/SQL code in it.||It is an extension of SQL, so it can contain SQL inside it.|
EXAMPLES OF PL/SQL PROGRAM
code for Sum of two numbers
Set serveroutput on
a := &a;
b := &b;
c := a+b;
Enter value of a:10
Enter value of b:15