0% found this document useful (0 votes)
38 viewsCursor
Uploaded by
Nick DamorCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
0% found this document useful (0 votes)
38 viewsCursor
Uploaded by
Nick DamorCopyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF or read online on Scribd
/ 31
Chapter 9: Cursors
.
(Implicit Cursors)
For the standard DEPT and EMP tables available in user SCOTT/TIGER,
increase the salary by 10% of the employee whose EMPNO is taken as input.
Display messages regarding this update.
DECLARE
NO EMPEMPNO%TYPE;
BEGIN
UPDATE EMP
; SET SAL = SAL * 1.1 WHERE EMPNO = &NO;
IF SQL%NOTFOUND THEN
DBMS_OUTPUT.PUT_LINE(EMPLOYEE DOES NOT
EXIST’);
ELSE :
DBMS_OUTPUT.PUT_LINE(‘SALARY UPDATED");
END IF;
END;
Write a PL/SQL block, which takes as input an employce name. If this employee
earns more than 5000 then give him commission, which is 25% of his salary, and
if he earns less than 1000 then remove him from the EMP table. Give appropriate
messages.
DECLARE ;
NAME EMP.ENAME%TYPE;
107SQL & PL/SQL Practice Book
BEGIN
NAME
UPDATE EMP SET COMM = NVL(COMM,0) + 0.25*SAL
WHERE ENAME = NAME AND SAL > 5000;
IF SQL%FOUND THEN
‘&NAME’;
DBMS_OUTPUT-PUT_LINE(NAME|’ EARNS MORE THAN
5000");
ELSE
DELETE FROM EMP
WHERE ENAME = NAME AND SAL If the worker is specialized in ‘CARPENTRY’ increase his wage by 10%.
If the worker is specialized either in ‘PAINTING’ or
the wage by 15%,
v
‘FABRICATION’ then increa®
For each worker having wage less than 50, update the wage to 50.
v
Im each of the above cases display the total number of workers whose wage bi
been changed as per condition,Cursors
VORKER (YORKER 1D, NAM WAGE_PER_HOUR, SPECIALISED_IN,
MANAGER_ID)
BEGIN
UPDATE WORKER
SET WAGE_PER_HOUR = WAGE_PER_HOUR * 1.1
WHERE SPECIALISED_IN = ‘CARPENTRY’;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘TOTAL WORKER:
CARPENTRY ‘|| TO_CHAR(SQL%ROWCOUNT);
§ SPECIALIZED IN
END IF;
UPDATE WORKER
SET WAGE_PER_HOUR = WAGE_PER_HOUR * LIS
WHERE SPECIALISED_IN IN (‘PAINTING’, ‘FABRICATION’ );
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘TOTAL. WORKERS SPECIALIZED IN
PAINTING OR FABRICATION ‘|| TO_CHAR(SQL%ROWCOUNT),
END IF;
UPDATE WORKER
SET WAGE_PER_HOUR = 50
WHERE WAGE_PER_HOUR 5;
DBMS_OUTPUTPUT_LINE(WORKER_ID||NAME|
|WAGE_PER_HOUR);
END LOOP;
CLOSE Cl;
END;
110DECLARE
DN DEPT.DNAME% TYPE;
D _DEPT.DEPTNO% Type;
FLAG BOOLEAN;
E EMP%ROWTYPE;
CURSOR C1 IS SELECT * FROM EMP;
BEGIN
FLAG := FALSE;
DN := ‘&DNAME’;
SELECT DEPTNO INTO D
FROM DEPT
WHERE DEPTNO IN
(SELECT DEPTNO
FROM DEPT
WHERE UPPER(DNAME) = UPPER(DN));
OPEN C1;
Loop
FETCH C1 INTO E;
EXIT WHEN (C1%NOTFOUND;
IF EDEPTNO = D THEN
IF (TO_NUMBER(TO_CHAR(SYSDATE,'YYYY'))
111: PLISQL Practice Book -
SLs TO_NUMBERTO_CHAR(E-HIREDATE, YYYY", :
ieee ceneieeceteer TE-ENAM,
* [IE-HIREDATE);
FLAG := TRUE;
END IF;
END IF;
END LOOP;
CLOSE C1;
IF FLAG = FALSE THEN
DBMS_OUTPUT-PUT_LINE(‘NO EMPLOYEE HAS WORKED
FOR 5 YEARS IN DEPARTMENT : ‘IDN);
END IF;
DN DEPT.DNAME%TYPE;
FLAG BOOLEAN;
CURSOR Cl(NM DEPT.DNAME%TYPE) Is
SELECT * FROM EMP
WHERE (TO_NUMBER(TO_CHAR(SYSDATE,YYYY")) .
TO.NUMBER(TO_CHAR(HIREDATE-"YYYy")) >= 5
AND DEPTNO IN
(SELECT DEPTNO FROM DEPT
WHERE UPPER(DNAME) = UPPER(NM));
Rl Ci%ROWTypR;
112Cursors
BEGIN
DN := ‘&DN’;
OPEN CI(DN);
LOOP
FETCH Cl INTO RI;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT:PUT_LINE(RI.EMPNO|/ ‘|RLLENAME||
* |IRL-HIREDATE);
END LOOP;
CLOSE Cl;
END;
4, For each job, display the worker ids of workers working on it, the total days each
worker will work and the grand total of days assigned.
WORKER. (WORKER ID, NAME, WAGE_PER_HOUR, SPECIALISED_IN,
MANAGER _ID)
JOB (JOB ID, TYPE_OF_JOB, STATUS)
JOB_ASSIGNED (WORKER ID,JOB ID,STARTING_DATE, NUMBER_OF_DAYS)
DECLARE
CURSOR C1 IS SELECT * FROM JOB;
CURSOR C2 (JID JOB.JOB_ID%TYPE) IS
SELECT * FROM JOB_ASSIGNED
WHERE JOB_ID = JID;
0:
G_TOTAL. NUMBER(7) ::
BEGIN
13Book
SQL & PL/SQL
FOR RI IN Cl
LOOP
DBMS_OUTPUTPUT_LINE(‘JOB : ‘|R1.TYPE_OF_JOB);
DBMS_OUTPUTPUT_LINE('WORKER ID DAYS ASSIGNED),
FOR R2 IN C2 (RI.JOB_ID)
Loop
G_TOTAL := G_TOTAL + R2.NUMBER_OF_DAYS; -
DBMS_OUTPUT.PUT_LINE(R2.WORKER_ID]|’
‘|IR2.NUMBER_OF_DAYS);
END Loop;
DBMS_OUTPUT.PUT_LINE(‘ 3
DBMS_OUTPUTPUT_LINE(TOTAL DAYS ASSIGNED ‘FOR
JOB ‘| R1.TYPE_OF_JOB I: Ml G_TOTAL);
G_TOTAL := 0;
END Loop;
END;
4
For each worker in the W
‘ORKER table, pass the worker ;
> rker id O
the JOB_ASSIGNED table and display d Bare
etails of only those wo a
‘completed their work, (STARTING_DATE + NUMBER OF ee, ir
= ' _OF_DAYS = 5000 increase by 500 + 12% of salary above 7000
"
”
Select .. For Update Being Used In Cursor
“Cursor - For Loop Used
DECLARE .
CURSOR C1 IS SELECT SAL, COMM FROM EMP FOR UPDATE; ;
us=
1 PUNE Beactioe Hook
AMT DNA ee
ncn
VOR IRE IN GC!
Looe
WECRLSAL | NVLUULCOMM, 0)) 7000 THEN
BLAM
E_AMT
END IF;
UPDATE, EMP
SET SAL = SAL + 500 4 B_AMT
WHERE CURRENT OF C1;
END IP;
END Loop;
COMMIT,
END;
6a : Cursors
suppose you have created the following three tables in the database. Write a PL/
. sQL block which will read records one by one from the PART_TRANS table and
make necessary updates in the PART_MASTER table. However if after the up-
date the TOT_QTY is going below the REORDER_LEVEL then the
PART_MASTER is not to be updated and the necessary details are stored in the
TEMP table.
PART_MASTER (P#, PNAME, PRICE, TOT_QTY, REORDER_ LEVEL)
PART_TRANS (O#, P#, QTY_REQ) Where O# is the order no.
TEMP (P#, QTY)
DECLARE
CURSOR CT IS SELECT * FROM PART_TRANS; :
CURSOR CM(PNO PART_MASTER.P#%TYPE) IS
SELECT * FROM PART_MASTER
WHERE P# = PNO;
BEGIN
FOR RT IN CT
LOOP
FOR RM IN CM(RTP#) E
Loop
IF (RM.TOT_QTY - RT-QTY_REQ) GENERAL : 1
dos SC za
> ASE 73
> SEBC’ oat od
> PH. : 5
> ~~ EX-SM : 6Cursors
DECLARE
CANDIDATE.RANKNO%TYPE;
RI
p2 CANDIDATE.RANKNO%TYPE;
3 VARCHAR2(4);
CURSOR Cl IS SELECT CATEGORY
FROM CANDIDATE
GROUP BY CATEGORY;
CURSOR C2 (C CANDIDATE.CATEGORY%TYPE) JS
SELECT * FROM CANDIDATE.
WHERE CATEGORY = C
ORDER BY PERCENTAGE DESC;
CNT NUMBER(4);
BEGIN
FOR RECI IN Cl
LOOP
nae anne
CNT = 0;
IF RECI.CATEGORY = ‘GENERAL’ THEN
R3:= RG’;
ELSIF RECI.CATEGORY = ‘SC’ THEN
© RB c= R3lf2";
ELSIF RECI.CATEGORY = ‘ST’ THEN
R3 = R33";
ELSIF RECL.CATEGORY = ‘SEBC’ THEN
gySQL & PLISQL Practice Book
R3 := R35";
| ELSE .
t R3 := R3|l'6";
| END IF;
FOR REC2 IN C2 (REC1.CATEGORY)
LOop
CNT := CNT + 1;
R2 := LPAD(TO_CHAR(CNT), 4, 0);
RI := R3IIR2;
UPDATE CANDIDATE,
SET RANKNO = RI
WHERE FORMNO = REC2.FORMNO;
END Loop;
END LOop;
commarr,
END;
8. For the given table, check which students have to pay fine and the amount
fine to be paid. A student can keep the book for 15 days. If the number of &
has exceeded 15 then, the fine is calculated as follows:
Upto 7 days: 50 paise per day z
B15 days -; Rell'per day (to be counted from the 8th day onwass)
i
More than 15: Re 1.5 per day (60 be counted from the 16th day on!
120Cursors
printed out.
LNO, BOOKNO, ISSUE_DATE, RETURN_DATE,
can Tater be
pINE (ROL
| qor_DAYS, FINE)
pEcLARE
uM NUMBER (3);
FINE NUMBER (5,2) := 0;
CURSOR C1 IS SELECT * FROM ISSUE;
TEMP NUMBER (3);
BEGIN
FOR RI IN Cl
LOOP
TEMP := RI.RETURN_DATE - R1.ISSUE_DATE;
TEMP := TEMP - 15 ;
IF (TEMP > 7) THEN.
NUM := TEMP - 7;
FINE := 3.5;
IF (NUM > 8) THEN
FINE := FINE + 8
NUM := NUM - 8;
FINE :=FINE + NUM * 1.5;
ELSE ;
FINE := FINE + NUM;
121SQL & PLISQL Practice Book
F (TEMP > 0) THEN
TEMP * 0.5;
FINE
END IF;
IF (FINE > 0) THEN
INSERT INTO FINE VALUES (RI-ROLLNO,
RIBOOKNO, RLISSUE_DATE, R1-RETURN_DATE,
RI.RETURN_DATE - RI.ISSUE_DATE, FINE);
END IF ;
END LOOP;
COMMIT:
END;
Write a PL/SQL block, which will accept the entrance test id and test center
from user and display details about all those applicants who have scored mn
than average for that test. Also display details about the dates on which that te
center had full attendance.
APPLICANT (AID, ANAME, ADDR, ABIRTH_DT)
ENTRANCE_TEST (ETID, ETNAME, MAX_SCORE, CUT_SCORE)
ETEST_CENTRE (ETCID, LOCATION, INCHARGE, CAPACITY)
ETEST_DETAILS (AID, ETID, ETCID, ETEST_DT, SCORE)
DECLARE
T_VAR_ID ENTRANCE_TEST.ETID%TYPE;
TC_VAR_ID ETEST_CENTRE.ETCID%TYPE;
/* ‘cursor Cl finds the average score for :
the input test Id and test centre Id Ws1 (T_VAR_ID ENTRANCE TID%TYP
T_CENTRE.ETCID%TYPE) 15
SELECT ETID, AVG (SCORE) AS AVERAGE
CURSOR
7C_VARID
FROM ETEST_DETAILS
WHERE ETCID = TC_VAR_ID
AND, ETID = T_VAR_ID;
cursor C2 finds all details of the test taken for
the input test Id and test centre Id 4
CURSOR C2 (T_VAR_ID ENTRANCE_TEST.ETID@TYPE,
TC_VAR_ID ETEST_CENTREETCID%TYPE) IS
SELECT * FROM ETEST_DETAILS
WHERE ETCID = TC.VAR_ID
AND ETID= T_VAR_ID;
cursor C3 finds the dates on which the test centre
had full attendance y
CURSOR C3 (TC_VAR_ID ETEST_CENTREETCID%TYPE) IS
SELECT ETEST_DT
FROM ETEST_DETAILS
WHERE ETCID = TC_VAR_ID
GROUP BY ETEST_DT
HAVING COUNT(*) =
(SELECT CAPACITY
: FROM ETEST_CENTRE
WHERE ETCID = TC_VAR_ID);
123SQL & PLISQL Practice Book
'&T_VAR_ID’
TC_VARID := '&TC_VAR_ID';
FOR RI IN CI(T_VAR_ID, TC_VAR_ID)
LOOP
FOR R2 IN C2(T_VAR_ID, TC_VAR_ID)
Loop
IF (R2.SCORE > RI.AVERAGE) THEN
FOR CAN_REC IN (SELECT * FROM
APPLICANT WHERE AID=R2.AID)
Loop
DBMS_OUTPUT.PUT_LINE (CAN_REC AID
‘| CAN_REC.ANAME ||’ TEAM(TEAM_ID ,TEAM_NAME );
PLAYER (TEAM_ID,PID,PNAME ,PBIRTHDAY,);
BOWLING(TEAM_ID, BOWLER_ID ,OVER, MAIDEN, RUN
WICKET_TAKEN) :
SCORE(TEAM_ID, PID, RUNS_MADE, OUT_TYPE, BALLER_Ip)
Ciyp,
EXTRA_RUN((TEAM_ID,WIDE_RUN NUMBER(2), NO_RUN , BYE Ruy
DECLARE
TOT NUMBER(4):=0;
TID VARCHAR2(30);
TPID VARCHAR2(5);
PNM VARCHAR2(30);
BNM VARCHAR2(30);
s SCORE%ROWTYPE;
T TEAM%ROWTYPE;
CURSOR Cl IS SELECT PID, PNAME FROM PLAYER
WHERE TEAM_ID = TID;
CURSOR C2 IS SELECT * FROM SCORE
WHERE PID = TPID AND TEAML_ID = TID;
CURSOR C3 IS SELECT PNAME FROM PLAYER
WHERE TEAM_ID = S.BTEAM_ID
AND PID = S.BALLER_ID;
BEGIN
TID:= *&TEAM_ID';
SELECT * INTO T FROM TEAM WHERE TID=TEAM_ID;
128OUT TYPE'|!’ BOWLER
pnts OUTPUTPUT_LINECNAME’
NAME")
OPEN Cl:
“SCORE’);
LooP
FETCH Cl INTO TPID, PNM;
EXIT WHEN CI%NOTFOUND;
DBMS_OUTPUTPUT(RPAD(PNM,I5)||"_*);
OPEN C2;
FETCH C2 INTO S;
IF C2%FOUND THEN
IF S.BALLER_IDSQL & PL/SQL Practice Book
CLOSE C1;
DBMS_OUTPUT.PUT_LINE(TOTAL SCORE : ‘||TOT);
END;
12, A supermarket maintains its stock systematically and wants 9 print the 4,
the following format, Write a PL/SQL code to execute it using the tables gi.
ITEM_MASTER(T_CODE,IT_NAME,IT_STOCKIT_UNITAT_ pricy,
CUST_MASTER(CUST_CODE,CUST_NAME.CUST_ADD.DUE_AMoty,
BILL(BILL_NO.BILL_DATE, CUST_CODE)
BILL_TRAN(BILL_NO,IT_CODE,IT_QTY)
CUSTOMER :
BILL NO
=
BILL DATE : Dat,
SR.NO. TEM NAME QTy UNIT _PRICE AMOUNT
1
TOTAL AMOUNT :
CCODE CUST_MASTER.CUST_CODE&TYPE:
CNAME CUST. —MASTER.CUST_NAMESZTYPE;
CaDD CUST_MASTER.CUST_ADD&TYPE:
BNO BILLBILL_NOZTYPE;
BDATE BILL BILL_DATESTYPE:
ITEM_MASTER.IT_NAMESZTYPE:
msCursors
ae ITEM_MASTER.IT_PRICE%TYPE;
a BILLTRAN.IT_QTY%TYPE;
BILL.BILL_NO®TYPE;
i NUMBER(10);
jawt. —- NUMBER(10);
NUMBER(3) := 1;
SR
cuRSOR C IS SELECT CUST_MASTER.CUST_CODE, CUST_NAME, CUST_ADD,
sL_NO, BILL_DATE FROM CUST_MASTER, BILL WHERE BILL_NO = B AND
(ist. MASTER.CUST_CODE = BILL.CUST_CODE;
nso I IS SELECT IT_NAME, IT_QTY, IT_UNIT, IT PRICE
ROM ITEM_MASTER, BILLTRAN WHERE
TEM MASTER.IT_CODE = BILLTRAN.IT_CODE AND BILL_NO = B:
BEGIN
B= &BILL_NO;
OPEN C;
LOOP
FETCH C INTO CCODE,CNAME,CADD,BNO,BDATE;
EXIT WHEN C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘CUSTOMER:’);
DBMS_OUTPUTPUT_LINE(‘CODE: ‘ || CCODE || *
‘Il BILL No.: « || BNO);
DBMS_OUTPUTPUT_LINE(‘NAME: “|| CNAME || ¢
‘I BLL DaTE.: + || BDATE);
PBMS_OUTPUT.PUT_LINE(‘ADDRESS: + || CADD);
131
he= ee,
SQL & PL/SQL Practice Book
DBMS_OUTPUT.PUT_LINEC:
a OS
i DBMS_OUTPUTPUT_LINECSR NO. ITEM NAMB.
: UNIT PRICE AMOUNT"); Ory
DBMS_OUTPUT-PUT LINE(—
S eee
END LOOP;
CLOSE C;
AMT
TAMT
OPEN I;
LOOP
FETCH I INTO INAME,IQTY,IUNIT,IPRICE;
EXIT WHEN I%NOTFOUND;
AMT := IQTY * IPRICE;
DBMS_OUTPUT.PUT_LINE(SR || *. * || INAME |]
“Wiery ys‘ TUNIT||* ‘PRICE ||‘ + || AMT;
TAMT := TAMT + AMT;
SR := SR + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE(*_________——
Ds
DBMS_OUTPUT.PUT_LINE(‘TOTAL AMOUNT : ‘ || TAMT);
CLOSE I;
END;
132a N
Cursors
oC li
‘ cursos)
ak
i wi eanptoyee code, mame, sabi and departnent name for the employees
yt
‘ - sgatany IS more
, PUTIGRR and use cursor vartabley
than the Input vate, (Use the standard EMP and DEPT
pwc ol soo!
pw ANU
BMBSALW TYPE;
§
pvp RHC IS RECORD
ane EMBEMPNOWTYPE,
PMENM | BMRENAMBS TYPE,
yaw BMRSALPYR,
DAMENM. DEPRDNAMBYTYPE);
EMIREC RECS
Using a strong ref cursor type y j
TOE TCUR IS REF CURSOR RETURN REC;
EMICUR TCR:
SAIN
Seedy
OPEN EMMCUR FOR SELECT EMPNO, ENAME, SAL, DNAME i
FROM EMP, DEPT i
WHERE EMPDEPTNO = DEPTDEPTNO
AND SAL > S;
Loop
FETCH EMPCUR INTO EMPREC;
EXIT WHEN. BMPCURGNOTFOUND:
ae
133DIMS_OL “y
DDMS_OUTPUT.PU!
DEPINM {| *
s
piiMs_OUT
DIMS_O1
END LOOP,
yMP
STUDENT (ROLL.NO, NAME, MARKSI, MARKS2, MARKS3,
JASQL, block, which calculates the percentage for exch
warks ws 100 for ea
write w
i puns
splay the detally al ve scored highest
”
ANY.S ANY
NM
MKS
vw
"
MAXI
MAK2
MAKS
™
IN
FOR RI IN CI
Loop
IF REMARKS! > 40 any RIMARKSD » gg
RKS2 > 49
AND RUMARKS3 > 49 THe,
RLMARKSI + RiMARKs? RiManKsa,
F Pls via;
ELSE
PIL = NULL;
END
UPDATE STUDENT
PERCENTAGE = 1
WHERE ROLLNO = RILROLLNO;
IP MAXI You might also like
