程序博客网 > 淘宝交易指数是什么

Mysql学习总结（11）——MySql存储过程与函数

来源：互联网 发布：淘宝交易指数是什么 编辑：程序博客网 时间：2024/06/15 11:04

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

一、存储过程

1.1、基本语法

[sql] view plaincopy
  1. CREATE PROCEDURE sp_name ([proc_parameter[,...]])  
  2.   
  3. 　　[characteristic ...] routine_body  

     Sp_name:存储过程的名称，默认在当前数据库中创建。这个名称应当尽量避免与MySQL的内置函数相同的名称

      Proc_parameter:存储过程的参数列表
      格式[IN|OUT|INOUT]param_name type
      Param_name为参数名，type为参数的数据类型。多个参数彼此间用逗号分隔。输入参数、输出参数和输入/输出参数，分别用in/out/inout标识。参数的取名不要与数    据表的列名相同。

   Characteristic:存储过程的某些特征设定，分别介绍
        1 COMMENT’string’:用于对存储过程的描述，其中string为描述内容,comment为关键字。
        2 LANGUAGE SQL:指明编写这个存储过程的语言为SQL语言。这个选项可以不指定。
        3 DETERMINISTIC:表示存储过程对同样的输入参数产生相同的结果;NOT DETERMINISTIC，则表示会产生不确定的结果（默认）。

        4 contains sql | no sql | reads sql data | modifies sql data Contains sql表示存储过程包含读或写数据的语句（默认）
        No sql表示不包含sql语句
        Reads sql data表示存储过程只包含读数据的语句
        Modifies sql data 表示存储过程只包含写数据的语句
        5 sql security:这个特征用来指定存储过程使用创建该存储过程的用户(definer)的许可来执行，还是使用调用者(invoker)的许可来执行。默认是definer

    Routine_body:存储过程的主体部分，包含了在过程调用的时候必须执行的sql语句。以begin开始，以end结束。如果存储过程体中只有一条sql语句,可以省略begin-end标志。

1.2、数据准备

[sql] view plaincopy
  1. CREATE TABLE  
  2.     t_user  
  3.     (  
  4.         USER_ID INT NOT NULL AUTO_INCREMENT,  
  5.         USER_NAME CHAR(30) NOT NULL,  
  6.         USER_PASSWORD CHAR(10) NOT NULL,  
  7.         USER_EMAIL CHAR(30) NOT NULL,  
  8.         PRIMARY KEY (USER_ID),  
  9.         INDEX IDX_NAME (USER_NAME)  
  10.     )  
  11.     ENGINE=InnoDB DEFAULT CHARSET=utf8;  
然后这是插入的一些数据：


1.3 IN、OUT、INOUT参数

（1）、带IN的存储过程

[sql] view plaincopy
  1. //创建储存过程.cmd 中运行  
  2. CREATE  PROCEDURE  SP_SEARCH(IN p_name CHAR(20))  
  3. BEGIN  
  4. IF p_name is null or p_name='' THEN  
  5. SELECT * FROM t_user;  
  6. ELSE  
  7. SELECT * FROM t_user WHERE USER_NAME LIKE p_name;  
  8. END IF;  
  9. END  
因为;分会冲突，所以要加delimiter //。将//设置为结束运行符号

如下：


调用：

[sql] view plaincopy
  1. //调用并输出结果  
  2. CALL SP_SEARCH('林炳文')  
结果


（2）、带OUT的存储过程

[sql] view plaincopy
  1. //带OUT返回的  
  2. CREATE  PROCEDURE  SP_SEARCH2(IN p_name CHAR(20),OUT p_int INT)  
  3. BEGIN  
  4. IF p_name is null or p_name='' THEN  
  5. SELECT * FROM t_user;  
  6. ELSE  
  7. SELECT * FROM t_user WHERE USER_NAME LIKE p_name;  
  8. END IF;  
  9. SELECT FOUND_ROWS() INTO p_int;  
  10. END  
调用输出：统计带林开头的人数

[sql] view plaincopy
  1. //调用并输出结果  
  2. CALL SP_SEARCH2('林%',@p_num);  
  3. SELECT @p_num;  


（3）、带INOUT的存储过程

[sql] view plaincopy
  1. //带INOUT的存储过程  
  2. CREATE PROCEDURE sp_inout(INOUT p_num INT)  
  3. BEGIN  
  4. SET p_num=p_num*10;  
  5. END  
  6. //调用并输出结果  
  7. SET @p_num=2;  
  8. call sp_inout(@p_num);  
  9. SELECT @p_num;  
输出结果：

1.4、存储过程体    

       存储过程体中可以使用各种sql语句和过程式语句的组合，来封装数据库应用中复杂的业务逻辑和处理规则，以实现数据库应用的灵活编程。下面主要介绍几个用于构造存储过程体的常用语法元素。
1、局部变量
在存储过程体中可以声明局部变量，用来存储存储过程体中临时结果。

[sql] view plaincopy
  1. DECLARE var_name[,…] type [DEFAULT value]  
  2. Var_name:指定局部变量的名称  
  3. Type:用于声明局部变量的数据类型  
  4. default子句:用于为局部变量指定一个默认值。若没有指定，默认为null.  
如：
[sql] view plaincopy
  1. Declare cid int(10);  
使用说明：
局部变量只能在存储过程体的begin…end语句块中声明。
局部变量必须在存储过程体的开头处声明。
局部变量的作用范围仅限于声明它的begin..end语句块，其他语句块中的语句不可以使用它。
局部变量不同于用户变量，两者区别：局部变量声明时，在其前面没有使用@符号，并且它只能在begin..end语句块中使用；而用户变量在声明时，会在其名称前面使用@符号，同时已声明的用户变量存在于整个会话之中。
2、set语句
使用set语句为局部变量赋值
[sql] view plaincopy
  1. Set var_name=expr  
  2. Set cid=910;  
3、select … into 语句
把选定列的值直接存储到局部变量中，语法格式
[sql] view plaincopy
  1. Select col_name[,…] into var_name[,…] table_expr  
  2. Col_name:用于指定列名  
  3. Var_name:用于指定要赋值的变量名  
  4. Table_expr:表示select语句中的from字句及后面的语法部分  
说明:存储过程体中的select…into语句返回的结果集只能有一行数据。

4、定义处理程序
是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决方法。

[sql] view plaincopy
  1. DECLARE handler_type HANDLER FOR condition_value[,…] sp_statement  
  2. handler_type:CONTINUE | EXIT | UNDO  
  3. Condition_value:Sqlwarning | not found | sqlexception  
5、流程控制语句
（1）条件判断语句
If语句

[sql] view plaincopy
  1. If search_condition then statement_list  
  2. [elseif search_condition then statement_list]…  
  3. [else statement_list]  
  4. End if  
Search_condition参数:条件判断语句
Statement_list参数:不同条件的执行语句

多重IF的存储过程实例

数据准备

学生表：

[sql] view plaincopy
  1. CREATE TABLE  
  2.     t_student  
  3.     (  
  4.         STU_ID INT NOT NULL,  
  5.         STU_NAME CHAR(10) NOT NULL,  
  6.         STU_CLASS INT NOT NULL,  
  7.         STU_SEX CHAR(2) NOT NULL,  
  8.         STU_AGE INT NOT NULL,  
  9.         PRIMARY KEY (STU_ID)  
  10.     )  
  11.     ENGINE=InnoDB DEFAULT CHARSET=utf8;  

数据如下：


成绩表（STU_ID是学生表是外键关系）：

[sql] view plaincopy
  1. CREATE TABLE  
  2.     t_grade  
  3.     (  
  4.         STU_ID INT NOT NULL,  
  5.         STU_SCORE INT NOT NULL,  
  6.         FOREIGN KEY (STU_ID) REFERENCES t_student (STU_ID),  
  7.         INDEX STU_ID (STU_ID)  
  8.     )  
  9.     ENGINE=InnoDB DEFAULT CHARSET=utf8;  

然后写一个存储过程：返回各个分数等级的人

[sql] view plaincopy
  1. //带多重IF的存储过程  
  2. CREATE  PROCEDURE SP_SCHOLARSHIP_LEVEL(IN p_level char(1))  
  3. BEGIN  
  4. IF p_level ='A'  THEN  
  5. SELECT * FROM t_grade WHERE STU_SCORE >=90;  
  6. ELSEIF  p_level ='B'  THEN  
  7. SELECT * FROM t_grade WHERE STU_SCORE <90 AND  STU_SCORE>=80;  
  8. ELSEIF  p_level ='C'  THEN  
  9. SELECT * FROM t_grade WHERE STU_SCORE <80 AND  STU_SCORE>=70;  
  10. ELSEIF  p_level ='D'  THEN  
  11. SELECT * FROM t_grade WHERE STU_SCORE <60;  
  12. ELSE  
  13. SELECT * FROM t_grade;  
  14. END IF;  
  15. END  

调用过程：
[sql] view plaincopy
  1. //调用并输出结果  
  2. CALL SP_SCHOLARSHIP_LEVEL('A');  

Case 语句
表达形式1
[sql] view plaincopy
  1. Case case_value  
  2. When when_value then statement_list  
  3. [When when_value then statement_list]…  
  4. [else statement_list]  
  5. End case  
表达形式2
[sql] view plaincopy
  1. Case  
  2. When search_condition then statement_list  
  3. End case  
使用范例

[sql] view plaincopy
  1. CREATE  PROCEDURE SP_SCHOLARSHIP_LEVEL3(IN p_level char(1))  
  2. BEGIN  
  3. DECLARE p_num int DEFAULT  0;  
  4. CASE p_level  
  5. WHEN 'A'  THEN  
  6. SET p_num=90;  
  7. WHEN 'B'  THEN  
  8. SET p_num=80;  
  9. WHEN 'C'  THEN  
  10. SET p_num=70;  
  11. WHEN 'D'  THEN  
  12. SET p_num=60;  
  13. ELSE  
  14. SET p_num=0;  
  15. END CASE;  
  16. SELECT * FROM t_grade g, t_student s WHERE g.STU_ID=s.STU_ID AND g.STU_SCORE >= p_num ;  
  17. END  
调用：

[sql] view plaincopy
  1. //调用并输出结果  
  2. CALL SP_SCHOLARSHIP_LEVEL3('d');  



(2)循环语句
While语句、repeat语句和loop语句。
While语句

[sql] view plaincopy
  1. [begin_label:]  
  2. while search_condition do  
  3. Statement_list  
  4. End while  
  5. [end_label]  
       判断条件search_condition是否为真,若为真,则执行statement_list中的语句，然后再进行判断，如若仍然为真则继续循环，直至条件判断不为真时循环结束。
使用范例

[sql] view plaincopy
  1. //带while的存储过程  
  2. CREATE PROCEDURE sp_cal(IN p_num INT,OUT p_result INT)  
  3. BEGIN  
  4.  SET p_result=1;  
  5.  WHILE p_num > 1 DO  
  6.   SET p_result = p_num * p_result;  
  7.   SET p_num = p_num-1;  
  8.  END WHILE;  
  9. END  
  10. //调用并输出结果  
  11. CALL sp_cal(5,@result);  
  12. SELECT @result;  
输出结果：计算5！


Repeat语句语法格式

[sql] view plaincopy
  1. [begin_label:]  
  2. repeat  
  3. Statement_list  
  4. Until search_condition  
  5. End repeat  
  6. [end_label]  
        Repeat语句首先执行statement_list中的语句，然后判断条件search_condition是否为真，倘若为真，则结束循环，若不为真，继续循环。
Repeat先执行后判断，while先判断后执行。
使用范例：

[sql] view plaincopy
  1. //带repeat的存储过程  
  2. CREATE PROCEDURE sp_cal2(IN p_num INT,OUT p_result INT)  
  3. BEGIN  
  4.   SET p_result=1;  
  5.   REPEAT  
  6.     SET p_result = p_num * p_result;  
  7.     SET p_num = p_num-1;  
  8.     UNTIL p_num<=1  
  9.   END REPEAT;  
  10. END  
  11. //调用并输出结果  
  12. CALL sp_cal2(5,@result);  
  13. SELECT @result;  


1.5、 调用存储过程

[sql] view plaincopy
  1. Call sp_name([parameter[,…]]);  
  2. Sp_name被调用存储过程的名称  
  3. Parameter:指定调用存储过程所要使用的参数。  

1.6、 修改存储过程

[sql] view plaincopy
  1. Alter procedure proc_name[characteristic…]  

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

1.7、 删除存储过程

[sql] view plaincopy
  1. Drop procedure [if exists] sp_name;  

二、函数

2.1、 定义

MySQL中，创建存储函数的基本形式如下：

[sql] view plaincopy
  1. CREATE FUNCTION sp_name([func_parameter[,...]])  
  2. RETURNS type  
  3. [characteristic ...] routine_body  
  4. Return  

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

调用存储函数

[sql] view plaincopy
  1. Select sp_name([func_parameter…])  
  2. Select fn_search(2);  
删除存储函数drop
修改存储函数alter 修改存储函数的某些相关特征。

2.2、函数使用例子

（比较大小 ，返回大的数）

[sql] view plaincopy
  1. /**函数使用**/  
  2. CREATE FUNCTION sp_cal_max(p_num1 INT,p_num2 INT)  
  3. RETURNS INT  
  4. BEGIN  
  5. IF p_num1 >= p_num2 THEN  
  6. RETURN p_num1;  
  7. ELSE  
  8. RETURN p_num2;  
  9. END IF;  
  10. END  
调用：

[sql] view plaincopy
  1. SET @p_num1=2;  
  2. SET @p_num2=34;  
  3. SELECT sp_cal_max(@p_num1,@p_num2);  


2.3、存储过程和函数区别

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

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

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

4）存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用），由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。 SQL语句中不可用存储过程，而可以使用函数。

三、光标（游标）

 3.1 定义

     查询语句可能查询出多条记录，在存储过程和函数中使用光标标来逐条读取查询结果集中的记录。光标的使用包括声明光标、打开光标、使用光标和关闭光标。光标必须声明光标、打开光标、使用光标和关闭光标。光标必须声明在处理程序之前，并且声明在变量和条件之后。

1 声明光标

[sql] view plaincopy
  1. Declare cursor_name cursor forselect_statement;  
  2. Cursor_name:光标名称  
  3. Select_statement:select语句的内容  
  4. Declare cur_employee cursor forselect name,age from employee;  

2 打开光标

[sql] view plaincopy
  1. Open cursor_name  
  2. Open cur_employee;  

3 使用光标

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

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

4 关闭光标

[sql] view plaincopy
  1. Close cursor_name;  
  2. Close cur_employee;  

每个光标不再需要时都应该被关闭，使用close语句将会释放光标所使用的全部资源。在一个光标被关闭后，如果没有重新被打开，则不能被使用。对于声明过的光标，则不需要再次声明，可直接使用open语句打开。

3.2、使用范例

（将表test_cur1数据复制到test_cur2）

[sql] view plaincopy
  1. CREATE TABLE `test_cur1` (    
  2.   `id` int(11) NOT NULL auto_increment,    
  3.   `type` char(11) default NULL,    
  4.   `order1` char(11) default NULL,    
  5.   PRIMARY KEY  (`id`)    
  6. )   
  7. INSERT INTO `test_cur1` VALUES (1, '145', 'd1');    
  8. INSERT INTO `test_cur1` VALUES (2, '134', '1d');    
  9. INSERT INTO `test_cur1` VALUES (3, '123', '1ad');    
  10. INSERT  INTO `test_cur1` VALUES (4, '121', '1as');  
  11.    
  12. CREATE TABLE `test_cur2` (    
  13.   `id` int(11) NOT NULL auto_increment,    
  14.   `type` char(11) default NULL,    
  15.   `order1` char(11) default NULL,    
  16.   PRIMARY KEY  (`id`)    
  17. )   
然后写光标了：

[sql] view plaincopy
  1. create procedure get_cur ()  
  2. BEGIN  
  3.   DECLARE done INT DEFAULT 0;  
  4.   DECLARE ID int(11);  
  5.   DECLARE type char(11);  
  6.   DECLARE order1 char(11);  
  7.   DECLARE mycur CURSOR FOR SELECT * FROM test_cur1;//定义光标  
  8.   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;  
  9.   //打开光标  
  10.   OPEN mycur;  
  11.   //开始循环  
  12.   REPEAT  
  13.     FETCH mycur INTO ID,type,order1;//取出光标的内容到临时变量  
  14.     IF NOT done THEN  
  15.       INSERT INTO test_cur2 VALUES (ID,type,order1);//插入到另一张表  
  16.     END IF;    
  17.   UNTIL done END REPEAT;//当done=1时结束循环  
  18.   //关闭光标  
  19.   CLOSE mycur;  
  20. END  

运行：

[sql] view plaincopy
  1. call get_cur()  

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


这是表1

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

0 0
淘宝交易指数是什么 淘宝交易指数是什么
原创粉丝点击
热门IT博客
好看的喜剧电影知乎好看的喜剧电影知乎
淘宝的假银笛喇叭淘宝的假银笛喇叭
好用的网络机顶盒
数据覆盖恢复
马曳 知乎
并发编程
java 取asill
qt5 udp socket编程
三维动画设计师知乎
剑三苍云成女捏脸数据
凤城高中淘宝屋
逆战一键瞬狙宏软件
7z解压软件 mac
如何在mac上切换输入法
苏州青蛙网络
爱丁堡艺术学院 知乎
angular.js
视频监控软件
2.单片机数字钟设计
恐怖主义 数据
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 富美家防火板色卡 富美家防火板 富美家 恃运而骄 恃宠而骄沈晏均 江太太恃宠而骄 晴空舞 恃宠而骄成语的意思 恃宠而骄楚湫 富友收件宝 富友收件宝客服电话 富图宝 富友收件宝下载 富得宝家具 富得宝家具怎么样 富得宝家具旗舰店 华宝证券至富 富得宝家具床 宝富国际 富良野 富蕴 富蕴县 富蕴县属于哪个市 投融长富 富血口服液 富血口服液多少钱一盒 富裕县蛋糕店 富裕县招聘信息 富裕火车站时刻表 富裕到齐市的火车站时刻表 中国财富榜 富贵小丫鬟免费阅读云舒 富贵小丫鬟完结免费阅读云舒 自己按摩富贵包手法 如何消除富贵包 富贵包是什么 富贵包疏通手法教程 怎么消除富贵包 怎样消除富贵包 颈椎富贵包清晰图片 颈后富贵包自我锻炼动作 富贵包怎样锻炼可以消除

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里