Oralce 递归sql

一、查询所有子节点

SELECT *
FROM district
START WITH NAME ='平昌县'
CONNECT BY PRIOR parent_id=ID

二、查询所有父节点

SELECT *
FROM district
START WITH NAME ='平昌县'
CONNECT BY PRIOR parent_id=ID

这个语法很好理解，就是递归语法，从什么节点依次去找。。

引用文献：https://www.cnblogs.com/Soprano/p/10659127.html

Mybatis 递归查询

 <resultMap id="getSelf" type="net.chunxiao.vo.GoodsCategoryVo">
        <id column="goodscateid" property="goodscateid"></id>
        <result column="name" property="name"></result>
        <collection property="catelist" select="getCategory" column="goodscateid"></collection>
        <!--查到的cid作为下次的pid-->
    </resultMap>
 
    <select id="getCategory" resultMap="getSelf">
        select * from goods_category where status=0 AND parentid=#{pid}
        ORDER BY displayorder,goodscateid

mybatis 接口：

List<GoodsCategoryVo> getCategory(Integer pid);

实体类：

public class GoodsCategoryVo {
    private Integer goodscateid;
    private String name;
    private Integer parentid;
    private String description;
    private Integer displayorder;
    private Double commissionrate;
    private Integer enabled;
    private List<GoodsCategoryVo> catelist;
……
}

参考文献：https://blog.csdn.net/janet796/article/details/79500349

mybatsi 调用存储过程：

mybatis 接口：

void addDep(@Param("dep") Department department);

xml中写法：

<select id="addDep" statementType="CALLABLE">
    call addDep(#{dep.name,mode=IN,jdbcType=VARCHAR},#{dep.parentId,mode=IN,jdbcType=INTEGER},#{dep.enabled,mode=IN,jdbcType=BOOLEAN},#{dep.result,mode=OUT,jdbcType=INTEGER},#{dep.id,mode=OUT,jdbcType=BIGINT})
</select>

解释：

注意statementType调用表示这是一个存储过程，mode=IN表示这是输入参数，mode=OUT表示这是输出参数，调用成功之后，在service中获取department的id和result字段，就能拿到相应的调用结果了。

参考文献：https://github.com/lenve/vhr/wiki/13.%E9%80%92%E5%BD%92%E6%9F%A5%E8%AF%A2%E4%B8%8E%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B%E8%B0%83%E7%94%A8