I want to create a simple MySQL function, I have this MySQL procedure:
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?
this is a mysql function example. I hope it helps. (I have not tested it yet, but should work)
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;//
Open the mysql terminal:
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
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:
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.
Try to change CREATE FUNCTION F_TEST(PID INT) RETURNS VARCHAR this portion to
CREATE FUNCTION F_TEST(PID INT) RETURNS TEXT
and change the following line too.
DECLARE NAME_FOUND TEXT DEFAULT "";
It should work.
VARCHAR is not a valid type, VARCHAR(50) or TEXT are.