这是beckhambobo以前写过的一个返回结果集的包,可以参照一下
CREATE OR REPLACE PACKAGE PAC_Mycursor
AS
TYPE QUERY_CURSOR IS REF CURSOR;PROCEDURE My_cur_out(
An_area_id IN number,
An_serv_id IN number,
result1 out QUERY_CURSOR);
END PAC_Mycursor;CREATE OR REPLACE PACKAGE BODY PAC_Mycursor
AS
PROCEDURE My_cur_out(
An_area_id IN number,
An_serv_id IN number,
result1 out QUERY_CURSOR
)
AS
BEGIN
OPEN result1 FOR
SELECT SERV_ID, SERV_SEQ_NBR
from a
where area_id = An_area_id and serv_id > An_serv_id ;
END;END PAC_Mycursor;没有在asp中使用,不好意思
CREATE OR REPLACE PACKAGE PAC_Mycursor
AS
TYPE QUERY_CURSOR IS REF CURSOR;PROCEDURE My_cur_out(
An_area_id IN number,
An_serv_id IN number,
result1 out QUERY_CURSOR);
END PAC_Mycursor;CREATE OR REPLACE PACKAGE BODY PAC_Mycursor
AS
PROCEDURE My_cur_out(
An_area_id IN number,
An_serv_id IN number,
result1 out QUERY_CURSOR
)
AS
BEGIN
OPEN result1 FOR
SELECT SERV_ID, SERV_SEQ_NBR
from a
where area_id = An_area_id and serv_id > An_serv_id ;
END;END PAC_Mycursor;没有在asp中使用,不好意思
我使用存储过程返回了记录集,如下:
create or replace package test_age
as
type mycursor is ref cursor;
procedure get_all(op_cur out mycursor);end test_age;
/create or replace package body test_age as
procedure get_all(op_cur out mycursor) is
begin
open op_cur for select name from mytbl;
end;
end test_age;/那么我在java里面怎么遍历这个返回结果呢?我真的无助了,大家告诉我啊!CallableStatement call = conn.prepareCall("{ call p_test(?) }");
call.registerOutParameter(1, OracleTypes.CURSOR);// 註冊out參數的SQL數據類型
call.execute();
ResultSet rs=(ResultSet)call.getObject(1);// 取得得數据結果集合
while(rs.next())
……
如何在ASP程序中调用(参数的用法),如何在ASP页面呈现记录集数据?
名称 空? 类型
----------------------------------------- -------- ----------------------------
ID NUMBER
XK_SX NUMBER
XK_WSX_DJ NUMBER
XK_WSX_XJ NUMBER
XK_WSX_HJ NUMBER
F_ZS NUMBER
F_WZS_DJ NUMBER
F_WZS_XJ NUMBER
F_WZS_HJ NUMBER
SW_CS_DJ NUMBER
SW_CS_XJ NUMBER
SW_CS_HJ NUMBER
BZ CHAR(100)
YY CHAR(4)
XZQ NUMBER
WRR2 NUMBER
WRR3 NUMBER
WRR2NAME CHAR(50)
WRR3NAME CHAR(50)
ADDVNM CHAR(9)2.在ORACLE中包的定义: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()
调试已通过~!:-)
CREATE OR REPLACE PACKAGE get_bom
as
TYPE bom is Ref cursor;
procedure example(bom_cr OUT BOM);
end get_bom;
/
CREATE OR REPLACE PACKAGE BODY get_bom
AS
procedure example(
bom_cr OUT BOM
) as
begin
open bom_cr for select * from test;
end example;
end get_bom;
/
declare
u_cur get_bom.bom;
rem varchar2(50);
begin
get_bom.example(u_cur);
loop
fetch u_cur into rem;
exit when u_cur %NOTFOUND;
dbms_output.put_line(rem);
end loop;
end;
2.create or replace procedure pv as --对上面的补充,也可以这样写的。
u_cur get_bom.bom;
rem varchar2(50);
begin
get_bom.example(u_cur);
loop
fetch u_cur into rem;
exit when u_cur %NOTFOUND;
dbms_output.put_line(rem);
end loop;
end
在oracle中:
一、包头说明
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE get2 (p_rc OUT myrctype);
END pkg_test;
二、包体部分
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get2(p_rc OUT pkg_test.myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
sqlstr:='select v_cbxmdm from cbgl_cbxmb';
OPEN p_rc FOR sqlstr;
END get2;
end pkg_test;
三、在ASP脚本中调用
set cm1 = Server.CreateObject("ADODB.Command")
set rs = Server.CreateObject("adodb.recordset")
cm1.ActiveConnection = "Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=pacm;Password=pacm2004;Data Source=pacm"
cm1.CommandType = 1
cm1.CommandTimeout = 100
cm1.Prepared = true
cm1.CommandText = "{call pkg_test.get2(?)}"
set rs.Source = cm1
on error resume next
set rs=cm1.Execute
rs.Open()
response.Write rs("v_cbxmdm")
出现的问题....
问题为:
(1)不要“response.Write rs("v_cbxmdm")”这行时,运行不提示错误;
(2)加上“response.Write rs("v_cbxmdm")”时所出现的错误为“在对应所需名称或序数的集合中,未找到项目”
请帮我指点一下,我以前是用SQL SERVER数据库,没用过ORACLE数据库,此问题一直让我头疼,目前正急需。谢谢!!!