ssm调用oracle存储过程

创建存储过程:

 

create or replace procedure PD_SEXPY(agegr in varchar2,minage in number,maxage in number)

is

begin
          update CUST_BASIC_INFO set AgeGroup = agegr where 
    
  case
     when length(card_no) = 18 then
                       ceil((to_char(sysdate, 'yyyyMMdd') -
                              to_char(to_date(substr(card_no, 7, 8), 'yyyy-MM-dd'),
                                       'yyyyMMdd')) / 10000)
                when length(card_no) = 15 then
                         ceil((to_char(sysdate, 'yyyyMMdd') -
                              to_char(to_date('19' || substr(card_no, 7, 6),
                                               'yyyy-MM-dd'),
                                       'yyyyMMdd')) / 10000)
                  when length(card_no) != 18 and length(card_no) != 15 and BIRTHDAY != null
                     then ceil((to_char(sysdate, 'yyyyMMdd') -
                              to_char(BIRTHDAY,
                                       'yyyyMMdd')) / 10000)
                       
                     end 
 
 
  in (select cu1.cuage from (select 
  case
     when length(cu.card_no) = 18 then
                       ceil((to_char(sysdate, 'yyyyMMdd') -
                              to_char(to_date(substr(cu.card_no, 7, 8), 'yyyy-MM-dd'),
                                       'yyyyMMdd')) / 10000)
                when length(cu.card_no) = 15 then
                         ceil((to_char(sysdate, 'yyyyMMdd') -
                              to_char(to_date('19' || substr(cu.card_no, 7, 6),
                                               'yyyy-MM-dd'),
                                       'yyyyMMdd')) / 10000)
                  when length(cu.card_no) != 18 and length(cu.card_no) != 15 and BIRTHDAY != null
                     then ceil((to_char(sysdate, 'yyyyMMdd') -
                              to_char(BIRTHDAY,
                                       'yyyyMMdd')) / 10000)
                       
                     end as cuAge

 from cust_basic_info cu)cu1 where cu1.cuage>=minage and cu1.cuage<=maxage);
         
        commit;
 
end PD_SEXPY;

mapper:

	
	public void getPD_SEXPY(@Param("map") Map<String, Object> map);

mapper xml:

<select id="getPD_SEXPY" statementType="CALLABLE"
		parameterType="map" >
	 	<![CDATA[
			{call PD_SEXPY(#{map.agegroup,mode=IN,  jdbcType=VARCHAR},
			#{map.agestage,mode=IN,  jdbcType=INTEGER},
			#{map.agestageend,mode=IN,  jdbcType=INTEGER})}
		]]>
	</select>

service:

		String agegroup = "1";
		Integer agestage = "18";
		Integer agestageend = "24";
		
			Map<String, Object> map = new HashMap<String, Object>();
			map.put("agegroup", agegroup);
			map.put("agestage", agestage);
			map.put("agestageend", agestageend);
			custBasicInfoMapper.getPD_SEXPY(map);