Hibernate 调用存储过程 练习2

public class TestDaoImpl extends HibernateDaoSupport implements TestDao {

public String hibernateCallPro() {
		return (String)this.getJdbcTemplate().execute(new CallableStatementCreator(){
			public CallableStatement createCallableStatement(Connection conn)
					throws SQLException {
				CallableStatement stmt = conn.prepareCall("{ call TEST_XJT(?,?,?)}");
				int i = 1;

				stmt.setInt(i++, 11);
				stmt.setString(i++, "B1");
				stmt.registerOutParameter(i++, Types.VARCHAR);
				return stmt;
			}
			
		}, new CallableStatementCallback(){
			public Object doInCallableStatement(CallableStatement stmt)
					throws SQLException, DataAccessException {
				 stmt.execute();
				 String result = stmt.getString(3);
				return result;
			}
			
		});
	}

public static void main(String[] args) {

		ApplicationContext context = new FileSystemXmlApplicationContext(
				"/WebContent/WEB-INF/applicationContext.xml");
		TestDao test = (TestDao) context.getBean("testDao");// 括号写需要测试的bean id;

		System.out.println(test.hibernateCallPro());
	}


}

 

CREATE OR REPLACE PROCEDURE TEST_XJT(P0 IN NUMBER,
                                     P1 IN VARCHAR2,
                                     P2 OUT VARCHAR2) IS

  STR   VARCHAR2(100);
  P_VAL VARCHAR2(100) := '12';
BEGIN

  SELECT T.C2 INTO STR FROM TEST2 T WHERE T.C2 = P1;
  IF P_VAL IS NOT NULL THEN
    DBMS_OUTPUT.put_line('P_VAL 不为空');
  END IF;

  INSERT INTO TEST2 (C1, C2, C3) VALUES (P0, 'S', SYSDATE);
  COMMIT;
  P2 := 'success';
EXCEPTION
  WHEN no_data_found THEN
    DBMS_OUTPUT.put_line('没有找到数据异常');
    P2 := 'error';
    ROLLBACK;
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('异常');
    P2 := 'error';
    ROLLBACK;
END TEST_XJT;