Coding is Funny: Assignment 3(pl block)

Assignment 3(pl block)

<------As.1------>
DECLARE
a number(10);
   b number(10);
c number(10);
BEGIN
   a:=&a;
   b:=&b;
c:=a+b;
   dbms_output.put_line(c);
END;
/

<------As.2-------->

DECLARE

i number(10);

n number(10);

BEGIN

n:=0;

i:=1;

while i<100

loop

n:=n+i;

i:=i+1;

end loop;

dbms_output.put_line('sum is:'||n);

END;

<------As.3------->

DECLARE

n number;

s number default 0;

ev number;

BEGIN

ev:=&value;

n:=1;

for n in 1..ev

loop

if mod(n,2)=1

then

s:=s+n;

end if;

END LOOP;

dbms_output.put_line(s);

END;

<------As.4------->

DECLARE

n number(3):=1;

s number(4):=0;

BEGIN

WHILE n<=100 LOOP

dbms_output.put_line(n);

s:=s+n;

n:=n+2;

END LOOP;

dbms_output.put_line('Sum of odd number'||s);

END;

<------As.5------>

DECLARE

sal number(10);

pf number(10);

da number(10);

hr number(10);

ns number(10);

BEGIN

sal:=&salary;

da:=sal*(15/100);

hr:=sal*(41/100);

if(sal<3000)

then

pf:=sal*(5/100);

elsif(sal<=5000)

then

pf:=sal*(7/100);

elsif(sal<=8000)

then

pf:=sal*(8/100);

else

pf:=sal*(10/100);

end if;

ns:=sal+da+hr-pf;

dbms_output.put_line('net salary'||ns);

END;

<-----As.6------->

DECLARE

a number(3);

b number(3);

c number(3);

BEGIN

a:=&a;

b:=&b;

c:=&c;

if a>b

and a>c

then

dbms_output.put_line('greatest number is'||a);

elsif b>a

and b>c

then

dbms_output.put_line('greatest number is'||b);

else

dbms_output.put_line('greatest number is'||c);

end if;

END;

<------As.7-------->

DECLARE

fac number:=1;

n number(3);

BEGIN

n:=&n;

while n>0

LOOP

fac:=n*fac;

n:=n-1;

end LOOP;

dbms_output.put_line(fac);

END;

<------As.8--------->

DECLARE

first number:=0;

second number:=1;

temp number;

n number(5);

i number;

BEGIN

n:=&n;

dbms_output.put_line('series');

dbms_output.put_line(first);

dbms_output.put_line(second);

for i in 2..n

LOOP

temp:=first+second;

first:=second;

second:=temp;

dbms_output.put_line(temp);

end LOOP;

END;

<--------As.9------->

DECLARE

n number;

i number;

temp number;

BEGIN

n:=&n;

i:=2;

temp:=1;

for i in 2..n/2

LOOP

if mod(n,i)=0

then

temp:=0;

exit;

end if;

end LOOP;

if temp=1

then

dbms_output.put_line('prime');

else

dbms_output.put_line('not prime');

end if;

END;

/

<---------As.10-------->
DECLARE
R NUMBER;
A NUMBER;
BEGIN
FOR NO IN 1..10
LOOP
R:=NO;
A:=3.14*R*R;
DBMS_OUTPUT.PUT_LINE('AREA OF CIRCLE:'||A);
END LOOP;
END;
/

<--------As.11--------->
DECLARE
N NUMBER;
I NUMBER;
REV NUMBER:=0;
R NUMBER;
BEGIN
N:=&N;
WHILE N>0
LOOP
R:=MOD(N,10);
REV:=(REV*10)+R;
N:=TRUNC(N/10);
END LOOP;
DBMS_OUTPUT.PUT_LINE('REVERSE IS '||REV);
END;

<---------As.12---------->

DECLARE
M1 NUMBER;
M2 NUMBER;
M3 NUMBER;
TOTAL NUMBER;
PER NUMBER;
BEGIN
M1:=&M1;
M2:=&M2;
M3:=&M3;
TOTAL:=M1+M2+M3;
PER:=TOTAL/3;
IF (PER>=60) THEN
DBMS_OUTPUT.PUT_LINE('1ST CLASS');
ELSIF (PER>=50) THEN
DBMS_OUTPUT.PUT_LINE('2ND CLASS');
ELSIF (PER>=40) THEN
DBMS_OUTPUT.PUT_LINE('PASS CLASS');
ELSE
DBMS_OUTPUT.PUT_LINE('FAIL');
END IF;
END;
/

<---------As.13---------->
CREATE TABLE MYTABLE
(NOS_COL NUMBER(5),
CHAR_COL VARCHAR2(20));
DECLARE
I NUMBER;
BEGIN
FOR I IN 1..10
LOOP
INSERT INTO MYTABLE VALUES(I,'ABC'||I);
END LOOP;
END;
/

<---------As.14-------------->
CREATE TABLE EMP
(NO NUMBER(5),
NAME VARCHAR(20),
SALARY NUMBER(10));

INSERT INTO EMP VALUES
(101,'ANKIT',10000);
INSERT INTO EMP VALUES
(102,'PARAS',1000);
INSERT INTO EMP VALUES
(103,'PRITESH',4000);
INSERT INTO EMP VALUES
(104,'NIKITA',5000);
INSERT INTO EMP VALUES
(105,'ROXY',4500);

DECLARE
TMP NUMBER;
NUM NUMBER;
SAL NUMBER;
CURSOR C1 IS SELECT NO,SALARY FROM EMP;
BEGIN
TMP:=&TMP;
OPEN C1;
IF C1%ISOPEN THEN
LOOP
FETCH C1 INTO NUM,SAL;
EXIT WHEN C1%NOTFOUND;
IF (TMP=NUM) AND (SAL>5000) THEN
UPDATE EMP SET SALARY=SALARY+1000 WHERE NO=TMP;
END IF;
END LOOP;
END IF;
CLOSE C1;
END;
/

<---------As.15-------->
CREATE TABLE AREAS
(RADIUS NUMBER,
AREA NUMBER);

DECLARE
R NUMBER;
A NUMBER;
ANS NUMBER;
BEGIN
FOR NO IN 1..7
LOOP
R:=NO;
SELECT POWER(R,2) INTO ANS FROM DUAL;
A:=3.14*ANS;
INSERT INTO AREAS VALUES(R,A);
END LOOP;
END;
/

<--------As.16---------->
CREATE TABLE EMP
(E_NO DECIMAL(5),
E_NAME VARCHAR(20),
E_SAL DECIMAL(5));

INSERT INTO EMP VALUES
(101,'ANKIT',10000);
INSERT INTO EMP VALUES
(102,'VISHV',1000);
INSERT INTO EMP VALUES
(103,'PARAS',4000);
INSERT INTO EMP VALUES
(104,'PRITESH',5000);
INSERT INTO EMP VALUES
(105,'SINU',4500);

DECLARE
NO DECIMAL(5);
CURSOR C1 IS SELECT * FROM EMP;
REC EMP%ROWTYPE;
BEGIN
NO:=&NO;
OPEN C1;
IF C1%ISOPEN THEN
LOOP
FETCH C1 IN 2 TO REC;
EXIT WHEN C1%NOTFOUND;
IF C1%FOUND THEN
IF REC.E_NO=NO THEN
DBMS_OUTPUT.PUT_LINE('ID:'||REC.E_NO||' NAME:'||REC.E_NAME||' SALARY:'||REC.E_SAL);
END IF;
END IF;
END LOOP;
END IF;
CLOSE C1;
END;
/

<------As.17---->

DROP TABLE EMPLOYEE;

CREATE TABLE EMPLOYEE

(

ENO NUMBER(5),

ENAME VARCHAR(10),

ESAL NUMBER(10)

);

INSERT INTO EMPLOYEE VALUES(151,'Ankit',22000);

INSERT INTO EMPLOYEE VALUES(152,'Paras',55000);

INSERT INTO EMPLOYEE VALUES(153,'Pritesh',10000);

INSERT INTO EMPLOYEE VALUES(154,'Vishal',25000);

INSERT INTO EMPLOYEE VALUES(155,’Sinu',50000);

INSERT INTO EMPLOYEE VALUES(156,'Shubham’,999);

SET SERVEROUTPUT ON;

DECLARE

NO NUMBER;

ENO NUMBER;

ENAME VARCHAR(10);

ESAL NUMBER ;

WHR NUMBER;

CURSOR CEMP IS SELECT * FROM EMPLOYEE;

BEGIN

OPEN CEMP;

NO:=&NO;

LOOP

FETCH CEMP INTO ENO,ENAME,ESAL;

EXIT WHEN CEMP %NOTFOUND;

IF ENO=NO THEN

SELECT ESAL INTO WHR FROM EMPLOYEE WHERE ENO=NO;

IF WHR>1000 THEN

DBMS_OUTPUT.PUT_LINE(' '||'ENO'||' '||'ENAME'||' '||'ESAL');

DBMS_OUTPUT.PUT_LINE (' '||ENO||' '||ENAME||' '||ESAL);

ELSE

DBMS_OUTPUT.PUT_LINE('SALARY NOT GREATER THAN 1000');

DBMS_OUTPUT.PUT_LINE('EMPLOYEE NOT FOUND');

END IF;

END LOOP;

END;

OUTPUT:

ENTER VALUE FOR NO: 155

OLD 10: NO:=&NO;

NEW 10: NO:=155;

ENO ENAME ESAL

155 Sinu 50000

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

<------As.18------->

DROP TABLE EMPLOYEE;

CREATE TABLE EMPLOYEE

(

ENO NUMBER(5),

ENAME VARCHAR(10),

ESAL NUMBER(10)

);

INSERT INTO EMPLOYEE VALUES(151,'Ankit',22000);

INSERT INTO EMPLOYEE VALUES(152,'Paras',55000);

INSERT INTO EMPLOYEE VALUES(153,'Pritesh',10000);

INSERT INTO EMPLOYEE VALUES(154,'Vishal',25000);

INSERT INTO EMPLOYEE VALUES(155,’Sinu',50000);

INSERT INTO EMPLOYEE VALUES(156,'Shubham’,999);

SET SERVEROUTPUT ON;

DECLARE

NO NUMBER;

ENO NUMBER;

ENAME VARCHAR(10);

ESAL NUMBER ;

WHR NUMBER;

CURSOR CEMP IS SELECT * FROM EMPLOYEE;

BEGIN

OPEN CEMP;

NO:=&NO;

LOOP

FETCH CEMP INTO ENO,ENAME,ESAL;

EXIT WHEN CEMP %NOTFOUND;

IF ENO=NO THEN

COMMIT;

UPDATE EMPLOYEE SET ESAL=ESAL-5000 WHERE NO=ENO;

SELECT ESAL INTO WHR FROM EMPLOYEE WHERE NO=ENO;

IF WHR<2000 THEN

DBMS_OUTPUT.PUT_LINE('SALARY LESS THAN 2000');

ELSE

DBMS_OUTPUT.PUT_LINE ('EMPLOYEE NOT FOUND');

END IF;

END LOOP;

END;

OUTPUT:

ENTER VALUE FOR NO: 156

OLD 10: NO:=&NO;

NEW 10: NO:=156;

SALARY LESS THAN 2000

PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.

<------As.19------->

DROP TABLE EMP;

CREATE TABLE EMP(ENO VARCHAR2(5),NAME VARCHAR2(10),

SALARY NUMBER);

INSERT INTO EMP VALUES('E11','Ankit',10000);

INSERT INTO EMP VALUES('E12','Meet',1000);

INSERT INTO EMP VALUES('E13','Parth',3000);

INSERT INTO EMP VALUES('E14','Brijesh’,6000);

SET SERVEROUTPUT ON;

DECLARE

SAL EMP.SALARY%TYPE;

ENAME EMP.NAME%TYPE;

ID EMP.ENO%TYPE;

BEGIN

ID:='&ID';

SELECT SALARY INTO SAL FROM EMP WHERE ENO=ID;

IF SAL<5000 THEN

DBMS_OUTPUT.PUT_LINE('YOU CAN NOT DECLARE');

ELSE

DELETE FROM EMP WHERE ENO=ID;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE('EMP IS NOT FOUND');

END;

OUTPUT:

ENTER VALUE FOR ID: E15

OLD 6: ID:='&ID';

NEW 6: ID:='E15';

EMP IS NOT FOUND

<-----As.20------->

CREATE TABLE EMP1

(

E_NO NUMBER PRIMARY KEY,

E_NAME VARCHAR2(10),

DATE_OF_JOIN DATE,

E_SAL NUMBER

);

INSERT INTO EMP1 VALUES(1,'Ankit','23-JUN-2000',10000);

INSERT INTO EMP1 VALUES(2,'Nikita','28-JAN-1999',15000);

INSERT INTO EMP1 VALUES(3,'Meet','10-DEC-1998',20000);

INSERT INTO EMP1 VALUES(4,'Raj','09-SEP-2000',25000);

INSERT INTO EMP1 VALUES(5,'Meshwa','23-MAR-1995',1000);

SET SERVEROUTPUT ON;

DECLARE

ENO NUMBER(10);

NAME VARCHAR2(10);

JDATE DATE;

ESALARY NUMBER(10);

LESS_THAN_SALARY EXCEPTION;

BEGIN

ENO:=&ENO;

NAME:='&NAME';

JDATE:='&JDATE';

ESALARY:=&ESALARY;

IF ESALARY>5000 THEN

INSERT INTO EMP1 VALUES(ENO,NAME,JDATE,ESALARY);

ELSE

RAISE LESS_THAN_SALARY;

END IF;

EXCEPTION

WHEN LESS_THAN_SALARY THEN

DBMS_OUTPUT.PUT_LINE('SALARY IS LESS THEN 5000 SO INSERTION NOT ALLOWED IN EMP TABLE');

END;

OUTPUT:

INSERT INTO EMPS1 VELUES

(6, 'PARTH', '2-APR-2005',4000);

OUTPUT

SALARY LESS THAN 5000

ORA-20001: INSERTION NOT ALLOWED

0 Comments

Assignment 3(pl block)

Blog Archive