mysql 返回错误,mysql过程函数返回错误

I'm trying to create a mysql function, but i keep getting following error. What is the problem with my query?

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BEGIN INSERT INTO order (carID, clientID) Select car.id, client.id FROM' at line 2

My query:

DELIMITER //

CREATE FUNCTION orderCreate(LicenseNumber varchar(30), Phonenumber varchar(20))

BEGIN

INSERT INTO `order` (carID, clientID) Select car.id, client.id FROM car, client, owns WHERE car.licensenumber = LicenseNumber AND client.phonenumber = Phonenumber AND car.id = owns.carID AND owns.clientID = client.id;

RETURN mysql_insert_id();

END //

DELIMITER ;

解决方案

FUNTION definition signature must have a RETURNS clause.

Your code did not use it.

And you can't use mysql_insert_id() within MySQL but PHP.

You have to use LAST_INSERT_ID().

Function:

delimiter //

drop function if exists orderCreate //

CREATE FUNCTION orderCreate( _LicenseNumber varchar(30), _Phonenumber varchar(20) )

RETURNS INTEGER

BEGIN

INSERT

INTO `order` ( carID, clientID )

SELECT car.id, client.id

FROM car, client, owns

WHERE car.licensenumber = _LicenseNumber

AND client.phonenumber = _Phonenumber

AND car.id = owns.carID

AND owns.clientID = client.id;

RETURN LAST_INSERT_ID();

END;

//

delimiter ;

And it would be a better practice to use different function/procedural parameter names over column names. Because unless used table name qualifiers with column names, there would arise an ambiguity in recognizing them and priority may be given to parameter names over column names.

select phonenumber from client;

Above statement may result all rows with input value PhoneNumber but not what you expected.

Example:

mysql> create procedure sp_so_q23838311( in deptno int )

-> select deptno from department

-> union all

-> select department.deptno from department;

Query OK, 0 rows affected (0.00 sec)

mysql> select deptno from department;

+--------+

| deptno |

+--------+

| 10 |

| 20 |

| 30 |

| 40 |

+--------+

4 rows in set (0.00 sec)

mysql> call sp_so_q23838311( 20 );

+--------+

| deptno |

+--------+

| 20 |

| 20 |

| 20 |

| 20 |

| 10 |

| 20 |

| 30 |

| 40 |

+--------+

8 rows in set (0.15 sec)

CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}

SONAME shared_library_name