这里就不关注mysql与oracle之间的差别了
存储过程procedure
存储过程可以是的对数据库的管理、以及显示关于数据库及用户信息的工作容易的多。存储过程是SQL语句和可控制流语句的预编译集合,以一个名词存储并作为一个单元处理。存储过程存储在数据库内,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其他强大的编程功能。
存储过程可包含程序流、逻辑以及对数据库的查询,他们可以接受参数、输出参数、返回单个或多个结果集以及返回值。
优点:
- 可以在单个存储过程中执行一系列SQL语句
- 可以从自己的存储过程内引用其他存储过程,这可以简化一系列复杂语句
- 存储过程在创建时即在服务器上进行编译,所以执行起来比单个SQL语句快
mysql
drop procedure if exists myProc;
CREATE PROCEDURE myProc()
BEGIN
DECLARE my_text TEXT;
set my_text = 'hello procedure';
select my_text;
END;
call myProc();
函数Function
是由一个或多个SQL语句组成的子程序,可用于封装代码以便重新使用。
用户定义函数是可返回值的历程。根据返回值的类型,每个用户定义函数可分为以下三个类别
- 返回可更新数据库表的函数
如果用户定义函数包含单个SELECT语句且该语句可更新,则该函数返回的表格格式结果也可以更新 - 返回不可更新数据表的函数
如果用户定义函数包含不止一个SELECT语句,或包含一个不可更新的SELECT语句,则该函数返回的表格格式结果也不可更新 - 返回标量值的函数
用户定义函数可以返回标量值
1. Scalar-valued function - returns a scalar value such as an integer or a timestamp. Can be used as column name in queries
2. Inline function - can contain a single SELECT statement.
3. Table-valued function - can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can’t enclose the logic for getting this rowset in a single SELECT statement.
oracle
CREATE OR REPLACE FUNCTION GET_NETCAR_ADDRESS(V_ORDERID VARCHAR2) RETURN NUMBER AS
/*
* 查询网约车发起地
*/
RESULTDATA NUMBER(20);
BEGIN
SELECT t.ADDRESS
INTO RESULTDATA
FROM E_NETCAR_ORDERMATCH t
WHERE t.ORDERID = V_ORDERID
AND ROWNUM < 2;
RETURN RESULTDATA;
EXCEPTION
WHEN OTHERS THEN
RETURN(0);
END GET_NETCAR_ADDRESS;
总结
- 一般来说,存储过程实现的功能更复杂(比如一系列操作如统计分表等),而函数实现的功能针对性更强(比如子查询某字段)
- 对于存储过程来说可以返回参数,而函数只能返回值或者表对象
- 存储过程一般是作为一个独立的部分来执行,而函数可以作为语句的一个部分来调用,由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面
- 当存储过程和函数被执行的时候,SQL Manager会到procedure cache中去取相应的查询语句,如果在procedure cache里没有相应的查询语句,SQL Manager就会对函数过程和函数进行编译。
- 存储过程只在创造时进行编译,以后每次执行存储过程都不需要重新编译,而一般SQL没执行一次就便以一次,所以使用存储过程可以提高数据库执行速度
- 存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值,可以向程序返回错误原因。但函数只能返回一个特定类型的值或表对象
- 存储过程中的CRUD的操作会影响数据库操作,但函数却不能