sql – Spring Data Jpa调用存储过程

[weixin_38066328]

我正在尝试创建一个调用存储过程来执行非常简单的插入和选择的方法, 我在Oracle数据库上创建了一个存储过程,如下所示： CREATE OR REPLACE PROCEDURE schema.insertNewTable(p_test IN VARCHAR2, p_out OUT VARCHAR2) IS BEGIN INSERT INTO NEWTABLE("TEST") VALUES(p_test); SELECT '1' INTO p_out FROM DUAL; COMMIT; END; 然后在一个实体上： @Procedure(procedureName = "insertNewTable", outputParameterName="p_out") String insertNewTable(@Param("p_test") String p_test); 但每次我称这种方法我得到这个： 2018-06-23 20:04:43,047|ORA-06550: row 1, colonna 7: PLS-00306: wrong number or types of arguments in call to 'INSERTNEWTABLE' ORA-06550: riga 1, column 7: PL/SQL: Statement ignored 2018-06-23 20:04:43,100|org.springframework.dao.InvalidDataAccessResourceUsageException: Error calling CallableStatement.getMoreResults; SQL [insertNewTable]; nested exception is org.hibernate.exception.SQLGrammarException: Error calling CallableStatement.getMoreResults at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:261) at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:244) at org.springframework.orm.jpa.AbstractEntityManagerFactoryBean.translateExceptionIfPossible(AbstractEntityManagerFactoryBean.java:503) 经过几次尝试(和例外),我修改了实体和调用以避免“OUT”类型,这样,我没有错误： CREATE OR REPLACE PROCEDURE schema.insertNewTable(p_test IN VARCHAR2) IS BEGIN INSERT INTO NEWTABLE("TEST") VALUES(p_test); COMMIT; END; 然后在一个实体上： @Procedure(procedureName = "insertNewTable") void insertNewTable(@Param("p_test") String p_test); 我如何解决此问题以获得返回值？

[weixin_38088019]

你可以使用NamedStoredProcedureQuery @NamedStoredProcedureQuery( name = "insertNewTable", procedureName = "insertNewTable", parameters = { @StoredProcedureParameter(mode = ParameterMode.IN, type = String.class, name = "p_test"), @StoredProcedureParameter(mode = ParameterMode.OUT, type = String.class, name = "p_out"), ) } ) 方法调用： StoredProcedureQuery query = this.em.createNamedStoredProcedureQuery("insertNewTable"); query.setParameter("p_test", ""TEST""); query.execute(); String out = query.getOutputParameterValue("p_out");