Its very very urgent Please....Could you please help.plz help in solving queries

[jenipriya]

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_Applicati on_Error.

DECLARE
CURSOR C_EMP1 IS
SELECT EmpId from Employee where (SYSDATE-Employee.DateOf Join)/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*Sa l;
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(‘Processe d 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%ROWTYP E;
Cursor C1 (club Varchar2:=&name ) is
Select EmpId from Club
Where Club.ClubName=c lub 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.Emp Name);
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%TY PE)
Is
Return Number
As
Begin
Select sal=Employee.Sa l-(Club.Fee+Emplo yee.Sal/30*Leave.NoOfLe ave) into Sal
From Employee,Club,L eave
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_em pid
From club
Group by ClubName;
v_count:=(sql%r owcount);
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.NoOf Employees = Department.NoOf Employees + 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.emp no,:NEW.sal);
UPDATE INTO Department SET Department.NoOf Employees = Department.NoOf Employees + 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(dat e)
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%TY PE;
v_finalsal Employee.Sal%TY PE;
v_noofleave Leave%DateOfLea ve%TYPE;
Begin
Select v_sal=v_sal-(v_clubfee+v_sa l/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.

[debasisdas]

In the article section of oracle of ths forum u will find hundreds of sample examples on all of your requested topic.
try to follow those.
if u still have doubts then please do post back.

[jenipriya]

In the article section of oracle of ths forum u will find hundreds of sample examples on all of your requested topic.
try to follow those.
if u still have doubts then please do post back.

I have gone through the articles and tried answers for these queries .. But stil i have doubt and hence posted with the ans i have tried.... Could you please check and tell me where i went wrong.This is my first assignment and haven't undergone any training. thats y i seek help badly from this forum.....

[debasisdas]

please check your PMs on the top of the page of the site.

[debasisdas]

Ans #1
===========
[CODE=oracle]DECLARE
CURSOR C_EMP IS SELECT EmpNO from Emp where (SYSDATE-HIREDATE)/730 > 1;
Begin
For rec1 in C_EMP
Loop
Exit when C_EMP%NOTFOUND;
UPDATE Emp SET Sal=Sal * 1.15 where empno=rec1.empn o;
End Loop;
End;[/CODE]
Generally raise_appicatio n_error is not used within a cursor.

[debasisdas]

Ans #2
===========
[CODE=oracle]DECLARE
CURSOR EMPREC IS SELECT * FROM EMP FOR UPDATE OF SAL NOWAIT;
MYREC EMPREC%ROWTYPE;
NUM NUMBER(4);
BEGIN
OPEN EMPREC;
LOOP
FETCH EMPREC INTO MYREC;
EXIT WHEN EMPREC%NOTFOUND ;
NUM:=EMPREC%ROW COUNT;
UPDATE EMP SET SAL=SAL * 1.10 WHERE CURRENT OF EMPREC;
END LOOP;
CLOSE EMPREC;
DBMS_OUTPUT.PUT _LINE(NUM ||' RECORDS UPDATED');
END;[/CODE]

[debasisdas]

Ans #4
==========
[CODE=oracle]Create or Replace Function Emp_Fn(eno IN Emp.Empno%TYPE, sal OUT Emp.Sal%TYPE)
Return Number
As
Begin
Select Emp.Sal-(Club.Fee+Emp.S al/30 * Leave.NoOfLeave ) into Sal
From Emp,Club,Leave Where emp.empno=club. empno and emp.empno=leave .empno and EmpId=eno;
return sal;
End;[/CODE]


from your code it clear that you have no idea of PL/SQL programming
please go through the basics first.