使用存储过程

小灰灰 2022-05-23 06:35 226阅读 0赞

前言：存储过程的好处

1、使用了存过程，很多相似性的删除，更新，新增等操作就变得轻松了，并且以后也便于管理！
    2、存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。   
    3、存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。     
    4、存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。     
    5、存储过程主要是在服务器上运行，减少对客户机的压力。   
    6、存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。   
    7、存储过程可以在单个存储过程中执行一系列SQL语句。   
    8、存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。

一、创建存储过程

1.基本语法：

create procedure sp_name()
    begin
    .........
    end

CREATE PROCEDURE productpricing()
    BEGIN
            SELECT Avg(prod_price) AS priceaverage
            FROM products;
    EEND;

**mysql命令行客户机的分隔符:**

如何临时更改命令行使用程序的语句分隔符，下面举个例子：

DELIMITER //
    CREATE PROCEDURE productpricing()
    BEGIN
        SELECT Ag(prod_price) AS priceaverage
        FROM products;
    END //
    DELIMITER;

其中DELIMITER//告诉命令行实用程序使用//作为新的语句结束分隔符，我们看到END//，并不是用的END；最后恢复为原来的语句分隔符还是用DELIMITER**；**。

2.参数传递

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATE PROCEDURE(\[\[IN |OUT |INOUT \] 参数名 数据类形...\])

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默值；

OUT 输出参数:该值可在存储过程内部被改变，指出相应的参数用来从存储过程传出的一个值返回给调用者；

INOUT 输入输出参数:调用时指定，并且可被改变和返回。

下面我们来看两个例子：

首先以使用OUT参数为例：

CREATE PROCEDURE productpricing(
        OUT pl DECIMAL(8, 2),//十进制数，精度为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;

为调用此修改过的存储过程，必须指定3个变量名，如下图：

CALL productpricing(@pricelow, 
                        @pricehigh,
                        @priceaverage);

为显示检索出的产品的平均价格：

SELECT @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(2005, @total);
    SELECT @total;

由于第一个参数用的IN所以需要传入值，而OUT则表示相应的参数用来从存储过程传出的一个值返回给调用者；

还有就是IN一般与WHERE子句配套使用，而OUT则没有必要。

参数的数据类型：存储过程的参数允许的数据类型与表中的使用的数据类型相同。

二、调用存储过程

1.基本语法：call sp\_name()

CALL productpricing(@pricelow, 
                        @pricehigh,
                        @priceaverage);

注意：所有的变量名都必须以@开始；

存储过程名称后面必须加括号，哪怕该存储过程没有参数传递。

**三、删除存储过程**

存储过程在创建之后，被保存在服务器上以供使用，直至被删除。