存储过程创建及springboot代码调用存储过程


阿里推荐最好不使用存储过程，因为存储过程代码过长涉及逻辑太多，导致修改业务时存储过程代码难以下手；于是没看过存储过程；

导致要用的时候不会，但是作为一名开发还是要会存储过程，于是百度学习了一波在此记录；

我是在navacat中创建的存储过程



右键函数选择新建函数



自定义函数名，选择过程；



然后添加输入输出参数点击完成；

我这里是输出了三个参数；这样存储过程就创建完成了；

右键运行存储过程函数也是生效的



接下来就要考虑在项目中怎么实现调用创建好的存储过程；

import com.lansi.realtynavi.mapper.pojo.DataInfoPo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallback;
import org.springframework.transaction.support.TransactionTemplate;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Description 存储过程
 * @Date 2021/3/18 13:50
 * @Created by nuoyi
 */
@Component
public class ProcedureReturnListExecutor {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    @Autowired
    private TransactionTemplate template;
    public List<DataInfoPo> get(){
        ProcedureReturnListTransactionCallback callback = new ProcedureReturnListTransactionCallback();
        return template.execute(callback);
    }
    class ProcedureReturnListTransactionCallback implements TransactionCallback<List<DataInfoPo>> {
        @Override
        public List<DataInfoPo> doInTransaction(TransactionStatus transactionStatus) {
            return jdbcTemplate.execute(new CallableStatementCreator() {
                @Override
                public CallableStatement createCallableStatement(Connection con) throws SQLException {
                    String procedure = "{call selectData(?,?,?)}";
                    CallableStatement cs = con.prepareCall(procedure);
                    cs.registerOutParameter(1, Types.INTEGER);
                    cs.registerOutParameter(2, Types.BIGINT);
                    cs.registerOutParameter(3, Types.BIGINT);
                    return cs;
                }
            }, new CallableStatementCallback<List<DataInfoPo>>() {
                @Override
                public List<DataInfoPo> doInCallableStatement(CallableStatement cs)
                        throws SQLException, DataAccessException {
                    ResultSet rs = cs.executeQuery();
                    List<DataInfoPo> list = new ArrayList<>();
                    while (rs.next()) {
                        Integer id = rs.getInt(1);
                        Long dataInfo = rs.getLong(2);
                        Long dataTime = rs.getLong(3);
                        DataInfoPo dataInfoPo = new DataInfoPo();
                        dataInfoPo.setId(id);
                        dataInfoPo.setData_info(dataInfo);
                        dataInfoPo.setData_time(dataTime);
                        list.add(dataInfoPo);
                    }
                    return list;
                }
            });
        }
    }
}



在自己需要用的地方调用即可



这样就完成啦。

1.存储过程简介

存储过程（Stored Procedure）是数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。在数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL 语句和流程控制语句的集合。

 2.创建存储过程的基本代码结构

CREATE PROCEDURE Procedure_Name  
 
--Procedure_Name为存储过程名(不能以阿拉伯数字开头)，在一个数据库中触发器名是唯一的。名字的长度不能超过个字。PROCEDURE可以简写为PROC。
	 
@Param1 Datatype,@Param2 Datatype 
	
--@Param1和@Param2为存储过程的参数，Datatype为参数类型,多个参数用逗号隔开,最多允许个参数。
	
AS --存储过程要执行的操作 
 
BEGIN
	
--BEGIN跟END组成一个代码块，可以写也可以不写，如果存储过程中执行的SQL语句比较复杂，用BEGIN和END会让代码更加整齐，更容易理解。
 
    
END
GO --GO就代表结操作完毕　　
 
 
exec Procedure_Name [参数名] --调用存储过程Procedure_Name。
 
drop procedure Procedure_Name --删除存储过程Procedure_Name，不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程
 
show procedure status --显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
 
show create procedure Procedure_Name --显示存储过程Procedure_Name的详细信息
 
exec sp_helptext Procedure_Name --显示你这个Procedure_Name这个对象创建文本


 

    

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author:        bin
-- Create date: 2009-07-22 12:41
-- Description:    分页，用到了ROW_NUMBER()
-- =============================================
create PROCEDURE [dbo].[proc_SplitPage_wb]
@tblName   varchar(255),       -- 表名
@strFields varchar(1000) = '*', -- 需要返回的列，默认*
@strOrder varchar(255)='',      -- 排序的字段名，必填
@strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC
@PageSize   int = 10,          -- 页尺寸,默认10
@PageIndex int = 1,           -- 页码,默认1
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL   varchar(5000)

if @strWhere !=''
set @strWhere=' where '+@strWhere

set @strSQL=
'SELECT * FROM ('+
    'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS pos,'+@strFields+' '+
    'FROM '+@tblName+' '+@strWhere+
') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)


exec (@strSQL)

 

转载于:https://www.cnblogs.com/cookies9/archive/2013/02/21/2921363.html

为数据库中所有表生成创建存储数据的存储过程代码
Author：CoolRzs
QQ    ：61437524
E-Mail：CoolRzs@163.com
Date  ：13:30 2006-12-18
        没有系统学过 SQL Server 知识，Transact-SQL 更是只在书本中东拼西凑的看了点，昨晚突然想这样写段代码来为数据库中所有表存储数据生成存储过程；也不知道这样写对不对，合不合规范，主要是想借此学习一下游标的使用；不过想要的结果倒是得到了，贴在这里，希望道上的高手给指点指点。
/*
过 程 名 : sp_CreateStoreProcedure
功能描述 : 为数据库中所有表生成创建存储数据的存储过程代码
作    者 : CoolRzs
*/
DECLARE @s VARCHAR(8000), @v VARCHAR(8000), @outfield varchar(20), @tabname VARCHAR(20), @tabid INT, @fieldname VARCHAR(20), @Type VARCHAR(20), @Length VARCHAR(4)
SET @tabname = ''
SET @tabid = 0
DECLARE cur_TabName SCROLL CURSOR FOR 
SELECT id, OBJECT_NAME(id) AS tabname FROM sysobjects WHERE xtype = 'U' AND OBJECTPROPERTY (id, 'IsMSShipped') = 0
OPEN cur_TabName
FETCH FIRST FROM cur_TabName INTO @tabid, @tabname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @s= ''
PRINT '/* ==sp_Store' + @tabname + ' Begin== */'
PRINT 'if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[sp_Store' + @tabname + ']'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)'
PRINT 'drop procedure [dbo].[sp_Store' + @tabname + ']'
PRINT 'GO'
PRINT 'SET QUOTED_IDENTIFIER OFF' 
PRINT 'GO'
PRINT 'SET ANSI_NULLS OFF '
PRINT 'GO'
PRINT 'CREATE PROCEDURE sp_Store' + @tabname
DECLARE cur_FieldName SCROLL CURSOR FOR 
Select c.name, t.name, c.length from syscolumns c inner join systypes t on c.xtype = t.xtype where id = @tabid order by c.colorder
OPEN cur_FieldName
FETCH FIRST FROM cur_FieldName INTO @fieldname, @Type, @Length
WHILE @@FETCH_STATUS = 0
BEGIN
IF (LOWER(@fieldname) = 'id') AND (LOWER(@Type) = 'int')
SET @outfield =  '@' + @fieldname + ' ' + @Type + ' output'
ELSE
BEGIN
IF (LOWER(@Type) = 'varchar')
--这里仅判断了 varchar 类型，实际应用中可能会有更多需要这样判断的类型；
BEGIN
PRINT '@' + @fieldname + ' ' + @Type + '(' + @Length + '),'
END
ELSE
BEGIN
PRINT '@' + @fieldname + ' ' + @Type + ','
END
END
IF (LOWER(@fieldname) <> 'id') 
IF (@s = '')
BEGIN
SET @s = @fieldname
SET @v = '@' + @fieldname
END
ELSE 
BEGIN
SET @s = @s + ', ' + @fieldname
SET @v = @v + ', @' + @fieldname
END
FETCH NEXT FROM cur_FieldName INTO @fieldname, @Type, @Length
END
CLOSE cur_FieldName
DEALLOCATE cur_FieldName
PRINT @outfield
PRINT 'AS'
PRINT 'INSERT INTO ' + @tabname + ' (' + @s + ') '
PRINT 'VALUES (' + @v + ')'
PRINT 'SET @id =  @@IDENTITY'
PRINT 'GO'
PRINT 'SET QUOTED_IDENTIFIER OFF '
PRINT 'GO'
PRINT 'SET ANSI_NULLS ON '
PRINT 'GO'
PRINT '/* ==sp_Store' + @tabname + ' End== */'
PRINT ''
FETCH NEXT FROM cur_TabName INTO @tabid, @tabname
END
CLOSE cur_TabName
DEALLOCATE cur_TabName
--＝转载请保持以上信息的完整，谢谢！＝--
--[完]