Tuesday, October 21, 2014

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.



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


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


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

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.
NO1 NUMBER;                     --LOCAL VARIABLE
NO1 := &N1;              --&N1 substitution variable
NO2 := &N2;              --&N2 substitution variable
TOT := NO1 + NO2;
PER := TOT /2;

No comments: