CREATE OR REPLACE PACKAGE pkg_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; /<%@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(?)}" set para = cm1.Parameters para.Append cm1.CreateParameter("p_id",3,1,,1) --最后一位是数字型,为1值.3代表number,1代表adparaminput,空的位置说明没范围
set rs = Server.CreateObject("ADODB.Recordset")set rs=cm1.Execute %>
可以参考如下: http://expert.csdn.net/Expert/topic/1180/1180654.xml?temp=.4250147以下是在asp参数对照表 var adEmpty = 0; var adTinyInt = 16; var adSmallInt = 2; var adInteger = 3; var adBigInt = 20; var adUnsignedTinyInt = 17; var adUnsignedSmallInt = 18; var adUnsignedInt = 19; var adUnsignedBigInt = 21; var adSingle = 4; var adDouble = 5; var adCurrency = 6; var adDecimal = 14; var adNumeric = 131; var adBoolean = 11; var adError = 10; var adUserDefined = 132; var adVariant = 12; var adIDispatch = 9; var adIUnknown = 13; var adGUID = 72; var adDate = 7; var adDBDate = 133; var adDBTime = 134; var adDBTimeStamp = 135; var adBSTR = 8; var adChar = 129; var adVarChar = 200; var adLongVarChar = 201; var adWChar = 130; var adVarWChar = 202; var adLongVarWChar = 203; var adBinary = 128; var adVarBinary = 204; var adLongVarBinary = 205; var adChapter = 136; var adFileTime = 64; var adDBFileTime = 137; var adPropVariant = 138; var adVarNumeric = 139;至于call pkg_test.get(?)}" 是我自已不断测试出来的结果,初时也是按照标准做法,但总是不成功,后来才发现之样才成功。而输出的类型是所定义的类型,不能直接具体到变量名称.还要提出一点,我用的是odbc驱动,是否与连接字符串有关,这方面我也会继续测试。 提供不同的连接字符串: 1、Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=timecard;Data Source=bosweb;Extended Properties=""2、Provider=MSDAORA;Password=timecard;User ID=timecard;Data Source=bosweb
我用的也是ODBC驱动: Set Conn=Server.CreateObject("ADODB.Connection") Set Rs = Server.CreateObject("ADODB.Recordset") conn.Open Session("connstr")
续传参问题: 包中定义: 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';在ASP里前面的参数都过去了,到下面这两句死活过不去:CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,,Item) CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,,Tname) p_col是要选的列名,p_table表名 在VIEW_NB_QSXK中没有它们的定义,是不是就要近过程里的定义传了呢?视图中预选列为NUMBER(10,2)型,我在传p_col时要传什么类型,什么类型都试了,就是过不去!:(
包头中:
v_ADDV OUT myrctype
包体中:
v_Result OUT myrctype 把它们改为统一名字另外:
CMD.CommandText = "{call nb_supFind.RecSel(?,?,?,?,?,?,?,{resultset 100,v_ADDV})}"
v_ADDV 要改为myrctype还有
rs要声明为对象才可用
set rs = Server.CreateObject("ADODB.Recordset")以下一大堆都有错:
CMD.Parameters.Append CMD.CreateParameter(p_StartDate,adVarChar,adParamInput,4,StartYr)'开始年份
CMD.Parameters.Append CMD.CreateParameter(p_EndDate,adVarChar,adParamInput,4,EndYr) '结束年份
CMD.Parameters.Append CMD.CreateParameter(p_Region,adVarChar,adParamInput,20,Addv) '行政区名
CMD.Parameters.Append CMD.CreateParameter(p_flag,adInteger,adParamInput,,1) '标识分区
CMD.Parameters.Append CMD.CreateParameter(p_col,adVarChar,adParamInput,100,Item) '选中项目名
CMD.Parameters.Append CMD.CreateParameter(p_table,adVarChar,adParamInput,50,Tname) '得到表名
command对象增加参数的用法有误,是这样的最后一位是值.
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;
/<%@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(?)}"
set para = cm1.Parameters
para.Append cm1.CreateParameter("p_id",3,1,,1) --最后一位是数字型,为1值.3代表number,1代表adparaminput,空的位置说明没范围
set rs = Server.CreateObject("ADODB.Recordset")set rs=cm1.Execute
%>
特感激你能那么仔细读我写的天书,还有点疑问,麻烦再看一下:)Q1:以下例子网上找的:SQLstr = {CALL forum_retrieve.getforums({resultset 10,forumname,thecount})} ‘建立存储过程调用字符串。其中forum_retrieve. ‘getforums说明是调用forum_retrieve包中的getforums ‘存储过程,forumname、thecount是存储过程的输出变量,在这里forumname、thecount是包里定义的输出变量,要是我把v_ADDV 要改为myrctype
那不成了输出类型而不是变量了吗?
你的例子里cm1.CommandText = "{call pkg_test.get(?)}" 这样也能搞定吗?不用定义输出变量吗?Q2:set para = cm1.Parameters
para.Append cm1.CreateParameter("p_id",3,1,,1)
如果我的输入参数是变量该怎么写?谢谢!:)
http://expert.csdn.net/Expert/topic/1180/1180654.xml?temp=.4250147以下是在asp参数对照表
var adEmpty = 0;
var adTinyInt = 16;
var adSmallInt = 2;
var adInteger = 3;
var adBigInt = 20;
var adUnsignedTinyInt = 17;
var adUnsignedSmallInt = 18;
var adUnsignedInt = 19;
var adUnsignedBigInt = 21;
var adSingle = 4;
var adDouble = 5;
var adCurrency = 6;
var adDecimal = 14;
var adNumeric = 131;
var adBoolean = 11;
var adError = 10;
var adUserDefined = 132;
var adVariant = 12;
var adIDispatch = 9;
var adIUnknown = 13;
var adGUID = 72;
var adDate = 7;
var adDBDate = 133;
var adDBTime = 134;
var adDBTimeStamp = 135;
var adBSTR = 8;
var adChar = 129;
var adVarChar = 200;
var adLongVarChar = 201;
var adWChar = 130;
var adVarWChar = 202;
var adLongVarWChar = 203;
var adBinary = 128;
var adVarBinary = 204;
var adLongVarBinary = 205;
var adChapter = 136;
var adFileTime = 64;
var adDBFileTime = 137;
var adPropVariant = 138;
var adVarNumeric = 139;至于call pkg_test.get(?)}" 是我自已不断测试出来的结果,初时也是按照标准做法,但总是不成功,后来才发现之样才成功。而输出的类型是所定义的类型,不能直接具体到变量名称.还要提出一点,我用的是odbc驱动,是否与连接字符串有关,这方面我也会继续测试。
提供不同的连接字符串:
1、Provider=OraOLEDB.Oracle.1;Persist Security Info=False;User ID=timecard;Data Source=bosweb;Extended Properties=""2、Provider=MSDAORA;Password=timecard;User ID=timecard;Data Source=bosweb
Set Conn=Server.CreateObject("ADODB.Connection")
Set Rs = Server.CreateObject("ADODB.Recordset")
conn.Open Session("connstr")
Set CMD=Server.CreateObject("ADODB.Command")
CMD.ActiveConnection = conn 'CMD.CommandText = "{call nb_supFind.RecSel(?,?,?,?,?,?,?,{resultset 100,myrctype})}"
CMD.CommandText = "{call nb_supFind.RecSel(?)}"
(两名全试,还是不行)
'输入变量赋值
'CMD.Parameters.Append CMD.CreateParameter("p_StartDate",200,1,,2000) '开始年份
'CMD.Parameters.Append CMD.CreateParameter("p_EndDate",200,1,,EndYr) '结束年份
'CMD.Parameters.Append CMD.CreateParameter("p_Region",200,1,,Addv) '行政区名
CMD.Parameters.Append CMD.CreateParameter("p_flag",3,1,,1) '标识分区
'CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,,Item) '选中项目名
'CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,,Tname) '得到表名
字符型的变参暂时全部注掉,就剩下第四条应该没错了吧? set rs.Source = CMD
'set rs=CMD.Execute
rs.Open() ???晕,这句还是错:~(参看beckhambobo兄指点的若干文档,修改结果如上,还是不对,为什么~~~~~?(女人不能写程序,不能来CSDN呀!男女平等~~~:))
CMD.CommandText = "{call nb_supFind.RecSel(?,?,?,?,?)}"//过程中有五个参数,要对应.'CMD.Parameters.Append CMD.CreateParameter("p_EndDate",200,1,,EndYr)//EndYr在那得出?request("endyr")
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;
结果如下
北京
北京现在在ASP里:
StartYr = request("StartYr") 已经测试,绝对有值
??CMD.Parameters.Append CMD.CreateParameter("p_StartDate",200,1,,StartYr)'开始年份
这句报错
ADODB.Parameters (0x800A0E7C)
(我想我传的参数肯定有问题,要是变量这样写还不行吗?)
注掉之后
set rs=CMD.Execute 又报错:((千思百想不得其解,等待中)
2、再次要看VIEW_NB_QSXK的数据类型,desc VIEW_NB_QSXK;
3、最后,command对象增加参数类型不是以存储过程参数的类型,以表结格的定义类型所参照
例如,p_Region varchar2(20);那么应表示以下
CMD.Parameters.Append CMD.CreateParameter("p_Region",200,1,20,2000)
包中定义:
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';在ASP里前面的参数都过去了,到下面这两句死活过不去:CMD.Parameters.Append CMD.CreateParameter("p_col",200,1,,Item) CMD.Parameters.Append CMD.CreateParameter("p_table",200,1,,Tname) p_col是要选的列名,p_table表名 在VIEW_NB_QSXK中没有它们的定义,是不是就要近过程里的定义传了呢?视图中预选列为NUMBER(10,2)型,我在传p_col时要传什么类型,什么类型都试了,就是过不去!:(
至于p_table定义它为字符类型,给它足够长度,或者为空,看情况如何。给我一个回复。
var adNumeric = 131;
CMD.Parameters.Append CMD.CreateParameter("p_col",131,1,10,Item) 不行
var adSingle = 4;
CMD.Parameters.Append CMD.CreateParameter("p_col",4,1,10,Item) 不行
var adDouble = 5;
CMD.Parameters.Append CMD.CreateParameter("p_col",5,1,10,Item) 不行
var adVarNumeric = 139;
CMD.Parameters.Append CMD.CreateParameter("p_col",139,1,10,Item) 不行 beckhambobo兄你说我还能用哪个类型呢?数据库里它所对应的列都是number(10,2)的,是不是我传参的时候长度给的不对?省略肯定不行,可写什么呢?
,所以和p_table相同设置,应该可以。设置成功后给一个邮件,要备份数据
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)快服了~ 什么邮件?