Oracle数据库小白使用记 -- 通过存储过程提取数据

本人是Oracle数据库小白，一直都是用Sql Server的，最近到被安排到一个项目去救火。这个项目使用的Java和Oracle数据库的组合，没办法，一切要从头学起。

其实这个项目本身并不是很复杂，就是一个ETL的问题，就是数据读起来很麻烦，需要很多Static Data和Reference Data，这些数据存在几个不同的系统中，而每个系统又有自己不同的调用方式，有些是Web Service，有些是直接数据库提取。我做的部分需要从另外一个Schema提取数据做参考。那个Schema的头对自己数据库的封装非常严格，不允许其他项目组从他的数据库中直接访问数据表，所有的数据访问都要通过封装好的存储过程来提取。

Oracle和Sql Server的一个很大的不同就是存储过程不能直接返回数据，而是要通过Output Cursor来返回数据。通常在存储过程中我们会定义一个类型为sys_refcursor的Output Parameter，然后在Java代码中，我们可以通过Spring Data的JDBCTemplate，来获取数据：

public List<AccountRequest> getAccountsToProcess() {
	return jdbcTemplate.execute(new CallableStatementCreator() {
		@Override
		public CallableStatement createCallableStatement(Connection connection)
				throws SQLException {
			CallableStatement statement = connection.prepareCall(queryToRun);
			statement.setObject(1, batchSize);
			statement.registerOutParameter(2, OracleTypes.CURSOR);
			statement.registerOutParameter(3, OracleTypes.INTEGER);
			statement.registerOutParameter(4, OracleTypes.VARCHAR);
			return statement;
		}
	}, new CallableStatementCallback<List<AccountRequest>>() {
		@Override
		public List<AccountRequest> doInCallableStatement(CallableStatement callableStatement) throws SQLException, DataAccessException {
			List<AccountRequest> accountRequests = new ArrayList<AccountRequest>();

			try {
				callableStatement.execute();

				ResultSet rs = (ResultSet) callableStatement.getObject(2);
				ResultSetMetaData meta = rs.getMetaData();

				while (rs.next()) {
					AccountRequest request = new AccountRequest();
					Map<String, Object> data = new HashMap<String, Object>();
					int i = 1;
					while (i <= meta.getColumnCount()) {
						data.put(meta.getColumnName(i), JdbcUtils.getResultSetValue(rs, i++));
					}

					// set account request values here
					
					accountRequests.add(request);
				}


			} catch (Exception ex) {
				log.error("Failed to load accounts to process", ex);
			}


			return accountRequests;
		}
	});
}

在这个例子中，我们通过CallableStatement来获得Cursor返回的数据。

当我在数据库中的另外一个存储过程中需要访问这个封装的数据的时候，相对的，就没有在Java中这么容易了。由于在上面的例子中，我们定义的Cursor是弱类型的，在我们读取数据的时候我们就需要首先定义输出数据的类型。假设我们的Cursor中包含的数据是AccountId和AccountName，我们就要先定义：

CREATE OR REPLACE TYPE Account AS Object
(
	AccountId     VARCHAR2(12),
	AccountName   VARCHAR2(50)
);

CREATE OR REPLACE TYPE T_Account AS TABLE OF Account;

然后，我们还需要定义一个读取数据的Function：

CREATE OR REPLACE FUNCTION LoadAccounts
RETURN T_Account IS
	v_map    T_Account   := T_Account();
	v_rc     SYS_REFCURSOR;
	v_cnt    number      := 0;
	AccountId     VARCHAR2(12);
	AccountName   VARCHAR2(50;
BEGIN
	-- load data into cursor v_rc
	
	LOOP
		FETCH v_rc INTO AccountId, AccountName;
		EXIT WHEN v_rc%NOTFOUND;
		v_map.extend;
		v_cnt := v_cnt + 1;
		v_map(v_cnt) := Account(AccountId, AccountName);
	END LOOP;
	CLOSE v_rc;
	RETURN v_map;
END;

通过上面的Function，我们就可以用Select * from Table(LoadAccounts)来访问数据了。

在Oracle中提取存储过程的数据比在Java中要繁琐。听说在11g中，这个过程被简化了，无奈我还在用10g。