I has been using PLSQL to build storage procedures in my daily work for a while.
Here are some lessons I have learned in our code that could have saved me some trouble in the future.
- Be careful where you commit your changes to data.
- Create a separated script for schema changes. Easier to track changes.
- Use one single wrapper object as the args for procedure.
- Use SYSREFCUSOR to return result set.
- Close resultSet (must)/ Statement (must)/ Connections(depends) after finished.
- Wrapping insert/update clause into functions will saves a lot of effort when table/type change.