`

mysql 存储过程

 
阅读更多

1.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。 
  2.当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作,如果用程序来完成,就变成了一条条的 SQL 语句,可能要多次连接数据库。而换成存储,只需要连接一次数据库就可以了。 
  3.存储过程可以重复使用,可减少数据库开发人员的工作量。 
  4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

 

1,变量 
DECLARE声明,SET赋值 
INT, INTEGER
BIGINT
FLOAT
DOUBLE
DECIMAL(precision, scale), NUMERIC(precision, scale)
DATE
DATETIME
CHAR(length)
VARCHAR(length)
BLOB, TEXT
LONGBLOB, LONGTEXT

 

可以在DECLARE变量时用DEFAULT设置默认值,不设则为NULL 

2,参数 
参数分IN、OUT和INOUT类型 

3,用户变量 
以@开头的为用户变量,作用域为session,所以可以当作全局变量使用 

4,注释

//为单行注释

/*
|
|  多行注释
|
*/

5,操作符 
算术操作符

+
-
*
/     # 结果可能为小数
DIV   # 结果为整数
%

比较操作符

>
<
<=
>=
BETWEEN
NOT BETWEEN
IN
NOT IN
=
<>, !=             # 不等号
<=>                # Null safe equal (returns TRUE if both arguments are Null)
LIKE
REGEXP
IS NULL
IS NOT NULL

 

逻辑操作符

AND
OR
XOR

 

位操作符

 

|
&
<<
>>
~

6,内建函数 
分为字符串函数、算术函数、日期和时间函数和其他函数 

常用MySQL函数

 

ABS
CEILING
CONCAT
CURDATE
DATE_ADD
DATE_SUB
FORMAT
GREATEST
IF
IFNULL
INSERT
INSTR
ISNULL
LEAST
LEFT
LENGTH
LOCATE
LOWER
LPAD
LTRIM
MOD
NOW
POWER
RAND
REPEAT
REPLACE
ROUND
RPAD
RTRIM
SIGN
SQRT
STRCMP
SUBSTRING
UPPER
VERSION

 

7,数据类型 
MySQL中所有的变量都为单元素,没有数组的概念。 

String类型 
CHAR:定长,不足的部分用空格,超出的部分截断,最大255字节 
VARCHAR:变长,不足的部分变短,超出的部分截断,最大65532字节 

ENUM类型 

SET类型 
SET与ENUm类似,但是可以有多个值

 

CREATE PROCEDURE sp_set(in_option SET('Yes', 'No', 'Maybe'))
BEGIN
  SELECT in_option;
END

--------------
CALL sp_set('Yes')
--------------

+-----------+
| in_option |
+-----------+
| Yes       |
+-----------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

--------------
CALL sp_set('Yes,No,Maybe')
--------------

+--------------+
| in_option    |
+--------------+
| Yes,No,Maybe |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

--------------
CALL sp_set('Yes,No,Go away')
--------------

ERROR 1265 (01000): Data truncated for column 'in_option' at row 1

 

Numeric类型 

DATE和DATETIME类型 
DATE用于存储日期,DATETIME用于存储日期和时间 

TEXT和BLOB类型 
TEXT可以存储64K,LONGTEXT可以存储4G 
BLOB和LONGBLOB与之类似,但是它们还可以存储二进制数据 

8,sql_mode 

'STRICT_TRANS_TABLES'(默认值): 对transactional的table做严格数据类型限制 
'STRICT_ALL_TABLES': 对所有table都做严格数据类型限制 

在strict mode下,如果出现Data truncate错误(如将String赋值给Integer)会raise error,而在no strict mode下则会出现不可预料的行为(如将String赋值给Integer时值变为0)并且只能在“show warnings;”时看到。

 

mysql> SHOW WARNINGS;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1265 | Data truncated for column 'b' at row 1 |
| Warning | 1265 | Data truncated for column 'c' at row 1 |
+---------+------+----------------------------------------+

 

存储过程应该一直在strict mode下工作,这样可以避免不可预期的错误。

 

 

 


一个存储过程包括名字,参数列表,以及可以包括很多SQL语句的SQL语句集。

一个存储过程包括名字,参数列表,以及可以包括很多 SQL 语句的 SQL 语句集。

创建存储过程:

语法:

CREATE PROCEDURE p()

 BEGIN

 /* 此存储过程的正文 */

 END

CREATE PROCEDURE productpricing()

 BEGIN

   SELECT Avg(pro_price) AS priceaverage

   FROM   products;

 END;

# begin…end 之间是存储过程的主体定义

# mysql 的分界符是分号(;)

                        

调用存储过程的方法是:

# CALL 加上过程名以及一个括号

例如调用上面定义的存储过程

CALL productpricing();

哪怕是不用传递参数,存储过程名字后面的括号“()”也是必须的

删除存储过程的方法是:

DROP PROCUDURE productpricing;

创建带参数的存储过程: 

CREATE PROCUDURE productpricing(

OUT p1 DECIMAL(8,2),

OUT ph DECIMAL(8,2),

OUT pa DECIMAL(8,2)

)

BEGIN

SELECT Min(prod_price) INTO pl FROM products;

SELECT Max(prod_price) INTO ph FROM products;        

SELECT Avg(prod_price) INTO pa FROM products;

END;

# DECIMAL 用于指定参数的数据类型

# OUT 用于表明此值是用于从存储过程里输出的

# MySQL 支持 OUT, IN, INOUT

调用带参数的存储过程: 

CALL   productpricing(@pricelow,

                    @pricehigh,

                    @priceaverage); 

所有的参数必须以 @ 开头

要想获取 @priceaverage 的值,用以下语句

SELECT @priceaverage;

获取三个的值,用以下语句

SELECT @pricehigh, @pricelow, @priceaverage;

另一个带 IN 和 OUT 参数的存储过程: 

CREATE PROCEDURE ordertotal(

   IN onumber INT,

   OUT ototal DECIMAL(8,2)

)

BEGIN

   SELECT Sum(item_price*quantity)

   FROM orderitems

   WHERE order_num = onumber

   INTO ototal;

END;

CALL ordertotal(20005, @total);

SELECT @total;

 添加一个完整的例子:(这是一个自定义分页的存储过程)

DELIMITER $$

DROP PROCEDURE IF EXISTS `dbcall`.`get_page`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `get_page`(
    /**//*Table name*/
    tableName varchar(100),
    /**//*Fileds to display*/
    fieldsNames varchar(100),
    /**//*Page index*/
    pageIndex int,
    /**//*Page Size*/
    pageSize int,  
    /**//*Field to sort*/
    sortName varchar(500),
    /**//*Condition*/
    strWhere varchar(500)
  )
BEGIN  
    DECLARE fieldlist varchar(200);  
    if fieldsNames=''||fieldsNames=null THEN
        set fieldlist='*';
    else
        set fieldlist=fieldsNames;  
    end if;

    if strWhere=''||strWhere=null then
        if sortName=''||sortName=null then  
            set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
        else
            set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);  
        end if;
    else
        if sortName=''||sortName=null then
            set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);
        else
            set @strSQL=concat('SELECT ',fieldlist,' FROM ',tableName,' WHERE ',strWhere,' ORDER BY ',sortName,' LIMIT ',(pageIndex-1)*pageSize,',',pageSize);  
        end   if;
    end   if;  
    PREPARE stmt1 FROM @strSQL;  
    EXECUTE stmt1;
    DEALLOCATE PREPARE stmt1;
END$$

DELIMITER ;

分享到:
评论

相关推荐

    mysql存储过程教程

    MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预编译一系列SQL语句并封装成一个可重复使用的单元,从而提高数据处理的效率和代码的复用性。本教程将深入探讨MySQL存储过程的创建、调用以及相关概念...

    mysql存储过程之返回多个值的方法示例

    本文实例讲述了mysql存储过程之返回多个值的方法。分享给大家供大家参考,具体如下: mysql存储函数只返回一个值。要开发返回多个值的存储过程,需要使用带有INOUT或OUT参数的存储过程。咱们先来看一个orders表它的...

    mysql经典教程+mysql存储过程讲解

    本教程结合"mysql经典教程+mysql存储过程讲解"的主题,将深入探讨MySQL的基础知识以及核心特性——存储过程。 首先,我们需要理解什么是数据库。数据库是一个组织和存储数据的系统,允许用户以结构化方式访问和管理...

    MySQL存储过程的异常处理方法

    本实例展示了如何在MySQL存储过程中实现异常处理,以捕获并处理可能出现的错误。 首先,我们注意到在创建存储过程`myProc`时,使用了`delimiter $$`来改变MySQL客户端的语句分隔符,这是为了在存储过程中使用多个...

    Java实现调用MySQL存储过程详解

    总的来说,Java通过JDBC调用MySQL存储过程涉及到连接数据库、创建`CallableStatement`、执行存储过程和处理结果。这使得开发者能够在Java应用中灵活地利用数据库提供的强大功能,实现更高效的数据处理。

    Mysql存储过程常用语句模板

    Mysql存储过程常用语句模板(含变量,if,三种循环等等) Mysql存储过程常用语句模板(含变量,if,三种循环等等) Mysql存储过程常用语句模板(含变量,if,三种循环等等) Mysql存储过程常用语句模板(含变量,if,三...

    mysql中文手册+mysql命令大全+mysql存储过程

    最后,"mysql存储过程.pdf"专注于MySQL的存储过程。存储过程是预编译的SQL语句集合,可以提高数据库操作的性能,减少网络流量,并增强数据安全。在PDF文档中,你将学习如何定义、调用和管理存储过程,以及如何使用...

    MySQL存储过程编程.pdf

    MySQL 存储过程编程 MySQL 存储过程编程是指在 MySQL 数据库中使用存储过程来实现业务逻辑的编程技术。存储过程是一种可以在数据库中存储和执行的程序单元,它可以实现复杂的业务逻辑和数据处理操作。 在 MySQL ...

    MySQL存储过程学习

    MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者预编译一系列的SQL语句,形成一个可重复使用的代码块,以提高数据处理的效率和应用程序的性能。在这个"MySQL存储过程学习"的主题中,我们将深入探讨...

    MySQL 存储过程入门到精通

    MySQL存储过程是数据库管理系统中的一个重要特性,它允许程序员或数据库管理员预先定义一组SQL语句,形成一个可重用的逻辑单元。在这个“MySQL存储过程入门到精通”资料中,你将深入理解存储过程的原理、创建、调用...

    c++实现调mysql存储过程

    c++实现调mysql存储过程,实现存储过程的出参入参,可以支持查询多数据返回,还有存储过程的复杂数据的增删改等

    mysql存储过程调试工具

    MySQL存储过程是数据库管理系统中的一种重要功能,它允许开发者编写一系列复杂的SQL语句,并作为一个单元进行执行,提高代码复用性和效率。然而,与普通的编程语言一样,存储过程的调试同样至关重要,因为错误的存储...

    mysql存储过程实例

    MySQL 存储过程实例 MySQL 存储过程实例详细介绍了 MySQL 存储过程的开发步骤,本节将通过具体的实例讲解 PHP 是如何操纵 MySQL 存储过程的。 创建存储过程 存储过程的创建是 MySQL 存储过程的基础,MySQL 5.0 ...

    mysql存储过程实现分页

    ### MySQL存储过程实现分页 #### 背景与需求 在数据库操作中,分页是一种常见的需求,尤其是在处理大量数据时。通过分页技术,可以有效地减少每次查询的数据量,提高系统的响应速度和用户体验。MySQL作为一种广泛...

    mysql 存储过程 实战

    ### MySQL存储过程实战知识点 #### 一、存储过程概述 MySQL 存储过程是一种预编译的 SQL 脚本,它可以包含复杂的逻辑控制结构、循环等操作,并且可以接受参数,执行完后还可以返回结果。存储过程可以提高数据处理...

    MySQL存储过程编程教程.pdf

    MySQL存储过程编程是数据库应用开发中的一个重要环节,它可以帮助开发者将一系列的数据库操作封装起来,以提高程序的可维护性和运行效率。在MySQL 5.0及以上版本中,存储过程、函数和触发器的支持为数据库提供了更...

Global site tag (gtag.js) - Google Analytics