PL/SQL - Part 6
display the rollno of the student who has the highest per
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
rl number(2);
BEGIN
SELECT rollno INTO rl FROM stutab
WHERE per=(SELECT MAX(per)
FROM stutab);
DBMS_OUTPUT.PUT_LINE('Student who has max per: '||rl);
END;
/
get the rollno from the user & display the name & dept of student
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
rl number(2);
nm varchar2(20);
dep varchar2(4);
p number(4,2);
BEGIN
/*take user input*/
rl:=&rl;
/*find the name*/
SELECT name INTO nm
FROM stutab
WHERE rollno=rl;
/*find the dept*/
SELECT dept INTO dep
FROM stutab
WHERE rollno=rl;
/*find the per*/
SELECT per INTO p
FROM stutab
WHERE rollno=rl;
/*display the results*/
DBMS_OUTPUT.PUT_LINE('Roll no: '||rl);
DBMS_OUTPUT.PUT_LINE('Name: '||nm);
DBMS_OUTPUT.PUT_LINE('Department: '||dep);
DBMS_OUTPUT.PUT_LINE('Percentage: '||p);
END;
/
get the rollno from the user & display the name & dept of student
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
rl number(2);
nm varchar2(20);
dep varchar2(4);
p number(4,2);
BEGIN
/*take user input*/
rl:=&rl;
/*find the name*/
SELECT name, dept,per INTO nm, dep,p
FROM stutab
WHERE rollno=rl;
/*display the results*/
DBMS_OUTPUT.PUT_LINE('Roll no: '||rl);
DBMS_OUTPUT.PUT_LINE('Name: '||nm);
DBMS_OUTPUT.PUT_LINE('Department: '||dep);
DBMS_OUTPUT.PUT_LINE('Percentage: '||p);
END;
/
illegal
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
rl number(2);
nm varchar2(20);
dep varchar2(4);
p number(4,2);
BEGIN
/*find the name*/
SELECT name, dept,per INTO nm, dep,p
FROM stutab;
/*display the results*/
DBMS_OUTPUT.PUT_LINE('Roll no: '||rl);
DBMS_OUTPUT.PUT_LINE('Name: '||nm);
DBMS_OUTPUT.PUT_LINE('Department: '||dep);
DBMS_OUTPUT.PUT_LINE('Percentage: '||p);
END;
/
PL-SQL and SQL integration
-------------------------
Note:
->the pl-sql variables must be named different from the table column names.
->the pl-sql variable data type must be same as the column data type of the table.
--------------------------------------------------
eg 1: insert data into the product tab by taking input from the user.
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declarations*/
pid number(3);
pnm varchar2(20);
pr number(5,2);
BEGIN
/*user input*/
pid:=&pid;
pnm:=&pnm;
pr:=≺
/*INSERT query with the variables integrated*/
INSERT INTO product
(prodid, prodname, prodprice)
VALUES(pid, pnm, pr);
/*Message*/
DBMS_OUTPUT.PUT_LINE('Record Inserted!');
END;
/
-------------------------------------------------
eg 2: insert data into the stutab2 tab by taking input from the user and calculating the per.
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
roll number(4);
nm varchar2(15);
gen char(1);
m1 number(4,1);
m2 number(4,1);
p number(4,1);
dob date;
BEGIN
/*user input*/
roll:=&roll;
nm:=&nm;
gen:=&gen;
m1:=&m1;
m2:=&m2;
dob:=&dob;
/*calculate per*/
p:=(m1+m2)/200.0*100.0;
/*INSERT query*/
INSERT INTO stutab2
(rollno,name,gender,marks1,marks2,per,dateofbirth)
VALUES(roll,nm,gen,m1,m2,p,dob);
/*message*/
DBMS_OUTPUT.PUT_LINE('Record Inserted');
END;
/
---------------------------------------------------------------
eg 3: update the marks1, marks2 and per of a rollno. get the new marks1, new marks2 and roll to to update the record of from the user, find the new per and update the record.
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
rolltosrch number(4);
newm1 number(4,1);
newm2 number(4,1);
newper number(4,1);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter roll no. to update the record of: ');
rolltosrch:=&rolltosrch;
DBMS_OUTPUT.PUT_LINE('Enter new marks: ');
newm1:=&newm1;
newm2:=&newm2;
/*calculate*/
newper:=(newm1+newm2)/200.0*100.0;
/*UPDATE query*/
UPDATE stutab2
SET marks1=newm1, marks2=newm2, per=newper
WHERE rollno=rolltosrch;
/*Message*/
DBMS_OUTPUT.PUT_LINE('Record Updated!');
END;
/
---------------------------------------------------------------
eg 4: enter the rollno and delete that record from the stutab2;
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
rolltodel number(4);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter roll no. to delete: ');
rolltodel:=&rolltodel;
/*DELETE Query*/
DELETE FROM stutab2
WHERE rollno=rolltodel;
/*message*/
DBMS_OUTPUT.PUT_LINE('Record Deleted!');
END;
/
---------------------------------------------------------------
eg 5: insert 3 records into the product tab by taking input from the user.
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declarations*/
pid number(3);
pnm varchar2(20);
pr number(5,2);
i number(2);
BEGIN
/*loop*/
FOR i in 1..3 LOOP
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter Product ID: ');
pid:=&pid;
DBMS_OUTPUT.PUT_LINE('Enter Product Name: ');
pnm:=&pnm;
DBMS_OUTPUT.PUT_LINE('Enter Product Price: ');
pr:=≺
/*INSERT query with the variables integrated*/
INSERT INTO product
(prodid, prodname, prodprice)
VALUES(pid, pnm, pr);
/*Message*/
DBMS_OUTPUT.PUT_LINE('Record Inserted!');
END LOOP;
END;
/
---------------------------
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declarations*/
id number(3);
i number(2);
BEGIN
/*loop*/
i:=1;
WHILE i<=3 LOOP
/*user input*/
/*DBMS_OUTPUT.PUT_LINE('Enter Product ID: ');*/
id:=&id;
/*Message*/
DBMS_OUTPUT.PUT_LINE('ID: '||id);
i:=i+1;
END LOOP;
END;
/
---------------------------
=================================
Executing SELECT Statement in PL/ SQL to fetch data:
------------------------------------------------
eg 1: enter the prodid from the user and fetch it's prodname
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
pid number(3);
pnm varchar2(20);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter Prodid to search: ');
pid := &pid;
/*SELECT query to fetch data*/
SELECT prodname INTO pnm
FROM product
WHERE prodid = pid;
/*Display the data*/
DBMS_OUTPUT.PUT_LINE('Prod ID: '||pid||' ProdName: '||pnm);
END;
/
-------------------------------------
Note:
->if the record is found the column value is assigned to the var next to INTO.
->if the record is not found, the PL SQL script is crashing... error.
->INTO can only be used once in the SELECT query
--------------------------------------------------------
eg 2: enter the prodid from the user and fetch it's prodname and prodprice.
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
pid number(3);
pnm varchar2(20);
ppr number(5,2);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter Prodid to search: ');
pid := &pid;
/*SELECT query to fetch data*/
SELECT prodname INTO pnm, prodprice INTO ppr ERROR
FROM product
WHERE prodid = pid;
/*Display the data*/
DBMS_OUTPUT.PUT_LINE('Prod ID: '||pid)
DBMS_OUTPUT.PUT_LINE('ProdName: '||pnm);
DBMS_OUTPUT.PUT_LINE('ProdPrice: '||ppr);
END;
/
---------------------------------------------
eg 2: enter the prodid from the user and fetch it's prodname and prodprice. (CORRECTED But HEavy)
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
pid number(3);
pnm varchar2(20);
ppr number(5,2);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter Prodid to search: ');
pid := &pid;
/*SELECT query to fetch prodname data*/
SELECT prodname INTO pnm
FROM product
WHERE prodid = pid;
/*SELECT query to fetch prodprice data*/
SELECT prodprice INTO ppr
FROM product
WHERE prodid = pid;
/*Display the data*/
DBMS_OUTPUT.PUT_LINE('Prod ID: '||pid);
DBMS_OUTPUT.PUT_LINE('ProdName: '||pnm);
DBMS_OUTPUT.PUT_LINE('ProdPrice: '||ppr);
END;
/
----------------------------------------------------------------
---------------------------------------------
eg 2: enter the prodid from the user and fetch it's prodname and prodprice. (EVEN Better implementation)
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
pid number(3);
pnm varchar2(20);
ppr number(5,2);
BEGIN
/*user input*/
DBMS_OUTPUT.PUT_LINE('Enter Prodid to search: ');
pid := &pid;
/*SELECT query to fetch prodname, prodprice data*/
SELECT prodname, prodprice INTO pnm, ppr
FROM product
WHERE prodid = pid;
/*Display the data*/
DBMS_OUTPUT.PUT_LINE('Prod ID: '||pid);
DBMS_OUTPUT.PUT_LINE('ProdName: '||pnm);
DBMS_OUTPUT.PUT_LINE('ProdPrice: '||ppr);
END;
/
-------------------------------------------------------
EG. 3: display the details of the product that have the highest price.
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
pid number(3);
pnm varchar2(20);
ppr number(5,2);
BEGIN
/*Sub Query*/
SELECT prodid, prodname, prodprice INTO pid, pnm, ppr
FROM product
WHERE prodprice = (SELECT MAX(prodprice) FROM product);
/*display*/
DBMS_OUTPUT.PUT_LINE('Product with the max price:');
DBMS_OUTPUT.PUT_LINE('Prod ID: '||pid);
DBMS_OUTPUT.PUT_LINE('ProdName: '||pnm);
DBMS_OUTPUT.PUT_LINE('ProdPrice: '||ppr);
END;
/
-----------------------------------------------------------
Eg 4: delete the record of the product that has the least price in the table
SET ECHO ON;
SET SERVEROUTPUT ON;
DECLARE
/*variable declaration*/
pidtodel number(3);
BEGIN
/*SELECT query to get the pid of the product having least price*/
SELECT prodid INTO pidtodel
FROM product
WHERE prodprice = (SELECT MIN(prodprice) FROM product);
/*Delete Query to delete the record*/
DELETE FROM product
WHERE prodid = pidtodel;
/*message display*/
DBMS_OUTPUT.PUT_LINE('Product with ID: '||pidtodel||' deleted!');
END;
/
or
DELETE FROM product
WHERE prodid =
(
SELECT prodid
FROM product
WHERE prodprice = (SELECT MIN(prodprice) FROM product)
);
---------------------------------------------------------------
0 Comments