create or replace procedure p_rpt_renyuanstat
(a_sqlwhere in varchar2,
re_cursor out pkg_vehicle.T_CURSOR)
as
v_select varchar2(4000);
v_length integer;
begin
if ( a_sqlwhere is null) then
v_length := 0;
else
v_length := length(trim(a_sqlwhere));
end if;
if (v_length < 1) THEN open re_cursor for
select
a.c_xianjiuzdw "单位名称",
count(distinct b.c_qianchurs) "迁出人数",
count(distinct c.c_qianrurs) "迁入人数",
count(distinct d.c_id) - count(distinct b.c_qianchurs) "现有人数"
from (select b.c_peopleid,a.c_xianjiuzdw,b.c_danwei as c_danwei from t_congyeryjbxx a,t_yidongjl b
where a.c_id = b.c_peopleid) a,
(select A.C_ID as c_id,B.c_peopleid AS c_qianchurs
from t_congyeryjbxx A,
t_yidongjl B
where A.c_id = B.c_peopleid
and B.c_Caozuorq > trunc(sysdate,'yyyy')
and B.c_yidonglx = '迁出') b,
(select A.C_ID as c_id,B.c_peopleid AS c_qianrurs
from t_congyeryjbxx A,
t_yidongjl B
where A.c_id = B.c_peopleid
and B.c_Caozuorq > trunc(sysdate,'yyyy')
and B.c_yidonglx = '迁入') c ,
t_congyeryjbxx d
where a.c_peopleid = b.c_id(+)
and a.c_peopleid = c.c_id(+)
and a.c_danwei = d.c_xianjiuzdw(+)
group by a.c_xianjiuzdw;
else
v_select := ' select '||
' a.c_xianjiuzdw "单位名称", '||
' count(distinct b.c_qianchurs) "迁出人数", '||
' count(distinct c.c_qianrurs) "迁入人数", '||
' count(distinct d.c_id) - count(distinct b.c_qianchurs) "现有人数" '||
' from '||
' (select b.c_peopleid,a.c_xianjiuzdw,b.c_danwei as c_danwei from t_congyeryjbxx a,t_yidongjl b '||
a_sqlwhere ||
' and a.c_id = b.c_peopleid) a, '||
' (select A.C_ID as c_id,B.c_peopleid AS c_qianchurs '||
' from t_congyeryjbxx A, '||
' t_yidongjl B '||
a_sqlwhere ||
' and A.c_id = B.c_peopleid '||
' and B.c_yidonglx = ''迁出'') b, '||
' (select A.C_ID as c_id,B.c_peopleid AS c_qianrurs '||
' from t_congyeryjbxx A, '||
' t_yidongjl B '||
a_sqlwhere ||
' and A.c_id = B.c_peopleid '||
' and B.c_yidonglx = ''迁入'') c , '||
' t_congyeryjbxx d '||
' where '||
' a.c_peopleid = b.c_id(+) '||
' and a.c_peopleid = c.c_id(+) '||
' and a.c_danwei = d.c_xianjiuzdw(+) '||
' group by a.c_xianjiuzdw ';
open re_cursor for v_select;
end if;
end p_rpt_renyuanstat;
高手说说b.c_id(+)是什么意思啊??????????????????
(a_sqlwhere in varchar2,
re_cursor out pkg_vehicle.T_CURSOR)
as
v_select varchar2(4000);
v_length integer;
begin
if ( a_sqlwhere is null) then
v_length := 0;
else
v_length := length(trim(a_sqlwhere));
end if;
if (v_length < 1) THEN open re_cursor for
select
a.c_xianjiuzdw "单位名称",
count(distinct b.c_qianchurs) "迁出人数",
count(distinct c.c_qianrurs) "迁入人数",
count(distinct d.c_id) - count(distinct b.c_qianchurs) "现有人数"
from (select b.c_peopleid,a.c_xianjiuzdw,b.c_danwei as c_danwei from t_congyeryjbxx a,t_yidongjl b
where a.c_id = b.c_peopleid) a,
(select A.C_ID as c_id,B.c_peopleid AS c_qianchurs
from t_congyeryjbxx A,
t_yidongjl B
where A.c_id = B.c_peopleid
and B.c_Caozuorq > trunc(sysdate,'yyyy')
and B.c_yidonglx = '迁出') b,
(select A.C_ID as c_id,B.c_peopleid AS c_qianrurs
from t_congyeryjbxx A,
t_yidongjl B
where A.c_id = B.c_peopleid
and B.c_Caozuorq > trunc(sysdate,'yyyy')
and B.c_yidonglx = '迁入') c ,
t_congyeryjbxx d
where a.c_peopleid = b.c_id(+)
and a.c_peopleid = c.c_id(+)
and a.c_danwei = d.c_xianjiuzdw(+)
group by a.c_xianjiuzdw;
else
v_select := ' select '||
' a.c_xianjiuzdw "单位名称", '||
' count(distinct b.c_qianchurs) "迁出人数", '||
' count(distinct c.c_qianrurs) "迁入人数", '||
' count(distinct d.c_id) - count(distinct b.c_qianchurs) "现有人数" '||
' from '||
' (select b.c_peopleid,a.c_xianjiuzdw,b.c_danwei as c_danwei from t_congyeryjbxx a,t_yidongjl b '||
a_sqlwhere ||
' and a.c_id = b.c_peopleid) a, '||
' (select A.C_ID as c_id,B.c_peopleid AS c_qianchurs '||
' from t_congyeryjbxx A, '||
' t_yidongjl B '||
a_sqlwhere ||
' and A.c_id = B.c_peopleid '||
' and B.c_yidonglx = ''迁出'') b, '||
' (select A.C_ID as c_id,B.c_peopleid AS c_qianrurs '||
' from t_congyeryjbxx A, '||
' t_yidongjl B '||
a_sqlwhere ||
' and A.c_id = B.c_peopleid '||
' and B.c_yidonglx = ''迁入'') c , '||
' t_congyeryjbxx d '||
' where '||
' a.c_peopleid = b.c_id(+) '||
' and a.c_peopleid = c.c_id(+) '||
' and a.c_danwei = d.c_xianjiuzdw(+) '||
' group by a.c_xianjiuzdw ';
open re_cursor for v_select;
end if;
end p_rpt_renyuanstat;
高手说说b.c_id(+)是什么意思啊??????????????????
----------
101
102
103
104
105SQL> select * from a2;BBB CCC
---------- --------------------
101
102
105SQL> select * from a1,a2 where a1.bbb(+)=a2.bbb;BBB BBB CCC
---------- ---------- --------------------
101 101
102 102
105 105SQL> select * from a1,a2 where a1.bbb=a2.bbb(+);BBB BBB CCC
---------- ---------- --------------------
101 101
102 102
103
104
105 105SQL>