Tuesday 23 April 2019

How to create packages in PLSQL


Demonstration of working of PL/SQL Packages:
 
Aim: To create PL/SQL Package with following procedures and functions
1              Procedures:
a.       Print Total Quantity Sales Summary Report(SalesId, Date, Quantity and Total Quantity)
b.      Print Total Quantity Sales Summary Report by Date wise
2               Functions:
a.       Return employee name who made maximum sales till date
b.      Return product name soled maximum quantity till date



Procedure :
STEP-1: Create the package named as ‘Sales package’ consisting of two procedures and two functions as follows:

create or replace package sales_pkg as
              PROCEDURE sales_sid_proc;
              PROCEDURE sales_date_proc;
              FUNCTION max_emp_func RETURN char;
              FUNCTION max_prod_func RETURN char;
end sales_pkg;
/

create or replace package body sales_pkg as
procedure  sales_sid_proc is
s_sid sales.sid%type;
s_date sales."date"%type;
s_qty sales.qty%type;
tqty sales.qty%type;
CURSOR salescur IS
select sid, "date", qty from sales order by sid, "date";
BEGIN
     tqty:=0;
     dbms_output.put_line('SID' || '          '|| 'DATE' || '         ' || 'QTY' || '      ' ||     'TOTAL QTY');
     dbms_output.put_line('---------' || '  '|| '--------' || '     ' || '-------' || '     ' || '---------');
     OPEN salescur;
     LOOP
              FETCH salescur into s_sid, s_date, s_qty;
              tqty:=tqty+s_qty;
              EXIT WHEN salescur%notfound;  
              dbms_output.put_line(s_sid || '        '|| s_date || '        ' || s_qty || '        ' ||  tqty);
     END LOOP;
     CLOSE salescur;
end sales_sid_proc;



procedure  sales_date_proc is
s_sid sales.sid%type;
s_date sales."date"%type;
s_qty sales.qty%type;
tqty sales.qty%type;
temp1 sales.sid%type;
temp2 sales.sid%type;
CURSOR salescur IS
select sid, "date", qty from sales order by "date";
BEGIN
       tqty:=0; 
       dbms_output.put_line('SID' || '          '|| 'DATE' || '           ' || 'QTY' || '      ' ||     'TOTAL QTY');
       dbms_output.put_line('---------' || '  '|| '--------' || '     ' || '-------' || '     ' || '---------');
       OPEN salescur;
       LOOP
              FETCH salescur into s_sid, s_date, s_qty;
              tqty:=tqty+s_qty;
              EXIT WHEN salescur%notfound;  
              dbms_output.put_line(s_sid || '        '|| s_date || '        ' || s_qty || '        ' ||  tqty);
       END LOOP;
      CLOSE salescur;
end sales_date_proc;


function max_emp_func return char is
max_emp varchar(20);
BEGIN
 Select ename into max_emp from emp inner join sales on emp.eid=sales.eid where    sales.qty=(select max(qty) from sales);
               RETURN max_emp;
end max_emp_func;


FUNCTION max_prod_func RETURN char iS
max_prod varchar(20);
BEGIN
 Select pname into max_prod from prod inner join sales on prod.pid=sales.pid where           sales.qty=(select max(qty) from sales);
               RETURN max_prod;
END max_prod_func;

END sales_pkg;
/



STEP-2: Call the procedures and functions from the above created package as follows:

set serveroutput on;
begin
dbms_output.put_line('Sales Summary Report');
sales_pkg.sales_sid_proc;
dbms_output.put_line('Sales Summary Report Date wise');
sales_pkg.sales_date_proc;
dbms_output.put_line('Employee who made highest sales: '||sales_pkg.max_emp_func);
dbms_output.put_line('The highest sold product is: '||sales_pkg.max_prod_func);
end;
/

Refer the following video for more details: 



Wednesday 8 November 2017

Implement and Study the Performance of GSM Network on NS2 (Simulation of GSM network Handoff process in NS2)


Here we are simulating the Handoff process in GSM cellular networks.The term cellular refers to the fact that the region covered by a cellular network is partitioned into a number of geographic coverage areas, known as cells, shown as hexagons on the left side of Figure. Each cell contains a base transceiver station (BTS) that transmits signals to and receives signals from the mobile stations in its cell. A GSM network’s base station controller (BSC) will typically service several tens of base transceiver stations. The role of the BSC is to allocate BTS radio channels to mobile subscribers, perform paging (finding the cell in which a mobile user is resident), and perform handoff of mobile users. The mobile switching center (MSC) plays the central role in user authorization and accounting (e.g., determining whether a mobile device is allowed to connect to the cellular network), call establishment and teardown, and handoff.

Network:
                             
                                 
Program:

#===================================
# Simulation parameters setup
#===================================
set val(chan) Channel/WirelessChannel ;# channel type
set val(prop) Propagation/TwoRayGround ;# radio-propagation model
set val(netif) Phy/WirelessPhy ;# network interface type
set val(mac) Mac/802_11 ;# MAC type
set val(ifq) Queue/DropTail/PriQueue ;# interface queue type
set val(ll) LL ;# link layer type
set val(ant) Antenna/OmniAntenna ;# antenna model
set val(ifqlen) 50 ;# max packet in ifq
set val(nn) 6 ;# number of mobilenodes
set val(rp) AODV ;# routing protocol
set val(x) 1052 ;# X dimension of topography
set val(y) 600 ;# Y dimension of topography
set val(stop) 10.0 ;# time of simulation end

#===================================
# Initialization
#===================================
#Create a ns simulator
set ns [new Simulator]

#Setup topography object
set topo [new Topography]
$topo load_flatgrid $val(x) $val(y)
create-god $val(nn)

#Open the NS trace file
set tracefile [open out.tr w]
$ns trace-all $tracefile

#Open the NAM trace file
set namfile [open out.nam w]
$ns namtrace-all $namfile
$ns namtrace-all-wireless $namfile $val(x) $val(y)
set chan [new $val(chan)];#Create wireless channel

#===================================
# Mobile node parameter setup
#===================================
$ns node-config -adhocRouting $val(rp) \
-llType $val(ll) \
-macType $val(mac) \
-ifqType $val(ifq) \
-ifqLen $val(ifqlen) \
-antType $val(ant) \
-propType $val(prop) \
-phyType $val(netif) \
-channel $chan \
-topoInstance $topo \
-agentTrace ON \
-routerTrace ON \
-macTrace ON \
-movementTrace ON

#===================================
# Nodes Definition
#===================================
#Create 6 nodes
set n0 [$ns node]
$n0 set X_ 303
$n0 set Y_ 302
$n0 set Z_ 0.0
$ns initial_node_pos $n0 20
set n1 [$ns node]
$n1 set X_ 527
$n1 set Y_ 301
$n1 set Z_ 0.0
$ns initial_node_pos $n1 20
set n2 [$ns node]
$n2 set X_ 748
$n2 set Y_ 300
$n2 set Z_ 0.0
$ns initial_node_pos $n2 20
set n3 [$ns node]
$n3 set X_ 952
$n3 set Y_ 299
$n3 set Z_ 0.0
$ns initial_node_pos $n3 20
set n4 [$ns node]
$n4 set X_ 228
$n4 set Y_ 500
$n4 set Z_ 0.0
$ns initial_node_pos $n4 20
set n5 [$ns node]
$n5 set X_ 305
$n5 set Y_ 72
$n5 set Z_ 0.0
$ns initial_node_pos $n5 20

#===================================
# Generate movement
#===================================
$ns at 2 " $n5 setdest 900 72 75 "

#===================================
# Agents Definition
#===================================
#Setup a TCP connection
set tcp0 [new Agent/TCP]
$ns attach-agent $n4 $tcp0
set sink1 [new Agent/TCPSink]
$ns attach-agent $n5 $sink1
$ns connect $tcp0 $sink1
$tcp0 set packetSize_ 1500


#===================================
# Applications Definition
#===================================
#Setup a FTP Application over TCP connection
set ftp0 [new Application/FTP]
$ftp0 attach-agent $tcp0
$ns at 1.0 "$ftp0 start"
$ns at 10.0 "$ftp0 stop"


#===================================
# Termination
#===================================
#Define a 'finish' procedure
proc finish {} {
global ns tracefile namfile
$ns flush-trace
close $tracefile
close $namfile
exec nam out.nam &
exit 0
}
for {set i 0} {$i < $val(nn) } { incr i } {
$ns at $val(stop) "\$n$i reset"
}
$ns at $val(stop) "$ns nam-end-wireless $val(stop)"
$ns at $val(stop) "finish"
$ns at $val(stop) "puts \"done\" ; $ns halt"
$ns run


awk:

BEGIN{
count1=0
pack1=0
time1=0
}
{
if($1=="r" && $3=="_5_" && $4=="AGT")
{
count1++
pack1=pack1+$8
time1=$2
}
}
END{
printf("The Throughput from n4 to n5: %f Mbps \n", ((count1*pack1*8)/(time1*1000000)));
}

Output: