CREATE FUNCTION agenda RETURN SYS_REFCURSOR
AS
my_cursor SYS_REFCURSOR;
BEGIN
OPEN my_cursor FOR
SELECT id,person_name,person_surname,person_address
FROM Person;
RETURN my_cursor;
END;
Invoking it from Hibernate requires mapping the Stored Procedure in the Person class.
<sql-query name="SP_agenda" callable="true">
<return alias="ev" class="Person">
<return-property name="id" column="id"/>
<return-property name="name" column="person_name"/>
<return-property name="surname" column="person_surname"/>
<return-property name="address" column="person_address"/>
</return>
{ ? = call agenda() }
</sql-query>
Then, you can execute the procedure just like a normal Query:
Query query = session.getNamedQuery("SP_agenda");
List results = query.list();
