The 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);

The Begin
STRSQL:='GRANT CREATE ANY TABLE TO test'.
The execute immediate STRSQL;
The 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)
) ';
The execute immediate temp;

Temp1:='create table temp1
(
Unitguid varchar2 (100)
, isfz varchar2 (2)
And passdate date
, orgName varchar2 (50)
) ';
The execute immediate temp1;


If (LV=0) then
Temp:="insert into temp select b.u nitguid, b.i SFZ, p. assdate, b. [provinceOrgName] from jg_info b where b.i SFZ=1 and year (p. assdate)='| | yearr;
The execute immediate temp;
Temp1:="insert into temp1 select b.u nitguid, b.i SFZ, p. assdate, b. [provinceOrgName] from jg_info b where b.i SFZ=1 and year (p. assdate)='| | yearr - 1;
The execute immediate temp1;
commit;
end if;
If (LV=1) then
Temp:="insert into temp select b.u nitguid, b.i SFZ, p. assdate, biggest ity from jg_info b where b.i SFZ=1 and year (p. assdate)='| | yearr;
The execute immediate temp;
Temp1:="insert into temp1 select b.u nitguid, b.i SFZ, p. assdate, biggest ity from jg_info b where b.i SFZ=1 and year (p. assdate)='| | yearr - 1;
The execute immediate temp1;
commit;
end if;
If (LV=2) then
Temp:='select p. assdate from jg_info b';
The execute immediate temp;
Temp:="insert into temp select b.u nitguid, b.i SFZ, p. assdate, biggest ounty from jg_info b '
| | 'where b.i SFZ=1'
| | 'and to_char (p. assdate,' yyyy ')='| | yearr;
The execute immediate temp;
Temp1:="insert into temp1 select b.u nitguid, b.i SFZ, p. assdate, biggest ounty from jg_info b '
| | 'where b.i SFZ=1'
| | 'and to_char (p. assdate,' yyyy ')='| | (yearr - 1);
The execute immediate temp1;
commit;
end if;

STRSQL:='create table tempOrgs as select * from KgOrgs a where Amy polumbo arentOrgCode=' | | orgcode;
The execute immediate STRSQL;


-????????????????????????????
Select count (*) into the from CNT KgOrgs where OrgName='???????????????????? ';
If (cnt> 0)
Then
STRSQL:='insert into tempOrgs
Select * from KgOrgs a where Amy polumbo arentOrgCode=(select OrgCode from tempOrgs where OrgName="???????????????????? ");
The execute immediate STRSQL;
STRSQL:='delete from tempOrgs where OrgName="???????????????????? "';
The execute immediate STRSQL;
commit;
end if;
Select count (*) into the from CNT KgOrgs where OrgName='???????? ';
If (cnt> 0) then
STRSQL:='insert into tempOrgs
Select * from KgOrgs a where Amy polumbo arentOrgCode=(select OrgCode from tempOrgs where OrgName="???????? ");
The execute immediate STRSQL;
STRSQL:='delete from tempOrgs where OrgName="???????? "';
The execute immediate STRSQL;
commit;
end if;

STRSQL:='create table resultt as Code, SELECT a.Org a.Org Name, a.N umberOfPeople, arjun edNum, a.C urrOccupationRatio
CASE,
WHEN (a.C urrOccupationRatio - a. astOccupationRatio) & lt; 0 THEN '-' | | CAST ((a. astOccupationRatio - a.C urrOccupationRatio) AS varchar2 (100))
WHEN (a.C urrOccupationRatio - a. astOccupationRatio)=0, THEN "' 0.00" '
The ELSE '+' | | CAST ((a.C urrOccupationRatio - a. astOccupationRatio) AS varchar2 (100))
END AS YearIncrease
CASE,
WHEN (a.C urrOccupationRatio - a.q gOccupationRatio) & lt; 0 THEN '-' | | CAST ((a.q gOccupationRatio - a.C urrOccupationRatio) AS varchar2 (100))
WHEN (a.C urrOccupationRatio - a.q gOccupationRatio)=0, THEN "' 0.00" '
The ELSE '+' | | CAST ((a.C urrOccupationRatio - a.q gOccupationRatio) AS varchar2 (100))
END AS QgIncrease
The FROM (
SELECT a.Org Code, a.Org Name, a.N umberOfPeople
, (
SELECT COUNT (1)
The FROM temp b
WHERE to_char (Name) a.org=b.org Name
) AS MedNum
CASE,
The WHEN NVL (a.n umberofpeople, CAST AS number (0))=0 THEN CAST AS number (0)
The ELSE CAST ((
SELECT COUNT (1)
The FROM temp b
WHERE to_char (Name) a.org=b.org Name
)/(CAST (a.n umberofpeople AS a decimal)/1000) * 100 AS a decimal (18, 2))
END AS CurrOccupationRatio
CASE,
The WHEN NVL (a.n umberofpeople, CAST AS number (0))=0 THEN CAST AS number (0)
The ELSE CAST ((
SELECT COUNT (1)
The FROM temp1 b
WHERE to_char (Name) a.org=b.org Name
)/(CAST (a.n umberofpeople AS a decimal)/1000) * 100 AS a decimal (18, 2))
END AS LastOccupationRatio, CAST ((
SELECT COUNT (1)
The FROM jg_info b
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull