存储过程与函数的区别-tutorial mysql-php.cn

核心提示：本质上没区别。只是函数有限制只能返回一个标量，而存储过程可以返回多个。并且函数是可以嵌入在SQL中使用的，可以在SELECT等SQL语句中调用，而存储过程不行。执行的本质都一样。函数限制比较多，如不能用临时表，只能用表变量等，而存储过程的

核心提示：本质上没区别。只是函数有限制只能返回一个标量，而存储过程可以返回多个。并且函数是可以嵌入在SQL中使用的，可以在SELECT等SQL语句中调用，而存储过程不行。执行的本质都一样。

函数限制比较多，如不能用临时表，只能用表变量等，而存储过程的限制相对就比较少。

1. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

2. 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。

3. 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数

可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

4. 当存储过程和函数被执行的时候，SQL Manager会到procedure cache中去取相应的查询语句，如果在

procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译。

Procedure cache：中保存的是执行计划，当编译好之后就执行procedure cache中的execution plan，之后SQL SERVER会根据每个execution plan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个execution plan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。

存储过程和函数具体的区别：

存储过程：可以使得对的管理、以及显示关于及其用户信息的工作容易得多。存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

可以出于任何使用 SQL 语句的目的来使用存储过程，它具有以下优点：

(1)功能强大，限制少。

(2)可以在单个存储过程中执行一系列 SQL 语句。

(3)可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

(4)存储过程在创建时即在上进行编译，所以执行起来比单个 SQL 语句快。

(5)可以有多个返回值，即多个输出参数，并且可以使用SELECT返回结果集。

函数：是由一个或多个 SQL 语句组成的子程序，可用于封装代码以便重新使用。自定义函数诸多限制，有许多语句不能使用，许多功能不能实现。函数可以直接引用返回值，用表变量返回记录集。但是，用户定义函数不能用于执行一组修改全局数据库状态的操作。

补充：

前面有一句，“可以处于任何使用SQL语句的目的来使用存储过程”。这里想说的是，有些时候有些地方使用函数或许会更方便些。例如，存在这样一个表：Temperature(Year, Month, Day, T02, T08, T14, T20)，其中Year,Month,Day是时间字段，T02, T08, T14, T20是指2时、8时、14时、20时四个时刻对应的温度值，这些温度值可为空。现在，要求统计2008年5月份的平均温度。

或许大家会这样写：

SELECT (AVG(T02)+AVG(T08)+AVG(T14)+AVG(T20))/4 FROM Temperature WHERE Year=2008 AND Month=5

如果不考虑空值的话，这样完全正确，但是考虑空值的话，如果根本没有统计02时的温度，那么AVG(T02)将为NULL，然后进行所有运算的结果都将为NULL。这显然是不正确的。

这里可以创建一个自定义函数，然后使用一个SELECT语句即可查询：

SELECT AVG(user.Average(T02,T08,T14,T20)) FROM Temperature WHERE Year=2008 AND Month=5

---------------------------------------------------存储过程--------------------------------------
--1、存储过程定义中的参数可以为任意数据类型，包括用户定义的类型，同时过程还可以拥有输出参数，用于返回任何数据类型的结果。
--2、存储过程只能直接返回整型值，通常用于显示过程执行的状态。
--3、在存储过程定义中可以包含任何T-SQL 语句，可以向用户返回查询的结果集，允许递归（最大32 层），可以创建/ 引用临时表。
--4、存储过程的参数有默认值时，只要在调用存储过程时不指定该参数的值或将DEFAULT 关键字指定为该参数的值，即可使用默认值。
--5、存储过程不能用于表达式、计算列、DEFAULT 约束和CHECK 约束中。
--6、系统内置的存储过程有以下特点：其名称以sp_ 开头，存储在MASTER 数据库中。并且符合以上条件的用户定义存储过程可以在任何数据库下，不需要提供四部分名，即可被调用。
---------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------函数---------------------------------------------------

--1、函数定义中的参数不能是 timestamp 、cursor 、table 和用户定义的数据类型，且没有输出参数。
--2、函数可以直接返回除LOB 、cursor 、table 、timestamp 外任何数据类型的值。

--3、在函数定义中不能有修改函数外对象的T-SQL 语句，不能向用户返回任何结果集，不允许递归，不能创建/ 引用临时表。
--4、函数的参数有默认值时，在调用函数时必须将DEFAULT 关键字指定为该参数的值，才可以使用默认值。
--5、函数可以用于表达式、计算列、DEFAULT 约束和CHECK 约束中。
--6、系统内置的函数有以下特点：其名称以fn_ 开头，名称都是小写字母，存储在MASTER 数据库中，其所有者为system_function_schema 。在调用系统内置的表值函数时，需要加:: 前缀。需要启用allow updates 服务器选项，才能将用户定义函数的所有者定义为 system_function_schema 。
----------------------------------------------------------------------------------------------------------------------

总结：

用户自定义函数在处理同一数据行中的各个字段时，特别方便有用。虽然这里使用存储过程也能达到查询目的，但是显然没有使用函数方便。而且，即使使用存储过程也无法处理SELECT查询中的同一数据行中的各个字段的运算。因为存储过程不返回值，使用时只能单独调用；而函数却能出现在能放置表达式的任何位置。

CREATE FUNCTION user.Average

(

@T02 float,

@T08 float,

@T14 float,

@T20 float

)
RETURNS float AS
BEGIN

DECLARE @sum float
DECLARE @num int
DECLARE @Ret float

SET @sum=0
SET @num=0

IF @T02 IS NOT NULL
BEGIN
SET @sum = @sum + @T02
SET @num = @num + 1
END

IF @T08 IS NOT NULL
BEGIN
SET @sum = @sum + @T08
SET @num = @num + 1
END

IF @T14 IS NOT NULL
BEGIN
SET @sum = @sum + @T14
SET @num = @num + 1
END

IF @T20 IS NOT NULL
BEGIN
SET @sum = @sum + @T20
SET @num = @num + 1
END

IF @num>0 SET @Ret = @sum / @num

Return @Ret

END

#创建表DEPT



CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT   UNSIGNED  NOT NULL  DEFAULT 0, 
dname VARCHAR(20)  NOT NULL  DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;



#创建表EMP雇员
CREATE TABLE emp
(empno  MEDIUMINT UNSIGNED  NOT NULL  DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2)  NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2)  NOT NULL,
hisal DECIMAL(17,2)  NOT NULL
)ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);

# 随机产生字符串
#定义一个新的命令结束符合
delimiter $$
#删除自定的函数
drop  function rand_string $$

#这里我创建了一个函数. 

#rand_string(n INT) rand_string 是函数名 (n INT) //该函数接收一个整数
create function rand_string(n INT) 
returns varchar(255) #该函数会返回一个字符串
begin 
#chars_str定义一个变量 chars_str,类型是 varchar(100),默认值&#39;abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ&#39;;
 declare chars_str varchar(100) default
   &#39;abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ&#39;;
 declare return_str varchar(255) default &#39;&#39;;
 declare i int default 0;
 while i < n do 
   set return_str =concat(return_str,substring(chars_str,floor(1+rand()*52),1));
   set i = i + 1;
   end while;
  return return_str;
  end $$


delimiter ;
select rand_string(6);

# 随机产生部门编号
delimiter $$
drop  function rand_num $$

#这里我们又自定了一个函数
create function rand_num( )
returns int(5)
begin 
 declare i int default 0;
 set i = floor(10+rand()*500);
return i;
  end $$


delimiter ;
select rand_num();

#******************************************
#向emp表中插入记录(海量的数据)


delimiter $$
drop procedure insert_emp $$


#随即添加雇员[光标]  400w
create procedure insert_emp(in start int(10),in max_num int(10))
begin
declare i int default 0; 
#set autocommit =0 把autocommit设置成0
 set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into emp values ((start+i) ,rand_string(6),&#39;SALESMAN&#39;,0001,curdate(),2000,400,rand_num());
  until i = max_num
 end repeat;
   commit;
 end $$


delimiter ;
#调用刚刚写好的函数, 1800000条记录,从100001号开始
call insert_emp(100001,4000000);


#**************************************************************
#  向dept表中插入记录

delimiter $$
drop procedure insert_dept $$


create procedure insert_dept(in start int(10),in max_num int(10))
begin
declare i int default 0; 
 set autocommit = 0;  
 repeat
 set i = i + 1;
 insert into dept values ((start+i) ,rand_string(10),rand_string(8));
  until i = max_num
 end repeat;
   commit;
 end $$


delimiter ;
call insert_dept(100,10);





#------------------------------------------------
#向salgrade 表插入数据
delimiter $$
drop procedure insert_salgrade $$
create procedure insert_salgrade(in start int(10),in max_num int(10))
begin
declare i int default 0; 
 set autocommit = 0;
 ALTER TABLE emp DISABLE KEYS;  
 repeat
 set i = i + 1;
 insert into salgrade values ((start+i) ,(start+i),(start+i));
  until i = max_num
 end repeat;
   commit;
 end $$
delimiter ;
#测试不需要了
#call insert_salgrade(10000,1000000);

Salin selepas log masuk

注释：

Salin selepas log masuk

随机函数：rand()  
在查询分析器中执行：select rand()，可以看到结果会是类似于这样的随机小数：0.36361513486289558，  
像这样的小数在实际应用中用得不多，一般要取随机数都会取随机整数。那就看下面的两种随机取整数的方法：  
1、  
A：  
select floor(rand()*N) ---生成的数是这样的：12.0  
B：  
select cast( floor(rand()*N) as int) ---生成的数是这样的：12  
2、  
A：select ceiling(rand() * N) ---生成的数是这样的：12.0  
B：select cast(ceiling(rand() * N) as int) ---生成的数是这样的：12  
其中里面的N是一个你指定的整数，如100，可以看出，两种方法的A方法是带有.0这个的小数的，而B方法就是真正的整数了。  
大致一看，这两种方法没什么区别，真的没区别？其实是有一点的，那就是他们的生成随机数的范围：  
方法1的数字范围：0至N-1之间，如cast( floor(rand()*100) as int)就会生成0至99之间任一整数  
方法2的数字范围：1至N之间，如cast(ceiling(rand() * 100) as int)就会生成1至100之间任一整数  
对于这个区别，看SQL的联机帮助就知了：  
------------------------------------------------------------------------------------ 
比较 CEILING 和 FLOOR  
CEILING 函数返回大于或等于所给数字表达式的最小整数。FLOOR 函数返回小于或等于所给数字表达式的最大整数。例如，对于数字表达式 12.9273，CEILING 将返回 13，FLOOR 将返回 12。FLOOR 和 CEILING 返回值的数据类型都与输入的数字表达式的数据类型相同。  
----------------------------------------------------------------------------------  
现在，各位就可以根据自己需要使用这两种方法来取得随机数了^_^  
另外，还要提示一下各位菜鸟，关于随机取得表中任意N条记录的方法，很简单，就用newid()： 
select top N * from table_name order by newid() ----N是一个你指定的整数，表是取得记录的条数 。

Salin selepas log masuk

<span style="font-family:Arial, Helvetica, sans-serif;"><span style="white-space: normal;">
</span></span>

Salin selepas log masuk

Php8, saya datang juga

Ketahui reka letak tapak web dalam masa 30 minit

Tutorial Video Permulaan Shangguan Oracle kepada Mahir

Baris pertama kod UNI-APP anda

Berkibar dari awal ke pelancaran apl

Tutorial Video Linux Baharu Brother Lian

Tutorial Video AXURE 9 (Sesuai untuk UI Reka Bentuk Produk Interaktif Pengurus Produk)

Tutorial Video PS Kemahiran Asas Sifar

Tutorial video UI 16 hari untuk anda bermula

Tutorial Video Teknik PS dan Teknik Menghiris

Tutorial Video Pembinaan Persekitaran Awan Alibaba dan Pelancaran Projek

Gambaran Keseluruhan Rangkaian Komputer - Pengetahuan Asas yang Perlu Dikuasai Pengaturcara

Tutorial Penting untuk Pengaturcara - Penjelasan Protokol HTTP

Tutorial Video Websocket

存储过程与函数的区别