Stored procedures are simple programs, or procedures, that are executed in solidDB® databases. solidDB® supports both SQL-written procedures stored in the server and external procedures written in C programming language, stored as dynamic libraries.
There are two types of stored procedures:
▪Stored procedures written in the solidDB® proprietary SQL procedure language
▪External stored procedures written in C programming language.
The external stored procedures are loaded at runtime using standard dynamic library interfaces as provided by the operating system. To enable use of external stored procedures, you must link your application dynamically to solidDB® using shared memory access (SMA) or linked library access (LLA).
Because stored procedures are stored and executed directly in the server, usage of stored procedures reduces network traffic and can thus improve performance. For example, complex, data-bound transactions can be run on the server itself. External stored procedures can also be used as migration tools; missing functionality can be added at the cost of writing a C program.
You can create a procedure that contains several SQL statements or a whole transaction and execute it with a single call statement. In addition to SQL statements, 3GL type control structures can be used enabling procedural control. You can also create nested stored procedures where one procedure is executed from within another.
Stored procedures can also be used for controlling access rights and database operations. Granting execute rights on a stored procedure automatically invokes the necessary access rights to all database objects used in the procedure. Therefore, administering database access rights may be greatly simplified by allowing access to critical data through procedures.
Stored procedures are registered and called using SQL statements.
There are three calling methods for the stored procedures:
▪Local procedures are executed on a local database server.
▪Remote procedures are procedures that are stored on one server and called by another. Remote stored procedures are applicable only to advanced replication setups.
▪Deferred procedures are procedures that are called after commit has been processed.
There are also system stored procedures that you can use to simplify tasks related to administering advanced replication operations.