Monday, 13 June 2016

PLSQL PROGRAMS TO PRACTICE 10

131. Write a procedure to create your own print statement?

CREATE OR REPLACE PROCEDURE PRINT(V VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(V);
END;
/

132. Write a procedure to accept the deptno as parameter and display the details of that dept also display the total salary, no of employees, max sal and avg sal?

CREATE OR REPLACE PROCEDURE EMPPRO(DEPTNOV NUMBER)
IS
CURSOR A IS
SELECT * FROM EMP WHERE DEPTNO=DEPTNOV;
B A%ROWTYPE;
NOE NUMBER:=0;
TOT NUMBER:=0;
AVGS NUMBER(7,2):=0;
MAXS NUMBER(7,2):=0;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO :'||B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME :'||B.ENAME);
DBMS_OUTPUT.PUT_LINE('JOB :'||B.JOB);
DBMS_OUTPUT.PUT_LINE('SAL :'||B.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE :'||B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('COMM :'||B.COMM);
DBMS_OUTPUT.PUT_LINE('**********************');
TOT:=TOT+B.SAL;
NOE:=NOE+1;
IF B.SAL>MAXS THEN
MAXS:=B.SAL;
END IF;
END LOOP;
AVGS:=TOT/NOE;
DBMS_OUTPUT.PUT_LINE('NO OF EMPLOYEE :'||NOE);
DBMS_OUTPUT.PUT_LINE('TOTAL SALARY :'||TOT);
DBMS_OUTPUT.PUT_LINE('AVG SALARY :'||AVGS);
DBMS_OUTPUT.PUT_LINE('MAX SALARY :'||MAXS);
CLOSE A;
END;
/

133. Write a procedure to accept two different numbers and print all odd numbers between the two given numbers?

CREATE OR REPLACE PROCEDURE ODDNO(A NUMBER,B NUMBER)
IS
N NUMBER(4);
BEGIN
N:=A;
WHILE N<B
LOOP
IF MOD(N,2)!=0 THEN
DBMS_OUTPUT.PUT_LINE(N);
END IF;
N:=N+1;
END LOOP;
END;
/

134. Write a procedure to accept two different numbers and print even numbers between the two given numbers?

CREATE OR REPLACE PROCEDURE EVENNO(A NUMBER,B NUMBER)
IS
N NUMBER(4);
BEGIN
N:=A;
WHILE N<B
LOOP
IF MOD(N,2)=0 THEN
DBMS_OUTPUT.PUT_LINE(N);
END IF;
N:=N+1;
END LOOP;
END;
/

135. Write a procedure to accept deptno as input and print the details of emps along with grade?

CREATE OR REPLACE PROCEDURE EMP_DETAIL(DEPTNOV NUMBER)
IS
CURSOR A IS
SELECT EMP.*,GRADE FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL
AND DEPTNO=DEPTNOV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS '||B.ENAME);
DBMS_OUTPUT.PUT_LINE('JOB IS '||B.JOB);
DBMS_OUTPUT.PUT_LINE('SAL IS '||B.SAL);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS '||B.DEPTNO);
DBMS_OUTPUT.PUT_LINE('GRADE IS '||B.GRADE);
END LOOP;
CLOSE A;
END;
/

136. Write a procedure to accept a number as parameter and print its multiplication table?

CREATE OR REPLACE PROCEDURE MULT(A NUMBER)
IS
B NUMBER(2) DEFAULT 1;
C NUMBER(3);
BEGIN
WHILE B<=10
LOOP
C:=A*B;
DBMS_OUTPUT.PUT_LINE(A||'*'||B||'='||C);
B:=B+1;
END LOOP;
END;
/

137. Write a procedure to accept two different numbers as input and print all even numbers and odd numbers in between them in two different horizontal lines?

CREATE OR REPLACE PROCEDURE EVENODD(A NUMBER,B NUMBER)
IS
N NUMBER;
EV VARCHAR2(1000);
OD VARCHAR2(1000);
BEGIN
N:=A;
WHILE N<B
LOOP
IF MOD(N,2)!=0 THEN
OD:=OD||' '||N;
ELSE
EV:=EV||' '||N;
END IF;
N:=N+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE ODD NOS ARE '||OD);
DBMS_OUTPUT.PUT_LINE('THE EVEN NOS ARE '||EV);
END;
/

138. Write a procedure to accept a string and check whether it is palindrome or not?

CREATE OR REPLACE PROCEDURE STRPAL(STR VARCHAR2)
IS
STR1 VARCHAR2(10);
S VARCHAR2(10);
BEGIN
FOR I IN REVERSE 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
STR1:=STR1||S;
END LOOP;
IF STR1=STR THEN
DBMS_OUTPUT.PUT_LINE('IT IS PALINDROME '||STR1);
ELSE
DBMS_OUTPUT.PUT_LINE('IT IS NOT PALINDROME '||STR1);
END IF;
END;
/

139. Write a procedure to accept a string and print it in reverse order?

CREATE OR REPLACE PROCEDURE STRREV(STR VARCHAR2)
IS
STR1 VARCHAR2(10);
S VARCHAR2(10);
BEGIN
FOR I IN REVERSE 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
STR1:=STR1||S;
END LOOP;
DBMS_OUTPUT.PUT_LINE('ORIGINAL '||STR);
DBMS_OUTPUT.PUT_LINE('REVERSE '||STR1);
END;
/

140. Write a procedure to accept the empno and print all the details of emp along with exp, grade and loc?

CREATE OR REPLACE PROCEDURE EMP_DET(EMPNOV NUMBER)
IS
EXP NUMBER(6,2);
E EMP%ROWTYPE;
GRADEV SALGRADE.GRADE%TYPE;
LOCV DEPT.LOC%TYPE;
BEGIN
SELECT EMP.* INTO E FROM EMP WHERE EMPNO=EMPNOV;
SELECT LOC INTO LOCV FROM DEPT WHERE DEPT.DEPTNO=E.DEPTNO;
SELECT GRADE INTO GRADEV FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL;
EXP:=MONTHS_BETWEEN(SYSDATE,E.HIREDATE)/12;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||E.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS '||E.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS '||E.SAL);
DBMS_OUTPUT.PUT_LINE('JOB IS '||E.JOB);
DBMS_OUTPUT.PUT_LINE('LOC IS '||LOCV);
DBMS_OUTPUT.PUT_LINE('GRADE IS '||GRADEV);
DBMS_OUTPUT.PUT_LINE('EXP IS '||EXP);
END;
/

141. Write a procedure to accept dname irrespective of case and print all the details of emps?

CREATE OR REPLACE PROCEDURE DETAILS(DNAMEV VARCHAR2)
IS
CURSOR A IS
SELECT EMP.*,DNAME FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND DNAME=DNAMEV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS '||B.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS '||B.SAL);
DBMS_OUTPUT.PUT_LINE('JOB IS '||B.JOB);
DBMS_OUTPUT.PUT_LINE('DNAME IS '||B.DNAME);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS '||B.HIREDATE);
END LOOP;
END;
/

142. Write a procedure to accept a string and print it in reverse case?

CREATE OR REPLACE PROCEDURE S_R_CASE(STR VARCHAR2)
IS
S VARCHAR2(10);
V VARCHAR2(10);
N NUMBER(3);
BEGIN
FOR I IN 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
N:=ASCII(S);
IF N BETWEEN 65 AND 90 THEN
V:=V||CHR(N+32);
ELSE
V:=V||CHR(N-32);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('STRING IN REVERSE CASE IS '||V);
END;
/

PLSQL PROGRAMS TO PRACTICE 9

121. Write a program to accept a year and display the emps belongs to that year?

DECLARE
Y NUMBER(4):=&YEAR;
CURSOR YEAR IS
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=Y;
B YEAR%ROWTYPE;
BEGIN
OPEN YEAR;
LOOP
FETCH YEAR INTO B;
EXIT WHEN YEAR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE YEAR;
END;
/

122.Write a program to accept a mgr and display who are working under that mgr?

DECLARE
MGRV NUMBER(4):=&MGR;
CURSOR AMGR IS
SELECT * FROM EMP WHERE MGR=MGRV;
B AMGR%ROWTYPE;
BEGIN
OPEN AMGR;
LOOP
FETCH AMGR INTO B;
EXIT WHEN AMGR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('EMP NAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('EMP SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE AMGR;
END;
/

123. Write a program to accept the grade and display emps belongs to that grade?

DECLARE
GRADEV SALGRADE.GRADE%TYPE:=&GRADE;
CURSOR A IS
SELECT EMP.*,GRADE FROM EMP,SALGRADE WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=GRADEV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
/

124. Write a program to accept a deptno and display who are working in that dept?

DECLARE
DEPTV EMP.DEPTNO%TYPE:=&DEPTNO;
CURSOR A IS
SELECT * FROM EMP WHERE DEPTNO=DEPTV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
/

125. Write a program to display all the information of emp table?

DECLARE
CURSOR A IS
SELECT * FROM EMP;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('MGR NO IS ' || B.MGR);
DBMS_OUTPUT.PUT_LINE('COMM IS ' || B.COMM);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
/

126. Write a program to accept the location and display empno, name, sal , date of join and also display the total salary, avg salary and no of emps?

DECLARE
LOCV DEPT.LOC%TYPE:='&LOC';
TOT NUMBER(10,2):=0;
ASAL NUMBER(10,2):=0;
NOEMPS NUMBER(5):=0;
CURSOR A IS
SELECT EMP.*,LOC FROM EMP,DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND LOC=LOCV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
NOEMPS:=NOEMPS+1;
TOT:=TOT+B.SAL;
ASAL:=TOT/NOEMPS;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SAL IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
DBMS_OUTPUT.PUT_LINE('TOT IS ' || TOT);
DBMS_OUTPUT.PUT_LINE('NOEMPS IS ' || NOEMPS);
DBMS_OUTPUT.PUT_LINE('ASAL IS ' || ASAL);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
/

127. Write a program to accept a range of salary (that is lower boundary and higher boundary) and print the details of emps along with loc,grade and exp?

DECLARE
LOSALV SALGRADE.LOSAL%TYPE:=&LOSAL;
HISALV SALGRADE.HISAL%TYPE:=&HISAL;
EXP NUMBER(5,2);
CURSOR A IS
SELECT EMP.*,LOC,GRADE FROM EMP,DEPT,SALGRADE WHERE EMP.DEPTNO=DEPT.DEPTNO
AND SAL BETWEEN LOSALV AND HISALV
AND SAL BETWEEN LOSAL AND HISAL;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
EXP:=MONTHS_BETWEEN(SYSDATE,B.HIREDATE)/12;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
DBMS_OUTPUT.PUT_LINE('EXP IS ' || EXP);
DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
/

128. Write a program to print all the details of emps accepting the job?

DECLARE
JOBV EMP.JOB%TYPE:='&JOB';
CURSOR A IS
SELECT * FROM EMP WHERE JOB=JOBV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMP NO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('EMP JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
/

129. Write a program to display the details of emps year wise?

DECLARE
CURSOR YEARS IS
SELECT DISTINCT TO_CHAR(HIREDATE,'YYYY') YEARS1 FROM EMP ORDER BY 1;
YEAR YEARS%ROWTYPE;
CURSOR A IS
SELECT * FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')=YEAR.YEARS1;
B A%ROWTYPE;
BEGIN
DBMS_OUTPUT.ENABLE(10000);
OPEN YEARS;
DBMS_OUTPUT.PUT_LINE('********************');
LOOP
FETCH YEARS INTO YEAR;
EXIT WHEN YEARS%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('YEAR :' || YEAR.YEARS1);
DBMS_OUTPUT.PUT_LINE('**********************');
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SALARY IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('DEPTNO IS ' || B.DEPTNO);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END LOOP;
CLOSE YEARS;
END;
/

130. Write a program to accept empno and print all the details along with loc and grade?

DECLARE
EMPNOV EMP.EMPNO%TYPE:=&EMPNO;
CURSOR A IS
SELECT EMP.*,GRADE,LOC FROM EMP,DEPT,SALGRADE
WHERE EMP.DEPTNO=DEPT.DEPTNO
AND SAL BETWEEN LOSAL AND HISAL AND EMPNO=EMPNOV;
B A%ROWTYPE;
BEGIN
OPEN A;
LOOP
FETCH A INTO B;
EXIT WHEN A%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO IS ' || B.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME IS ' || B.ENAME);
DBMS_OUTPUT.PUT_LINE('SALARY IS ' || B.SAL);
DBMS_OUTPUT.PUT_LINE('JOB IS ' || B.JOB);
DBMS_OUTPUT.PUT_LINE('HIREDATE IS ' || B.HIREDATE);
DBMS_OUTPUT.PUT_LINE('LOC IS ' || B.LOC);
DBMS_OUTPUT.PUT_LINE('GRADE IS ' || B.GRADE);
DBMS_OUTPUT.PUT_LINE('*************************');
END LOOP;
CLOSE A;
END;
/

PLSQL PROGRAMS TO PRACTICE 8

102.WAP to accept 2 nos and find the sum and product of the nos and print the output

DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
S NUMBER;
M NUMBER;
BEGIN
S:=A+B;
M:=A*B;
DBMS_OUTPUT.PUT_LINE('SUM OF '||'A'||' AND '||'B'||' IS '||S);
DBMS_OUTPUT.PUT_LINE('PRODUCT OF '||'A'||' AND '||'B'||' IS '||M);
END;
/

103.WAP to accept 2 nos and find the remainder when the first number is divided by sencond(dont use mod function)

DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
C NUMBER;
M NUMBER;
BEGIN
C:=TRUNC(A/B);
M:=A-C*B;
DBMS_OUTPUT.PUT_LINE('REMAINDER IS '||M);
END;
/

104.WAP to display all the ASCII characters 0-9--48-57,A-Z--65-90,a-z--97-122

BEGIN
FOR I IN 1..255
LOOP
DBMS_OUTPUT.PUT_LINE(I||'-'||CHR(I));
END LOOP;
END;
/

105.Print the following format
ORACLE
ORACL
ORAC
ORA
OR
O

DECLARE
STR VARCHAR2(10):='&STR';
L VARCHAR2(10);
N NUMBER(15);
BEGIN
N:=LENGTH(STR);
WHILE N>=1
LOOP
L:=SUBSTR(STR,1,N);
N:=N-1;
DBMS_OUTPUT.PUT_LINE(L);
END LOOP;
END;
/

106.WAP to display "GOOD MORNING" or "GOOD AFTERNOON" or "GOOD NIGHT" depending upon the current time

DECLARE
HH NUMBER;
BEGIN
HH:=TO_CHAR(SYSDATE,'HH24');
IF HH>6 AND HH<12 THEN
DBMS_OUTPUT.PUT_LINE('GOOD MORNING');
ELSIF HH>=12 AND HH<18 THEN
DBMS_OUTPUT.PUT_LINE('GOOD AFTERNOON');
ELSIF HH>=18 AND HH<25 THEN
DBMS_OUTPUT.PUT_LINE('GOOD NIGHT');
END IF;
END;
/

107.WAP to accept two strings and concat the two strings

DECLARE
STR VARCHAR2(20):='&STR';
STR1 VARCHAR2(20):='&STR1';
V VARCHAR2(40);
BEGIN
V:=STR||''||STR1;
DBMS_OUTPUT.PUT_LINE(V);
END;
/

108.WAP to accept a string and count the no of chars,words in that string

DECLARE
STR VARCHAR2(20):='&STR';
NOC NUMBER(4):=0;
NOW NUMBER(4):=1;
S CHAR;
BEGIN
FOR I IN 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
NOC:=NOC+1;
IF S=' ' THEN
NOW:=NOW+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE NO. OF CHARS '||NOC);
DBMS_OUTPUT.PUT_LINE('THE NO. OF WORDS '||NOW);
END;
/

109.WAP to accept the octal number and print it in decimal format

DECLARE
N VARCHAR2(20):='&N';
A NUMBER;
P NUMBER:=0;
C CHAR;
BEGIN
A:=LENGTH(N);
FOR I IN 1..A
LOOP
C:=SUBSTR(N,I,1);
P:=P+C*POWER(8,A-I);
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE INTEGER OF A GIVEN OCTAL IS '||P);
END;
/

110.WAP to accept the mgr and find how many emps are working under that mgr

DECLARE
MGRV EMP.MGR%TYPE:=&MGRNO;
N NUMBER:=0;
BEGIN
SELECT COUNT(*) INTO N FROM EMP WHERE MGR=MGRV;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EMPLOYEE UNDER THAT MANAGER ARE '||N);
END;
/

111.WAP to accept the empno and update the employee row on the following
If sal < 2600 then sal=sal+10% of sal make the changes in the emp table

DECLARE
EMPNOV EMP.EMPNO%TYPE:=&EMPNO;
SALV NUMBER(7,2):=0;
BEGIN
SELECT SAL INTO SALV FROM EMP WHERE EMPNO=EMPNOV;
IF SALV < 2600 THEN
SALV:=SALV+SALV*(10/100);
END IF;
UPDATE EMP SET SAL=SALV WHERE EMPNO=EMPNOV;
DBMS_OUTPUT.PUT_LINE('EMPNO IS '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('SAL IS '||SALV);
END;
/

112.Write the floyds triangle
1
2 3
4 5 6
7 8 9 10
11 12 13 14 15
16 17 18 19 20 21
...............
79..............91

DECLARE
N NUMBER:=1;
V VARCHAR2(100);
BEGIN
FOR I IN 1..92
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||N;
N:=N+1;
EXIT WHEN N=92;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
EXIT WHEN N=92;
V:=NULL;
END LOOP;
END;
/

113.WAP to accept the real value and print integer value only

DECLARE
N NUMBER(7,3):=&N;
A NUMBER(5);
BEGIN
A:=TRUNC(N);
DBMS_OUTPUT.PUT_LINE('REAL VALUE IS '||A);
END;
/

114.WAP to calculate the sum of n odd factorials

DECLARE
N NUMBER:=&N;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
IF MOD(I,2)!=0 THEN
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+F;
F:=1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM '||S);
END;
/

115.WAP to calculate the sum of n even factorials

DECLARE
N NUMBER:=&N;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
IF MOD(I,2)=0 THEN
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=S+F;
F:=1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM '||S);
END;
/

116.WAP to generate the nos which are prime and odd between 1 and 100

DECLARE
N NUMBER;
CNT NUMBER:=0;
BEGIN
FOR I IN 1..100
LOOP
FOR J IN 1..I
LOOP
IF MOD(I,J)=0 THEN
CNT:=CNT+1;
END IF;
END LOOP;
IF CNT <= 2 THEN
IF MOD(I,2)!=0 THEN
DBMS_OUTPUT.PUT_LINE(I);
END IF;
END IF;
CNT:=0;
END LOOP;
END;
/

117.Write a program to generate following series
12
12 22
12 22 32
12 22 32 42
12 22 32 42 52

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||J||CHR(178);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

118.Find the roots of a quadratic equation

DECLARE
A NUMBER(4):=&A;
B NUMBER(4):=&B;
C NUMBER(4):=&C;
D NUMBER(8,2);
R1 NUMBER(8,2);
R2 NUMBER(8,2);
BEGIN
D:=POWER(B,2)-4*A*C;
IF D = 0 THEN
DBMS_OUTPUT.PUT_LINE('ROOTS ARE EQUAL');
ELSIF D > 0 THEN
R1:=(-B+SQRT(D))/2*A;
R2:=(-B-SQRT(D))/2*A;
DBMS_OUTPUT.PUT_LINE('FIRST ROOT IS '||R1);
DBMS_OUTPUT.PUT_LINE('SECOND ROOT IS '||R2);
ELSE
DBMS_OUTPUT.PUT_LINE('ROOTS ARE IMAGINARY');
END IF;
END;
/

119.WAP to accept the 2 diff nos, assume that first one is smaller and second one is highest value then print the all even nos in between them horizontally

DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
V VARCHAR2(100);
BEGIN
FOR I IN A..B
LOOP
IF MOD(I,2)=0 THEN
V:=V||' '||I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
/

120.WAP to accept two diff nos assume that first one is smaller and second one is highest value then print the all odd nos in between them horizontally

DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
V VARCHAR2(100);
BEGIN
FOR I IN A..B
LOOP
IF MOD(I,2)!=0 THEN
V:=V||' '||I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
/

PLSQL PROGRAMS TO PRACTICE 7

89.Write a program to print the following series
1
1 2
1 2 3
1 2 3 4
1 2 3 4 5

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

90.Write a program to print the following series
1
2 1
3 2 1
4 3 2 1
5 4 3 2 1

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN REVERSE 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

91.Write a program to print the following series
1 2 3 4 5
1 2 3 4
1 2 3
1 2
1

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

92.Write a program to print the following series
1 1 1 1 1
2 2 2 2 2
3 3 3 3 3
4 4 4 4 4
5 5 5 5 5

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..5
LOOP
V:=V||' '||I;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

93.Write a program to print the following series
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5
1 2 3 4 5

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..5
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

94.Write a program to print the following series
5 4 3 2 1
5 4 3 2
5 4 3
5 4
5

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN REVERSE 1..5
LOOP
IF I<=J THEN
V:=V||' '||J;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

95.Write a program to print the following series
5 5 5 5 5
4 4 4 4
3 3 3
2 2
1

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..5
LOOP
FOR J IN 1..5
LOOP
IF I>=J THEN
V:=V||' '||I;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

96.Write a program to print the following series
1
2 2
3 3 3
4 4 4 4
5 5 5 5 5

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||I;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

97.Write a program to print the following series
1
0 1
1 0 1
0 1 0 1
1 0 1 0 1

DECLARE
A NUMBER:=1;
V VARCHAR2(20):=1;
BEGIN
DBMS_OUTPUT.PUT_LINE(V);
FOR I IN 1..4
LOOP
IF SUBSTR(V,1,1)='1' THEN
V:='0'||V;
ELSE
V:='1'||V;
END IF;
DBMS_OUTPUT.PUT_LINE(V);
END LOOP;
END;
/

98.Write a program to print the following series
*
* *
* * *
* * * *
* * * * *

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||'*';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

99.Write a program to print the following series
*
* *
* * *
* * * *
* * * * *
* * * *
* * *
* *
*

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||' '||'*';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
FOR I IN REVERSE 1..5
LOOP
FOR J IN 2..I
LOOP
V:=V||' '||'*';
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

100.Write a program to print the following series
1 2 3 4 5
2 3 4 5
3 4 5
4 5
5

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN 1..5
LOOP
FOR J IN I..5
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

101.Write a program to print the following series
5 4 3 2 1
4 3 2 1
3 2 1
2 1
1

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..5
LOOP
FOR J IN REVERSE 1..I
LOOP
V:=V||' '||J;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

PLSQL PROGRAMS TO PRACTICE 6

79.WAP to accept the empno and display ename,sal,hiredate and calculate ta,da,hra,lic,gross,exp and
print all emp details. ta is 30% of sal,da is 20% of sal,hra is 15% of sal,lic is 5% of sal

DECLARE
EMPNOV NUMBER:=&EMPNOV;
ENAMEV EMP.ENAME%TYPE;
SALV EMP.SAL%TYPE;
HIREDATEV EMP.HIREDATE%TYPE;
EXP NUMBER(7,2);
TA NUMBER(7,2);
DA NUMBER(7,2);
HRA NUMBER(7,2);
LIC NUMBER(7,2);
GROSS NUMBER(7,2);
S NUMBER:=0;
BEGIN
SELECT ENAME,SAL,HIREDATE INTO ENAMEV,SALV,HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
TA:=SALV*30/100;
DA:=SALV*20/100;
HRA:=SALV*15/100;
LIC:=SALV*5/100;
GROSS:=SALV+TA+DA+HRA-LIC;
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP);
DBMS_OUTPUT.PUT_LINE('TA '||TA);
DBMS_OUTPUT.PUT_LINE('DA '||DA);
DBMS_OUTPUT.PUT_LINE('HRA '||HRA);
DBMS_OUTPUT.PUT_LINE('LIC '||LIC);
DBMS_OUTPUT.PUT_LINE('GROSS '||GROSS);
END;
/

80.WAP to accept the item no ,item name,qty,unit price and calculate the bill
If the bill > 500 then give discount 2% of bill amount and display the details

DECLARE
INO NUMBER:=&INO;
INAME VARCHAR2(50):='&INAME';
QTY NUMBER(5):=&QTY;
UP NUMBER(7,2):=&UP;
DIS NUMBER(7,2):=0;
BILL NUMBER(7,2);
NET NUMBER(7,2);
BEGIN
BILL:=QTY*UP;
IF BILL>500 THEN
DIS:=BILL*2/100;
END IF;
NET:=BILL-DIS;
DBMS_OUTPUT.PUT_LINE('ITEM NO '||INO);
DBMS_OUTPUT.PUT_LINE('ITEM NAME '||INAME);
DBMS_OUTPUT.PUT_LINE('QUANTITY '||QTY);
DBMS_OUTPUT.PUT_LINE('UNIT PRICE '||UP);
DBMS_OUTPUT.PUT_LINE('BILL AMT '||BILL);
DBMS_OUTPUT.PUT_LINE('DISCOUNT '||DIS);
DBMS_OUTPUT.PUT_LINE('NET AMT '||NET);
END;
/

81.Write a program to generate sequence of numbers horizontally from 1 to 25

DECLARE
V VARCHAR2(100);
BEGIN
FOR I IN 1..25
LOOP
V:=V||' '||I;
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
END;
/

82.WAP to accept a empno and display empno,name,sal,exp,dname,grade and loc.

DECLARE
EMPNOV NUMBER:=&EMPNO;
ENAMEV EMP.ENAME%TYPE;
HIREDATEV DATE;
SALV EMP.SAL%TYPE;
EXP NUMBER;
DNAMEV DEPT.DNAME%TYPE;
GRADEV SALGRADE.GRADE%TYPE;
BEGIN
SELECT ENAME,SAL,HIREDATE,DNAME,GRADE INTO ENAMEV,SALV,HIREDATEV,DNAMEV,GRADEV FROM EMP,DEPT,SALGRADE
WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO AND SAL BETWEEN LOSAL AND HISAL;
EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP||' YEARS');
DBMS_OUTPUT.PUT_LINE('DNAME '||DNAMEV);
DBMS_OUTPUT.PUT_LINE('GRADE '||GRADEV);
END;
/

83.WAP to accept a empno and display empno,based on experience calculate the bonus and store it into the bonus table
If exp > 5 years then bonus is 1 month salary
If exp between 5 and 9 years then bonus is 20% of annual salary
If exp more than 9 years then bonus is 1 month sal plus 25% of annual salary

DECLARE
EMPNOV NUMBER:=&EMPNO;
ENAMEV EMP.ENAME%TYPE;
HIREDATEV DATE;
SALV EMP.SAL%TYPE;
EXP NUMBER;
DNAMEV DEPT.DNAME%TYPE;
GRADEV SALGRADE.GRADE%TYPE;
BEGIN
SELECT ENAME,SAL,HIREDATE,DNAME,GRADE INTO ENAMEV,SALV,HIREDATEV,DNAMEV,GRADEV FROM EMP,DEPT,SALGRADE
WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO AND SAL BETWEEN LOSAL AND HISAL;
EXP:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('ENAME '||ENAMEV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE '||EXP||' YEARS');
DBMS_OUTPUT.PUT_LINE('DNAME '||DNAMEV);
DBMS_OUTPUT.PUT_LINE('GRADE '||GRADEV);
END;
/

84.WAP to accept the empno, based upon the dname transfer the emps ie, make the changes in the emp table. Transfer the emps from Accounting dept to Research, Research dept to Operation, Opertion dept to Sales
and Sales to Accounting dept

DECLARE
EMPNOV NUMBER:=&EMPNO;
DNAMEV VARCHAR2(20);
DNAMEVV VARCHAR2(20);
BEGIN
SELECT DNAME INTO DNAMEV FROM EMP,DEPT WHERE EMPNO=EMPNOV AND EMP.DEPTNO=DEPT.DEPTNO;
IF DNAMEV='ACCOUNTING' THEN
DNAMEVV:='RESEARCH';
ELSIF DNAMEV='RESEARCH' THEN
DNAMEVV:='SALES';
ELSIF DNAMEV='SALES' THEN
DNAMEVV:='OPERATIONS';
ELSIF DNAMEV='OPERATIONS' THEN
DNAMEVV:='ACCOUNTING';
END IF;
UPDATE EMP SET DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME=DNAMEVV) WHERE EMPNO=EMPNOV;
END;
/

85.WAP to accept the empno and display all the details of emp. If emp doesnot exist display the appreciate message

DECLARE
EMPNOV NUMBER:=&EMPNO;
EMPV EMP%ROWTYPE;
BEGIN
SELECT * INTO EMPV FROM EMP WHERE EMPNO=EMPNOV;
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPV.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME '||EMPV.ENAME);
DBMS_OUTPUT.PUT_LINE('JOB '||EMPV.JOB);
DBMS_OUTPUT.PUT_LINE('SALARY '||EMPV.SAL);
DBMS_OUTPUT.PUT_LINE('HIREDATE '||EMPV.HIREDATE);
DBMS_OUTPUT.PUT_LINE('DEPTNO '||EMPV.DEPTNO);
DBMS_OUTPUT.PUT_LINE('MGRNO '||EMPV.MGR);
DBMS_OUTPUT.PUT_LINE('COMMISSION '||EMPV.COMM);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('EMP NUMBER DOES NOT EXIST');
END;
/

86.WAP to accept the empno and print all the details of emp,dept and salgrade

DECLARE
E EMP%ROWTYPE;
D DEPT%ROWTYPE;
S SALGRADE%ROWTYPE;
BEGIN
SELECT * INTO E FROM EMP WHERE EMPNO=&EMPNO;
SELECT * INTO D FROM DEPT WHERE E.DEPTNO=DEPT.DEPTNO;
SELECT * INTO S FROM SALGRADE WHERE E.SAL BETWEEN LOSAL AND HISAL;
DBMS_OUTPUT.PUT_LINE('EMPNO '||E.EMPNO);
DBMS_OUTPUT.PUT_LINE('DEPTNO '||D.DEPTNO);
DBMS_OUTPUT.PUT_LINE('DNAME '||D.DNAME);
DBMS_OUTPUT.PUT_LINE('LOCATION '||D.LOC);
DBMS_OUTPUT.PUT_LINE('GRADE '||S.GRADE);
DBMS_OUTPUT.PUT_LINE('HISALARY '||S.HISAL);
DBMS_OUTPUT.PUT_LINE('LOWSALARY '||S.LOSAL);
END;
/

87.WAP to accept the mgrno and display the empno,ename,sal,dname and grade of all emps working under that mgr

DECLARE
MGRV NUMBER:=&MGRV;
CURSOR EMPCUR IS
SELECT EMPNO,ENAME,SAL,DEPTNO,GRADE FROM EMP,SALGRADE WHERE MGR=MGRV AND SAL BETWEEN LOSAL AND HISAL;
X EMPCUR%ROWTYPE;
BEGIN
OPEN EMPCUR;
LOOP
FETCH EMPCUR INTO X;
EXIT WHEN EMPCUR%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('EMPNO '||X.EMPNO);
DBMS_OUTPUT.PUT_LINE('ENAME '||X.ENAME);
DBMS_OUTPUT.PUT_LINE('SALARY '||X.SAL);
DBMS_OUTPUT.PUT_LINE('DEPTNO '||X.DEPTNO);
DBMS_OUTPUT.PUT_LINE('GRADE '||X.GRADE);
DBMS_OUTPUT.PUT_LINE('******************');
END LOOP;
CLOSE EMPCUR;
END;
/

88.WAP to accept the empno and display the exp with minimum 3 decimal places

DECLARE
EMPNOV NUMBER:=&EMPNOV;
HIREDATEV DATE;
EXPV NUMBER(10,5);
BEGIN
SELECT HIREDATE INTO HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
EXPV:=ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12,3);
DBMS_OUTPUT.PUT_LINE('EXPERIENCE OF EMP'||EMPNOV||' IS '||EXPV||' YEARS ');
END;
/

PLSQL PROGRAMS TO PRACTICE 5

66.Write a program to display all combination of 1,2,&3

BEGIN
FOR I IN 1..3
LOOP
FOR J IN 1..3
LOOP
FOR K IN 1..3
LOOP
DBMS_OUTPUT.PUT_LINE(I||J||K);
END LOOP;
END LOOP;
END LOOP;
END;
/

67.Write a program to find out the series 12+22+32+42+....++n2

DECLARE
N NUMBER:=&N;
A NUMBER:=1;
B NUMBER:=2;
C NUMBER:=0;
D NUMBER:=0;
S NUMBER:=0;
BEGIN
WHILE A<=N
LOOP
C:=C+A*A;
A:=A+2;
END LOOP;
WHILE B<=N
LOOP
D:=D+B*B;
B:=B+2;
END LOOP;
S:=C-D;
DBMS_OUTPUT.PUT_LINE('RESULT IS '||S);
END;
/

68.Write a program to accep the time in HH & MIN format and find the total senconds

DECLARE
H NUMBER:=&HOUR;
M NUMBER:=&MINUTE;
S NUMBER(10):=0;
BEGIN
S:=(H*60*60)+(M*60);
DBMS_OUTPUT.PUT_LINE(H||' HOURS '||M||' MINUTES '||'IS'||S||' SECONDS');
END;
/

69.WAP to accept the distance between two cities in km and convert into mts ,cm & ft

DECLARE
D NUMBER:=&D;
M NUMBER:=0;
CM NUMBER:=0;
FT NUMBER:=0;
BEGIN
M:=D*1000;
CM:=M*100;
FT:=ROUND(CM/12.3);
DBMS_OUTPUT.PUT_LINE('DISTANCE IN METERS IS '||M);
DBMS_OUTPUT.PUT_LINE('DISTANCE IN CENTIMETERS IS '||CM);
DBMS_OUTPUT.PUT_LINE('DISTANCE IN FOOT IS '||FT);
END;
/

70.Write a program to find the series x+x2/2!+x3/3!+.....+xn/n!

DECLARE
N NUMBER:=&N;
X NUMBER:=&X;
S NUMBER:=0;
F NUMBER:=1;
BEGIN
FOR I IN 1..N
LOOP
FOR J IN 1..I
LOOP
F:=F*J;
END LOOP;
S:=ROUND(s+(POWER(X,I)/F),3);
F:=1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF NUMBER IS '||S);
END;
/

71.Write a program to accept the population of hyderabad each year the population increases
2% after 4y what is the population of hyd

DECLARE
P NUMBER:=&P;
L NUMBER;
BEGIN
FOR J IN 1..4
LOOP
L:=P*2/100;
P:=P+L;
END LOOP;
DBMS_OUTPUT.PUT_LINE('POPULATION OF HYDERABAD AFTER 4 YEARS IS '||TRUNC(P));
END;
/

72.WAP to accept the 3 dates and display the most recently month among 3 dates

DECLARE
D1 DATE:='&D1';
D2 DATE:='&D2';
D3 DATE:='&D3';
M1 NUMBER;
M2 NUMBER;
M3 NUMBER;
BEGIN
M1:=TO_CHAR(D1,'MM');
M2:=TO_CHAR(D2,'MM');
M3:=TO_CHAR(D3,'MM');
IF M1>M2 AND M1>M3 THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D1,'MON')||' IS RECENT MONTH');
ELSIF M2>M1 AND M2>M3 THEN
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D2,'MON')||' IS RECENT MONTH');
ELSE
DBMS_OUTPUT.PUT_LINE(TO_CHAR(D3,'MON')||' IS RECENT MONTH');
END IF;
END;
/

73.Accept a string and print in the following format
O
OR
ORA
ORAC
ORACL
ORACLE

DECLARE
V VARCHAR2(20):='&V';
C VARCHAR(20);
BEGIN
FOR I IN 1..LENGTH(V)
LOOP
C:=SUBSTR(V,1,I);
DBMS_OUTPUT.PUT_LINE(C);
END LOOP;
END;
/

74.Write a program to accept the annual income of the emp and find the income tax
i) If the annsal > 60000 then tax is 10% of income
ii) If the annsal > 100000 then tax is Rs 800+16% of income
iii) If the annsal > 140000 then tax is Rs 2500+25% of income

DECLARE
AI NUMBER(10,2):=&ANNUALINCOME;
TAX NUMBER(10,3):=0;
BEGIN
IF AI BETWEEN 36000 AND 50000 THEN
TAX:=AI*10/100;
ELSIF AI BETWEEN 50000 AND 100000 THEN
TAX:=800+AI*16/100;
ELSIF AI > 100000 THEN
TAX:=2500+AI*25/100;
END IF;
DBMS_OUTPUT.PUT_LINE('ANNUAL INCOME '||AI);
DBMS_OUTPUT.PUT_LINE('TAX '||TAX);
END;
/

75.WAP to accept a year as i/p & find how many even number present in that year

DECLARE
Y NUMBER:=&YEAR;
A VARCHAR2(20);
CNT NUMBER(5):=0;
BEGIN
FOR I IN 1..LENGTH(Y)
LOOP
A:=SUBSTR(Y,I,1);
IF MOD(A,2)=0 THEN
CNT:=CNT+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EVEN DIGIT IS '||CNT);
END;
/

76.WAP to accept a year as i/p & find how many odd number present in that year

DECLARE
Y NUMBER:=&YEAR;
A VARCHAR2(20);
CNT NUMBER(5):=0;
BEGIN
FOR I IN 1..LENGTH(Y)
LOOP
A:=SUBSTR(Y,I,1);
IF MOD(A,2)!=0 THEN
CNT:=CNT+1;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('NUMBER OF EVEN DIGIT IS '||CNT);
END;
/

77.WAP to accept a number and calculate the sum of numbers in even places

DECLARE
N NUMBER:=&NUMBER;
A VARCHAR2(10);
S NUMBER:=0;
BEGIN
FOR I IN 1..LENGTH(N)
LOOP
A:=SUBSTR(N,I,1);
IF MOD(I,2)=0 THEN
S:=S+A;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF EVEN PLACE IS '||S);
END;
/

78.WAP to accept the emp details and calculate the bonus based on the following conditions
i) If sal < 500 then bonus is 10% sal
ii) If sal > 3500 then bonus is 12% sal
iii) If sal > 1000 then bonus is 13.5% sal

DECLARE
EMPNOV NUMBER:=&EMPNOV;
SALV NUMBER;
B NUMBER(7,2);
BEGIN
SELECT SAL INTO SALV FROM EMP WHERE EMPNO=EMPNOV;
IF SALV BETWEEN 500 AND 3500 THEN
B:=SALV*10/100;
ELSIF SALV BETWEEN 3500 AND 10000 THEN
B:=SALV*12/100;
ELSIF SALV>10000 THEN
B:=SALV*13.5/100;
END IF;
DBMS_OUTPUT.PUT_LINE('EMPNO '||EMPNOV);
DBMS_OUTPUT.PUT_LINE('SALARY '||SALV);
DBMS_OUTPUT.PUT_LINE('BONUS '||B);
END;
/

PLSQL PROGRAMS TO PRACTICE 4

52.WAP to accept the basic salary and find out the ta,da,hra,lic and gs
i)ta 20% of basic, da 10% of basic, hra 30% of basic, lic 5% of basic

DECLARE
BS NUMBER(6,2):=&BS;
TA NUMBER(6,2);
DA NUMBER(6,2);
HRA NUMBER(6,2);
GS NUMBER(6,2);
LIC NUMBER(6,2);
NS NUMBER(8,2);
BEGIN
TA:=BS*(20/100);
HRA:=BS*(30/100);
DA:=BS*(10/100);
LIC:=BS*(5/100);
GS:=TA+HRA+DA;
NS:=GS-LIC;
DBMS_OUTPUT.PUT_LINE('EMPLOYEE BS IS '||BS);
DBMS_OUTPUT.PUT_LINE('GROSS SALARY IS '||GS);
DBMS_OUTPUT.PUT_LINE('NET SALARY IS '||NS);
END;
/

53.WAP to accept the length and breadth of a rectangle and find out the perimeter

DECLARE
L NUMBER(4,2):=&L;
B NUMBER(4,2):=&B;
A NUMBER(4,2);
BEGIN
A:=2*(L+B);
DBMS_OUTPUT.PUT_LINE('THE PERIMETER OF RECTANGLE IS '||A);
END;
/

54.WAP to accept the cost price and selling price of an item and find
the loss or profit

DECLARE
CP NUMBER(25,2):=&CP;
SP NUMBER(25,2):=&SP;
AMT NUMBER(7,2);
BEGIN
IF CP < SP THEN
AMT:=SP-CP;
DBMS_OUTPUT.PUT_LINE('PROFIT IS '||AMT);
ELSE
AMT:=CP-SP;
DBMS_OUTPUT.PUT_LINE('LOSS IS '||AMT);
END IF;
END;
/

55.Writ a program to generate the following series
53 53 53 53 53
43 43 43 43
33 33 33
23 23
13

DECLARE
V VARCHAR2(20);
BEGIN
FOR I IN REVERSE 1..5
LOOP
FOR J IN 1..I
LOOP
V:=V||I||CHR(179);
END LOOP;
DBMS_OUTPUT.PUT_LINE(V);
V:=NULL;
END LOOP;
END;
/

56.WAP to accept a no in binary format and print it in decimal format

DECLARE
N VARCHAR2(20):=&N;
PRO NUMBER(10,4):=0;
L VARCHAR2(10);
BEGIN
FOR I IN 1..LENGTH(N)
LOOP
L:=SUBSTR(N,I,1);
PRO:=PRO+L*POWER(2,LENGTH(N)-I);
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE DECIMAL NUMBER IS '||PRO);
END;
/

57.WAP to accept two nos and input and find one no is raised to another one (without using any function)

DECLARE
A NUMBER:=&A;
B NUMBER:=&B;
R NUMBER:=1;
BEGIN
FOR I IN 1..B
LOOP
R:=R*A;
END LOOP;
DBMS_OUTPUT.PUT_LINE('A RAISED POWER B IS '||R);
END;
/

58.WAP to accept a sentence and count the no of chars in that sentence

DECLARE
STR VARCHAR2(100):='&STR';
NO NUMBER(5):=0;
I NUMBER;
BEGIN
I:=INSTR(STR,'.');
DBMS_OUTPUT.PUT_LINE('NO OF CHAR IS '||I);
END;
/

59.WAP to accept two strings and display the large one among those

DECLARE
STR1 VARCHAR2(100):='&STR1';
STR2 VARCHAR2(100):='&STR2';
BEGIN
IF LENGTH(STR1) > LENGTH(STR2) THEN
DBMS_OUTPUT.PUT_LINE(STR1 ||' IS GREATER');
ELSIF LENGTH(STR1) < LENGTH(STR2) THEN
DBMS_OUTPUT.PUT_LINE(STR2 ||' IS GREATER');
ELSE
DBMS_OUTPUT.PUT_LINE('BOTH STRINGS ARE EQUAL');
END IF;
END;
/

60.WAP to display all the nos whose sum of digits is 9 from 1 to 9999

DECLARE
N NUMBER;
M NUMBER;
S NUMBER:=0;
BEGIN
FOR I IN 1..999
LOOP
N:=I;
WHILE N>0
LOOP
M:=MOD(N,10);
S:=S+M;
N:=TRUNC(N/10);
END LOOP;
IF S=9 THEN
DBMS_OUTPUT.PUT_LINE(I||' ');
END IF;
S:=0;
END LOOP;
END;
/

61.WAP to accept a no and find the sum in a single digit

DECLARE
N NUMBER(4):=&N;
S NUMBER(10):=0;
BEGIN
WHILE LENGTH(N)>1
LOOP
FOR I IN 1..LENGTH(N)
LOOP
S:=S+SUBSTR(N,I,1);
END LOOP;
N:=S;
S:=0;
END LOOP;
DBMS_OUTPUT.PUT_LINE('THE SUM IN SINGLE DIGIT IS '||N);
END;
/

62.Ente the no of days and find out the no of years and no of days and months

DECLARE
D NUMBER:=&D;
Y NUMBER;
M NUMBER;
BEGIN
Y:=TRUNC(D/365);
M:=TRUNC(MOD(D,365)/30);
D:=MOD(MOD(D,365),30);
DBMS_OUTPUT.PUT_LINE(Y||' YEARS '||M||' MONTHS '||D||' DAYS');
END;
/

63.WAP to accept the date and print all the weekdays along with the given date

DECLARE
D DATE:='&D';
V VARCHAR2(20);
BEGIN
FOR I IN 1..7
LOOP
V:=TO_CHAR(D,'DAY')||D;
DBMS_OUTPUT.PUT_LINE(V);
D:=D+1;
END LOOP;
END;
/

64.WAP while purchasing certain items,discount of each is as follows
i) If qty purchased > 1000 discount is 20%
ii) If the qty and price per item are i/p then calculate the expenditure

DECLARE
QTY NUMBER(5):=&QTY;
UP NUMBER(6,2):=&UP;
DIS NUMBER(6,2):=0;
TAMT NUMBER(10,2);
BILL NUMBER(10,2);
BEGIN
BILL:=QTY*UP;
IF BILL > 1000 THEN
DIS:=BILL*20/1000;
END IF;
TAMT:=BILL-DIS;
DBMS_OUTPUT.PUT_LINE('THE TOTAL AMOUNT IS '||TAMT);
END;
/

65.Write a program to accept a string and count the no of individual chars

DECLARE
V VARCHAR2(100):='&V';
V1 VARCHAR2(100);
LB NUMBER;
LA NUMBER;
DIFF NUMBER;
C CHAR;
N NUMBER(5):=0;
BEGIN
V1:=V;
WHILE LENGTH(V1)>0
LOOP
C:=SUBSTR(V1,1,1);
LB:=LENGTH(V1);
V1:=REPLACE(V1,C);
LA:=NVL(LENGTH(V1),0);
DIFF:=LB-LA;
IF ASCII(C)=32 THEN
DBMS_OUTPUT.PUT_LINE('SPACE'||' EXISTS '||DIFF||' TIMES');
ELSE
DBMS_OUTPUT.PUT_LINE(C||' EXISTS '||DIFF||' TIMES');
END IF;
N:=N+DIFF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('TOTAL LENGTH OF THE GIVEN STRING '||V||'='||N);
END;
/