Oracle stored procedure return result set

Oracle stored procedure returns a result set 1. Return Array Create a package or stored procedure in the Oracle Background Connect Scott / Tiger; Create or Replace Package Ado_CallPkg ASTYPE EID Is Table of Number (4) Index by binary_integer; type ename is table of varcha2 ( 40) INDEX BY BINARY_INTEGER; PROCEDURE getEmpNames (empid OUT eid, empname OUT ename); end ado_callpkg; CREATE OR REPLACE PACKAGE BODY ado_callpkg ASPROCEDURE getEmpNames (empid OUT eid, empname OUT ename) ISCURSOR c1 IS select employee_id, first_name || '', '' || middle_initial || ',' '|| Last_name As Name from Employee; CNT Number Default 1; C1% RowType; Beginopen C1; Loop Fetch C1 INTO C; EmpName (CNT): = C.Name; EmpID (cnt): = c.employee_id; exit when c1% notfound; - process the data cnt: = CNT 1; end loop; close c1; end; end ado_callpkg; 2 front VB program number DIM CN AS New AdoDB.Connection Dim rs As New ADODB.Recordset Dim cmd As New ADODB.Command Dim str As String str = "{call ado_callpkg.getEmpNames ({resultset 100, empid, empname})}" cn.Open "Provider = MSDAORA.1; Password = Tiger; User ID = Scott; Data Source = ORACLE; Persist Security Info = True "With cmd .CommandText = str .ActiveConnection = cn .CommandType = adCmdText End With rs.CursorLocation = adUseClient rs.Open cmd Do While Not rs.EOF Debug.Print rs. Fields (0) .Value & vbtab & rs.fields (1) .Value rs.movenext loop ----------- Summary 1 Oracle's background stored procedure, should pass through a similar array and digital The index variable returns, how many columns have, with how many variables 2 front desk, the called SQL statement should be paid attention to, {call . (, , .... , {ResultSet , , , ...

})} Note the details, (1) to specify a number, indicate the number of rows accepted, if it is too small, and the actual return record is greater than this number, it will be wrong (2) If there is input parameter, it should Create command in the input parameters, where the corresponding use? Alternatively, as {call ado_callpkg.getEmpNames (?, {resultset 100, empid, empname})} consistent with the definition (3) output function, and you store the parameter name to the same, The order is also the same, otherwise it will be wrong.