PL/SQL sample queries Part - VI

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;
/
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;
/
143. Write a function to accept the empno and return exp with minimum 3 decimal?
CREATE OR REPLACE FUNCTION E_DETAILS(EMPNOV NUMBER) RETURN NUMBER
IS
HIREDATEV EMP.HIREDATE%TYPE;
EXP NUMBER(6,3);
BEGIN
SELECT HIREDATE INTO HIREDATEV FROM EMP WHERE EMPNO=EMPNOV;
EXP:=MONTHS_BETWEEN(SYSDATE,HIREDATEV)/12;
RETURN EXP;
END;
/
144. Write a function to accept a number and print the factorial of that number?
CREATE OR REPLACE FUNCTION FAC(NUM NUMBER) RETURN NUMBER
IS
FACT NUMBER(4):=1;
BEGIN
FOR I IN REVERSE 1..NUM
LOOP
FACT:=FACT*I;
END LOOP;
RETURN FACT;
END;
/
145. Write a function to accept a grade and return the number of emps belongs to that grade?
CREATE OR REPLACE FUNCTION EMPGRADE(GRADEV NUMBER) RETURN VARCHAR2
IS
N NUMBER(4);
BEGIN
SELECT COUNT(*) INTO N FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL AND GRADE=GRADEV;
RETURN 'NO OF EMPS ARE'||N;
END;
/
146. Write a program to accept the mgr number and return no of emp working at that mgr?
CREATE OR REPLACE FUNCTION N_EMPS(MGRV NUMBER) RETURN VARCHAR2
IS
N NUMBER(4);
BEGIN
SELECT COUNT(*) INTO N FROM EMP WHERE MGR=MGRV;
RETURN 'THE NO OF EMPS ARE WORKING UNDER THIS MGR IS '||N;
END;
/
147. Write a function to accept a character string and print it in reverse case?
CREATE OR REPLACE FUNCTION REVERSE(STR VARCHAR2) RETURN VARCHAR2
IS
STR1 VARCHAR2(20);
S VARCHAR2(20);
N NUMBER(4);
BEGIN
FOR I IN 1..LENGTH(STR)
LOOP
S:=SUBSTR(STR,I,1);
N:=ASCII(S);
IF N BETWEEN 65 AND 90 THEN
STR1:=STR1||CHR(N+32);
ELSE
STR1:=STR1||CHR(N-32);
END IF;
END LOOP;
RETURN 'THE REVERSE CASE IS '||STR1;
END;
/
148. Write a function to accept a string and check whether it is palindrome or not?
CREATE OR REPLACE FUNCTION STRPAL1(STR VARCHAR2) RETURN 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
RETURN 'IT IS PALINDROME '||STR1;
ELSE
RETURN 'IT IS NOT PALINDROME '||STR1;
END IF;
END;
/
149. Write a function to accept the grade and return max, tot, avg salary and number of emps belongs to that grade as script without using any group functions?
CREATE OR REPLACE FUNCTION EMP_DETAILS_SCRIPT (GRADEV SALGRADE.GRADE%TYPE) RETURN VARCHAR2
IS
V VARCHAR2(30000);
CURSOR EMP_CUR IS
SELECT EMP.*,GRADE,DNAME FROM DEPT,EMP,SALGRADE
WHERE GRADE=GRADEV AND EMP.DEPTNO=DEPT.DEPTNO AND
SAL BETWEEN LOSAL AND HISAL;
EMP_CUR_V EMP_CUR%ROWTYPE;
MAXSAL EMP.SAL%TYPE:=0;
MINSAL EMP.SAL%TYPE;
AVGSAL NUMBER(6,2);
SUMSAL NUMBER(10,2):=0;
CNT NUMBER:=0;
FLAG CHAR:=0;
EX EXCEPTION;
BEGIN
OPEN EMP_CUR;
LOOP
FETCH EMP_CUR INTO EMP_CUR_V;
EXIT WHEN EMP_CUR%NOTFOUND;
IF MAXSAL < EMP_CUR_V.SAL THEN
MAXSAL:=EMP_CUR_V.SAL;
END IF;
IF FLAG=0 THEN
MINSAL:=EMP_CUR_V.SAL;
FLAG:=1;
ELSIF FLAG=1 AND MINSAL > EMP_CUR_V.SAL THEN
MINSAL:=EMP_CUR_V.SAL;
END IF;
SUMSAL:=SUMSAL+EMP_CUR_V.SAL;
CNT:=CNT+1;
ENDLOOP;
IF CNT=0 THEN
RAISE EX;
END IF;
AVGSAL:=SUMSAL/CNT;
V:='THE MAXIMUM SALARY OF GRADE' ||GRADEV||' IS'||MAXSAL||' MINIMUM SALARY IS'||MINSAL||
'AVERAGE SALARY IS'||AVGSAL||' TOTAL EMPS WORKING FOR THIS GRADE ARE'||CNT;
CLOSE EMP_CUR;
RETURN V;
EXCEPTION
WHEN EX THEN
RETURN 'THERE IS NO EMPLOYEE WORKING FOR THIS GRADE, CHECK AND RE-ENTER THE GRADE....';
END;
/