PL/SQL : Lets program our way into Database

* PL/SQL is Oracle Corporation's procedural extension to SQL. Its a 4th generation language which provides the fancy and powerful features of software engineering like overloading,data-encapsulation,collection types, exceptions and information hiding.

--------------

* The basic unit of PL/SQL program is Block. There are 2 types of blocks : Anonymous and Named. Subprograms fall in the category of named blocks. The basic structure of a block is :

DECLARE

-- Declaration block (optional)

BEGIN

-- Programming logic

EXCEPTION

-- Exception-handling (optional)

END

----------

* Subprograms are named PL/SQL blocks that can be called with a set of parameters. PL/SQL has 2 types of subprograms:procedures and functions. Generally, a function is used to calculate a value while procedure is used to perform an action. Procedures act like new statements while functions act like new expressions or operators.

--------

* Package is a database object that bundles logically related data-types , variables , cursors , and subprograms together. It defines a simple clear interface to a set of related procedures and types that can be accessed by SQL statements. Packages usually have 2 parts : a specification and a body. The specification defines the application programming interface ; it defines the types, constants, variables, exceptions, cursors and subprograms. The body fills in the SQL queries for cursors and the code for subprograms. Packages are stored in databases where they can be shared by many applications. When a packaged subprogram is called for the first time, the whole of package gets loaded and cached into the memory thereby saving lots of time on disk I/O for subsequent calls.

-----

* Difference between CHAR and VARCHAR2 Datatypes:

When you assign a character value to a CHAR variable, if the value is shorter than the declared length of the variable, PL/SQL blank-pads the value to the declared length. Information about trailing blanks in the original value is lost. If the character value is longer than the declared length of the CHAR variable, PL/SQL aborts the assignment and raises the predefined exception VALUE_ERROR. PL/SQL neither truncates the value nor tries to trim trailing blanks. However , when you assign a character value to a VARCHAR2 variable, if the value is shorter than the declared length of the variable, PL/SQL neither blank-pads the value nor strips trailing blanks. Character values are assigned intact, so no information is lost. If the character value is longer than the declared length of the VARCHAR2 variable, PL/SQL aborts the assignment and raises VALUE_ERROR. PL/SQL neither truncates the value nor tries to trim trailing blanks.

--

* PL/SQL Collections:

A collection is an ordered group of elements all of the same type. Its a general concept that covers lists , arrays and other datatypes used in programming algorithms. PL/SQL offers 3 types of collections:

1. Nested Tables

2. Associative Arrays(Index-by Tables)

3. Varrays

Variables whose type is either nested table or Varray must be initialized with a constructor before they can be used.

-

* Differences between Nested Table and Array:

a> Nested Tables do not have a declared number of elements, while arrays have a predefined number.

b> Nested tables may not have consecutive subscripts, while arrays are always dense (have consecutive subscripts). Initailly, nested tables are dense, but they can become parse.

-

*Constructor: Constructors are used to initialize an object. It is a system-defined function whose name is same as that of its object. While an ordinary function returns some type, a constructor returns self as result. In PL/SQL , a constructor is used to initialize a nested table or a varray. Until a nested table or varray is initialized , it is automatically null. (The collection itself is null and not its elements)

-

*Transaction: A transaction is a series of SQL DML statements that does a logical unit of work. The statements either fail or suceed in a group. e.g two update statements might credit one bank account and debit another one. It is important not to allow one operation to succeed while the other one fails.