开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL-存储过程 -> 正文阅读

[大数据]MySQL-存储过程

前言

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点

存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程的性能调校与撰写，受限于各种数据库系统。

第一个例子

我们通过一个简单的例子来学习存储过程的基本语法
基本语法：

CREATE PROCEDURE produce_name(参数列表)
begin -- 表示过程体开始{ --
   -- 这里就是你的业务逻辑代码了 --
end -- 表示过程体结束} --

案例：
先说几个问题，我这里用的是Navicat。Navicat中默认的语句结束符号是“;” 但是在存储过程中我们可能会有多条语句，每条语句的结束都是“；”，为了防止创建存储过程中Navicat就使用“；”作为结束符号，我们需要重新申明结束符号：

DELIMITER $$
或
DELIMITER //

tips : 当然这个符号你可以自己定义（不要太随意就行）。
来吧！看看案例：

-- 创建存储过程统计图书的数量 --
-- 修改结束标记 --
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_bookcount;
-- 创建存储过程 --
CREATE PROCEDURE proc_bookcount()
BEGIN
	-- 申明变量 --
	DECLARE v_count int;
	-- 给变量赋值 --
	select count(*) into v_count from g_book;
	-- 查询count --
	select v_count;
END $$
-- 将结束标记修改回来 --
DELIMITER ;

执行之后查看左边的结构视口：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jRIr1EJG-1632642140911)(MySQL-存储过程.assets/image-20210926085606454.png)]

执行存储过程：

-- 执行存储过程 --
CALL proc_bookcount;

删除存储过程：

DROP PROCEDURE proc_bookcount;

当然我们上面的存储过程仅仅是执行了一条查询语句。
存储过程中可以有业务逻辑，那么就需要基本的流程控制。
所以我们要写出一定质量的存储过程，就需要了解存储过程中的一些基本语法。
tips：只要你学过编程，这些都很简单。
当然，让我们觉得恶心的就是，每个不同的数据库中的存储过程的语法都不一样。这也是前面说过的存储过程最大的缺点。

基本语法

变量

MySQL的变量有：全局变量，会话变量，用户变量，系统变量，局部变量。
我们这里重点研究存储过程的使用，暂时只说局部变量。
局部变量就是申明在BEGIN和END之间的变量，作用域也就是在BEGIN和END之间。
申明变量：使用DECLARE关键字

DECLARE var_name var_type [default_value]

案例：

-- 修改结束标记 --
DELIMITER $$
-- 创建存储过程 --
CREATE PROCEDURE proc_bookcount()
BEGIN
	-- 申明变量 --
	DECLARE v_isbn varchar(255) default '9787111573319';
	DECLARE v_title varchar(255);
	-- 使用set给变量赋值 --
	set v_isbn = '9787302444541';
	-- 使用 select into 的方法给变量赋值 --
	SELECT title into v_title from g_book where isbn = v_isbn;
	-- 显示两个变量 --
	select v_isbn,v_title;
END $$
-- 将结束标记修改回来 --
DELIMITER ;

变量申明：

DECLARE v_isbn varchar;

给变量赋值有两种方式：
方式1：直接set

set v_bookid = 1;

方式2：使用select into

select count(*) into v_count from book

tips：select后面的列必须和变量对应起来。

比如:

select isbn,title into v_isbn,v_title from book where booid = 1

案例：

局部变量一定要放在存储过程的开头

CREATE PROCEDURE proc_bookcount()
BEGIN
	-- 申明变量 --
	DECLARE v_isbn varchar(255) default '9787111573319';
	DECLARE v_title varchar(255);
	-- 使用set给变量赋值 --
	set v_isbn = '9787302444541';
	-- 使用 select into 的方法给变量赋值 --
	SELECT isbn,title into v_isbn,v_title from g_book where isbn = v_isbn;
	-- 显示两个变量 --
	select v_isbn,v_title;
END $$

流程控制语句

分支语句

[1] if…then …else
语法

-- 只有if的结构 --
if 条件  then
 执的语句
end if;
--if.. else --
if 条件  then
 	if语句
else
	else语句
end if;
-- 多重分支 --
if 条件  then
 	if语句
elseif 条件  then
	语句。。。
	。。。。。
else
	else语句
end if;

案例：

-- 分支语句IF --
DELIMITER $$
create PROCEDURE proc_demo()
begin
	declare v_num int;
	declare v_result varchar(100) default '奇数';
	set v_num = CEIL(RAND() * 100);
	IF mod(v_num , 2) = 0 THEN
		set v_result = '偶数';
	end if;
	select CONCAT('数字',v_num,'是',v_result);
end $$
DELIMITER ;
-- if .. else  --
DELIMITER $$
create PROCEDURE proc_demo()
begin
	declare v_num int;
	declare v_result varchar(100);
	set v_num = CEIL(RAND() * 100);
	IF mod(v_num , 2) = 0 THEN
		set v_result = '偶数';
	ELSE
		set v_result = '奇数';
	end if;
	select CONCAT('数字',v_num,'是',v_result);
end $$
DELIMITER ;
-- 多重if --
DELIMITER $$
create PROCEDURE proc_demo()
begin
	declare v_num int;
	declare v_result varchar(100);
	set v_num = CEIL(RAND() * 100);
	IF mod(v_num , 2) = 0 THEN
		set v_result = '偶数';
	elseif mod(v_num , 3) THEN
		set v_result = '3的倍数';
	ELSE
		set v_result = '奇数';
	end if;
	select CONCAT('数字',v_num,'是',v_result);
end $$
DELIMITER ;

调用上面的过程

CALL proc_demo;

[2]CASE结构
直接上菜吧：

DELIMITER $$
CREATE PROCEDURE PROC_DEMO()
BEGIN
	DECLARE v_num int;
	set v_num = ROUND(RAND() * 10);
	CASE v_num
	when MOD(v_num,2)=0 THEN
		select '偶数';
	when MOD(v_num,3)=0 THEN
		select '3的倍数';
	else
		select '奇数';
	end case;
END $$
DELIMITER ;

循环语句

[1]while ···· end while

while 条件 do
    --循环体
end while

DELIMITER $$
CREATE PROCEDURE PROC_DEMO()
BEGIN
	DECLARE v_num int default 0;
	while v_num < 10 do
	insert into temp values(v_num);
	set v_num = v_num + 1;
	end while;
END $$
DELIMITER ;

[2]repeat···· end repeat

repeat
    --循环体
until 循环条件  
end repeat;

DELIMITER $$
create PROCEDURE proc_demo()
begin
	declare v_num int default 0;
	repeat 
		insert into temp values(v_num);
		set v_num = v_num + 1;
		-- 退出循环 --
		until v_num >=10 end repeat;
end $$
DELIMITER ;

[3]loop ·····endloop

loop 循环不需要初始条件，这点和 while 循环相似，同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环。

DELIMITER $$
create PROCEDURE proc_demo()
begin
	declare v_num int default 10;
	-- 这里准备一个循环标记loop_lable，这个名字可以随意 --
	loop_lable:loop 
		insert into temp values(v_num);
		set v_num = v_num + 1;
		-- 退出循环 --
		if v_num >= 20 THEN
			leave loop_lable;
		end if;
	end loop;
end $$
DELIMITER ;

使用loop实现一个双层循环：

DELIMITER $$
create PROCEDURE proc_demo()
begin
	declare v_num int default 0;
	declare v_inner_number int default 0;
	-- 这里准备一个循环标记loop_lable，这个名字可以随意 --
	loop_lable:loop 
		set v_inner_number = 0;
		loop_inner_lable:loop
			insert into temp values(CONCAT(v_num,v_inner_number));
			set v_inner_number = v_inner_number + 1;
			if v_inner_number >= 5 THEN
				leave loop_inner_lable;
			end if;
		end loop;
		set v_num = v_num + 1;
		if v_num >= 3 THEN
			leave loop_lable;
		end if;
	end loop;
end $$
DELIMITER ;

存储过程的参数

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

CREAT EPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

1、in 输入参数

就简单理解为java方法的参数
为了确保我们的数据表结构不外漏，所以我们准备一个存储添加数据

-- 创建一个添加图书的存储过程  -- 
DELIMITER $$
create procedure proc_addbook(in p_isbn varchar(255),in p_title varchar(255),in p_cost float,p_price float,p_pid varchar(10),p_category int)
BEGIN
	insert into g_book values(p_isbn,p_title,p_cost,p_price,p_pid,p_category);
END $$
DELIMITER ;

额~~~~
调用有输入参数的存储过程就需要传入对应的参数

call proc_addbook('95286589','一个废铁的练成史',56.3,185,'P005',1)

2、out输出参数

就简单的理解为java中方法的返回值

--  输出参数的存储过程 --
-- 定义存储过程，查询指定名称的出版社的图书的数量,并且以输出参数返回 --
DELIMITER $$
create PROCEDURE proc_countbypublisher(in p_pname varchar(255),out p_count int)
BEGIN
	-- 将查询的结果赋值给输出参数 --
	select count(*) into p_count from g_book where pid = (select pid from g_pubsher where pname = p_pname);
end $$
DELIMITER ;

调用有输出参数的存储过程

set @count_out=0;
call proc_countbypublisher('机械工业出版社',@count_out);
select @count_out '机械工业出版社的图书数量';

关于用户变量的说明：

上面的调用过程中，我们使用了用户变量 @count_out
用户变量都是以@开头，默认是在一次连接中有效的。

3、inout输入参数

额！！！就字面意思。既能输入，也能输出。其实知道就好，尽量不要使用这种类型的参数

--  inout参数的存储过程 --
-- 定义存储过程，查询指定名称的出版社的图书的数量,并且以输出参数返回 --

DELIMITER $$
create PROCEDURE proc_countbypublisher1(inout param varchar(255))
BEGIN
	-- 将查询的结果赋值给输出参数 --
	select count(*) into param from g_book where pid = (select pid from g_pubsher where pname = param);
end $$
DELIMITER ;

调用

-- 调用有inout的存储过程 --
set @param = '机械工业出版社';
call proc_countbypublisher1(@param);
select @param '机械工业出版社的图书数量'

JDBC调用存储过程

准备一个分页查询的存储过程

在这个存储过程中需要执行字符串形式的sql，需要用户变量。

DELIMITER $$
create PROCEDURE proc_page(tableName varchar(255),pageNum int,pageSize int,term varchar(255),out total int)
BEGIN
	declare startIndex int default 0;
	declare done int default 0;
	-- 申明用户变量保存总条数 --
	set @v_total = 1;
	-- 执行sql查询总条数 这里的用户变量在编译的时候，可以继续使用 --
	set @v_total_sql = concat('select count(*) into @v_total from ',tableName);
	if term!=''  THEN
		set @v_total_sql = CONCAT(@v_total_sql,' where ',term);
	end if;
	prepare s0 from @v_total_sql;
	execute s0;
	deallocate prepare s0;
	set total = @v_total;
	-- 根据参数拼接sql语句 --
	set @v_sql = CONCAT('select * from ',tableName);
	if term!=''  THEN
		set @v_sql = CONCAT(@v_sql,' where ',term);
	end if;
	-- 计算开始位置 --
	set startIndex = (pageNum-1)*pageSize;
	set @v_sql = CONCAT(@v_sql,' limit ',startIndex,',',pageSize);
	prepare s1 from @v_sql;
	execute s1;
	deallocate prepare s1;
end $$
DELIMITER ;


drop PROCEDURE proc_page

-- 调用测试--
set @out_total = 0;
call proc_page('g_book',1,3,'',@out_total);
select @out_total;

JDBC调用存储过程：

package com.st.dao;

import java.sql.*;
import java.util.concurrent.Callable;

/**
 * @author 戴着假发的程序
 */
public class ProcTestDAO extends BaseDAO{
    public void queryPaperByProc() throws SQLException {
        Connection con = getCon();
        // CallableStatement可以用来执行存储过程
        // sql语句必须使用｛｝包裹。 无论是什么参数都使用？占位
        CallableStatement cst = con.prepareCall("{call proc_page(?,?,?,?,?)}");
        //设置参数
        // 输入参数直接设置
        cst.setString(1,"paper");
        cst.setInt(2,2);
        cst.setInt(3,5);
        cst.setString(4,"paper_title like '%问卷%'");
        //输出参数是要注册的。
        cst.registerOutParameter(5, Types.INTEGER);
        //执行
        ResultSet rs = cst.executeQuery();
        // 取出注册的参数的值
        int total = cst.getInt(5);
        System.out.println("总体条数："+total);
        while(rs.next()){
            System.out.println(rs.getString("paper_title"));
        };
    }

    public static void main(String[] args) {
        try {
            new ProcTestDAO().queryPaperByProc();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

大数据最新文章

实现Kafka至少消费一次

亚马逊云科技：还在苦于ETL？Zero ETL的时代

初探MapReduce

【SpringBoot框架篇】32.基于注解+redis实现

Elasticsearch：如何减少 Elasticsearch 集

Go redis操作

Redis面试题

专题五 Redis高并发场景

基于GBase8s和Calcite的多数据源查询

Redis——底层数据结构原理

加:2021-09-27 14:09:28 更:2021-09-27 14:10:07

360图书馆购物三丰科技阅读网日历万年历 2024年11日历

-2024/11/23 22:49:47-

图片自动播放器
↓图片自动播放器↓

TxT小说阅读器
↓语音阅读,小说下载,古典文学↓

一键清除垃圾
↓轻轻一点,清除系统垃圾↓

图片批量下载器
↓批量下载图片,美女图库↓

网站联系: qq:121756557 email:121756557@qq.com IT数码