PL/SQL: SIMPLE LOOPS

LOOPS executes statement or set of statement  or part of program for several times.
First of all we discuss Simple loop.

Simple Loop:
Syntax:
Loop
statement 1;
statement 2;
.
.
.
statement n;
End loop;

Example:
declare
counter number:=0;
result number;
num number:=&Enter_Number_For_Table;
begin
loop
counter := counter+1;
result := num*counter;
dbms_output.put_line(num||' * '||counter||' = '||result);
if counter>=10 then
exit;
end if;
end loop;

end;

Result:

Enter_Number_For_table = 14

14 * 1 = 14

14 * 2 = 28

14 * 3 = 42

14 * 4 = 56

14 * 5 = 70

14 * 6 = 84

14 * 7 = 98

14 * 8 = 112

14 * 9 = 126

14 * 10 = 140

First we declare variables for counter , result and a number for which table is created.

After begin part, We Define a loop. Increment by 1 in counter. and give way  how generate or calcute the result.

Then we print result for every execution.

Then we use IF STATEMENT  to stop or finalize the loop. Then end if and end of loop and end of program.
we can also Finalize the Loop as
Exit when counter>=10;
 The result of all query is above mentioned. If any problem,then feel free to ask.

PL/SQL: IF ELSEIF OR IF THEN ELSEIF STATEMENT

If Then else if statement is used with the multiple conditions in Selection structure.

Syntax:
IF condition then
statement(s);
elseif conditon  then
statement(s);
elseif  condition then
statement(s);
else
statement(s);
end if;
statement(s);
end;


IF THEN ELSE Statement is responsible for the execution of first statement if the condition is true. and if conditin is true then it will never evaluate the rest conditions .In case non of this condition is true, it evaluate the ELSE statement.

Example:

declare
place varchar2(30):= '&Enter_City_name';
begin
if place = 'Metropolis' then
dbms_output.put_line('City Is protected by Salman Khan');
elsif place='Gotham' then
dbms_output.put_line('City is protected by Amir khan');
elsif place='Amazon' then
dbms_output.put_line('City is protected by Shahrukh Khan');
else
dbms_output.put_line('Call to the Police');
end if;
dbms_output.put_line('Thank you For Contacting us');

end;

Result:
Enter_City_name = Metropolis

City Is protected by Salman Khan
Thank you For Contacting us


Now check it with using city name that is not in else statements.
Try it again.

declare
place varchar2(30):= '&Enter_City_name';
begin
if place = 'Metropolis' then
dbms_output.put_line('City Is protected by Salman Khan');
elsif place='Gotham' then
dbms_output.put_line('City is protected by Amir khan');
elsif place='Amazon' then
dbms_output.put_line('City is protected by Shahrukh Khan');
else
dbms_output.put_line('Call to the Police');
end if;
dbms_output.put_line('Thank you For Contacting us');
end;

Result:
Enter_City_name = Faisalabad

Call to the Police

Thank you For Contacting us

PL/SQL: IF ELSE OR IF THEN ELSE STATEMENT

If then else statement executes sequence of statements only when the condition is evaluated to be true.

Syntax:
If condition then
statement(s);
else
statement(s);
end if;
statement(s);
end;
/

Example:
declare
v_num number:= &Enter_a_Number;
begin
if mod(v_num,2)=0 then
dbms_output.put_line('You Entered an Even number '||v_num);
  else
  dbms_output.put_line('You Entered an Odd number '||v_num);
end if;
dbms_output.put_line('Muhammad Talah Zubair');
end;

/
give input 10

Result:
You Entered an Even number 10
Muhammad Talah Zubair

Now check it again, by providing odd number as input , and check out the result.

Example:
declare
v_num number:= &Enter_a_Number;
begin
if mod(v_num,2)=0 then
dbms_output.put_line('You Entered an Even number '||v_num);
  else
  dbms_output.put_line('You Entered an Odd number '||v_num);
end if;
dbms_output.put_line('Muhammad Talah Zubair');
end;

/
give input 11 

Result:
You Entered an Odd number 11

Muhammad Talah Zubair

PL/SQL: Constant

Constants are the user defined identifiers whose value remain unchanged during the execution of a program.
Syntax:
constant_name Constant datatype(size) := value;

Constant must be initialized during the declaration.

Example:
Declare
v_pi constant number:=3.14157;
begin
dbms_output.put_line(v_pi);
end;

Result:
3.14157

OR:

declare
v-pi constant number(7,6) not null default 3.14157;
begin
dbms_output.put_line(v_pi);

Result:
3.14157

This query prevent you to assigning null values to you constant.

PL/SQL:Anchored data type

Data type which you assign to a variable based on database object.
Syntax:
variable_name typed_attribute %type

Example:
Declare
vname employees.first_name %type
Begin
select first_name into vname from employees where employee_id=100;
dbms_output.put_line(vname);
end;


In the above example vname is the name of variable and employees is table name of database, first_name is attibute(column)of employees table %type used for anchored datatype. This querry will asign datatype with respect to database object.

PL/SQL: If Statement

If statement is descision making statement. You will check the condition and perform any action based on the outcome of if statement.
The outcome of if statement is either True or False.

Syntax:
If condition then
statement(s);
end if;

For example:

Example:
declare
vsalary number(10,2);
begin
select salary into vsalary from employees where employee_id=100;
if vsalary>6000 then
dbms_output.put_line('He earn more than TOM');
end if;
end;

Result:
He earn more than TOM

PL/SQL: INSERT DATA AS MEMORY VARIABLES.

First of all , we create a table.

Table:
create table t1 (c1 number(3), c2 varchar2(30), c3 date);

Result:

table created.

Insert data into table t1 Using PL/SQL MEMORY_VARIABLE:

Code:

declare

a number:=2;

d date:=sysdate;

begin

insert into t1 values(1,'Talha','29-apr-2017');

insert into t1 values(a,'Zubair',d);

insert into t1 (c1,c3) values(3,d);

end;

Result:

3 ROWS inserted

Now check

select * from t1;

Result:

1 Talha 29-APR-17

2 Zubair 30-APR-17

3         30-APR-17