Matlab调用返回游标的存储过程的分析和处理

2、Matlab调用Oracl带游标参数输出的存储过程

笔者也是将工作之中遇到的问题进行了搜集与整理，才完成该文的编写，希望能帮助到有需要的朋友。

2.1、PLSQL中的存储过程

 PROCEDURE p_test_for_matlab(p_i IN VARCHAR2, p_cur OUT type_cur) IS
  BEGIN
    OPEN p_cur FOR
      SELECT * FROM mes_imp_operate_log d WHERE rownum <= p_i;
  END;

下面执行存储过程返回(官网文档给的，处理返回游标时，很明显这样不行，满足不了需求)：

% Execute query and get data from Oracle
curs = runstoredprocedure(conn, 'mes_wytest_pkg.p_test_for_matlab', {5}, {oracle.jdbc.OracleTypes.CURSOR});

论坛Matlab团队的人给出解释是（因为runstoredprocedure该函数中包含了一句关闭游标的动作。因此无法获取和游标相关的数据，意思就是对于返回游标的存储过程，不好使）。 但是，上诉runstoredprocedure 可以执行返回值不是游标的存储过程。)

 

----------------------------------------------推荐使用的---------------------------------------------------------------------------

2.2、官网论坛，给出的写法是（附带runCursorSP.m）：

conn = database('AE','e','e', 'oracle.jdbc.driver.OracleDriver','jdbc:oracle:thin:@10.24.10.3:1521:');
curs = runCursorSP(conn, 'mes_wytest_pkg.p_test_for_matlab', {5}, {oracle.jdbc.OracleTypes.CURSOR});
a = curs{1,1};
close(conn);

返回的结果是：

这就是我们要的东西。

-------------------------------------------------------拓展修改应用----------------------------------------------------------------

2.3、我们修改试试，尝试将存储过程修改成返回Number类型

 

curs = runCursorSP(conn, 'mes_wytest_pkg.p_test_for_matlab', {5}, {oracle.jdbc.OracleTypes.NUMBER});

 

PROCEDURE p_test_for_matlab(p_i IN VARCHAR2, p_cur OUT NUMBER) IS
  BEGIN
    SELECT COUNT(*)
    INTO   p_cur
    FROM   mes_imp_operate_log d
    WHERE  rownum <= p_i;
  END;

测试结果显示：

此处也可以使用 runstoredprocedure。

其 runCursorSP.m 文件是这样定义的(直接复制就好)：

function x = runCursorSP(c,spcall,inarg,typeout)
%RUNCURSORSP Function to run an Oracle stored procedure that returns a
%cursor object. Usage is same as RUNSTOREDPROCEDURE
 
%Set defaults for inarg and typeout
if nargin < 3
  inarg = [];
end
if nargin < 4
  typeout = [];
end
 
%Get JDBC connection Handle
h = c.Handle;
 
%Get Database name
dMetaData = dmd(c);
sDbName = get(dMetaData,'DatabaseProductName');
 
%Build stored procedure call
spcall = [spcall '('];
for i = 1:length(inarg)
  if isnumeric(inarg{i}) || islogical(inarg{i})
    inarg{i} = num2str(inarg{i},17);
  elseif strcmp(sDbName,'MySQL') || strcmp(sDbName,'Microsoft SQL Server')
    inarg{i} = ['''' inarg{i} ''''];
  end
  spcall = [spcall inarg{i} ','];    %#ok, not sure how long spcall will be
end
numout = length(typeout);
for i = 1:numout
  spcall = [spcall '?,']; %#ok, not sure how long spcall will be
end
 
%Allow for procedures with no inputs or outputs
if ~(isempty(inarg) && isempty(typeout))  
  spcall(end) = ')';
else
  spcall(end) = [];
end
spcall = ['{call ' spcall '}'];
 
%Create callable statement
csmt = h.prepareCall(spcall);
 
%Register output parameters
for i = 1:numout
  csmt.registerOutParameter(i,typeout{i});
end
 
%Execute callable statement, method depends on output parameters 
if ~isempty(typeout)  
  csmt.executeUpdate;
else
  try  
    x = csmt.execute;
  catch exception 
    error(message('database:runstoredprocedure:returnedResultSet', exception.message));
  end
  return
end
 
%Return output parameters as native data types
x = cell(numout,1);
for i = 1:numout
  x{i} = csmt.getObject(i);
  
  %Check if Oracle resultset returned
  
  if(isa(x{i}, 'oracle.jdbc.driver.OracleResultSetImpl'))
      data = fetchCursorData(c, x{i});
      x{i} = data;
  end
    
end
 
%Close callable statement
close(csmt)
end
function data = fetchCursorData(conn, rs)
%FETCHCURSORDATA function to fetch data in a resultset object returned when 
% calling a stored procedure that returns a cursor.
% data = fetchCursorData(conn, rs)
% conn : Database Connection Object
% rs : ResultSet object
 
 
%Fetch the data
 
h = conn.Handle;
 
fet = com.mathworks.toolbox.database.fetchTheData(h, rs, '');
 
md = getTheMetaData(fet);
status = validResultSet(fet,md);
 
if(status ~=0 )
    
    %Get preferences
    p = setdbprefs({'NullStringRead';'NullNumberRead';'DataReturnFormat'});
    tmpNullNumberRead = 'NullNumberReadPlaceHolderCFG3358';
    
    %Fetch batchCount rows
    resultSetMetaData = getValidResultSet(fet,md);
    dataFetched = dataFetch(fet,resultSetMetaData,p.NullStringRead,tmpNullNumberRead);
    
    
    %Convert java.util.vector to cell array
    %Get number of rows and columns
    
    rsmd = getMetaData(rs);
    ncols = getColumnCount(rsmd);
   
    nrows =  size(dataFetched) / ncols;
    data = system_dependent(44,dataFetched,nrows)';
    
    
    %Convert NullNumberRead value into numeric value
    i = find(strcmp(data,tmpNullNumberRead));
    data(i) = {str2num(p.NullNumberRead)};
    
else
    error('No valid resultset');
end
 
 
%close resultset
rs.close;
 
end

谢谢阅读！

分享是美德！

分享共进步！