SELECT INTO（含缺省值）

SELECT INTO 语句广泛用于向一组标量变量分配结果集。由于 语句不接受空结果集，因此，如果返回空结果集，则必须定义退出处理程序。DEFAULT 值的引入使得处理空结果集时无需写入出口处理程序，即可在结果集为空时为目标变量指派缺省值。

语法

代码语法SELECT INTO [DEFAULT ] [][][] [{ , ... }][] [] ;[EXEC | EXECUTE IMMEDIATE] [ INTO [DEFAULT ] ] [ USING ]

描述

也可以使用单个数组元素作为 SELECT INTO 和 EXEC INTO 的结果。INTO 子句的语法扩展如下：

 ::= [{, }...]

  ::=  |  '['  ']'

示例代码DROP TABLE T1;CREATE TABLE T1 (A INT NOT NULL, B VARCHAR(10)); DO BEGIN DECLARE A_COPY INT ARRAY; DECLARE B_COPY VARCHAR(10) ARRAY; SELECT A, B INTO A_COPY[1], B_COPY[1] DEFAULT -2+1, NULL FROM T1; SELECT :A_COPY[1], :B_COPY[1] from dummy; --(A_COPY[1],B_COPY[1]) = (-1,?), use default value EXEC 'SELECT A FROM T1' INTO A_COPY[1] DEFAULT 2; SELECT :A_COPY[1], :B_COPY[1] from dummy; --(A_COPY[1]) = (2), exec into statement with default value INSERT INTO T1 VALUES (0, 'sample0'); SELECT A, B INTO A_COPY[1], B_COPY[1] DEFAULT 5, NULL FROM T1; SELECT :A_COPY[1], :B_COPY[1] from dummy; --(A_COPY[1],B_COPY[1]) = (0,'sample0'), executed as-isEND;

示例

DO BEGIN
    DECLARE A_COPY INT;
    DECLARE B_COPY VARCHAR(10);
    CREATE ROW TABLE T1 (A INT NOT NULL, B VARCHAR(10));
    SELECT A, B INTO A_COPY, B_COPY DEFAULT -2+1, NULL FROM T1;
    --(A_COPY,B_COPY) = (-1,?), use default value
    EXEC 'SELECT A FROM T1' INTO A_COPY DEFAULT 2;
    --(A_COPY) = (2), exec into statement with default value
    INSERT INTO T1 VALUES (0, 'sample0');
    SELECT A, B INTO A_COPY, B_COPY DEFAULT 5, NULL FROM T1;
    --(A_COPY,B_COPY) = (0,'sample0'), executed as-is
END;

限制和约束

限制显式并行执行块中不支持具有缺省值的 SELECT INTO 子句。避免在同一 SQLScript 块中使用这两个功能，因为这可能会导致意外行为或运行时错误。有关详细信息，请参阅 3637202 。