14-12-2012, 01:29 PM
PL/SQL
PLSQl.docx (Size: 73.53 KB / Downloads: 19)
What is PL/SQL?
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).
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).
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 Placeholders
Placeholders are temporary storage area. Placeholders can be any of Variables, Constants and Records. Oracle defines placeholders to store data temporarily, which are used to manipulate data during the execution of a PL SQL block.
Depending on the kind of data you want to store, you can define placeholders with a name and a datatype. Few of the datatypes used to define placeholders are as given below.
Number (n,m) , Char (n) , Varchar2 (n) , Date , Long , Long raw, Raw, Blob, Clob, Nclob, Bfile
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.
Scope of Variables
PL/SQL allows the nesting of Blocks within Blocks i.e, the Execution section of an outer block can contain inner blocks. Therefore, a variable which is accessible to an outer Block is also accessible to all nested inner Blocks. The variables declared in the inner blocks are not accessible to outer blocks. Based on their declaration we can classify variables into two types.
• Local variables - These are declared in a inner block and cannot be referenced by outside Blocks.
• Global variables - These are declared in a outer block and can be referenced by its itself and by its inner blocks.
For Example: In the below example we are creating two variables in the outer block and assigning thier product to the third variable created in the inner block. The variable 'var_mult' is declared in the inner block, so cannot be accessed in the outer block i.e. it cannot be accessed after line 11. The variables 'var_num1' and 'var_num2' can be accessed anywhere in the block.
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.