For the full syntax of the CREATE PROCEDURE statement, see CREATE PROCEDURE.
You can use stored procedures to return a result set table with several rows of data in separate columns. This is a solidDB proprietary method to return data and it is performed by using the RETURNS structure.
When you use the RETURNS structure, you must separately declare result set column names for the output data rows. There can be any number of result set column names. The result set column names are declared in the RETURNS section of the procedure definition.
By default, the procedure returns only one row of data that contains the values as they were at the moment when the stored procedure was run or was forced to exit. However, it is also possible to return result sets from a procedure by using the following syntax:
RETURN ROW;
Every RETURN ROW instruction adds a new row into the returned result set where column values are the current values of the result set column names.
For example, the following statement creates a procedure that has two input parameters and two result set column names for output rows:
"CREATE PROCEDURE phonebook_search (IN first_name VARCHAR, last_name VARCHAR) RETURNS (phone_nr NUMERIC, city VARCHAR) BEGIN -- procedure_body END";
This procedure should be called by using two input parameters of data type VARCHAR. The procedure returns an output table consisting of two columns named PHONE_NR of type NUMERIC and CITY of type VARCHAR, for example:
CALL phonebook_search ('JOHN','DOE');
The result is returned in the following format (after the procedure body is coded):
PHONE_NR CITY 3433555 NEW YORK 2345226 LOS ANGELES
The following statement creates a calculator procedure:
"CREATE PROCEDURE calc(i1 FLOAT, op CHAR(1), i2 FLOAT) RETURNS (calcresult FLOAT) BEGIN DECLARE i INTEGER;
IF op = '+' THEN calcresult := i1 + i2; ELSEIF op = '-' THEN calcresult := i1 - i2; ELSEIF op = '*' THEN calcresult := i1 * i2; ELSEIF op = '/' THEN calcresult := i1 / i2; ELSE calcresult := 'Error: illegal op'; END IF END";
You can test the calculator by using the following statement:
CALL calc(1,'/',3);
With the RETURNS structure, SELECT statements can also be wrapped into database procedures. The following statement creates a procedure that uses a SELECT statement to list the backups that were created from the database:
"CREATE PROCEDURE show_backups RETURNS (backup_number VARCHAR, date_created VARCHAR) BEGIN -- First set action for failing statements. EXEC SQL WHENEVER SQLERROR ROLLBACK, ABORT;
-- Prepare and execute the select statement EXEC SQL PREPARE sel_cursor SELECT REPLACE(property, 'backup ', ''), SUBSTRING(value_str, 1, 19) FROM SYS_INFO WHERE property LIKE 'backup %'; EXEC SQL EXECUTE sel_cursor INTO (backup_number, date_created);
-- Fetch first row; EXEC SQL FETCH sel_cursor; -- Loop until end of table WHILE SQLSUCCESS LOOP -- Return the fetched row RETURN ROW; -- Fetch next EXEC SQL FETCH sel_cursor; END LOOP; END";