14
Hi all... its very urgent.. please........i m a beginner in oracle.... Anyone please help me wit dese codes i hv tried... and correct the errors...
The table structures i hav
Employee (EmpID, EmpName,DeptID,DateOfJoin, Sal, Addr)
Finance (EmpID, Sal)
Club (Clubname, EmpID, Fee, DateOfJoin)
Leave (EmpID, Date)
Department (DeptID, DeptName, NoOfEmployees)
-----------------------------------------------------------------------------------------------------
The queries i hav tried... please help me with the code to sovle errors.. I m a fresher .... please help me....
1) write a PL/SQL block to increase the salary of a given employee by 15 % if the years of experience of that employee is greater than 2 years else generate an error using Raise_Application_Error.
DECLARE
CURSOR C_EMP1 IS
SELECT EmpId from Employee where (SYSDATE-Employee.DateOfJoin)/730>1;
Emp_Rec C_EMP1%ROWTYPE;
Begin
Open C_EMP1;
For rec1 in C_EMP1
Loop
Fetch C_EMP1 into Emp_Rec;
Exit when C_EMP1%NOTFOUND;
UPDATE Employee
SET Sal=Sal+0.15*Sal;
End Loop;
Close C_EMP1;
End; // Error – Cursor already open
2) write a PL/SQL block to update the salary of all employees by 10 %. Make use of For Update and Where Current of Clauses in cursor.
DECLARE
CURSOR C_EMP IS
SELECT EmpId,Sal from Employee
For Update NOWAIT;
Emp_Rec C_EMP%ROWTYPE;
Begin
Open C_EMP;
Loop
Fetch C_EMP into Emp_Rec;
Exit when C_EMP%NOTFOUND;
UPDATE Employee
SET Sal=Sal+0.1*Sal
Where current of C_EMP;
DBMS_OUTPUT.PUT_LINE(‘Processed Rows :’|| sql%rowcount);
End Loop;
Close C_EMP;
End; // Successful – But not displaying the processed rows
3) write a PL/SQL block to display the employees joined in a given club. Make use of cursor with parameters for fetching data.
Declare
Emp_Rec Employee%ROWTYPE;
Cursor C1 (club Varchar2:=&name) is
Select EmpId from Club
Where Club.ClubName=club group by clubName;
Cursor C2(empid Employee.EmpId%TYPE) is
Select EmpName from Employee
Where Employee.EmpId=empid;
Begin
For mrec in C1
Loop
For mrec1 in C2(mrec.empid)
Loop
DBMS_OUTPUT.PUT_LINE(mrec1.EmpName);
End Loop;
End Loop;
End; /// identifier 'GYMNASIUM' must be declared and wrong number or types of arguments in call to 'C1'
4) Write a stored function that receives an employee number and returns the total salary deductions for that employee.
Create or Replace Function Emp_Fn(eno IN Employee.EmpId%TYPE:=0 ,sal OUT Employee.Sal%TYPE)
Is
Return Number
As
Begin
Select sal=Employee.Sal-(Club.Fee+Employee.Sal/30*Leave.NoOfLeave) into Sal
From Employee,Club,Leave
Where EmpId=eno;
End;
/
5) write a PL/SQL block to find the no of employees in each club.
Declare
V_count number(10);
V_clubname varchar2(15);
V_empid number(10);
Begin
Select ClubName,EmpId into v_clubname,v_empid
From club
Group by ClubName;
v_count:=(sql%rowcount);
dbms_output.put_line(v_count);
End;
6) whenever a new employee is added to the employee table one row should be added in the Finance table for that employee and update Department table such that Department.NoOfEmployees = Department.NoOfEmployees + 1.
Whenever the salary field is updated the difference should be updated in the finance table.
CREATE OR REPLACE TRIGGER mytrig
After INSERT ON Employee
FOR each ROW
BEGIN
IF inserting THEN
INSERT INTO Finance VALUES(:NEW.empno,:NEW.sal);
UPDATE INTO Department SET Department.NoOfEmployees = Department.NoOfEmployees + 1;
END IF;
END;
7)how to display the salary deduction details for the employees in a given department for a given month. The salary deduction is sum ( Club fees for that employee) + ( Employee.Sal / 30 * no of leaves taken for that month)
Declare
v_s_date date := &startdate;
v_e_date date := &enddate;
Begin
select EmpId,count(date)
from Leave
where date between v_s_date and v_e_date
end
Declare
V_eno Employee.EmpId%TYPE:=&no;
v_clubfee Club.Fee%TYPE;
v_sal Employee.Sal%TYPE;
v_finalsal Employee.Sal%TYPE;
v_noofleave Leave%DateOfLeave%TYPE;
Begin
Select v_sal=v_sal-(v_clubfee+v_sal/30*v_noofleave) into v_finalsal
From employee where EmpId:=&v_eno;
End;
how to combine both the queries ? will i get the ans if i write query lik dis?
8) write a PL/SQL block to display the employee details with department name and salary deductions. Make use of cursor for loops to fetch rows.
How to calculate the salary deductions and to display?
9)write a stored function to find the employee ID who is having highest no of memberships.
Create or Replace Function F1(count IN Number, empid Employee.EmpId)
Is
Return number
As
Begin
Select count(ClubName),EmpId
From Club
Groupby EmpId;
End;
10) How to write a stored procedure that deletes all the rows from the finance table and insert new values into it. Make use of the stored function to find the deduction in salary. Finance.Sal = Employee.Sal – deductions.
