Difference between Procedure and Function – Learn how to create a procedure and function with example Programs and also learn basic difference between procedure and functions with simple examples.
Basic difference between Procedure and Function
| S.No | PROCEDURE | FUNCTION |
|---|---|---|
| 1 | Used mainly to execute certain business logic with DML and DRL statements | Used mainly to perform some computational process and returning the result of that process. |
| 2 | Procedure can return zero or more values as output. | Function can return only single value as output |
| 3 | Procedure cannot call with select statement, but can call from a block or from a procedure | Function can call with select statement , if function doesnot contain any DML statements and DDL statements.. function with DML and DDL statements can call with select statement with some special cases (using Pragma autonomous transaction) |
| 4 | OUT keyword is used to return a value from procedure | RETURN keyword is used to return a value from a function. |
| 5 | It is not mandatory to return the value | It is mandatory to return the value |
| 6 | RETURN will simply exit the control from subprogram | RETURN will exit the control from subprogram and also returns the value |
| 7 | Return datatype will not be specified at the time of creation | Return datatype is mandatory at the time of creation |
Let’s Create a Student table (std) with column names as (STDNO , SNAME, JOINDATE, FEE,EPTNO)
CREATE TABLE std(STDNO NUMBER(4,0),
SNAME VARCHAR2(10 BYTE),
JOINDATE DATE,
FEE NUMBER(7,2),
DEPTNO NUMBER(2,0));Insert the following data into std Table
| STDNO | SNAME | JOINDATE | FEE | DEPTNO |
|---|---|---|---|---|
| 7369 | SMITH | 17-DEC-80 12.00.00 AM | 13000 | 1 |
| 7499 | ALLEN | 20-FEB-81 12.00.00 AM | 16000 | 3 |
| 7521 | WARD | 22-FEB-81 12.00.00 AM | 12500 | 3 |
| 7566 | JONES | 02-APR-81 12.00.00 AM | 2975 | 2 |
| 7654 | MARTIN | 28-SEP-81 12.00.00 AM | 12500 | 3 |
| 7698 | BLAKE | 01-MAY-81 12.00.00 AM | 28500 | 3 |
| 7844 | TURNER | 08-SEP-81 12.00.00 AM | 1500 | 3 |
To know about point 1 and 2 of functions and procedures please click on the hyperlinks.
Before discussing from point 3 in procedures and functions, Lets create procedures and functions to calculate the highest fee pay for the given department.
Simple program to find the highest fee pay from the department using function and procedures.
Logic implementation using functions
CREATE OR REPLACE
FUNCTION FUN_GET_highest_fee_by_detp(
P_DEPT_NO IN NUMBER)
RETURN NUMBER
AS
P_NEW_FEE NUMBER;
BEGIN
IF P_DEPT_NO IS NOT NULL THEN
BEGIN
SELECT MAX(FEE) INTO P_NEW_FEE FROM STD WHERE DEPTNO=P_DEPT_NO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
P_NEW_FEE :=0;
END;
END IF;
RETURN P_NEW_FEE;
END;Logic implementation using procedures
CREATE OR REPLACE
PROCEDURE PROC_GET_highest_fee_by_detp(
P_DEPT_NO IN NUMBER ,
P_NEW_FEE OUT NUMBER)
AS
BEGIN
IF P_DEPT_NO IS NOT NULL THEN
BEGIN
SELECT MAX(FEE) INTO P_NEW_FEE FROM STD WHERE DEPTNO=P_DEPT_NO;
EXCEPTION
WHEN NO_DATA_FOUND THEN
P_NEW_FEE :=0;
END;
END IF;
END;Calling function using select statement.
select FUN_GET_highest_fee_by_detp(3) highest_fee_pay from dual;| highest_fee_pay |
|---|
| 28500 |
Calling Procedure using select statement.
select PROC_GET_highest_fee_by_detp(7369,0) from dual;Output
ORA-00904: “PROC_GET_HIGHEST_FEE_BY_DETP”: invalid identifier
00904. 00000 – “%s: invalid identifier”
When we try to execute a procedure using select it will through an error stating as above output.
Calling function using pl-sql block.
DECLARE
V_Hihest_FEE number;
BEGIN
V_Hihest_FEE :=FUN_GET_highest_fee_by_detp(3);
dbms_output.put_line('highest fee pay for the department 3 is ' || V_Hihest_FEE);
END;Output
highest fee pay for the department 3 is 28500
Calling procedure using pl-sql block.
DECLARE
V_Hihest_FEE number;
BEGIN
PROC_GET_highest_fee_by_detp(3,V_Hihest_FEE);
dbms_output.put_line('highest fee pay for the department 3 is ' || V_Hihest_FEE);
END;Output
highest fee pay for the department 3 is 28500
here is the simple programs to update the fee pay for the student using function and procedures.
Logic implementation using functions
CREATE OR REPLACE
FUNCTION FUN_UPDATE_NEW_PAY(
P_STD_NO IN NUMBER ,
P_FEE IN NUMBER)
RETURN NUMBER
AS
P_NEW_FEE NUMBER;
BEGIN
UPDATE STD
SET FEE =FEE+P_FEE
WHERE STDNO=P_STD_NO RETURNING FEE
INTO P_NEW_FEE;
return P_NEW_FEE;
END;Logic implementation using procedures
CREATE OR REPLACE
PROCEDURE PROC_UPDATE_NEW_PAY(
P_STD_NO IN NUMBER ,
P_FEE IN NUMBER,
P_NEW_FEE OUT NUMBER)
AS
BEGIN
UPDATE STD
SET FEE =FEE+P_FEE
WHERE STDNO=P_STD_NO RETURNING FEE
INTO P_NEW_FEE;
END;Calling function using select statement.
select FUN_UPDATE_NEW_PAY(7369,1000) from dual;Output:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "TRAINING593.FUN_UPDATE_NEW_PAY", line 10
14551. 00000 - "cannot perform a DML operation inside a query "
*Cause: DML operation like insert, update, delete or select-for-update
cannot be performed inside a query or under a PDML slave.
*Action: Ensure that the offending DML operation is not performed or
use an autonomous transaction to perform the DML operation within
the query or PDML slave.Note:
- Use autonomous transactions to perform the DML operations in a function calling with a select statement.
We can call the above function using plsql block without select statement as follows
Calling function using pl-sql block
DECLARE
V_new_FEE number;
BEGIN
V_new_FEE :=FUN_UPDATE_NEW_PAY(7369,1000);
dbms_output.put_line('new fee pay for the student id 7369 is ' || V_new_FEE);
END;Output
new fee pay for the student id 7369 is 13000
Calling procedure using pl-sql block
DECLARE
V_new_FEE number;
BEGIN
PROC_UPDATE_NEW_PAY(7369,1000,V_new_FEE);
dbms_output.put_line('new fee pay for the student id 7369 is ' || V_new_FEE);
END;Output
new fee pay for the student id 7369 is 14000
Logic implementation using functions to perform autonomous transactions.
CREATE OR REPLACE
FUNCTION FUN_UPDATE_NEW_PAY(
P_STD_NO IN NUMBER ,
P_FEE IN NUMBER)
RETURN NUMBER
AS
pragma autonomous_transaction;
P_NEW_FEE NUMBER;
BEGIN
UPDATE STD
SET FEE =FEE+P_FEE
WHERE STDNO=P_STD_NO RETURNING FEE
INTO P_NEW_FEE;
COMMIT;
RETURN P_NEW_FEE;
END;Sql query to call the functions containing DML statements.
select FUN_UPDATE_NEW_PAY(7369,1000) from dual;Note:
- Pragma transactions are the child transactions for the main transaction.
- This child transactions executes independently with the main transaction.
- DML operations with Pragma transactions needs to commit explicitly with in the block when we are calling using select query.
- No need of pragma declaration in a function with DML statement execution, when we are calling from PL-SQL block without select statement.