SQL Guide : solidDB® SQL statements : CREATE PROCEDURE : Examples of CREATE PROCEDURE statement
  
Examples of CREATE PROCEDURE statement
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.)

"CREATE PROCEDURE Sum1ToN(n INT)
RETURNS (SumSoFar INT)
BEGIN
   DECLARE SumOfRemainingItems INT;
   DECLARE nMinusOne INT;
   DECLARE autoname VARCHAR;

   SumSoFar := 0;
   SumOfRemainingItems := 0;
   nMinusOne := n - 1;

   IF (nMinusOne > 0) THEN
      Autoname := GET_UNIQUE_STRING('CURSOR_NAME_PREFIX_') ;
      EXEC SQL PREPARE curs1 CURSORNAME(autoname)
            CALL Sum1ToN(?);
      EXEC SQL EXECUTE curs1 USING(nMinusOne)
            INTO(SumOfRemainingItems);
      EXEC SQL FETCH curs1;
      EXEC SQL CLOSE curs1;
      EXEC SQL DROP curs1;
   END IF;

   SumSoFar := n + SumOfRemainingItems;
END";
Using EXECDIRECT in CREATE PROCEDURE
CREATE TABLE table1 (x INT, y INT);
INSERT INTO table1 (x, y) VALUES (1, 2);

"CREATE PROCEDURE FOO
RETURNS (r INT)
BEGIN
DECLARE autoname VARCHAR;
Autoname := GET_UNIQUE_STRING('CUR_');
EXEC SQL curs_name INTO(r) CURSORNAME(autoname) EXECDIRECT
   SELECT y FROM TABLE1 WHERE x = 1;
EXEC SQL FETCH curs_name;
EXEC SQL CLOSE curs_name;
EXEC SQL DROP curs_name;
END";

CALL foo();
SELECT * FROM table1;
Creating a unique name for a synchronization message
Creating a unique name for a synchronization message:
DECLARE Autoname VARCHAR;
DECLARE Sqlstr VARCHAR;
Autoname := get_unique_string('MSG_') ;
Sqlstr := 'MESSAGE' + autoname + 'BEGIN';
EXEC SQL EXECDIRECT Sqlstr;
...
Sqlstr := 'MESSAGE' + autoname + 'FORWARD';
EXEC SQL EXECDIRECT Sqlstr;
Using GET_UNIQUE_STRING
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)

BEGIN

DECLARE Autoname VARCHAR; DECLARE MsgBeginStr VARCHAR;
DECLARE MsgEndStr VARCHAR;

Autoname := GET_UNIQUE_STRING('MSG_');
MsgBeginStr := 'MESSAGE ' + Autoname + ' BEGIN';
MsgEndStr := 'MESSAGE ' + Autoname + ' END';

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
See also
CREATE PROCEDURE