Functions:
=======
A function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value.
Functions are divided into two types
- System defined functions
- User defined functions
User defined functions: A function which is defined by user manually those functions can be called as user defined functions.
Example: add();
--> User defined functions are further divided into four types, depending on receiving and sending the values.
- A function which receives and returns the value
- A function which receives but doesn’t returns the value
- A function doesn’t receive but returns the value
- A function doesn’t receive and doesn’t return the value
1) Declaration of the function
2) Body of the function
2) Body of the function
A declaration of the function starts with keyword called create and ends with return statement, whereas body of the function starts with a keyword called is and ends with end statement.
Syntax :
=====
Create [OR Replace] function function_name [parameters]
return return_datatype;
IS
Declaration_section
Begin
Execution_section
Return return_variable;
Exception
exception section
return return_variable;
End;
=====
Create [OR Replace] function function_name [parameters]
return return_datatype;
IS
Declaration_section
Begin
Execution_section
Return return_variable;
Exception
exception section
return return_variable;
End;
Example-1 :write a function input two numbers and find out its sum.
SQL>Create function add(a in number, b in number)
Return number
Is
Begin
c:=a+b;
return c;
End;
/
Function created
Execution :
=======
SQL> var x number
SQL> exec :x:=add(5,10);
PL/SQL procedure successfully completed
SQL> print x
X
---------------------------------------------
15
Return number
Is
Begin
c:=a+b;
return c;
End;
/
Function created
Execution :
=======
SQL> var x number
SQL> exec :x:=add(5,10);
PL/SQL procedure successfully completed
SQL> print x
X
---------------------------------------------
15
Example-2 :write a function input any number and find the factorial.
SQL>Create or replace function fact(n number)
Return number
Is
i number;
f number:=1;
Begin
for i in 1..n loop
f:=f*i;
End loop;
return f;
End;
/
Function created
Execution :
=======
SQL> var x number
SQL> exec :x:=fact(5);
PL/SQL procedure successfully completed
SQL> print x
X
---------------------------------------------
120
Return number
Is
i number;
f number:=1;
Begin
for i in 1..n loop
f:=f*i;
End loop;
return f;
End;
/
Function created
Execution :
=======
SQL> var x number
SQL> exec :x:=fact(5);
PL/SQL procedure successfully completed
SQL> print x
X
---------------------------------------------
120
we consider the following two tables i.e emp, dept to write SQL queries in the coming PL/SQL programs.
Example-3 :write a function to find the maximum salary in employee table.
SQL>Create or replace function bigsal
Return number
Is
c number;
Begin
select max(sal) into c from emp;
Return c;
End;
/
Function created
Execution :
=======
SQL> var x number
SQL> exec :x:=bigsal()
PL/SQL Procedure Successfully Completed
SQL> print x
X
---------------------------------------------
5000
Return number
Is
c number;
Begin
select max(sal) into c from emp;
Return c;
End;
/
Function created
Execution :
=======
SQL> var x number
SQL> exec :x:=bigsal()
PL/SQL Procedure Successfully Completed
SQL> print x
X
---------------------------------------------
5000
Example-4 :write a function to count the number of employees working in sales department.
SQL>Create or replace function countsales
Return number
Is
c number;
Begin
select count(*) into c from emp where deptno=(select deptno from dept where dname=’SALES’);
Return c;
End;
/
Function created
Execution :
=======
SQL> var x number
SQL> exec :x:=countsales()
PL/SQL Procedure Successfully Completed
SQL> print x
X
---------------------------------------------
6
Return number
Is
c number;
Begin
select count(*) into c from emp where deptno=(select deptno from dept where dname=’SALES’);
Return c;
End;
/
Function created
Execution :
=======
SQL> var x number
SQL> exec :x:=countsales()
PL/SQL Procedure Successfully Completed
SQL> print x
X
---------------------------------------------
6
Example-5 :write a function to display the minimum salary employee name working in accounts department.
SQL>Create or replace function ename_minsal
Return varchar2
Is
c varchar2(20);
Begin
select ename into c from emp where sal=(select min(sal) from emp
where deptno=(select deptno from dept where dname=’ACCOUNTING’));
return c;
End;
/
Function created
Execution :
=======
SQL> var y varchar2(20)
SQL> exec :y:=ename_minsal()
PL/SQL Procedure Successfully Completed
SQL> print y
Y
---------------------------------------------
MILLER
Return varchar2
Is
c varchar2(20);
Begin
select ename into c from emp where sal=(select min(sal) from emp
where deptno=(select deptno from dept where dname=’ACCOUNTING’));
return c;
End;
/
Function created
Execution :
=======
SQL> var y varchar2(20)
SQL> exec :y:=ename_minsal()
PL/SQL Procedure Successfully Completed
SQL> print y
Y
---------------------------------------------
MILLER
Example-6 :write a function to display the hire date of an employee who are having the maximum commission.
SQL>Create or replace function hiredatefn
Return date
Is
c date;
Begin
select hiredate into c from emp where comm=(select max(comm) from emp);
Return c;
End;
SQL> var y varchar2(15); //should not take variable as date use varchar, number..etc
SQL> exec :y:=hiredatefn()
PL/SQL function successfully completed
SQL> print y
Y
---------------------------------------------
28-SEP-81
Return date
Is
c date;
Begin
select hiredate into c from emp where comm=(select max(comm) from emp);
Return c;
End;
SQL> var y varchar2(15); //should not take variable as date use varchar, number..etc
SQL> exec :y:=hiredatefn()
PL/SQL function successfully completed
SQL> print y
Y
---------------------------------------------
28-SEP-81
Example-7 :write a function to count number of employees working in sales and accounts department.
SQL>Create or replace function count_emp
Return number
Is
c number;
Begin
select count(*) into c from emp
where deptno in(select deptno from dept where dnmae in(‘SALES’,’ACCOUNTING’));
Return c;
End;
/
Function created
Execution :
=======
SQL> var z number
SQL> exec :z:=count_emp()
PL/SQL Procedure Successfully Completed
SQL> print z
Z
---------------------------------------------
9
Return number
Is
c number;
Begin
select count(*) into c from emp
where deptno in(select deptno from dept where dnmae in(‘SALES’,’ACCOUNTING’));
Return c;
End;
/
Function created
Execution :
=======
SQL> var z number
SQL> exec :z:=count_emp()
PL/SQL Procedure Successfully Completed
SQL> print z
Z
---------------------------------------------
9
Example-8 :write a function to count number of employees whose salaries is greater than any employee salary working in 20th department.
SQL>Create or replace function count_emp
Return number
Is
c number;
Begin
Select count(*) into c from emp where sal> any(select max(sal) from emp where deptno=20);
Return c;
End;
/
Function created
Execution :
=======
SQL> var a number
SQL> exec :a:=count_emp()
PL/SQL Procedure Successfully Completed
SQL> print a
A
---------------------------------------------
1
Return number
Is
c number;
Begin
Select count(*) into c from emp where sal> any(select max(sal) from emp where deptno=20);
Return c;
End;
/
Function created
Execution :
=======
SQL> var a number
SQL> exec :a:=count_emp()
PL/SQL Procedure Successfully Completed
SQL> print a
A
---------------------------------------------
1
Example-9 :write a function to find sum of the salaries in 20th department.
SQL>Create or replace function sumsal
Return number
Is
c number;
Begin
select sum(sal) from emp where deptno=20;
Return c;
End;
/
Function created
Execution :
=======
SQL> var a number
SQL> exec :a:=sumsal()
PL/SQL Procedure Successfully Completed
SQL> print a
A
---------------------------------------------
10875
Return number
Is
c number;
Begin
select sum(sal) from emp where deptno=20;
Return c;
End;
/
Function created
Execution :
=======
SQL> var a number
SQL> exec :a:=sumsal()
PL/SQL Procedure Successfully Completed
SQL> print a
A
---------------------------------------------
10875
--> Syntax to see the list of all sub programs
SQL> select * from user_source;
--> Syntax to see the source code of a particular program
SQL> select text from user_source where name=’<sub program name>’;
--> Syntax to drop the package
SQL> drop function <function_name>;
![]() | ![]() | ![]() |
No comments:
Post a Comment