PL/SQL Basic

What is PLSQL:

• PL/SQL is the Oracle Procedural Language extension of SQL.
• A PL/SQL program can have both SQL statements and Ptocedural statements.
• In the PL/SQL program, the SQL is a popular language for both querying and updating data in a relational database management systems(RDBMS) while the procedural statements are used to process individual piece of data and control the program flow.
• PL/SQL is a highly structured and readable language.
• PL/SQL is a standard and portable language for Oracle Database development. If you develop a program that executes on an Oracle database, you can quickly move it to another compatablr Oracle database without any changes.

**Procedural refers to a series of ordered steps that the computer should follow to produce a result.

Why SQL alone is not enough?

• SQL does not support looping and condition statements etc.
• In SQL, you can not execute more than one statements at a time, so running more than one statements increases the network traffic.
• SQL always gives system defined error messages   when user perform any wrong transactions, no place for custom exceptions.
• SQL does not support procedural language features such as code reusability and modularity and some other features of OOPS.

To overcome all the above limitation of SQL, We use PL/SQL.

PL/SQL Advantages:

PL/SQL is a poweful, completely portable, high-performance transaction processing language that offers the following advantages:

• Support for SQL.
• Support for object oriented programming
• Better performance
• Higher Productivity
• Full Portability
• Tight integration with Oracle
• Tight Security

PL/SQL Structure:

PL/SQL is a block structured language, meaning that PL/SQL programs are divided and written in logical blocks of code.

Each block consists of three sub parts:

DECLARE

<declaration section>

BEGIN

<executable commands(s)>

EXCEPTION

<exception handling>

END;

First example:

DECLARE

BEGIN

dbms_output.put_line('Welcome to the world');

END;

** to view the output, SQL developer >> View tab >> Dbms Output >> click on plus button >> select the database where you will run the plsql code >> ok >> now execute the code and see the output.

Declaring Variable:

DECLARE section is used to declare all our variables.

Syntax:

<Variable Name> <Type>;

Example:

ordernumber number := 1001; 

Code:

DECLARE

ordernumber number :=1001;

orderid number default 1002;

customername varchar2(20):='John';

BEGIN

dbms_output.put_line(ordernumber);

dbms_output.put_line(orderid);

dbms_output.put_line(customername);

END;

output: 

1001

1002

John

**This is we declare a variable and assign 1001 value to it. We can also assign a value using default keyword like below

Orderid number default 1002;

** if we use constant keyword with a variable that means that variable cant be used as an assignment target.

ordernumber constant number:=1001;

Comments in PL/SQL:

Single line comments:

-- variable creation

multi line comments:

/* this is a multi line comment

We can write about what we are doing in the program

*/

Scope of variables:

Global variable vs Local variable

• The variable which is declared under the main block and is visible within the entire program is called Global variable.
• The variable which is declared under the sub block and is only visible within the sub block is called Local variable.

Example:

DECLARE

-- Global Variable

num1 number:=20;

BEGIN

dbms_output.put_line(' Outer variable num1: ' || num1)

DECLARE

-- Local Variable

num2 number:=10;

BEGIN

dbms_output.put_line(' Inner variable num1: ' || num1)

dbms_output.put_line(' Inner variable num2: ' || num2)

END;

END;

Output:

Outer variable num1: 20

Inner variable num1: 20

Inner variable: 10

IF then ELSE statement:

This is needed when we want output or action based on some condition. There are folowing 3 types of decision making statements:

Type1:

If <condition> 

then <action>

end if;

Case: if total amount > 100, give 10% discount.

DECLARE

total_amount number:= 102;

discount number:=0;

BEGIN

If total_amount > 100

Then discount := total_amount * .1;

End if;

dbms_output.put_line(discount);

end;

Output: 10.2

Type2:

If <condition>

Then <action>

else

<action>

end if;

Case : if total_amount > 100, give 10% else give 5 % discount

DECLARE

total_amount number:= 100;

discount number:=0;

BEGIN

If total_amount > 100

Then discount := total_amount * .1;

Else

Discount:= total_amount * .05;

End if;

dbms_output.put_line(discount);

end;

Output : 5

type3:

If <condition>

Then <action>

elsif <condition>

Then <action>

Else

<action>

end if;

Case: if total_amount >200, give 20%, if total_amount >=100 and total_amount <=200 give 10%, else give 5% discount.

DECLARE

total_amount number:= 100;

discount number:=0;

BEGIN

If total_amount > 200

Then discount := total_amount * .2;

Elsif total_amount >=100 and total_amount <=200

Then discount:= total_amount * .1;

Else

discount :=total_amount*.05;

End if;

dbms_output.put_line(discount);

end;

Output: 10

CASE statement:

CASE selector 
   WHEN 'value1' THEN S1; 
   WHEN 'value2' THEN S2; 
   WHEN 'value3' THEN S3; 
   ... 
   ELSE Sn;  -- default case 
END CASE;

Case: if total_amount >200, give 20%, if total_amount >=100 and total_amount <=200 give 10%, else give 5% discount.

DECLARE

total_amount number:= 100;

discount number:=0;

BEGIN

CASE

When total_amount > 200

Then discount := total_amount * .2;

When total_amount >=100 and total_amount <=200

Then discount:= total_amount * .1;

Else

discount :=total_amount*.05;

End CASE;

dbms_output.put_line(discount);

end;

Output: 10

WHILE Loop:

While loop is used to execute statements till a particular condition is met.

DECLARE

counter numer(2) := 10;

BEGIN

WHILE counter < 20

LOOP

dbms_output.put_line('value of counter: ' || counter);

counter := counter + 1;

END LOOP;

END;

/

Output:

value of counter : 10

value of counter : 11

value of counter : 12

value of counter : 13

value of counter : 14

value of counter : 15

value of counter : 16

value of counter : 17

value of counter : 18

value of counter : 19

FOR LOOP:

For loop allows us to execute code repeatedly for a fixed number of times.

DECLARE

counter numer(2) := 10;

BEGIN

FOR counter IN 10..20

LOOP

dbms_output.put_line('value of counter: ' || counter);

END LOOP;

END;

/

Output:

value of counter : 10

value of counter : 11

value of counter : 12

value of counter : 13

value of counter : 14

value of counter : 15

value of counter : 16

Value of counter : 17

value of counter : 18

value of counter : 19

value of counter : 20

To reverse the loop order:

DECLARE

counter numer(2) := 10;

BEGIN

FOR counter IN REVERSE 10..20

LOOP

dbms_output.put_line('value of counter: ' || counter);

END LOOP;

END;

/