Download DBMS notes by gajendra jingar and more Thesis Database Management Systems (DBMS) in PDF only on Docsity!
UNIT- 5
PL SQL basically stands for "Procedural Language extensions to SQL". This is the extension of Structured Query Language (SQL) that is used in Oracle. Unlike SQL, PL/SQL allows the programmer to write code in procedural format. It combines the data manipulation power of SQL with the processing power of procedural language to create a super powerful SQL queries. It allows the programmers to instruct the compiler 'what to do' through SQL and 'how to do' through its procedural way. Similar to other database languages, it gives more control to the programmers by the use of loops, conditions and object oriented concepts. Architecture of PL/SQL The PL/SQL architecture mainly consists of following 3 components:
- PL/SQL block
- PL/SQL Engine
- Database Server PL/SQL block: This is the component which has the actual PL/SQL code. This consists of different sections to divide the code logically (declarative section for declaring purpose, execution section for processing statements, exception handling section for handling errors) It also contains the SQL instruction that used to interact with the database server. All the PL/SQL units are treated as PL/SQL blocks, and this is the starting stage of the architecture which serves as the primary input. Following are the different type of PL/SQL units. o Anonymous Block o Function o Library o Procedure o Package Body o Package Specification o Trigger o Type o Type Body PL/SQL Engine PL/SQL engine is the component where the actual processing of the codes takes place. PL/SQL engine separates PL/SQL units and SQL part in the input (as shown in the image below). The separated PL/SQL units will be handled with the PL/SQL engine itself. The SQL part will be sent to database server where the actual interaction with database takes place. It can be installed in both database server and in the application server.
Database Server: This is the most important component of Pl/SQL unit which stores the data. The PL/SQL engine uses the SQL from PL/SQL units to interact with the database server. It consists of SQL executor which actually parses the input SQL statements and execute the same.
Advantage of Using PL/SQL
- Better performance, as sql is executed in bulk rather than a single statement
- High Productivity
- Tight integration with SQL
- Full Portability
- Tight Security
- Support Object Oriented Programming concepts.
Basic Difference between SQL and PL/SQL In this section, we will discuss some differences between SQL and PL/SQL SQL PL/SQL
SQL is a single query that is used to perform DML and DDL operations.
PL/SQL is a block of codes that used to write the entire program blocks/ procedure/ function, etc.
It is declarative, that defines what needs to be done, rather than how things need to be done.
PL/SQL is procedural that defines how the things needs to be done.
Execute as a single statement. Execute as a whole block.
Mainly used to manipulate data. Mainly used to create an application.
Interaction with Database server. No interaction with the database server.
Cannot contain PL/SQL code in it. It is an extension of SQL, so it can contain SQL inside it.
Execution Section
Execution part is the main and mandatory part which actually executes the code that is written inside it. Since the PL/SQL expects the executable statements from this block this cannot be an empty block, i.e., it should have at least one valid executable code line in it. Below are few more characteristics of this part. This can contain both PL/SQL code and SQL code. This can contain one or many blocks inside it as a nested blocks. This section starts with the keyword 'BEGIN'. This section should be followed either by 'END' or Exception-Handling section (if present)
Exception-Handling Section:
The exception are unavoidable in the program which occurs at run-time and to handle this Oracle has provided an Exception-handling section in blocks. This section can also contain PL/SQL statements. This is an optional section of the PL/SQL blocks. This is the section where the exception raised in the execution block is handled. This section is the last part of the PL/SQL block. Control from this section can never return to the execution block. This section starts with the keyword 'EXCEPTION'. This section should be always followed by the keyword 'END'.
DATA TYPE
In oracle, each value or constant is assigned with a data type. Any data type is associated with the specific storage format and range constraints. Basically, it defines how the data is stored, handled and treated by Oracle during the data storage and processing. The main difference between PL/SQL and SQL data types is, SQL data type are limited with table column while the PL/SQL data types are used in the PL/SQL blocks. More on this later in the tutorial.
CHARACTER Data type NUMBER Data type BOOLEAN Data type DATE Data type LOB Data type
CHARACTER Data type:
This data type basically stores alphanumeric characters in string format. The literal values should always be enclosed between single quotes while assigning them to CHARACTER data type. This character data type is further classified as follows: CHAR Data type (fixed string size) VARCHAR2 Data type (variable string size) VARCHAR Data type NCHAR (native fixed string size) NVARCHAR2 (native variable string size) LONG and LONG RAW CHAR Data type: This data type stores the string value, and the size of the string is fixed at the time of declaring the variable. Oracle will be blank-padded the variable if the variable didn't occupy the entire size that has been declared for it, hence oracle will allocate the memory for declared size even if the variable didn't occupy it fully. The size restriction for this data type is 1-2000 bytes. CHAR data type is more appropriate to use where ever fixed size of data will be handled. .
Syntax Explanation: The first declaration statement declares the variable 'grade' of CHAR data type with the maximum size of 1 byte (default value).
Syntax Explanation:
The above declaration statement declares the variable 'native' of NCHAR data type with the maximum size of 10. The length of this variable depends upon the (number of length) per byte as defined in the character set.
NVARCHAR2 Data type:
This data type is same as VARCHAR2 data type, but the character set will be of national character set.
This character set can be defined for the session using NLS_PARAMETERS. The character set can be either UTF16 or UTF8. The size restriction is 1-4000 bytes.
Syntax Explanation:
The above declaration statement declares the variable 'Native_var' of NVARCHAR2 data type with the maximum size of 10.
LONG and LONGRAW Data type:
This data type is used to store large text or raw data up to the maximum size of 2GB.
These are mainly used in the data dictionary. LONG data type is used to store character set data, while LONG RAW is used to store data in binary format. LONG RAW data type accept media objects, images, etc. whereas LONG works only on data that can be stored using character set.
Syntax Explanation:
The above declaration statement declares the variable 'Large_text' of LONG data type and 'Large_raw' of LONG RAW data type.
Note: Using LONG data type is not recommended by Oracle. Instead LOB data type should be preferred.
NUMBER Data type:
This data type stores fixed or floating point numbers up to 38 digits of precision. This data type is used to work with fields which will contain only number data. The variable can be declared either with precision and decimal digit details or without these information. Values need not to enclose within quotes while assigning for this data type.
Syntax Explanation:
In the above, the first declaration declares the variable 'A' is of number data type with total precision 8 and decimal digits 2. The second declaration declares the variable 'B' is of number data type with total precision 8 and no decimal digits. The third declaration is the most generic, declares variable 'C' is of number data type with no restriction in precision or decimal places. It can take up to a maximum of 38 digits.
BOOLEAN Data type:
This data type stores the logical values. It represents either TRUE or FALSE and mainly used in conditional statements. Values need not enclose within quotes while assigning for this data type.
Syntax Explanation:
In the above, variable 'Var1' is declared as BOOLEAN data type. The output of the code will be either true or false based on the condition set.
3. BFILE
BLOB:
This data type stores the LOB data in the binary file format up to the maximum size of 128 TB. This doesn't store data based on the character set details, so it can store the unstructured data such as multimedia objects, images, etc.
Syntax Explanation:
In the above, variable 'Binary_data' is declared as BLOB.
CLOB and NCLOB:
CLOB data type stores the LOB data into the character set, whereas NCLOB stores the data in the native character set. Since these data types uses character set based storage, these cannot store the data like multimedia, images, etc. that cannot be put into a character string. The maximum size of these data types is 128 TB.
Syntax Explanation:
In the above, variable 'Charac_data' is declared as CLOB data type.
BFILE:
BFILE are the data types that stored the unstructured binary format data outside the database as an operating-system file. The size of BFILE is to a limited operating system, and they are read-only files and can't be modified.
Complex Data Types in PL/SQL
Record Type Collection Constructor and Initialization Concept in Collections Collection Methods
PL/SQL Decision Making Statements
Decision making statements are important to write any complex programs. These
statements will decide the control flow at run time based on pre-defined conditions.
Introduction to Decision Making Statements
IF-THEN Statement
IF-THEN-ELSE Statement
IF-THEN-ELSIF Statement
NESTED-IF Statement
CASE Statement
SEARCHED CASE Statement
IF-THEN Statement The IF-THEN statement is mainly used to execute a particular section of codes only when the condition is satisfied. The condition should yield Boolean (True/False). It is a basic conditional statement which will allow the ORACLE to execute/skip a particular piece of code based on the pre-defined conditions.
Syntax Explanation: In the above syntax, keyword 'IF' will be followed by a condition which evaluates to 'TRUE'/'FALSE'. The control will execute the <action_block> only if the condition returns . In the case of condition evaluates to then, SQL will skip the <action_block> and it will start executing the code next to 'END IF' block. Note: Whenever condition evaluate to 'NULL', then SQL will treat 'NULL' as 'FALSE'. Example 1 : In this example, we are going to print a message when the number is greater than
- For that, we will execute the following code To print a message when a number has value more than 100, we execute the following code.
IF-THEN-ELSIF Statement The IF-THEN-ELSIF statement is mainly used where one alternative should be chosen from a set of alternatives, where each alternative has its own condition to be satisfied. The first condition that returns will be executed, and the remaining conditions will be skipped. The IF-THEN-ELSIF statement may contain 'ELSE' block in it. This 'ELSE' block will be executed if none of the condition is satisfied. Note : ELSE block is optional in this conditional statement. If there is no ELSE block, and none of the condition satisfied, then the controller will skip all the action block and start executing the remaining part of the code.
Syntax Explanation: In the above syntax, the control will execute the <action_block1> only if the condition returns . If condition1 is not satisfied, then the controller will check for condition2. The controller will exit from the IF-statement in the following two cases.
o When the controller found any condition that returns . In this case, the corresponding action_block will be executed and the controller will exit this IF- statement block and will start executing the remaining code. o When none of the condition satisfied, the then controller will execute ELSE block if present, then will exit from the IF-statement.
NESTED-IF Statement
The NESTED-IF statement is basically allows programmers to place one or more 'IF' condition inside another 'IF' condition's <action_block> other than normal statements. Each 'IF' condition should have a separate 'END IF' statement which marks the end-of- scope of that particular <action_block>. The 'IF' statement will consider the nearest 'END IF' statement as an endpoint for that particular condition. The pictorial representation for NESTED-IF is shown below diagram.
Syntax Explanation: In the above syntax, the outer IF contains one more IF statement in its action block. The condition1 returns , then control will be executing <action_block1> and checks the condition2. If condition2 also returns , then <action_block2> will also be executed. In case of condition2 evaluates to then, SQL will skip the <action_block2>
Example of Nested- If Statement: Greatest of three number
In this example, we are going to print the greatest of three numbers by
using Nested-If statement. The numbers will be assigned in the declare
part, as you can see in the code below, i.e Number= 10,15 and 20 and the
maximum number will be fetched using nested-if statements.
Each 'WHEN' clause is treated as an alternatives which have and <action_block>. The 'WHEN' clause which matches the value as that of the expression will be selected, and the corresponding <action_block> will be executed. 'ELSE' block is optional which hold the <action_block_default> that needs to be executed when none of the alternatives match the expression value. The 'END' marks the end of CASE statement and it is a mandatory part of CASE. Example 1: Arithmetic Calculation using Case In this example, we are going to do arithmetic calculation between two numbers 55 and 5.
SEARCHED CASE Statement
SEARCHED CASE statement is similar to CASE statement, rather than using the selector to select the alternative, SEARCHED CASE will directly have the expression defined in the WHEN clause. The first WHEN clause that satisfies the condition will be executed, and the controller will skip the remaining alternatives.
Syntax Explanation: In the above syntax, each WHEN clause has the separate and <action_block>.
The WHEN clause for which the expression returns TRUE will be executed. 'ELSE' block is optional which hold the <action_block_default> that needs to be executed when none of the alternatives satisfies. The 'END' marks the end of CASE statement and it is a mandatory part of CASE. Example 1: Arithmatic Calculation using Searched Case In this example, we are going to do arithmetic calculation between two numbers 55 and 5.
Types of Loop in PL/SQL PL/SQL provides following three types of loops Basic loop statement For loop statement While loop statement Basic Loop Statement This loop statement is the simplest loop structure in PL/SQL. The execution block starts with keyword 'LOOP' and ends with the keyword 'END LOOP'. The exit condition should be given inside this execution block so that control exit from the loop. It needs EXIT keyword to be given explicitly in the execution part to exit from the loop.
Syntax Explanation: In the above syntax, key word 'LOOP' marks beginning of the loop and 'END LOOP' marks the end of the loop. The execution block contains all the code that needs to be executed including the EXIT condition. The execution part can contain any execution statement. Note: Basic loop statement with no EXIT keyword will be an INFINITE-LOOP that will never stop.
WHILE Loop Statement
WHILE loop statement works similar to the Basic loop statement except the EXIT condition is at the very beginning of the loop. It works like entry-check loop in which execution block will not even be executed once if the condition is not satisfied, as the exit condition is checking before execution part. It does not require keyword 'EXIT' explicitly to exit from the loop since it is validating the condition implicitly each time of the loop.
Syntax Explanation:
In the above syntax, keyword 'WHILE' marks beginning of the loop and 'END LOOP' marks the end of the loop. EXIT condition is evaluated each time before the execution part is starts executing. The execution block contains all the code that needs to be executed. The execution part can contain any execution statement.
FOR Loop Statement
"FOR LOOP" statement is best suitable when you want to execute a code for known number of times rather than based on some other conditions. In this loop, the lower limit and the higher limit will be specified and as long as the loop variable is in between these range the loop will be executed. The loop variable is self-incremental, so no explicit increment operation is needed in this loop. The loop variable need not to be declared, as it is declared implicitly.
Syntax Explanation: In the above syntax, keyword 'FOR' marks beginning of the loop and 'END LOOP' marks the end of the loop. Loop variable is evaluated every time before executing the execution part. The execution block contains all the code that needs to be executed. The execution part can contain any execution statement. The loop_variable is declared implicitly during the execution of the entire loop, and the scope of this loop_variable will be only inside this loop. If the loop variable came out of the range, then control will exit from the loop. The loop can be made to work in the reverse order by adding the keyword 'REVERSE' before lower_limit.
Nested Loops
The loop statements can also be nested. The outer and inner loop can be of different types. In nested loop, for every one iteration value of the outer loop, the inner loop will be executed fully.
