用DBMS_OUTPUT()
最好用TOAD,PL/SQL DEVELOPER ,调试功能比较好
最好用TOAD,PL/SQL DEVELOPER ,调试功能比较好
解决方案 »
- 大虾们 帮我解决一下这个问题
- ORACLE企业管理器和SQLSERVER企业管理器相似吗
- 求哪位达人帮小弟分析下StatspackReport
- 新手请教:如何知道某条记录已被锁住了?
- 增加了on delete cascade级联删除 ,现在想撤销如何做?
- oracle中,通常应该有几个数据库。是不是我需要一个数据库我就建立一个数据库?
- 一个基本概念问题!关于oracle中的数据库和方案!
- 求助
- 关于select 语句中对某一属性求和(用sum吗?)
- 关于oracle调外部存储过程的问题
- oracle for aix 为什么不能显示中文
- 求解:ORA-01081: cannot start already-running ORACLE - shut it down first
ORA-01401: 插入的值对于列过大
ORA-06512: 在"SCITEL.GEN_MORE_COMM", line 74
ORA-06512: 在line 1怎么知道那个值是多大呢,在存储过程中怎样把这个值输出来显示呢?谢谢!
p_comm_date IN varchar2) --结算日期
AS
--取直销商相关变量
v_Agent_ID varchar2(10);
v_Level_ID integer;
v_Net_Type varchar2(5);
--入库变量
v_FAMgrBak number(8,2):=0.0;
v_FAAdMeAw number(8,2):=0.0;
v_FAKeAw number(8,2):=0.0;
v_temp number(8,2):=0.0;
--遍历表中指定级别、指定结算月的所有直销商IDCURSOR csr_agent_else IS
SELECT agent_id,net_type,to_number(level_id)
FROM dir_agent_info
WHERE level_id='00002' or level_id='00001';
begin
open csr_agent_else; --打开游标
loop
FETCH csr_agent_else INTO v_Agent_ID,v_Net_Type,v_Level_ID;
EXIT WHEN csr_agent_else%NOTFOUND; --计算该直销商所有子、孙(不包括他自身)三个佣金
select nvl(sum(value),0) into v_FAMgrBak from dir_result_detail
where comm_date=p_comm_date
and Factor_id in ('FAFrGCom','FAFrCCom','FANeCCom','FANeGCom','FASrGCom','FASrCCom')
and agent_id in (select agent_id from dir_agent_info
START WITH (agent_id<>v_Agent_ID) and parent_id=v_Agent_ID
CONNECT BY PRIOR agent_id=parent_id);
if (v_Level_ID=1) then
v_FAMgrBak:=v_FAMgrBak*0.05+1200;
-------------------------------------------------------------------
select nvl(count(*),0) into v_FAAdMeAw from dir_agent_info
where first_pass=p_comm_date and to_number(level_id)=3 and parent_id<>v_Agent_ID and agent_id in (select agent_id from dir_agent_info
START WITH (agent_id<>v_Agent_ID) and parent_id=v_Agent_ID
CONNECT BY PRIOR agent_id=parent_id); select nvl(count(*),0) into v_temp from dir_agent_info
where first_pass=p_comm_date and to_number(level_id)=3 and parent_id=v_Agent_ID and agent_id in (select agent_id from dir_agent_info
START WITH (agent_id<>v_Agent_ID) and parent_id=v_Agent_ID
CONNECT BY PRIOR agent_id=parent_id);
v_FAAdMeAw:=v_FAAdMeAw*10+v_temp*40;
-------------------------------------------------------------------
select nvl(count(*),0) into v_FAKeAw from dir_agent_info
where to_number(pass_flag)=1 and to_number(level_id)=3 and agent_id in (select agent_id from dir_agent_info
START WITH (agent_id<>v_Agent_ID) and parent_id=v_Agent_ID
CONNECT BY PRIOR agent_id=parent_id);
if v_FAKeAw>=25 then
v_FAKeAw:=600;
else
v_FAKeAw:=0;
end if;
else
v_FAMgrBak:=v_FAMgrBak*0.05+800;
--------------------------------------------------------------------------------
select nvl(count(*),0) into v_FAAdMeAw from dir_agent_info
where first_pass=p_comm_date and to_number(level_id)=3 and parent_id=v_Agent_ID and agent_id in (select agent_id from dir_agent_info
START WITH (agent_id<>v_Agent_ID) and parent_id=v_Agent_ID
CONNECT BY PRIOR agent_id=parent_id);
v_FAAdMeAw:=v_FAAdMeAw*40;
-----------------------------------------------------------------------------
select nvl(count(*),0) into v_FAKeAw from dir_agent_info
where to_number(pass_flag)=1 and to_number(level_id)=3 and agent_id in (select agent_id from dir_agent_info
START WITH (agent_id<>v_Agent_ID) and parent_id=v_Agent_ID
CONNECT BY PRIOR agent_id=parent_id);
if v_FAKeAw>=10 then
v_FAKeAw:=300;
else
v_FAKeAw:=0;
end if;
end if;
insert into dir_result_detail(agent_id,factor_id,value,comm_date,net_type)
values(v_Agent_ID,'FAMgrBak',to_char(v_FAMgrBak),p_comm_date,v_Net_Type); insert into dir_result_detail(agent_id,factor_id,value,comm_date,net_type)
values(v_Agent_ID,'FAKeAw',to_char(v_FAKeAw),p_comm_date,v_Net_Type); insert into dir_result_detail(agent_id,factor_id,value,comm_date,net_type)
values(v_Agent_ID,'FAAdMeAw',to_char(v_FAAdMeAw),p_comm_date,v_Net_Type);
commit;
END LOOP; close csr_agent_else;
end;
exec ...........