CURSOR c1 IS SELECT wbid,wbname FROM managerinfo WHERE theflag = 'WX' or wbname = theflag ORDER by wbid;
CURSOR c1 IS SELECT wbid,wbname FROM managerinfo WHERE theflag = 'WX' or wbname like theflag || '%' ORDER by wbid;
CURSOR c1 IS SELECT wbid,wbname FROM managerinfo WHERE theflag = 'WX' or wbname like theflag || '%' ORDER by wbid;
1. CURSOR c1 ISif ... then --在此判断 ?入参数是否等于“wx”SELECT wbid,wbname FROM managerinfo ORDER by wbid; elseSELECT wbid,wbname --在此判断 wbname 的前?个字符等于?入的参数 FROM managerinfo ORDER by wbid; end if=======================>CURSOR c1( flag IN VARCHAR2 ) is SELECT wbid,wbname FROM managerinfo where substr(wbid,1,2) = flag ORDER by wbid; 2. FOR c IN c1 LOOP ======> FOR c IN c1(theflag ) LOOP OK?????????????????
CURSOR c1 IS if ... then --在此判断 输入参数是否等于“wx” SELECT wbid,wbname FROM managerinfo ORDER by wbid; else SELECT wbid,wbname --在此判断 wbname 的前两个字符等于输入的参数 FROM managerinfo ORDER by wbid; end if 不能这样写,要不用: CURSOR c1 IS SELECT wbid,wbname FROM managerinfo WHERE theflag = 'WX' or wbname like theflag || '%' ORDER by wbid; 要不就用动态游标。
谢谢给位,按照各位的说法,代码修改如下 各位看看,还有没有错。还有,sqlplus 下怎么测试, CREATE OR REPLACE PACKAGE guanli_d1 ASTYPE twbid IS TABLE of managerinfo.wbid %TYPE INDEX BY BINARY_INTEGER; TYPE twbname IS TABLE of managerinfo.wbname %TYPE INDEX BY BINARY_INTEGER; TYPE tcount_1 IS TABLE of int INDEX BY BINARY_INTEGER; TYPE tcount_2 IS TABLE of int INDEX BY BINARY_INTEGER;PROCEDURE get_userinfo ( theflag in varchar2, thewbid OUT twbid, thewbname OUT twbname, theCount_1 OUT tcount_1, theCount_2 OUT tcount_2 ); end guanli_d1;CREATE OR REPLACE PACKAGE BODY guanli_d1 AS PROCEDURE get_userinfo ( theflag in varchar2, thewbid OUT twbid, thewbname OUT twbname, theCount_1 OUT tcount_1, theCount_2 OUT tcount_2 ) AS CURSOR c1 IS SELECT wbid,wbname FROM managerinfo where theflag='wx' or wbid like theflag || '%' ORDER by wbid; counter NUMBER DEFAULT 1; BEGIN FOR c IN c1 LOOP thewbid(counter):=c.wbid; thewbname(counter):=c.wbname; SELECT COUNT(*) into thecount_1(counter) FROM userinfo WHERE wb_id=thewbid(counter) and biaozhi='T'; counter :=counter+1; END LOOP; END get_userinfo; END guanli_d1;
有两个表
表一
wbid wbname
wx001 aaaaa
xs001 bbbbb
xs002 ccccc
xs003 ddddd
zs001 eeeee
表二
wbid startdate
wx001 2002-7-11
wx001 2002-7-11
wx001 2002-7-11
xs001 2002-7-11
xs001 2002-7-11
xs002 2002-7-11
zs001 2002-7-11我写的过程实现从表一中取得所有wbid列表
然后在表二中按照从表一中取得的wbid统计出记录条数
结果是wbid wbname count
wx001 aaaaaa 3
xs001 bbbbbb 2
xs002 cccccc 1
xs003 dddddd 0
zs001 eeeeee 1现在要加入一个输入参数
当参数=“wx” 时结果与上面一样
当参数=“xs” 时结果为xs001 bbbbbb 2
xs002 cccccc 1
xs003 dddddd 0不知大家能看明白吗?
SELECT wbid,wbname
FROM managerinfo
WHERE theflag = 'WX'
or wbname = theflag
ORDER by wbid;
SELECT wbid,wbname
FROM managerinfo
WHERE theflag = 'WX'
or wbname like theflag || '%'
ORDER by wbid;
SELECT wbid,wbname
FROM managerinfo
WHERE theflag = 'WX'
or wbname like theflag || '%'
ORDER by wbid;
CURSOR c1 ISif ... then --在此判断 ?入参数是否等于“wx”SELECT wbid,wbname
FROM managerinfo
ORDER by wbid; elseSELECT wbid,wbname --在此判断 wbname 的前?个字符等于?入的参数
FROM managerinfo
ORDER by wbid; end if=======================>CURSOR c1( flag IN VARCHAR2 ) is
SELECT wbid,wbname
FROM managerinfo
where substr(wbid,1,2) = flag
ORDER by wbid; 2.
FOR c IN c1 LOOP
======>
FOR c IN c1(theflag ) LOOP
OK?????????????????
if ... then --在此判断 输入参数是否等于“wx”
SELECT wbid,wbname
FROM managerinfo
ORDER by wbid;
else
SELECT wbid,wbname --在此判断 wbname 的前两个字符等于输入的参数
FROM managerinfo
ORDER by wbid;
end if
不能这样写,要不用:
CURSOR c1 IS
SELECT wbid,wbname
FROM managerinfo
WHERE theflag = 'WX'
or wbname like theflag || '%'
ORDER by wbid;
要不就用动态游标。
各位看看,还有没有错。还有,sqlplus 下怎么测试,
CREATE OR REPLACE PACKAGE guanli_d1
ASTYPE twbid IS TABLE of managerinfo.wbid %TYPE INDEX BY BINARY_INTEGER;
TYPE twbname IS TABLE of managerinfo.wbname %TYPE INDEX BY BINARY_INTEGER;
TYPE tcount_1 IS TABLE of int INDEX BY BINARY_INTEGER;
TYPE tcount_2 IS TABLE of int INDEX BY BINARY_INTEGER;PROCEDURE get_userinfo
(
theflag in varchar2,
thewbid OUT twbid,
thewbname OUT twbname,
theCount_1 OUT tcount_1,
theCount_2 OUT tcount_2
);
end guanli_d1;CREATE OR REPLACE PACKAGE BODY guanli_d1
AS
PROCEDURE get_userinfo
(
theflag in varchar2,
thewbid OUT twbid,
thewbname OUT twbname,
theCount_1 OUT tcount_1,
theCount_2 OUT tcount_2
)
AS
CURSOR c1 IS
SELECT wbid,wbname
FROM managerinfo
where theflag='wx'
or wbid like theflag || '%'
ORDER by wbid;
counter NUMBER DEFAULT 1;
BEGIN
FOR c IN c1 LOOP
thewbid(counter):=c.wbid;
thewbname(counter):=c.wbname;
SELECT COUNT(*)
into thecount_1(counter)
FROM userinfo
WHERE wb_id=thewbid(counter)
and biaozhi='T';
counter :=counter+1;
END LOOP;
END get_userinfo;
END guanli_d1;
直接把上面的代码粘贴到pl*sql里面
最后加/就可以了
用show error 察看详细错误信息
'存储过程使用的常量
adParamInput = &H0001
adParamOutput = &H0002
adParamInputOutput = &H0003
adTinyInt = 16
adSmallInt = 2
adInteger = 3
adSingle = 4
adDecimal = 14
adNumeric = 131
adBoolean = 11
adChar = 129
adVarChar = 200
adCmdText = &H0001
adCmdTable = &H0002
adCmdStoredProc = &H0004 aProject=rtrim(Request.Form("project"))
atablename=rtrim(Request.Form("tablename"))set conn=server.CreateObject("adodb.connection")
conn.Open application("DSN")
set CmdSP=server.CreateObject("adodb.command")CmdSP.ActiveConnection=conn
CmdSP.CommandType =AdCmdStoredProcCmdSP.CommandText="sp_output_reportname"
CmdSP.Parameters.Append CmdSP.CreateParameter(":project",adVarChar ,adParamInput,10,aproject)
CmdSP.Parameters.Append CmdSP.CreateParameter(":tablename",adVarChar ,adParamInput,50,atablename)
CmdSP.Parameters.Append CmdSP.CreateParameter(":report_name",adVarChar ,adParamOutput,100)CmdSP.Execute
report=CmdSP(":report_name")
Response.Write report
Set CmdSp=nothing