PL/SQL: TABLE BASED DATA TYPE.

Record datatype variables are composit data structure made up of different components called fields which have the same name and datatype as that of the columns of the table which is using in creating the record.
Example:

set serveroutput on;
declare
v_emp employees%rowtype;
begin
select * into v_emp from employees where employee_id=100;
dbms_output.put_line(v_emp.first_name||' '||v_emp.salary);
end;

/

REsult:
Steven 24000

In the above example v_emp is of employees row type..

PL/SQL: Backup Trigger.

To make synchronize backup of table, We create a table to store the backup.
Create Table:
create table heros_backup as select * from heros where 1=2;

This will create the table same as heros but does not copy the data of hero table.
Now
Create Backup Trigger:
create or replace trigger heros_bu
before insert or delete or update on heros
for each row
enable
begin
if inserting then
  insert into heros_backup (hero_name) values(:NEW.hero_name);
elsif deleting then
  delete from heros_backup where hero_name=:old.hero_name;
elsif updating then
  update heros_backup set hero_name = :NEW.hero_name where hero_name=:old.hero_name;
end if;

end;

Result:

Trigger HEROS_BU compiled

Now check by inserting data into heros.

insert into heros values('Muhammad Talha Zubair');

To Check:

select * from heros_backup;

Result:

Muhammad Talha Zubair

PL/SQL Audit Trigger

In previous topic we discuss the audit of table when inserting, updatind, or deleting any row. If we want to save the audition in another table then
First of all we have to create a table.

Create Table:
create table audit_heros
(
new_name varchar2(30),
old_name varchar2(30),
user_name varchar2(30),
operation_date varchar2(30),
operation varchar2(30)
);

/
Result:
Table audit_heros created.

Now we create an audit trigger on audit heros, that keeps the record of changing the heros table.

Audit Trigger:
create or replace trigger au_heros
before insert or delete or update on heros
for each row
enable
declare
v_user varchar2(30);
v_date varchar2(30);
begin
select user, to_char(sysdate,'DD/MM/YYYY HH24:MI:SS') into v_user,v_date from dual;
if inserting then
  insert into audit_heros values(:NEW.hero_name,'NULL',v_user,v_date,'INSERT');
elsif deleting then
  insert into audit_heros values('NULL',:OLD.hero_name,v_user,v_date,'DELETE');
elsif updating then
  insert into audit_heros values(:NEW.hero_name,:OLD.hero_name,v_user,v_date,'update');
end if;
end;

/
Result:
Trigger au_heros  compiled.

Now by inserting or deleting or updating the row in heros table. we can check the working of au_heros trigger.
When Deleting:
delete from heros where hero_name='Salman';

Result:
3 rows deleted.
Now chek audit_heros table

select * from audit_heros;
Result:

NULL Salman HR 13/05/2017 16:34:45 DELETE
NULL Salman HR 13/05/2017 16:34:45 DELETE
NULL Salman HR 13/05/2017 16:34:45 DELETE

When updating:
update heros set hero_name='Aybue khan' where hero_name='Ayube';

And again check the audit_heros table
select * from audit_heros;
Result:

NULL          Salman HR 13/05/2017 16:34:45 DELETE
NULL          Salman HR 13/05/2017 16:34:45 DELETE
NULL          Salman HR 13/05/2017 16:34:45 DELETE

Aybue khan Ayube HR 13/05/2017 16:40:56 update

Note:
NEW keyword allows you access a row that is being proceed . It means that it allow acess to a row that is being inserted or updated in the table

OLD keyword allow access to a table that is already inserted or updated in the table.

PL/SQL DML Trigger With Before Insert, Update and Delete DML Examples

To test this trigger, first of all we have to create a table.

Table:

create table heros

(hero_name varchar2(30));

Result:

Table HEROS created.

Now we create a dml trigger,

CREATE OR REPLACE TRIGGER bi_heros

BEFORE INSERT ON heros

FOR EACH ROW 

ENABLE

DECLARE

v_user varchar2(30);

begin

select user into v_user from dual;

dbms_output.put_line('User '||v_user||' Just inserted a line');

end;

/

Result:

Trigger BI_HEROS compiled

To check that is this trigger working or not, We Insert a row into the table and find out the result.

insert into heros values('Talha Zubair');

Result:

1 row inserted.

User HR Just inserted a line

We add another trigger on heros table.

CREATE OR REPLACE TRIGGER bu_heros

BEFORE update ON heros

FOR EACH ROW 

ENABLE

DECLARE

v_user varchar2(30);

begin

select user into v_user from dual;

dbms_output.put_line('User '||v_user||' Just updated a line');

end;

/

Update data

update heros set hero_name='Talha Zubair Khan' where hero_name='Talha Zubair';

Result:

1 row updated.

User HR Just updated a line

Create Insert, update, delete Trigger at a time.

code:

create or replace trigger tr_heros

before insert or delete or update on heros

for each row

enable

declare

v_user varchar2(30);

begin

Select user into v_user from dual;

if inserting then

dbms_output.put_line('User '||v_user||' Just Inserted A line');

elsif deleting then

dbms_output.put_line('User '||v_user||' Just deleted A line');

elsif updating then

dbms_output.put_line('User '||v_user||' Just Updated A line');

end if;

end;

/

Result:

Trigger TR_HEROS compiled

Now test the trigger.

inserting:

insert into heros values('Salman');

Result:

1 row inserted.

User HR Just Inserted A line

Updating:

delete from heros where hero_name='Salman';

Result:

1 rows deleted.

User HR Just deleted A line

PL/SQL: TRIGGERS INTRO

Named PL/SQL blocks which are stored in database are called triggers.
Or
Specialized stored programs which executes implicitly when trigger event occurs.
Events:
Events may be anything,
such as
1. DML statement, which includes insert or delete statement.
2. DDL statement, which includes create or alter statements.
3. System event, which includes shutdown or restart database.
4. User event, Which include logoff or log on of user on the database.

TYPES of TRIGGERS:
There are 5 types of trigges.

1. DML TRIGGERS.
As name show, this type or trigger fire with the dml statements, before or after the insert, delete or update statement.

2. DDL TRIGGERS:
These are the triggers which are created with the ddl statements, such as create or alter statement, These triggers fire when any changes occure.

3. SYSTEM EVENT TRIGGERS:
This type of triggers occure when database or system event occur, such as logon or log off of the database.

4. INSTEAD-OF TRIGGERS:
These tirggers enables you to stop or redirected the performance of dml triggers.

5. COMPOUND TRIGGERS:
These are multi tasking triggers that act as low level triggers when the data inserted, deleted or updated from the database table. Compound triggers used to audit, check, replace or save even before the change.

PL/SQL:WHILE LOOP

WHILE LOOP is also used for the itteration of  block of statement.
The best usable of while is when the number of itteration are unknown.

Syntax:
begin
while condition loop
statement1;
statement2;
statement3;
.
.
statementn;
end loop;

In the above example, while and loop are the keywords. and condition is the critarea , which decides the number of itteration.

Example:
declare
result number:=0;
counter number:=1;
table_num number:= &Enter_number_For_table;
begin
while counter <=10 loop
result:=counter*table_num;
dbms_output.put_line(table_num||' * '||counter||' = '||result);
counter:=counter+1;
end loop;
dbms_output.put_line('M Talha Zubair, Out from loop');

end;

Result:
19 * 1 = 19
19 * 2 = 38
19 * 3 = 57
19 * 4 = 76
19 * 5 = 95
19 * 6 = 114
19 * 7 = 133
19 * 8 = 152
19 * 9 = 171
19 * 10 = 190
M Talha Zubair, Out from loop


While Loop with Boolean Variable as test Variable:
Example:
declare
v_test boolean:=true;
counter number:=1;
begin
while v_test loop
dbms_output.put_line(counter);
counter:=counter+1;
if counter=10 then
exit;
end if;
end loop;
dbms_output.put_line('M Talha Zubair, Out from loop');
end;

Result:
1
2
3
4
5
6
7
8
9
M Talha Zubair, Out from loop

PL/SQL: FOR LOOP

FOR LOOP  is used for the fixed number of execution of statement or set of statement.

Syntax:
For Counter_variable in lower_limit . . upper limit Loop
statement 1;
statement 2;
statement 3;
.
.
statement n;
end loop;
end;

Counter variable us is used to continue the loop or execution of statements. IN is the keyword. lower LIMIT and UPPER LIMIT is the critarea for which loops executes the statement(s).

Example:
declare
counter number:=0;
begin
for counter in 1 .. 10 loop
dbms_output.put_line(counter||' I Love Pakistan');
end loop;

end;

Result:
1 I Love Pakistan
2 I Love Pakistan
3 I Love Pakistan
4 I Love Pakistan
5 I Love Pakistan
6 I Love Pakistan
7 I Love Pakistan
8 I Love Pakistan
9 I Love Pakistan
10 I Love Pakistan


To print the table of any number input by the user, The query will be as follow

Example:
declare
result number:=0;
table_num number:= &Enter_number_For_table;
begin
for counter in 1 .. 10 loop
result:=counter*table_num;
dbms_output.put_line(table_num||' * '||counter||' = '||result);
end loop;

end;

Result:
Enter_number_For_table = 19
19 * 1 = 19
19 * 2 = 38
19 * 3 = 57
19 * 4 = 76
19 * 5 = 95
19 * 6 = 114
19 * 7 = 133
19 * 8 = 152
19 * 9 = 171
19 * 10 = 190

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.