CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/
AS
TYPE myrctype IS REF CURSOR; PROCEDURE get (p_id NUMBER, p_rc OUT myrctype);
END pkg_test;
/CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0 THEN
OPEN p_rc FOR
SELECT ID, NAME, sex, address, postcode, birthday
FROM student;
ELSE
sqlstr :=
'select id,name,sex,address,postcode,birthday
from student where id=:w_id';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/
VB中的调用方法(使用OO4O):
Dim myrecordset as OraDynaset
...
' Create the Deptno parameter
OraDatabase.Parameters.Add "DEPTNO", 10, ORAPARM_INPUT OraDatabase.Parameters("DEPTNO").ServerType = ORATYPE_NUMBER
' Create OraDynaset based on "EmpCursor" created in stored procedure.
Set OraDynaset = OraDatabase.CreatePLSQLDynaset("Begin Employee.GetEmpData (:DEPTNO,:EmpCursor); end;", "EmpCursor", 0&)' Now attach the Oradynaset to Data control's recordset.
set myrecordset = OraDynaset
...附:Package Employee create or replace package Employee as
type NUMARRAY is table of NUMBER index by BINARY_INTEGER; --Define EMPNOS array
type VCHAR2ARRAY is table of VARCHAR2(10) index by BINARY_INTEGER; --Define ENAMES array
cursor c1 is select * from emp;
type empCur is ref cursor return c1%rowtype;
procedure GetEmpData(indeptno IN NUMBER,EmpCursor in out empCur );
PROCEDURE GetEmpNamesInArray (ArraySize IN INTEGER, inEmpnos IN NUMARRAY, outEmpNames OUT VCHAR2ARRAY);
PROCEDURE GetEmpName (inEmpno IN NUMBER, outEmpName OUT VARCHAR2);
FUNCTION GetEmpSal (inEmpno IN NUMBER) RETURN NUMBER;end Employee;
/create or replace package body Employee asPROCEDURE GetEmpNamesInArray (ArraySize IN INTEGER, inEmpnos IN NUMARRAY, outEmpNames OUT VCHAR2ARRAY) is
--ArrIndex INTEGER;
BEGIN
FOR I in 1..ArraySize loop
SELECT ENAME into outEmpNames(I) from EMP WHERE EMPNO = inEmpNos(I);
END LOOP;
END;
PROCEDURE GetEmpName (inEmpno IN NUMBER, outEmpName OUT VARCHAR2) is
BEGIN
SELECT ENAME into outEmpName from EMP WHERE EMPNO = inEmpNo;
END;FUNCTION GetEmpSal (inEmpno IN NUMBER)
RETURN NUMBER is
outEmpsal NUMBER(7,2);
BEGIN
SELECT SAL into outEmpsal from EMP WHERE EMPNO = inEmpno;
RETURN (outEmpsal);
END;procedure GetEmpData(indeptno IN NUMBER,EmpCursor in out empcur)
is
begin
open EmpCursor for
select * from emp where deptno= indeptno;
end GetEmpData; end Employee;
/
并且已经建立了一个包,里面有一个函数,
如何通过TQuery执行这个存储过程(或函数),
并且得到返回的记录集?