使用OLE DB和ADO调用返回记录集的Oracle存储过程

ords(p_cursor OUT empcur,  q_cursor OUT empcur,
              indeptno IN NUMBER, p_errorcode OUT NUMBER) IS 
BEGIN  
  p_errorcode := 0;     -- p_errorcode为错误编号
--打开游标变量p_cursor，查询所属部门编号
--为indeptno的所有职工的信息
  OPEN p_cursor FOR   
    SELECT * FROM emp 
WHERE deptno = indeptno  ORDER BY empno; 
--打开游标变量q_cursor，查询所属部门编号
--为indeptno的所有职工的编号信息
    OPEN q_cursor FOR  
SELECT empno  FROM emp  
WHERE deptno = indeptno   ORDER BY empno;
    EXCEPTION                  --例外处理
      WHEN OTHERS THEN 
      p_errorcode:= SQLCODE; 
 END GetEmpRecords; --存储过程GetEmpRecords定义结束
--定义存储函数GetDept
 FUNCTION GetDept(inempno IN NUMBER, 
                  p_errorcode OUT NUMBER)
 RETURN empcur IS  p_cursor empcur;  --返回值
 BEGIN  
p_errorcode := 0; 
--查询职工编号为inempno的职工的所属部门编号
    OPEN p_cursor FOR 
SELECT deptno  FROM emp WHERE empno = inempno; 
    RETURN (p_cursor); 
    EXCEPTION 
      WHEN OTHERS THEN 
      p_errorcode:= SQLCODE;
 END GetDept;  
END Employees; --程序包体定义结束

4      利用ADO调用返回记录集的Oracle存储过程
使用OLE DB和ADO调用返回记录集的Oracle存储过程，在定义ADO对象和设置参数时，需要特定的设置。

4．1 设定数据库连接字符串
在ADO应用程序中，如果要访问返回记录集的Oracle存储过程，需要使用OraOLE DB的数据库连接字符串的指定格式。在连接字符串中，PLSQLRSet属性表明OraOLE DB是否需要从PL/SQL存储过程返回记录集。如果需要返回记录集，PLSQLRSet设为1，否则设置为0，默认为 0。在Command命令执行后，该属性需要设置为0。
4．2 参数的设置
在ADO应用程序中，可以定义调用存储过程的参数。可使用Command对象的CreateParameter 方法创建Parameter 对象，并使用 Append 方法将它们添加到 Parameters 集合，在调用存储过程时，Parameters 集合将参数传递给存储过程。由于OraOLE DB采用定位传递参数的格式，即Command对象在调用存储过程时，调用参数的位置应和存储过程的参数的位置相对应。根据调用的存储过程的参数属性、名称和位置，在ADO中， Append 方法添加参数的次序应和存储过程中参数的顺序一致。
在OLE DB的标准中，没有REF CURSOR类型的预定义数据类型，因此在调用存储过程时，不能创建REF CURSOR类型的参数。在ADO调用返回记录集的存储过程时，OLE DB自动为存储过程中REF CURSOR类型的传出参数返回记录集，该记录集可以赋值给一个Recordset对象。如果PL/SQL存储过程有一个或多个REF CURSOR类型的传出参数，OLE DB将这些参数的记录集集成在一起，并通过第一个REF CURSOR类型的传出参数返回，这些记录集同样可以赋值给一个Recordset对象。
4．3 调用存储过程的格式
调用返回记录集存储过程之前，应将Command对象的 CommandType 属性指定SQL类型以优化查询性能，即CommandType 属性值设为AdCmdText。
使用 Command对象的CommandText 属性定义调用存储过程的命令。当使用Command对象执行Oracle返回记录集存储过程时，须以odbc转义符调用格式来调用存储过程,例如：{call credit_account(？,？)}。在这种格式中，传入和传出参数都用问号“？”来表示。
4．4返回记录集
可使用Command对象的 Execute 方法调用Oracle返回记录集的存储过程，并在适当的时候将结果记录集返回给 Recordset对象。该对象可以存储一个或多个结果记录集。当存储多个记录集时，可以用Recordset对象的NextRecordSet属性获得下一个记录集。
下面给出在ASP中调用返回存储过程或函数的示例程序：
<%@ Language=VBScript %>
<!--#include file="adovbs.inc"-->
<%
'Connection 对象代表了打开的、与数据源的连接。
set Con=server.CreateObject("ADODB.Connection")
set Rst1 =server.CreateObject("ADODB.Recordset")
set Rst2=server.CreateObject("ADODB.Recordset")
set Cmd =server.CreateObject("ADODB.Command")
set Prm1 =server.CreateObject("ADODB.Parameter")
set Prm2  =server.CreateObject("ADODB.Parameter")
Con.Provider = "OraOLEDB.Oracle"  '设置OLE DB提供者
'设置数据库连接字符串，test是数据库网络服务名
Con.ConnectionString = "Data Source=TEST;" & _
"User ID=scott;Password=tiger;PLSQLRSet=1"
'调用返回记录集的存储过程，PLSQLRSet指定为1
Con.Open
'创建Command对象到目标数据源的连接
Cmd.ActiveConnection = Con
cmd.CommandType=adcmdtext
'虽然Employees.GetEmpRecords()有四个参数，
'但只有需要设定两个参数
'因为REF CURSOR参数由OLE DB连接提供者自动设定。
Set Prm1 = Cmd.CreateParameter("Prm1", adSmallInt, adParamInput, , 30)  '创建一个输入参数，对应于indeptno
Cmd.Parameters.Append Prm1   '将参数添加到参数集合中
Set Prm2 = Cmd.CreateParameter("Prm2", adSmallInt, adParamOutput)    '创建一个输出参数，对应于p_errorcode
Cmd.Parameters.Append Prm2
'调用

上一页  [1] [2] [3]  下一页