procedure proc_GetAgentDaily(p_cursor out sys_refcursor)as
Operator_Id number;
Agent varchar2(50);
ext_code varchar2(100);
HBill NUMBER;
JBill NUMBER;
PBill NUMBER;
AllBill NUMBER;
name varchar2(500);
state NUMBER;
ID NUMBER;
Cursor Cur is Select ID,agent_name,ext_code from tbl_agent where length(ext_code)>=3;
begin
delete from tbl_Storage;
delete from tbl_stor_linshi;
insert into tbl_stor_linshi(AGENT_ID,NO) select AGENT_ID , NO from tbl_event where to_char(DEAL_DATE,'yyyy')=to_char(sysdate,'yyyy') and to_char(DEAL_DATE,'MM')=to_char(sysdate,'MM') and to_char(DEAL_DATE,'DD')=to_char(sysdate,'DD');
commit;
Open Cur;
Fetch Cur Into ID,Agent,ext_code;
While Cur%found loop
select count(NO) into HBill from tbl_stor_linshi where AGENT_ID = ID and NO in (select EVENT_NO FROM tbl_event_detail WHERE DEAL_CATEGORY = 421);
select count(NO) into PBill from tbl_stor_linshi where AGENT_ID = ID and NO in (select EVENT_NO FROM tbl_event_detail WHERE DEAL_CATEGORY = 422 OR DEAL_CATEGORY = 4);
select count(NO) into JBill from tbl_stor_linshi where AGENT_ID = ID and NO in (select EVENT_NO FROM tbl_event_detail WHERE DEAL_CATEGORY = 3);
select count(NO) into AllBill from tbl_stor_linshi where AGENT_ID = ID ;
select case when category is null then 1 else category end into state from tbl_agent_operate where rownum<2 and tbl_agent_operate.agent_id=ID order by tbl_agent_operate.ID desc;
insert into tbl_Storage(Operator_Id,Agent,ext_code,HBill,JBill,PBill,AllBill)values(ID,Agent,ext_code,HBill,JBill,PBill,AllBill);
commit;
Fetch Cur Into ID,Agent,ext_code;
end loop;
Close Cur;
open p_cursor for select * from tbl_Storage;
end proc_GetAgentDaily;红色标记处,老是提示未找到数据 ora-01403但是Select ID,agent_name,ext_code from tbl_agent where length(ext_code)>=3; 中id的值在tbl_agent_operate 中都能找到记录,
Operator_Id number;
Agent varchar2(50);
ext_code varchar2(100);
HBill NUMBER;
JBill NUMBER;
PBill NUMBER;
AllBill NUMBER;
name varchar2(500);
state NUMBER;
ID NUMBER;
Cursor Cur is Select ID,agent_name,ext_code from tbl_agent where length(ext_code)>=3;
begin
delete from tbl_Storage;
delete from tbl_stor_linshi;
insert into tbl_stor_linshi(AGENT_ID,NO) select AGENT_ID , NO from tbl_event where to_char(DEAL_DATE,'yyyy')=to_char(sysdate,'yyyy') and to_char(DEAL_DATE,'MM')=to_char(sysdate,'MM') and to_char(DEAL_DATE,'DD')=to_char(sysdate,'DD');
commit;
Open Cur;
Fetch Cur Into ID,Agent,ext_code;
While Cur%found loop
select count(NO) into HBill from tbl_stor_linshi where AGENT_ID = ID and NO in (select EVENT_NO FROM tbl_event_detail WHERE DEAL_CATEGORY = 421);
select count(NO) into PBill from tbl_stor_linshi where AGENT_ID = ID and NO in (select EVENT_NO FROM tbl_event_detail WHERE DEAL_CATEGORY = 422 OR DEAL_CATEGORY = 4);
select count(NO) into JBill from tbl_stor_linshi where AGENT_ID = ID and NO in (select EVENT_NO FROM tbl_event_detail WHERE DEAL_CATEGORY = 3);
select count(NO) into AllBill from tbl_stor_linshi where AGENT_ID = ID ;
select case when category is null then 1 else category end into state from tbl_agent_operate where rownum<2 and tbl_agent_operate.agent_id=ID order by tbl_agent_operate.ID desc;
insert into tbl_Storage(Operator_Id,Agent,ext_code,HBill,JBill,PBill,AllBill)values(ID,Agent,ext_code,HBill,JBill,PBill,AllBill);
commit;
Fetch Cur Into ID,Agent,ext_code;
end loop;
Close Cur;
open p_cursor for select * from tbl_Storage;
end proc_GetAgentDaily;红色标记处,老是提示未找到数据 ora-01403但是Select ID,agent_name,ext_code from tbl_agent where length(ext_code)>=3; 中id的值在tbl_agent_operate 中都能找到记录,
解决方案 »
- oracle三道题目
- 在 cmd 窗口在怎么删除用户?
- 两表数据合成一张新表问题
- 高手推荐几本数据库方面的书籍
- oracle用plsql来创建表空间
- 在ORACLE 10g中怎么控制指定用户只能访问指定数据表?
- 急,遇到这个情况:ORA-00001:unique constraint (DLJF.SYS_TBL_TJDL) violated
- 有兴趣的可以看看,测一下自己的水平,我公司的Oracle试题一?
- 一用户使用truncate table命令删除了一个重要的业务记录表内容,数据库设置为Noarchievelog,且没有备份。愿出1000元人民币,有效期7天。
- 怎么样从oracle7.3.4升级到ORACLE8i?
- 关于imp的问题,新手求助!
- Oracle 子查询问题.求高手!
select case when category is null then 1 else category end into state from tbl_agent_operate where rownum<2 and tbl_agent_operate.agent_id=ID order by tbl_agent_operate.ID desc;
那你查的到不啊? 去掉into 查下呢
我怀疑是rownum<2 放在最前面了的问题
from (select nvl(category,1) as category
from tbl_agent_operate
where tbl_agent_operate.agent_id=ID
order by tbl_agent_operate.ID desc ) t
where rownum<2;
Operator_Id number;
Agent varchar2(50);
ext_code varchar2(100);
HBill NUMBER;
JBill NUMBER;
PBill NUMBER;
AllBill NUMBER;
name varchar2(500);
state NUMBER;
ID NUMBER;你试试把你的变量名称改为ID_N