Oracle 儲存過程返回結果集 (轉)

Oracle 儲存過程返回結果集 (轉)[@more@]

1.返回陣列 (作者:/Message_Board/Send.?sendto=enhydraboy" target=_blank>enhydraboy(亂舞的浮塵) )

在後臺建立一個包或者過程
connect tt/tiger;

CREATE OR REPLACE PACKAGE ado_callpkg AS
TYPE eid IS TABLE of NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE ename IS TABLE of VARCHAR2(40) INDEX BY BINARY_INTEGER;
PROCEDURE getEmpNames (empid OUT eid,empname OUT ename);
end ado_callpkg;

CREATE OR REPLACE PACKAGE BODY ado_callpkg  AS
PROCEDURE getEmpNames (empid OUT eid,empname OUT ename) IS
CURSOR c1 IS employee_id,first_name||','||Middle_Initial||','||last_name as name from employee;
cnt NUMBER DEFAULT 1;
c c1%ROWTYPE;
BEGIN
open c1;
L
  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 前臺vb程式

 Dim cn As New ADO.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 "Provr=MSDAORA.1;Pass=tiger;User ID=scott;Data =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
 
  De.Print rs.Fields(0).Value & vbTab & rs.Fields(1).Value
  rs.MoveNext
  Loop

------------

總結
1 oracle的後臺儲存過程，應該透過一個類似陣列並且帶有數字的變數返回，有多少個列，就有對應多少個變數
2 前臺，呼叫的語句寫法要注意，
{call .(,,....,{resultset ,,,...})}
注意的細節，
(1) 要自己指定一個數字，表示接受的行數大小，如果太小，而實際返回的記錄大於這個數字，會出錯
(2) 如果有輸入引數，應該在command中建立輸入引數,對應的地方用?替代，如
{call ado_callpkg.getEmpNames(?,{resultset 100,empid,empname})}
(3) output和你儲存的定義一致，引數名要一樣，次序也一樣，否則也會出錯。