PL SQL

Points to Remember : -

• PL/SQL stands for PROCEDURAL language extensions to SQL.
• PL/SQL extends SQL by adding programming structures and subroutines available in any high level language.
• PL/SQL can be used for both server-side and client-side development.
• PL/SQL has syntax and rules that determine how programming statements work together.
• PL/SQL is not a standalone programming language, hence it cannot be used individually for developing applications.
• PL/SQL is a part of the ORACLE RDBMS, and hence can reside in two environments, the client and the server.
• Any module that is developed using PL/SQL an be moved easily between server side and client side applications.
• Either in client or server environment any PL/SQL block or sub routine is processed by the PL/SQL engine.
• PL/SQL engine is a special component that processes and executes any PL/SQL statements and sends any SQL statements processor.
• The SQL statement processor is always located on the oracle server and hence all SQL Statements are processed on the server only.As per the necessity the PL/SQL engine can be located either at ...

            1) SERVER side.

          2) CLIENT side.

• When PL/SQL engine is located upon the SERVER. The whole PL/SQL block is passed to the PL/SQL engine on the ORACLE SERVER for processing including client components.
• This concepts gives security, less load and performance for applications.
• When the PL/SQL engine is located upon the client, the PL/SQL processing is done on the client side. All SQL statements that are embedded within the PL/SQL block, are sent to the ORACLE SERVER for further processing.
• If the PL/SQL block doesn't contain any SQL statements, the entire block is executed on the client side itself.
• Because of the above concept, the application load in the network will highly reduce and the system becomes highly loose coupled in the development process, giving high accessibility for easy maintenance.

Advantages : -

• PL/SQL improves server performance by reducing the calls from your application to ORACLE.
• Without PL/SQL, the Oracle server must process eash SQL statement individually, that results in high performance overhead on the server due to additional call for each segment.
• Your application can pass numerous SQL statements to Oracle at once that improves      network traffic.

• All PL/SQL code is portable to any operating system and platform on which Oracle runs.
• Recompilations are minimized because packages do not require to be recompiled if procedures within the package are redefined.
• PL/SQL enables conditional and sequential control statements, theus provides tremendous programming flexibility.
• Modularity is promoted because PL/SQL lets you break down an applicatio into manageable ,well-defined logic modules.
• PL/SQL makes error detection and handling easy.
• Support for object oriented programming.
• Tight Security.