INTRODUCTION TO PL/SQL
PL/SQL is Oracle’s procedural linguistic communication extension to SQL. PL/SQL allows you lot to mix SQL statements amongst procedural statements similar IF statement, Looping structures etc. PL/SQL is the superset of SQL. It uses SQL for information retrieval in addition to manipulation in addition to uses its ain statements for information processing.
PL/SQL plan units are by in addition to large categorized equally follows:
· Anonymous blocks
· Stored procedures
Anonymous block:
This is a PL/SQL block that appears inside your application. In many applications PL/SQL blocks tin seem where SQL statements tin appear. Such blocks are called equally Anonymous blocks
Stored Procedure:
This is a PL/SQL block that is stored inwards the database amongst a name. Application programs tin execute these procedures using the name. Oracle also allows you lot to create functions, which are same equally procedures but render a value, in addition to packages, which are a collection of procedures in addition to functions.
Need for PL/SQL:
SQL statements are defined inwards term of constraints nosotros want to cook on the resultant of a query. Such a linguistic communication is usually referred to equally declarative. This contrasts amongst the thence called procedural languages where a plan specifies a listing of operations to hold out performed sequentially to attain the desired result. PL/SQL adds selective (i.e. if...then...else...) in addition to iterative constructs (i.e. loops) to SQL.
PL/SQL is almost useful to write triggers in addition to stored procedures. Stored procedures are units of procedural code stored inwards a compiled shape inside the database,
PL/SQL Architecture:
Every PL/SQL block is offset executed past times PL/SQL engine. This is the engine that compiles in addition to executes PL/SQL blocks. PL/SQL engine is available inwards Oracle Server in addition to certainly Oracle tools such equally Oracle Forms in addition to Oracle Reports.
PL/SQL engine executes all procedural statements ofa PL/SQL of the block, but sends SQL command to SQL statements executorin the Oracle RDBMS. That agency PL/SQL separates SQL commands from PL/SQL commands in addition to executes PL/SQL commands using Procedural tilt executor, which is a component subdivision of PL/SQL engine.
PL/SQL Architecture
Features of PL/SQL:
The next are of import features of PL/SQL.
Block structure
PL/SQL is a block-structured language. Each plan written inwards PL/SQL is written equally a block. Blocks tin also hold out nested. Each block is meant for a item task.
Variables in addition to constants
PL/SQL allows you lot to declare variables in addition to constants. Variables are used to shop values temporarily.Variables in addition to constants tin hold out used inwards SQL in addition to PL/SQL procedural statements only similar an expression.
Control structures
PL/SQL allows command structures similar IF statement, FOR loop, WHILE loop to hold out used inwards the block. Control structures are almost of import extension to SQL inwards PL/SQL. Control structures allow whatsoever information physical care for possible inwards PL/SQL.
Exception handling
PL/SQL allows errors, called equally exceptions, to hold out detected in addition to handled. Whenever in that location is a predefined mistake PL/SQL raises an exception automatically. These exceptions tin hold out handled to recover from errors.
Modularity
PL/SQL allows physical care for to hold out divided into dissimilar modules. Subprograms called equally procedures in addition to functions tin hold out defined in addition to invoked using the name. These subprograms tin also get got arameters.
Cursors
Influenza A virus subtype H5N1 cursor is a someone SQL expanse used to execute SQL statements in addition to shop processing information. PL/SQLimplicitly uses cursors for all DML commands in addition to SELECT command that returns alone 1 row. And it also allows you lot to define explicit cursor to bargain amongst multiple row queries.
Built-in functions
Most of the SQL functions that nosotros get got seen thence far inwards SQL are available inwards PL/SQL. These functions tin hold out used to manipulate variables of PL/SQL.
Advantages Of PL/SQL
A Simple these are the Advantages of PL/SQL:
Block Structures: PL SQL consists of blocks of code, which tin hold out nested inside each other. Each block forms a unit of measurement of a describe or a logical module. PL/SQL Blocks tin hold out stored inwards the database in addition to reused.
Procedural Language Capability: PL SQL consists of procedural linguistic communication constructs such equally conditional statements (if else statements) in addition to loops similar (FOR loops).
Better Performance: PL SQL engine processes multiple SQL statements simultaneously equally a unmarried 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 tin hold out taken depending upon the type of the exception or it tin hold out displayed to the user amongst a message.
PL/SQL Block:
Each PL/SQL plan consists of SQL in addition to PL/SQL statements which from a PL/SQL block.
The Declaration department (optional).
The Execution department (mandatory).
The Exception Handling (or Error) department (optional).
Declaration Section:
The Declaration department of a PL/SQL Block starts amongst the reserved keyword DECLARE. This department is optional in addition to is used to declare whatsoever placeholders similar variables, constants, records in addition to cursors, which are used to manipulate information inwards the execution section. Placeholders may hold out whatsoever of Variables, Constants in addition to Records, which stores information temporarily. Cursors are also declared inwards this section.
Execution Section:
The Execution department of a PL/SQL Block starts amongst the reserved keyword BEGIN in addition to ends amongst END. This is a mandatory department in addition to is the department where the plan logic is written to perform whatsoever task. The programmatic constructs similar loops, conditional tilt in addition to SQL statements shape the component subdivision of execution section.
Exception Section:
The Exception department of a PL/SQL Block starts amongst the reserved keyword EXCEPTION. This department is optional. Any errors inwards the plan tin hold out handled inwards this section, thence that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot hold out handled, the Block terminates abruptly amongst errors.
Every tilt inwards the inwards a higher house 3 sections must terminate amongst a semicolon ; . PL/SQL blocks tin hold out nested inside other PL/SQL blocks. Comments tin hold out used to document code.
PL/SQL Data types:
PL/SQL provides a diversity of predefined information types, which tin hold out divided into 4 categories:
Scalar- Represents a unmarried value.
Composite-Is a collection of components
Reference -Is a pointer that points to around other item.
LOB -Holds a lob locator.
The next are the information types inwards diverse categoryies
Scalar -NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN
Composite -RECORD, TABLE in addition to VARRAY.
Reference -REF CURSOR, REF Object type
LOB -BFILE, BLOB, CLOB, in addition to NCLOB.
PL/SQL Variables in addition to Constants:
Variables:
Influenza A virus subtype H5N1 variable is nil but a refer given to a storage expanse that our programs tin manipulate. Each variable inwards PL/SQL has a specific information type, which determines the size in addition to layout of the variable's memory; the make of values that tin hold out stored inside that retentiveness in addition to the prepare of operations that tin hold out applied to the variable.
The refer of a PL/SQL variable consists of a missive of the alphabet optionally followed past times to a greater extent than letters, numerals, dollar signs, underscores, in addition to disclose signs in addition to should non top xxx characters. By default, variable names are non case-sensitive. You cannot job a reserved PL/SQL keyword equally a variable name.
PL/SQL programming linguistic communication allows to define diverse types of variables, which nosotros volition comprehend inwards subsequent chapters similar appointment fourth dimension information types, records, collections, etc. For this chapter, permit us report alone basic variable types.
Variable Declaration inwards PL/SQL
PL/SQL variables must hold out declared inwards the proclamation department or inwards a bundle equally a global variable. When you lot declare a variable, PL/SQL allocates retentiveness for the variable's value in addition to the storage location is identified past times the variable name.
The syntax for declaring a variable is:
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
Initializing Variables inwards PL/SQL
Whenever you lot declare a variable, PL/SQL assigns it a default value of NULL. If you lot want to initialize a variable amongst a value other than the NULL value, you lot tin do thence during the declaration, using either of the following:
· The DEFAULT keyword
· The assignment operator
For example:
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';
You tin also specify that a variable should non get got a NULL value using the NOT NULL constraint. If you lot job the NOT NULL constraint, you lot must explicitly assign an initial value for that variable.
It is a proficient programming practise to initialize variables properly otherwise, sometimes plan would make unexpected result. Try the next illustration which makes job of diverse types of variables:
DECLARE
a integer := 10;
b integer := 20;
c integer;
f real;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;
/
Scope of Pl/SQL Variables:
PL/SQL allows the nesting of Blocks inside Blocks i.e, the Execution department of an outer block tin incorporate inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared inwards the inner blocks are non accessible to outer blocks. Based on their proclamation nosotros tin form variables into ii types.
Local variables - These are declared inwards a inner block in addition to cannot hold out referenced past times exterior Blocks.
Global variables - These are declared inwards a outer block in addition to tin hold out referenced past times its itself in addition to past times its inner blocks.
For Example: In the below illustration nosotros are creating ii variables inwards the outer block in addition to assigning thier production to the tertiary variable created inwards the inner block. The variable 'var_mult' is declared inwards the inner block, thence cannot hold out accessed inwards the outer block i.e. it cannot hold out accessed after describe 11. The variables 'var_num1' in addition to 'var_num2' tin hold out accessed anywhere inwards the block.
1> DECLARE
2> var_num1 number;
3> var_num2 number;
4> BEGIN
5> var_num1 := 100;
6> var_num2 := 200;
7> DECLARE
8> var_mult number;
9> BEGIN
10> var_mult := var_num1 * var_num2;
11> END;
12> END;
13> /
PL/SQL Constants:
As the refer implies a constant is a value used inwards a PL/SQL Block that remains unchanged throughout the program. Influenza A virus subtype H5N1 constant is a user-defined literal value. You tin declare a constant in addition to job it instead of actual value.
For example: If you lot want to write a plan which volition increment the salary of the employees past times 25%, you lot tin declare a constant in addition to job it throughout the program. Next fourth dimension when you lot want to increment the salary 1 time again you lot tin modify the value of the constant which volition hold out easier than changing the actual value throughout the program.
General Syntax to declare a constant is:
constant_name CONSTANT datatype := VALUE;
constant_name is the refer of the constant i.e. similar to a variable name.
The give-and-take CONSTANT is a reserved give-and-take in addition to ensures that the value does non change.
VALUE - It is a value which must hold out assigned to a constant when it is declared. You cannot assign a value later.
For example, to declare salary_increase, you lot tin write code equally follows:
DECLARE
salary_increase CONSTANT disclose (3) := 10;
You must assign a value to a constant at the fourth dimension you lot declare it. If you lot do non assign a value to a constant piece declaring it in addition to endeavour to assign a value inwards the execution section, you lot volition instruct a error. If you lot execute the below Pl/SQL block you lot volition instruct error.
DECLARE
salary_increase CONSTANT number(3);
BEGIN
salary_increase := 100;
dbms_output.put_line (salary_increase);
END;
Sumber http://www.gcreddy.com/
PL/SQL is Oracle’s procedural linguistic communication extension to SQL. PL/SQL allows you lot to mix SQL statements amongst procedural statements similar IF statement, Looping structures etc. PL/SQL is the superset of SQL. It uses SQL for information retrieval in addition to manipulation in addition to uses its ain statements for information processing.
PL/SQL plan units are by in addition to large categorized equally follows:
· Anonymous blocks
· Stored procedures
Anonymous block:
This is a PL/SQL block that appears inside your application. In many applications PL/SQL blocks tin seem where SQL statements tin appear. Such blocks are called equally Anonymous blocks
Stored Procedure:
This is a PL/SQL block that is stored inwards the database amongst a name. Application programs tin execute these procedures using the name. Oracle also allows you lot to create functions, which are same equally procedures but render a value, in addition to packages, which are a collection of procedures in addition to functions.
Need for PL/SQL:
SQL statements are defined inwards term of constraints nosotros want to cook on the resultant of a query. Such a linguistic communication is usually referred to equally declarative. This contrasts amongst the thence called procedural languages where a plan specifies a listing of operations to hold out performed sequentially to attain the desired result. PL/SQL adds selective (i.e. if...then...else...) in addition to iterative constructs (i.e. loops) to SQL.
PL/SQL is almost useful to write triggers in addition to stored procedures. Stored procedures are units of procedural code stored inwards a compiled shape inside the database,
PL/SQL Architecture:
Every PL/SQL block is offset executed past times PL/SQL engine. This is the engine that compiles in addition to executes PL/SQL blocks. PL/SQL engine is available inwards Oracle Server in addition to certainly Oracle tools such equally Oracle Forms in addition to Oracle Reports.
PL/SQL engine executes all procedural statements ofa PL/SQL of the block, but sends SQL command to SQL statements executorin the Oracle RDBMS. That agency PL/SQL separates SQL commands from PL/SQL commands in addition to executes PL/SQL commands using Procedural tilt executor, which is a component subdivision of PL/SQL engine.
PL/SQL Architecture
Features of PL/SQL:
The next are of import features of PL/SQL.
Block structure
PL/SQL is a block-structured language. Each plan written inwards PL/SQL is written equally a block. Blocks tin also hold out nested. Each block is meant for a item task.
Variables in addition to constants
PL/SQL allows you lot to declare variables in addition to constants. Variables are used to shop values temporarily.Variables in addition to constants tin hold out used inwards SQL in addition to PL/SQL procedural statements only similar an expression.
Control structures
PL/SQL allows command structures similar IF statement, FOR loop, WHILE loop to hold out used inwards the block. Control structures are almost of import extension to SQL inwards PL/SQL. Control structures allow whatsoever information physical care for possible inwards PL/SQL.
Exception handling
PL/SQL allows errors, called equally exceptions, to hold out detected in addition to handled. Whenever in that location is a predefined mistake PL/SQL raises an exception automatically. These exceptions tin hold out handled to recover from errors.
Modularity
PL/SQL allows physical care for to hold out divided into dissimilar modules. Subprograms called equally procedures in addition to functions tin hold out defined in addition to invoked using the name. These subprograms tin also get got arameters.
Cursors
Influenza A virus subtype H5N1 cursor is a someone SQL expanse used to execute SQL statements in addition to shop processing information. PL/SQLimplicitly uses cursors for all DML commands in addition to SELECT command that returns alone 1 row. And it also allows you lot to define explicit cursor to bargain amongst multiple row queries.
Built-in functions
Most of the SQL functions that nosotros get got seen thence far inwards SQL are available inwards PL/SQL. These functions tin hold out used to manipulate variables of PL/SQL.
Advantages Of PL/SQL
A Simple these are the Advantages of PL/SQL:
Block Structures: PL SQL consists of blocks of code, which tin hold out nested inside each other. Each block forms a unit of measurement of a describe or a logical module. PL/SQL Blocks tin hold out stored inwards the database in addition to reused.
Procedural Language Capability: PL SQL consists of procedural linguistic communication constructs such equally conditional statements (if else statements) in addition to loops similar (FOR loops).
Better Performance: PL SQL engine processes multiple SQL statements simultaneously equally a unmarried 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 tin hold out taken depending upon the type of the exception or it tin hold out displayed to the user amongst a message.
PL/SQL Block:
Each PL/SQL plan consists of SQL in addition to PL/SQL statements which from a PL/SQL block.
The Declaration department (optional).
The Execution department (mandatory).
The Exception Handling (or Error) department (optional).
Declaration Section:
The Declaration department of a PL/SQL Block starts amongst the reserved keyword DECLARE. This department is optional in addition to is used to declare whatsoever placeholders similar variables, constants, records in addition to cursors, which are used to manipulate information inwards the execution section. Placeholders may hold out whatsoever of Variables, Constants in addition to Records, which stores information temporarily. Cursors are also declared inwards this section.
Execution Section:
The Execution department of a PL/SQL Block starts amongst the reserved keyword BEGIN in addition to ends amongst END. This is a mandatory department in addition to is the department where the plan logic is written to perform whatsoever task. The programmatic constructs similar loops, conditional tilt in addition to SQL statements shape the component subdivision of execution section.
Exception Section:
The Exception department of a PL/SQL Block starts amongst the reserved keyword EXCEPTION. This department is optional. Any errors inwards the plan tin hold out handled inwards this section, thence that the PL/SQL Blocks terminates gracefully. If the PL/SQL Block contains exceptions that cannot hold out handled, the Block terminates abruptly amongst errors.
Every tilt inwards the inwards a higher house 3 sections must terminate amongst a semicolon ; . PL/SQL blocks tin hold out nested inside other PL/SQL blocks. Comments tin hold out used to document code.
PL/SQL Data types:
PL/SQL provides a diversity of predefined information types, which tin hold out divided into 4 categories:
Scalar- Represents a unmarried value.
Composite-Is a collection of components
Reference -Is a pointer that points to around other item.
LOB -Holds a lob locator.
The next are the information types inwards diverse categoryies
Scalar -NUMBER, CHAR, VARCHAR2, DATE, BOOLEAN
Composite -RECORD, TABLE in addition to VARRAY.
Reference -REF CURSOR, REF Object type
LOB -BFILE, BLOB, CLOB, in addition to NCLOB.
PL/SQL Variables in addition to Constants:
Variables:
Influenza A virus subtype H5N1 variable is nil but a refer given to a storage expanse that our programs tin manipulate. Each variable inwards PL/SQL has a specific information type, which determines the size in addition to layout of the variable's memory; the make of values that tin hold out stored inside that retentiveness in addition to the prepare of operations that tin hold out applied to the variable.
The refer of a PL/SQL variable consists of a missive of the alphabet optionally followed past times to a greater extent than letters, numerals, dollar signs, underscores, in addition to disclose signs in addition to should non top xxx characters. By default, variable names are non case-sensitive. You cannot job a reserved PL/SQL keyword equally a variable name.
PL/SQL programming linguistic communication allows to define diverse types of variables, which nosotros volition comprehend inwards subsequent chapters similar appointment fourth dimension information types, records, collections, etc. For this chapter, permit us report alone basic variable types.
Variable Declaration inwards PL/SQL
PL/SQL variables must hold out declared inwards the proclamation department or inwards a bundle equally a global variable. When you lot declare a variable, PL/SQL allocates retentiveness for the variable's value in addition to the storage location is identified past times the variable name.
The syntax for declaring a variable is:
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
Initializing Variables inwards PL/SQL
Whenever you lot declare a variable, PL/SQL assigns it a default value of NULL. If you lot want to initialize a variable amongst a value other than the NULL value, you lot tin do thence during the declaration, using either of the following:
· The DEFAULT keyword
· The assignment operator
For example:
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';
You tin also specify that a variable should non get got a NULL value using the NOT NULL constraint. If you lot job the NOT NULL constraint, you lot must explicitly assign an initial value for that variable.
It is a proficient programming practise to initialize variables properly otherwise, sometimes plan would make unexpected result. Try the next illustration which makes job of diverse types of variables:
DECLARE
a integer := 10;
b integer := 20;
c integer;
f real;
BEGIN
c := a + b;
dbms_output.put_line('Value of c: ' || c);
f := 70.0/3.0;
dbms_output.put_line('Value of f: ' || f);
END;
/
Scope of Pl/SQL Variables:
PL/SQL allows the nesting of Blocks inside Blocks i.e, the Execution department of an outer block tin incorporate inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared inwards the inner blocks are non accessible to outer blocks. Based on their proclamation nosotros tin form variables into ii types.
Local variables - These are declared inwards a inner block in addition to cannot hold out referenced past times exterior Blocks.
Global variables - These are declared inwards a outer block in addition to tin hold out referenced past times its itself in addition to past times its inner blocks.
For Example: In the below illustration nosotros are creating ii variables inwards the outer block in addition to assigning thier production to the tertiary variable created inwards the inner block. The variable 'var_mult' is declared inwards the inner block, thence cannot hold out accessed inwards the outer block i.e. it cannot hold out accessed after describe 11. The variables 'var_num1' in addition to 'var_num2' tin hold out accessed anywhere inwards the block.
1> DECLARE
2> var_num1 number;
3> var_num2 number;
4> BEGIN
5> var_num1 := 100;
6> var_num2 := 200;
7> DECLARE
8> var_mult number;
9> BEGIN
10> var_mult := var_num1 * var_num2;
11> END;
12> END;
13> /
PL/SQL Constants:
As the refer implies a constant is a value used inwards a PL/SQL Block that remains unchanged throughout the program. Influenza A virus subtype H5N1 constant is a user-defined literal value. You tin declare a constant in addition to job it instead of actual value.
For example: If you lot want to write a plan which volition increment the salary of the employees past times 25%, you lot tin declare a constant in addition to job it throughout the program. Next fourth dimension when you lot want to increment the salary 1 time again you lot tin modify the value of the constant which volition hold out easier than changing the actual value throughout the program.
General Syntax to declare a constant is:
constant_name CONSTANT datatype := VALUE;
constant_name is the refer of the constant i.e. similar to a variable name.
The give-and-take CONSTANT is a reserved give-and-take in addition to ensures that the value does non change.
VALUE - It is a value which must hold out assigned to a constant when it is declared. You cannot assign a value later.
For example, to declare salary_increase, you lot tin write code equally follows:
DECLARE
salary_increase CONSTANT disclose (3) := 10;
You must assign a value to a constant at the fourth dimension you lot declare it. If you lot do non assign a value to a constant piece declaring it in addition to endeavour to assign a value inwards the execution section, you lot volition instruct a error. If you lot execute the below Pl/SQL block you lot volition instruct error.
DECLARE
salary_increase CONSTANT number(3);
BEGIN
salary_increase := 100;
dbms_output.put_line (salary_increase);
END;