PL/SQL - Part 5

PL/SQL - Part 5

/*
Get the roll, name, dob, m1 , m2 & dept from the user
and insert insert into the stutab
*/

SET ECHO ON;
SET SERVEROUTPUT ON;

DECLARE
/*Variable declaration*/
rl number;
nm varchar2(15);
db date;
x1 number(3,1);
x2 number(3,1);
p number(3,1);
dp varchar2(3);
BEGIN
/*user input*/
rl:=&rl;
nm:=&nm;
db:=&db;
x1:=&x1;
x2:=&x2;
dp:=&dp;
/*calculate per*/
p:=(x1+x2)/2.0;
/*Insert into stutab*/
INSERT INTO stutab(roll,name,dob,m1,m2,per,dept) VALUES(rl,nm,db,x1,x2,p,dp);

DBMS_OUTPUT.PUT_LINE('Record of roll no. '||rl||' inserted and per is '||p||'!');
END;
/

/*
Enter Roll no, new m1, new m2.
Update the per of that student in the table
*/

SET ECHO ON;
SET SERVEROUTPUT ON;

DECLARE
/*Variable declaration*/
rl number;
newm1 number(3,1);
newm2 number(3,1);
newper number(3,1);
BEGIN
/*user input*/
rl:=&rl;
newm1:=&newm1;
newm2:=&newm2;
/*caluclate per*/
newper:=(newm1+newm2)/2.0;
/*Update the table*/
UPDATE stutab SET m1=newm1,m2=newm2,per=newper WHERE roll=rl;

DBMS_OUTPUT.PUT_LINE('Record of roll no. '||rl||' updated with new per - '||newper||'!');
END;
/

/*
Enter roll from user & delete that record
*/

SET ECHO ON;
SET SERVEROUTPUT ON;

DECLARE
/*Variable declaration*/
rl number;
BEGIN
/*user input*/
rl:=&rl;
/*Delete record*/
DELETE FROM stutab WHERE roll=rl;
DBMS_OUTPUT.PUT_LINE('Record of roll no. '||rl||' deleted !');
END;
/


/*
Display the roll no.,name & per of the student who has the max per.
*/

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
rl number;
nm varchar2(25);
p number(3,1);
BEGIN
/*retreiev data from SELECT COmmand*/
SELECT roll INTO rl
FROM stutab
WHERE per = (SELECT MAX(per) FROM stutab);

SELECT name INTO nm
FROM stutab
WHERE per = (SELECT MAX(per) FROM stutab);

SELECT per INTO p
FROM stutab
WHERE per = (SELECT MAX(per) FROM stutab);

/*display the data*/
DBMS_OUTPUT.PUT_LINE('The details of student having highest per:');
DBMS_OUTPUT.PUT_LINE('Roll: '||rl||' , Name: '||nm||' , Per: '||p);
END;
/

/*
delete the record of the student where DOB is NULL
*/

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
rl number;
BEGIN
/*check which student does not have dob present*/
SELECT roll INTO rl FROM stutab WHERE dob IS NULL;
/*delete the record*/
DELETE FROM stutab WHERE roll = rl;
DBMS_OUTPUT.PUT_LINE('Record of roll no. '||rl||' deleted!');
END;
/