The examples in this section illustrate typical CREATE PROCEDURE statements and clauses.
CREATE PROCEDURE
"create procedure test2(tableid integer) returns (cnt integer) begin exec sql prepare c1 select count(*) from sys_tables where id > ?; exec sql execute c1 using (tableid) into (cnt); exec sql fetch c1; exec sql close c1; exec sql drop c1; end";
Using the explicit RETURN statement
This 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";
Using EXECDIRECT
-- This example shows how to use "execdirect".
"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";
Using CURSORNAME
This 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;
Using GET_UNIQUE_STRING and CURSORNAME
This example uses the GET_UNIQUE_STRING and CURSORNAME functions in a recursive stored procedure.
The stored procedure below 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.)
This 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;
-- Once 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, -- as shown below. 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:
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