Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
Creates a temporary table and copies the result set of an open cursor to it.
sa_copy_cursor_to_temp_table( cursor_name , table_name [, first_row [, max_rows ] ] )
Use this VARCHAR(256) parameter to specify the name of the open cursor.
Use this VARCHAR(256) parameter to specify the name of the temporary table.
Use this BIGINT parameter to specify the number of the first row to copy to the temporary table. The default is 1.
Use this BIGINT parameter to specify the maximum number of rows to copy to the temporary table. The default is 9223372036854775807 (all rows).
Suppose you have a cursor of several integer columns. sa_copy_cursor_to_temp_table creates a temporary table using a statement in this form:
BEGIN CREATE LOCAL TEMPORARY TABLE TempTab ( col1 INT, col2 INT, ... rownum bigint primary key ) END;
sa_copy_cursor_to_temp_table names the columns col1,col2, and so on to avoid duplication of names or difficulty if cursor columns do not have a well defined name (for example, if they are a complex expression).
Once the temporary table is created, the contents of the open cursor are inserted by moving to the row number indicated by first_row, and inserting the number of rows indicated by max_rows. After the contents have been inserted into the temporary table, the cursor is re-positioned at its original location.
You must have EXECUTE privilege on the system procedure.
Copying from the cursor fetches the rows using the cursor's isolation settings. This may acquire locks on rows and have other effects equivalent to fetching from the cursor.
If concurrent changes are made outside of the current connection and the cursor is not protected from these by materialization or isolation settings, then it is possible that the cursor will be positioned on a different row after the procedure completes. For example, if the previous current row of the cursor was deleted, the cursor could be repositioned on the row after the original position.
If an error occurs while copying from the cursor, the cursor enters an invalid state, and further operations on the cursor fail with an error.
The following batch creates a cursor named myCursor and loads it with data from the Products table. The cursor is then opened (OPEN statement). A DROP statement drops myTempTable, if it already exists. Calling sa_copy_cursor_to_temp_table creates a temporary table called myTempTable and copies the contents of myCursor into it. Finally, a SELECT statement returns the data that was copied into the temporary table from the cursor:
BEGIN DECLARE myCursor CURSOR FOR SELECT ID, Name, Description, Color, Quantity FROM Products; OPEN myCursor; DROP TABLE IF EXISTS myTempTable; CALL sa_copy_cursor_to_temp_table( 'myCursor', 'myTempTable' ); CLOSE myCursor; SELECT * FROM myTempTable; END