Mysql高级笔记-创建过程+java实现调用创建过程

存储引擎

和大多数数据库不同，MySQL中有一个存储引擎的概念，针对不同的存储需求可以选择最优的存储引擎

存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。

MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB

存储过程

创建存储过程的基本格式

过程与函数的区别

**过程：**事先经过编译并存储在数据库中的一段 SQL 语句的集合

**函数：**函数是具有返回值的过程被称为函数

create procedure 过程名称 ( in / out / inout  参数名称 参数类型 )
begin
 ......
end;

调用存储过程

call 过程名称;

实例：

#创建函数过程
create procedure mytest(in a int,in b int,out sum int)
begin
 set sum=a+b;
end;

#调用函数过程
call mytest(1,2,@num);
select @num;

1、定义变量

MySQL 中的存储过程类似 java 中的方法。既然如此，在存储过程中也同样可以使用变量。java 中的局部变量作用域是变量所在的方法，而 MySQL 中的局部变量作用域是所在的存储过程

declare 变量名 变量类型 default 默认值;

变量赋值

set 变量名 = 表达式值;

实例

#创建过程
create procedure mytest02()
begin
 declare `nametest` varchar(20);
 set `nametest` = '张三';
 select * from `user` where `name` = `nametest`;
end;
#调用过程
call mytest02();

判断

2、if条件语句

IF 语句包含多个条件判断，根据结果为 TRUE、FALSE 执行语句，与编程语言中的 if、else if、else 语法类似。

if then 条件
  ...
elseif then 条件
  ...
else
  ...
end if;

实例

#创建过程
create procedure mypro2(in num int)
begin
	if num<0 then -- 条件开始
		select '负数';
	elseif num=0 then
		select '不是正数也不是负数';
	else
		select '正数';
	end if;-- 条件结束	
end;

#调用过程
call mypro2(-1);

3、case条件语句

case 语句的基本格式

case 
	when 条件 then ...;
	when 条件 then ...;
	...
else ...
end case;

实例

#创建过程
create procedure casetest(in num int)
begin
	case
		when num > 0 then select '正数';
		when num < 0 then select '负数';
  else select '0';
	end case;
end;

#调用过程
call casetest(10);
call casetest(0);
call casetest(-1);

循环遍历

4、while循环语句

while语句的用法和java中的while循环类似

基本格式如下：

while 条件 do
....
end while;

实例

#创建过程
create procedure whiletest(out sum int)
begin 
	declare num int default 0; # 默认num的数值为0
	set sum = 0;
	while num < 10 do
		set num = num + 1;
		set sum = sum + num;
	end while;
end;

#调用过程
call whiletest(@sum);
select @sum;

5、repeat循环语句

repeat 语句的用法和 java 中的 do…while 语句类似，都是先执行循环操作，再判断条件，区别是 repeat 表达式值为 false 时才执行循环操作，直到表达式值为 true 停止。

基本格式如下：

repeat 
.... 循环体
until #循环条件
end repeat;

实例如下：

#创建过程
create procedure repeattest(out sum int)
begin
	declare num int default 0;
	set sum = 0;
	repeat
		set num = num + 1;
		set sum = sum + num;
	until num >= 10
	end repeat;
end;

#调用过程
call repeattest(@sum);
select @sum;

6、loop循环语句

循环语句，用来重复执行某些语句。执行过程中可使用 leave 语句或 iterate 跳出循环，也可以嵌套 IF 等判断语句。
1、leave 语句效果相当于 java 中的 break，用来终止循环；
2、iterate 语句效果相当于 java 中的 continue，用来结束本次循环操作，进入下一次循环。

基本格式如下：

循环名称:loop
 ...
 if 条件 then 
 ...
 leave 循环名称; / iterate;
end if;
end loop 循环名称;

实例：

#创建过程
create procedure looptest(out sum int)
begin
	declare num int default 0;
	set sum = 0;
	loop_sum:loop
	set num = num + 1;
	set sum = sum + num;
	if num >= 10 then 
		leave loop_sum;
	end if;
	end loop loop_sum;
end;

#调用过程
call looptest(@sum);
select @sum;

过程管理

7、显示所有存储过程

show procedure status;

8、显示特定数据库的存储过程

show procedure status where `name` = '存储过程名称';

9、显示特定模式的存储过程，模糊查询

show procedure status `name` like '%模糊查询%';

10、显示存储过程的源码

show create procedure 过程名称;

11、删除存储过程

drop procedure 过程名称;

光标

光标的基本格式

#定义初始化光标：
	declare 光标名称 cursor for select.... 查询语句;
#打开光标：
	open 光标名称;
#遍历光标：
	fetch 光标名 into 变量名1,变量名2,.....; #变量要与光标中字段对应，变量接收光标中的一行信息
	select concat('id=',e_id,',name=',e_name,',age=',e_age,',salary',e_salary);  #遍历
#关闭光标：
	close 变量名;

函数

函数的基本格式

#声明函数
create function 函数名称(形参名称 形参类型)
#声明返回值类型
return 返回值类型
#函数体
begin 
	#定义变量
	declare cnum int(11);
	......
	select count(1) into cnum from city where country_id=countryId;
	......
    #返回值
	return cnum;
end;

触发器

触发器是与表有关的数据库对象，指在inset/update/delete之前或之后，触发并执行触发器中定义的SQL语句的集合

OLD表示更新之前的数据

NEW表示更新之后的数据

触发器类型	NEW 和 OLD的使用
INSERT 型触发器	NEW 表示将要或者已经新增的数据
UPDATE 型触发器	OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器	OLD 表示将要或者已经删除的数据

现在Mysql触发器还只支持行级触发，不支持语句级触发（Oracle支持语句级触发）

创建触发器

create trigger 触发器的名称
before / after insert / update / delete
on 检测的表名
[ for each row ] 行级触发器
begin 
	.....
	触发体
	..... 
end;

删除触发器

如果没有指定 schema_name，默认为当前数据库 。

drop trigger [schema_name.]trigger_name

查看触发器

可以通过执行 SHOW TRIGGERS 命令查看触发器的状态、语法等信息。

show TRIGGERS

java调用存储过程

SpringBoot+Mybatis整合调用MySQL数据库中的存储过程

1、SpringBoot整合MyBatis框架

根据Mapper.xml文件的位置分为两种情况

添加依赖

  	<!--Mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.0</version>
        </dependency>

配置文件

#数据库配置
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=123456

主启动类

//根据情况添加
@MapperScan(basePackages = "com.xx.xx.dao")

方式一：

Mapper.xml文件位于java.main.com.xx.xx.dao中时，需要在pom.xml文件中设置源文件读取位置

   <build>
        <!--将指定文件下的xml、properties文件读取到classpath下-->
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
            <!--
              不配置特殊文件读取时，是只读取src/main/resources文件下
              当配置过其他路径后，原路径会被替代，所以需要重新配置
            -->
            <resource>
                <directory>src/main/resources</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>false</filtering>
            </resource>
        </resources>
    </build>

方式二：

mapper.xml文件在resources中添加，需要在配置文件中设置读取位置

mybatis.mapper-locations=classpath:mapper/*.xml

2、调用MySQL创建过程

有参有返回值

创建过程

#基本函数格式
create procedure mytest(in a int,in b int,out sum int)
begin
 set sum=a+b;
end;

dao层接口

public interface MyTest {
    void myTest(HashMap<String,Object> param);
}

Mapper.xml文件

<select id="myTest" parameterMap="seedMap" statementType="CALLABLE">
    {call mytest(#{a,mode=IN},#{b,mode=IN},#{sum,mode=OUT,jdbcType=INTEGER})}
</select>
<parameterMap id="seedMap" type="java.util.Map">
    <parameter property="a" jdbcType="INTEGER" mode="IN"/>
    <parameter property="b" jdbcType="INTEGER" mode="IN"/>
    <parameter property="sum" jdbcType="INTEGER" mode="OUT"/>
</parameterMap>

调用方法

    @GetMapping(value = "/get/{a}/{b}/sum.do")
    public Object getSum(@PathVariable(value = "a") Integer a,@PathVariable(value = "b") Integer b){
        Map<String,Object> param = new HashMap<>();
        param.put("a", a);
        param.put("b", b);
        myTestService.mytest(param);
        return param.get("sum");
    }

无参有返回值

创建过程

#变量定义
create procedure mytest02()
begin
 declare `nametest` varchar(20);
 set `nametest` = '张三';
 select * from `user` where `name` = `nametest`;
end;

dao层接口

public interface MyTest {
    List<Map<String,Object>> mytest02();
}

Mapper.xml文件

<select id="mytest02" resultMap="resultMap">
    {call mytest02()}
</select>
<resultMap id="resultMap" type="map">
    <result column="uid" property="uid"/>
    <result column="username" property="username"/>
    <result column="password" property="password"/>
    <result column="name" property="name"/>
    <result column="email" property="email"/>
    <result column="telephone" property="telephone"/>
    <result column="birthday" property="birthday"/>
    <result column="sex" property="sex"/>
    <result column="state" property="state"/>
    <result column="code" property="code"/>
</resultMap>

调用方法

 @GetMapping(value = "/get/getUser.do")
 public List<Map<String,Object>> getUser(){
    return myTestService.mytest02();
 }

```

调用方法

 @GetMapping(value = "/get/getUser.do")
 public List<Map<String,Object>> getUser(){
    return myTestService.mytest02();
 }