Work around to simulate the use of Records

This page is deprecated but was left for compatibility purposes. If you want to see the updated section, please refer to Collections And Records

Description

This section describes how to simulate the behavior of Oracle records in SELECT and INSERT Statements, using RESULTSET and CURSORS of Snowflake Scripting.

Snowflake Scripting RESULTSET and CURSOR

You might also be interested in CURSOR.

Snowflake RESULTSET Syntax

<resultset_name> RESULTSET [ DEFAULT ( <query> ) ] ;

LET <resultset_name> RESULTSET [ { DEFAULT | := } ( <query> ) ] ;

LET <resultset_name> RESULTSET [ { DEFAULT | := } ( <query> ) ] ;

Recommendations

For the following examples, this code was executed to better understanding of the examples:

Oracle

CREATE TABLE numbers_table(num integer, word varchar2(20));
INSERT INTO numbers_table VALUES (1, 'one');
CREATE TABLE aux_numbers_table(aux_num integer, aux_word varchar2(20));

Snowflake

CREATE OR REPLACE TABLE PUBLIC.numbers_table (num integer,
word VARCHAR(20));

INSERT INTO PUBLIC.numbers_table VALUES (1, 'one');

CREATE OR REPLACE TABLE PUBLIC.aux_numbers_table (aux_num integer,
aux_word VARCHAR(20));

Using RESULTSET and Cursors instead of Records

Oracle

Query

CREATE OR REPLACE PROCEDURE proc_insert_select_resultset
AS
TYPE number_record_definition IS RECORD(
	rec_num numbers_table.num%type,
	rec_word numbers_table.word%type
);
number_record number_record_definition;
BEGIN 
	SELECT * INTO number_record FROM numbers_table;	
	INSERT INTO aux_numbers_table VALUES number_record;
END;

CALL proc_insert_select_resultset();
SELECT * FROM aux_numbers_table;

Result

|AUX_NUM|AUX_WORD|
|-------|--------|
|1      |one     |

Snowflake

Query

CREATE OR REPLACE PROCEDURE PUBLIC.proc_select_into()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
DECLARE
    NUMBER_RECORD RESULTSET;
BEGIN
    NUMBER_RECORD := (SELECT * FROM numbers_table);
    INSERT INTO PUBLIC.aux_numbers_table SELECT * FROM table(result_scan(last_query_id()));
END;
$$;

Query using cursor

CREATE OR REPLACE PROCEDURE PUBLIC.proc_select_into()
RETURNS INTEGER
LANGUAGE SQL
AS
$$
DECLARE
    NUMBER_VARIABLE INTEGER;
    WORD_VARIABLE VARCHAR;
    NUMBER_RECORD RESULTSET;
BEGIN
    LET c2 CURSOR FOR NUMBER_RECORD;
    FOR row_variable IN c2 DO
        let var1 integer := row_variable.num;
        let var2 varchar := row_variable.word;
        INSERT INTO PUBLIC.aux_numbers_table VALUES(:var1, :var2);
    END FOR;
end;
$$;

Result

|AUX_NUM|AUX_WORD|
|-------|--------|
|1      |one     |

Known Issues

1. Limitation in the use of RESULTSET

RESULTSET is very limited in its use. If table(result_scan(last_query_id())) statement, should be used just after the RESULTSET's query is executed. For further information check this link.

Related EWIs

No related EWIs.