Stored procedures are simple programs, or procedures, that are executed in the server. The user can create procedures that contain several SQL statements or whole transactions, and execute them with single call statement. In addition to SQL statements, 3GL type control structures can be used enabling procedural control. In this way complex, data-bound transactions may be run on the server itself, thus rstored procedures reducing network trafficcontrol to 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.
This guide explains in detail how to use the SOLID Server stored procedures. In the beginning of this guide the general concepts of using the procedures are explained. Later chapters go further in the actual syntax of different statements in the procedures. In the end of the guide there are sections discussing transaction management, sequences and other advanced stored procedure features.
A stored procedure is a standard SOLID database object that can be manipulated using standard DDL statements CREATE and DROP.
In its simplest form a stored procedure definition looks like:
"CREATE PROCEDURE < procedure_name> < parameter section> BEGIN <declare section local variables> <procedure body> END";
Note: As the SQL Editor is not able to parse these statements the whole statement has to be enclosed in double quotes.
The following example creates a procedure called TEST:
"CREATE PROCEDURE test BEGIN END";
Procedures can be run by issuing a CALL statement followed by the name of the procedure to be invoked:
CALL test;
Procedure names have to be unique within a database schema.
All the standard naming restrictions considering database objects, like using reserved words, identifier lengths etc., apply to stored procedure names. See appendix F in the Administrator’s Guide for an overview of reserved words.
A stored procedure communicate with the calling program using parameters. Stored procedures accept two types of parameters:have three kinds of parameres:
• Input parameters; given as an input to the procedure can be used inside the procedure.
• Output parameters; returned values from the procedure. Stored procedures may return a result set of several rows with output parameters as the columns.
The types temporary storage of column and control values.of parameters must be declared. See appendix C in the Administrator’s Guide for supported data types. The syntax used in parameter declaration is:
<parameter_name> <parameter datatype>
Input parameters are declared between parentheses directly after the procedure name, output parameters are declared in a special RETURNS section of the procedure definition:
"CREATE PROCEDURE <procedure name> [ (<input param1> <datatype>, <input param2> <datatype>, … >) ] [ RETURNS (<output param1> <datatype>, <output param2> <datatype>, … >) ] BEGIN END";
There can be any number of input and output parameters. Input parameters have to be supplied in the same order as they are defined when the procedure is called.
Declaring input parameters in the procedure heading make their values accessible inside the procedure by referring to the parameter name.
The output parameters will appear in the returned result set. The parameters will appear as columns in the result set in the same order as they are defined. A procedure may return one or rows. Thus, also select statements can be wrapped into database procedures.
The following statement creates a procedure that has two input parameters and two output parameters:
"CREATE PROCEDURE PHONEBOOK_SEARCH (FIRST_NAME VARCHAR, LAST_NAME VARCHAR) RETURNS (PHONE_NR NUMERIC, CITY VARCHAR) BEGIN -- < procedure body > END";
This procedure should be called using two input parameter of data type VARCHAR. The procedure returns an output table consisting of 2 columns named phone_nr of type NUMERIC and CITY of type VARCHAR.
E.g.
call phonebook_search ( ‘JOHN’,’DOE’); Result looks like the following (when the procedure body has been programmed) PHONE_NR CITY 34335556 NEW YORK 23452266 LOS ANGELES
Local variables that are used inside the procedure for temporary storage of column and control values are defined in a separate section of the stored procedure directly following the BEGIN keyword.
The syntax of declaring a variable is:
DECLARE <variable name> <data type>;
Note that every declare statement should be ended with a semicolon (;).
The variable name is an alphanumeric string that identifies the variable. The data type of the variable can be any valid SQL data type supported. See appendix C in the Administrator’s Guide for supported data types.
E.g.
"CREATE PROCEDURE PHONEBOOK_SEARCH (FIRST_NAME VARCHAR, LAST_NAME VARCHAR) RETURNS (PHONE_NR NUMERIC, CITY VARCHAR) BEGIN DECLARE i INTEGER; DECLARE dat DATE; END";
Note that input and output parameters are treated like local variables within a procedure within the exception that input parameters have a preset value and output parameter values are returned or can be appended to the returned result set.
The procedure body contains the actual stored procedure program based on assignments, expressions, SQL statements and the likes.
Any type of expression including scalar functions can be used in a procedure body. See appendix D in the Administrator’s Guide for valid expressions.
To assign values to variables either of the following syntax is used:
SET <variable_name > = < expression> ;
or
<variable_name> := <expression >;
Example:
SET i = i+ 20 ; i := 100;
Variables and constants are initialized every time a procedure is executed. By default, variables are initialized to NULL. Unless a variable has been explicitly initialized, its value is undefined, as the following example shows:
BEGIN DECLARE total INTEGER; ... total := total + 1; -- assigns a null to total ...
Therefore, a variable should never be referenced before it has been assigned a value.
The expression following the assignment operator can be arbitrarily complex, but it must yield a data type that is the same as or convertible to the data type of the variable.
When possible , SOLID procedure language can provide conversion of data types implicitly. This makes it possible to use literals, variables and parameters of one type where another type is expected.
Implicit conversion is not possible if:
• information would be lost in the conversion.
• a string to be converted to an integer contains non-numeric data
Examples:
DECLARE string_var VARCHAR; string_var := ‘The value is ‘ + CONVERT_CHAR(1200*3 / 2);
results in value ‘The value is 1800’ in variable string_var.
DECLARE string_var CHAR(3); string_var := 123.45;
results in value ‘123’ in variable string_var.
DECLARE string_var VARCHAR(2); string_var := 123.45;
Comparison operators compare one expression to another. The result is always TRUE, FALSE, or NULL. Typically, comparisons are used in conditional control statements and allow comparisons of arbitrarily complex expressions. The following table gives the meaning of each operator:
| Operator | Meaning |
| = | is equal to |
| <> | is not equal to |
| < | is less than |
| > | is greater than |
| <= | is less than or equal to |
| >= | is greater than or equal to |
Note that the != notation cannot be used inside a stored procedure, use the ANSI-SQL compliant <> instead.
The logical operators can be used to build more complex queries. The logical operators AND, OR, and NOT operate according to the tri-state logic illustrated by the truth tables shown below. AND and OR are binary operators; NOT is a unary operator.
| NOT | true | false | null |
| false | true | null |
| AND | true | false | null |
| true | true | false | null |
| false | false | false | false |
| null | null | false | null |
| OR | true | false | null |
| true | true | true | true |
| false | true | false | null |
| null | true | null | null |
As the truth tables show, AND returns the value TRUE only if both its operands are true. On the other hand, OR returns the value TRUE if either of its operands is true. NOT returns the opposite value (logical negation) of its operand. For example, NOT TRUE returns FALSE.
NOT NULL returns NULL because nulls are indeterminate.
When not using parentheses to specify the order of evaluation, operator precedence determines the order.
Note that ‘true’ and ‘false’ are not literals accepted by SQL parser but values. Logical expression value can be interpreted as a numeric variable:
false = 0 or NULL
true = 1 or any other numeric value
Example:
IF <expression> = TRUE THEN
can be simply written
IF <expression> THEN
The IS NULL operator returns the Boolean value TRUE if its operand is null, or FALSE if it is not null. Comparisons involving nulls always yield NULL. To test whether a value is NULL, do not use the expression,
IF variable = NULL THEN ...
because it never evaluates to TRUE.
Instead, use the following statement:
IF variable IS NULL THEN ...
Note that when using multiple logical operators in Solid stored procedures the individual logical expressions should be enclosed in parentheses like:
( (A >= B ) AND ( C= 2) ) OR ( A= 3)
Often, it is necessary to take alternative actions depending on circumstances. The IF statement executes a sequence of statements conditionally. There are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSEIF.
The simplest form of IF statement associates a condition with a statement list enclosed by the keywords THEN and END IF (not ENDIF), as follows:
IF <condition> THEN
<statement-list>;
END IF
The sequence of statements is executed only if the condition evaluates to TRUE. If the condition evaluates to FALSE or NULL, the IF statement does nothing. In either case, control passes to the next statement. An example follows:
IF sales > quota THEN
SET pay = pay + bonus;
END IF
The second form of IF statement adds the keyword ELSE followed by an alternative statement list, as follows:
IF <condition> THEN
<statement-list1>;
ELSE
<statement-list2>;
END IF
The statement list in the ELSE clause is executed only if the condition evaluates to FALSE or NULL. Thus, the ELSE clause ensures that a statement list is executed. In the following example, the first or second assignment statement is executed when the condition is true or false, respectively:
IF trans_type = 'CR' THEN
SET balance = balance + credit;
ELSE
SET balance = balance - debit;
END IF
THEN and ELSE clauses can include IF statements. That is, IF statements can be nested, as the following example shows:
IF trans_type = 'CR' THEN
SET balance = balance + credit ;
ELSE
IF new_balance >= minimum_balance THEN
SET balance = balance - debit ;
ELSE
SET balance = minimum_balance;
END IF
END IF
Occasionally it is necessary to select an action from several mutually exclusive alternatives. The third form of IF statement uses the keyword ELSEIF to introduce additional conditions, as follows:
IF <condition1> THEN
<statement-list1>;
ELSEIF <condition2> THEN
<statement-list2>;
ELSE
<statement-list3>;
END IF
If the first condition evaluates to FALSE or NULL, the ELSEIF clause tests another condition. An IF statement can have any number of ELSEIF clauses; the final ELSE clause is optional. Conditions are evaluated one by one from top to bottom. If any condition evaluates to TRUE, its associated statement list is executed and the rest of the statements (inside the IF-THEN-ELSEIF) are skipped. If all conditions evaluate to FALSE or NULL, the sequence in the ELSE clause is executed. Consider the following example:
IF sales > 50000 THEN
bonus := 1500;
ELSEIF sales > 35000 THEN
bonus := 500;
ELSE
bonus := 100;
END IF
If the value of "sales" is more than 50000, the first and second conditions are true. Nevertheless, "bonus" is assigned the proper value of 1500 since the second condition is never tested. When the first condition evaluates to TRUE, its associated statement is executed and control passes to the next statement following the IF-THEN-ELSEIF.
When possible, use the ELSEIF clause instead of nested IF statements. That way, the code will be easier to read and understand. Compare the following IF statements:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
These statements are logically equivalent, but the first statement obscures the flow of logic, whereas the second statement reveals it.
The WHILE-LOOP statement associates a condition with a sequence of statements enclosed by the keywords LOOP and END LOOP, as follows:
WHILE <condition> LOOP
<statement_list>;
END LOOP
Before each iteration of the loop, the condition is evaluated. If the condition evaluates to TRUE, the statement list is executed, then control resumes at the top of the loop. If the condition evaluates to FALSE or NULL, the loop is bypassed and control passes to the next statement. An example follows:
WHILE total <= 25000 LOOP
...
total := total + salary;
END LOOP
The number of iterations depends on the condition and is unknown until the loop completes. Since the condition is tested at the top of the loop, the sequence might execute zero times. In the latter example, if the initial value of "total" is greater than 25000, the condition evaluates to FALSE and the loop is bypassed, altogether
Loops can be nested. When an inner loop is finished control is returned to the next loop. The procedure continues from the next statement after end loop.
It may be necessary to force the procedure to leave a loop prematurely. This can be implemented using the LEAVE keyword:
WHILE total < 25000 LOOP
<statement list>
total := total + salary;
IF <exit_condition> THEN
LEAVE;
END IF
END LOOP
<statement list 2>
Upon successful evaluation of the <exit_condition> the loop is left, and the procedure continues at the statement list 2.
Note that although SOLID Server supports from version 2.2 onwards the ANSI-SQL CASE syntax, the CASE construct cannot be used inside a stored procedure as a control structure!
Nulls can cause confusing behaviour. Some common mistakes can be avoided by keeping the following rules in mind:
• comparisons involving nulls always yield NULL
• applying the logical operator NOT to a null yields NULL
• in conditional control statements, if the condition evaluates to NULL, its associated sequence of statements is not executed
In the example below, you might expect the statement list to execute because "x" and "y" seem unequal. Remember though that nulls are indeterminate. Whether "x" is equal to "y" or not is unknown. Therefore, the IF condition evaluates to NULL and the statement list is bypassed.
x := 5;
y := NULL;
...
IF x <> y THEN -- evaluates to NULL, not TRUE
<statement-list>; -- not executed
END IF
In the next example, one might expect the statement list to execute because "a" and "b" seem equal. But, again, this is unknown, so the IF condition evaluates to NULL and the statement list is bypassed.
a := NULL;
b := NULL;
...
IF a = b THEN -- evaluates to NULL, not TRUE
<statement-list>; -- not executed
END IF
Applying the logical operator NOT to a null yields NULL. Thus, the following two statements are not always equivalent:
|
|
|
|
|
|
|
|
|
|
The sequence of statements in the ELSE clause is executed when the IF condition evaluates to FALSE or NULL. If either or both "x" and "y" are NULL, the first IF statement assigns the value of "y" to "high", but the second IF statement assigns the value of "x" to "high". If neither "x" nor y" is NULL, both IF statements assign the corresponding value to "high".
Zero length strings are treated by SOLID Server like they are : a string of zero length, instead of like a null. NULL values should be specifically assigned like in the following:
SET a = NULL;
This also means that checking for NULL values will return FALSE when applied to a zero-length string.
Following is an example of a simple procedure that determines whether a person is and adult on the basis of a birthday as input parameter.
Note the usage of {} on scalar functions, and semicolons to end assignments and IF/END IF structures.
"CREATE PROCEDURE grown_up
( birth_date DATE)
RETURNS ( description VARCHAR)
BEGIN
DECLARE temp INTEGER;
-- determine the number of years since the day of birth
temp := {fn TIMESTAMPDIFF(SQL_TSI_YEAR,birth_date,now())};
IF temp >= 18 THEN
--over 18 it’s an adult
description := 'ADULT';
ELSE
-- still a minor
description := 'MINOR';
END IF
END";
A procedure may be exited prematurely by issuing the keyword
RETURN;
at any location. After this keyword control is directly handed to the program calling the procedure, returning the values bound to the output parameters as indicated in the returns-section of the procedure definition.
By default a stored procedure returns one row of data. The row is returned when the complete procedure has been run or has been forced to exit. This row conforms to the declared output parameters in the parameter section of the procedure.
Starting from SOLID Server 2.2 it is also possible to return result sets from a procedure using the following syntax:
return row;
Every RETURN ROW call adds a new row into the returned result set.
Using SQL statements inside a stored procedure is somewhat different from issuing SQL directly from tools like SQL Editor.
Any SQL statement will have to be executed through an explicit cursor definition. A cursor is a specific allocated part of the server process memory in which track is kept of the statement being processed. Memory space is allocated for holding one row of the underlying statement, together with some status information on the current row (in SELECTS) or the number of rows affected by the statement (in UPDATES, INSERTS and DELETES).
In this way query results are processed one row at a time. The stored procedure logic should take care of the actual handling of the rows, and the positioning of the cursor on the required row(s).
There are five basic steps in handling a cursor:
1. Preparation of the cursor - the definition
2. Executing the cursor - executing the statement
3. Fetching on the cursor (for select procedure calls) - getting the results row by row
4. Closing the cursor after use - still enabling it to re-execute
5. Dropping the cursor from memory - definitely removing it
A cursor is defined (prepared) using the following syntax:
EXEC SQL PREPARE <cursor name> <SQL statement>;
By preparing a cursor, memory space is allocated to accommodate one row of the result set of the statement, the statement is parsed and optimized.
A cursor name given for the statement has to be unique within the connection. When a cursor is prepared SOLID Server checks that no other cursor of this name is currently open. If there is one, error number 14504 is returned.
Note that statement cursors can be opened also using the ODBC API. Also these cursor names need to be different from the cursors opened from procedures.
Example:
EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name like ‘SYS%’;
This statement will prepare the cursor named sel_tables, but will not execute the statement that it contains.statements have to be prepared before they can be executed. The preparation optimizes the statement and opens a cursor the statement. In preparation phase possible parameters for the statement are indicated using the question mark ‘?’ placeholder for a parameter.
The syntax of the preparation is:
EXEC SQL PREPARE <cursor name> <SQL statement> ;
A cursor name given for the statement has to be unique in the whole database. When a cursor is prepared SOLID Server checks that no other cursor of this name is currently open. If there is one, error number 14504 is returned.
Note that statement cursors can be opened also using the ODBC API. Also these cursors need to be different from the cursors opened from procedures.
The following example prepares an SQL statement and opens cursor el. is executed.
Once a procedure has been successfully prepared it can be executed. An execute binds possible input and output variables to it and runs the actual statement.
Syntax of the execute statement is:
EXEC SQL EXECUTE <cursor name>
[ INTO ( var1, var2, … ) ];
The optional section INTO binds result data of the statement to variables.
Variables listed in parenthesis after the INTO keyword are used when running a SELECT or CALL statement. The resulting columns of the SELECT or CALL statement are bound to these variables when the statement is executed. The variables are bound starting from the left-most column listed in the statement. Binding of variables continues to the following column until all variables in the list of variables have been bound. For example to extend the sequence for the cursor sel_tables that was prepared earlier we need to run the following statements:
EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name like ‘SYS%’ EXEC SQL EXECUTE sel_tables INTO (tab);
The statement is now executed and the resulting table names will be returned into variable tab in the subsequent Fetch statements.
When a SELECT or CALL statement has been prepared and executed it is ready for fetching data from it. Other statements (UPDATE,INSERT,DELETE, DDL) do not require fetching as there will be no result set. Fetching results is done using the fetch syntax:
EXEC SQL FETCH <cursor name>;
This command fetches a single row from the cursor to the variables that were bound with the INTO keyword when the statement was executed.
To complete the previous example to actually get result rows back , the statements will look like:
EXEC SQL PREPARE sel_tables SELECT table_name FROM sys_tables WHERE table_name like ‘SYS%’ EXEC SQL EXECUTE sel_tables INTO (tab); EXEC SQL FETCH sel_tables;
After this the variable tab will contain the table name of the first table found conforming to the WHERE-clause.
Subsequent calls to fetch on the cursor sel_tables will get the next row(s) if the select found more than one.
To fetch all table names a loop construct may be used:
WHILE <expression> LOOP EXEC SQL FETCH sel_tables; END LOOP
Note that after the completion of the loop the variable tab will contain the last fetched table name.
Cursors may be closed by issuing the statement
EXEC SQL CLOSE <cursor_name>;
This will not remove the actual cursor definition from memory, it may be re-executed when the need arises.
Cursors may be dropped from memory, releasing all resources by the statement :
EXEC SQL DROP <cursor_name>;
The return value of the latest EXEC SQL statement executed inside a procedure body is stored into variable SQLSUCCESS. This variable is automatically generated for every procedure. If the previous SQL statement was successful, the value 1 is stored into SQLSUCCESS. After a failed SQL statement, a value 0 is stored into SQLSUCCESS.
The value of SQLSUCCESS may be used, for instance, to determine when the cursor has reached the end of the result set as in the following example:
EXEC SQL FETCH sel_tab;
-- loop as long as last statement in loop is successful
WHILE SQLSUCCESS LOOP
-- do something with the results like return the row
EXEC SQL FETCH sel_tab;
END LOOP
After the execution of UPDATE, INSERT and DELETE statements an additional variable is available to check the result of the statement. Variable SQLROWCOUNT contains the number of rows affected by the last statement.
For error checking of EXEC SQL statements the SQLSUCCESS variable may be used like indicated above. To return the actual error that caused the statement to fail to the calling application the following syntax may be used:
EXEC SQL PREPARE <cursornname> <sql statement> EXEC SQL EXECUTE <cursorname> IF NOT SQLSUCCESS THEN RETURN SQLERROR OF <cursorname>; END IF
Processing will stop immediately when this statement is executed and the procedure return code is SQL_ERROR. The actual database error can be returned using SQLError function:
Solid Database error 10033: Primary key unique constraint violation
From SOLID Server 2.2 onwards the need to code
IF NOT SQLSUCCESS THEN…
after every SQL statement in a procedure can be diminished by using the following syntax:
EXEC SQL WHENEVER SQLERROR [ROLLBACK [WORK],] ABORT;
When this statement is included in a stored procedure all return values of executed SQL statements are checked for errors. If statement execution returns an error, the procedure is automatically aborted and SQLERROR of the last cursor is returned. Optionally the transaction can be rolled back.
The statement should be included before any EXEC SQL statements directly following the DECLARE section of variables.
Below is an example of a complete procedure returning all table names from SYS_TABLES that start with ‘SYS’:
"CREATE PROCEDURE sys_tabs
RETURNS ( tab VARCHAR)
BEGIN
-- abort on errors
EXEC SQL WHENEVER SQLERROR ROLLBACK, ABORT;
-- prepare the cursor
EXEC SQL PREPARE sel_tables
SELECT table_name
FROM sys_tables
WHERE table_name like ‘SYS%’;
-- execute the cursor
EXEC SQL EXECUTE sel_tables INTO (tab);
-- loop through rows
EXEC SQL FETCH sel_tables;
WHILE sqlsuccess LOOP
RETURN ROW;
EXEC SQL FETCH sel_tables;
END LOOP
-- close and drop the used cursors
EXEC SQL CLOSE sel_tables;
EXEC SQL DROP sel_tables;
END";
In order to make a cursor more dynamic, an SQL statement can contain parameter markers that indicate values that are bound to the actual parameter values at execute time. As parameter marker the ? symbol is used.
Syntax example:
EXEC SQL PREPARE sel_tabs SELECT table_name FROM sys_tables WHERE table_name LIKE ? AND table_schema LIKE ?;
The execution statement is adapted by including a USING keyword to accommodate the binding of a variable to the parameter marker.
EXEC SQL EXECUTE sel_tabs USING ( var1, var2 ) INTO ( tabs);
In this way a single cursor can be used multiple times without having to re-prepare the cursor. As preparing a cursor involves also the parsing and optimizing of the statement, significant performance gains can be achived by using re-usable cursors.
Note that the USING list only accepts variables, data can not be directly passed in this way. So if e.g. an insert into a table should be made, one column value of which should always be the same ( status = ‘NEW’) then the following syntax would be wrong:
EXEC SQL EXECUTE ins_tab USING (nr, desc, dat, ’NEW’);
The correct way would be to define the constant value in the prepare section:
EXEC SQL PREPARE ins_tab INSERT INTO my_tab ( id, descript, in_date, status) VALUES ( ?,?,?,’NEW’); EXEC SQL EXECUTE ins_tab USING ( nr, desc, dat);
Note that variables can be used multiple times in the using list!
The parameters in a SQL statement have no intrinsic data type or explicit declaration. Therefore, parameter markers can be included in an SQL statement only if their data types can be inferred from another operand in the statement.
For example, in an arithmetic expression such as ? + COLUMN1, the data type of the parameter can be inferred from the data type of the named column represented by COLUMN1. A procedure cannot use a parameter marker if the data type cannot be determined.
The following table describes how a data type is determined for several types of parameters.
| Location of Parameter | Assumed Data Type |
| One operand of a binary arithmetic or comparison operator | Same as the other operand |
| The first operand in a BETWEEN clause | Same as the other operand |
| The second or third operand in a BETWEEN clause | Same as the first operand |
| An expression used with IN | Same as the first value or the result column of the subquery |
| A value used with IN | Same as the expression |
| A pattern value used with LIKE | VARCHAR |
| An update value used with UPDATE | Same as the update column |
An application cannot place parameter markers in the following locations:
• As a SQL identifier (name of a table, name of a column etc.)
• In a SELECT list.
• As both expressions in a comparison-predicate.
• As both operands of a binary operator.
• As both the first and second operands of a BETWEEN operation.
• As both the first and third operands of a BETWEEN operation.
• As both the expression and the first value of an IN operation.
• As the operand of a unary + or - operation.
• As the argument of a set-function-reference.
For more information, see the ANSI SQL-92 specification.
In the following example, a stored procedure will read rows from one table and insert parts of them in another, using multiple cursors:
"CREATE PROCEDURE tabs_in_schema (schema_nm VARCHAR)
RETURNS ( nr_of_rows INTEGER)
BEGIN
DECLARE tab_nm VARCHAR;
EXEC SQL PREPARE sel_tab
SELECT table_name
FROM sys_tables
WHERE table_schema = ?;
EXEC SQL PREPARE ins_tab
INSERT INTO my_table (table_name,schema) VALUES ( ?,?);
nr_of_rows := 0;
EXEC SQL EXECUTE sel_tab USING ( schema_nm) INTO (tab_nm);
EXEC SQL FETCH sel_tab;
WHILE SQLSUCCESS LOOP
nr_of_rows := nr_of_rows + 1;
EXEC SQL EXECUTE ins_tab USING(tab_nm, schema_nm);
IF SQLROWCOUNT <> 1 THEN
RETURN SQLERROR OF ins_tab;
END IF
EXEC SQL FETCH sel_tab;
END LOOP
END ";
As calling a procedure forms a part of the supported SQL syntax, a stored procedure may be called from within another stored procedure. Like all SQL statements a cursor should be prepared and executed like:
EXEC SQL PREPARE cp call_myproc( ?,?); EXEC SQL EXECUTE cp USING ( var1, var2);
If procedure my_proc returns one or more values, then subsequently a fetch should be done on the cursor cp to retrieve those values:
EXEC SQL PREPARE cp call_myproc( ?,?); EXEC SQL EXECUTE cp USING ( var1, var2) INTO ( ret_var1, ret_var2); EXEC SQL FETCH cp;
Note that if the called procedure uses a return row statement, the calling procedure should utilize a WHILE LOOP construct to fetch all results.
Recursive calls are possible, but discouraged because cursor names are unique at connection level and infinite recursion may crash the server process.
In SOLID Server procedures it is possible to use positioned updates and deletes. This means that an update or delete will be done to a row where a given cursor is currently positioned. The positioned updates and deletes can also be used within stored procedures using the cursor names used within the procedure.
Following syntax is used for positioned updates
UPDATE <table_name> SET <column> = value WHERE CURRENT OF <cursor_name>
and for deletes
DELETE FROM <table_name> WHERE CURRENT OF <cursor_name>
In both cases the <cursor_name>refers to a statement doing a SELECT on the table that is to be updated/deleted from.
Positioned cursor update is a semantically suspicious concept in SQL standard that may cause peculiarities also with SOLID Server. Please note the following restriction when using positioned updates.
Below is an example written with pseudo code that will cause an endless loop with SOLID Server (error handling, binding variables & other important tasks omitted for brevity and clarity):
"CREATE PROCEDURE ENDLESS_LOOP BEGIN EXEC SQL PREPARE MYCURSOR SELECT * FROM TABLE1; EXEC SQL PREPARE MYCURSOR_UPDATE UPDATE TABLE1 SET COLUMN2 = 'new data'; EXEC SQL EXECUTE MYCURSOR; EXEC SQL FETCH MYCURSOR; WHILE SQLSUCCESS LOOP EXEC SQL EXECUTE MYCURSOR_UPDATE; EXEC SQL COMMIT WORK; EXEC SQL FETCH MYCURSOR; END LOOP END";
The endless loop is caused by the fact that when the update is committed, a new version of the row becomes visible in the cursor and it is accessed in the next FETCH statement. This happens because the incremented row version number is included in the key value and the cursor finds the changed row as the next greater key value after the current position. The row gets updated again, the key value is changed and again it will be the next row found.
In the above example, the updated column2 is not assumed to be part of the primary key for the table, and the row version number was the only index entry changed. However, if such a column value is changed that is part of the index through which the cursor has searched the data, the changed row may jump further forward or backward in the search set.
For these reasons, using positioned update is not recommended in general and searched update should be used instead whenever possible. However, sometimes the update logic may be too complex to be expressed in SQL WHERE clause and in such cases positioned update can be used as follows:
Positioned cursor update works deterministically in SOLID, when the where clause is such that the updated row does not match the criteria and therefore does not reappear in the fetch loop. Constructing such a search criteria may require using additional column only for this purpose.
Note that other users' changes do not become visible in the open cursor, only those committed within the same database session.
Stored procedures use transactions like any other interface to the database. A transaction may be committed or rolled back either inside the procedure or outside the procedure. Inside the procedure a commit or roll back is done using the following syntax:
EXEC SQL COMMIT WORK; EXEC SQL ROLLBACK WORK;
These statements end the previous transaction and start a new one.
If a transaction is not committed inside the procedure, it may be ended externally using:
• the SQL API,
• another stored procedure or
• by autocommit, if the connection has AUTOCOMMIT switch set to ON.
Note that when a connection has autocommit activated it does not force autocommit inside a procedure. The commit is done when the procedure exits.
By default, when a procedure exits, all cursors opened in a procedure are closed. Closing cursors means that cursors are left in prepared state and can be re-executed.
After exit the procedure is put on the procedure cache. When the procedure is dropped from the cache, all cursors are finally dropped.
The number of procedures kept in cache is determined by the solid.ini file setting :
[SQL] ProcedureCache = <nr of procedures>
This means that, as long as the procedure is on the procedure cache, all cursors can be re-used as long as they are not dropped. SOLID Server itself manages this by keeping track of the cursors declared, and notices if the statement a cursor contains has been prepared.
As cursor management, especially in a heavy multi-user environment, can use a considerable amount of server resources it is good practice to always close cursors immediately and preferably also drop all cursors that are not used anymore.
Only the most frequently used procedures may be left non-dropped to reduce the cursor preparation effort.
Note that transactions are not related to procedures or other statements. Commit or rollback does therefore NOT release any resources in a procedure.
• There is no restriction on the SQL statements used. Any valid SQL statement can be used inside a stored procedure, including DDL and DML statements
• Cursors may be declared anywhere in a stored procedure. Cursors that are certainly going to be used are best prepared directly following the declare section.
• Cursors that are used inside control structures, and are therefore not always necessary, are best declared at the point where they are activated, to limit the amount of open cursors and hence the memory usage.
• The cursor name is an undeclared identifier, not a variable; it is used only to reference the query. You cannot assign values to a cursor name or use it in an expression.
• Cursors may be re-executed repeatedly without having to re-prepare them. Note that this can have a serious influence on performance; repetitively preparing cursors on similar statements may decrease the performance by around 40% in comparison to re-executing already prepared cursors!
• Any SQL related statement will have to be preceded by the keywords EXEC SQL.
SOLID sequences can only be accessed through stored procedures. After creation of the sequence by:
CREATE [DENSE] SEQUENCE <sequence_name>;
the current sequence value can be retrieved by using the following syntax:
EXEC SEQUENCE <sequence_name>.CURRENT INTO <variable>;
New sequence values can be retrieved using the following syntax:
EXEC SEQUENCE <sequence_name>.NEXT INTO <variable>;
It is also possible to set the current value of a sequence to a predefined value by using the following syntax:
EXEC SEQUENCE <sequence_name> SET VALUE USING <variable>;
An example of using a stored procedure to retrieve a new sequence number is given below:
"CREATE PROCEDURE get_my_seq RETURNS (val INTEGER) BEGIN EXEC SEQUENCE my_sequence.NEXT INTO val; END";
Event alerts are special objects in a SOLID Server database. They are used for sending events from one application to another. The use of event alerts removes resource consuming database polling from applications.
The system does not automatically generate events, they must be triggered by stored procedures. Similarly the events can only be received in stored procedures. When an application calls a stored procedure that waits for a specific event to happen, the application is blocked until the event is triggered and received. In multithreaded environments separate threads and connections can be used to access the database during the event standstill.
An event has a name that identifies it and a set of parameters. The name can be any user-specified alphanumeric string. An event object is created with the SQL statement
CREATE EVENT event-name
[(parameter-name data-type
[parameter-name data-type ...])]
The parameter list specifies parameter names and parameter types. The parameter types are normal SQL types. Events are dropped with the SQL statement
DROP EVENT event-name
Events are triggered and received inside stored procedures. Special stored procedure statements are used to trigger and receive events.
The event is triggered with the stored procedure statement
POST EVENT event-name (parameters)
Event parameters must be local variables or parameters in the stored procedure where the event is triggered. All clients that are waiting for the posted event will receive the event.
To make a procedure wait for an event to happen, the WAIT EVENT construct is used in the stored procedure:
wait-event-statement ::=
WAIT EVENT
[event-specification ...]
END WAIT
event-specification ::=
WHEN event-name (parameters) BEGIN
statements
END EVENT
Stored procedures are owned by the creator, and are part of the creator’s schema. Users needing to run stored procedures in other schema’s need to be granted EXECUTE privilege on the procedure:
GRANT EXECUTE ON <Proc_name> TO USER[,ROLE];
All database objects accessed within the granted procedure , even subsequently called procedures, are accessed according the rights of the owner of the procedure. No special grants are necessary.