By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,618 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

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

P: 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.
Jul 7 '07 #1
Share this Question
Share on Google+
6 Replies


debasisdas
Expert 5K+
P: 8,127
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.
Jul 9 '07 #2

P: 14
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.....
Jul 9 '07 #3

debasisdas
Expert 5K+
P: 8,127
please check your PMs on the top of the page of the site.
Jul 9 '07 #4

debasisdas
Expert 5K+
P: 8,127
Ans #1
===========
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. CURSOR C_EMP IS SELECT EmpNO from Emp where (SYSDATE-HIREDATE)/730 > 1;
  3. Begin
  4. For rec1 in C_EMP
  5. Loop
  6. Exit when C_EMP%NOTFOUND;
  7. UPDATE Emp SET Sal=Sal * 1.15 where empno=rec1.empno;
  8. End Loop;
  9. End;
Generally raise_appication_error is not used within a cursor.
Jul 14 '07 #5

debasisdas
Expert 5K+
P: 8,127
Ans #2
===========
Expand|Select|Wrap|Line Numbers
  1. DECLARE
  2. CURSOR EMPREC IS SELECT * FROM EMP FOR UPDATE OF SAL NOWAIT;
  3. MYREC EMPREC%ROWTYPE;
  4. NUM NUMBER(4);
  5. BEGIN
  6. OPEN EMPREC;
  7. LOOP
  8. FETCH EMPREC INTO MYREC;
  9. EXIT WHEN EMPREC%NOTFOUND;
  10. NUM:=EMPREC%ROWCOUNT;
  11. UPDATE EMP SET SAL=SAL * 1.10 WHERE CURRENT OF EMPREC;
  12. END LOOP;
  13. CLOSE EMPREC;
  14. DBMS_OUTPUT.PUT_LINE(NUM ||'   RECORDS UPDATED');
  15. END;
Jul 14 '07 #6

debasisdas
Expert 5K+
P: 8,127
Ans #4
==========
Expand|Select|Wrap|Line Numbers
  1. Create or Replace Function Emp_Fn(eno IN Emp.Empno%TYPE,sal OUT Emp.Sal%TYPE)
  2. Return Number
  3. As
  4. Begin
  5. Select Emp.Sal-(Club.Fee+Emp.Sal/30 * Leave.NoOfLeave) into Sal
  6. From Emp,Club,Leave Where emp.empno=club.empno and emp.empno=leave.empno and EmpId=eno;
  7. return sal;
  8. End;

from your code it clear that you have no idea of PL/SQL programming
please go through the basics first.
Jul 14 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.