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?
The syntax to create a function in MySQL is: CREATE FUNCTION function_name [ (parameter datatype [, parameter datatype]) ] RETURNS return_datatype BEGIN declaration_section executable_section END; function_name.
MySQL provides a set of built-in function which performs particular tasks for example the CURDATE() function returns the current date. You can create a stored function using the CREATE FUNCTION statement.
It's very simple to run the MySQL function. Instead of procedure we can add any multiple line function in above example. @Sebastianb, under the "delimiter //" prompt we can call functions as well. using CALL is just an example for reference of procedures.
The CREATE FUNCTION statement is used for creating a stored function and user-defined functions. A stored function is a set of SQL statements that perform some operation and return a single value. Just like Mysql in-built function, it can be called from within a Mysql statement.
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;//
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With