获取错误CallableStatementCallback;从postgreSQL调用过程时错误的SQL语法

Question

我正在使用Spring-JDBC支持和PostgreSQL并得到错误。我能够从后端运行过程。所以它是编译过程。

CREATE OR REPLACE PACKAGE schemaname.my_pkg_name
IS
PROCEDURE update_email(person_id numeric, OUT email_gratitude_id numeric);
END my_pkg_name;
CREATE OR REPLACE PACKAGE BODY schemaname.my_pkg_name
IS
PROCEDURE update_email(person_id numeric, OUT email_gratitude_id numeric) IS
begin
select email_gratitude_id into email_gratitude_id from schemaname.emp_email_dtls_tbl
where person_id=person_id;
end;
END my_pkg_name
private class EmployeeSP extends StoredProcedure
{ 
    private static final String SPROC_NAME = "schemaname.my_pkg_name.update_email"; 
    public EmployeeSP( DataSource datasource )
    { 
        super( datasource, SPROC_NAME );                    
        declareParameter( new SqlParameter("person_id", Types.INTEGER) );           
        declareParameter( new SqlOutParameter("email_gratitude_id", Types.INTEGER ) ); 
        compile();
    }
    public Object execute(int emp_id)
    { 
        Map<String,Object> results = super.execute(emp_id,null); 
        return results.get("email_gratitude_id"); 
    }
};

EmployeeSP tp = new EmployeeSP(template.getDataSource());
tp.execute(123456);

<>之前=====================================================================21:31:48 . 386 ERROR [com.myproject.dao]。(http—0.0.0.0-8080-6)org.springframework.jdbc. badsqlgramareexception: CallableStatementCallback;错误的SQL语法[{调用schemaname.my_pkg_name.update_email(?,)});postgresql.util. psqlexception: ERROR: function schemaname.my_pkg_name.update_email(integer)不存在org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate (SQLStateSQLExceptionTranslator.java: 98)org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate (AbstractFallbackSQLExceptionTranslator.java: 72)org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate (AbstractFallbackSQLExceptionTranslator.java: 80)org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate (AbstractFallbackSQLExceptionTranslator.java: 80)org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java: 1030)org.springframework.jdbc.core.JdbcTemplate.call (JdbcTemplate.java: 1064)

Answer 1

try 'CREATE FUNCTION somefunc() RETURNS integer AS $$ '即使用'FUNCTION'代替'PACKAGE'。请查阅第39章。PL/pgSQL - SQL过程语言!

还可以查看Oracle的PL/pgSQL语言和PostgreSQL的PL/SQL语言之间的差异