DBMS Procedure

SQL> REM 1: Ask the user for the name of a class and
other information required SQL> REM for a tuple of table
Classes.
Add this information to Classes and also add the ship
SQL> REM with the class name to Ships.Display appropriate
message if the Class REM already exists.
SQL>
SQL> create or replace procedure find_class(
2 c classes%rowtype,
3 temp out number)is
4 begin
5 temp:=0;
6 select 1 into temp
7 from classes
8 where class=c.class;
9 EXCEPTION when no_data_found then
10 insert into classes
values(c.class,c.type,c.country,c.numguns,c.bore,c.displa
ceme
nt);
11 insert into ships
values(c.class,c.class,NULL);
12 temp:=0;
13 end;
14 /
Procedure created.
SQL> declare
2 c classes%rowtype;
3 temp number;
4 begin
5 c.class:='&class';
6 c.type:='&type';
7 c.country:='&country';
8 c.numguns:=&numguns;
9 c.bore:=&bore;
10 c.displacement:=&displacement;
11 find_class(c,temp);
12 if temp=0 then
13 dbms_output.put_line('Class '||c.class||' is a new
class ....Inserted successfully in class and
ships..!!!!');
14 else
15 dbms_output.put_line('Class '||c.class||' already
exists!!!');
16 end if;
17 end;
18 /
Enter value for class: Iowa
old 5: c.class:='&class';
new 5: c.class:='Iowa';
Enter value for type: bb
old 6: c.type:='&type';
new 6: c.type:='bb';
Enter value for country: india
old 7: c.country:='&country';
new 7: c.country:='india';
Enter value for numguns: 3
old 8: c.numguns:=&numguns;
new 8: c.numguns:=3;
Enter value for bore: 12
old 9: c.bore:=&bore;
new 9: c.bore:=12;
Enter value for displacement: 12000
old 10: c.displacement:=&displacement;
new 10: c.displacement:=12000;
Class Iowa already exists!!!
PL/SQL procedure successfully completed.
SQL> /
Enter value for class: Renold
old 5: c.class:='&class';
new 5: c.class:='Renold';
Enter value for type: bb
old 6: c.type:='&type';
new 6: c.type:='bb';
Enter value for country: India
old 7: c.country:='&country';
new 7: c.country:='India';
Enter value for numguns: 9
old 8: c.numguns:=&numguns;
new 8: c.numguns:=9;
Enter value for bore: 19
old 9: c.bore:=&bore;
new 9: c.bore:=19;
Enter value for displacement: 16000
old 10: c.displacement:=&displacement;
new 10: c.displacement:=16000;
Class Renold is a new class ....Inserted successfully in
class and ships..!!!!
PL/SQL procedure successfully completed.
SQL> select * from classes where class='Renold';
CLASS TYPE COUNTRY NUMGUNS
BORE DISPLACEMENT
--------------- ----- --------------- ----------
---------- ------------
Renold bb India 9
19 16000
SQL> select * from ships where name='Renold';
NAME CLASS LAUNCHED
--------------- --------------- ----------
Renold Renold
SQL>
SQL> REM 2: Ask the user for the name of a battle. Find
the countries of the ships SQL> REM involved in the
battle.Print
the country with most ships sunk and the country
SQL> REM with most ships damaged. Print appropriate
result.
SQL>
SQL> create or replace procedure
2 countries_involved(bname outcomes.battle%type,
3 cur2 IN OUT sys_refcursor) is
4 begin
5 open cur2 for
6 select distinct country
7 from classes c
8 join ships s on(c.class=s.class)
9 join outcomes o on (o.ship=s.name)
10 where battle=bname;
11 end;
12 /
Procedure created.
SQL> create or replace procedure
2 max_coun(res outcomes.result%type,
3 bname outcomes.battle%type,
4 cur in out sys_refcursor) is
5 begin
6 open cur for
7 select distinct country
8 from classes
9 where class IN (select class from
ships
10 where name IN(select
ship from outcomes
11 where
result=res and
battle=bname)
12 group by class
13 having
count(name)>=ALL(select count(name)
from ships
14
where name IN(select ship from outcomes
15
where result=res and battle=bname)
16 group
by class));
17 end;
18 /
Procedure created.
SQL>
SQL> declare
2 bname outcomes.battle%type;
3 temp classes.country%type;
4 cur sys_refcursor;
5 cur2 sys_refcursor;
6 ctry classes.country%type;
7 begin
8 bname:='&battle_name';
9 dbms_output.put_line('For battle ::'||bname);
10 dbms_output.put_line('------------------------');
11 dbms_output.put_line('Countries involved');
12 dbms_output.put_line('************************');
13 countries_involved(bname,cur2);
14 loop
15 fetch cur2 into temp;
16 exit when cur2%notfound;
17 dbms_output.put_line(temp);
18 end loop;
19 close cur2;
20
dbms_output.put_line('----------------------------------'
);
21 dbms_output.put_line('COUNTRY WITH MAXIMUM SHIP
SUNK');
22
dbms_output.put_line('----------------------------------'
);
23 max_coun('sunk',bname,cur);
24 loop
25 fetch cur into ctry;
26 exit when cur%notfound;
27 dbms_output.put_line(ctry);
28 end loop;
29 close cur;
30 max_coun('damaged',bname,cur);
31
dbms_output.put_line('---------------------------------')
;
32 dbms_output.put_line('COUNTRY WITH MAXIMUM SHIP
DAMAGED');
33
dbms_output.put_line('---------------------------------')
;
34 loop
35 fetch cur into ctry;
36 exit when cur%notfound;
37 dbms_output.put_line(ctry);
38 end loop;
39 close cur;
40 end;
41 /
Enter value for battle_name: Guadalcanal
old 8: bname:='&battle_name';
new 8: bname:='Guadalcanal';
For battle ::Guadalcanal
------------------------
Countries involved
************************
USA
Gt. Britain
Japan
-------------------------------
COUNTRY WITH MAXIMUM SHIP SUNK
-------------------------------
Japan
---------------------------------
COUNTRY WITH MAXIMUM SHIP DAMAGED
---------------------------------
Gt. Britain
PL/SQL procedure successfully completed.
SQL> REM 3:Given the name of a battle, produce the two
countries whose ships were involved in the
SQL> REM battle. If there are more or fewer than two
countries involved, produce NULL for both countries.
SQL>
SQL> create or replace procedure find_coun(
2 tbattle IN battles.name%type,
3 country1 OUT classes.country%type,
4 country2 OUT classes.country%type,
5 tp out number,
6 tcount out number) is
7 cursor c is
8 select distinct country
9 from classes c
10 join ships s on(c.class=s.class)
11 join outcomes o ON(o.ship=s.name)
12 where o.battle=tbattle;
13 temp c%rowtype;
14 begin
15 tcount:=0;
16 open c;
17 LOOP
18 fetch c into temp;
19 exit when c%NOTFOUND;
20 END LOOP;
21 tcount:=c%rowcount;
22 close c;
23 open c;
24 if tcount=2
25 then
26 fetch c into country1;
27 fetch c into country2;
28 tp:=1;
29 else
30 country1:=NULL;
31 country2:=NULL;
32 tp:=0;
33 end if;
34 close c;
35 end;
36 /
Procedure created.
SQL>
SQL> declare
2 tbattle battles.name%type;
3 country1 classes.country%type;
4 country2 classes.country%type;
5 tp number;
6 tcount number;
7 begin
8 tbattle:='&Battle_Name';
9 find_coun(tbattle,country1,country2,tp,tcount);
10 if tp = 0
11 then
12 dbms_output.put_line('No of country is ');
13 dbms_output.put_line(tcount);
14 dbms_output.put_line('Country1 : NULL');
15 dbms_output.put_line('Country2 : NULL');
16 else
17 dbms_output.put_line('Country1');
18 dbms_output.put_line(country1);
19 dbms_output.put_line('Country2');
20 dbms_output.put_line(country2);
21 end if;
22 end;
23 /
Enter value for battle_name: North Cape
old 8: tbattle:='&Battle_Name';
new 8: tbattle:='North Cape';
Country1
USA
Country2
Japan
PL/SQL procedure successfully completed.
SQL> /
Enter value for battle_name: Guadalcanal
old 8: tbattle:='&Battle_Name';
new 8: tbattle:='Guadalcanal';
No of country is
3
Country1 : NULL
Country2 : NULL
PL/SQL procedure successfully completed.
SQL> REM 4:Given a ship name, determine if the ship was
in a battle with a date before the ship was launched.
REM If so, display the battle date and prompt the user
with option to change the date oF launch.
SQL>
SQL>
SQL> insert into classes
values('newclass','bb','india',12,25,34000);
1 row created.
SQL> insert into ships
values('newship','newclass','1990');
1 row created.
SQL> insert into battles values('newbat','25-may-1970');
1 row created.
SQL> insert into outcomes
values('newship','newbat','ok');
1 row created.
SQL>
SQL> create or replace function
2 func4( tship ships.name%type,
3 battle_Date battles.battle_date%type,
4 tlaunched ships.launched%type)
5 return number is
6 flag number;
7 begin
8 if substr(extract (year from (battle_Date)),3,2) <
substr(tlaunched,3,2)
9 then
10 flag:=0;
11 return flag;
12 else
13 flag:=1;
14 return flag;
15 end if;
16 end;
17 /
Function created.
SQL>
SQL> create or replace procedure
2 proc ( tlaunched ships.launched%type,
3 tship ships.name%type ) is
4 begin
5 update ships
6 set launched=tlaunched
7 where name=tship;
8 end;
9 /
Procedure created.
SQL>
SQL>
SQL> declare
2 tship ships.name%type;
3 battle_Date battles.battle_date%type;
4 tlaunched ships.launched%type;
5 flag number;
6 begin
7 tship:='&shipname';
8 select launched into tlaunched
9 from ships
10 where name=tship;
11 select battle_date into battle_Date
12 from battles b
13 join outcomes o on(b.name=o.battle)
14 where o.ship=tship;
15 dbms_output.put_line(' Battle date is
'||battle_Date);
16 dbms_output.put_line('Launched date is
'||tlaunched);
17
flag:=func4(tship,battle_Date,tlaunched);
18 if flag=0
19 then
20 tlaunched:='&NewLaunchDate';
21 proc(tlaunched,tship);
22 dbms_output.put_line( 'Launched date
updated as ' || tlaunched);
23 else
24 dbms_output.put_line( battle_Date || '
is less than '|| tlaunched);
25 end if;
26 end;
27 /
Enter value for shipname: newship
old 7: tship:='&shipname';
new 7: tship:='newship';
Enter value for newlaunchdate: 1960
old 20: tlaunched:='&NewLaunchDate';
new 20: tlaunched:='1960';
Battle date is 25-MAY-1970
Launched date is 1990
Launched date updated as 1960
PL/SQL procedure successfully completed.
SQL> /
Enter value for shipname: Revenge
old 7: tship:='&shipname';
new 7: tship:='Revenge';
Enter value for newlaunchdate: 1989
old 20: tlaunched:='&NewLaunchDate';
new 20: tlaunched:='1989';
Battle date is 11-NOV-1942
Launched date is 1916
11-NOV-42 is less than 1916
PL/SQL procedure successfully completed.
SQL> spool off;