Home > Database > Mysql Tutorial > MySql存储过程与函数详解_MySQL

MySql存储过程与函数详解_MySQL

WBOY
Release: 2016-05-30 17:11:29
Original
1161 people have browsed it

存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句。存储过程和函数可以避免开发人员重复的编写相同的SQL语句。而且,存储过程和函数是在MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输。
代码如下:

Alter procedure proc_name[characteristic…] 

只能修改存储过程的特征,如果要修改存储过程的内容,可以先删除该存储过程,然后再重新创建
1.7、 删除存储过程

代码如下:

Drop procedure [if exists] sp_name; 

二、函数
2.1、 定义

MySQL中,创建存储函数的基本形式如下:
CREATE FUNCTION sp_name([func_parameter[,...]]) 
RETURNS type 
[characteristic ...] routine_body 
Return 
Copy after login

子句用于声明存储函数返回值的数据类型。存储过程是用户定义的一系列sql语句的集合,涉及特定表或其它对象的任务,用户可以调用存储过程,而函数通常是数据库已定义的方法,它接收参数并返回某种类型的值并且不涉及特定用户表。
调用存储函数

Select sp_name([func_parameter…]) 
Select fn_search(2); 
Copy after login

删除存储函数drop
修改存储函数alter 修改存储函数的某些相关特征。

2.2、函数使用例子
(比较大小 ,返回大的数)

/**函数使用**/ 
CREATE FUNCTION sp_cal_max(p_num1 INT,p_num2 INT) 
RETURNS INT 
BEGIN 
IF p_num1 >= p_num2 THEN 
RETURN p_num1; 
ELSE 
RETURN p_num2; 
END IF; 
END 
Copy after login

调用:

SET @p_num1=2; 
SET @p_num2=34; 
SELECT sp_cal_max(@p_num1,@p_num2); 
Copy after login

2.3、存储过程和函数区别
1)一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强。存储过程,功能强大,可以执行包括修改表等一系列数据库操作;用户定义函数不能用于执行一组修改全局数据库状态的操作。

2)对于存储过程来说可以返回参数,如记录集,而函数只能返回值或者表对象。函数只能返回一个变量;而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型,而函数只能有IN类~~存储过程声明时不需要返回类型,而函数声明时需要描述返回类型,且函数体中必须包含一个有效的RETURN语句。

3)存储过程,可以使用非确定函数,不允许在用户定义函数主体中内置非确定函数。

4)存储过程一般是作为一个独立的部分来执行( EXECUTE 语句执行),而函数可以作为查询语句的一个部分来调用(SELECT调用),由于函数可以返回一个表对象,因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程,而可以使用函数。
三、光标(游标)
3.1 定义
查询语句可能查询出多条记录,在存储过程和函数中使用光标标来逐条读取查询结果集中的记录。光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前,并且声明在变量和条件之后。
1 声明光标

Declare cursor_name cursor forselect_statement; 
Cursor_name:光标名称 
Select_statement:select语句的内容 
Declare cur_employee cursor forselect name,age from employee; 
Copy after login

2 打开光标

Open cursor_name 
Open cur_employee; 
Copy after login

3 使用光标
Mysql中使用fetch关键字来使用光标,语法形式

Fetch cur_name intovar_name[,var_name…]; 
Cur_name表示光标的名称 
Var_name表示将光标中的select语句查询出来的信息存入该参数。Var_name必须在声明光标前就定义好。 
Fetch cur_employee intoemp_name,emp_age; 
Copy after login

4 关闭光标

Close cursor_name; 
Close cur_employee; 
Copy after login

每个光标不再需要时都应该被关闭,使用close语句将会释放光标所使用的全部资源。在一个光标被关闭后,如果没有重新被打开,则不能被使用。对于声明过的光标,则不需要再次声明,可直接使用open语句打开。
3.2、使用范例
(将表test_cur1数据复制到test_cur2)

CREATE TABLE `test_cur1` ( 
 `id` int(11) NOT NULL auto_increment, 
 `type` char(11) default NULL, 
 `order1` char(11) default NULL, 
 PRIMARY KEY (`id`) 
) 
INSERT INTO `test_cur1` VALUES (1, '145', 'd1'); 
INSERT INTO `test_cur1` VALUES (2, '134', '1d'); 
INSERT INTO `test_cur1` VALUES (3, '123', '1ad'); 
INSERT INTO `test_cur1` VALUES (4, '121', '1as'); 
 
CREATE TABLE `test_cur2` ( 
 `id` int(11) NOT NULL auto_increment, 
 `type` char(11) default NULL, 
 `order1` char(11) default NULL, 
 PRIMARY KEY (`id`) 
) 


Copy after login

然后写光标了:

create procedure get_cur () 
BEGIN 
 DECLARE done INT DEFAULT 0; 
 DECLARE ID int(11); 
 DECLARE type char(11); 
 DECLARE order1 char(11); 
 DECLARE mycur CURSOR FOR SELECT * FROM test_cur1;//定义光标 
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
 //打开光标 
 OPEN mycur; 
 //开始循环 
 REPEAT 
 FETCH mycur INTO ID,type,order1;//取出光标的内容到临时变量 
 IF NOT done THEN 
  INSERT INTO test_cur2 VALUES (ID,type,order1);//插入到另一张表 
 END IF; 
 UNTIL done END REPEAT;//当done=1时结束循环 
 //关闭光标 
 CLOSE mycur; 
END 

Copy after login

运行:

call get_cur() 
Copy after login

来看看两张表的数据:这是表2

这是表1

说明数据已成功复制过去了。

以上就是本文的全部内容,希望对大家学习MySql存储过程与函数有所帮助

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template