asp是通过什么用户调用这个pkg的?
这个用户是否有权限呢?
这个用户是否有权限呢?
解决方案 »
- [求助]求教一个查询sql
- windows上启动OEM
- oracle的预编译pro*C/C++问题
- 怎样在XP里打开ORACLE的图型界面???
- Oracel 中很多role 不存在,是权限问题么?
- 关于存储过程的问题,急用。
- 能否按照DECODE条件来COUNT???
- ORA-24314: service handle not initialized 数据库服务器启动不了,求救!
- 我的OracleOraHome90ManagementServer为什么老是启动不了?
- 请问如何查询oracle一个表占用多少字节?
- 进入oracle8.1.6控制台使用sysman账号,进入几次以后就不能再进去了!是因为3次不改默认密码就锁死这个用户了?
- 恢复数据时提示 imp-00015 错误,急(在线等候)
有两个表
表一
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