对SP和UDF有帮助吗? [英] Help with SP and UDF?

问题描述

我正在尝试通过MySQL和PHP学习和使用SP(存储过程)和UDF(用户定义函数). SP和UDF有什么区别，目的是什么?

在带有SP的PHP和SQL中，一个简单的代码将如何更新，编写或从MySQL数据库的表中获取内容?需要了解并了解使用SP和UDF的要点.

提供帮助！谢谢！

解决方案

存储过程

存储过程是MySQL编写并由MySQL执行的MySQL代码.

存储功能示例

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

存储过程示例

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

UDF的

UDF是C(++)或类似的代码，它们被编译为.so (linux)或.dll (windows)

比起使用以下命令将其插入MySQL:

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

回顾
UDF很复杂，存储过程很简单.
您可以在SO
中找到很多有关存储过程的示例. 由于UDF更为复杂，因此只有在您发现存储函数/存储过程不再适合您时(减慢/功能不足等)，我才会使用它们

链接
有关存储过程的更多信息，请参见:
http://dev.mysql.com/doc/refman/5.1/en/stored-routines.html

有关UDF的更多信息，请参见:
http://dev.mysql.com/doc/refman/5.1/en/udf-compiling.html

有关存储过程的常见问题
调试:如何调试MySQL存储过程?
将它们与php一起使用?: PHP开发人员应该使用MySQL的存储过程吗?
视图还是存储过程?: MySQL:视图与存储过程
使用sproc进行更新: mysql过程在更新行时更新前几行中的数字引用
在sproc中引发和处理错误:如何在MySQL函数中引发错误

如何从php调用存储过程
真的只是另一个查询

-- 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' ";

祝你好运.

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!

解决方案

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.

这篇关于对SP和UDF有帮助吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！