Differences between Stored procedures(SP) and Functions(User defined functions (UDF)):
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.
- What is Stored Procedures in SQL ?
- Difference between SQL and PLSQL
- SQL | Procedures in PL/SQL
- Different types of Procedures in MySQL
- MySQL | Creating stored function
- SQL | Functions (Aggregate and Scalar Functions)
- PLSQL | SIN Function
- PLSQL | ABS Function
- PLSQL | LN Function
- PLSQL | COS Function
- PLSQL | LOG Function
- PLSQL | MOD Function
- PLSQL | CHR Function
- PLSQL : || Operator
- PLSQL | LEAST Function
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to firstname.lastname@example.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.