CODEWITHSUNDEEP
phpmysqlsqluser-defined-functions
3D-kreativ
3D-kreativ

Reputation: 9299

Help with SP and UDF?

I'm trying to learn and use SP(Stored Procedure) and UDF(User Defined Function) with MySQL and PHP. What is the difference between SP and UDF and what is the purpose?

How would a simple piece of code look like in PHP and SQL with a SP that is updating, writing or fetching something from a table in a MySQL Database? Need to understand and see the point of using SP and UDF.

Preciate the help! Thanks!

Upvotes: 3

Views: 618

Answers (1)

Johan
Johan

Reputation: 76547

Stored procedures

A Stored Procedure is MySQL code written in and executed by MySQL.

Example of a stored function

CREATE FUNCTION AreWeThereYet(Location integer) RETURNS boolean
BEGIN
  Return 0;
END

Example of a stored procedure

CREATE PROCEDURE InsertRow(A integer)
BEGIN
  INSERT INTO table1 VALUES(A);
END

UDF's

A UDF is C(++) or similar code compiled as a .so (linux) or .dll (windows)

Which you than insert into MySQL using a command like: 

CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.so'; //linux
CREATE FUNCTION metaphon RETURNS STRING SONAME 'udf_example.dll'; //windows

Recap
UDF's are complex, stored procedures are simple.
You can find lots of examples on stored procedure in SO
Because UDF's are more complex, I would only use them if you find that a stored function/stored procedure does not work for you anymore, (to slow/ not powerfull enough etc)

Links
For more info on stored procedures see:
http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html

For more info on UDF's see:
http://dev.mysql.com/doc/refman/5.1/en/udf-compiling.html

Good SO questions on stored procedures
debugging: How do you debug MySQL stored procedures?
use them with php?: Should PHP developers use MySQL's stored procedures?
views or sproc?: MySQL: Views vs Stored Procedures
using sproc to do updates: mysql procedure to update numeric reference in previous rows when one is updated
raising and handling erros in sproc: How to raise an error within a MySQL function

How to call a stored procedure from php
It's really just another query

-- normal select
$query = "SELECT * FROM table1";
-- call to stored proc
$query = "CALL InsertARow('1')";
-- use a stored function
$query = "SELECT AreWeThereYet('10')";
-- or
$query = "SELECT * FROM table1 WHERE AreWeThereYet(field1) = '1' ";

Good luck.

Upvotes: 4

Related Questions