using cursors salesman get bonus as 10% of their salary ,
managers get 20% , analalist get 30%. generae a report
showing the employee name, salary , bonus.
Answers were Sorted based on User's Feedback
Answer / neelaveni
declare
/*cursor c_bonus
is
select * from empsal; */
begin
for i in (select * from empsal)
loop
if i.job='salesman' then
i.bonus:=i.sal*10/100;
elsif i.job='mgr' then
i.bonus:=i.sal*20/100;
elsif i.job='analyst' then
i.bonus:=i.sal*30/100;
end if;
dbms_output.put_line('The ename is : '||i.ename);
dbms_output.put_line('The sal is : '||i.sal);
dbms_output.put_line('The job is : '||i.job);
dbms_output.put_line('The bonus is : '||i.bonus);
end loop;
end;
Is This Answer Correct ? | 7 Yes | 0 No |
Answer / rajasekaran
SQL> declare
2
3 emp_id employees.employee_id%type;
4 fname employees.first_name%type;
5 sal employees.salary%type;
6 jobid employees.job_id%type;
7 bonus number;
8 v_emp_refcur SYS_REFCURSOR;
9 begin
10 open v_emp_refcur for select
employee_id,first_name,job_id,salary from employees where
job_id in ('SA_M
AN','AC_MGR','AD_ASST');
11 dbms_output.put_line ('Employee id fname sal
jobid bonus');
12 dbms_output.put_line ('----------- ----- ---
----- -----');
13 loop
14 fetch v_emp_refcur into emp_id,fname,jobid,sal ;
15 EXIT WHEN v_emp_refcur%NOTFOUND;
16 if jobid='SA_MAN' then
17 bonus := (sal*10)/100;
18 elsif jobid='AC_MGR' then
19 bonus := (sal*20)/100;
20 elsif jobid='AD_ASST' then
21 bonus := (sal*30)/100;
22 end if;
23 dbms_output.put_line (emp_id||' '||fname||'
'||sal||' '||jobid||' '||bonus);
24 end loop;
25 close v_emp_refcur;
26 end;
27 /
Employee id fname sal jobid bonus
----------- ----- --- ----- -----
205 Shelley 12000 AC_MGR 2400
200 Jennifer4400 AD_ASST 1320
145 John 14000 SA_MAN 1400
146 Karen 13500 SA_MAN 1350
147 Alberto 12000 SA_MAN 1200
148 Gerald 11000 SA_MAN 1100
149 Eleni 10500 SA_MAN 1050
212 aa SA_MAN
PL/SQL procedure successfully completed.
Is This Answer Correct ? | 4 Yes | 0 No |
Answer / ajit
CREATE OR REPLACE package SCOTT.Insr_sal
IS
TYPE Emp_rec IS RECORD ( V_Empno Emp.Empno%type,
V_Ename EMP.ENAME%type,
V_sal EMP.SAL%TYPE,
V_comm EMP.COMM%TYPE,
V_hiredate EMP.HIREDATE%TYPE,
V_job EMP.JOB%TYPE,
V_Total_sal EMP.Sal%TYPE
);
TYPE Emp_ref_cur IS REF CURSOR
RETURN Emp_rec;
FUNCTION Emp_Sal_hike ( P_job varchar2 )
RETURN Emp_ref_cur;
END;
CREATE OR REPLACE package BODY SCOTT.Insr_sal
IS
FUNCTION Emp_Sal_hike ( P_job varchar2 )
RETURN Emp_ref_cur
IS
C_emp_ref Insr_sal.Emp_ref_cur;
V_raise_sal NUMBER;
BEGIN
CASE
WHEN P_job = 'CLERK' THEN
V_raise_sal := 1.4;
WHEN P_job = 'SALESMAN' THEN
V_raise_sal := 1.6;
WHEN P_job = 'MANAGER' THEN
V_raise_sal := 1.8;
WHEN P_job = 'ANALYST' THEN
V_raise_sal := 1.3;
WHEN P_job = 'PRESIDENT' THEN
V_raise_sal := 2.5;
ELSE
NULL;
END CASE;
OPEN C_emp_ref FOR
SELECT Empno,Ename, Sal, Comm, Hiredate, Job, V_raise_sal * ( Sal + NVL ( Comm, 0 ) ) V_Total_sal
FROM Emp
WHERE job = P_job;
RETURN C_emp_ref;
CLOSE C_emp_ref;
END;
END Insr_sal;
Is This Answer Correct ? | 1 Yes | 0 No |
Answer / narenkumar reddy
declare
cursor c1 is select * from emp;
begin
for i in c1
loop
if i.job='salesman' then
bonus:=sal+sal*10/100;
elsif i.job='manager' then
bonus:=sal+sal*20/100;
elsif i.job='analyst' then
bonus:=sal+sal*30/100;
end if;
dbms_output_output.put_line(i.ename||i.sal||i.sal||bonus);
end;
Is This Answer Correct ? | 0 Yes | 3 No |
Which one is better subquery or joins?
Describe different types of general function used in sql?
Mention what is the function that is used to transfer a pl/sql table log to a database table?
What is the difference between microsoft access and sql?
What is the difference between join and union.
27 Answers 3i Infotech, ABC, Oracle, Polaris, TCS, XT Global,
What is optimistic concurrency control? : Transact sql
How to Declare Fixed Length String Value In PL SQL
What is clustered, non-clustured and unique index. How many indexes can be created on a table ?
Why is sql important?
what is the difference between trigger and storedprocedures
how to include numeric values in sql statements? : Sql dba
Delete the emps whose salaries are lowest sals of their own dept.