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.
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.
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 ‘- -‘
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.
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.
Num2 Num1%type; /*declare variable Num2 having same datatype as that of Num1. */
Literal: is a value, which is not represented by an identifier. It is simply a value. E.g 18, 201 etc.
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(
PL/SQL BLOCK DIGRAM:
/*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 */
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.
NO1 NUMBER; --LOCAL VARIABLE
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);