Whenever we write and run a set of SQL statements together they can be categorized in one of the following -
- Stored procedures
- User defined functions
A very common interview question. Why would we write a SQL stored procedure when we can do the same via JDBC calls. The most simple answer is to avoid network traffic. Instead of making multiple JDBC calls we just call the procedure and handle the business logic in it.
Consider a very simple scenario. You have to maintain records of the money other people owe you or the amount you owe to others. So you create a table with lets say column as name and amoutDue. Every time you make or receive payment you will have to fetch the data, compare it with current payment and then to addition or subtraction accordingly. This will increase your network calls leading to high network traffic. So instead you call a procedure with name and amount (positive or negative) and let the procedure handle the logic. So in procedure we would have logic to select row for the name provided., depending on the amount alter the amount column for the entry. If the amount is o then there is no need to keep the record/row in the table. So delete it.
There are other benefits as well like transaction management and error handling that can be done at stored procedure level but the major benefit is less network traffic. Another use case in centralized maintained . You have to change only the common stored procedure and the business logic will get reflected in all dependent applications.
Difference between Stored procedure and user defined Functions
- A function can return only one value which is mandatory where as a stored procedure can have multiple (0 or more) output parameters.
- Function can have only input parameters where as a stored procedure can have multiple input/output parameters.
- We can use select as well as DML statements (insert/update/delete) in stored procedures but only select statement in a function.
- We can call a function from within a procedure but we cannot call a procedure from a function.
- We can do Exception handling as well as transaction management in procedures but we cannot do the same in functions.
- Functions can be used in SQL statements anywhere in the WHERE/HAVING/SELECT clause where as stored procedures cannot be.
- You can directly call a procedure and exec myUser.myPackage.myProc(myArg1,,myArg2). Function you can use it either in a query or call from a procedure.
User defined Functions
Function in a general SQL context can be either -
- System Defined
- Scalar Functions (Eg. abs(), round(), upper(), lower() etc.)
- Aggregate Functions(Eg. max(), min(), avg(), count() etc.) [generally used in group by clause]
- Usage : SELECT *, MyUser.MyScalarFunction() FROM MyTable
- User Defined
- Scalar Function
- Scalar function return single value due to actions perform by the function.
- Function can return value of any data type.
- Usage : SELECT * FROM MyTableFunction()
- These type of functions returns a table variable due to actions perform by function.
- We can only use a single SELECT statement to return the value.
- These type of functions returns a custom declared table variable due to action perform by function.
- Here we need to explicitly define the table schema to be returned. We can perform operations on this table to insert/update/delete and then return.
To see actual SQL queries for procedures and functions in PL/SQL refer -