MySQL，创建一个简单的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14606900/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL, create a simple function
提问by Dembele
I want to create a simple MySQL function, I have this MySQL procedure:
我想创建一个简单的 MySQL 函数，我有这个 MySQL 程序：
CREATE PROCEDURE getUser(gU INT)
SELECT * FROM Company
WHERE id_number = gU;
CALL getUser(2);
I need some help making this into a MySQL function. What are the pros and cons of using a function over a procedure?
我需要一些帮助才能将它变成 MySQL 函数。使用函数而不是过程的优缺点是什么？
回答by Alejandro Bastidas
this is a mysql function example. I hope it helps. (I have not tested it yet, but should work)
这是一个 mysql 函数示例。我希望它有帮助。（我还没有测试过，但应该可以工作）
DROP FUNCTION IF EXISTS F_TEST //
CREATE FUNCTION F_TEST(PID INT) RETURNS VARCHAR
BEGIN
/*DECLARE VALUES YOU MAY NEED, EXAMPLE:
DECLARE NOM_VAR1 DATATYPE [DEFAULT] VALUE;
*/
DECLARE NAME_FOUND VARCHAR DEFAULT "";
SELECT EMPLOYEE_NAME INTO NAME_FOUND FROM TABLE_NAME WHERE ID = PID;
RETURN NAME_FOUND;
END;//
回答by Eric Leschinski
MySQL function example:
MySQL函数示例：
Open the mysql terminal:
打开mysql终端：
el@apollo:~$ mysql -u root -pthepassword yourdb
mysql>
Drop the function if it already exists
如果函数已经存在，则删除它
mysql> drop function if exists myfunc;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Create the function
创建函数
mysql> create function hello(id INT)
-> returns CHAR(50)
-> return 'foobar';
Query OK, 0 rows affected (0.01 sec)
Create a simple table to test it out with
创建一个简单的表来测试它
mysql> create table yar (id INT);
Query OK, 0 rows affected (0.07 sec)
Insert three values into the table yar
将三个值插入表 yar
mysql> insert into yar values(5), (7), (9);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
Select all the values from yar, run our function hello each time:
从 yar 中选择所有值，每次运行我们的函数 hello：
mysql> select id, hello(5) from yar;
+------+----------+
| id | hello(5) |
+------+----------+
| 5 | foobar |
| 7 | foobar |
| 9 | foobar |
+------+----------+
3 rows in set (0.01 sec)
Verbalize and internalize what just happened:
将刚刚发生的事情表达出来并内化：
You created a function called hello which takes one parameter. The parameter is ignored and returns a CHAR(50)containing the value 'foobar'. You created a table called yar and added three rows to it. The select statement runs the function hello(5)for each row returned by yar.
您创建了一个名为 hello 的函数，它接受一个参数。该参数被忽略并返回一个CHAR(50)包含值 'foobar' 的值。您创建了一个名为 yar 的表并向其中添加了三行。select 语句hello(5)为 yar 返回的每一行运行该函数。
回答by jewelhuq
Try to change CREATE FUNCTION F_TEST(PID INT) RETURNS VARCHARthis portion to
CREATE FUNCTION F_TEST(PID INT) RETURNS TEXT
尝试CREATE FUNCTION F_TEST(PID INT) RETURNS VARCHAR将此部分更改为
CREATE FUNCTION F_TEST(PID INT) RETURNS TEXT
and change the following line too.
并更改以下行。
DECLARE NAME_FOUND TEXT DEFAULT "";
DECLARE NAME_FOUND TEXT DEFAULT "";
It should work.
它应该工作。
