NoSQL Zone is brought to you in partnership with:

Seth is the CTO at NuoDB. His main areas of focus are on the administration, security and resource management models, automation and the tools that drive these pieces. Seth is a DZone MVB and is not an employee of DZone and has posted 42 posts at DZone. You can read more from them at their website. View Full User Profile

NuoDB 1.2 SQL Stored Procedures

09.02.2013
| 543 views |
  • submit to reddit

Originally authored by Alberto Massari

Introduction

One of the new preview features of the NuoDB NewSQL DBMS 1.2 is the addition of SQL stored procedures to the toolbox that users have at their disposal. Although they don't add any new capability to the database engine, as the same task can be done by client code written in any of the supported languages (C++, Java, .NET, Python, Perl...), a significant part of the SQL community has grown used to write them mainly for performance and security purposes.

The performance advantage can be seen when the procedure is executing several SQL statements: in fact, by running inside the Transaction Engine, the network roundtrips needed to send the statement and retrieve the data are removed. On the other hand, this moves the CPU load associated with the control flow instructions from the client to the database server, albeit this extra load should be a minor one. The security aspect often has to do with how the database is designed: there could be a set of tables implementing a data model that the DBA doesn't want to be freely manipulated, and whose UPDATE privilege is granted to a restricted number of users. In such cases, the only entry point for manipulating these tables is a stored procedure that can validate the data and then perform the multiple INSERT commands that guarantee the consistency of the data model. So, if you are one of the users that feel the need for stored procedures, this is how you can create them in NuoDB:

CREATE PROCEDURE myProc(IN p1 INTEGER, 
                        INOUT p2 VARCHAR(256), 
                        OUT p3 DECIMAL(18,2))
RETURNS rows(col1 INTEGER, col2 VARCHAR(256))
LANGUAGE SQL
SECURITY INVOKER
AS
....
END_PROCEDURE

The default (and only) language that stored procedures can be written in is SQL, so the LANGUAGE SQL modifier is not really needed; but we are working on embedding other high-level languages so that you will be able to turn existing client code into stored procedures. Stay tuned for more announcements! In any case not everyone can create a stored procedure: only users who have been granted the DBA role by the administrator can create them. The newly created procedure can be executed, modified or dropped only by its creator; she has to explicitly GRANT the EXECUTE and/or ALTER privilege to the other users before they can use or modify it.

At this point, whoever has been given the execute privilege on the procedure will be able to access and manipulate the objects referenced by the procedure. That is, unless the procedure is created with the SECURITY INVOKER modifier; in this case at execution time a check is done to ensure that the current user has the needed privileges. The declaration of the procedure defines the contract between the caller and the SQL code located between the AS and the END_PROCEDURE marker. The caller provides a value for each of the parameters marked as IN or INOUT, while the procedure returns values to the caller via the parameters declared as INOUT or OUT plus the in-memory table whose structure is defined by the RETURNS modifier. Both parameters and return table are optional, so it's possible to declare a procedure that doesn't accept parameters and doesn't return any value. Taking this procedure as an example, a Java client could invoke it using this fragment of code:

// prepare the EXECUTE statement
CallableStatement cStmt = 
     connection.prepareCall("EXECUTE myProc(?, ?, ?)");
// set a value for the IN parameter
cStmt.setInt(1, 10);
// set a value for the INOUT parameter
cStmt.setInt(2, 10);
// register the INOUT parameter to retrieve its changed value
cStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
// register the OUT parameter to retrieve its changed value
cStmt.registerOutParameter(3, java.sql.Types.DECIMAL);

ResultSet resultSet = cStmt.executeQuery();
while(resultSet.next()) {
    int col1 = resultSet.getInt("col1");
    String col2 = resultSet.getString("col2");
}

// beyond iterating over the returned result set, 
// get the changed values
String p2 = cStmt.getString(2);
int p3 = cStmt.getInt(3);

A C# example could instead look like this:

NuoDbCommand cmd = new NuoDbCommand("myProc", connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Prepare();
cmd.Parameters["p1"].Value = 10;
cmd.Parameters["p2"].Value = 10;
using (DbDataReader reader = cmd.ExecuteReader())
{
    while (reader.Read())
    {
        int col1 = reader["col1"];
        string col2 = reader["col2"]);
    }
}
string p2 = cmd.Parameters["p2"].Value;
int p3 = cmd.Parameters["p3"].Value;

Variables

Having examined the interface of the procedure, let's focus on what the procedure can manipulate: while normal statements specified by a remote client can only work on the fields of the referenced tables, stored procedures can also make use of variables, referencing them in any place where a literal constant can be used. A set of pre-defined variables are the parameters declared in the interface of the procedure, but other ones can be declared throughout the body of the procedure simply by means of the VAR instruction:

VAR v1;
VAR v2 INTEGER;
VAR v3 = 'initial value';
VAR v4, v5, v6 INT = 0, v7;

If the variable declaration doesn't specify a data type, no type checking will occur when assigning a value to it.

Data manipulation

The bulk of the SQL statements are the same DML instructions UPDATE, INSERT, DELETE and EXECUTE/CALL that a remote client can specify. As for the SELECT statement, our choice was to allow it in two different forms: as the right side of an assignment operator or as a cursor of a FOR loop.

VAR v1, v2, v3;
v1, v2 = SELECT col1, col2 FROM user.table WHERE col3 = p1;
...
FOR SELECT col1 FROM user.table;
    v3 = col1;
END_FOR;

In the assignment operator the variables listed on the left hand side of the equal sign will be assigned in order to each column returned by the SELECT statement on the other side; v1 will get the value of col1, and v2 will get col2. If the SELECT doesn't identify any row, both variables will contain a NULL value. If, on the other hand, the SELECT identifies more than one row, a runtime error will occur. In this scenario, the FOR loop can be used; it will fetch the rows one by one and for each row it will execute the statements listed until the END_FOR command. If these statements reference one of the fields listed in the SELECT statement, they will have access to the corresponding column of the current row. The procedure statements have also access to a new table that is not backed by any storage: it's the table declared in the RETURNS modifier, that will be returned to the caller at the end of the procedure. This table can be filled by a standard INSERT command.

INSERT INTO rows SELECT col1, col2 FROM user.table WHERE col3 = p1;
INSERT INTO rows VALUES (v1, v2);

Completing the description of the FOR loop, it can also be used to iterate over the cursor returned by other two instructions: EXECUTE and INSERT. In the FOR EXECUTE case, the loop will iterate over the rows of the table that the invoked procedure declares in its RETURNS modifier; in the FOR INSERT case, the loop will iterate over the in-memory table that contains the values of the IDs that the INSERT command generated.

FOR INSERT INTO user.table VALUES (v1);
    v3 = id;
END_FOR;

Controlling the flow

The IF command can be used to control the flow of instructions; the test condition can be any valid boolean test:

IF (v1 > v2 + 4)
...
ELSE
...
END_IF;

IF (CHARACTER_LENGTH(p2) < v1)
...
END_IF;

With the WHILE command, the same set of instructions can be executed multiple times. Just be sure that the exit condition can be satisfied sooner or later!

VAR index INTEGER = 0;
WHILE (index < 100)
...
    index = index + 1;
END_WHILE;

Early exit

The statements inside a WHILE or a FOR loop can include the BREAK statement, to immediately jump to the first instruction after the end of the closest loop without waiting for the natural exit condition. The RETURN command can instead be placed in any position inside the procedure body, and it will immediately end the procedure with a success code. A THROW command will instead make the procedure fail, rolling back any DML operation that the procedure has previously invoked and reporting to the caller the error message that is specified as the argument of the THROW command.

IF (p1 < 0)
    THROW 'index out of bounds';
END_IF;

IF (p1 = 0)
    RETURN;
END_IF;

VAR index INTEGER = 0, maxLoops INTEGER = 100;
WHILE (index < p1)
    IF (index > maxLoops)
        BREAK;
    END_IF;
    index = index +1;
END_WHILE;

Exception handling

If an instruction inside the procedure body fails (e.g. an INSERT that inserts a duplicate value in a primary key, or an assignment that specifies a value outside the valid range of the target datatype) the entire procedure fails and performs a rollback of the current transaction. If such an error is considered recoverable by the writer of the procedure, she can wrap the instruction inside a TRY/CATCH block and perform the needed recovery operations. If, by inspecting the error_message string, the failure is not what was expected, it can be re-thrown via a THROW instruction.

/* example code, don't try this at home! */
VAR tmpID INT = 1;
WHILE (TRUE)
    TRY
        INSERT INTO user.table (ID, COL1) VALUES (tmpID, v1);
        BREAK;
    CATCH(error_message)
        IF (error_message CONTAINING 'duplicate')
            tmpID = tmpID + 1;
        ELSE
            THROW error_message;
        END_IF;
    END_TRY;
END_WHILE;

Conclusions

To put the new feature under test, we have modified the DBT-2 benchmark code (available at https://github.com/nuodb/dbt2) to include also a version that makes use of stored procedures. Here is a sample of how the new_order procedure looks:

CREATE PROCEDURE new_order(tmp_w_id INT, tmp_d_id INT, tmp_c_id INT,
                           tmp_o_all_local INT, tmp_o_ol_cnt INT,
                           ol_i_id1 INT, ol_supply_w_id1 INT, ol_quantity1 INT,
                           ol_i_id2 INT, ol_supply_w_id2 INT, ol_quantity2 INT,
                           ol_i_id3 INT, ol_supply_w_id3 INT, ol_quantity3 INT,
                           ol_i_id4 INT, ol_supply_w_id4 INT, ol_quantity4 INT,
                           ol_i_id5 INT, ol_supply_w_id5 INT, ol_quantity5 INT,
                           ol_i_id6 INT, ol_supply_w_id6 INT, ol_quantity6 INT,
                           ol_i_id7 INT, ol_supply_w_id7 INT, ol_quantity7 INT,
                           ol_i_id8 INT, ol_supply_w_id8 INT, ol_quantity8 INT,
                           ol_i_id9 INT, ol_supply_w_id9 INT, ol_quantity9 INT,
                           ol_i_id10 INT, ol_supply_w_id10 INT, ol_quantity10 INT,
                           ol_i_id11 INT, ol_supply_w_id11 INT, ol_quantity11 INT,
                           ol_i_id12 INT, ol_supply_w_id12 INT, ol_quantity12 INT,
                           ol_i_id13 INT, ol_supply_w_id13 INT, ol_quantity13 INT,
                           ol_i_id14 INT, ol_supply_w_id14 INT, ol_quantity14 INT,
                           ol_i_id15 INT, ol_supply_w_id15 INT, ol_quantity15 INT)
                              
AS

  VAR out_c_credit, out_c_last, out_d_next_o_id;
  VAR out_w_tax, out_d_tax, out_c_discount;
  VAR tmp_i_name, tmp_i_data, tmp_i_id, tmp_i_price;
  VAR tmp_ol_supply_w_id, tmp_ol_quantity, tmp_ol_amount;
  VAR tmp_s_quantity, tmp_total_amount;
  VAR o_id = 0;

  out_w_tax = SELECT w_tax
              FROM warehouse
              WHERE w_id = tmp_w_id;

  out_d_tax, out_d_next_o_id = SELECT d_tax, d_next_o_id
                               FROM district
                               WHERE d_w_id = tmp_w_id
                                 AND d_id = tmp_d_id;

  o_id=out_d_next_o_id;

  UPDATE district
  SET d_next_o_id = d_next_o_id + 1
  WHERE d_w_id = tmp_w_id
    AND d_id = tmp_d_id;

  out_c_discount, out_c_last, out_c_credit = SELECT c_discount, c_last, c_credit
                                             FROM customer
                                             WHERE c_w_id = tmp_w_id
                                               AND c_d_id = tmp_d_id
                                               AND c_id = tmp_c_id;

  INSERT INTO new_order (no_o_id, no_d_id, no_w_id)
  VALUES (out_d_next_o_id, tmp_d_id, tmp_w_id);

  INSERT INTO orders (o_id, o_d_id, o_w_id, o_c_id, o_entry_d, o_carrier_id, 
                      o_ol_cnt, o_all_local)
  VALUES (out_d_next_o_id, tmp_d_id, tmp_w_id, tmp_c_id, 
          now(), NULL, tmp_o_ol_cnt, tmp_o_all_local);

  tmp_total_amount = 0;

  VAR counter = 0;
  WHILE (counter < 15)
    IF (tmp_o_ol_cnt > counter )

      tmp_i_id = CASE counter
                    WHEN 0 THEN ol_i_id1
                    WHEN 1 THEN ol_i_id2
                    WHEN 2 THEN ol_i_id3
                    WHEN 3 THEN ol_i_id4
                    WHEN 4 THEN ol_i_id5
                    WHEN 5 THEN ol_i_id6
                    WHEN 6 THEN ol_i_id7
                    WHEN 7 THEN ol_i_id8
                    WHEN 8 THEN ol_i_id9
                    WHEN 9 THEN ol_i_id10
                    WHEN 10 THEN ol_i_id11
                    WHEN 11 THEN ol_i_id12
                    WHEN 12 THEN ol_i_id13
                    WHEN 13 THEN ol_i_id14
                    WHEN 14 THEN ol_i_id15
                 END;
      tmp_ol_supply_w_id = CASE counter
                    WHEN 0 THEN ol_supply_w_id1
                    WHEN 1 THEN ol_supply_w_id2
                    WHEN 2 THEN ol_supply_w_id3
                    WHEN 3 THEN ol_supply_w_id4
                    WHEN 4 THEN ol_supply_w_id5
                    WHEN 5 THEN ol_supply_w_id6
                    WHEN 6 THEN ol_supply_w_id7
                    WHEN 7 THEN ol_supply_w_id8
                    WHEN 8 THEN ol_supply_w_id9
                    WHEN 9 THEN ol_supply_w_id10
                    WHEN 10 THEN ol_supply_w_id11
                    WHEN 11 THEN ol_supply_w_id12
                    WHEN 12 THEN ol_supply_w_id13
                    WHEN 13 THEN ol_supply_w_id14
                    WHEN 14 THEN ol_supply_w_id15
                 END;
      tmp_ol_quantity = CASE counter
                    WHEN 0 THEN ol_quantity1
                    WHEN 1 THEN ol_quantity2
                    WHEN 2 THEN ol_quantity3
                    WHEN 3 THEN ol_quantity4
                    WHEN 4 THEN ol_quantity5
                    WHEN 5 THEN ol_quantity6
                    WHEN 6 THEN ol_quantity7
                    WHEN 7 THEN ol_quantity8
                    WHEN 8 THEN ol_quantity9
                    WHEN 9 THEN ol_quantity10
                    WHEN 10 THEN ol_quantity11
                    WHEN 11 THEN ol_quantity12
                    WHEN 12 THEN ol_quantity13
                    WHEN 13 THEN ol_quantity14
                    WHEN 14 THEN ol_quantity15
                 END;

      tmp_i_price, tmp_i_name, tmp_i_data = SELECT i_price, i_name, i_data
                                            FROM item
                                            WHERE i_id = tmp_i_id;

      IF (tmp_i_price > 0 )
          tmp_ol_amount = tmp_i_price * tmp_ol_quantity;

          call new_order_2(tmp_w_id, tmp_d_id, tmp_i_id, tmp_ol_quantity, tmp_i_price,
                           tmp_i_name, tmp_i_data, out_d_next_o_id, tmp_ol_amount,
                           tmp_ol_supply_w_id, counter + 1, tmp_s_quantity);

          tmp_total_amount = tmp_total_amount + tmp_ol_amount;
      END_IF;
    ELSE
      BREAK;
    END_IF;
    counter = counter + 1;
  END_WHILE;

END_PROCEDURE


Published at DZone with permission of Seth Proctor, author and DZone MVB. (source)

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)