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.
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.
- What is Stored Procedures in SQL ?
- Advantages and Disadvantages of Using Stored Procedures - SQL
- Difference between Stored and Derived Attribute
- 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 : || Operator
- PLSQL | COS Function
- PLSQL | SIN Function
- PLSQL | LOG Function
- PLSQL | LEAST Function
- PLSQL | MOD Function
- PLSQL | CHR Function
- PLSQL | EXP Function
- PLSQL | ABS Function
- PLSQL | TAN Function
- PLSQL | LN Function
- PLSQL | ADD_MONTHS 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 email@example.com. 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.