sqlserver JdbcTemplate 调用存储过程

1、存储过程和存储函数区别：
存储函数：可以有一个返回值，但是可以通过out参数返回多个值
存储过程：没有返回值，但是可以通过out参数返回多个值
原则：如果有多个返回值就使用存储过程。

存储过程语法格式：

CREATE PROCEDURE [dbo].[]
(@diff int,
@TJ Varchar(50),
@Param Varchar(4000) out)      
                                             --传出时CVALUE      
As          
  Declare @FParam Varchar(20)        
  Declare @I int      
  Declare @B bit      
  Declare @type int      
  Declare @Length int      
  Declare @CFYBZ varchar(10)         
  set @FParam=@Param          
  set @Param=''       
  set @B = (select Bintran from tbsysparam2 where CBH = @FParam and CTJ = @TJ) 
  if @B = 0        
    begin Tran     
  Update tbsysparam2 With (RowLock) set Cvalue = '0' where CBH = @FParam and CTJ = @TJ and ( (ICSHFS = 1 and DATEPART(d, getdate())<> DATEPART(d,DUpdate)) or (ICSHFS = 2 and month(DUpdate)<>month(getdate()))  or (ICSHFS = 3 and year(DUPdate) <> year(getdate(
))) )         
  Update tbsysparam2  With (ROWLOCK) set CValue=CValue+@diff,DUpDate = GetDate()  where CBH = @FParam and CTJ = @TJ      
  select @Param=CValue-(@diff-1),@type = Itype,@length = Ilength,@CFYBZ=ISNULL(CFYBZ,'') from tbsysparam2 where CBH = @FParam and CTJ = @TJ            
  if @@error =  0           
  begin          
    if @B = 0      
      commit Tran           
    set @FParam = ''      
    if @type = 0      
      set @FParam = ''       
    if @type = 1      
      set @FParam = substring(convert(varchar,year(getdate())),3,2)      
    if (@type = 2)        
      set @FParam = substring(convert(varchar,year(getdate())),3,2)+ REPLICATE('0',(2-LEN(month(getdate()))))+ convert(varchar,month(getdate())) --一位，或者两位     --DATEPART(d, ICSHFS)    
    if (@type = 3)        
      set @FParam = substring(convert(varchar,year(getdate())),3,2)+ REPLICATE('0',(2-LEN(month(getdate()))))+ convert(varchar,month(getdate())) + REPLICATE('0',(2-LEN(DATEPART(d,getdate())))) + convert(varchar,DATEPART(d,getdate()))     
    if (@type = 4)
      set @FParam = substring(convert(varchar,year(getdate())),1,4)      
    set @I = len(@FParam) + Len(@Param)      
    if  @I<= @length        
      set @TJ = @FParam +@CFYBZ+ REPLICATE('0',(@length -@I))  --日月及零      
    set @Fparam = @param   --CVALUE    
    set @param = @TJ + @Fparam    
    if @param = '00'  
      set @param = ''  
    return 1          
  end          
  else           
  begin     
     if @B = 0        
       rollback Tran          
     set @param = ''       
     return 0          
  end

调用方式：
sql

DECLARE @parm VARCHAR(30)
set @parm = 'WZ002'
EXECUTE GetSysNumber2 '1','00',@parm out
SELECT @parm

存储过程

ALTER PROCEDURE [dbo].[test1]
(@name Varchar(50))   
AS
BEGIN
	SELECT * from YXLIS..TBAnalyse where CXMMC like @name
END

调用方式

EXECUTE test1 '血糖'

java 调用方式

 
@SpringBootTest
class SimpleInterfaceApplicationTests {

    @Resource
    protected JdbcTemplate jdbcTemplate;

    @Test
    public void tes1t1() {
        jdbcTemplate.execute("use YXHIS");
        Object execute_test = jdbcTemplate.execute("EXECUTE GetSysNumber2 ?,?,? ",
                (CallableStatementCallback) cs -> {
                    cs.setObject("diff","1");
                    cs.setObject("TJ","00");
                    cs.setObject("Param","WZ002");
                    cs.registerOutParameter("Param", Types.VARCHAR);
                    cs.execute();
                    Object ksbm = cs.getObject("Param");
                    return ksbm;
                });
        System.out.println(execute_test);
    }

    @Test
    void contextLoads() {
        jdbcTemplate.execute("use YXNET");
        Object execute_test = jdbcTemplate.execute("EXECUTE test1 ?",
                (CallableStatementCallback) cs -> {
                    cs.setObject(1,"血糖");
                    ResultSet resultSet = cs.executeQuery();
                    List list = convertResultSetToList(resultSet);
                    return list;
                });
        System.out.println(execute_test);
    }

    public List convertResultSetToList(ResultSet rs) throws SQLException {
        // 封装到 List
        List<Map<String, Object>> resultList = new ArrayList<>();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        while (rs.next()) {// 转换每行的返回值到Map中
            Map rowMap = new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                rowMap.put(columnName, rs.getString(columnName));
            }
            resultList.add(rowMap);
        }
        rs.close();
        return resultList;
    }

}