Friday, 21 September 2012

13 Point comparison between SQL and PLSQL

SQL and PL/SQL both are the integrated part of DATA BASE MANAGEMENT SYSTEM. SQL is basic while PL/SQL is a procedural language which uses SQL to perform multiple tasks on database transactions and manipulations. We can embed SQL in a PL/SQL program, but we cannot embed PL/SQL within a SQL statement. Here are some noteworthy differences between SQL and PL/SQL. 

1. Procedural Capabilities: SQL does not have any procedural capabilities while PL/SQL supports it. It means that PL/SQL provides conditional, iterative and sequential control structures and many more programming facilities. SQL is a data oriented language for selecting and manipulating sets of data while PL/SQL is a procedural language to create applications. SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tells the database how to do things (procedural).

2. Flexibility: PL/SQL is much more flexible than SQL as we can program any thing as we desire while in case of SQL you have to write just one statement to retrieve the results.

3. Server Performance: PL/SQL improves server performance by reducing the number of calls from application to oracle server. The application pass block of SQL statements to oracle server at one time instead of passing each statement individually. This reduces the network traffic between application and oracle server.

4. Error Detection and handling: PL/SQL provides the facility of error detection and handling. It enables the user to define exceptions on their won which is very poor in SQL.

5. Modular Programming: PL/SQL is modular programming because it allows you to divide your application into managable well defined logic modules such as procedures and functions.

6. Reduced Recompilation: PL/SQL reduces recompilation work as the combination of SQL commands can be executed at the same time which is not possible in SQL where you have to execute only one statement at a time.

7. Reduced I/O: The input output operations occur very less in the case of PL/SQL as the set of SQL commands can be handled at a single time while you have to execute all the statement individually in the case of SQL.

8. Platform Independence and Portability: The code of PL/SQL can be used on any platform which runs oracle while you can not do the same with SQL commands. You have to explicitly write all the SQL commands when you shift from one platform to another. While in case of PL/SQL, if you have made a program, you can run it on any platform.

9. Dynamic SQL: PL/SQL supports dynamic SQL which makes your application more flexible and versatile.

10. Security: By using PL/SQL you can provide security to the sensitive data by moving the code from client to server, you can protect data from tampering, hide the internal details and can restrict who has access to this code.

11. Traffic Congestion: PL/SQL uses block of SQL commands and are passed at one time not frequently as in the case of SQL, so the traffic to the server is considerably reduced. 

12. Transaction Performance: PL/SQL also improves Transaction performance as the many calculations can be performed very efficiently and quickly without calling the oracle engine. 

13. Reusability: PL/SQL code once created can be used any time anywhere but this is not possible for SQL Commands. So the same code can be used by many applications and you have no need of creating the same functions or procedures again and again.

No comments:

Post a Comment