CHAPTER 8 Legacy databases and custom SQL left outer join BILLING_ADDRESS ba on us.USER_ID = ba.USER_ID where us.RANKING >= IN_RANK; end; drop procedure SELECT_USERS_BY_RANK You embed the DDL for the stored procedure in a element for creation and removal. That way, Hibernate automatically creates and drops the procedure when the database schema is created and updated with the hbm2ddl tool. You could also execute the DDL by hand on your database catalog. Keeping it in your mapping files (in whatever location seems appropriate, such as in MyStoredProcedures.hbm.xml) is a good choice if you re working on a nonlegacy system with no existing stored procedures. We ll come back to other options for the mapping later in this chapter. As before, the stored procedure code in the example is straightforward: a join query against the base tables (primary and secondary tables for the User class) and a restriction by RANKING, an input argument to the procedure. You must observe a few rules for stored procedures mapped in Hibernate. Stored procedures support IN and OUT parameters. If you use stored procedures with Oracle s own JDBC drivers, Hibernate requires that the first parameter of the stored procedure is an OUT; and for stored procedures that are supposed to be used for queries, the query result is supposed to be returned in this parameter. In Oracle 9 or newer, the type of the OUT parameter has to be a SYS_REFCURSOR. In older versions of Oracle, you must define your own reference cursor type first, called REF CURSOR examples can be found in Oracle product documentation. All other major database management systems (and drivers for the Oracle DBMS not from Oracle) are JDBC-compliant, and you can return a result directly in the stored procedure without using an OUT parameter. For example, a similar procedure in Microsoft SQL Server would look as follows: create procedure SELECT_USERS_BY_RANK @IN_RANK int as select us.USER_ID as USER_ID, us.FIRSTNAME as FIRSTNAME, us.LASTNAME as LASTNAME,
From our experience, we can recommend PHP5 Web Hosting services, if you need affordable webhost to host and run your web application.
This entry was posted
on Saturday, March 8th, 2008 at 1:21 am and is filed under PHP5.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.