存储过程如下:
create or replace package Tools
is
type ResultData is ref cursor;
procedure sp_Page(p_OutCursor out ResultData);
end Tools; create or replace package body Tools
is
procedure sp_Page(p_OutCursor out ResultData)
as
begin
v_sql := 'select * from tablename'
OPEN p_OutCursor FOR v_sql;
end sp_Page;
end Tools;
在asp中调用:
Set RS = Server.CreateObject( ADODB.RecordSet )
Set CMD = Server.CreateObject( ADODB.Command ) '建立对象
SQLstr = {CALL Tools.sp_page({resultset 100,p_OutCursor})}
CMD.ActiveConnection = Con ‘建立CMD对象与Con的联系。con的代码省略
CMD.CommandType = 4
CMD.CommandText = SQLstr ‘指定Command对象的执行字符串。
SET RS=CMD.Execute()
do while not Rs.eof
response.write(rs("字段名"))
Rs.movenext
loop
我知道这样是错的,可不知道要怎么写,请大家帮帮忙。
create or replace package Tools
is
type ResultData is ref cursor;
procedure sp_Page(p_OutCursor out ResultData);
end Tools; create or replace package body Tools
is
procedure sp_Page(p_OutCursor out ResultData)
as
begin
v_sql := 'select * from tablename'
OPEN p_OutCursor FOR v_sql;
end sp_Page;
end Tools;
在asp中调用:
Set RS = Server.CreateObject( ADODB.RecordSet )
Set CMD = Server.CreateObject( ADODB.Command ) '建立对象
SQLstr = {CALL Tools.sp_page({resultset 100,p_OutCursor})}
CMD.ActiveConnection = Con ‘建立CMD对象与Con的联系。con的代码省略
CMD.CommandType = 4
CMD.CommandText = SQLstr ‘指定Command对象的执行字符串。
SET RS=CMD.Execute()
do while not Rs.eof
response.write(rs("字段名"))
Rs.movenext
loop
我知道这样是错的,可不知道要怎么写,请大家帮帮忙。
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>