CREATE OR REPLACE Procedure proc_GetJgOccupationRatio(
orgParam varchar2,
yearr int,
typee varchar2,--???????? 0????????1??????
rd out sys_refcursor
)
AS orgcode varchar2(50);
lv int ;
temp varchar2(200);
temp1 varchar2(200);
cnt int;
strsql varchar2(3000);
Begin
strsql:='GRANT CREATE ANY TABLE TO test';
execute immediate strsql;
select ORGCODE,LV into orgcode,lv from Kgorgs where OrgName=orgParam;
if orgParam='00' then
orgcode:='00';
lv:=0;
commit;
end if; if(orgParam='??????' or orgParam='??????' or orgParam='??????' or orgParam='??????')
then
lv:=2;
commit;
end if; temp:='create table temp(
unitguid varchar2(16),
isfz varchar2(2),
passdate date,
orgName varchar2(50)
)';
execute immediate temp; temp1:='create table temp1
(
unitguid varchar2(100)
,isfz varchar2(2)
,passdate date
,orgName varchar2(50)
)';
execute immediate temp1;
if(LV=0) then
temp:='insert into temp select b.unitguid,b.isfz,b.passdate,b.[provinceOrgName] from jg_info b where b.isfz=1 and year(b.passdate)='||yearr;
execute immediate temp;
temp1:='insert into temp1 select b.unitguid,b.isfz,b.passdate,b.[provinceOrgName] from jg_info b where b.isfz=1 and year(b.passdate)='||yearr-1;
execute immediate temp1;
commit;
end if;
if(LV=1) then
temp:='insert into temp select b.unitguid,b.isfz,b.passdate,b.city from jg_info b where b.isfz=1 and year(b.passdate)='||yearr;
execute immediate temp;
temp1:='insert into temp1 select b.unitguid,b.isfz,b.passdate,b.city from jg_info b where b.isfz=1 and year(b.passdate)='||yearr-1;
execute immediate temp1;
commit;
end if;
if(LV=2) then
temp:='select b.passdate from jg_info b';
execute immediate temp;
temp:='insert into temp select b.unitguid,b.isfz,b.passdate,b.county from jg_info b '
||' where b.isfz=1 '
||' and to_char(b.passdate,''yyyy'')='||yearr;
execute immediate temp ;
temp1:='insert into temp1 select b.unitguid,b.isfz,b.passdate,b.county from jg_info b '
||' where b.isfz=1 '
||' and to_char(b.passdate,''yyyy'')='||(yearr-1);
execute immediate temp1;
commit;
end if; strsql:='create table tempOrgs as select * from KgOrgs a where a.ParentOrgCode = '||orgcode;
execute immediate strsql;
--??????????????????
select count(*) into cnt from KgOrgs where OrgName='??????????????????' ;
if (cnt>0)
then
strsql:='insert into tempOrgs
select * from KgOrgs a where a.ParentOrgCode = (select OrgCode from tempOrgs where OrgName="??????????????????")';
execute immediate strsql;
strsql:='delete from tempOrgs where OrgName="??????????????????"';
execute immediate strsql;
commit;
end if;
select count(*) into cnt from KgOrgs where OrgName='??????';
if (cnt>0) then
strsql:='insert into tempOrgs
select * from KgOrgs a where a.ParentOrgCode = (select OrgCode from tempOrgs where OrgName="??????")';
execute immediate strsql;
strsql:='delete from tempOrgs where OrgName="??????"';
execute immediate strsql;
commit;
end if;
strsql:='create table resultt as SELECT a.OrgCode,a.OrgName, a.NumberOfPeople, a.MedNum, a.CurrOccupationRatio
, CASE
WHEN (a.CurrOccupationRatio - a.LastOccupationRatio) < 0 THEN ''-''|| CAST((a.LastOccupationRatio - a.CurrOccupationRatio) AS varchar2(100))
WHEN (a.CurrOccupationRatio - a.LastOccupationRatio) = 0 THEN ''0.00''
ELSE ''+''||CAST((a.CurrOccupationRatio - a.LastOccupationRatio) AS varchar2(100))
END AS YearIncrease
, CASE
WHEN (a.CurrOccupationRatio - a.qgOccupationRatio) < 0 THEN ''-''|| CAST((a.qgOccupationRatio - a.CurrOccupationRatio) AS varchar2(100))
WHEN (a.CurrOccupationRatio - a.qgOccupationRatio) = 0 THEN ''0.00''
ELSE ''+''|| CAST((a.CurrOccupationRatio - a.qgOccupationRatio) AS varchar2(100))
END AS QgIncrease
FROM (
SELECT a.OrgCode,a.OrgName, a.NumberOfPeople
, (
SELECT COUNT(1)
FROM temp b
WHERE to_char(a.orgName)=b.orgName
) AS MedNum
, CASE
WHEN nvl(a.numberofpeople,CAST(0 AS number)) = 0 THEN CAST(0 AS number)
ELSE CAST((
SELECT COUNT(1)
FROM temp b
WHERE to_char(a.orgName)=b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS CurrOccupationRatio
, CASE
WHEN nvl(a.numberofpeople, CAST(0 AS number)) = 0 THEN CAST(0 AS number)
ELSE CAST((
SELECT COUNT(1)
FROM temp1 b
WHERE to_char(a.orgName) =b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS LastOccupationRatio, CAST((
SELECT COUNT(1)
FROM jg_info b
WHERE isfz = 1
AND to_char(b.passdate,''yyyy'')='||yearr||'
) / (CAST((
SELECT SUM(numberofpeople)
FROM KgOrgs where LV=1
) AS decimal) / 1000) * 100 AS decimal(18, 2)) AS qgOccupationRatio
FROM tempOrgs a
) a ';
execute immediate strsql; if(typee=1)
then
strsql:='open rd for select OrgName, NumberOfPeople, MedNum,''1:''||cast(CurrOccupationRatio as varchar(100)) CurrOccupationRatio,YearIncrease,QgIncrease from resultt order by OrgCode';
execute immediate strsql;
commit;
-- else
--strsql:='open rd for select OrgName, CurrOccupationRatio from resultt order by OrgCode';
-- execute immediate strsql;
-- commit;
end if; temp:='drop table temp';
execute immediate temp;
temp1:='drop table temp1';
execute immediate temp1;
strsql:='drop table tempOrgs';
execute immediate strsql;
strsql:='drop table resultt';
execute immediate strsql;
END;
orgParam varchar2,
yearr int,
typee varchar2,--???????? 0????????1??????
rd out sys_refcursor
)
AS orgcode varchar2(50);
lv int ;
temp varchar2(200);
temp1 varchar2(200);
cnt int;
strsql varchar2(3000);
Begin
strsql:='GRANT CREATE ANY TABLE TO test';
execute immediate strsql;
select ORGCODE,LV into orgcode,lv from Kgorgs where OrgName=orgParam;
if orgParam='00' then
orgcode:='00';
lv:=0;
commit;
end if; if(orgParam='??????' or orgParam='??????' or orgParam='??????' or orgParam='??????')
then
lv:=2;
commit;
end if; temp:='create table temp(
unitguid varchar2(16),
isfz varchar2(2),
passdate date,
orgName varchar2(50)
)';
execute immediate temp; temp1:='create table temp1
(
unitguid varchar2(100)
,isfz varchar2(2)
,passdate date
,orgName varchar2(50)
)';
execute immediate temp1;
if(LV=0) then
temp:='insert into temp select b.unitguid,b.isfz,b.passdate,b.[provinceOrgName] from jg_info b where b.isfz=1 and year(b.passdate)='||yearr;
execute immediate temp;
temp1:='insert into temp1 select b.unitguid,b.isfz,b.passdate,b.[provinceOrgName] from jg_info b where b.isfz=1 and year(b.passdate)='||yearr-1;
execute immediate temp1;
commit;
end if;
if(LV=1) then
temp:='insert into temp select b.unitguid,b.isfz,b.passdate,b.city from jg_info b where b.isfz=1 and year(b.passdate)='||yearr;
execute immediate temp;
temp1:='insert into temp1 select b.unitguid,b.isfz,b.passdate,b.city from jg_info b where b.isfz=1 and year(b.passdate)='||yearr-1;
execute immediate temp1;
commit;
end if;
if(LV=2) then
temp:='select b.passdate from jg_info b';
execute immediate temp;
temp:='insert into temp select b.unitguid,b.isfz,b.passdate,b.county from jg_info b '
||' where b.isfz=1 '
||' and to_char(b.passdate,''yyyy'')='||yearr;
execute immediate temp ;
temp1:='insert into temp1 select b.unitguid,b.isfz,b.passdate,b.county from jg_info b '
||' where b.isfz=1 '
||' and to_char(b.passdate,''yyyy'')='||(yearr-1);
execute immediate temp1;
commit;
end if; strsql:='create table tempOrgs as select * from KgOrgs a where a.ParentOrgCode = '||orgcode;
execute immediate strsql;
--??????????????????
select count(*) into cnt from KgOrgs where OrgName='??????????????????' ;
if (cnt>0)
then
strsql:='insert into tempOrgs
select * from KgOrgs a where a.ParentOrgCode = (select OrgCode from tempOrgs where OrgName="??????????????????")';
execute immediate strsql;
strsql:='delete from tempOrgs where OrgName="??????????????????"';
execute immediate strsql;
commit;
end if;
select count(*) into cnt from KgOrgs where OrgName='??????';
if (cnt>0) then
strsql:='insert into tempOrgs
select * from KgOrgs a where a.ParentOrgCode = (select OrgCode from tempOrgs where OrgName="??????")';
execute immediate strsql;
strsql:='delete from tempOrgs where OrgName="??????"';
execute immediate strsql;
commit;
end if;
strsql:='create table resultt as SELECT a.OrgCode,a.OrgName, a.NumberOfPeople, a.MedNum, a.CurrOccupationRatio
, CASE
WHEN (a.CurrOccupationRatio - a.LastOccupationRatio) < 0 THEN ''-''|| CAST((a.LastOccupationRatio - a.CurrOccupationRatio) AS varchar2(100))
WHEN (a.CurrOccupationRatio - a.LastOccupationRatio) = 0 THEN ''0.00''
ELSE ''+''||CAST((a.CurrOccupationRatio - a.LastOccupationRatio) AS varchar2(100))
END AS YearIncrease
, CASE
WHEN (a.CurrOccupationRatio - a.qgOccupationRatio) < 0 THEN ''-''|| CAST((a.qgOccupationRatio - a.CurrOccupationRatio) AS varchar2(100))
WHEN (a.CurrOccupationRatio - a.qgOccupationRatio) = 0 THEN ''0.00''
ELSE ''+''|| CAST((a.CurrOccupationRatio - a.qgOccupationRatio) AS varchar2(100))
END AS QgIncrease
FROM (
SELECT a.OrgCode,a.OrgName, a.NumberOfPeople
, (
SELECT COUNT(1)
FROM temp b
WHERE to_char(a.orgName)=b.orgName
) AS MedNum
, CASE
WHEN nvl(a.numberofpeople,CAST(0 AS number)) = 0 THEN CAST(0 AS number)
ELSE CAST((
SELECT COUNT(1)
FROM temp b
WHERE to_char(a.orgName)=b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS CurrOccupationRatio
, CASE
WHEN nvl(a.numberofpeople, CAST(0 AS number)) = 0 THEN CAST(0 AS number)
ELSE CAST((
SELECT COUNT(1)
FROM temp1 b
WHERE to_char(a.orgName) =b.orgName
) / (CAST(a.numberofpeople AS decimal) / 1000) * 100 AS decimal(18, 2))
END AS LastOccupationRatio, CAST((
SELECT COUNT(1)
FROM jg_info b
WHERE isfz = 1
AND to_char(b.passdate,''yyyy'')='||yearr||'
) / (CAST((
SELECT SUM(numberofpeople)
FROM KgOrgs where LV=1
) AS decimal) / 1000) * 100 AS decimal(18, 2)) AS qgOccupationRatio
FROM tempOrgs a
) a ';
execute immediate strsql; if(typee=1)
then
strsql:='open rd for select OrgName, NumberOfPeople, MedNum,''1:''||cast(CurrOccupationRatio as varchar(100)) CurrOccupationRatio,YearIncrease,QgIncrease from resultt order by OrgCode';
execute immediate strsql;
commit;
-- else
--strsql:='open rd for select OrgName, CurrOccupationRatio from resultt order by OrgCode';
-- execute immediate strsql;
-- commit;
end if; temp:='drop table temp';
execute immediate temp;
temp1:='drop table temp1';
execute immediate temp1;
strsql:='drop table tempOrgs';
execute immediate strsql;
strsql:='drop table resultt';
execute immediate strsql;
END;
resultt有值,但游标总是自动关闭