Mysql的存储函数

weixin_45401140

已于 2023-03-03 18:18:32 修改

阅读量1.3k

点赞数 12

分类专栏： mysql的树状查询的实现文章标签： mysql

于 2020-04-11 17:55:55 首次发布

本文链接：https://blog.csdn.net/weixin_45401140/article/details/105455356

版权

mysql的树状查询的实现专栏收录该内容

1 篇文章 0 订阅

订阅专栏

文章目录

MySql

一、mysql的迭代查询自定义函数

concat()函数

功能：将多个字符串连接成一个字符串。

语法：concat(str1, str2,…)

在这里插入图片描述

通过set给变量赋值

自定义函数的语法

create function 函数名([参数列表]) returns 数据类型
begin
 sql语句;
 return 值;
end;

#查询语句，

SELECT * FROM org WHERE FIND_IN_SET(org_id, getChild(1))



#添加自定义函数

delimiter /
DROP FUNCTION IF EXISTS `test`.`getChild` /  
CREATE FUNCTION `getChild`(rootId INT) 
RETURNS VARCHAR(1000)
BEGIN 
DECLARE ptemp varchar(1000); #定义变量
DECLARE ctemp varchar(1000);
       SET ptemp = '#';   #通过set给变量赋值
       SET ctemp =cast(rootId as CHAR);
       while  ctemp is not null DO
             SET ptemp = concat(ptemp,',',ctemp); 
            SELECT group_concat(org_id) INTO ctemp FROM org   
            WHERE  FIND_IN_SET(pid,ctemp)>0; 
       END while  ;  
       RETURN ptemp;  
 END

展示添加的自定义函数


show create function getChild;

在这里插入图片描述
查询

SELECT * FROM org WHERE FIND_IN_SET(org_id, getChild(1))

在这里插入图片描述
库结构

项目开发的目录结构

在这里插入图片描述

实体类的封装

public class Node {
    //行数据id
    private Integer org_id;
    //节点名字
    private String  name;
    private List<Node> next;
    }

application.xml配置文件

server:
  port: 9999
  context-path: /MyLog
  jsp-servlet:
    init-parameters:
      development: true

spring:
  mvc:
    view:
      suffix: .jsp
      prefix: /
  datasource:
    username: root
    password: root
    #url: jdbc:mysql://localhost:3306/hadoop
    url: jdbc:mysql://localhost:3306/test #验证mysql树状查询
    driver-class-name: com.mysql.jdbc.Driver
mybatis:
  type-aliases-package: com.baizhi.entity
  mapper-locations: classpath:com/baizhi/dao/*Mapper.xml

dao接口的开发

 //--------------查询所有的子节点
    public List<Node> getNodeTree();

dao的xml配置文件

<mapper namespace="com.baizhi.dao.IntoDb">
<!-- 基础的节点
   select 表示要迭代的sql语句 -->


    <resultMap id="BaseTreeResultMap" type="Node">
        <result column="org_id" property="org_id"/>
        <result column="name" property="name"/>

        <collection column="org_id" property="next" javaType="java.util.ArrayList"
                    ofType="Node" select="getNextNodeTree"/>
    </resultMap>



    <resultMap id="NextTreeResultMap" type="Node">
        <result column="org_id" property="org_id"/>
        <result column="name" property="name"/>
        <collection column="org_id" property="next" javaType="java.util.ArrayList"
                    ofType="Node" select="getNextNodeTree"/>
    </resultMap>

    <select id="getNextNodeTree" resultMap="NextTreeResultMap">
        SELECT
        *
        FROM org
        WHERE pid = #{org_id}
    </select>

    <select id="getNodeTree" resultMap="BaseTreeResultMap">
        SELECT
        *
        FROM org
        WHERE pid = 0
    </select>

</mapper>

测试

package com.baizhi.test;

import com.baizhi.Application;
import com.baizhi.dao.IntoDb;
import com.baizhi.entity.Node;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import java.util.List;

@RunWith(SpringRunner.class)
@SpringBootTest(classes = Application.class)
public class t1 {

    @Autowired
    private IntoDb intoDb;

    @Test
    public  void  test1(){

        List<Node> nodeTree = intoDb.getNodeTree();
        for (Node node : nodeTree) {
            System.out.println(node);
        }
    }
}

执行结果

Node{org_id=1, name='温州市', next=[Node{org_id=4, name='温A', next=[Node{org_id=6, name='温A1', next=[]}, Node{org_id=7, name='温A2', next=[]}]}, Node{org_id=5, name='温B', next=[Node{org_id=8, name='温B1', next=[]}, Node{org_id=9, name='温B2', next=[]}]}]}
Node{org_id=2, name='杭州市', next=[Node{org_id=10, name='杭A', next=[]}]}
Node{org_id=3, name='天津市', next=[]}

所需依赖

<?xml version="1.0" encoding="UTF-8"?>

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>com.baizhi</groupId>
  <artifactId>Statistical</artifactId>
  <version>1.0-SNAPSHOT</version>
  <packaging>war</packaging>

  <name>Statistical Maven Webapp</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.source>1.7</maven.compiler.source>
    <maven.compiler.target>1.7</maven.compiler.target>
  </properties>

  <!--继承springboot的父项目-->
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.7.RELEASE</version>
  </parent>




  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.12</version>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
      <groupId>org.mybatis.spring.boot</groupId>
      <artifactId>mybatis-spring-boot-starter</artifactId>
      <version>2.1.1</version>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
      <version>5.1.38</version>
    </dependency>

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
      <exclusions>
        <exclusion>
          <groupId>org.junit.vintage</groupId>
          <artifactId>junit-vintage-engine</artifactId>
        </exclusion>
      </exclusions>
    </dependency>


    <dependency>
      <groupId>log4j</groupId>
      <artifactId>log4j</artifactId>
      <version>1.2.17</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-hdfs -->
    <dependency>
      <groupId>org.apache.hadoop</groupId>
      <artifactId>hadoop-hdfs</artifactId>
      <version>2.6.0</version>
    </dependency>

    <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common -->
    <dependency>
      <groupId>org.apache.hadoop</groupId>
      <artifactId>hadoop-common</artifactId>
      <version>2.6.0</version>
    </dependency>

    <dependency>
      <groupId>org.apache.hadoop</groupId>
      <artifactId>hadoop-mapreduce-client-core</artifactId>
      <version>2.6.0</version>
    </dependency>
    <dependency>
      <groupId>org.apache.hadoop</groupId>
      <artifactId>hadoop-mapreduce-client-jobclient</artifactId>
      <version>2.6.0</version>
    </dependency>

    <dependency>
      <groupId>org.apache.hadoop</groupId>
      <artifactId>hadoop-mapreduce-client-common</artifactId>
      <version>2.6.0</version>
    </dependency>

    <dependency>
      <groupId>org.apache.hbase</groupId>
      <artifactId>hbase-client</artifactId>
      <version>1.2.4</version>
    </dependency>
    <dependency>
      <groupId>org.apache.hbase</groupId>
      <artifactId>hbase-common</artifactId>
      <version>1.2.4</version>
    </dependency>
    <dependency>
      <groupId>org.apache.hbase</groupId>
      <artifactId>hbase-protocol</artifactId>
      <version>1.2.4</version>
    </dependency>
    <dependency>
      <groupId>org.apache.hbase</groupId>
      <artifactId>hbase-server</artifactId>
      <version>1.2.4</version>
    </dependency>
    <!-- springboot与email的整合 -->

    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-mail</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-freemarker</artifactId>
    </dependency>
    <dependency>
      <groupId>org.apache.commons</groupId>
      <artifactId>commons-lang3</artifactId>
      <version>3.8.1</version>
    </dependency>


  </dependencies>

  <build>
    <finalName>Statistical</finalName>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.1.0</version>
        </plugin>
        <!-- see http://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_war_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.0.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.8.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>2.22.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-war-plugin</artifactId>
          <version>3.2.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>2.5.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>2.8.2</version>
        </plugin>
      </plugins>

    </pluginManagement>

    <plugins>
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-compiler-plugin</artifactId>
        <configuration>
          <source>8</source>
          <target>8</target>
        </configuration>
      </plugin>


      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>

    </plugins>

  </build>
</project>

六、mysql的自定义函数

创建自定义函数
CREATE FUNCTION simpleFun()RETURNS VARCHAR(20)
begin 
RETURN (select type  from  hd_payment_conf where site_id='0' and payment_id =14
);

end $

#删除自定义函数
drop function if exists simpleFun;
查看自定义函数的值

select  simpleFun()
#使用自定义函数
update hd_payment_conf set haidai_config =(select simpleFun());

存储函数创建

设置存储函数启动后失效，
set global log_bin_trust_function_creators=TRUE;
设置存储函数永久有效
set global log_bin_trust_function_creators=1;

DELIMITER $$

create function filter_underwritingTypecd(under_writing VARCHAR(5000))
returns varchar(5000)

begin 
    # 声明了一个局部变量c，并且指定它的默认值为1
		
		declare c VARCHAR(5000) ;   
		set c=under_writing;
    return c;
  
end $$
DELIMITER;

使用存储函数

-- 使用存储函数
select filter_underwritingTypecd('324');

删除存储函数

-- 删除存储函数
drop function filter_underwritingTypecd;

查看存储函数

-- 查看存储函数
show create  function filter_underwritingTypecd;

if函数使用

如果输入1返回你好，输入其他返回 world；


delimiter $$

create function var_demo(a int )
returns varchar(100) 
begin
 declare result varchar(100);
    if a=1 then set result='你好';
		else set result='world';
		end if;
		

    return result;
end $$

delimiter ;


select var_demo(2);
drop function var_demo;

循环语句

语法：

while express do
    语句列表
end while;

--  循环语句

delimiter $$

create function sum_all(n int unsigned)
returns int
begin
    declare result int default 0;
    declare i int default 1;
    while i <= n do
        set result = result + i;
        set i = i + 1;
    end while;
    return result;
end $$

delimiter ;

drop function sum_all
## 计算1+2+3+4+5 .累加和
select  sum_all(5)

weixin_45401140

关注

12
点赞
踩
9

收藏

觉得还不错? 一键收藏
1
评论
Mysql的存储函数

MySql一、mysql的迭代查询自定义函数concat()函数功能：将多个字符串连接成一个字符串。语法：concat(str1, str2,…)通过set给变量赋值自定义函数的语法create function 函数名([参数列表]) returns 数据类型begin sql语句; return 值;end;#查询语句，SELECT * FROM org WHE...
复制链接

扫一扫