PL/SQL - Part 2

PL/SQL - Part 2

/*input data from the user and insert*/

SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
nm varchar2(10);
db date;
p number;
dep varchar2(3);
BEGIN
/*user input*/
nm:=&nm;
db:=&db;
p:=&p;
dep:=&dep;
/*insert query*/
INSERT INTO Test1
(name, dob, per, dept)
VALUES(nm,db,p,dep);
/*message*/
DBMS_OUTPUT.PUT_LINE('Record Inserted!');
END;
/

-----------------------------------------------------------------------
/*Enter the Name and update the record with new per*/
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
nm varchar2(10);
newp number;
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter the name to search: ');
nm:=&nm;
DBMS_OUTPUT.PUT_LINE('Enter the new per: ');
newp:=&newp;
/*UPDATE Query*/
UPDATE Test1
SET per = newp
WHERE name = nm;
/*message*/
DBMS_OUTPUT.PUT_LINE('Record Updated!');
END;
/

-------------------------------------------------------------------------

/*Enter the Name and delete the record*/
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
nm varchar2(10);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter the name to delete: ');
nm:=&nm;
/*DELETE Query*/
DELETE FROM Test1
WHERE name = nm;
/*message*/
DBMS_OUTPUT.PUT_LINE('Record Deleted!');
END;
/

------------------------------------------------------------------------

/*
Read roll, new m1 and new m2  from the user
Update m1 , m2  and per of that user only
Display the result of that user - invalid
*/

SET ECHO ON;
SET SERVEROUTPUT ON;

DECLARE
/*Variable declaration*/
rl number;
newm1 number(3,1);
newm2 number(3,1);
newper number(3,1);
BEGIN
/*get the roll, newm1, newm2 from the user*/
rl := &rl;
newm1 := &newm1;
newm2 := &newm2;
/*compute the new per*/
newper := (newm1+newm2)/2.0;
/*Update this new data in the table using the UPDATE DML query*/
UPDATE stutab SET m1 = newm1, m2 = newm2, per = newper WHERE roll = rl;
/*display the result of that specific roll using SELECT statement*/
/*SELECT * FROM stutab WHERE roll = rl;*/
END;
/

/*
read all the col values of a table from the user & insert that data into the table
*/


SET ECHO ON;
SET SERVEROUTPUT ON;

DECLARE
rl number;
nm varchar2(15);
db date;
x1 number(3,1);
x2 number(3,1);
p number(3,1);
dep varchar2(3);
BEGIN
rl := &rl;
nm := &nm;
db := &db;
x1 := &x1;
x2 := &x2;
dep := &dep;

p := (x1+x2)/2.0;

INSERT INTO stutab VALUES(rl,nm,db,x1,x2,p,dep);

DBMS_OUTPUT.PUT_LINE('Record Inserted');
END;
/


/*
Find the roll no of the student who has the MIN per.
DELETE that student's record.
*/

SET ECHO ON;
SET SERVEROUTPUT ON;

DECLARE
rl number;
BEGIN
/*find the roll no of the MINper student*/
SELECT roll INTO rl FROM stutab WHERE per = (SELECT MIN(per) FROM stutab);
/*delete the roll's record*/
DELETE FROM stutab WHERE roll = rl;
DBMS_OUTPUT.PUT_LINE('Record of roll no. '||rl||' Deleted!');
END;
/

/*
Find the roll no of the student whose dob is not present.
DELETE that student's record.
*/

SET ECHO ON;
SET SERVEROUTPUT ON;

DECLARE
rl number;
BEGIN
/*find the roll no of the MINper student*/
SELECT roll INTO rl FROM stutab WHERE dob IS NULL;
/*delete the roll's record*/
DELETE FROM stutab WHERE roll = rl;
DBMS_OUTPUT.PUT_LINE('Record of roll no. '||rl||' Deleted!');
END;
/