Interested to Buy Any Domain ? << Click Here >> for more details...
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.
- 4 Answers
- 12338 Views
- Fidelity, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
how can I make a script that can be bi-language (supports english, german)? : Sql dba
What are primary key and foreign key and how they work?
What do you mean by stored procedures?
Write a sql query to convert all character to uppercase after hypen.
How you can copy a file to file content and file to pl/sql table in advance pl/sql?
what is the use of anchoring object? what r the difference between anchoring object & enclosing object? can any one tell me all the details?
What are two statement types in sql?
Can we insert in view in sql?
how to analyze tables with 'mysqlcheck'? : Sql dba
What is a schema sql?
how do you tune the slow running queries in oracle db , explain the methodology
What are hotfixes and patches?
What is meant by truncate in sql?
what is the difference between delete and truncate statement in sql? : Sql dba
What is row_number () in sql?
