PL / SQL is a programming long with SQL which allows you to write sets of statements as one block called as a PL / SQL block.
PL / SQL blocks contain the following :
Variables
Constants
Conditional stats
Looping stats
SQL states
All SQL operators & functions etc
Syntax:
Declare
Variable declaration Constant declaration
------
Begin
------
End;
Q. Write a PL / SQL block for the following requirements:
Answer.
It is used to alter end and then enter this is for execution SQD Set serveroutput on; You have to write this before PL / SQL block (before declare)
if Syntax
If (condition) then S1;
S2;
End if;
If (condition) then
S1;
else
S2
end if
S3
End if;
if (condition) then
S1;
else if (condition)
S2:
else
S3
End if;
Example:
declare
a number (4);
b number (4);
begin
a:=10; b:=20;
if (a>b) then dbms_output.put_line (a);
else
dbms output.put_line (b);
end if;
Simple Loop:
Loop
S1;
S2;
Exit when condition End loop;
While loop
while (condition) loop
S1;
S2;
End loop;
For Loop
For value in (Reverse) Start Value end value Loop
----
----
End loop;
Q. Display the five divisible from 1 to 100
Answer.
declare
i number (4);
begin
I:=1;
loop
if (i mod 5=0) then dbms_output. Put_line (i); end if;
i:=i+1
exit when i=100; end loop;
end;
declare
i number (4);
begin
i : = 1;
while (i<=100) loop
if (i mod 5=0) then dbms_output.put_line(i); end if;
end loop; end;
Declare
i number (4);
begin
for i in 1….100 loop
dbms_output.put_line(i);
end loop; end;
The stored procedure is PL/SQL block name which takes some parameter and returns some parameters.
Syntax:
Create or replace procedure pro_name (var_name(in/out/inout) data type, …….)
as
-------------
-------------
Begin
-------------
end;
Q. 1) Write a stored procedure with the following requirements:
a. It has to take two parameters, m and n
b. Display the number from n to m
Ans.
Create or replace procedure P1 (m number in number) as
i number (4);
begin
for i in reverse m….n loop dbms_output.put_line(i);
end loop;
end;
Call P1 (10, 25);
(This is used for changing the values without changing the complete block)
Q. 2) Write a stored procedure with the following requirements:
Considering the following table:
ID A B C D 1. 10 20 NULL NULL 2. 20 40 NULL NULL 3. 0 0 NULL NULL 4. - 5 NULL NULL
Ans.
Create or replace procedure P2 (id, number) as
a1 number (4);
b1 number (4);
c1 number (4);
d1 number (4);
begin
select a,b into a1, b1 from hello where ID = x;
C1:=a1+b1 D1:=a1-b1;
If (c1=0) then
delete from hello where id=x;
else
update hello set c=c1, d=d1 where id=x;
end if;
end;
call p2(1);
With Stored Procedure
When you submit your SQL stat to SQL engine, it will be compiled and executed when different users are submitting same query repeatedly. The query will be compiled every time which is unnecessary.
When one user is submitting a set of queries, then all queries will be compiled every time.
This will increase the request processing time
If you write a stored procedure with a set of SQL then you can reduce the request processing time because stored procedure and SQL stat used in the stored procedure will be compiled only once and will be executed every time directly.
Q. Write the stored procedure with the following requirements:
Considering the following student table
| Student ID | Student Name | M1 | M2 | M3 | M4 | Total | Average | Status |
|---|---|---|---|---|---|---|---|---|
| 101 | A | 50 | 80 | 70 | 80 | Null | Null | Null |
The procedure has to take student ID as parameter and do the following tasks:
Ans.
Create the table called students
Create table students (
Student ID number (4), Primary key, student name char 912) m1 number (2), m2 number (2), m3 number (2), m4 number (2),
Total number (3),
Average number (3),
Status char (10));
Insert sample records
Insert into students (SID, name, m1, m2, m3, m4) values (101, 'A', 90,70,80,90);Insert into students (SID, name, m1, m2, m3, m4) values (102,'B',10,30,20,50);Insert into students (SID, name, m1, m2, m3, m4) values (103, 'c', 199,90, 90,70);Create the table called results
Create table results (passed a number (2), fail number (2));
Insert one sample record
Insert into results values (0,0);
Create a replace procedure P3 (ID number) as
mm1 number (2);
mm2 number (2);
mm3 number (2);
mm4 number (2);
total number (2)
average number (2);
Stat char (10)
X number (2);
Begin
Select m1, m2, m3, m4 into mm1, mm2, mm3, mm4, from students
Where SID = ID;
Total : = mm1 + mm2 + mm3 + mm4;
Average : = Total /4;
If (avg<50) then Stat:="passed";
End if;
Update students select total =101, average = avg status = stat
Where SID = ID;
if (stat='passed') then
select passed into x from results;
x=x+1;
update results set passed = x;
else
select failed into x from results;
x=x+1;
update results set failed = x;
end if;
end;
Call P3 (101);
Call P3 (102);
Call P3 (103);
Types of Parameters:
In parameters
Out parameters
Inout parameters
In parameters carry the data from the caller of the procedure to the procedure. Create procedure P1 (a in num, b number) as By default all the parameters are in parameter.
Out parameter carries the data from a procedure to the caller.
Example:
Create procedure P1 (a in number, b number, c out number, d out number);
Inout parameter is used to carry the data from caller to procedure and from procedure to caller.
Trigger is a PL / SQL block within same name
Trigger will be called automatically whenever you do insert, update or delete operations on the table
Syntax:
Create or replace trigger name
[after / before]
[insert / update / delete]
On table_name
[for each row]
declare
--------
Variable declarations
--------
Begin
End;
Write a trigger with following requirements:
1) Consider the following tables:
| Student ID | Student Name | Phone | |
|---|---|---|---|
| 101 | A | 1111111111 |
| Student ID | Student Name | Phone | Phone | Phone | |
|---|---|---|---|---|---|
| 101 | A | 1111 | Update | 8 feb | |
| 102 | A | 1234 | Update | 8 feb | |
| 103 | A | 1234 | Update | 8 feb |
Old is an object representing 1 row and it is only for update and delete open
New used for insert open
2) Whenever update or delete operation is issued on ONLYFORJAVA student table, then the trigger has to be invoked automatically and has to move the existing records into the ONLYFORJAVA student_backup table.
Ans.
Create ONLYFORJAVA student table and insert for the record and then ONLYFORJAVA student_backup table and insert six records
create or replace trigger
before update or delete
on ONLYFORJAVAstudents
declare
ID number (3);
Sn Char (10);
Ph number (7);
Op char (10);
Opd char (10);
begin
id : = :old.sid;
sn : = old.sname;
em : = old.email;
ph : = old.phone;
if (updating) then op : ='update';
end if;
if (deleting) then op : = 'delete';
end if;
opd : = sysdate;
insert into ONLYFORJAVA students_backup
Values (ID, Student name, email, phone, OP, OPD);
End;
There are three transactional statements:
Commitment
Rollback
Save point
Definitions:
The transaction is the process of performing a set of database operations as one unit When all the database operations in this unit are successful. Then we make the operation permanent by issuing commit
When any one database operation unit fails, then we have to cancel the operation by issuing rollback.
SQL > S1
SQL > S2
SQL > S3
SQL > Commit; (roll back;)
SQL > Select
SQL > Savepoint SP1;
SQL > S1
SWL > S2
SQL > S3
SQL > Select
SQL > Commit: (Rollback):
SQL > Savepoint SP1; (Rollback SP1);
SQL > Select
SQL > Savepoint SP2; (rollback SP2)