Examples On PL/SQL Blocks Database Management System

Examples On PL/SQL Blocks Database Management System

EXAMPLES ON PL/SQL BLOCKS Database Management System

EXAMPLES ON PL/SQL BLOCKS

Example 1.                              

EXAMPLES ON PL/SQL BLOCKS Database Management System : /*This block finds the next highest employee in the chain of Command above employee number 2345 who has a salary higher than 10000. */

DECLARE
Salary emp.sal%type;
Manager emp.mgr%type;
Fname emp.ename%type;
Start empno CONSTANT NUMBER(4):=2345;
BEGIN
Selectsal, mgr, ename into salary, manager, Fname from emp Where empno=startemp;

While salary<10000 LOOP

Select sal, mgr, ename into salary, manager, Fname
From emp
Where empno=manager;

END LOOP; Insert into temp values(null, Salary, Fname); COMMIT;
END;
/

Example 2.    

Example 2.
/* It uses a cursor to select the five highest paid employees from the emp Table*/
Declare
Cursor c1 is
Select ename, empno, sal from emp
Order by sal Desc;
Cename char(10);

Cempno number(4);
Csal number(7,2);

Begin
Open c1;
LOOP

Fetch c1 into cename, cempno, Csal;
Exit when (c1%ROWCOUNTY5) or (c1%NOTFOUND);
Insert into temp values (csal, cempno, cename);
Commit;
END LOOP;
CLOSE c1;
END;
/

Example 3.

/* This block calculate the total number of all the wages(Sal+Comm) paid to employees in Department 40. It also counts the number of these people having salaries above 2500 and also how many of them get commission more than their salaries*/

Declare
Cursor emp_cur (dnum number) is
Select sal, comm. From emp
Where deptno=dnum;

Total wages number(6,2):=0;
High paid number(4):=0;
High comm number(4):=0;

Begin

For emp record in emp cur(40)
LOOP
emp record.comm:=nvl(emp record.comm,0);

Total wages:=total wages+emp record.sal+emp record.comm;
If emp_record.sall>2500 Then
High paid:=High paid-1;
End if;

If emp_record.comm> emp_record.sal Then
High comm:=High comm+1;
End if;
END LOOP,
Insert into temp; values (High paid, High comm, TO_CHAR(total_wages) T_Wage);
Commit;
End;
/