PL/SQL-ANSWERS
1. DECLARE
X NUMBER: =30;
Y NUMBER: =40;
BEGIN
DBMS_OUTPUT.PUT_LINE ('SUM='||(X+Y));
END;
OUTPUT: SUM=70
2. DECLARE
X NUMBER: =&X;
Y NUMBER: =&Y;
BEGIN
DBMS_OUTPUT.PUT_LINE ('SUM='||(X+Y));
END;
OUTPUT:
Enter value for x: 30
Enter value for y : 40
SUM=70
3. DECLARE
S NUMBER;
BEGIN
SELECT SUM(SAL) INTO S FROM EMP;
DBMS_OUTPUT.PUT_LINE('SUM OF THE SALARY='||S);
END;
OUTPUT:
SUM OF THE SALARY=34259.25
4. BEGIN
FOR I IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(TO_CHAR(I));
END LOOP;
END;
OUTPUT:
1
2
.
.
10
5. DECLARE
I NUMBER: =10;
BEGIN
WHILE I>=1 LOOP
DBMS_OUTPUT.PUT_LINE (I);
I:=I-1;
END LOOP;
END;
OUTPUT:
10
9
.
.
1
6. DECLARE
I NUMBER:=10;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE (I);
EXIT WHEN I=1;
I:=I-1;
END LOOP;
END;
OUTPUT:
10
9
.
.
1
7. DECLARE
X NUMBER:=&X;
K NUMBER;
S NUMBER:=0;
BEGIN
WHILE X<>0 LOOP
K:=MOD(X,10);
X:=TRUNC(X/10);
S:=S+K;
END LOOP;
DBMS_OUTPUT.PUT_LINE('SUM OF DIGITS=' ||S);
END;
OUTPUT:
Enter value for x: 345
SUM OF DIGITS=12
8. DECLARE
N NUMBER:=&N;
F NUMBER:=1;
BEGIN
FOR I IN 1..N LOOP
F:=F*I;
END LOOP;
DBMS_OUTPUT.PUT_LINE('FACTORIAL='||F);
END;
OUTPUT:
Enter value for n: 4
FACTORIAL=24
9. DECLARE
X NUMBER:=&X;
K NUMBER;
S NUMBER:=0;
BEGIN
WHILE X<>0 LOOP
K:=MOD(X,10);
X:=TRUNC(X/10);
S:=S*10+K;
END LOOP;
DBMS_OUTPUT.PUT_LINE('REVERSE NUMBER=' ||S);
END;
OUTPUT:
Enter value for x: 345
REVERSE NUMBER=543
10. DECLARE
S NUMBER;
BEGIN
SELECT AVG(SAL) INTO S FROM EMP;
DBMS_OUTPUT.PUT_LINE('AVERAGE SALARY='||S);
END;
OUTPUT:
AVERAGE SALARY=2447.089285714285714285714285714285714286
11. DECLARE
S NUMBER;
BEGIN
SELECT SAL INTO S FROM EMP WHERE ENAME='SMITH';
DBMS_OUTPUT.PUT_LINE('SALARY OF SMITH='||S);
END;
OUTPUT:
SALARY OF SMITH=968
12. DECLARE
S VARCHAR2(20):='&S';
REV VARCHAR2(20):='';
N NUMBER;
BEGIN
N:=LENGTH(S);
FOR I IN REVERSE 1..N LOOP
REV:=REV||SUBSTR(S,I,1);
END LOOP;
IF REV=S THEN
DBMS_OUTPUT.PUT_LINE('PALINDROME');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT PALINDROME');
END IF;
END;
OUTPUT:
Enter value for s: LIRIL
PALINDROME
13. DECLARE
N EMP.ENAME%TYPE;
CURSOR C1 IS SELECT ENAME FROM EMP;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO N;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(N);
END LOOP;
CLOSE C1;
END;
OUTPUT:
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
14. DECLARE
X NUMBER:=&X;
Y NUMBER:=X;
K NUMBER;
S NUMBER:=0;
BEGIN
WHILE X<>0 LOOP
K:=MOD(X,10);
X:=TRUNC(X/10);
S:=S+K*K*K;
END LOOP;
IF Y=S THEN
DBMS_OUTPUT.PUT_LINE('ARMSTRONG');
ELSE
DBMS_OUTPUT.PUT_LINE('NOT ARMSTRONG');
END IF;
END;
OUTPUT:
Enter value for x: 153
ARMSTRONG
15. DECLARE
I NUMBER:=0;
CURSOR C1 IS SELECT ENAME FROM EMP;
BEGIN
FOR R IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(R.ENAME);
I:=I+1;
EXIT WHEN I=3;
END LOOP;
END;
OUTPUT:
SMITH
ALLEN
WARD
16. DECLARE
CURSOR C1 IS SELECT * FROM EMP ORDER BY SAL DESC;
BEGIN
FOR R IN C1 LOOP
IF C1%ROWCOUNT= 3 THEN
DBMS_OUTPUT.PUT_LINE(R.ENAME||' '||R.JOB||' '||R.SAL);
EXIT;
END IF;
END LOOP;
END;
OUTPUT:
FORD ANALYST 3000
17. DECLARE
CURSOR C1 IS SELECT * FROM EMP;
BEGIN
FOR R IN C1 LOOP
IF C1%ROWCOUNT= 4 THEN
DBMS_OUTPUT.PUT_LINE(R.EMPNO||' '||R.ENAME||' '||R.JOB||' '||R.SAL);
EXIT;
END IF;
END LOOP;
END;
OUTPUT:
7566 JONES MANAGER 2975
18. DECLARE
D EMP.DEPTNO%TYPE;
CURSOR C1 IS SELECT ENAME FROM EMP WHERE DEPTNO=&D;
BEGIN
FOR R IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(R.ENAME);
END LOOP;
END;
OUTPUT:
Enter value for d: 10
CLARK
KING
MILLER
19. DECLARE
CURSOR C1 IS SELECT * FROM EMP ORDER BY JOB;
BEGIN
FOR R IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(R.EMPNO||' '||R.ENAME||' '||R.JOB||' '||R.SAL);
END LOOP;
END;
OUTPUT:
7788 SCOTT ANALYST 3000
7902 FORD ANALYST 3630
7369 SMITH CLERK 968
7876 ADAMS CLERK 1100
7934 MILLER CLERK 1573
7900 JAMES CLERK 1149.5
7566 JONES MANAGER 3599.75
7782 CLARK MANAGER 2964.5
7698 BLAKE MANAGER 3448.5
7839 KING PRESIDENT 6050
7499 ALLEN SALESMAN 1936
7654 MARTIN SALESMAN 1512.5
7844 TURNER SALESMAN 1815
7521 WARD SALESMAN 1512.5
20. DECLARE
CURSOR C1 IS SELECT * FROM EMP ORDER BY DEPTNO;
BEGIN
FOR R IN C1 LOOP
DBMS_OUTPUT.PUT_LINE(R.EMPNO||' '||R.ENAME||' '||R.JOB||' '||R.SAL||' '||R.DEPTNO);
END LOOP;
END;
OUTPUT:
7782 CLARK MANAGER 2964.5 10
7839 KING PRESIDENT 6050 10
7934 MILLER CLERK 1573 10
7369 SMITH CLERK 968 20
7876 ADAMS CLERK 1100 20
7902 FORD ANALYST 3630 20
7788 SCOTT ANALYST 3000 20
7566 JONES MANAGER 3599.75 20
7499 ALLEN SALESMAN 1936 30
7698 BLAKE MANAGER 3448.5 30
654 MARTIN SALESMAN 1512.5 30
7900 JAMES CLERK 1149.5 30
7844 TURNER SALESMAN 1815 30
7521 WARD SALESMAN 1512.5 30
21. BEGIN
UPDATE EMP SET SAL= SAL+500 WHERE JOB='MANAGER';
UPDATE EMP SET SAL= SAL+400 WHERE JOB='CLERK';
UPDATE EMP SET SAL= SAL+300 WHERE JOB NOT IN
('MANAGER','CLERK');
END;
22. DECLARE
N EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO N FROM EMP WHERE EMPNO=&ENO;
EXCEPTION WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('SORRY NO RECORD');
END;
OUTPUT:
Enter value for eno: 222
SORRY NO RECORD
23. DECLARE
N EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO N FROM EMP WHERE JOB='MANAGER';
EXCEPTION WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('TOO MANY RECORDS');
END;
OUTPUT:
TOO MANY RECORDS
24. CREATE OR REPLACE FUNCTION FACT( X NUMBER) RETURN NUMBER AS
K NUMBER:=1;
BEGIN
FOR I IN 1..X LOOP
K:=K*I;
END LOOP;
RETURN K;
END;
EXECUTION:
Usage at SQL:
SQL> EXEC DBMS_OUTPUT.PUT_LINE(FACT(4));
OUTPUT: 24
Usage in PL/SQL:
DECLARE
N NUMBER:=&N;
BEGIN
DBMS_OUTPUT.PUT_LINE('FACTORIAL=' ||FACT(N));
END;
OUTPUT:
Enter value for n: 4
FACTORIAL=24
25. CREATE OR REPLACE PROCEDURE FACT( X NUMBER) AS
K NUMBER:=1;
BEGIN
FOR I IN 1..X LOOP
K:=K*I;
END LOOP;
DBMS_OUTPUT.PUT_LINE('FACTORIAL =' || K);
END;
Usage at SQL:
SQL> EXEC FACT(4);
OUTPUT: FACTORIAL=24
26. CREATE OR REPLACE TRIGGER EMPINS AFTER INSERT ON EMP FOR EACH
ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('TRIGGER MESSAGE: RECORD INSERTED');
END;
Testing the trigger:
SQL> insert into emp (empno,ename) values (1234,'Ramu');
Trigger Message: Record inserted
27. CREATE OR REPLACE TRIGGER EMPINS1 BEFORE INSERT ON EMP FOR EACH
ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('TRIGGER MESSAGE: RECORD WILL BE INSERTED NOW');
END;
Testing the trigger:
SQL> insert into emp (empno,ename) values (1111,'Kiran');
Trigger Message: Record will be inserted now
28. CREATE OR REPLACE TRIGGER EMPUPDATE BEFORE UPDATE OR INSERT OR DELETE ON EMP FOR EACH ROW
WHEN(TO_CHAR(SYSDATE,'DY')='SUN')
BEGIN
RAISE_APPLICATION_ERROR(-20012,'SORRY! NO TRANSACTIONS ON SUNDAY');
END;
OUTPUT:
Trigger created.
Note: If you try insertion or deletion or update on emp on Sunday it gives the following error.
SORRY! NO TRANSACTIONS ON SUNDAY
