- create function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
create or replace function rc_increment
(phiredate rahul_emp.hiredate%type , psal rahul_emp.sal%type)
return number
is
service_yrs number;
revised_sal rahul_emp.sal%type ;
begin
service_yrs := trunc(months_between(sysdate,phiredate)/12);
if service_yrs > 25 then
revised_sal := psal + (psal*.2);
elsif service_yrs > 23 then
revised_sal := psal + (psal*.15);
else
revised_sal := psal + (psal*.02);
end if;
return revised_sal;
End;
- calling function from block
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
declare
cursor cemp is select empno,ename,job,sal,hiredate from rahul_emp
where deptno = 10
for update of sal;
new_sal rahul_emp.sal%type;
begin
For mrec in cemp
loop
new_sal := rc_increment(mrec.hiredate ,mrec.sal);
dbms_output.put_line('here in function new salary '||new_sal);
update rahul_emp set sal = new_sal
where current of cemp;
end loop;
End;
- function call from sql statement
1
select empno,hiredate,sal,rc_increment(hiredate,sal) from rahul_emp;
1
2
3
update rahul_emp
set sal = rc_increment(hiredate,sal)
where empno = 7900;
- question sol function return number
1
2
3
4
5
6
7
8
9
10
11
12
13
create or replace function rc_fuc
(deptNo rahul_emp.deptNo%type )
return number
is
dcount number;
begin
select count(*) into dcount from rahul_emp where deptno=deptNo;
if sql%notfound then
return 0;
end if;
return 1;
End;
- calling block for function
1
2
3
4
5
6
7
8
declare
begin
dbms_output.put_line('answer = '||rc_fuc(&inp) );
End;
- question sol function return varchar
1
2
3
4
5
6
7
8
9
10
11
12
13
create or replace function rc_fuc
(deptNumber rahul_emp.deptNo%type )
return varchar
is
dcount number;
begin
select count(*) into dcount from rahul_emp where deptno=deptNumber;
if dcount>0 then
return 'Department has employees';
end if;
return 'Department does not has employees';
End;
- calling block for function
1
2
3
4
5
6
7
declare
begin
dbms_output.put_line('answer = '||rc_fuc(&inp) );
End;
- sql statement with above created function
1
select rc_fuc(0) from rahul_dept;