PolarDB:Basic statements

Assign a value

You can assign a value to a PL/SQL variable by using the following syntax:

    variable { := | = } expression;

The expression of this statement is evaluated in such a way that an SQL SELECT statement is sent to the primary database engine. The result value of this expression must be a single value. If the variable is a row or record variable, the result value may be a row value. The variable to which you want to assign a value can be a simple variable, a field of a row or record variable, or an array element of a simple variable or field. If the variable is a simple variable, you can qualify it with a block name. An equal sign (=) can be used in place of := that is compatible with PL/SQL.

If the result data type of the expression does not match that of the variable, the former is forcibly converted to the latter, which is similar to an assignment cast. If no assignment cast is available for the pair of data types that are involved, the PL/SQL interpreter attempts to convert the result value to a string. That means applying the output function of the resulting data type before applying the input function of the variable type. If the result value in string form cannot be accepted by the input function, a runtime error may be returned by the input function.

Examples:

    tax := subtotal * 0.06;
    my_record.user_id := 20;

Execute a statement with no result

For an SQL statement that does not return rows, such as an INSERT statement without a RETURNING clause, you can execute the statement after you write it into a PL/SQL function.

In this case, any PL/SQL variable name that appears in the statement text is considered a parameter, and the current value of the variable is provided as the value of the parameter when the statement is being executed. This is exactly the same as the handling of expressions.

If an SQL statement is directly executed in a PL/SQL function, PL/SQL caches and reuses the execution plan for the statement.

Sometimes, it is useful to evaluate an expression or a SELECT query and discard the result. For example, you can invoke a function that has side effects but no useful result value in this way. To do this in PL/SQL, use the PERFORM statement:

    PERFORM query;

The query is executed but the result is discarded. Write the query in the same way you write an SQL SELECT statement, and replace the initial keyword SELECT with PERFORM. If you want to execute a WITH query, use PERFORM and then enclose the query in parentheses. In this case, only one row of data is returned. PL/SQL variables are substituted into the query, and PL/SQL caches the execution plan for the statement, as described earlier for a statement that does not return results. The special variable FOUND is set to true if the statement returns at least one row of data, and set to false if the statement returns no data.

Example:

    PERFORM create_mv('cs_session_page_requests_mv', my_query);

Execute a query that returns a single row of data

The result of an SQL statement that returns a single row possibly with multiple columns can be assigned to a record variable, a row variable, or a list of scalar variables. This effect is achieved by writing a basic SQL statement and adding an INTO clause. Examples:

    SELECT select_expressions INTO [STRICT] target FROM ...;
    INSERT ... RETURNING expressions INTO [STRICT] target;
    UPDATE ... RETURNING expressions INTO [STRICT] target;
    DELETE ... RETURNING expressions INTO [STRICT] target;

target can be a record variable, a row variable, or a list of simple variables and record or row fields that are separated with commas (,). PL/SQL variables are substituted into the rest of the query and the execution plan is cached, as described earlier for a statement that does not return rows. In this case, you can execute SELECT statements, INSERT, UPDATE, and DELETE statements with RETURNING clauses, and other statements that return a row set, such as EXPLAIN statements. Except for the INTO clause, a PL/SQL statement is written in the same way as a regular SQL statement.

If you want to assign the result value to a row or a list of variables, the result columns of the query must exactly match the structure of the row or variables, including the number and data types. Otherwise, a runtime error occurs. If you want to assign the result value to a record variable, the record variable automatically adapts itself to the row type of the query result columns.

An INTO clause can appear almost anywhere in an SQL statement. In most cases, an INTO clause is placed before or after the list of select_expressions in a SELECT statement, or at the end of the statements of other types. We recommend that you adhere to this convention in case the PL/SQL interpreter becomes stricter in future versions.

If STRICT is not specified in the INTO clause, target is set to the first row to be returned by the query, or set to null if the query does not return rows. The definition of "first row" is not clear unless ORDER BY is used. All result rows after the first row are discarded. You can check the special variable FOUND to determine whether a single row of data is returned.

    SELECT * INTO myrec FROM emp WHERE empname = myname;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'employee % not found', myname;
    END IF;

If STRICT is specified, the query must return exactly one row. Otherwise, a runtime error occurs. The error may be NO_DATA_FOUND or TOO_MANY_ROWS. If you want to catch the error, you can use an exception block. Example:

    BEGIN
    SELECT * INTO STRICT myrec FROM emp WHERE empname = myname;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                RAISE EXCEPTION 'employee % not found', myname;
            WHEN TOO_MANY_ROWS THEN
                RAISE EXCEPTION 'employee % not unique', myname;
    END;

If a statement with STRICT is successful, FOUND is set to true.

For the INSERT, UPDATE, and DELETE statements with the RETURNING clauses, PL/SQL reports an error when more than one row of data is returned even if STRICT is not specified. This is because no option such as ORDER BY is available to determine which affected row should be returned.

If the print_strict_params parameter is enabled for the function, and an error is reported because the conditions of STRICT are not met, the DETAIL section of the error message includes the parameters that are passed to the query. You can specify the print_strict_params parameter setting for all functions by modifying the plpgsql.print_strict_params parameter. However, this takes effect only on the functions that are compiled after the modification. You can also use a compiler option to enable the print_strict_params parameter for a function. Example:

    CREATE FUNCTION get_userid(username text) RETURN int
    IS
    #print_strict_params on
    DECLARE
    userid int;
    BEGIN
        SELECT users.userid INTO STRICT userid
            FROM users WHERE users.username = get_userid.username;
        RETURN userid;
    END;

If the statement fails to be executed, an error message similar to the following one is returned:

    ERROR:  query returned no rows
    DETAIL:  parameters: $1 = 'nosuchuser'
    CONTEXT:  PL/SQL function get_userid(text) line 6 at SQL statement

Execute a dynamic statement

In many scenarios, you may want to execute a dynamic statement in a PL/SQL function. A dynamic statement involves different tables or data types in each execution. In most cases, PL/SQL cannot cache the execution plan for a dynamic statement. To resolve this issue, you can use an EXECUTE statement. Example:

    EXECUTE command-string [ INTO [STRICT] target ] [ USING expression [, ... ] ];

command-string is an expression that results in a string of the text type that contains the statement to be executed. The optional target is a record variable, a row variable, or a list of simple variables and record or row fields that are separated with commas (,), into which the results of the statement are stored. The optional USING expression provides the values to be inserted into the statement.

PL/SQL variables are not substituted into the statement string. All required variable values must be inserted into the statement string when the string is being generated. Alternatively, you can use parameters, as described below.

In addition, no execution plan is cached for the statement that is executed by using EXECUTE. Instead, an execution plan is generated for this statement every time it is executed. This way, the statement string can be dynamically created in a function that performs actions on different tables and columns.

The INTO clause specifies the destination to which the result value of the SQL statement that returns rows is assigned. If you want to assign the value to a row or a list of variables, the row or variables must exactly match the structure of the result value. If you want to assign the value to a record variable, the record variable automatically adapts itself to the structure of the result value. If multiple rows are returned, only the first row is assigned to the INTO variable. If no row is returned, the NULL value is assigned to the INTO variable. If no INTO clause is specified, the result value is discarded.

If STRICT is specified, an error is reported unless the statement exactly returns one row of data.

You can use parameter values in a statement string, and the parameter values are referenced by using symbols such as $1 and $2. These symbols are used to reference the values provided in the USING clause. This method is applicable if you want to insert data values as text into a statement string. This eliminates the runtime load of converting the values to text and back, and it is less vulnerable to SQL injection attacks because no quoting or escaping is required. Example:

    EXECUTE 'SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2'
       INTO c
       USING checked_user, checked_date;

Parameter symbols can be used only for data values. If you want to use table or column names that are dynamically determined, you must insert the table or column names as text into the statement string. For example, if the previous query needs to be performed on a dynamically selected table, you can use the following syntax:

    EXECUTE 'SELECT count(*) FROM '
        || quote_ident(tabname)
        || ' WHERE inserted_by = $1 AND inserted <= $2'
       INTO c
       USING checked_user, checked_date;

Alternatively, you can use the %I specification of format() for table or column names. This way, the strings separated by line feeds are concatenated. This method is more simple. Example:

    EXECUTE format('SELECT count(*) FROM %I '
       'WHERE inserted_by = $1 AND inserted <= $2', tabname)
       INTO c
       USING checked_user, checked_date;

In addition, parameter symbols work only in SELECT, INSERT, UPDATE, and DELETE statements. In other statements, which are called utility statements, you must insert values as text into the statement string even if the values are data values.

In the first example provided in this section, the EXECUTE statement with a simple constant statement string and some USING parameters is functionally equivalent to a statement written directly in PL/SQL, and allows automatic PL/SQL variable substitution. The key difference is that EXECUTE replans the statement during each execution based on the current parameter values, whereas PL/SQL creates a generic plan and caches it for reuse. In cases where the optimal plan is strongly dependent on parameter values, it is helpful to use EXECUTE to explicitly ensure that a generic plan is not chosen.

EXECUTE does not support SELECT INTO. However, you can execute a plain SELECT statement and specify an INTO clause in the EXECUTE statement.

You often have to deal with the escaping of single quotation marks (') when you use dynamic statements. We recommend that you use a dollar sign ($) in the function body to quote fixed text.

Be careful to handle dynamic values because they may contain quotation marks. The following sample code provides an example of using format(). In this example, a dollar sign ($) is used for quoting in the function body, so the quotation marks are single quotation marks (').

    EXECUTE format('UPDATE tbl SET %I = $1 '
       'WHERE key = $2', colname) USING newvalue, keyvalue;

You can also directly call quoting functions. Example:

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

In this example, the quote_ident and quote_literal functions are used. For safety, before you insert values into a dynamic query, the expressions that contain column or table identifiers must be passed by using quote_ident. If an expression contains a value that should be a string in the constructed statement, the expression must be passed by using quote_literal. These functions take the appropriate steps to return text that is enclosed in double quotation marks (") or single quotation marks ('). All embedded special characters are properly escaped.

Because quote_literal is specified as STRICT, it always returns null when it is invoked with a null parameter. In the preceding example, if newvalue or keyvalue is null, the entire dynamic query string becomes null, which results in an error of the EXECUTE statement. You can prevent this issue by using the quote_nullable function, which works the same as quote_literal, except that quote_nullable returns the string NULL when it is invoked with a null parameter. Example:

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

If you are dealing with a potentially null parameter value, you should replace quote_literal with quote_nullable.

Be careful to ensure that null values in a query do not deliver unexpected results. For example, if keyvalue is null, the following WHERE clause never succeeds:

    'WHERE key = ' || quote_nullable(keyvalue)

This is because the result of using a null operand with the = operator is always null. If you want a null value to work like a regular key value, you can rewrite the preceding statement in the following way:

    'WHERE key IS NOT DISTINCT FROM ' || quote_nullable(keyvalue)

The dollar sign ($) quoting is useful only for quoting fixed text. Do not write a statement similar to the following one:

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

This is because if the content of newvalue happens to contain $$, an error occurs. This may happen when you use any other dollar-quoting delimiters. Therefore, to safely quote text that is unknown in advance, you must properly use quote_literal, quote_nullable, or quote_ident.

Dynamic SQL statements can also be safely constructed by using the format function. Example:

    EXECUTE format('UPDATE tbl SET %I = %L '
       'WHERE key = %L', colname, newvalue, keyvalue);

%I is equivalent to quote_ident and %L is equivalent to quote_nullable. The format function can be used together with a USING clause. Example:

    EXECUTE format('UPDATE tbl SET %I = $1 WHERE key = $2', colname)
       USING newvalue, keyvalue;

This method is better because variables are processed in their native data type format, rather than being forcibly converted to text and quoted by using %L. This method is also more efficient.

Obtain the result status

You can use multiple methods to determine the effect of a statement. The first method is to use the GET DIAGNOSTICS statement. Syntax:

    GET [ CURRENT ] DIAGNOSTICS variable { = | := } item [ , ... ];

This statement allows you to retrieve system status indicators. CURRENT is a noise word. Each item is a keyword that identifies a state value to be assigned to a specific variable. The variable must be of the correct data type to receive the state value. The following table describes the available diagnostics items. The colon-equal sign (:=) can be used to replace the equal sign (=) of an SQL statement. Example

    GET DIAGNOSTICS integer_var = ROW_COUNT;

Available diagnostics items

The second method is to check the special variable FOUND, which is of the boolean type. Each time a PL/SQL function is invoked, the initial value of FOUND is false. The value is set based on the results of the following statements:

• If a SELECT INTO statement assigns one row, the value of FOUND is set to true. If no row is returned, the value is set to false.

• If a PERFORM statement returns one or more rows and discards the results, the value of FOUND is set to true. If no row is returned, the value is set to false.

• If an UPDATE, INSERT, or DELETE statement affects at least one row, the value of FOUND is set to true. If no row is affected, the value is set to false.

• If a FETCH statement returns one row, the value of FOUND is set to true. If no row is returned, the value is set to false.

• If a MOVE statement successfully repositions the cursor, the value of FOUND is set to true. Otherwise, the value is set to false.

• If a FOR or FOREACH statement has been iterated one or more times, the value of FOUND is set to true. Otherwise, the value is set to false. When the statement exits a loop, the value of FOUND is set in the same way. During the loop execution, although FOUND may be modified by the execution of other statements in the loop body, the value is not modified by the loop statement.

• If a RETURN QUERY or RETURN QUERY EXECUTE statement returns at least one row, the value of FOUND is set to true. If no row is returned, the value is set to false.

If you execute PL/SQL statements other than the preceding ones, the value of FOUND does not change. Take note that EXECUTE changes the output of GET DIAGNOSTICS but does not change the value of FOUND.

FOUND is local to every PL/SQL function, and any modification to it affects only the current function.

Execute a placeholder statement

A placeholder statement that does nothing is useful. For example, it can indicate the empty branch that is deliberately set aside in the if-then-else chain. To do this, you can use NULL statements.

    NULL;

For example, the following two statements achieve the same effect:

    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN
            NULL;  -- Ignores errors.
    END;

    BEGIN
        y := x / 0;
    EXCEPTION
        WHEN division_by_zero THEN  -- Ignores errors.
    END;

You can choose one of them based on your personal preference.