Stored procedures are simple programs, or procedures, that are executed in solidDB databases. solidDB supports the following types of stored procedures:
▪ stored procedures that are written in the solidDB proprietary SQL procedure language,
▪ external stored procedures that are written in the C programming language, see External stored procedures.
Because stored procedures are stored and executed directly in the server, the use of stored procedures reduces network traffic and therefore can improve performance. For example, complex, data-bound transactions can be run on the server instead of in the application.
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 to enable 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 execution rights on a stored procedure automatically invokes the necessary access rights to all database objects that are used in the procedure. Therefore, administering database access rights can be greatly simplified by using procedures to access to critical data.
There are also predefined, system stored procedures that you can use to simplify tasks such as administering Advanced Replication, see SQL: System stored procedures.
Stored procedures are registered and called by using SQL statements.
There are three calling methods for the stored procedures:
▪ Local proceduresare executed on a local database server.
▪ Deferred proceduresare procedures that are called after a commit has been processed, see START AFTER COMMIT.
The following example is a simple procedure that determines whether a person is an adult on the basis of a birthday as input parameter.
Note the usage of {fn ...} on scalar functions, and semicolons (;) to end assignments.
"CREATE PROCEDURE grown_up (birth_date DATE) RETURNS (description VARCHAR) BEGIN DECLARE age INTEGER; --determine the number of years since the day of birth age := {fn TIMESTAMPDIFF(SQL_TSI_YEAR, birth_date, now())}; IF age >= 18 THEN -- If age is at least 18, then they are an adult description := 'ADULT'; ELSE -- otherwise they are a minor description := 'MINOR'; END IF END";