create or replace procedure proc_GetAgentDaily(p_cursor out sys_refcursor)as
Operator_Id number;
Agent varchar2(10);
ext_code varchar2(10);
HBill number;
JBill number;
PBill number;
AllBill number;
cursor Cur is Select ID,name,ext_code from tbl_agent where length(ext_code)>=3;
begin
delete from tbl_Storage;
commit;
delete from tbl_stor_linshi;
commit;
-- select * into #temp from Apply 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');
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 Operator_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 ;
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 Operator_Id,Agent,ext_code;
end loop;
Close Cur;
open p_cursor for select * from tbl_Storage;
end;
end proc_GetAgentDaily;
u老是提示最后一行最后一行有错误
Operator_Id number;
Agent varchar2(10);
ext_code varchar2(10);
HBill number;
JBill number;
PBill number;
AllBill number;
cursor Cur is Select ID,name,ext_code from tbl_agent where length(ext_code)>=3;
begin
delete from tbl_Storage;
commit;
delete from tbl_stor_linshi;
commit;
-- select * into #temp from Apply 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');
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 Operator_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 ;
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 Operator_Id,Agent,ext_code;
end loop;
Close Cur;
open p_cursor for select * from tbl_Storage;
end;
end proc_GetAgentDaily;
u老是提示最后一行最后一行有错误
end;
end proc_GetAgentDaily;
--编写过程块的时候,要注意对齐:
if ...
end if;begin
...
end;loop
end loop;begin
for i in 1..length('asdfghjkl')
loop
if mod(i,2)=0 then
dbms_output.put_line(substr('asdfghjkl',i,1));
end if;
end loop;
end;
/
s
f
h
k