调用有参数的存储过程错误，哪位大佬帮忙看看啊！

Question

调用有参数的存储过程错误，哪位大佬帮忙看看啊！

烤肉老猎人

211

发布于
2018-02-13

更新于
2018-02-13

新手上路，请多包涵

1、调用有参数的存储过程报错，调用无参的能正常执行，错误如下：

org.springframework.orm.jpa.JpaSystemException: Error preparing CallableStatement; nested exception is org.hibernate.exception.GenericJDBCException: Error preparing CallableStatement
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:333) ~[spring-orm-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244) ~[spring-orm-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:491) ~[spring-orm-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.dao.support.ChainedPersistenceExceptionTranslator.translateExceptionIfPossible(ChainedPersistenceExceptionTranslator.java:59) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.dao.support.DataAccessUtils.translateIfNecessary(DataAccessUtils.java:213) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:147) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.data.jpa.repository.support.CrudMethodMetadataPostProcessor$CrudMethodMetadataPopulatingMethodInterceptor.invoke(CrudMethodMetadataPostProcessor.java:133) ~[spring-data-jpa-1.10.3.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:92) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at com.sun.proxy.$Proxy89.userIsExist(Unknown Source) ~[na:na]
    at cn.e3mall.sso.service.impl.RegisterServiceImpl.checkData(RegisterServiceImpl.java:30) ~[classes/:na]
    at cn.e3mall.sso.service.impl.RegisterServiceImpl$$FastClassBySpringCGLIB$$6f4301e5.invoke(<generated>) ~[classes/:na]
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) ~[spring-core-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:720) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:655) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at cn.e3mall.sso.service.impl.RegisterServiceImpl$$EnhancerBySpringCGLIB$$1c9b3b84.checkData(<generated>) ~[classes/:na]
    at com.alibaba.dubbo.common.bytecode.Wrapper0.invokeMethod(Wrapper0.java) ~[na:2.5.3]
    at com.alibaba.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:46) ~[dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:72) ~[dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:53) ~[dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:64) ~[dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:75) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:42) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:78) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:60) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:112) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:38) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:91) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:108) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:84) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:170) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:52) [dubbo-2.5.3.jar:2.5.3]
    at com.alibaba.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:82) [dubbo-2.5.3.jar:2.5.3]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [na:1.8.0_102]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [na:1.8.0_102]
    at java.lang.Thread.run(Thread.java:745) [na:1.8.0_102]
Caused by: org.hibernate.exception.GenericJDBCException: Error preparing CallableStatement
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:109) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:414) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.procedure.internal.ProcedureCallImpl.getOutputs(ProcedureCallImpl.java:363) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.jpa.internal.StoredProcedureQueryImpl.outputs(StoredProcedureQueryImpl.java:234) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.jpa.internal.StoredProcedureQueryImpl.execute(StoredProcedureQueryImpl.java:217) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution$ProcedureExecution.doExecute(JpaQueryExecution.java:304) ~[spring-data-jpa-1.10.3.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.JpaQueryExecution.execute(JpaQueryExecution.java:82) ~[spring-data-jpa-1.10.3.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.doExecute(AbstractJpaQuery.java:114) ~[spring-data-jpa-1.10.3.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.query.AbstractJpaQuery.execute(AbstractJpaQuery.java:104) ~[spring-data-jpa-1.10.3.RELEASE.jar:na]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.doInvoke(RepositoryFactorySupport.java:482) ~[spring-data-commons-1.12.3.RELEASE.jar:na]
    at org.springframework.data.repository.core.support.RepositoryFactorySupport$QueryExecutorMethodInterceptor.invoke(RepositoryFactorySupport.java:460) ~[spring-data-commons-1.12.3.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.data.projection.DefaultMethodInvokingMethodInterceptor.invoke(DefaultMethodInvokingMethodInterceptor.java:61) ~[spring-data-commons-1.12.3.RELEASE.jar:na]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor$1.proceedWithInvocation(TransactionInterceptor.java:99) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:281) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) ~[spring-aop-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:136) ~[spring-tx-4.3.3.RELEASE.jar:4.3.3.RELEASE]
    ... 46 common frames omitted
Caused by: java.sql.SQLException: 试图设置未出现在 SQL 中的参数名: IN_VALIDATE
    at oracle.jdbc.driver.OracleCallableStatement.addNamedPara(OracleCallableStatement.java:6626) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.OracleCallableStatement.setString(OracleCallableStatement.java:5934) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at oracle.jdbc.driver.OracleCallableStatementWrapper.setString(OracleCallableStatementWrapper.java:250) ~[ojdbc6-11.2.0.jar:11.2.0.1.0]
    at com.alibaba.druid.pool.DruidPooledCallableStatement.setString(DruidPooledCallableStatement.java:441) ~[druid-1.0.26.jar:1.0.26]
    at org.hibernate.type.descriptor.sql.VarcharTypeDescriptor$1.doBind(VarcharTypeDescriptor.java:52) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:104) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:369) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.type.AbstractStandardBasicType.nullSafeSet(AbstractStandardBasicType.java:365) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.procedure.internal.AbstractParameterRegistrationImpl.prepare(AbstractParameterRegistrationImpl.java:324) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    at org.hibernate.procedure.internal.ProcedureCallImpl.buildOutputs(ProcedureCallImpl.java:402) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
    ... 63 common frames omitted

2、相关类如下：
Entity：

@Entity
@NamedStoredProcedureQueries({
        @NamedStoredProcedureQuery(name = "P_USER_IS_EXIST", procedureName = "PKG_E3_COMMON.P_USER_IS_EXIST", parameters = {
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "IN_VALIDATE", type = String.class),
                @StoredProcedureParameter(mode = ParameterMode.IN, name = "IN_TYPE", type = Integer.class),
                @StoredProcedureParameter(mode = ParameterMode.OUT, name = "OUT_RESULT", type = String.class)}) })
public class PackageE3 implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = -3620360434435461283L;
    @Id
    Long id;
}

Repository：

public interface ProcedureRepository extends CrudRepository<PackageE3, Long> {
    @Procedure(name = "P_USER_IS_EXIST")
    String userIsExist(@Param("IN_VALIDATE") String param, @Param("IN_TYPE") Integer type);
}

调用的地方：

@Override
    public E3Result checkData(String param, int type) {
        String result = procedureRepository.userIsExist(param, type);
        System.out.println(result);
        int indexOf = result.indexOf("true");

        if (indexOf != -1) {
            return E3Result.ok(false);
        }
        // 如果没有数据返回true
        return E3Result.ok(true);
    }

存储过程：

CREATE OR REPLACE PACKAGE BODY PKG_E3_COMMON IS
  PROCEDURE P_USER_IS_EXIST(IN_VALIDATE IN VARCHAR2, --待验证的数据
                            IN_TYPE     IN INTEGER, --验证数据的类型（1：用户名，2：手机，3：Email）
                            OUT_RESULT  OUT VARCHAR2) AS
    V_COUNT INT;
  BEGIN
    IF IN_TYPE = 1 THEN
      SELECT COUNT(T.ID)
        INTO V_COUNT
        FROM MDS.TB_USER T
       WHERE T.USERNAME = IN_VALIDATE;
      OUT_RESULT := '用户名';
    ELSIF IN_TYPE = 2 THEN
      SELECT COUNT(T.ID)
        INTO V_COUNT
        FROM MDS.TB_USER T
       WHERE T.PHONE = IN_VALIDATE;
      OUT_RESULT := '手机号';
    ELSIF IN_TYPE = 3 THEN
      SELECT COUNT(T.ID)
        INTO V_COUNT
        FROM MDS.TB_USER T
       WHERE T.EMAIL = IN_VALIDATE;
      OUT_RESULT := 'Email';
    END IF;
    IF V_COUNT > 0 THEN
      OUT_RESULT := 'FALSE,' || OUT_RESULT || '已存在';
    ELSE
      OUT_RESULT := 'TRUE,用户不存在，可以注册';
    END IF;
  END P_USER_IS_EXIST;
END PKG_E3_COMMON;

哪位好心的大佬教教我，刚入行的菜鸟一枚

java

阅读 2.8k

撰写回答

你尚未登录，登录后可以

和开发者交流问题的细节
关注并接收问题和回答的更新提醒
参与内容的编辑和改进，让解决方法与时俱进

推荐问题

相似问题

找不到问题？创建新问题

调用有参数的存储过程错误，哪位大佬帮忙看看啊！

你尚未登录，登录后可以

小网站有必要将图片放到阿里云OSS存储吗？

Spring中的两个疑惑?

求java/php大佬帮帮忙？

springboot业务代码能否动态加载和更新？

Java实例变量默认值赋值时机是什么时候？

java连redis-sentinel连不上,接下来如何排查?

请问，低代码中，DSL和DSL2CODE是否有公共语言的实现呢？