Oracle数据库的结果集可以使用Cursor来返回。
例如
首先声明
TYPE CurLog IS REF CURSOR;
然后
function spDispLog return CurLog as
vCVLog CurLog;
begin
open vCvLog for
select LogType, LogDate, LogDesc
from Log
return vCvLog;
end;
这样就可以从存储过程返回一个结果集了。我印象里面使用ADO，可以使用recordset来接收数据。但是印象不是很清楚了。
例如
首先声明
TYPE CurLog IS REF CURSOR;
然后
function spDispLog return CurLog as
vCVLog CurLog;
begin
open vCvLog for
select LogType, LogDate, LogDesc
from Log
return vCvLog;
end;
这样就可以从存储过程返回一个结果集了。我印象里面使用ADO，可以使用recordset来接收数据。但是印象不是很清楚了。
as
type cur_test is ref cursor; -- 定義一個cursor的type
end pkg_test;
/
create or replace procedure p_test
(
v_cur out pkg_test.cur_test
)
as
v_sql varchar2(100); --
begin
v_sql := 'select a1,a2 from test';
OPEN v_cur FOR v_sql; --
exception
when others then
DBMS_OUTPUT.PUT_LINE('Error ---------------' || sqlcode || ' : ' || sqlerrm );
end p_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;
/在asp中使用:
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/conn.asp" -->
<%
set cm1 = Server.CreateObject("ADODB.Command")
cm1.ActiveConnection = MM_conn_STRING
cm1.CommandType = 1
cm1.CommandTimeout = 0
cm1.Prepared = true
cm1.CommandText = "{call pkg_test.get(?)}"//(?,{resultset 0, myrctype})
set para = cm1.Parameters
para.Append cm1.CreateParameter("p_id",3,1,,1)
set rs = Server.CreateObject("ADODB.Recordset")set rs=cm1.Execute
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>中山大学附属第五医院</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<LINK
href="images/med.css" type=text/css
rel=stylesheet>
</head>
<body bgcolor="#B2CFE4" text="#000000">
<%
while not rs.eof
response.write rs("name")
rs.movenext()
wend %></body>
</html>
<%
rs.close
cm1.ActiveConnection.Close
set cm1=nothing
%>
open 游标变量 for 'select * from ('||拼成的结果集字符串||') v_table '，返回游标变量