Introduction to PL/SQL

PL/SQL is a Procedural Language. It is a procedural extension of Oracle-SQL that offers language constructs similar to those in imperative programming languages. PL/SQL allows its users to develop complex database applications that require the usage of control structures and procedural elements such as procedures, function, modules etc

.

Limitations of SQL:

1.      It doesn’t offers procedural capabilities.

2.      No error handling procedures are supported by SQL.

3.      Processing in SQL is Time-Consuming.

4.      Network traffic is high in SQL.

5.      SQL commands can’t be shared by multiple applications.

Advantages of PL/SQL over SQL:

1.      It has procedural capabilities.

2.      It supports error-handling procedures.

3.      Processing in PL/SQL is less time consuming.

4.      Use of PL/SQL reduces the network traffic.

5.      PL/SQL promotes sharing of programs by multiple applications.

Block: PL/SQL block is a set of related procedural SQL statements, enclosed within the reserved words BEGIN and END. PL/SQL supports two types of blocks.

1.      Anonymous Block: these are the block without header. They are not stored in the database.

2.      Named Block: these are the block having headers or labels. They are stored in the database. Named blocks can be procedures, functions or triggers.

Reserved words: are the predefined words that are assigned some specific and special meaning. These words can’t be used as identifiers (variable, function name, procedure name etc.) in PL/SQL. E.g Declare, Begin, For, Loop, End, End Loop, End If, Then Etc.

Comment: comments are the non-executable statements in PL/SQL. These are used to increase the readability of the program. There are two types of comments supported by PL/SQL.

1.      Single line comment: these comments start with the sign ‘- -‘

2.      Multiple Line Comment: these comments start with /* and end with */.

Identifier: refers to the name of the object in PL/SQL. E.g Variable, Cursors, Procedures, Functions etc.

Delimiters: are the symbols that have special meaning in PL/SQL. E.g assignment operator( := ), concatenation operator( || ), range( .. ),  +, -, *, /, exponential( ** ) etc.

Datatypes specify the storage format and valid range of value for a variable. PL/SQL provides following datatypes:

1.      Scalar type: it is atomic means it is not made up of other datatypes. e.g CHAR, VARCHAR2, NUMBER, DATE, BOOLEAN, LONG.

2.      Composite type: it is made up of other datatypes. it has some internal components that can be manipulated individually. E.g. TABLE, RECORD.

3.      Reference type: it holds values, called pointers that designate other program items.

Variable: is a named storage location in memory that is used to store the values that can be changed during the execution of the program. PL/SQL supports three types of variables.

1.      Local Variable: these types of variables are declared in the DECLARE block of PL/SQL code.

2.      Substitution Variable: these type of variables get automatically declared by prefixing ‘&’ sign to the variable. These variables are used to input the data at the run time.

3.      Bind Variable: these types of variables can be declared in SQL prompt and can be used with PL/SQL code by prefix the ‘:’ sign.

Anchored Declaration: when a variable is declared with reference to another variable’s datatype known as anchored declaration. PL/SQL uses %TYPE & %ROWTYPE declaration attributes for this. E.g.

Num1 number(5);

Num2 Num1%type;    /*declare variable Num2 having same datatype as that of Num1. */

Esal emp.sal%type;     /* declare variable Esal with the datatype similar to sal column of EMP table*/

Literal: is a value, which is not represented by an identifier. It is simply a value. E.g 18, 201 etc.

Constant: is that variable whose value is assign at the time of declaration and doesn’t change during the time of execution. E.g. pi constant number := 3.14

Printing in PL/SQL Block: to print the output with in the same line statement: DBMS_OUTPUT.PUT().

To print the output in different line statement: DBMS_OUTPUT.PUT_LINE()

SET SERVEROUTPUT ON is an environment variable with two possible values ON or OFF. The output generated by the DBMS_OUTPUT package is visible only when SERVEROUTPUT variable is ON.

PL/SQL BLOCK DIGRAM:

DECLARE

/*this block is used for variable declaration, cursor/record definition */

BEGIN

/*this block is used to define the SQL-PL/SQL statements*/

EXCEPTION

/* all the exception handling statements written in this block */

END;

CONTROL CONSTRUCTS IN PL/SQL:

1. Sequence: means the statements are being executed sequentially. This is the default follow of statements.

Write a PL/SQL program to input two no’s and display the total and average of these no.

DECLARE

NO1 NUMBER;                     --LOCAL VARIABLE

NO2 NUMBER;

TOT NUMBER;

PER NUMBER;

BEGIN

NO1 := &N1;              --&N1 substitution variable

NO2 := &N2;              --&N2 substitution variable

TOT := NO1 + NO2;

PER := TOT /2;

DBMS_OUTPUT.PUT_LINE(‘TOTAL ‘ || TOT);

DBMS_OUTPUT.PUT_LINE(‘AVERAGE ‘ || PER);

END;