改变功能

改变功能 •更改用户定义的函数(UDF)

句法

改变功能 [schema.]name
    [ ( arg_name arg_type [ ,arg_name arg_type ],... ) ]
    RETURNS
    {
        return_data_type
    |
        TABLE table_name ( column_name column_type [ ,column_name column_type ]... )
    }
    [ DETERMINISTIC | NOT DETERMINISTIC ]
    [ LANGUAGE SQL | LANGUAGE JAVA ]
    [ SECURITY INVOKER | SECURITY DEFINER ]
{
  EXTERNAL 'jar_id:method_name'
|
  AS
    sql_code_block
  END_FUNCTION;
}

描述

ALTER FUNCTION uses the exact same syntax as 创建功能 (see 创建功能) and will succeed only if the function already exists. To change a function, you need to supply the body and the parameters of the function. You cannot change the number of parameters defined for a function using the 改变功能 since this would actually be creating a new function.

改变功能 does not modify the privileges, nor the owner, of a function. If User A, for instance, creates the function and then grants to User B the right to modify it, the function will still have User A as the creator (important if SECURITY DEFINER is specified) and all the rights that User A granted User B.

争论

用户定义函数的每个参数都被隐式声明为输入参数,并且不会被UDF修改。

返回值

The RETURNS clause is required. If a function returns a single data type value, it is a scalar function. If a function returns a table, it is a table function. A function defined as a table function will return multiple rows that contain multiple values.

标量函数返回一个值。这可以用于SELECT语句和/或其可以在WHERE子句中使用。

A table function is used in the FROM clause of a 选择 statement. There are two ways to return the table, RETURN (SELECT…​) which creates a streaming result set that fetches the rows on demand and 插入 INTO…​ with a separate RETURN statement which will return the table once all the data is inserted. When using a table function, you cannot use the same function multiple times as items in the FROM clause, even if the parameter values being passed to the function are different.

确定性与不是确定性的

A deterministic UDF means that the function is guaranteed to return the same results given the same arguments. A UDF is not deterministic when it may return different results on successive calls with the same arguments. A query using a UDF declared with NOT DETERMINISTIC will re-evaluate the function at every row. The default is NOT DETERMINISTIC.

语言

The language for a stored procedure, user defined function or trigger may be SQL or Java. This is specified by declaring the procedure, function or trigger with LANGUAGE SQL or LANGUAGE JAVA. The default language, if none is specified, is SQL. SQL stored procedures, UDFs, and triggers are defined by sql_code_block. See SQL程序语言 欲获得更多信息。

Stored procedures, user defined functions and triggers written in Java must use the EXTERNAL keyword. See 使用嵌入式Java存储过程.

安全

您可以声明存储过程或用户定义的函数,以两种不同的方式在各种不同的方式上工作’影响数据库对象的权限。可以以两种方式之一声明过程或功能:

SECURITY INVOKER

检查调用用户的权限。如果存储过程或用户定义的函数正在插入,选择和/或更新一个或多个表,则调用用户必须具有对每个表的操作具有适当的特权。

SECURITY DEFINER

Specifies that the stored procedure or user defined function is to be executed with the privileges of the user that created it. For example, the calling user may not have privileges to 选择 or 插入 INTO tables when referencing them outside the stored procedure or user defined function, but will be allowed access when executing the stored procedure or function. If a security level is not specified, this is the default.

谁获取程序上的执行权限可以访问该过程的创建者的相同表/架构/序列/ etc ..即使当前用户仍然是调用者也是如此。

外部定义

Declaring a UDF as EXTERNAL is done to describe a dynamically loadable Java function that is to be executed. For LANGUAGE JAVA, the EXTERNAL function defined by sp_routine must be in the format classid:fully_qualified_class.method, where classid is the ID used in the 创建javaclass. command used to upload to the NuoDB server the JAR with the defined code. See 创建javaclass..

SQL程序语言 - 改变功能 body

If defining this UDF in SQL, sql_code_block is one or more SQL statements. See SQL程序语言 欲获得更多信息。

系统表

The body of the function is stored in SYSTEM.FUNCTIONS in the FUNCTIONTEXT column. See 功能系统表描述.

参数

schema

Name of the schema in which the function will be created. The default is the current schema as defined by the 使用 command. If schema is provided, and the schema does not exist, it will be created.

name

可选地,函数的架构合格名称。

arg_name

输入参数的变量名称。

arg_type

输入参数的SQL数据类型。

return_data_type

SQL data type of the value being returned from the function. This can be used as an item in a 选择 statement and/or this can be used in a WHERE clause.

expression

指定表示要返回的信息的表达式。这可以是函数或数学运算符的形式(参见 SQL函数和操作或表达。函数也可以返回函数。支持对相同功能的递归调用。

table_name

从函数返回的表的名称。

column_name

Describes one or more columns being returned from the function. When using a function in a FROM clause, these are the column names in the result set.

column_type

SQL data type for each column_name in the table being returned from the function. This data type must be compatible with the data type of the source data. For example, a VARCHAR data type can be selected or inserted into a STRING data type, but not into an INTEGER data type.

When using the nuosql command line utility to create or alter multi-statement functions, the 放 DELIMITER command must be used prior to defining the function. This new delimiter is used to indicate the end of the function definition and will execute the 创建功能 statement. See the examples below.

例子

Use 改变功能 to change the RETURNS data type. This example also shows how the number of input parameters cannot be changed, only the data type of those input parameters.

 放  DELIMITER @
CREATE FUNCTION fnc_test_function (i_number integer)
  RETURNS INTEGER
AS
  RETURN i_number + 1;
END_FUNCTION;
@

SET DELIMITER ;

select fnc_test_function(1) from dual;
 FNC_TEST_FUNCTION
 ------------------
         2

SET DELIMITER @
ALTER FUNCTION fnc_test_function (i_number integer, i_increment integer)
  RETURNS INTEGER
AS
  RETURN i_number + i_increment;
END_FUNCTION;
@
ERROR: function " 使用 R.FNC_TEST_FUNCTION"/2 doesn't exists
ALTER FUNCTION fnc_test_function(i_number integer)
  RETURNS STRING
AS
  RETURN 'value plus one equals '|| cast(i_number + 1 as string);
END_FUNCTION;
@
SET DELIMITER ;

select fnc_test_function(2) from dual;
    FNC_TEST_FUNCTION
 -----------------------
 value plus one equals 3

SET DELIMITER @
ALTER FUNCTION fnc_test_function(i_number string)
  RETURNS integer
AS
  RETURN cast(i_number as integer) + 1;
END_FUNCTION;
@
SET DELIMITER ;

select fnc_test_function(2) from dual;
 FNC_TEST_FUNCTION
 ------------------
         3