支持的 PL/pgSQL 语句 - Amazon Redshift

支持的 PL/pgSQL 语句

PL/pgSQL 语句使用程序性结构(包括循环和条件表达式)增强 SQL 命令,以控制逻辑流。大部分 SQL 命令都可以使用,包括数据操作语言(DML,Data Manipulation Language)(例如 COPY、UNLOAD 和 INSERT)以及数据定义语言(DDL,Data Definition Language)(例如 CREATE TABLE)。有关完整的 SQL 命令列表,请参阅 SQL 命令。此外,Amazon Redshift 支持以下 PL/pgSQL 语句。

赋值

赋值语句将值赋予变量。表达式必须返回单个值。

identifier := expression;

使用非标准 = 而不是 := 进行赋值也是可以接受的。

如果表达式的数据类型与变量的数据类型不匹配,或者变量具有大小或精度要求,则将隐式转换结果值。

下面是一个示例。

customer_number := 20; tip := subtotal * 0.15;

SELECT INTO

SELECT INTO 语句将多列(但仅限一行)的结果赋予一个记录变量或赋予标量变量列表。

SELECT INTO target select_expressions FROM ...;

在前一种语法中,target 可以是记录变量,或者是简单变量和记录字段的逗号分隔列表。select_expressions 列表和命令的剩余部分与常规 SQL 中相同。

如果将变量列表用作 target,则所选值必须与目标的结构完全匹配,否则会出现运行时错误。当记录变量是目标时,它自动将自身配置为查询结果列的行类型。

INTO 子句可以在 SELECT 语句中的几乎任何位置出现。它通常出现在紧挨着 SELECT 子句的后面,或者正好在 FROM 子句之前。即,它显示在 select_expressions 列表紧挨着的前面或后面。

如果查询返回零行,则将 NULL 值赋予 target。如果查询返回多行,则第一行赋予 target,丢弃剩余的行。除非语句包含 ORDER BY,否则第一行不是确定性的。

要确定赋值是否至少返回了一行,请使用特殊 FOUND 变量。

SELECT INTO customer_rec * FROM cust WHERE custname = lname; IF NOT FOUND THEN RAISE EXCEPTION 'employee % not found', lname; END IF;

要测试某个记录结果是否为 null,您可以使用有条件的 IS NULL。没有方法确定是否已经丢弃了任何其他行。以下示例处理未返回任何行的情况。

CREATE OR REPLACE PROCEDURE select_into_null(return_webpage OUT varchar(256)) AS $$ DECLARE customer_rec RECORD; BEGIN SELECT INTO customer_rec * FROM users WHERE user_id=3; IF customer_rec.webpage IS NULL THEN -- user entered no webpage, return "http://" return_webpage = 'http://'; END IF; END; $$ LANGUAGE plpgsql;

无操作

无操作语句 (NULL;) 是不执行任何操作的占位符语句。无操作语句可指示 IF-THEN-ELSE 链的一个分支为空。

NULL;

动态 SQL

对于在每次从 PL/pgSQL 存储过程运行时会涉及到不同表或不同数据类型的动态命令,可以使用 EXECUTE 语句来生成此类命令。

EXECUTE command-string [ INTO target ];

在前面的内容中,command-string 是生成字符串(文本类型)的表达式,其中包含要运行的命令。command-string 值发送到 SQL 引擎。在命令字符串上不进行 PL/pgSQL 变量替换。变量的值必须在命令字符串构造时插入。

注意

您不能在动态 SQL 中使用 COMMIT 和 ROLLBACK 语句。有关在存储过程中使用 COMMIT 和 ROLLBACK 语句的信息,请参阅管理事务

使用动态命令时,您通常必须处理单引号的转义。建议您在函数正文中使用美元符号来括起引号中的固定文本。插入结构化查询中的动态值需要特殊处理,因为它们本身可能包含引号。以下示例假定用美元符号整体括起了函数,因此无需两个引号。

EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = ' || quote_literal(newvalue) || ' WHERE key = ' || quote_literal(keyvalue);

前面的示例显示了函数 quote_ident(text)quote_literal(text)。此示例将包含列和表标识符的变量传递到 quote_ident 函数。它还可以在结构化命令中,将包含文本字符串的变量传递到 quote_literal 函数。这两个函数均采取相应的步骤,分别返回括在双引号或单引号中的输入文本,其中任何嵌入的特殊字符均正确转义。

美元符号括起仅对与括起固定文本有用。请不要按照以下格式编写前述示例。

EXECUTE 'UPDATE tbl SET ' || quote_ident(colname) || ' = $$' || newvalue || '$$ WHERE key = ' || quote_literal(keyvalue);

不能这样做的原因是,如果 newvalue 的内容偶然包含了 $$,示例会中断。您可能选择的任何其他美元符号括起分隔符都会出现这种问题。要安全地括起事先不知道内容的文本,请使用 quote_literal 函数。

Return

RETURN 语句从存储过程返回调用方。

RETURN;

下面是一个示例。

CREATE OR REPLACE PROCEDURE return_example(a int) AS $$ BEGIN FOR b in 1..10 LOOP IF b < a THEN RAISE INFO 'b = %', b; ELSE RETURN; END IF; END LOOP; END; $$ LANGUAGE plpgsql;

条件:IF

在 Amazon Redshift 使用的 PL/pgSQL 语言中,IF 条件语句可以采用以下格式:

  • IF ... THEN

    IF boolean-expression THEN statements END IF;

    下面是一个示例。

    IF v_user_id <> 0 THEN UPDATE users SET email = v_email WHERE user_id = v_user_id; END IF;
  • IF ... THEN ... ELSE

    IF boolean-expression THEN statements ELSE statements END IF;

    下面是一个示例。

    IF parentid IS NULL OR parentid = '' THEN return_name = fullname; RETURN; ELSE return_name = hp_true_filename(parentid) || '/' || fullname; RETURN; END IF;
  • IF ... THEN ... ELSIF ... THEN ... ELSE

    关键词 ELSIF 也可以拼写成 ELSEIF。

    IF boolean-expression THEN statements [ ELSIF boolean-expression THEN statements [ ELSIF boolean-expression THEN statements ...] ] [ ELSE statements ] END IF;

    下面是一个示例。

    IF number = 0 THEN result := 'zero'; ELSIF number > 0 THEN result := 'positive'; ELSIF number < 0 THEN result := 'negative'; ELSE -- the only other possibility is that number is null result := 'NULL'; END IF;

条件:CASE

在 Amazon Redshift 使用的 PL/pgSQL 语言中,CASE 条件语句可以采用以下格式:

  • 简单 CASE

    CASE search-expression WHEN expression [, expression [ ... ]] THEN statements [ WHEN expression [, expression [ ... ]] THEN statements ... ] [ ELSE statements ] END CASE;

    简单 CASE 语句根据操作数是否相等提供有条件的执行。

    search-expression 值求值一次,接下来与 WHEN 子句中的各个 expression 进行比较。如果找到匹配,则对应的 statements 运行,然后控制传递到 END CASE 之后的下一个语句。不对后面的 WHEN 表达式求值。如果未找到匹配,则运行 ELSE statements。但是,如果没有 ELSE,则将引发 CASE_NOT_FOUND 异常。

    下面是一个示例。

    CASE x WHEN 1, 2 THEN msg := 'one or two'; ELSE msg := 'other value than one or two'; END CASE;
  • 搜索 CASE

    CASE WHEN boolean-expression THEN statements [ WHEN boolean-expression THEN statements ... ] [ ELSE statements ] END CASE;

    CASE 的搜索形式提供有条件执行,基于布尔值表达式求值为对还是错。

    每个 WHEN 子句的 boolean-expression 按顺序求值,直至发现得到 true 的子句。然后,对应的语句运行,然后控制传递到 END CASE 之后的下一个语句。不对后面的 WHEN 表达式求值。如果没有找到 true 结果,则运行 ELSE statements。但是,如果没有 ELSE,则将引发 CASE_NOT_FOUND 异常。

    下面是一个示例。

    CASE WHEN x BETWEEN 0 AND 10 THEN msg := 'value is between zero and ten'; WHEN x BETWEEN 11 AND 20 THEN msg := 'value is between eleven and twenty'; END CASE;

Loops

在 Amazon Redshift 使用的 PL/pgSQL 语言中,循环语句可以采用以下格式:

  • 简单循环

    [<<label>>] LOOP statements END LOOP [ label ];

    简单循环定义无条件的循环,这种循环将无限重复,直至由 EXIT 或 RETURN 语句终止。在嵌套循环中,EXIT 和 CONTINUE 可以使用可选的标签来指定 EXIT 和 CONTINUE 语句引用哪个循环。

    下面是一个示例。

    CREATE OR REPLACE PROCEDURE simple_loop() LANGUAGE plpgsql AS $$ BEGIN <<simple_while>> LOOP RAISE INFO 'I am raised once'; EXIT simple_while; RAISE INFO 'I am not raised'; END LOOP; RAISE INFO 'I am raised once as well'; END; $$;
  • 退出循环

    EXIT [ label ] [ WHEN expression ];

    如果没有 label,则终止最内部的循环,接下来运行 END LOOP 后面的语句。如果有 label,则必须是当前或某个更靠外级别的嵌套循环或块的标签。然后,指定的循环或块终止,继续由循环或块对应的 END 之后的语句控制。

    如果指定了 WHEN,则循环仅在 expression 为 true 时退出。否则,控制将传递到 EXIT 之后的语句。

    您可以对所有类型的循环使用 EXIT,它不限于仅由无条件循环使用。

    用于 BEGIN 块时,EXIT 将控制传递到块结束之后的语句。为此必须使用标签。在匹配 BEGIN 块时,永远不考虑无标签的 EXIT。

    下面是一个示例。

    CREATE OR REPLACE PROCEDURE simple_loop_when(x int) LANGUAGE plpgsql AS $$ DECLARE i INTEGER := 0; BEGIN <<simple_loop_when>> LOOP RAISE INFO 'i %', i; i := i + 1; EXIT simple_loop_when WHEN (i >= x); END LOOP; END; $$;
  • 继续循环

    CONTINUE [ label ] [ WHEN expression ];

    如果未给出 label,则执行跳转到最内部循环的下一次迭代。即,跳过循环正文中剩余的所有语句。然后,控制返回到循环控制表达式(如果有),确定是否需要下一次循环迭代。如果存在 label,它指定将继续其执行的循环的标签。

    如果指定了 WHEN,则循环的下一次迭代仅在 expression 为 true 时开始。否则,控制将传递到 CONTINUE 之后的语句。

    您可以对所有类型的循环使用 CONTINUE,它不限于仅由无条件循环使用。

    CONTINUE mylabel;
  • WHILE 循环

    [<<label>>] WHILE expression LOOP statements END LOOP [ label ];

    只要 boolean-expression 求值为 true,WHILE 语句就会重复一组语句。在每个条目即将进入循环正文之前对表达式进行检查。

    下面是一个示例。

    WHILE amount_owed > 0 AND gift_certificate_balance > 0 LOOP -- some computations here END LOOP; WHILE NOT done LOOP -- some computations here END LOOP;
  • FOR 循环(整数变体)

    [<<label>>] FOR name IN [ REVERSE ] expression .. expression LOOP statements END LOOP [ label ];

    FOR 循环(整数变体)创建一个循环,对一系列整数值进行迭代。变量名称自动定义为整数类型,并且仅在循环中存在。忽略循环中任何现有的变量名称定义。给出了范围下限和上限的两个表达式,在进入循环时求值一次。如果您指定 REVERSE,则在每次迭代之后减去步进值而不是累加。

    如果下限大于上限(在 REVERSE 情况下则为小于),循环正文不运行。不引发错误。

    如果将标签附加到 FOR 循环,则您可以使用限定名称,通过该标签应用整数循环变量。

    下面是一个示例。

    FOR i IN 1..10 LOOP -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop END LOOP; FOR i IN REVERSE 10..1 LOOP -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop END LOOP;
  • FOR 循环(结果集变体)

    [<<label>>] FOR target IN query LOOP statements END LOOP [ label ];

    target 是一个记录变量,或者是标量变量的逗号分隔列表。目标依次分配给查询生成的每一行,并为每一行运行循环正文。

    FOR 循环(结果集变体)使得存储过程可以迭代查询的结果并相应操作数据。

    下面是一个示例。

    CREATE PROCEDURE cs_refresh_reports() AS $$ DECLARE reports RECORD; BEGIN FOR reports IN SELECT * FROM cs_reports ORDER BY sort_key LOOP -- Now "reports" has one record from cs_reports EXECUTE 'INSERT INTO ' || quote_ident(reports.report_name) || ' ' || reports.report_query; END LOOP; RETURN; END; $$ LANGUAGE plpgsql;
  • 具有动态 SQL 的 FOR 循环

    [<<label>>] FOR record_or_row IN EXECUTE text_expression LOOP statements END LOOP;

    具有动态 SQL 的 FOR 循环使得存储过程可以迭代动态查询的结果并相应操作数据。

    下面是一个示例。

    CREATE OR REPLACE PROCEDURE for_loop_dynamic_sql(x int) LANGUAGE plpgsql AS $$ DECLARE rec RECORD; query text; BEGIN query := 'SELECT * FROM tbl_dynamic_sql LIMIT ' || x; FOR rec IN EXECUTE query LOOP RAISE INFO 'a %', rec.a; END LOOP; END; $$;

游标

您可以不必一次运行整个查询,而是设置游标。游标 封装一个查询,并一次读取几行查询结果。这样做的原因之一是避免在结果包含大量行时过多占用内存。另一个原因是返回对存储过程所创建游标的引用,这使得调用函数可以读取行。此方法提供了从存储过程返回大量行集的高效方式。

要在 NONATOMIC 过程中使用游标,请将游标循环置于 START TRANSACTION...COMMIT 之间。

要设置游标,请先声明游标变量。在 PL/pgSQL 中,对游标的所有访问都通过游标变量进行,该变量始终是特殊数据类型 refcursorrefcursor 数据类型仅仅存放对游标的引用。

您可以通过声明类型为 refcursor 的变量来创建游标变量。或者,您可使用以下游标声明语法。

name CURSOR [ ( arguments ) ] FOR query ;

在前文中,arguments(如果指定)是 name datatype 对的逗号分隔列表,每一对定义一个由 query 中参数值替换的名称。用于替换这些名称的实际值稍后在打开游标时指定。

下面是一个示例。

DECLARE curs1 refcursor; curs2 CURSOR FOR SELECT * FROM tenk1; curs3 CURSOR (key integer) IS SELECT * FROM tenk1 WHERE unique1 = key;

这三个变量均具有数据类型 refcursor,但第一个可用于任意查询。相反,第二个变量绑定了已经充分指定的查询,最后一个绑定了参数化查询。key 值由打开游标时的整数参数值替换。变量 curs1 称为未绑定 是因为它未绑定到任何特殊查询。

您必须先打开它,然后才能使用游标检索行。PL/pgSQL 具有三种形式的 OPEN 语句,其中两个使用未绑定游标变量,第三个语句使用绑定游标变量:

  • 打开供选择:游标变量已打开,并提供了指定的查询来运行。该游标不能已经打开。此外,它必须已经声明作为未绑定游标(即,作为简单 refcursor 变量)。SELECT 查询的处理方式与 PL/pgSQL 中的 SELECT 语句相同。

    OPEN cursor_name FOR SELECT ...;

    下面是一个示例。

    OPEN curs1 FOR SELECT * FROM foo WHERE key = mykey;
  • 打开供执行:游标变量已打开,并提供了指定的查询来运行。该游标不能已经打开。此外,它必须已经声明作为未绑定游标(即,作为简单 refcursor 变量)。以字符串表达式格式指定查询的方式,与在 EXECUTE 命令中的方式相同。此方法提供了灵活性,使得查询可以在不同运行之间变化。

    OPEN cursor_name FOR EXECUTE query_string;

    下面是一个示例。

    OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1);
  • 打开绑定游标:这种类型的 OPEN 用于打开的游标变量,在声明时已经将查询绑定到其上。该游标不能已经打开。只有在声明了游标来获取参数时,才必须显示实际参数值表达式的列表。这些值在查询中提交。

    OPEN bound_cursor_name [ ( argument_values ) ];

    下面是一个示例。

    OPEN curs2; OPEN curs3(42);

打开某个游标之后,您可以使用下述语句来处理它。这些语句不必出现在打开游标的同一个存储过程中。您可将 refcursor 值返回到存储过程之外,让调用函数来处理游标。所有门户在事务结束之后隐式关闭。因此,您只能在事务结束后,使用 refcursor 值来引用打开游标。

  • FETCH 从游标将下一行检索到目标中。目标可以是行变量、记录变量,或者是简单变量的逗号分隔列表,就像与 SELECT INTO 一样。对于 SELECT INTO,您可以检查特殊变量 FOUND 以查看是否获取了某行。

    FETCH cursor INTO target;

    下面是一个示例。

    FETCH curs1 INTO rowvar;
  • CLOSE 将关闭打开游标下的门户。您可以使用此语句,在事务结束之前释放资源。您也可以使用此语句再次释放游标成为打开状态。

    CLOSE cursor;

    下面是一个示例。

    CLOSE curs1;

RAISE

使用 RAISE level 语句报告消息和引发错误。

RAISE level 'format' [, variable [, ...]];

可能的级别包括 NOTICE、INFO、LOG、WARNING 和 EXCEPTION。EXCEPTION 引发错误,这通常会取消当前事务。其他级别仅生成不同优先级的消息。

在格式字符串中,% 替换为下一个可选参数的字符串表示形式。写作 %% 格式将发出文本 %。当前,可选参数必须为简单变量而不是表达式,格式必须是简单字符串文本。

在以下示例中,v_job_id 的值替换字符串中的 %。

RAISE NOTICE 'Calling cs_create_job(%)', v_job_id;

使用 RAISE 语句重新引发由异常处理块捕获的异常。此语句仅在 NONATOMIC 模式存储过程的异常处理块中有效。

RAISE;

事务控制

您可以处理 Amazon Redshift 所用 PL/pgSQL 语言中的事务控制语句。有关在存储过程中使用 COMMIT、ROLLBACK 和 TRUNCATE 的信息,请参阅管理事务

在 NONATOMIC 模式存储过程中,使用 START TRANSACTION 来启动事务块。

START TRANSACTION;
注意

PL/pgSQL 语句 START TRANSACTION 与 SQL 命令 START TRANSACTION 有以下不同:

  • 在存储过程中,START TRANSATION 不等同于 BEGIN。

  • PL/pgSQL 语句不支持可选的隔离级别和访问权限关键字。