13-03-2014, 03:23 PM
Procedural Language extension of SQL
Language extension.ppt (Size: 302.5 KB / Downloads: 9)
[b]What is PL/SQL?[/b]
PL/SQL stands for Procedural Language extension of SQL.
PL/SQL is a combination of SQL along with the procedural features of programming languages. It was developed by Oracle Corporation in the early 90’s to enhance the capabilities of SQL.
The PL/SQL Engine:
Oracle uses a PL/SQL engine to processes the PL/SQL statements. A PL/SQL code can be stored in the client system (client-side) or in the database (server-side).
Declaration Section:
The Declaration section of a PL/SQL Block starts with the reserved keyword DECLARE. This section is optional and is used to declare any placeholders like variables, constants, records and cursors, which are used to manipulate data in the execution section. Placeholders may be any of Variables, Constants and Records, which stores data temporarily. Cursors are also declared in this section.
Execution Section:
The Execution section of a PL/SQL Block starts with the reserved keyword BEGIN and ends with END. This is a mandatory section and is the section where the program logic is written to perform any task. The programmatic constructs like loops, conditional statement and SQL statements form the part of execution section.
Exception Section:
The Exception section of a PL/SQL Block starts with the reserved keyword EXCEPTION. This section is optional. Any errors in the program can be handled in this section, so that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot be handled, the Block terminates abruptly with errors.
Advantages of PL/SQL
These are the advantages of PL/SQL.
Block Structures: PL SQL consists of blocks of code, which can be nested within each other. Each block forms a unit of a task or a logical module. PL/SQL Blocks can be stored in the database and reused.
Procedural Language Capability: PL SQL consists of procedural language constructs such as conditional statements (if else statements) and loops like (FOR loops).
Better Performance: PL SQL engine processes multiple SQL statements simultaneously as a single block, thereby reducing network traffic.
Error Handling: PL/SQL handles errors or exceptions effectively during the execution of a PL/SQL program. Once an exception is caught, specific actions can be taken depending upon the type of the exception or it can be displayed to the user with a message.
PL/SQL Variables
These are placeholders that store the values that can change through the PL/SQL Block.
The General Syntax to declare a variable is:
variable_name datatype [NOT NULL := value ];
variable_name is the name of the variable.
datatype is a valid PL/SQL datatype.
NOT NULL is an optional specification on the variable.
value or DEFAULT valueis also an optional specification, where you can initialize a variable.
Each variable declaration is a separate statement and must be terminated by a semicolon.
PL/SQL Constants
As the name implies a constant is a value used in a PL/SQL Block that remains unchanged throughout the program. A constant is a user-defined literal value. You can declare a constant and use it instead of actual value.
For example: If you want to write a program which will increase the salary of the employees by 25%, you can declare a constant and use it throughout the program. Next time when you want to increase the salary again you can change the value of the constant which will be easier than changing the actual value throughout the program.
Conditional Statements in PL/SQL
As the name implies, PL/SQL supports programming language features like conditional statements, iterative statements.
The programming constructs are similar to how you use in programming languages like Java and C++. In this section I will provide you syntax of how to use conditional statements in PL/SQL programming.
IF THEN ELSE STATEMENT
1)IF condition THEN statement 1; ELSE statement 2; END IF;
2)IF condition 1 THEN statement 1; statement 2; ELSIF condtion2 THEN statement 3; ELSE statement 4; END IF
3)IF condition 1 THEN statement 1; statement 2; ELSIF condtion2 THEN statement 3; ELSE statement 4; END IF;
4)IF condition1 THEN ELSE IF condition2 THEN statement1; END IF; ELSIF condition3 THEN statement2; END IF;