ASP调用ORACLE存储过程并返回结果集

来源：asp酷网作者：出处：巧巧读书 2005-11-25 进入讨论组

看过网上好多ASP调用ORACLE的方法，但是没有特好特直接的东西，所以总结各家的东西最后得出的一个自己需要的方法，感觉也比较大众所以特此发布

################################################### 
oracle的存储过程 
################################################### 

--创建包头 
create or replace package pck_as 
as 
type t_product_id is table of number 
index by binary_integer; 
type t_serial_no is table of varchar(300) 
index by binary_integer; 
type t_buy_date is table of date 
index by binary_integer; 

procedure allsfc 
( 
product_id out t_product_id, 
serial_no out t_serial_no, 
buy_date out t_buy_date 
); 

--procedure allsfc1; 
end pck_as; 


--创建包体 
create or replace package body pck_as 
as 
procedure allsfc 
( 
product_id out t_product_id, 
serial_no out t_serial_no, 
buy_date out t_buy_date 
) 
is 
cursor cur_sfc_tbl_product_reg is 
select product_id,serial_no,buy_date from sfc_tbl_product_reg; 

i number default 1; 

begin 
for onrecord in cur_sfc_tbl_product_reg 
loop 
product_id(i) := onrecord.product_id; 
serial_no(i) := onrecord.serial_no; 
buy_date(i) := onrecord.buy_date; 
i := i + 1; 
end loop; 
end; 
end; 




################################################### 
ASP的调用 
################################################### 
<html> 
<head> 
<meta http-equiv="Content-Type" content="text/html; charset=gb2312"> 
<title>无标题文档</title> 
</head> 

<body> 
<% 
dim str_ora 
dim objconn_ora 
str_ora = "Driver={Microsoft ODBC for Oracle};Server=ora192;uid=dwuser;pwd=family.samsung.com.cn" 
set objconn_ora = server.CreateObject("adodb.connection") 
objconn_ora.connectionstring = str_ora 
objconn_ora.CursorLocation = 3 
objconn_ora.open 

dim strsql 
set rs1 = server.CreateObject("ADODB.RecordSet") 
strsql = "{call pck_as.allsfc({resultset 90000, product_id, serial_no, buy_date})}" 
set objcomm1 = server.CreateObject("adodb.command") 
objcomm1.activeconnection = objconn_ora 
objcomm1.commandtype = 1 
objcomm1.commandtext = strsql 
set rs1 = objcomm1.execute 


while not rs1.eof 
response.Write(rs1(0) & "|" & rs1(1) & "|" & rs1(2) & "|<BR>") 
rs1.movenext 
wend 
%> 
</body> 
</html>