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;

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.

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;

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;

how many tupples can insert in 1 second in sql

1 Answers  

---

What is the purpose of cursors in pl/sql?

0 Answers  

---

What is Overloading of procedures ?

2 Answers  

---

What is synchronized subquery?

0 Answers  

---

What can sql server reporting services do?

0 Answers  

---

What is auto increment?

0 Answers  

---

First round ------------------- - Procedure - Packages - Views - Virtual tables - Can we use dcl with in function? - Joins and few scenarios - Triggers and its type - Pragma, type and its functionality - How to create db link in oracle - Materialized view - How to find duplicate values from table? - Cursor and its functionality - Write a script to display friday and its date from a entire year. - Exception Handling Second round ------------------------ Gave a scenario like. Need to write a function to perform. When user try to change a password. It must not be last five password and a given password can be combination of characters, symbols, upper and lower case.

0 Answers   Prodapt,

---

what are dynamic queries in t-sql? : Transact sql

0 Answers  

---

How are functions and procedures called in PL/SQL?

0 Answers  

---

Is it possible to create the following trigger: before or after update trigger for each row?

0 Answers  

---

Is natural join and inner join same?

0 Answers  

---

What are the uses of sysdate and user keywords?

0 Answers  

---