PLSQL

• PL/SQL: It stands for procedural language by structure query language And it extends from sql.
• In SQL we can’t execute more than on statement at a time but whereas pl/sql with in execute more their one statements at a time.
• PLSQL was developed by oracle in early 90′s to enhance the capability of sql.
• It allows database designer to develp complex database application that require the uses of control structure and procedure elements such as procedures functions and modules.
• PLSQL is a programming language that interact with oracle database and it is store directly in the database.
• It is only programming language that interacts with the oracle database natively and within the database environment.
• PLSQL is a database oriented programming language that extends sql with procedural capability by adding programming concepts founding procedural language , reverse in a structural language that is more powerful then SQL .
• A procedural language allows writing a program that specifies list of operations to be perform sequentially to achieve the desirable result.
• SQL does not allow defining error messages that popup if an error is encountered during data manipulation while processing an SQL statement if error occur oracle display its own error message . whereas PLSQL permits dealing with error as required and facilities displaying user friendly message when users are encountered.
• SQL does not have any procedural capabilities such as condition checking, looping, branching
• These are required so that the data can be tested before its permanent storage .whereas pl/sql development tool not only support SQL data manipulation us also provide procedural capabilities such as conditional checking ,branching and looping.
• Pl/sql is a high performance transaction processing language since PLSQL operate over the SQL command it is support of SQL.

Data Types in PL/SQL

• The data type which are used in SQL are same supported by PLSQL.

Operators in PLSQL

• The operator which are used in SQLare same operators are supported by PLSQL
• And new extra operator in PLSQL is assignment operator(:=)

Input Statement in PL/SQL

• There is no input statement in pl/sql if we want to input the values at run time we need to use 

Output statement in pl/sql

• Dbms_output.put_line/put();
• Dbms_output is a package that includes a number of procedures and function that accumulates information

Syntax

• Dbms_ouput.put_line(‘message’);

Example:

Dbms_output.put_line(‘Kamlesh’);

Syntax:

• Dbms_output.put_line(‘message’||variable);

Example:

Dbms_output.put_line(‘Kamlesh’||mca);

Syntax:

Dbms_output.put_line(variable);

Example:

Dbms_output.put_line(mca);

Block :

It is a grouped code or set of statement .In PLSQL these blocks are divided into two types.
Anonymous block

Sub program or name PLSQL block

Anonymous block or Un-named block:

• Are those block which cannot be stored permanently I database ,these block are automatically destroyed these blocks has no proper name to reference further so these blocks can also be called as unnamed PLSQL blocks.

Sub program or named PLSQL block:

• Are those blocks which can store permanently in e databases as database objects .so user can access these blocks has some name to fewer further so these block can also be called as named PLSQL blocks.

Structure of anonymous blocks

Declare

Declaration of variables;

Courser declaration;

Type declaration;

Begin

Statement 1;

{

Statement n;

Exception

Exception handling statement;

End;

Anonymous block contain four sections

• Declare section
• Begin section
• Exception section
• End section

Declare section :

• In this section programmer need to declare all the variable cursors ,types etc.

Begin section :

• In this section programmer need to write all the logical ,executable and output statement etc

Exception section :

• This section contains all the exception handling statements.

End section :

• This section represents program termination

Notes:

• Here begin and end sections are mandatory whereas declare and exception section are optional.

Declaration of variable in pl/sql

Syntax:

Variable datatypes

A number;

Syntax

Variable datatype:=value;

A number:=10;

Rules for naming a variable

• Variable must start with a alphabet
• Variable contains minimum one character and maximum 30 characters.
• Variable should not contain any special characters or space except underscore.

Comments in pl/sql

Comments are used to increase the readability of program comprehensively.there are two types of comments—-

• Single line comment e.g ——-
• Multiline comment e.g. /* _________________*/

Write a PLSQL block to print hello world.

Begin

Dbms_out.put_line(‘hello world’);

Ends;

Write a PLSQL block to input two numbers and find out its sum


Declare

A number:=10;

B number:=20;

C number;

Begin

C:=a+b;

Dbms_out.put_line(‘the sum is ’||c);

End;
Write pl/sql block to input feet and convert into inches.


Declare

F number:=&f;

Begin

Dbms_out.put_line(‘the converted feet into inches is’||(f*12));

Ends;

Write a PLSQL block to put two numbers and interchange there values


Declare

A number:=&a;

B number:=&b;

C number;

Begin

C:=a;

A:=b;

B:=c;

/*

A:=a+b;

B:=a-b;

C:=a-b;

*/

Dbms_out.put_line(‘the value of a is ’||a);

Dbms_out.put_line(‘the value of b is’||b);

Ends;
Control statements


Conditional statement :

• if
• If else
• Switch
• Nested if

If

 
Syntax:
If(condition ) then
Statement
End if

If-elseIf(condition) then
Statement 1
Statement 2
Else
Statement 1
Statement 2
End if
end

This is a conditional statement in PLSQL which check the condition are true or false by means of relational operators such as >,< ,<=,>= etc.

• The condition is satisfy then it will check the statement between if and else when the condition is not satisfy then it will execute the statement after else
• Every if condition should end with end if.

Write a PLSQL block take two no’s and find out the big number.

Declare

A number:=&a;

B number:=&b;

Begin

If(a>b) then

Dbms_out.print_line(‘a is big’);

Else

If(a=b) then

Dbms_out.print_line(‘both are equal’);

Else

Dbms_out.print_line(‘b is big’);

End if;

End if;

End;


Write a program to display and input the positive and negative no.


Declare

A number=&a;

Begin

If(a>0) then

Dbms_out.print_line(‘a is positive no’);

Else if (a=0)

Dbms_out.print_line(‘a is netural’);

Else if(b=0) then

Dbms_out.print_line(‘b is neutral’);

Else

Dbms_out.print_line(‘a is negative’);

End if

End if

End


Write a pl/sql program to find out even or odd;

Declare

A number:=&a;

Begin

If(mod(a,2)=o)then

Dbms_out.print_line(‘a is even’);

Else

Dbms_out.print_line(‘a is odd’);

End if;

End;