创建功能

创建功能 •创建用户定义的函数(UDF)

句法

CREATE [OR REPLACE] FUNCTION [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;
}

描述

The 创建功能 statement is used to create user defined functions. There are two types of functions.

  • A scalar function returns a simple value. It can be used in the 选择 list of a 选择 query or a view. A scalar function can also be used in a WHERE clause.

  • A table function returns a table, that is, multiple rows with multiple values. It can be used in place of a database table object in a FROM clause.

When the OR REPLACE clause is used for an existing function name, the effect is that of dropping the existing function and creating a new one with the given specification. OR REPLACE does not have any effect on the command if a function with the specified name does not already exist.

创建功能 也可用于创建在Java中编写的存储过程。看 下面和 使用嵌入式Java存储过程.

Only users assigned the system DBA role can create a function (see 授予)。 If schema is provided, then the function is created in the specified schema. Otherwise, it is created in the current schema. The name of the function must not match any existing function with the same number of input arguments in the same schema. However, multiple functions with the same name can be defined in the same schema if they contain different numbers of arguments. This is called overloading (see 例2.)。

The user that creates the function becomes the owner of the function. The owner must use the 授予 statement to give ALTER执行 privileges to other users in order for them to be able to change or execute the function. Any user that has permission to alter a function can also remove the function. If a user drops and recreates a function then the new function is not the same entity as the old function. That user is the owner of the recreated function and must 授予 appropriate privileges to other users.

Functions are not case sensitive. For example, a function created with the name Fnc_ToDo can be called by select FNC_TODO() from dual.

使用创建或替换语句时,与两个不同的事务同时创建相同的数据库对象时,可能会返回以下错误:
duplicate value in unique index ...
如果通过基于表达式的索引调用用户定义的函数,则任何删除或修改函数的尝试都会使用错误警告,但不会重新创建索引。

争论

用户定义函数的每个参数都被隐式声明为输入参数,并且不会被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.

例5. illustrates returning the table using RETURN (SELECT…​). There are times when a single 选择 statement cannot generate the result set. 例6. illustrates returning the table using 插 INTO…​ 和 a separate RETURN statement once all of the data is inserted. There can be one or more 插 INTO statements prior to the RETURN statement. This materializes the entire table in memory. The SQL system property MAX_MATERIALIZED_QUERY_SIZE 设置实现物化查询可以使用的最大字节量。默认值为64 MB。

确定性与不是确定性的

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.

NOODB未验证功能是否实际确定。这是用户必须确保的东西。如果该函数标记为确定性,Nuodb会因此将其视为,而不管函数实际上。

缓存 below.

名称解析

函数的名称解析范围优先级澳门比分网址函数通过用户定义的函数。如果用户以与现有澳门比分网址函数相同的名称和参数计数创建函数,则将调用澳门比分网址功能。要调用用户定义的函数,必须使用完全限定的名称。

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程序语言 想要查询更多的信息。

缓存

Deterministic user defined functions are stored in an in-memory cache. The size of this cache is limited by the system property UDF_CACHE_SIZE. (See SQL澳门比分网址属性。)缓存存储任何类型的用户定义函数,无论如何实现它(Java或SQL)。确定性用户定义的函数是将在使用相同参数运行时返回相同结果的函数。

NOODB未验证功能是否实际确定。这是用户必须确保的东西。如果该函数标记为确定性,Nuodb会因此将其视为,而不管函数实际上。

当缓存已满时,已删除最旧的条目,因此如果再次使用参数组合,则将执行用户定义的功能代码以计算结果。默认值为50.一个例子如下:

  • 表包含值(1),(2),(3),(1),(1),(4),(1),(2),(1),(20),(3),( 1)

  • 执行“选择 process(value) FROM data” where process is a user defined function.

  • With a default UDF_CACHE_SIZE of 50, process() is executed only five times, for these argument values: 1, 2, 3, 4, 20.

  • With a UDF_CACHE_SIZE of two, process() gets invoked nine times, for these values: 1, 2, 3, 1, 4, 2, 20, 3, 1.

    拨电至 process() 价值 缓存

    1

    1

    1

    2

    2

    1,2

    3

    3

    2,3

    4

    1

    3,1

    -

    1

    3,1

    5

    4

    1,4

    -

    1

    4,1

    6

    2

    1,2

    -

    1

    2,1

    7

    20

    1,20

    8

    3

    20,3

    9

    1

    3,1

  • With a UDF_CACHE_SIZE of 0, process() is invoked all the time: for 1, 2, 3, 1, 1, 4, 1, 2, 1, 20, 3, 1.

澳门比分网址表

The body of the function is stored in SYSTEM.FUNCTIONS in the FUNCTIONTEXT column. See 功能澳门比分网址表描述例1 below.

参数

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. Functions cannot be created in the SYSTEM schema. This schema is reserved for database metadata.

name

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

arg_name

输入参数的变量名称。

arg_type

输入参数的SQL数据类型。

return_data_type

从函数返回的值的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.

例子

例1: Using a function as an item in a 选择 list.

如果函数声明一个或多个参数,则它们将显示为函数的所有语句的变量。

删除功能 IF EXISTS func_is_date;
SET DELIMITER @
CREATE FUNCTION func_is_date (i_date string)
       RETURNS BOOLEAN
       DETERMINISTIC
       LANGUAGE SQL
       SECURITY INVOKER
AS
   VAR l_out BOOLEAN = 'TRUE';
   VAR l_timestamp TIMESTAMP;
   try
       l_timestamp = (SELECT cast(DATE(i_date) as timestamp) FROM DUAL);
   catch(error)
       l_out = 'FALSE';
   end_try;
   RETURN l_out;
END_FUNCTION;
@
SET DELIMITER ;

SELECT func_is_date('2014-08-01') from dual;
 [TEST.FUNC_IS_DATE]
 -------------------
         TRUE

SELECT func_is_date('2014-09-45') from dual;
 [TEST.FUNC_IS_DATE]
 --------------------
        FALSE

+

放 OUTPUT VERTICAL;
SELECT * FROM system.functions;
==================================== Row #1 ====================================
SCHEMA: USER
FUNCTIONNAME: FUNC_IS_DATE
PARAMETERCOUNT: 2
CREATOR: DBA
FUNCTIONTYPE: 0
FUNCTIONTEXT: (i_date string, i_format string )        RETURNS BOOLEAN
DETERMINISTIC        LANGUAGE SQL        SECURITY INVOKER AS
VAR l_out BOOLEAN = 'TRUE';    VAR l_timestamp TIMESTAMP;
try        l_timestamp = (SELECT date_from_str(i_date, i_format) FROM DUAL);
catch(error)        l_out = 'FALSE';    end_try;
RETURN l_out; END_FUNCTION; ;

VERSION: 1
示例2:重载函数名称。
 DELIMITER @
CREATE FUNCTION function1 (in_parm1 STRING)
      RETURNS STRING
AS
      THROW 'function1 with 1 parameter';
END_FUNCTION;
@

CREATE FUNCTION function1 (in_parm1 STRING, in_parm2 STRING)
      RETURNS STRING
AS
      THROW 'function1 with 2 parameters';
END_FUNCTION;
@
 DELIMITER ;

选择 function1('string1') FROM DUAL;
function1 with 1 parameter

选择 function1('string1','string2') FROM DUAL;
function1 with 2 parameters

DROP FUNCTION function1;
/* function TEST.FUNCTION1 is an overloaded function: use the syntax */
/* <schema>.<name>/<parameter count> to identify the one to be dropped */

DROP FUNCTION function1/1;
DROP FUNCTION function1/2;
Example 3: Using a function as an item in a 选择 list and in a WHERE clause.

选择2010年为Chicago Blackhawks播放的所有玩家:

放 DELIMITER @
创建功能 fnc_position (i_position STRING)
      RETURNS string
AS
      RETURN (SELECT CASE i_position
                WHEN 'C' then 'Center'
                WHEN 'D' then 'Defense'
                WHEN 'G' then 'Goalie'
                WHEN 'L' then 'Left Wing'
                WHEN 'R' then 'Right Wing'
                WHEN 'F' then 'Forward'
                ELSE 'UnKnown'
               END FROM DUAL);
END_FUNCTION;
@
创建功能 fnc_get_team (i_playerid STRING, i_year STRING)
      RETURNS string
AS
      RETURN (SELECT t.name FROM SCORING s, TEAMS t
        WHERE s.playerid = i_playerid
          AND s.year = i_year
          AND s.stint = 1
          AND s.year = t.year
          AND s.teamid = t.teamid);
END_FUNCTION;
@
SET DELIMITER ;

SELECT FIRSTNAME, LASTNAME, fnc_position(position) as position_name
FROM PLAYERS
WHERE fnc_get_team(playerid,'2010') = 'Chicago Blackhawks'
 ORDER BY fnc_position(position), lastname, firstname;
 FIRSTNAME   LASTNAME   POSITION_NAME
 ---------- ----------- --------------
  Dave      Bolland       Center
  Evan      Brophey       Center
  Jacob     Dowell        Center
  ...
Example 4: Using a function as an item in a 选择 list when creating a view.

在不首先丢弃视图的情况下,无法删除在视图中使用的函数。

In the following example the VIEW is created first, then the rows are ordered in the output using an ORDER BY in the SQL 选择 statement. It is not good practice to use ORDER BY in a VIEW definition. To improve performance, the NuoDB optimizer will ignore ordering in a VIEW, unless a LIMIT or OFFSET is supplied in the VIEW definition, along with the ORDER BY.
删除功能 IF EXISTS fnc_position;
SET DELIMITER @
创建功能 fnc_position (i_position STRING)
      RETURNS string
AS
      RETURN (SELECT CASE i_position
                WHEN 'C' then 'Center'
                WHEN 'D' then 'Defense'
                WHEN 'G' then 'Goalie'
                WHEN 'L' then 'Left Wing'
                WHEN 'R' then 'Right Wing'
                WHEN 'F' then 'Forward'
                ELSE 'UnKnown'
               END FROM DUAL);
END_FUNCTION;
@
SET DELIMITER ;

CREATE VIEW vw_players_list AS
 SELECT FIRSTNAME, LASTNAME, fnc_position(position) as position_name
  FROM PLAYERS;

SELECT * FROM vw_players_list ORDER BY position_name;
   FIRSTNAME     LASTNAME   POSITION_NAME
 ------------- ------------ --------------
 Antti         Aalto            Center
 Reg           Abbott           Center
 Sid           Abel             Center
  ...

DROP FUNCTION IF EXISTS fnc_position;
#Error: function "USER.FNC_POSITION/1" is referenced by view USER.VW_PLAYERS_LIST and cannot be dropped
DROP VIEW vw_players_list;
DROP FUNCTION IF EXISTS fnc_position;
例5.: Using a table function in a FROM clause. This uses a RETURN (SELECT…​.) statement.
删除功能 IF EXISTS fnc_tbl_get_teams;
SET DELIMITER @
创建功能 fnc_tbl_get_teams (i_PlayerId STRING)
      RETURNS TABLE fnc_tbl_get_teams (
          PlayerId     STRING,
          year         INTEGER,
          position     VARCHAR(4),
          team_name    VARCHAR(48),
          stint        INTEGER,
          gamesplayed  INTEGER,
          conferenceid VARCHAR(2),
          Divisionid   VARCHAR(2) )
AS
    RETURN ( SELECT
              CAST(s.playerId AS STRING) AS PLAYERID,
              s.year,
              s.position,
              t.name AS TEAM_NAME,
              s.stint,
              s.gamesplayed,
              t.conferenceid,
              t.divisionid FROM scoring s, teams t
            WHERE s.playerId = i_PlayerId
               AND s.year = t.year
               AND s.teamid = t.teamid );
END_FUNCTION;
@

SET DELIMITER ;

SELECT p.playerid,
    p.firstname as FIRST,
    p.lastname as LAST,
    f.year,
    f.position as POS,
    f.team_name,
    f.stint,
    f.gamesplayed as GAMES,
    f.conferenceid as CONF,
    f.divisionid as DIV
FROM players p, fnc_tbl_get_teams(p.playerid) f
 WHERE p.firstnhl = 2010 和 p.lastnhl = 2011
 ORDER BY p.playerid, f.year, f.stint;
 PLAYERID  FIRST LAST  YEAR  POS     TEAM_NAME       STINT  GAMES CONF DIV
 --------- ----- ----- ----- --- ------------------- ------ ----- ---- ---
 adamlu01  Luke  Adam  2010   L  Buffalo Sabres        1      19   EC  NE
 adamlu01  Luke  Adam  2011   L  Buffalo Sabres        1      52   EC  NE
 aulieke01 Keith Aulie 2010   D  Toronto Maple Leafs   1      40   EC  NE
 aulieke01 Keith Aulie 2011   D  Toronto Maple Leafs   1      17   EC  NE
 aulieke01 Keith Aulie 2011   D  Tampa Bay Lightning   2      19   EC  SE
...
例6.: Using a table function in a FROM clause. This uses an 插 INTO…​SELECT…​ statement with a RETURN specification.
DROP FUNCTION IF EXISTS fnc_tbl_get_teams;
 DELIMITER @
CREATE FUNCTION fnc_tbl_get_teams (i_PlayerId STRING)
      RETURNS TABLE fnc_tbl_get_teams (
        PlayerId     STRING,
        year         STRING,
        position     STRING,
        team_name    STRING,
        stint        STRING,
        games_played INTEGER,
        conference   STRING,
        Division     STRING )
AS
       INTO fnc_tbl_get_teams
        选择 s.playerId,
               s.year,
               s.position,
               t.name,
               s.stint,
               s.gamesplayed,
               t.conferenceid,
               t.divisionid
          FROM scoring s, teams t
         WHERE s.playerId = i_PlayerId
           AND s.year = t.year
           AND s.teamid = t.teamid;
      RETURN;
END_FUNCTION;
@

 DELIMITER ;

选择 p.playerid,
    p.firstname as FIRST,
    p.lastname as LAST,
    f.year,
    f.position as POS,
    f.team_name,
    f.stint,
    f.games_played as GAMES,
    f.conference as CONF,
    f.division as DIV
FROM players p, fnc_tbl_get_teams(p.playerid) f
 WHERE p.firstnhl = 2010  p.lastnhl = 2011
 ORDER BY p.playerid, f.year, f.stint;
 PLAYERID   FIRST LAST YEAR  POS        TEAM_NAME      STINT  GAMES CONF DIV
 --------- ------ ---- ----- --- --------------------- ------ ----- ---- ---
 adamlu01  Luke   Adam  2010  L   Buffalo Sabres          1   19     EC  NE
 adamlu01  Luke   Adam  2011  L   Buffalo Sabres          1   52     EC  NE
 aulieke01 Keith  Aulie 2010  D   Toronto Maple Leafs     1   40     EC  NE
 aulieke01 Keith  Aulie 2011  D   Toronto Maple Leafs     1   17     EC  NE
 aulieke01 Keith  Aulie 2011  D   Tampa Bay Lightning     2   19     EC  SE
...
示例7:函数名称为表和表别名。
删除功能 IF EXISTS fnc_format_name;
SET DELIMITER @
创建功能 fnc_format_name (team_id STRING)
  RETURNS TABLE output (fullname VARCHAR(50))
AS
  RETURN ( SELECT firstname||' '||lastname AS fullname
             FROM players
            WHERE playerid IN (SELECT playerid
                                 FROM scoring
                                WHERE teamid = team_id)
         );
END_FUNCTION;
@
SET DELIMITER ;

SELECT fnc_format_name.fullname FROM fnc_format_name('CHI') LIMIT 3;
   FULLNAME
 -------------
 Clarence Abel
 Sid Abel
 Craig Adams

有关更多信息和示例,请参阅 SQL程序语言.