Working with Cursors and Dynamic SQL

Objective

After completing this lesson, you will be able to work with cursors and dynamic SQL.

Cursors and Dynamic SQL

Because the SQL language is broad and has changed over its lifetime, it will inevitably include elements that, while valid, are not necessarily useful or recommended except in edge cases. Particular care must be taken when using such features, as they may lead to performance issues or other undesired effects.

Working with Cursors

Cursors are used to fetch single rows from the result set returned by a query. When a cursor is declared, it is bound to the query. It is possible to parameterize the cursor query.

However, recall that SQL is designed to work on sets, not individual rows. While it is sometimes conceptually easier to construct a given query by repeatedly fetching single rows and performing actions on them (as opposed to finding a set-oriented solution, which can sometimes be tricky), such an approach is almost always going to cost orders of magnitude more and take far longer than an equivalent set-oriented query.

Cursors can be defined either after the signature of the procedure and before the procedure’s body, or at the beginning of a block with the DECLARE token. The cursor is defined with a name, optionally a list of parameters, and an SQL SELECT statement.

The cursor provides the functionality to iterate through a query result row by row. Starting in SAP HANA 2.0 SPS03, an updatable cursor is available, which helps to change a record directly on the row to which the cursor is currently pointing.

Note

Avoid using cursors when it is possible to express the same logic with SQL. Cursors cannot be optimized the same way SQL can.

Dynamic SQL

Dynamic SQL bypasses the usual order of execution of SQL operations. When a block of SQL is submitted, it is parsed, optimized, and a plan constructed for the entire block before executing any single statement. In certain circumstances, this can lead to statement blocks that cannot be executed as written.

For example, imagine a block of SQL with 23 statements in it; in statement #7 a table, PRELIMRESULTS, is created, and populated with data in statements 9-12. In statement #16 a query is run against PRELIMRESULTS. Under standard rules, this block will fail to run – when parsing the block (prior to optimization, plan generation, and finally execution) the PRELIMRESULTS table does not yet exist, so statement #16 will fail on the name existence check. Since the entire block of code cannot parse, none of it is optimized, no plan is generated, and execution does not take place, even though the PRELIMRESULTS table would have existed by the time statement #16 was run.

Note

The reason for this is that SQL optimization depends on performing statistical analyses of tables referenced – size, distribution of data values, etc – to determine the best method of access. PRELIMRESULTS would exist prior to statement #16 running, but determining an access method must happen in the optimize stage, when it does not yet exist. Because the costs of a bad plan can turn a subsecond execution into one running for hours or even days, trying to construct a plan based on an unknown table that doesn't yet exist is seen as a worse choice than not executing in the first place.

The preferred approach to this problem would be to create the PRELIMRESULTS table outside of the block, so that it exists at parse/optimize/plan time. If it is possible to preload PRELIMRESULTS with sample data that is statistically similar to that which is expected in statements 9-12 and replace the dummy data with the real results, that would also be helpful.

But it would also be possible to change the parse/optimize/plan approach by skipping statement #16 until just before it is time to execute it: don't parse it, optimize it, or construct a plan for it until statements 1-15 have finished execution.

In other words, dynamic SQL allows you to construct an SQL statement during the execution time of a procedure.

It allows you to use variables.
It provides more flexibility when creating SQL statements.
There are two commands and one function particularly relevant to Dynamic SQL: EXEC, EXECUTE IMMEDIATE, and APPLY_FILTER.

The EXEC command executes the SQL statement <sql-statement> passed in a string argument. EXEC does not return any result set if <sql_statement> is a SELECT statement. You have to use EXECUTE IMMEDIATE for that purpose.

If the query returns a single row, you can assign the value of each column to a scalar variable by using the INTO clause.

EXECUTE IMMEDIATE executes the SQL statement passed in a string argument. The results of queries executed with EXECUTE IMMEDIATE are appended to the procedures result iterator.

The APPLY_FILTER function applies a dynamic filter on a table or table variable. Logically, it can be considered a partial dynamic SQL statement.

Some of the disadvantages of the dynamic SQL are:

Opportunities for optimizations are limited.
Statement is potentially recompiled every time the statement is executed.
Cannot use SQLScript variables in the SQL statement.
Cannot bind the result of a dynamic SQL statement to an SQLScript variable.
Must be very careful to avoid SQL injection bugs that might harm the integrity or security of the database.

This last point is the most important. Because dynamic SQL will run any string submitted to it if it's a valid SQL statement, bad actors can submit malicious SQL statements that piggyback on legitimate input. If the application that collects input for execution in a dynamic context does not carefully scrub its inputs, arbitrary code execution in your SAP HANA database can occur.

SQLScript includes functions can mitigate the possibility of SQL injection attacks:

ESCAPE_SINGLE_QUOTES- Used for variables containing a SQL string literal
ESCAPE_DOUBLE_QUOTES - Used for variables containing a delimited SQL identifier
IS_SQL_INJECTION_SAFE - Used to check that a variable contains safe simple SQL identifiers

However, given the risks of SQL injection, it is best to avoid the use of dynamic SQL entirely.

Next lesson