SQL 方言参考/过程语言/用户定义函数
< SQL 方言参考
| 数据库 | 创建语法 | 调用 |
|---|---|---|
| DB2 |
CREATE FUNCTION function_name
(input_parameter_name datatype, ...)
RETURNS return_type
BEGIN
/* SQL code */
END
|
VALUES function_name(...)
or
SELECT function_name(...) FROM ... WHERE function_name(...) = ...
|
| MonetDB |
CREATE [ OR REPLACE ] FUNCTION function_name
(input_parameter_name datatype, ...)
RETURNS return_type /* return type can also be a table structure */
BEGIN
/* SQL code */
END
CREATE [ OR REPLACE ] FUNCTION function_name
(input_parameter_name datatype, ...)
RETURNS return_type /* return type can also be a table structure */
LANGUAGE { C | CPP | R | PYTHON3 | PYTHON3_MAP }
{
function_body_in_language_syntax
}
CREATE [ OR REPLACE ] FUNCTION function_name
(input_parameter_name datatype, ...)
RETURNS return_type /* return type can also be a table structure */
EXTERNAL NAME MAL_function_name
|
SELECT function_name(...)
/* when the return type of the function is a table, following is allowed */
SELECT ... FROM function_name(...)
|
| MySQL |
DELIMITER $$
CREATE FUNCTION function_name
(input_parameter_name datatype, ... )
RETURNS datatype
BEGIN
RETURN
/* SQL code */
END$$
DELIMITER ;
|
SELECT function_name(...)
|
| PostgreSQL |
CREATE FUNCTION function_name
(input_parameter_name datatype, ...)
RETURNS datatype
AS $$
DECLARE
variable_name datatype;
BEGIN
/* SQL code */
END;
$$ LANGUAGE plpgsql;
|
SELECT function_name(...)
|
| Firebird |
UDF 是在外部工具中编写并在可执行形式下编译的。 DECLARE EXTERNAL FUNCTION function_name [datatype, ...]
RETURNS datatype
ENTRY_POINT 'entryname'
MODULE_NAME 'modulename';
|
SELECT function_name(...)
|
| OpenLink Virtuoso | ||
| Oracle |
CREATE OR REPLACE my_function(p_contract IN VARCHAR2, p_org_id IN VARCHAR2)
RETURN DATE
AS
l_ret_eff_date DATE := SYSDATE;
BEGIN
RETURN l_ret_eff_date;
END;
/
|
SELECT my_function('PARM1', 'ORG1')
FROM dual;
|
| SQLite |
N/A |
N/A |
| SQL Server |