Many RDBMS support the concept of “routines”, usually calling them procedures and/or functions. These concepts have been around in programming languages for a while, also outside of databases. Famous languages distinguishing procedures from functions are:
The general distinction between (stored) procedures and (stored) functions can be summarised like this:
- Are called using JDBC CallableStatement
- Have no return value
- Usually support OUT parameters
- Can be used in SQL statements
- Have a return value
- Usually don’t support OUT parameters
But there are exceptions to these rules:
- DB2, H2, and HSQLDB don’t allow for JDBC escape syntax when calling functions. Functions must be used in a SELECT statement
- H2 only knows functions (without OUT parameters)
- Oracle functions may have OUT parameters
- Oracle knows functions that mustn’t be used in SQL statements for transactional reasons
- Postgres only knows functions (with all features combined). OUT parameters can also be interpreted as return values, which is quite elegant/freaky, depending on your taste
- The Sybase jconn3 JDBC driver doesn’t handle null values correctly when using the JDBC escape syntax on functions
In general, it can be said that the field of routines (procedures / functions) is far from being standardised in modern RDBMS. Every database has its ways and JDBC only provides little abstraction over the great variety of procedures / functions implementations, especially when advanced data types such as cursors / UDT’s / arrays are involved.
3 thoughts on “What are procedures and functions after all?”
Simple and straight forward explanation.
Thanks, Mitesh. I stumbled upon your blog and I’m glad to read some things about Cloud Computing, especially when databases are involved – as I’m planning to officially support some products with jOOQ. I see you’ve posted about SQL Azure. Have you made some experience with Google Cloud SQL and/or Sybase SQL Anywhere OnDemand as well?