PL/SQL is a natural language of the DBA. It does not have any procedural capabilities such as looping and branching. For this, Oracle provides the PL/SQL language.
PL/SQL is really an extension of SQL. PL/SQL adds to the power of SQL and provides the user with all the facilities of a programming environment. PL/SQL is a procedural language that ORACLE developed as an extension slandered SQL in order to provide a way to execute procedural logic on the database.
PL/SQL is ORACLE’s procedural language extension to SQL. Unlike SQL, PL/SQL is a procedural, not declarative. This means that PL/SQL code specifies exactly how things get done. PL/SQL also enables you to embed SQL statements within procedural code.PL/SQL bridges the gap between database technology and procedural programming languages. It can be thought of as a development tool that extends the facilities of ORACLE’s SQL database language. Programmers can use it to create programs for validation and manipulation of table data. Via PL/SQL he can insert, delete, update and retrieve table data as well as use procedural techniques such as writing loops or branching to another block of code. It allows him to use all the SQL data manipulation statements as well as transaction processing and cursor control operations. PL/SQL blocks can contain any number of SQL statements. It allows you to logically group a number of SQL sentences and parse tem to DBA as a single block.What PL/SQL can do for programmers?PL/SQL offers procedural methods of code writing for accessing the database and manipulating the data with flexibility and ease. This is because procedural supports the following.
- SQL data manipulation statements, by using which table data can be manipulated.
- SQL transaction processing statements, which are treated as a single block by DBA, so that all changes made to the data in the table, are done or undone all at once.
- SQL functions can be called from within PL/SQL. These functions can be numeric, character, date or data conversion functions. The functions available in the SQL can be used in PL/SQL blocks.
- SQL predicates: are used in the where conditions of SQL sentences, for data manipulation. The predicate to a where condition can consist of a simple comparison or of multiple comparisons using the logical operations AND, OR and NOT. Other comparison operators like BETWEEN, IS NULL, LIKE, EXISTS, etc. can also be used to form predicates.
- PL/SQL allows you to declare variables and then use them in blocks of code. It is quite possible to use variables to store the results of a query for later processing or calculate values and insert them into an ORACLE table later. PL/SQL variable can be used anywhere, either in SQL statements or in PL/SQL blocks.