SQL | Difference between functions and stored procedures in PL/SQL
Differences between Stored procedures(SP) and Functions(User defined functions (UDF)):
Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course by GeeksforGeeks.
- SP may or may not return a value but UDF must return a value.
SP -> create or replace procedure GEEKS(x int) is y int; begin ..... UDF-> FUNCTION GEEKS(x int) /*return statement so we must return value in function */ RETURN int IS y int; BEGIN .....
- SP can have input/output parameter but UDF only has input parameter.
SP -> CREATE OR REPLACE PROCEDURE Factorial(x IN NUMBER, result OUT NUMBER) is begin .... UDF -> FUNCTION Factorial(x IN NUMBER) /* only input parameter */ return NUMBER is result NUMBER; begin .....
- We can call UDF from SP but cannot call SP from a function.
Calling UDF cal() inside SP square() but reverse is not possible. set serveroutput on; declare a int; c int; function cal(temp int) return int as ans int; begin ans:=temp* temp; return ans; end; procedure square(x in int, ans out int) is begin dbms_output.put_line('calling function in procedure'); ans:= cal(x); end; begin a:=6; square(a, c); dbms_output.put_line('the answer is '|| c); end;
calling function in procedure the answer is 36
- We cannot use SP in SQL statement like SELECT, INSERT, UPDATE, DELETE, MERGE etc. but we can use them with UDF.
- We can use try-catch exception handling in SP but we cannot do that in UDF.
- We can use transaction in SP but it is not possible in UDF.