Introduction to PL/SQL (Procedural language / Structure Query Language)
The development of database application typically requires language constructs similar to those that can be found in programming language such as c,c++ or pascal.SQL is very flexible ,powerful and easy to learn language. Data manipulation becomes very easy with a few English like sentences. However SQL is a non-procedural language. It does not offer any programming construct for conditional selections , iteration etc. hence cannot be used as an application development tool. A solution to this problem is PL
PL/SQL (Procedural language / Structure Query Language) is a procedural Extension of Oracle-SQL that offers language constructs. The basic construct is PL/SQL is a block. In a block constant and variable can be declared. Statements in PL/SQL block include SQL statements control structure (loops), condition statement (if- then- else) exception handling and calls of other PL/SQL blocksNeed of PL in SQL or say Limitation of SQL
1. No Procedural Capabilities: SQL does not offer any type of procedural capabilities such as conditional selection , looping and branching etc.
2. Time Consuming Processing: All SQL statement are executed by the database server or oracle engine. SQL statement are passed to the oracle engine one at a time. Each time an SQL statement is executed a call is made to the engine's resources. This adds to the traffic on the network , thereby decreasing the speed of data processing ,especially in a multi-user environment.
3. No error Handling Routines/ Procedures: While processing SQL statement , if an error occurs, SQL known no way to handle these errors. Advantage of PL/SQL:
1. Procedural Capabilities: PL/SQL is the procedural extension of Oracle-SQL. That is apart from supporting SQL, it offers construct for conditional selection, iteration etc. variable and constant declarations are possible. It also offers error handling features.
2. Improve Performance: Processing is fast since entire block is sent to the Oracle engine rather than one statement at a time.
3. Portability An application developed in PL/SQL is portable across hardware and operating system platforms.
4.Error Handling Procedures/ Routines: PL/SQL support customized error handling routines i.e a user can write his/her own error handling routines.
5.Enhanced Productivity : PL/SQL can be embedded in Oracle application development tools and 3GL interface program.
6.Integration with RDBMS: Variables in PL/SQL can belong to any SQL data type such as CHAR,DATE etc. or to any PL/SQL data type such as BOOLEAN,INTEGER and so on.
7.Improved Transaction Performance : PL/SQL supports all sorts of calculations without involving Oracle Engine . This result in improved transaction performance. Structure of PL/SQL Block
Declare Section :This section is meant for declaring variables, constraints, cursors etc. This section is optional.
begin Section : This section consists of a set of SQL and PL/SQL Statement. Actual data manipulation , retrieval, looping and branching construct are specified in this section. This section is compulsory .
Exception Section:Any errors or exceptions raised during execution of the PL/SQL block are handle here. Errors can arise due to syntax, logic and validation rule violation. This section is optional.
End Section:This marks the end of a PL/SQL block. This section is compulsory.