动态澳门比分网址.

Nuodb 澳门比分网址支持在运行时定义和编译的澳门比分网址语句以用于澳门比分网址存储过程或用户定义的函数(UDFS)。

句法

执行 IMMEDIATE sql_statement
    [ INTO target_var [ , target_var ] ... ]
    [ USING VALUES input_var [ , input_var ] ... ]

描述

动态澳门比分网址. and the 执行 IMMEDIATE statement allow any 澳门比分网址 statement to be executed within a 澳门比分网址 stored procedure , trigger or UDF.

来自Nuodb 2.6,大多数程序语句可以在独立语句中使用,而无需创建存储过程。

执行 IMMEDIATE allows 澳门比分网址 statements to be defined and compiled at runtime, thereby allowing the 澳门比分网址 statement to be dynamically built at execution time by substituting parameter values or concatenating text strings. Concatenating text strings allows building 澳门比分网址 statements that select from different tables and columns. The 执行 IMMEDIATE statement supports the following capabilities:

  • 定义澳门比分网址语句并将其输出分配给存储过程,触发器或函数内的本地变量。分配对本地变量的执行立即语句时,它必须括在括号中。

  • Display a message using the THROW command.

  • Define the RETURNS result set in a stored procedure or a RETURN value from a function

  • Execute DML or DDL statements or any 澳门比分网址 statement (for example, 更新 回滚, 犯罪), within a stored procedure or function.

  • 执行存储过程*,触发器或函数*使用to参数在运行时动态分配该过程代码块的本地,in和/或Inout参数值。调用使用参数将输入值分配给调用存储过程的OUT和/或INOUT参数。

NOODB不支持与SELECT语句中的返回列值的参数。但是,可变分配语句可用于实现此功能。
When using a SELECT query as the sql-statement, the query will not be executed unless the result is assigned to a local variable or returned as a result set.

When writing dynamic 澳门比分网址, NULL values need to be considered. This is true of 澳门比分网址 statements in general, not just dynamic 澳门比分网址. For example, an 澳门比分网址 statement such as WHERE column = value will never be equal if value is NULL.

在构造动态澳门比分网址时,还需要考虑包含引用文字的澳门比分网址语句。 Nuodb支持将在值中自动转义引号的函数。看 QUOTENAME.

参数

sql_statement

A string representing a valid 澳门比分网址 statement to be used by 执行 IMMEDIATE. This can be any DDL or DML statement.

INTO target_var [ , target_var ]…​

The INTO parameter takes a comma separated list of one or more locally defined variables to be assigned the values returned from the 澳门比分网址 statement defined by sql_statement. It is assumed that sql_statement represents a call to a stored procedure or a scalar UDF. NuoDB supports assigning variables that are OUT and/or INOUT parameter values of the stored procedure or the scalar return value from the UDF. The INTO parameter is optional and only required if sql_statement returns one or more parameter values. DML statements would not require the INTO clause. The number of INTO variables defined must match the number of OUT and/or INOUT parameters to the stored procedure or one in the case of calling a scalar UDF. If no rows are returned by the 澳门比分网址 statement, the INTO variables are assigned NULL values.

USING VALUE input_var [ , input_var ]…​

One or more locally defined variables or input parameter values, in the form of a comma separated list of values used to substitute parameter values defined by sql_statement. This is optional and only required if the sql_statement contains parameters, designated by “?”. The number of USING values defined must match the number of parameters defined by sql_statement.

例子

Example 1: Using 执行 IMMEDIATE for local variable assignment
DROP PROCEDURE IF EXISTS proc_exec1;
 DELIMITER @
CREATE PROCEDURE proc_exec1 (IN inTableName STRING, IN inSelectCol STRING, inWhereCol STRING, IN inColValue STRING) AS
    VAR outParm STRING;
    outParm=(执行 IMMEDIATE 'SELECT '||inSelectCol||' FROM '||inTableName ||' WHERE '||inWhereCol||' = '||inColValue);
    THROW inSelectCol||' = '||outParm;
END_PROCEDURE
@
 DELIMITER ;

执行 proc_exec1('HOCKEY','NAME','ID','1');
Procedure HOCKEY.PROC_EXEC1, NAME = PATRICE BERGERON
Example 2: Using 执行 IMMEDIATE to execute DDL
DROP PROCEDURE IF EXISTS proc_exec2;
 DELIMITER @
CREATE PROCEDURE proc_exec2(IN inTableName STRING, IN inAltName STRING)
 as
    VAR sqlStr STRING='';
    VAR l_sqlStr STRING, l_field STRING, l_datatype STRING, l_length STRING;
    执行 IMMEDIATE 'DROP TABLE IF EXISTS '||inAltName;
    sqlStr='CREATE TABLE '||inAltName||'(';
    FOR 选择 field,d.name FROM system.fields,system.datatypes d WHERE datatype = d.id AND tablename =  inTableName;
       l_field=field;
       l_datatype = name;
       l_sqlStr=sqlStr||l_field||' '||l_datatype||',';
       sqlStr=l_sqlStr;
    END_FOR;
    sqlStr=SUBSTR(l_sqlStr,1,LENGTH(l_sqlStr)-1)||');';
    执行 IMMEDIATE sqlStr;
    执行 IMMEDIATE 'Insert into '||inAltName||' SELECT * FROM '||inTableName||' limit 2;';
END_PROCEDURE;
@
 DELIMITER ;

称呼 proc_exec2('HOCKEY','AltHockey');

选择 * FROM AltHockey;

 ID  NUMBER        NAME       POSITION   TEAM
 --- ------- ---------------- --------- ------

  1    37    PATRICE BERGERON  Forward  Bruins
  2    48    CHRIS BOURQUE     Forward  Bruins

表演 TABLE AltHockey;

    Tables named ALTHOCKEY

    Found table ALTHOCKEY in schema HOCKEY

        Fields:
            ID bigint
            NUMBER integer
            NAME string
            POSITION string
            TEAM string
Example 3: Using 执行 IMMEDIATE to assign OUT/INOUT variables from a called stored procedure
DROP PROCEDURE IF EXISTS proc_calling;
DROP PROCEDURE IF EXISTS proc_get_data;

 DELIMITER @

CREATE PROCEDURE proc_get_data(IN iNumber INTEGER, OUT ioNewPosition STRING)
AS
    ioNewPosition=(选择 CASE position
           WHEN 'Goalie' THEN 'Fan'
           WHEN 'Fan' THEN 'Goalie'
           ELSE ioNewPosition END FROM hockey WHERE id = iNumber);
END_PROCEDURE;
@

CREATE PROCEDURE proc_calling (IN inNumber INTEGER)
AS
    VAR l_new_position STRING;
    执行 IMMEDIATE 'CALL proc_get_data(?,?)' INTO l_new_position  USING VALUES  inNumber;
    UPDATE hockey  position = l_new_position WHERE id = inNumber;
END_PROCEDURE;
@

 DELIMITER ;

START TRANSACTION;
 AUTOCOMMIT OFF;
称呼  proc_calling(24);

选择 * FROM hockey WHERE id = 24;
 ID  NUMBER     NAME    POSITION   TEAM
 --- ------- ---------- --------- ------
 24     1    MAX SUMMIT  Goalie   Bruins

回滚;
选择 * FROM hockey WHERE id = 24;
 ID  NUMBER     NAME    POSITION   TEAM
 --- ------- ---------- --------- ------
 24     1    MAX SUMMIT    Fan    Bruins
Example 4: Using 执行 IMMEDIATE to RETURN a value from a function.
DROP FUNCTION IF EXISTS func_player_info;
 DELIMITER @
CREATE FUNCTION func_player_info( inTable STRING, inTeam STRING)
RETURNS table tmp_hockey(Number BIGINT, Name STRING, Team STRING)
 AS
RETURN (执行 IMMEDIATE 'SELECT number,Name,Team
FROM '||inTable||' WHERE team = ?' USING VALUES inTeam);
END_FUNCTION;
@
 DELIMITER ;

选择 * FROM func_player_info('HOCKEY','Bruins') LIMIT 2;

 NUMBER        NAME        TEAM
 ------- ---------------- ------

    1    MAX SUMMIT       Bruins
   11    GREGORY CAMPBELL Bruins
示例5:任何过程也可以在触发器,存储过程或UDF之外运行。
if (select 1 from dual) update hockey set position = 'Goalie' where id = 20; end_if;