What is PL/SQL? Full Form, Architecture of PL/SQL Developer
What is PL/SQL?
Oracle PL/SQL is an extension of SQL language that combines the data manipulation power of SQL with the processing power of procedural language to create super powerful SQL queries. PL/SQL ensures seamless processing of SQL statements by enhancing the security, portability, and robustness of the Database.
PL/SQL means instructing 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. The PL/SQL Full form is "Procedural Language extensions to SQL".
PL/SQL Developer is a free Integrated Development Environment provided by Oracle to develop Software in Oracle Database environment and perform various Database tasks with ease. The PL/SQL Developer IDE provides with GUI and Plugins to use in order to help the end users save the time on their Database tasks.
Architecture of PL/SQL
The Below PL/SQL Example is a pictorial representation of PL/SQL Architecture.
The PL/SQL architecture mainly consists of following three components:
This is the component which has the actual PL/SQL code.
This consists of different sections to divide the code logically (declarative section for declaring purpose, execution section for processing statements, exception handling section for handling errors)
It also contains the SQL instruction that used to interact with the database server.
All the PL/SQL units are treated as PL/SQL blocks, and this is the starting stage of the architecture which serves as the primary input.
Following are the different type of PL/SQL units.
PL/SQL engine is the component where the actual processing of the codes takes place.
PL/SQL engine separates PL/SQL units and SQL part in the input (as shown in the image below).
The separated PL/SQL units will be handled by the PL/SQL engine itself.
The SQL part will be sent to database server where the actual interaction with database takes place.
It can be installed in both database server and in the application server.
This is the most important component of Pl/SQL unit which stores the data.
The PL/SQL engine uses the SQL from PL/SQL units to interact with the database server.
It consists of SQL executor which parses the input SQL statements and execute the same.
Features & Advantages of PL/SQL
Better performance, as SQL is executed in bulk rather than a single statement
Tight integration with SQL
Supports Object Oriented Programming concepts.
Scalability and Manageability
Supports Web Application Development
Supports Server Page Development
Disadvantages of PL/SQL
Stored Procedures in PL/SQL uses high memory
Lacks functionality debugging in stored procedures
Any change in underlying database requires change in the presentation layer also
Does not completely separate roles of back-end developer and fron-end developer
Difficult to separate HTML development with PL/SQL development
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 need 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 a Database server.
No interaction with the database server.
Cannot contain PL/SQL code in it.
It is an extension of SQL, so that it can contain SQL inside it.