Procedures are owned by the creator of the procedure. Access rights can be granted to other users. When the procedure is run, it has the same rights to database objects as the creator.
Usage
Use the CREATE PROCEDURE statement to create a procedure that contains several SQL statements or a whole transaction and execute it with a single call statement, see Stored procedures.
Stored procedures are written in the solidDB proprietary SQL procedure language (internal procedures) or in the C programming language (external procedures), see External stored procedures.
The output of an external procedure can be returned as a result set with a single row or as output parameters.
▪ SQL‑data‑access‑indication: Indicates how the procedure affects the associated data. You can use this clause to make the use of stored procedures more efficient in HotStandby (HSB) configurations, see Using stored procedures in HotStandby configurations.
– READS SQL DATA: Procedure contains only SQL instructions that read data, for example. SELECT statements.
– MODIFIES SQL DATA: Procedure contains SQL statements that write data, for example, UPDATE, INSERT, or DELETE statements.
– NO SQL: Procedure contains no SQL statements.
– CONTAINS SQL: Procedure contains SQL statements, but does not contain statements that read or write data, for example, SET statements.
▪ int-procedure-definition
– prepare‑statement:
▪ EXEC SQL PREPARE: Prepares the SQL statements.
▪ cursor‑name: Specifies a cursor name inside the transaction.
▪ CURSORNAME: Dynamically generates a cursor name instead of using a specified cursor name, see Using dynamic cursor names.
– execute‑statement: Executes prepared SQL statements by using cursors, and closes or drops cursors. For more information, see Using a cursor.
The statement can also be used to terminate transactions or control the transaction type. For more information, see Transactions.
– fetch statement: Fetches rows. If the fetch completes successfully, the column values are stored into the variables that are defined in the INTO clause of the EXECUTE or EXECDIRECT statement. For more information, see Using a cursor.
– post‑statement: Posts system events and user-defined events, see POST EVENT.
– wait‑register‑statement: Tells the server that you want to be notified (or stop being notified) of all future occurrences of the specified event, even if you are not yet waiting for it, see REGISTER EVENT and UNREGISTER EVENT.
By separating the REGISTER EVENT and WAIT EVENT statements, you can start queuing events immediately, while waiting until later to actually start processing them.
– wait‑event‑statement: Makes procedures wait for an event to happen before continuing. You can use WAIT EVENT with both system-defined events and user-defined events.
If you want to stop the stored procedure waiting for an event, you can use ODBC function SQLCancel() called from a separate thread in the client application. This function cancels executing statements. Alternatively, you can create a specific user event and send it. The waiting stored procedure must be modified to wait for this additional event. The client application recognizes this event and exits the waiting loop.
With system events, you can also wait on an event without using a stored procedure by using the ADMIN EVENT statement, see ADMIN EVENT. However, you cannot post events by using ADMIN EVENT.
– control‑statement: Control statements that you can use in stored procedures. For more information, see Using control statements.
– writetrace‑statement: Sends trace output (string) to the soltrace.out trace file. This can be useful when debugging problems in stored procedures.
The output is written only if you turn tracing on.
– exec‑direct‑statement: Allows you to execute statements inside stored procedures without preparing those statements first. For more information, see Using EXECDIRECT.
▪ ext-procedure-definition:
– LANGUAGE C: Indicates that the CREATE PROCEDURE statement is being used to register a new procedure that is based on code that is written in the C programming language.
– EXTERNAL NAME'ext‑procedure‑lib‑identifier': Identifies the name of the user-written code that implements the procedure. The name must be enclosed by single quotation marks. Extraneous blanks are not permitted.
– [path]library‑name: Identifies the path (optional) and name of the function. If you specify an absolute path, you must append the file extension of the library file. If you do not specify the file extension, you can port and use the library on several platforms, however, you might need to add the location of the library to the LD_LIBRARY_PATH or LIBPATH (Linux and UNIX) or PATH (Windows) environment variable.
– !C‑routine‑name: Identifies the entry point name of the C routine (procedure) to be invoked. The exclamation point (!) serves as a delimiter between the library name and the routine name. For example, '!proc8' would direct solidDB to look for the library in the location specified by [path]library‑name, and to use entry point proc8 within that library.
Notes
▪ On Windows 32-bit systems, use the calling convention _cdecl for calling the C routines. This allows the checking and handling of varying argument counts. Also, ensure that your DLL exports the routines you want to call: for example, include the routines in the corresponding .def file in your DLL project.
▪ The library (and the procedure within the library) does not need to exist when the CREATE PROCEDURE statement is executed. However, when the procedure is called, the library and procedure within the library must exist and be accessible.
Restrictions
▪ The call interface of the external functions is assumed to be non-fenced and thread-safe.
▪ All procedures are assumed non-deterministic; the procedure depends on some state values that affect results.
▪ You should not start new threads that execute in parallel with the called procedure; unlike an ODBC handle, the provided connection handle is not multithread safe. If new threads are launched, they cannot access the connection concurrently or after the original external procedure has returned.
Return values
▪ SQL_ERROR
▪ SQL_SUCCESS
Examples
Procedure that uses the RETURN statement
The following example uses the explicit RETURN statement to return multiple rows, one at a time.
"create procedure return_tables returns (name varchar) begin exec sql execdirect create table table_name (lname char (20)); exec sql whenever sqlerror rollback, abort; exec sql prepare c1 select table_name from sys_tables; exec sql execute c1 into (name); while sqlsuccess loop exec sql fetch c1; if not sqlsuccess then leave; end if return row; end loop; exec sql close c1; exec sql drop c1; end";
Procedure that uses the EXECDIRECT clause
"CREATE PROCEDURE p BEGIN DECLARE host_x INT; DECLARE host_y INT; -- Examples of execdirect without a cursor. Here we create a -- table and insert a row into that table. EXEC SQL EXECDIRECT create table foo (x int, y int); EXEC SQL EXECDIRECT insert into foo(x, y) values (1, 2);
SET host_x = 1;
-- Example of execdirect with cursor name. -- In this example, "c1" is the cursor name; "host_x" is the -- variable whose value will be substituted for the "?"; -- "host_y" is the variable into which we will store the value -- of the column y (when we fetch it). -- Note: although you do not need a "prepare" statement, you -- still need close/drop.
EXEC SQL c1 USING(host_x) INTO(host_y) EXECDIRECT SELECT y from foo where x=?; EXEC SQL FETCH c1; EXEC SQL CLOSE c1; EXEC SQL DROP c1; END";
Procedures that uses CURSORNAME() function
The following example shows the usage of the CURSORNAME() pseudo-function. This shows only part of the body of a stored procedure, not a complete stored procedure.
-- Declare a variable that will hold a unique string that we can -- use as a cursor name. DECLARE autoname VARCHAR ; Autoname := GET_UNIQUE_STRING('CUR_') ; EXEC SQL PREPARE curs_name CURSORNAME(autoname) SELECT * FROM TABLES; EXEC SQL EXECUTE curs_name USING(...) INTO(...); EXEC SQL FETCH curs_name; EXEC SQL CLOSE curs_name; EXEC SQL DROP curs_name;
Procedure that uses GET_UNIQUE_STRING and CURSORNAME
The following example uses the GET_UNIQUE_STRING and CURSORNAME functions in a recursive stored procedure.
The following stored procedure demonstrates the use of these two functions in a recursive procedure. The cursor name curs1 appears to be hardcoded, but in fact it has been mapped to the dynamically generated name.
-- Demonstrate GET_UNIQUE_STRING and CURSORNAME functions in a -- recursive stored procedure. -- Given a number N greater than or equal to 1, this procedure -- returns the sum of the numbers 1 - N. (This can also be done -- in a loop, but the purpose of the example is to show the use -- of the CURSORNAME function in a recursive procedure.)
The following example demonstrates how to use the GET_UNIQUE_STRING() function to generate unique message names from within a recursive stored procedure.
CREATE TABLE table1 (i int, beginMsg VARCHAR, endMsg VARCHAR); -- This is a simplified example of recursion. -- Note that the messages that are composes are not actually -- used! This is not a true example of synchronization; it is -- only an example of generating unique message names. The -- "count" parameter is the number of times that you want this -- function to call itself (not including the initial call). "CREATE PROCEDURE repeater(count INT)
EXEC SQL c1 USING (count, MsgBeginStr, MsgEndStr) EXECDIRECT INSERT INTO table1 (i, beginMsg, endMsg) VALUES (?,?,?); EXEC SQL CLOSE c1; EXEC SQL DROP c1;
-- After you have composed the SQL statement as a string, -- you can execute it one of two ways: -- 1) by using the EXECDIRECT feature or -- 2) by preparing and executing the SQL statement. -- In this example, we use EXECDIRECT.
EXEC SQL EXECDIRECT MsgBeginStr; EXEC SQL EXECDIRECT MsgEndStr; -- Do something useful here. -- The recursive portion of the function. IF (count > 1) THEN SET count = count - 1; -- Note that we can also use our unique name as a cursor name, -- for example: EXEC SQL Autoname USING (count) EXECDIRECT CALL repeater(?); EXEC SQL CLOSE Autoname; EXEC SQL DROP Autoname; END IF RETURN; END";
CALL repeater(3); -- Show the message names that we composed. SELECT * FROM table1;
The output from this SELECT statement would look similar to the following example:
I BEGINMSG ENDMSG -- -------------------- ------------------ 1 MESSAGE MSG_019 BEGIN MESSAGE MSG_019 END 2 MESSAGE MSG_020 BEGIN MESSAGE MSG_020 END 3 MESSAGE MSG_021 BEGIN MESSAGE MSG_021 END
External procedure
The following example registers an external stored procedure EXTPROCEXAMPLE1.
CREATE PROCEDURE EXTPROCEXAMPLE1(IN P1 INTEGER, INOUT P2 INTEGER, OUT P3 CHAR(10)) READS SQL DATA LANGUAGE C EXTERNAL NAME 'examplelib1!extprocexample1'