This is an automated email from the ASF dual-hosted git repository.
dtenedor pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/spark.git
The following commit(s) were added to refs/heads/master by this push:
new a1577253d88d [SPARK-55011][DOCS] CURSORs docs
a1577253d88d is described below
commit a1577253d88d3cc50900e00b1f489f6cc7659502
Author: Serge Rielau <[email protected]>
AuthorDate: Thu Jan 29 11:57:28 2026 -0800
[SPARK-55011][DOCS] CURSORs docs
### What changes were proposed in this pull request?
Document the cursors feature introduced in
[SPARK-54759](https://issues.apache.org/jira/browse/SPARK-54759)
### Why are the changes needed?
Docs are good. We like docs.
### Does this PR introduce _any_ user-facing change?
No
### How was this patch tested?
<img width="1083" height="1037" alt="Screenshot 2026-01-12 at 9 21 38 AM"
src="https://github.com/user-attachments/assets/5ee74b4e-27d3-4a6b-b2f7-f80cd97724cb";
/>
<img width="1100" height="1044" alt="Screenshot 2026-01-12 at 9 21 31 AM"
src="https://github.com/user-attachments/assets/6024bf48-7825-4ea9-9be4-3855fbaf9faf";
/>
<img width="1010" height="957" alt="Screenshot 2026-01-12 at 9 21 18 AM"
src="https://github.com/user-attachments/assets/f0757dfc-120b-4cac-9f16-9c22b0c32a2f";
/>
### Was this patch authored or co-authored using generative AI tooling?
Claude
Closes #53768 from srielau/cursors-docs.
Authored-by: Serge Rielau <[email protected]>
Signed-off-by: Daniel Tenedorio <[email protected]>
---
docs/control-flow/close-stmt.md | 147 ++++++++++++++++++++++++++++
docs/control-flow/compound-stmt.md | 59 ++++++++++-
docs/control-flow/fetch-stmt.md | 194 +++++++++++++++++++++++++++++++++++++
docs/control-flow/open-stmt.md | 162 +++++++++++++++++++++++++++++++
docs/sql-ref-scripting.md | 26 ++++-
5 files changed, 578 insertions(+), 10 deletions(-)
diff --git a/docs/control-flow/close-stmt.md b/docs/control-flow/close-stmt.md
new file mode 100644
index 000000000000..e364ffb6c205
--- /dev/null
+++ b/docs/control-flow/close-stmt.md
@@ -0,0 +1,147 @@
+---
+layout: global
+title: CLOSE statement
+displayTitle: CLOSE statement
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+Closes an open cursor and releases its resources.
+
+The `CLOSE` statement closes a cursor that was previously opened with `OPEN`,
freeing the memory and resources associated with its result set. After closing,
the cursor can be reopened with `OPEN` to execute the query again with fresh
parameter bindings.
+
+## Syntax
+
+```
+CLOSE cursor_name
+```
+
+## Parameters
+
+- **`cursor_name`**
+
+ The name of an open cursor. The cursor can be optionally qualified with a
compound statement label (e.g., `outer_label.my_cursor`).
+
+## Examples
+
+```SQL
+-- Basic cursor lifecycle
+> BEGIN
+ DECLARE x INT;
+ DECLARE my_cursor CURSOR FOR SELECT id FROM range(3);
+
+ OPEN my_cursor;
+ FETCH my_cursor INTO x;
+ VALUES (x);
+ CLOSE my_cursor;
+ END;
+0
+
+-- Close cursor in handler
+> BEGIN
+ DECLARE x INT;
+ DECLARE my_cursor CURSOR FOR SELECT id FROM range(2);
+
+ DECLARE EXIT HANDLER FOR NOT FOUND
+ BEGIN
+ CLOSE my_cursor;
+ VALUES ('Cursor closed on completion');
+ END;
+
+ OPEN my_cursor;
+ REPEAT
+ FETCH my_cursor INTO x;
+ UNTIL false END REPEAT;
+ END;
+Cursor closed on completion
+
+-- Reopen cursor with different parameters
+> BEGIN
+ DECLARE x INT;
+ DECLARE param_cursor CURSOR FOR SELECT id FROM range(10) WHERE id = ?;
+
+ OPEN param_cursor USING 3;
+ FETCH param_cursor INTO x;
+ VALUES ('First open:', x);
+ CLOSE param_cursor;
+
+ OPEN param_cursor USING 7;
+ FETCH param_cursor INTO x;
+ VALUES ('Second open:', x);
+ CLOSE param_cursor;
+ END;
+First open:|3
+Second open:|7
+
+-- Qualified cursor name with label
+> BEGIN
+ outer_lbl: BEGIN
+ DECLARE outer_cur CURSOR FOR SELECT id FROM range(3);
+ DECLARE x INT;
+
+ OPEN outer_cur;
+ FETCH outer_cur INTO x;
+
+ inner_lbl: BEGIN
+ FETCH outer_lbl.outer_cur INTO x;
+ END;
+
+ CLOSE outer_lbl.outer_cur;
+ VALUES ('Closed from outer scope');
+ END;
+ END;
+Closed from outer scope
+
+-- Processing all rows before close
+> BEGIN
+ DECLARE x INT;
+ DECLARE done BOOLEAN DEFAULT false;
+ DECLARE results STRING DEFAULT '';
+ DECLARE my_cursor CURSOR FOR SELECT id FROM range(5);
+
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
+
+ OPEN my_cursor;
+ REPEAT
+ FETCH my_cursor INTO x;
+ IF NOT done THEN
+ SET results = results || CAST(x AS STRING) || ',';
+ END IF;
+ UNTIL done END REPEAT;
+ CLOSE my_cursor;
+
+ VALUES (results);
+ END;
+0,1,2,3,4,
+```
+
+## Notes
+
+- The cursor must be open before calling `CLOSE`. Attempting to close a cursor
that is not open raises a `CURSOR_NOT_OPEN` error.
+- After closing, the cursor can be reopened with `OPEN`. This is useful when
you want to re-execute the cursor's query with different parameter bindings.
+- Cursors are automatically closed in the following scenarios:
+ - When the compound statement that declares them exits normally
+ - When an `EXIT` handler is triggered (all cursors in the compound statement
and nested compounds are closed)
+ - When the compound statement exits due to an unhandled exception
+- It is good practice to explicitly close cursors when they are no longer
needed, rather than relying on implicit closure. This frees resources earlier
and makes the code's intent clearer.
+- Closing a cursor does not affect the cursor declaration. The cursor name
remains in scope and can be reopened.
+
+## Related articles
+
+- [Compound Statement](../control-flow/compound-stmt.html)
+- [OPEN Statement](../control-flow/open-stmt.html)
+- [FETCH Statement](../control-flow/fetch-stmt.html)
+- [SQL Scripting](../sql-ref-scripting.html)
diff --git a/docs/control-flow/compound-stmt.md
b/docs/control-flow/compound-stmt.md
index d34e70648de4..4aab34a05676 100644
--- a/docs/control-flow/compound-stmt.md
+++ b/docs/control-flow/compound-stmt.md
@@ -27,6 +27,7 @@ Implements a SQL Script block that can contain a sequence of
SQL statements, con
[ label : ]
BEGIN
[ { declare_variable | declare_condition } ; [...] ]
+ [ declare_cursor ; [...] ]
[ declare_handler ; [...] ]
[ SQL_statement ; [...] ]
END [ label ]
@@ -37,11 +38,15 @@ declare_variable
declare_condition
DECLARE condition_name CONDITION [ FOR SQLSTATE [ VALUE ] sqlstate ]
+declare_cursor
+ DECLARE cursor_name [ ASENSITIVE | INSENSITIVE ] CURSOR
+ FOR query
+
declare_handler
DECLARE handler_type HANDLER FOR condition_values handler_action
handler_type
- EXIT
+ { EXIT | CONTINUE }
condition_values
{ { SQLSTATE [ VALUE ] sqlstate | condition_name } [, ...] |
@@ -89,7 +94,23 @@ condition_values
- **`sqlstate`**
- A `STRING` literal of 5 alphanumeric characters (case insensitive)
consisting of A-Z and 0..9. The SQLSTATE must not start with ‘00’, ‘01’, or
‘XX’. Any SQLSTATE starting with ‘02’ will be caught by the predefined NOT
FOUND exception as well. If not specified, the SQLSTATE is ‘45000’.
+ A `STRING` literal of 5 alphanumeric characters (case insensitive)
consisting of A-Z and 0..9. The SQLSTATE must not start with `'00'`, `'01'`, or
`'XX'`. Any SQLSTATE starting with `'02'` will be caught by the predefined `NOT
FOUND` handler as well. If not specified, the SQLSTATE is `'45000'`.
+
+- **`declare_cursor`**
+
+ A local cursor declaration for iterating through query results.
+
+ - **`cursor_name`**
+
+ An unqualified name for the cursor. The name must be unique among all
cursors declared in this compound statement. Cursors can be qualified with the
compound statement `label` to disambiguate duplicate names.
+
+ - **`ASENSITIVE`** or **`INSENSITIVE`**
+
+ Optional keywords specifying that once the cursor is opened, the result
set is not affected by DML changes within or outside the session. This is the
default and only supported behavior.
+
+ - **`query`**
+
+ The query that defines the cursor. The query is not executed until the
cursor is opened with `OPEN cursor_name`. At open time, any variable references
and parameter markers in the query are bound to their current values.
- **`declare_handler`**
@@ -99,7 +120,11 @@ condition_values
- **`EXIT`**
- Classifies the handler to exit the compound statement after the
condition is handled.
+ Classifies the handler to exit the compound statement after the
condition is handled. All cursors opened within the compound statement and
nested compound statements are implicitly closed.
+
+ - **`CONTINUE`**
+
+ Classifies the handler to continue execution after the handler
completes. Execution resumes with the statement following the one that raised
the condition.
- **`condition_values`**
@@ -121,7 +146,7 @@ condition_values
- **`NOT FOUND`**
- Applies to any error condition with a SQLSTATE ‘02’ class.
+ Applies to any condition with a SQLSTATE `'02xxx'` class. This includes
the `CURSOR_NO_MORE_ROWS` condition (SQLSTATE `'02000'`) raised when fetching
beyond the end of a cursor's result set.
- **`handler_action`**
@@ -136,7 +161,7 @@ condition_values
## Examples
```SQL
--- A compound statement with local variables, and exit hanlder and a nested
compound.
+-- A compound statement with local variables, an exit handler and a nested
compound.
> BEGIN
DECLARE a INT DEFAULT 1;
DECLARE b INT DEFAULT 5;
@@ -149,11 +174,35 @@ condition_values
VALUES (a);
END;
15
+
+-- A compound statement with a cursor and a CONTINUE handler for iteration.
+> BEGIN
+ DECLARE x INT;
+ DECLARE done BOOLEAN DEFAULT false;
+ DECLARE total INT DEFAULT 0;
+ DECLARE my_cursor CURSOR FOR SELECT id FROM range(5);
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
+
+ OPEN my_cursor;
+ REPEAT
+ FETCH my_cursor INTO x;
+ IF NOT done THEN
+ SET total = total + x;
+ END IF;
+ UNTIL done END REPEAT;
+ CLOSE my_cursor;
+
+ VALUES (total);
+ END;
+10
```
## Related articles
- [SQL Scripting](../sql-ref-scripting.html)
+- [OPEN Statement](../control-flow/open-stmt.html)
+- [FETCH Statement](../control-flow/fetch-stmt.html)
+- [CLOSE Statement](../control-flow/close-stmt.html)
- [CASE Statement](../control-flow/case-stmt.html)
- [IF Statement](../control-flow/if-stmt.html)
- [LOOP Statement](../control-flow/loop-stmt.html)
diff --git a/docs/control-flow/fetch-stmt.md b/docs/control-flow/fetch-stmt.md
new file mode 100644
index 000000000000..67d5ccb2059a
--- /dev/null
+++ b/docs/control-flow/fetch-stmt.md
@@ -0,0 +1,194 @@
+---
+layout: global
+title: FETCH statement
+displayTitle: FETCH statement
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+Fetches the next row from an open cursor into variables.
+
+The `FETCH` statement retrieves one row at a time from the cursor's result set
and assigns column values to the specified variables. If no more rows are
available, the `CURSOR_NO_MORE_ROWS` condition is raised (SQLSTATE `'02000'`).
+
+## Syntax
+
+```
+FETCH [ [ NEXT ] FROM ] cursor_name INTO variable_name [, ...]
+```
+
+## Parameters
+
+- **`cursor_name`**
+
+ The name of an open cursor. The cursor can be optionally qualified with a
compound statement label (e.g., `outer_label.my_cursor`).
+
+- **`NEXT FROM`**
+
+ Optional keywords. `NEXT` and `FROM` are syntactic sugar and do not affect
behavior. Only forward fetching is supported.
+
+- **`variable_name`**
+
+ A local or session variable to receive column values. The number of
variables must match the number of columns in the cursor's result set, with one
exception:
+ - If exactly one variable is specified and it is a `STRUCT` type, and the
cursor returns multiple columns, the column values are assigned to the struct's
fields by position.
+
+ Column data types must be compatible with the target variables (or struct
fields) according to store assignment rules.
+
+## Examples
+
+```SQL
+-- Basic fetch into variables
+> BEGIN
+ DECLARE x INT;
+ DECLARE y STRING;
+ DECLARE my_cursor CURSOR FOR
+ SELECT id, 'row_' || id FROM range(3);
+
+ OPEN my_cursor;
+ FETCH my_cursor INTO x, y;
+ VALUES (x, y);
+ CLOSE my_cursor;
+ END;
+0|row_0
+
+-- Fetch multiple rows with REPEAT loop
+> BEGIN
+ DECLARE x INT;
+ DECLARE done BOOLEAN DEFAULT false;
+ DECLARE total INT DEFAULT 0;
+ DECLARE sum_cursor CURSOR FOR SELECT id FROM range(5);
+
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;
+
+ OPEN sum_cursor;
+ REPEAT
+ FETCH sum_cursor INTO x;
+ IF NOT done THEN
+ SET total = total + x;
+ END IF;
+ UNTIL done END REPEAT;
+ CLOSE sum_cursor;
+
+ VALUES (total);
+ END;
+10
+
+-- Fetch into a struct variable
+> BEGIN
+ DECLARE result STRUCT<id: INT, name: STRING>;
+ DECLARE struct_cursor CURSOR FOR
+ SELECT id, 'name_' || id FROM range(3);
+
+ OPEN struct_cursor;
+ FETCH struct_cursor INTO result;
+ VALUES (result.id, result.name);
+ CLOSE struct_cursor;
+ END;
+0|name_0
+
+-- Using NEXT FROM (optional syntax)
+> BEGIN
+ DECLARE x INT;
+ DECLARE cursor1 CURSOR FOR SELECT id FROM range(3);
+
+ OPEN cursor1;
+ FETCH NEXT FROM cursor1 INTO x;
+ VALUES (x);
+ CLOSE cursor1;
+ END;
+0
+
+-- Qualified cursor name with label
+> BEGIN
+ outer_lbl: BEGIN
+ DECLARE outer_cur CURSOR FOR SELECT id FROM range(5);
+ DECLARE x INT;
+
+ OPEN outer_cur;
+
+ inner_lbl: BEGIN
+ FETCH outer_lbl.outer_cur INTO x;
+ VALUES (x);
+ END;
+
+ CLOSE outer_cur;
+ END;
+ END;
+0
+
+-- Exit handler for NOT FOUND
+> BEGIN
+ DECLARE x INT;
+ DECLARE my_cursor CURSOR FOR SELECT id FROM range(2);
+
+ DECLARE EXIT HANDLER FOR NOT FOUND
+ BEGIN
+ VALUES ('No more rows');
+ END;
+
+ OPEN my_cursor;
+ FETCH my_cursor INTO x;
+ FETCH my_cursor INTO x;
+ FETCH my_cursor INTO x; -- Triggers EXIT handler
+ VALUES ('This will not execute');
+ CLOSE my_cursor;
+ END;
+No more rows
+
+-- Specific CURSOR_NO_MORE_ROWS handler
+> BEGIN
+ DECLARE x INT DEFAULT 0;
+ DECLARE done BOOLEAN DEFAULT false;
+ DECLARE count INT DEFAULT 0;
+ DECLARE my_cursor CURSOR FOR SELECT id FROM range(3);
+
+ DECLARE CONTINUE HANDLER FOR CURSOR_NO_MORE_ROWS SET done = true;
+
+ OPEN my_cursor;
+ WHILE NOT done DO
+ FETCH my_cursor INTO x;
+ IF NOT done THEN
+ SET count = count + 1;
+ END IF;
+ END WHILE;
+ CLOSE my_cursor;
+
+ VALUES (count);
+ END;
+3
+```
+
+## Notes
+
+- The cursor must be opened with `OPEN` before calling `FETCH`. Attempting to
fetch from a closed cursor raises a `CURSOR_NOT_OPEN` error.
+- Each `FETCH` advances the cursor position by one row.
+- When no more rows are available, `FETCH` raises the `CURSOR_NO_MORE_ROWS`
condition:
+ - SQLSTATE: `'02000'`
+ - Error condition: `CURSOR_NO_MORE_ROWS`
+ - This is caught by `NOT FOUND` handlers (which catch all SQLSTATE `'02xxx'`
conditions)
+- If no `CONTINUE HANDLER` or `EXIT HANDLER` is declared for `NOT FOUND`, the
completion condition is silently ignored and execution continues. This allows
scripts to continue after exhausting a cursor.
+- Type compatibility follows store assignment rules:
+ - Implicit casts are applied when possible
+ - Incompatible types raise a type mismatch error
+- Variables can be local variables declared in the compound statement or
session variables created with `DECLARE VARIABLE` at the session level.
+
+## Related articles
+
+- [Compound Statement](../control-flow/compound-stmt.html)
+- [OPEN Statement](../control-flow/open-stmt.html)
+- [CLOSE Statement](../control-flow/close-stmt.html)
+- [WHILE Statement](../control-flow/while-stmt.html)
+- [REPEAT Statement](../control-flow/repeat-stmt.html)
+- [SQL Scripting](../sql-ref-scripting.html)
diff --git a/docs/control-flow/open-stmt.md b/docs/control-flow/open-stmt.md
new file mode 100644
index 000000000000..3de65084cba1
--- /dev/null
+++ b/docs/control-flow/open-stmt.md
@@ -0,0 +1,162 @@
+---
+layout: global
+title: OPEN statement
+displayTitle: OPEN statement
+license: |
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+---
+
+Opens a cursor and executes its query, positioning it before the first row.
+
+The `OPEN` statement executes the query defined in the cursor declaration,
binding any parameter markers if specified. Once opened, the cursor can be used
with `FETCH` to retrieve rows.
+
+## Syntax
+
+```
+OPEN cursor_name [ USING { constant_expr [ AS param_name ] } [, ...] ]
+```
+
+## Parameters
+
+- **`cursor_name`**
+
+ The name of a declared cursor. The cursor can be optionally qualified with a
compound statement label to reference a cursor from an outer scope (e.g.,
`outer_label.my_cursor`).
+
+- **`USING { constant_expr [ AS param_name ] } [, ...]`**
+
+ Optional clause to bind values to parameter markers in the cursor's query.
+
+ - **`constant_expr`**
+
+ A constant expression (literal or variable) to bind to a parameter marker.
The expression must be castable to the type expected by the query.
+
+ - **`AS param_name`**
+
+ An optional alias for the parameter. For named parameter markers
(`:param_name`), this specifies which parameter to bind. If not specified for
positional markers (`?`), parameters are bound by position.
+
+## Examples
+
+```SQL
+-- Open a simple cursor without parameters
+> BEGIN
+ DECLARE total INT;
+ DECLARE my_cursor CURSOR FOR SELECT sum(id) FROM range(10);
+
+ OPEN my_cursor;
+ FETCH my_cursor INTO total;
+ VALUES (total);
+ CLOSE my_cursor;
+ END;
+45
+
+-- Open cursor with positional parameters
+> BEGIN
+ DECLARE total INT;
+ DECLARE param_cursor CURSOR FOR
+ SELECT sum(id) FROM range(100) WHERE id BETWEEN ? AND ?;
+
+ OPEN param_cursor USING 10, 20;
+ FETCH param_cursor INTO total;
+ VALUES (total);
+ CLOSE param_cursor;
+ END;
+165
+
+-- Open cursor with named parameters
+> BEGIN
+ DECLARE min_val INT;
+ DECLARE named_cursor CURSOR FOR
+ SELECT min(id) FROM range(100) WHERE id >= :threshold;
+
+ OPEN named_cursor USING 25 AS threshold;
+ FETCH named_cursor INTO min_val;
+ VALUES (min_val);
+ CLOSE named_cursor;
+ END;
+25
+
+-- Open cursor using variables as parameters
+> BEGIN
+ DECLARE lower INT DEFAULT 5;
+ DECLARE upper INT DEFAULT 15;
+ DECLARE result INT;
+ DECLARE var_cursor CURSOR FOR
+ SELECT count(*) FROM range(100) WHERE id BETWEEN ? AND ?;
+
+ OPEN var_cursor USING lower, upper;
+ FETCH var_cursor INTO result;
+ VALUES (result);
+ CLOSE var_cursor;
+ END;
+11
+
+-- Open cursor with various data types
+> BEGIN
+ DECLARE type_name STRING;
+ DECLARE value_sum INT;
+ DECLARE type_cursor CURSOR FOR
+ SELECT typeof(:p) as type, sum(:p + id) FROM range(3);
+
+ OPEN type_cursor USING 10 AS p;
+ FETCH type_cursor INTO type_name, value_sum;
+ VALUES (type_name, value_sum);
+ CLOSE type_cursor;
+ END;
+INT|33
+
+-- Qualified cursor name with label
+> BEGIN
+ outer_lbl: BEGIN
+ DECLARE outer_cur CURSOR FOR SELECT max(id) FROM range(10);
+ DECLARE max_val INT;
+
+ OPEN outer_cur;
+
+ inner_lbl: BEGIN
+ DECLARE inner_cur CURSOR FOR SELECT min(id) FROM range(5);
+ DECLARE min_val INT;
+
+ OPEN inner_cur;
+ FETCH outer_lbl.outer_cur INTO max_val;
+ FETCH inner_cur INTO min_val;
+ VALUES (max_val, min_val);
+ CLOSE inner_cur;
+ END;
+
+ CLOSE outer_cur;
+ END;
+ END;
+9|0
+```
+
+## Notes
+
+- A cursor can only be opened once. Attempting to open an already-opened
cursor raises a `CURSOR_ALREADY_OPEN` error.
+- Parameter binding behavior matches `EXECUTE IMMEDIATE`:
+ - For positional parameters (`?`), expressions are bound in the order
specified.
+ - For named parameters (`:name`), the `AS param_name` clause specifies the
binding.
+ - All parameter markers in the query must be bound.
+- Variable references in the cursor's query are evaluated at `OPEN` time,
using current variable values.
+- The cursor's result set is materialized at `OPEN` time. Subsequent changes
to variables or tables do not affect the result set.
+- If the cursor's query raises an error during execution, the cursor remains
in a closed state.
+
+## Related articles
+
+- [Compound Statement](../control-flow/compound-stmt.html)
+- [FETCH Statement](../control-flow/fetch-stmt.html)
+- [CLOSE Statement](../control-flow/close-stmt.html)
+- [EXECUTE IMMEDIATE Statement](../sql-ref-syntax-aux-exec-imm.html)
+- [SQL Scripting](../sql-ref-scripting.html)
diff --git a/docs/sql-ref-scripting.md b/docs/sql-ref-scripting.md
index 7d26bd07fed5..60b1768a20b9 100644
--- a/docs/sql-ref-scripting.md
+++ b/docs/sql-ref-scripting.md
@@ -21,7 +21,7 @@ license: |
You can employ powerful procedural logic using SQL/PSM standard-based
scripting syntax.
Any SQL script consists of and starts with a [compound
statement](control-flow/compound-stmt.html) block (`BEGIN ... END`).
-A compound statement starts with a section to declare local variables,
user-defined conditions, and condition handlers, which are used to catch
exceptions.
+A compound statement starts with a section to declare local variables,
cursors, user-defined conditions, and condition handlers, which are used to
catch exceptions.
This is followed by the compound statement body, which consists of:
- Flow control statements include loops over predicate expressions,
[FOR](control-flow/for-stmt.html) loops over query results, conditional logic
such as [IF](control-flow/if-stmt.html) and
[CASE](control-flow/case-stmt.html), and means to break out loops such as
[LEAVE](control-flow/leave-stmt.html) and
[ITERATE](control-flow/iterate-stmt.html).
@@ -30,7 +30,8 @@ This is followed by the compound statement body, which
consists of:
- [Queries](sql-ref-syntax-qry-select.html) that return result sets to the
invoker of the script.
- [SET](sql-ref-syntax-aux-set-var.html) statements to set local variables as
well as session variables.
- The [EXECUTE IMMEDIATE](sql-ref-syntax-aux-exec-imm.html) statement.
-- Nested compound statements, which provide nested scopes for variables,
conditions, and condition handlers.
+- Cursor processing with [OPEN](control-flow/open-stmt.html),
[FETCH](control-flow/fetch-stmt.html), and
[CLOSE](control-flow/close-stmt.html) statements.
+- Nested compound statements, which provide nested scopes for variables,
cursors, conditions, and condition handlers.
## Passing data between the invoker and the compound statement
@@ -39,15 +40,18 @@ There are two ways to pass data to and from a SQL script:
- Use session variables to pass scalar values or small sets of arrays or maps
from one SQL script to another.
- Use parameter markers to pass scalar values or small sets of arrays or map
data from a notebook widget, Python, or another language to the SQL Script.
-## Variable scoping
+## Variable and cursor scoping
Variables declared within a compound statement can be referenced in any
expression within a compound statement.
Spark resolves identifiers from the innermost scope outward, following the
rules described in [Name Resolution](sql-ref-name-resolution.html).
You can use the optional compound statement labels to disambiguate duplicate
variable names.
+Cursors declared within a compound statement can be referenced in `OPEN`,
`FETCH`, and `CLOSE` statements within that compound statement.
+Like variables, you can use optional compound statement labels to disambiguate
duplicate cursor names in nested scopes.
+
## Condition handling
-SQL Scripting supports condition handlers, which are used to intercept and
process exceptions to `EXIT` processing of the SQL script.
+SQL Scripting supports condition handlers, which are used to intercept and
process exceptions to either `EXIT` the compound statement or `CONTINUE`
execution.
Condition handlers can be defined to handle three distinct classes of
conditions:
@@ -59,6 +63,8 @@ Condition handlers can be defined to handle three distinct
classes of conditions
- A generic `SQLEXCEPTION` handler can catch all conditions falling into the
`SQLEXCEPTION` (any `SQLSTATE` which is not `XX***` and not `02***`).
+- A `NOT FOUND` handler catches all conditions with SQLSTATE class `'02xxx'`,
which includes the `CURSOR_NO_MORE_ROWS` condition raised when fetching beyond
the end of a cursor's result set.
+
The following are used to decide which condition handler applies to an
exception.
This condition handler is called the **most appropriate handler**:
@@ -70,7 +76,11 @@ This condition handler is called the **most appropriate
handler**:
For example, a handler on a named condition is more specific than one on a
named `SQLSTATE`.
A generic `EXCEPTION` handler is the least specific.
-The outcome of a condition handler is to execute the statement following the
compound statement that declared the handler to execute next.
+The outcome of a condition handler is as follows:
+
+- An `EXIT` handler executes its statement, then exits the compound statement
that declared the handler. All cursors opened within that compound statement
and nested compound statements are implicitly closed.
+
+- A `CONTINUE` handler executes its statement, then continues execution with
the statement following the one that raised the condition.
The following is a list of supported control flow statements:
@@ -83,3 +93,9 @@ The following is a list of supported control flow statements:
* [LOOP](control-flow/loop-stmt.html)
* [REPEAT](control-flow/repeat-stmt.html)
* [WHILE](control-flow/while-stmt.html)
+
+The following is a list of cursor statements:
+
+* [OPEN](control-flow/open-stmt.html)
+* [FETCH](control-flow/fetch-stmt.html)
+* [CLOSE](control-flow/close-stmt.html)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
