What's the differences between stored procedures, functions and routines?

mysql terminology
Yang picture Yang · Apr 21, 2010 · Viewed 52.8k times · Source

In MySQL database context, what is the difference among these 3 terms:

  • stored procedure
  • stored function
  • stored routine

Also the build-in functions like those date time functions (e.g. WEEKDAY() etc) are considered as what?

Answer

Jakob picture Jakob · Apr 21, 2010

Google is your friend. The first match for "mysql routine function procedure" is this: http://dev.mysql.com/doc/refman/5.0/en/stored-routines-syntax.html

A quick summary:

A stored routine is either a procedure or a function.

A procedure is invoked using a CALL statement and can only pass back values using output variables.

A function can be called from inside a statement just like any other function and can return a scalar value.

Related questions

MySQL concepts: session vs connection

I'm a bit confused by MySQL concepts: session vs connection. When talking about connecting to MySQL, we use connection terminology, connection pool, etc. However in the MySQL online doc: http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html it talks …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More