Dozens of practical pl/sql (4)

Phase IV

Q. Write a process to accept the three department numbers entered by the user and display the department name of two department numbers.

A.

CREATE OR REPLACE PROCEDURE deptname (No1 Dept.deptno%type,no2 Dept.deptno%type,no3 Dept.deptno%type) as

Vflag number;

Vdeptno1 Dept.deptno%type;

Vdeptno2 Dept.deptno%type;

Vdname1 Dept.dname%type;

Vdname2 Dept.dname%type;



BEGIN

Vflag:=to_number (To_char (sysdate, ' SS '));

IF (Vflag>=1 and vflag<=10) OR (vflag>=50 and vflag<60) THEN

SELECT deptno,dname into vdeptno1,vdname1 from dept WHERE Deptno=no1;

SELECT deptno,dname into vdeptno2,vdname2 from dept WHERE Deptno=no2;

elsif (vflag>=11 and Vflag<=20) OR (vflag>=40 and VFLAG&LT;50) THEN

SELECT deptno,dname into vdeptno1,vdname1 from dept WHERE Deptno=no1;

SELECT deptno,dname into vdeptno2,vdname2 from dept WHERE Deptno=no3;

ELSE

SELECT deptno,dname into vdeptno1,vdname1 from dept WHERE Deptno=no2;

SELECT deptno,dname into vdeptno2,vdname2 from dept WHERE Deptno=no3;

End IF;

Dbms_output. Put_Line (' Department No.: ' | |  Vdeptno1 | | ' '||' Department Name: ' | | VDNAME1);

Dbms_output. Put_Line (' Department No.: ' | |  Vdeptno2 | | ' '||' Department Name: ' | | VDNAME2);



End;

/

EXECUTE Deptname (10,20,30);



Q. Write a procedure to display the employee department name and location for the specified employee name.

A.

CREATE OR REPLACE PROCEDURE deptmesg (pename emp.ename%type,pdname out Dept.dname%type,ploc out Dept.loc%type) as

BEGIN

SELECT Dname,loc into Pdname,ploc

From Emp,dept

WHERE Emp.deptno=dept.deptno and Emp.ename=pename;

End;

/

VARIABLE vdname VARCHAR2 (14)

VARIABLE Vloc VARCHAR2 (13)



EXECUTE deptmesg (' SMITH ',: vdname,:vloc);

PRINT Vdname Vloc;



Q. Prepare a process for a pay increase of 10% for a particular employee, after which, if the employee has been employed for more than 60 months, the amount of the additional salary is 3000.

A.

CREATE OR REPLACE PROCEDURE raise_sal (No in number) as

Vhiredate DATE;

Vsal Emp.sal%type;

BEGIN

SELECT hiredate,sal into vhiredate,vsal from emp WHERE empno=no;

IF Months_between (sysdate,vhiredate) >60 THEN

VSAL:=NVL (vsal,0) *1.1+3000;

ELSE

VSAL:=NVL (vsal,0) *1.1;

End IF;

UPDATE emp SET sal=vsal WHERE empno=no;

End;

/

VARIABLE No number

BEGIN

: no:=7369;

End;

/

EXECUTE raise_sal (: NO)

SELECT empno,ename,sal,comm,hiredate from emp WHERE empno=:no;



Q. Write a function to check whether the salary of the specified employee is within the valid range. The salary ranges for different positions are:

Designation Raise

Clerk 1500-2500

Salesman 2501-3500

Analyst 3501-4500

Others 4501 and above.

If the salary is in this range, the message "Salary is OK" will be displayed, otherwise the salary will be updated to the maximum water value in the range.

A.

CREATE OR REPLACE FUNCTION sal_level (no emp.empno%type) return CHAR as

Vjob Emp.job%type;

Vsal Emp.sal%type;

VMESG CHAR (50);

BEGIN

SELECT job,sal into vjob,vsal from emp WHERE empno=no;

IF vjob= ' clerk ' THEN

IF vsal>=1500 and vsal<=2500 THEN

Vmesg:= ' Salary is OK ';

ELSE

vsal:=1500;

Vmesg:= ' Have updated your salary to ' | | To_char (vsal);

End IF;

elsif vjob= ' salesman ' THEN

IF vsal>=2501 and vsal<=3500 THEN

Vmesg:= ' Salary is OK ';

ELSE

vsal:=2501;

Vmesg:= ' Have updated your salary to ' | | To_char (vsal);

End IF;

elsif vjob= ' ANALYST ' THEN

IF vsal>=3501 and vsal<=4500 THEN

Vmesg:= ' Salary is OK ';

ELSE

vsal:=3501;

Vmesg:= ' Have updated your salary to ' | | To_char (vsal);

End IF;

ELSE

IF vsal>=4501 THEN

Vmesg:= ' Salary is OK ';

ELSE

vsal:=4501;

Vmesg:= ' Have updated your salary to ' | | To_char (vsal);

End IF;

End IF;

UPDATE emp SET sal=vsal WHERE empno=no;

return VMESG;

End;

/

DECLARE

VMESG CHAR (50);

Vempno Emp.empno%type;

BEGIN

vempno:=&empno;

Vmesg:=sal_level (Vempno);

Dbms_output. Put_Line (VMESG);

End;

/

--select empno,ename,sal,comm,hiredate from emp WHERE empno=:no;



Q. Write a function to display the number of days that the employee worked in this organization.

A.

CREATE OR REPLACE FUNCTION hire_day (no emp.empno%type) return number as

Vhiredate Emp.hiredate%type;

Vday number;



BEGIN

SELECT hiredate into vhiredate from emp WHERE empno=no;

Vday:=ceil (sysdate-vhiredate);

return vday;

End;

/

DECLARE

Vday number;

Vempno Emp.empno%type;

BEGIN

vempno:=&empno;

Vday:=hire_day (Vempno);

Dbms_output. Put_Line (vday);

End;

/



--select empno,ename,sal,comm,hiredate from emp WHERE empno=:no;