一个简单的用户函数却不会写

[青出于蓝]

我要编写一个用户函数，并在函数内部进行一些简单判断，并返回值。但居然查找了N多资料都无法实现这么一个简单的需求，编译执行时会提示语法错误。求各高手帮助啊！

CREATE FUNCTION f_MyFun1(Param1 TEXT)
RETURNS INT

IF (Param1 LIKE 'ABC') THEN
RETURN 1;
END IF;

IF (Param1 LIKE 'DEF') THEN
RETURN 2;
ELSE
RETURN 3;
END IF;

上在只是例子，但不要告诉我使用IF()函数。意思是函数里面有会一些判断（IF），并根据这些判断返回一些值。我觉得上面的代码应该是可以的，但执行时就是会提示语法错误。

[小在在]

提示什么错误？ 有没有开启创建函数的功能？

mysql> show variables like '%func%';
+-----------------------------------------+-------+
| Variable_name | Value |
+-----------------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+-----------------------------------------+-------+
1 row in set (0.02 sec)

二、如果Value处值为OFF，则需将其开启。
mysql> set global log_bin_trust_function_creators=1;

[小在在]

少了begin,end

CREATE FUNCTION f_MyFun1(Param1 TEXT)
  RETURNS INT
BEGIN
  IF (Param1 LIKE 'ABC') THEN
    RETURN 1;
  END IF;

  IF (Param1 LIKE 'DEF') THEN
    RETURN 2;
  ELSE
    RETURN 3;
  END IF;
END

[WWWWA]

DELIMITER $$ CREATE FUNCTION f_MyFun1(Param1 TEXT) RETURNS INT BEGIN IF (Param1 LIKE 'ABC') THEN RETURN 1; END IF; IF (Param1 LIKE 'DEF') THEN RETURN 2; ELSE RETURN 3; END IF; END$$ DELIMITER ;

[ACMAIN_CHM]

少了begin / end 这个在MYSQL的官方免费手册中有例子啊。

引用

12.8. MySQL Compound-Statement Syntax This section describes the syntax for the BEGIN ... END compound statement and other statements that can be used in the body of stored programs: Stored procedures and functions, triggers, and events. These objects are defined in terms of of SQL code that is stored on the server for later invocation (see Chapter 18, Stored Programs and Views). 12.8.1. BEGIN ... END Compound Statement Syntax [begin_label:] BEGIN [statement_list] END [end_label] BEGIN ... END syntax is used for writing compound statements, which can appear within stored programs. A compound statement can contain multiple statements, enclosed by the BEGIN and END keywords. statement_list represents a list of one or more statements, each terminated by a semicolon (;) statement delimiter. statement_list is optional, which means that the empty compound statement (BEGIN END) is legal. Use of multiple statements requires that a client is able to send statement strings containing the ; statement delimiter. This is handled in the mysql command-line client with the delimiter command. Changing the ; end-of-statement delimiter (for example, to //) allows ; to be used in a program body. For an example, see Section 18.1, “Defining Stored Programs”. A compound statement can be labeled. end_label cannot be given unless begin_label also is present. If both are present, they must be the same. The optional [NOT] ATOMIC clause is not supported. This means that no transactional savepoint is set at the start of the instruction block and the BEGIN clause used in this context has no effect on the current transaction. 12.8.2. DECLARE Syntax The DECLARE statement is used to define various items local to a program: Local variables. See Section 12.8.3, “Variables in Stored Programs”. Conditions and handlers. See Section 12.8.4, “Conditions and Handlers”. Cursors. See Section 12.8.5, “Cursors”. DECLARE is allowed only inside a BEGIN ... END compound statement and must be at its start, before any other statements. Declarations must follow a certain order. Cursors must be declared before declaring handlers, and variables and conditions must be declared before declaring either cursors or handlers. 12.8.3. Variables in Stored Programs You may declare and use variables within stored programs. 12.8.3.1. DECLARE for Local Variables DECLARE var_name [, var_name] ... type [DEFAULT value] This statement is used to declare local variables within stored programs. To provide a default value for the variable, include a DEFAULT clause. The value can be specified as an expression; it need not be a constant. If the DEFAULT clause is missing, the initial value is NULL. Local variables are treated like stored routine parameters with respect to data type and overflow checking. See Section 12.1.12, “CREATE PROCEDURE and CREATE FUNCTION Syntax”. Local variable names are not case sensitive. The scope of a local variable is within the BEGIN ... END block where it is declared. The variable can be referred to in blocks nested within the declaring block, except those blocks that declare a variable with the same name. 12.8.3.2. Variable SET Statement SET var_name = expr [, var_name = expr] ... The SET statement in stored programs is an extended version of the general SET statement (see Section 12.5.4, “SET Syntax”). Each var_name may refer to a local variable declared inside a stored program, a system variable, or a user-defined variable. The SET statement in stored programs is implemented as part of the pre-existing SET syntax. This allows an extended syntax of SET a=x, b=y, ... where different variable types (locally declared variables, global and session system variables, user-defined variables) can be mixed. This also allows combinations of local variables and some options that make sense only for system variables; in that case, the options are recognized but ignored. 12.8.3.3. SELECT ... INTO Statement SELECT col_name [, col_name] ... INTO var_name [, var_name] ... table_expr SELECT ... INTO syntax enables selected columns to be stored directly into variables. The query should return a single row. If the query returns no rows, a warning with error code 1329 occurs (No data), and the variable values remain unchanged. If the query returns multiple rows, error 1172 occurs (Result consisted of more than one row). If it is possible that the statement may retrieve multiple rows, you can use LIMIT 1 to limit the result set to a single row. SELECT id,data INTO x,y FROM test.t1 LIMIT 1; User variable names are not case sensitive. See Section 8.4, “User-Defined Variables”. In the context of SELECT ... INTO statements that occur as part of events executed by the Event Scheduler, diagnostics messages (not only errors, but also warnings) are written to the error log, and, on Windows, to the application event log. For additional information, see Section 18.4.5, “Event Scheduler Status”. 12.8.3.4. Scope and Resolution of Local Variables The scope of a local variable is within the BEGIN ... END block where it is declared. The variable can be referred to in blocks nested within the declaring block, except those blocks that declare a variable with the same name. Local variables are within scope only during stored routine execution, so references to them are disallowed within prepared statements because those are global to the current session and the variables might have gone out of scope when the statement is executed. For example, SELECT ... INTO local_var cannot be used as a prepared statement. Local variable names should not be the same as column names. If an SQL statement, such as a SELECT ... INTO statement, contains a reference to a column and a declared local variable with the same name, MySQL currently interprets the reference as the name of a variable. For example, in the following statement, xname is interpreted as a reference to the xname variable rather than the xname column: CREATE PROCEDURE sp1 (x VARCHAR(5)) BEGIN DECLARE xname VARCHAR(5) DEFAULT 'bob'; DECLARE newname VARCHAR(5); DECLARE xid INT; SELECT xname,id INTO newname,xid FROM table1 WHERE xname = xname; SELECT newname; END; When this procedure is called, the newname variable returns the value 'bob' regardless of the value of the table1.xname column. See also Section D.1, “Restrictions on Stored Routines, Triggers, and Events”. 12.8.4. Conditions and Handlers Certain conditions may require specific handling. These conditions can relate to errors or warnings, as well as to general flow control inside a stored program.

[青出于蓝]

谢谢各位，WWWWA的是正解。