Database Procedures
A database procedure is a set of SQL statements that can be stored in the database. Once this has been done, clients do not need to keep reissuing the individual statements but can refer to the database procedure instead.
Database procedures can be useful in many situations:
When multiple client applications are written in different languages or work on different platforms, but need to perform the same database operations.
Some institutes, like banks, where security is important, use database procedures for all common operations. This provides a consistent and secure environment, and procedures can ensure that each operation is properly logged. In such a setup, applications and users would not get any access to the database tables directly, but can only execute specific database procedures.
Database procedures can provide improved performance because less information needs to be sent between the server and the client. However, this increases the load on the database server system because more of the work is done on the server side and less is done on the client (application) side. Take this into consideration if many client machines (such as Web servers) are serviced by only one or a few database servers.
Database procedures also allow you to have libraries of functions in the database server. This is a feature shared by modern application languages that allow such design internally, for example, by using classes.
Database Procedures in Information Designer
Using Information Designer you select pre-made database procedures and configure these to be accessible in information links. These information links are available to the Spotfire users to retrieve or manipulate data.
In Information Designer you select a database procedure from your available data sources, and define which input parameters that the procedure should prompt for, and any potential resulting columns and joins.
Then you configure a complete information link with one or more combinations of procedures and columns from other tables.
The Three Kinds of Procedures
Information Designer defines three kinds of procedures:
Pre-update procedure - this procedure does not return any data, it only performs an operation on one or more databases. All pre-update procedures in an information link will always be executed before any query procedure.
Query procedure - just like a database table, this procedure returns data.
Post-update procedure - this procedure does not return any data, it only performs an operation on one or more databases. All post-update procedures in an information link will always be executed after any query procedure.
Note: If using Oracle stored procedures that return data, Information Designer only support procedures that return data of the type REF CURSOR (also known as Table Function).