Pl/sql Training Institues,classes In Marathahalli Bangalore

By Author: First Enquiry
Total Articles: 10
Comment this article

PL/SQL
PL/SQL stands for Procedural Language extensions to the Structured Query Language (SQL). It is a powerful language for both querying and updating data in relational databases.
PL/SQL includes procedural language elements such as conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variables of those types, and triggers. It can handle exceptions (runtime errors). Arrays are supported involving the use of PL/SQL collections. Implementations from version 8 of Oracle Database onwards have included features associated with object-orientation. One can create PL/SQL units such as procedures, functions, packages, types, and triggers, which are stored in the database for reuse by applications that use any of the Oracle Database programmatic interfaces.
The main feature of SQL (non procedural) is also a drawback of SQL. One can use control statements like decision making, iterative control if only SQL is to be used. PL/SQL is basically a procedural language, which provides functionality of decision making, iteration and many more features like other procedural ...
... programming languages. A PL/SQL program unit is one of the following: PL/SQL anonymous block, procedure, function, package specification, package body, trigger, type specification, type body, library. Program units are the PL/SQL source code that is compiled, developed and ultimately executed on the database.
PL/SQL anonymous block
The basic unit of a PL/SQL source program is the block, which groups together related declarations and statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into a declarative part, an executable part, and an exception-handling part. The declaration section is optional and may be used to define and initialize constants and variables. If a variable is not initialized then it defaults to NULL value. The optional exception-handling part is used to handle run time errors. Only the executable part is required. A block can have a label.
Function
The purpose of a PL/SQL function is generally to compute and return a single value. This returned value may be a single scalar value (such as a num ber, date or character string) or a single collection (such as a nested table or varray). User-defined functions supplement the built-in functions provided by Oracle Corporation.
Procedures
Procedures resemble functions in that they are named program units that can be invoked repeatedly. The primary difference is that functions can be used in a SQL statement whereas procedures cannot. Another difference is that the procedure can return multiple values whereas a function should only return a single value. The procedure begins with a mandatory heading part to hold the procedure name and optionally the procedure parameter list. Next come the declarative, executable and exception-handling parts, as in the PL/SQL Anonymous Block.
A procedure may also be created in a PL/SQL package - this is called a Package Procedure. A procedure created in a PL/SQL anonymous block is called a nested procedure. The standalone or package procedures, stored in the database, are referred to as "stored procedures".
Procedures can have three types of parameters: IN, OUT and IN OUT.
1. An IN parameter is used as input only. An IN parameter is passed by reference, though it can be changed by the inactive program.
2. An OUT parameter is initially NULL. The program assigns the parameter a value and that value is returned to the calling program.
3. An IN OUT parameter may or may not have an initial value. That initial value may or may not be modified by the called program. Any changes made to the parameter are returned to the calling program by default by copying but - with the NOCOPY hint - may be passed by reference.
PL/SQL also supports external procedures via the Oracle database's standard process
Packages
Packages are groups of conceptually linked functions, procedures, variables, PL/SQL table and record TYPE statements, constants, cursors etc. The use of packages promotes re-use of code. Packages are composed of the package specification and an optional package body. The specification is the interface to the application; it declares the types, variables, constants, exceptions, cursors, and subprograms available. The body fully defines cursors and subprograms, and so implements the specification. Two advantages of packages are:
1. Modular approach, encapsulation/hiding of business logic, security, performance improvement, re-usability. They support object-oriented programming features like function overloading and encapsulation.
2. Using package variables one can declare session level (scoped) variables, since variables declared in the package specification have a session scope.