我给你部分asp.net调用的代码: Dim Oraclecon As New OleDbConnection("Provider=MSDAORA.1;Password=tiger;" & _
"User ID=scott;Data Source=MyOracleServer;" & _
"Persist Security Info=True")
Oraclecon.Open() Dim myCMD As New OleDbCommand _
("{call pkg_test.get_data( {resultset 0, cur})}", Oraclecon)
Dim myReader As OleDbDataReader
myReader = myCMD.ExecuteReader() Dim x, count As Integer count = 0
Do While myReader.Read()
For x = 0 To myReader.FieldCount - 1
Console.Write(myReader(x) & " ")
Next
Console.WriteLine()
count += 1
Loop MsgBox(count & " Rows Returned.") myReader.Close()
Oraclecon.Close()
"User ID=scott;Data Source=MyOracleServer;" & _
"Persist Security Info=True")
Oraclecon.Open() Dim myCMD As New OleDbCommand _
("{call pkg_test.get_data( {resultset 0, cur})}", Oraclecon)
Dim myReader As OleDbDataReader
myReader = myCMD.ExecuteReader() Dim x, count As Integer count = 0
Do While myReader.Read()
For x = 0 To myReader.FieldCount - 1
Console.Write(myReader(x) & " ")
Next
Console.WriteLine()
count += 1
Loop MsgBox(count & " Rows Returned.") myReader.Close()
Oraclecon.Close()
解决方案 »
- oracle PL/SQL 如何执行包里的存储过程,存储过程带游标输出参数
- pl/json问题
- 有关oracle的死锁问题,客户端程序用pb编写。
- ORA-12514:TNS:无法解析指定的连接标识符
- Oracle9I在哪里可以下载呢?
- 如何用SQL检索出一个DataBase里面所有的用户
- (急)如何将文本文件导入oracle8i中?
- DROP TABLESPACE "TEST"后,为什么oracle\oradata\bs\TEST.ORA还存在?
- 如何写存储过程
- MSTR可否如下报表
- 走过路过的兄弟一定要帮我up一下,这几天真见鬼啦!!!
- 请问如何将字符串CCC LIKE '%DDDD%'付给表中Char型的字段.散分!
好像要对cursor一条一条的输出……
你搜索一下“记录集”。
c_test pkg_test.rc_test;
begin
getdate(c_test);
for v_test in c_test loop
-- do sth
end loop;
end;
/
不过你试试吧
不行的话,我再找其他方法,最近太忙,懒得测试了to shyuan:
你有没有ASP的代码,或者VB之类的用ADO访问CURSOR的例子,多谢了先!
w_rc pkg_test.rc_test; --定义ref cursor型变量
--定义临时变量,用于显示结果
w_id student.id%type;
w_name student.name%type;
w_sex student.sex%type;
w_address student.address%type;
w_postcode student.postcode%type;
w_birthday student.birthday%type;
begin
pkg_test.get_data(w_rc);
--fetch结果并显示
loop
fetch w_rc into w_id,w_name,w_sex,w_address,w_postcode,w_birthday;
exit when w_rc%notfound;
dbms_output.put_line(w_name);
end loop;
end;
/
CREATE OR REPLACE PACKAGE nb_supFind
AS
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE RecSel( p_StartDate IN CHAR,
p_EndDate IN CHAR,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_col IN VARCHAR2, --选中项目名
p_table IN VARCHAR2, --操作表名
v_ADDV OUT myrctype --作为输出的游标
);
END nb_supFind;CREATE OR REPLACE PROCEDURE NB_SURERFIND (
--参数声明
p_StartDate IN VIEW_NB_TYGL.YY%TYPE,
p_EndDate IN VIEW_NB_TYGL.YY%TYPE,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_item IN NUMBER,
p_table IN VARCHAR2,
)
AS
PROCEDURE RecSel(
p_StartDate IN CHAR,
p_EndDate IN CHAR,
p_Region IN VARCHAR2, --分区内容
p_flag IN NUMBER, --标识是行政区/二级区/三级区
p_col IN VARCHAR2, --选中项目名
p_table IN VARCHAR2, --操作表名
v_ADDV OUT myrctype --作为输出的游标
) IS
strsql VARCHAR2(500);
BEGIN
IF p_flag = 1 then
if p_Region = 'NULL' then
strsql:='SELECT ADDVNM,YY,SUM('||p_col||')
FROM '||p_table||
' WHERE YY <= '||p_EndDate||' AND YY>='|| p_StartDate||'
GROUP BY ADDVNM,YY';
else
strsql:='SELECT ADDVNM,YY,SUM('||p_col||')
FROM '||p_table||
' WHERE YY <= TO_NUMBER('||p_EndDate||') AND YY>=TO_NUMBER('|| p_StartDate||') AND ADDVNM='''||p_Region||
''' GROUP BY ADDVNM,YY';
end if;
DBMS_OUTPUT.PUT_LINE(strsql);
--打开动态游标
OPEN v_ADDV FOR strsql;
END IF;
END RecSel;
END nb_supFind; 3.在SQL PLUS中测试:
set serveroutput on
DECLARE
p_rc NB_SUPFIND.myrctype;
str varchar2(50);
BEGIN
NB_SUPFIND.RecSel('2000','2000','北京',1,'YSQ_SL','VIEW_NB_QSXK',p_rc);
LOOP
FETCH p_rc INTO str;
DBMS_OUTPUT.PUT_LINE(str);
EXIT WHEN p_rc%NOTFOUND;
END LOOP;
END;成功!4.在ASP里的调用
Set Conn=Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
'以下两句含在globo文件中
connstr="Provider=MSDAORA;Password=water;User ID=water;Data Source=oradb_netserver"
Session("connstr")=connstr
conn.Open Session("connstr") Set CMD=Server.CreateObject("ADODB.Command")
CMD.ActiveConnection = conn
CMD.CommandType = adCmdText
CMD.CommandTimeout = 0
CMD.Prepared = true CMD.CommandText = "{call nb_supFind.RecSel(?,?,?,?,?,?)}" 这里只写输入不写输出
CMD.Parameters.Append CMD.CreateParameter("p_StartDate",129,1,4,"2000")'开始年份
CMD.Parameters.Append CMD.CreateParameter("p_EndDate",129,1,4,EndYr) '结束年份
CMD.Parameters.Append CMD.CreateParameter("p_Region",129,1,9,Addv) '行政区名
CMD.Parameters.Append CMD.CreateParameter("p_flag",3,1,4,1) '标识分区
CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,50,Item) '选中项目名
CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,100,Tname) '得到表名 set rs.Source = CMD
on error resume next
set rs=CMD.Execute
if err.number <> 0 then
response.Write err.Description
response.End
end if
rs.Open()