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:
- Ada
- BASIC
- Pascal
- etc…
The general distinction between (stored) procedures and (stored) functions can be summarised like this:
Procedures:
- Are called using JDBC CallableStatement
- Have no return value
- Usually support OUT parameters
Functions:
- 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.