//涉及到的知識點：游標的使用，根據參數構造動態SQL語句，遞歸查詢,在數據源里設置頁面數據顯示的樣式

create or replace procedure P_MES_SB_JBEffectCheck

(

P_BDate in varchar2,--開始年月???????????? --------------李XX

P_EDate in varchar2,--結束年月???????????? --------------設備有效作業率匯總表查詢(含季度)

P_ConditionId out varchar2???????????????? --------------2010-11-29

)

as

v_conditionId varchar2(50);?????????? --條件GUID

v_BDate varchar2(50);

v_EDate varchar2(50);

v_Month varchar2(50);--月份

v_Year varchar2(50);--年份

--v_YearAndMonth varchar2(50);--年月

v_SQLSTR varchar2(32766);

v_SQLPJSTR varchar2(32766);--車間平均

v_SQL1 varchar2(32766);

v_SQL2 varchar2(32766);

v_SQL3 varchar2(32766);

v_SQLHJ varchar2(32766);--機種小計

v_SQLCJHJ varchar2(32766);--車間合計

V_SQLCJ varchar2(32766); -- 車間

V_SQLJZ varchar2(32766); -- 機種

v_chejian varchar2(200);--車間ID

--v_chejianName varchar2(200);--車間合計名字

v_jizhong varchar2(200);--機種名字

v_jizhongName varchar2(200);--機種小計名字

v_innerSQL varchar2(3000);--非合計 inner ＳＱＬ

v_innerHJSQL varchar2(32766);--合計inner ＳＱＬ

--v_innerBZHJSQL varchar2(32766);--班組合計inner ＳＱＬ

v_innerHJKSQL varchar2(32766);--空

v_innerPJSQL varchar2(3000);--平均 inner ＳＱＬ

--v_innerHJPJSQL varchar2(32766);--車間平均合計inner ＳＱＬ

v_HJSQL1 varchar2(32766);--機種合計

v_HJSQL2 varchar2(32766);

v_HJSQL3 varchar2(32766);

v_HJSQL4 varchar2(32766);

--v_PJHJSQL1 varchar2(32766);--機種合計

--v_PJHJSQL2 varchar2(32766);

--v_PJHJSQL3 varchar2(32766);

--v_PJHJSQL4 varchar2(32766);

/* v_BZHJSQL1 varchar2(32766);--班組合計

v_BZHJSQL2 varchar2(32766);

v_BZHJSQL3 varchar2(32766);

v_BZHJSQL4 varchar2(32766);*/

--v_where varchar2(3000);

v_Date1 varchar2(32766);

v_Date2 varchar2(32766);

v_Date3 varchar2(32766);

v_Date4 varchar2(32766);

v_count NUMBER;

vT varchar2(200);--改變顏色

vColumnSql varchar2(32766); --改變顏色

vSql varchar2(32766);

TYPE My_CurType IS REF CURSOR;??????? --動態游標

v_cursorCJ My_CurType;??? --游標 班組

v_cursorJZ My_CurType;??? --游標 機種

v_cursorPJ My_CurType;??? --游標 車間平均

begin

--delete from dynaTempData;? commit;

--delete from dynaTempColumn ;commit;

--if P_BDate is NULL OR P_EDate is null THEN

-- v_BDate:=to_char(sysdate,'yyyy-MM');

--v_EDate:=to_char(sysdate,'yyyy-MM');

--ELSE

v_BDate:=to_char(to_date(P_BDate,'yyyy-MM'),'yyyy-MM');

v_EDate:=to_char(to_date(P_EDate,'yyyy-MM'),'yyyy-MM');

--end if;

v_conditionId:=sys_guid();

p_ConditionId:=v_conditionId;

--v_BDate:=to_char(to_date(P_BDate,'yyyy-MM'),'yyyy-MM');

--v_EDate:=to_char(to_date(P_EDate,'yyyy-MM'),'yyyy-MM');

---------------------#插入固定列頭#---------------------------

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField,mergetype)

values(sys_guid(),v_conditionId, 'T1', 'T1', '班組', '1', 120,'Model1',2);--左對齊

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField,mergetype)

values(sys_guid(),v_conditionId, 'T2', 'T2', '機種', '2', 150,'Model1',2);

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T3', 'T3', '機型', '3', 100,'Model1');

INSERT INTO dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder, width)

VALUES(sys_guid(), v_conditionId, 'T8', 'T8', '顏色', '100', 0);

---------------------##插入動態列標題##-----------------

IF v_BDate!='1111-11' THEN

--v_Month:=to_char(v_EDate,'MM');

v_Month:=substr(v_EDate,6,2);

v_Year:=substr(v_EDate,1,4);

--計算有效作用率--

v_innerSQL:='(

SELECT DECODE(sum(nvl(RD.F_BootDate,0))*CT.f_Name*MC.F_DesignCapacity,NULL,0,0,0,to_char(SUM(nvl(RD.f_Amount,0))*10000*100/(sum(nvl(RD.F_BootDate,0))*CT.f_Name*MC.F_DesignCapacity*60),''fm99999999999990.0000''))

FROM MES_SB_RunningRecord RD

JOIN

(?? SELECT DISTINCT(CASE WHEN f_name LIKE ''%甲%'' THEN ''甲班'' WHEN f_name LIKE ''%乙%'' THEN ''乙班''

WHEN f_name LIKE ''%丙%'' THEN ''丙班''

WHEN f_name LIKE ''%丁%'' THEN ''丁班''

ELSE f_name END) AS NewNAME,mes_jc_teamsid

FROM MES_JC_Teams

)? T ON t.mes_jc_teamsid=RD.mes_jc_teamsid

JOIN MES_JC_TZ TZ ON TZ.mes_jc_tzid=RD.MES_JC_TZId

JOIN MES_JC_ModelCode MC ON TZ.mes_jc_modelcodeid=MC.mes_jc_modelcodeid

JOIN (SELECT * FROM mes_jc_jjzj_view UNION SELECT * FROM mes_jc_bzzj_view) SS ON SS.MES_JC_TZID=RD.MES_JC_TZID

JOIN MES_JC_Coefficient CT ON CT.Mes_Jc_Coefficientid=MC.Mes_Jc_Coefficientid

WHERE T.NewNAME=TM.NewNAME AND SS.f_Name=S.f_Name AND MC.f_Model=M.f_Model

AND (RD.IsDeleted=''0'' or RD.IsDeleted is null)

AND (RD.IsDisabled=''0'' or RD.IsDisabled is null)

AND (to_char(RD.F_Date,''yyyy-MM'') ';

--計算車間平均

v_innerPJSQL:='(

SELECT DECODE(sum(nvl(RD.F_BootDate,0))*CT.f_Name*MC.F_DesignCapacity,NULL,0,0,0,to_char(SUM(nvl(RD.f_Amount,0))*10000*100/(sum(nvl(RD.F_BootDate,0))*CT.f_Name*MC.F_DesignCapacity*60),''fm99999999999990.0000''))

FROM MES_SB_RunningRecord RD

JOIN

(?? SELECT DISTINCT(CASE WHEN f_name LIKE ''%甲%'' THEN ''甲班'' WHEN f_name LIKE ''%乙%'' THEN ''乙班''

WHEN f_name LIKE ''%丙%'' THEN ''丙班''

WHEN f_name LIKE ''%丁%'' THEN ''丁班''

ELSE f_name END) AS NewNAME,mes_jc_teamsid

FROM MES_JC_Teams

)? T ON t.mes_jc_teamsid=RD.mes_jc_teamsid

JOIN MES_JC_TZ TZ ON TZ.mes_jc_tzid=RD.MES_JC_TZId

JOIN MES_JC_ModelCode MC ON TZ.mes_jc_modelcodeid=MC.mes_jc_modelcodeid

JOIN (SELECT * FROM mes_jc_jjzj_view UNION SELECT * FROM mes_jc_bzzj_view) SS ON SS.MES_JC_TZID=RD.MES_JC_TZID

JOIN MES_JC_Coefficient CT ON CT.Mes_Jc_Coefficientid=MC.Mes_Jc_Coefficientid

WHERE SS.f_Name=S.f_Name AND MC.f_Model=M.f_Model

AND (RD.IsDeleted=''0'' or RD.IsDeleted is null)

AND (RD.IsDisabled=''0'' or RD.IsDisabled is null)

AND (to_char(RD.F_Date,''yyyy-MM'') ';

---機種合計SQL---

v_innerHJSQL:='(SELECT decode(SUM(llcl),null,0,0,0,to_char(SUM(cl)*10000*100/SUM(llcl),''fm9999999999900.00''))

FROM (

SELECT sum(nvl(RD.F_AMOUNT,0)) AS cl,--產量

sum(nvl(RD.F_BootDate,0))*CT.f_Name*MC.F_DesignCapacity*60 AS llcl--理論產量

FROM MES_SB_RunningRecord RD

JOIN

(?? SELECT DISTINCT(CASE WHEN f_name LIKE ''%甲%'' THEN ''甲班'' WHEN f_name LIKE ''%乙%'' THEN ''乙班''

WHEN f_name LIKE ''%丙%'' THEN ''丙班''

WHEN f_name LIKE ''%丁%'' THEN ''丁班''

ELSE f_name END) AS NewNAME,mes_jc_teamsid

FROM MES_JC_Teams

)? T ON t.mes_jc_teamsid=RD.mes_jc_teamsid

JOIN MES_JC_TZ TZ ON TZ.mes_jc_tzid=RD.MES_JC_TZId

JOIN MES_JC_ModelCode MC ON TZ.mes_jc_modelcodeid=MC.mes_jc_modelcodeid

JOIN (SELECT * FROM mes_jc_jjzj_view UNION SELECT * FROM mes_jc_bzzj_view) SS ON SS.MES_JC_TZID=RD.MES_JC_TZID

JOIN MES_JC_Coefficient CT ON CT.Mes_Jc_Coefficientid=MC.Mes_Jc_Coefficientid

WHERE? (RD.IsDeleted=''0'' or RD.IsDeleted is null)

AND (RD.IsDisabled=''0'' or RD.IsDisabled is null)

AND (to_char(RD.F_Date,''yyyy-MM'')';

v_innerHJKSQL:= '(SELECT cl

FROM (

SELECT '''' AS cl,--產量

'''' AS llcl --理論產量

FROM MES_SB_RunningRecord RD

JOIN

(?? SELECT DISTINCT(CASE WHEN f_name LIKE ''%甲%'' THEN ''甲班'' WHEN f_name LIKE ''%乙%'' THEN ''乙班''

WHEN f_name LIKE ''%丙%'' THEN ''丙班''

WHEN f_name LIKE ''%丁%'' THEN ''丁班''

ELSE f_name END) AS NewNAME,mes_jc_teamsid

FROM MES_JC_Teams

)? T ON t.mes_jc_teamsid=RD.mes_jc_teamsid

JOIN MES_JC_TZ TZ ON TZ.mes_jc_tzid=RD.MES_JC_TZId

JOIN MES_JC_ModelCode MC ON TZ.mes_jc_modelcodeid=MC.mes_jc_modelcodeid

JOIN (SELECT * FROM mes_jc_jjzj_view UNION SELECT * FROM mes_jc_bzzj_view) SS ON SS.MES_JC_TZID=RD.MES_JC_TZID

JOIN MES_JC_Coefficient CT ON CT.Mes_Jc_Coefficientid=MC.Mes_Jc_Coefficientid

WHERE? (RD.IsDeleted=''0'' or RD.IsDeleted is null) AND ROWNUM=1

AND (RD.IsDisabled=''0'' or RD.IsDisabled is null? ';

if v_Month ='06' then

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T4', 'T4', '06月', '4',80,'Model2');--右對齊

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T5', 'T5', '04月-06月', '5',80,'Model2');

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T6', 'T6', '01月-06月', '6',80,'Model2');

v_Date1:='='''||v_Year||'-06'' ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue1,';

v_Date2:='? Between '''||v_Year||'-04''and '''||v_Year||'-06'' ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue2,';

v_Date3:='? Between '''||v_Year||'-01''and '''||v_Year||'-06'' ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue3 ,''44'' as yue4';

-- v_Date1:='';

v_SQLSTR:='SELECT DISTINCT TM.NewNAME,S.f_Name,M.f_Model,

'||v_innerSQL||v_Date1||v_innerSQL||v_Date2||v_innerSQL||v_Date3;

--dbms_output.put_line(v_innerSQL||v_Date1||v_innerSQL||v_Date2);

---機種合計

v_HJSQL1:=v_innerHJSQL||' ='''||v_Year||'-06 ''';

v_HJSQL2:=v_innerHJSQL||' Between '''||v_Year||'-04''and '''||v_Year||'-06 ''';

v_HJSQL3:=v_innerHJSQL||' Between '''||v_Year||'-01''and '''||v_Year||'-06''';

v_HJSQL4:=v_innerHJKSQL;

--車間平均

v_SQLPJSTR:='SELECT DISTINCT S.f_Name,M.f_Model,

'||v_innerPJSQL||v_Date1||v_innerPJSQL||v_Date2||v_innerPJSQL||v_Date3;

end if;

if v_Month ='09' then

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T4', 'T4', '09月', '4',80,'Model2');

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T5', 'T5', '07月-09月', '5',80,'Model2');

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T6', 'T6', '01月-09月', '6',80,'Model2');

v_Date1:='='''||v_Year||'-09'' ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue1,';

v_Date2:='? Between '''||v_Year||'-07''and '''||v_Year||'-09'' ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue2,';

v_Date3:='? Between '''||v_Year||'-01''and '''||v_Year||'-09'' ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue3 ,''44'' as yue4 ';

v_SQLSTR:='SELECT DISTINCT TM.NewNAME,S.f_Name,M.f_Model,

'||v_innerSQL||v_Date1||v_innerSQL||v_Date2||v_innerSQL||v_Date3;

---機種合計

v_HJSQL1:=v_innerHJSQL||' ='''||v_Year||'-09''';

v_HJSQL2:=v_innerHJSQL||' Between '''||v_Year||'-07''and '''||v_Year||'-09''';

v_HJSQL3:=v_innerHJSQL||' Between '''||v_Year||'-01''and '''||v_Year||'-09''';

v_HJSQL4:=v_innerHJKSQL;

--車間平均

v_SQLPJSTR:='SELECT DISTINCT S.f_Name,M.f_Model,

'||v_innerPJSQL||v_Date1||v_innerPJSQL||v_Date2||v_innerPJSQL||v_Date3;

end if;

if v_Month ='12' then

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T4', 'T4', '12月', '4',80,'Model2');

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T5', 'T5', '10月-12月', '5',80,'Model2');

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T6', 'T6', '07月-12月', '6',80,'Model2');

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T7', 'T7', '01月-12月', '7',80,'Model2');

v_Date1:='='''||v_Year||'-12'' ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue1 ,';

v_Date2:='? Between '''||v_Year||'-10''and '''||v_Year||'-12'' ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue2,';

v_Date3:='? Between '''||v_Year||'-07''and '''||v_Year||'-12'' ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue3, ';

v_Date4:='? Between '''||v_Year||'-01''and '''||v_Year||'-12'' ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue4 ';

v_SQLSTR:='SELECT DISTINCT TM.NewNAME,S.f_Name,M.f_Model,? '||v_innerSQL||v_Date1||v_innerSQL||v_Date2||v_innerSQL||v_Date3||v_innerSQL||v_Date4;

---機種合計

v_HJSQL1:=v_innerHJSQL||'='''||v_Year||'-12''';

v_HJSQL2:=v_innerHJSQL||' Between '''||v_Year||'-10''and '''||v_Year||'-12''';

v_HJSQL3:=v_innerHJSQL||' Between '''||v_Year||'-07''and '''||v_Year||'-12''';

v_HJSQL4:=v_innerHJSQL||' Between '''||v_Year||'-01''and '''||v_Year||'-12''';

--車間平均

v_SQLPJSTR:='SELECT DISTINCT S.f_Name,M.f_Model,? '||v_innerPJSQL||v_Date1||v_innerPJSQL||v_Date2||v_innerPJSQL||v_Date3||v_innerPJSQL||v_Date4;

end if;

if v_Month !='06' AND v_Month !='09' AND v_Month !='12' then

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T4', 'T4', v_Month||'月', '4',80,'Model2');--右對齊

insert into dynaTempColumn(dynaTempColumnId,ConditionId,FieldName,AsName,Caption1,displayOrder,width,RefField)

values(sys_guid(),v_conditionId, 'T5', 'T5', '01月-'||v_Month||'月', '4',80,'Model2');

--v_Date1:='='''||v_Year||'-09'') as yue1,';

v_Date1:='='''||v_Year||'-'||v_Month||''' ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue1,';

v_Date2:='Between '''||v_Year||'-01'' and '''||v_Year||'-'||v_Month||'''? ) GROUP BY CT.f_Name,MC.F_DesignCapacity ) as yue2,''33'' as yue3 ,''44'' as yue4';

v_SQLSTR:='SELECT DISTINCT TM.NewNAME,S.f_Name,M.f_Model,

'||v_innerSQL||v_Date1||v_innerSQL||v_Date2;

---機種合計

v_HJSQL1:=v_innerHJSQL||'='''||v_Year||'-'||v_Month||'''';

v_HJSQL2:=v_innerHJSQL||' Between '''||v_Year||'-01'' and '''||v_Year||'-'||v_Month||'''';

v_HJSQL3:=v_innerHJKSQL;

v_HJSQL4:=v_innerHJKSQL;

--車間平均

v_SQLPJSTR:='SELECT DISTINCT S.f_Name,M.f_Model,

'||v_innerPJSQL||v_Date1||v_innerPJSQL||v_Date2;

end if;

v_SQL2:=' FROM MES_SB_RunningRecord R

LEFT JOIN MES_JC_TZ Z ON Z.mes_jc_tzid=R.MES_JC_TZId

LEFT JOIN MES_JC_ModelCode M ON Z.mes_jc_modelcodeid=M.mes_jc_modelcodeid

JOIN (SELECT * FROM mes_jc_jjzj_view UNION SELECT * FROM mes_jc_bzzj_view) S ON S.MES_JC_TZID=R.MES_JC_TZID

JOIN MES_JC_Coefficient CT ON CT.Mes_Jc_Coefficientid=M.Mes_Jc_Coefficientid

JOIN

(SELECT DISTINCT(CASE WHEN f_name LIKE ''%甲%'' THEN ''甲班'' WHEN f_name LIKE ''%乙%'' THEN ''乙班''

WHEN f_name LIKE ''%丙%'' THEN ''丙班''

WHEN f_name LIKE ''%丁%'' THEN ''丁班''

ELSE f_name END) AS NewNAME,mes_jc_teamsid

FROM MES_JC_Teams)

TM ON TM.mes_jc_teamsid=R.mes_jc_teamsid

WHERE? (R.IsDeleted=''0'' or R.IsDeleted is null)

AND (R.IsDisabled=''0'' or R.IsDisabled is null)

';

v_SQLSTR:=v_SQLSTR||',''0?? '' as TTT'||v_SQL2;

v_SQLPJSTR:=v_SQLPJSTR||',''0?? '' as TTT '||v_SQL2;

------------------##游標循環-班組--------------------------------

V_SQLCJ:='select distinct TM.NewNAME from MES_SB_RunningRecord R

JOIN (SELECT DISTINCT(CASE WHEN f_name LIKE ''%甲%'' THEN ''甲班'' WHEN f_name LIKE ''%乙%'' THEN ''乙班''

WHEN f_name LIKE ''%丙%'' THEN ''丙班''

WHEN f_name LIKE ''%丁%'' THEN ''丁班''

ELSE f_name END) AS NewNAME,mes_jc_teamsid

FROM MES_JC_Teams

) TM ON TM.mes_jc_teamsid=R.mes_jc_teamsid

where (R.IsDeleted=''0'' or R.IsDeleted is null)

AND (R.IsDisabled=''0'' or R.IsDisabled is null)';

v_count:=0;

open v_cursorCJ FOR? V_SQLCJ;????????????loop

FETCH v_cursorCJ into v_chejian;--班組

EXIT WHEN v_cursorCJ%NOTFOUND;

--v_count:=v_count+1;

--內層游標

V_SQLJZ:='SELECT DISTINCT S.f_Name

FROM MES_SB_RunningRecord R

JOIN (SELECT * FROM mes_jc_jjzj_view UNION SELECT * FROM mes_jc_bzzj_view) S ON S.MES_JC_TZID=R.MES_JC_TZID

JOIN

(SELECT DISTINCT(CASE WHEN f_name LIKE ''%甲%'' THEN ''甲班'' WHEN f_name LIKE ''%乙%'' THEN ''乙班''

WHEN f_name LIKE ''%丙%'' THEN ''丙班''

WHEN f_name LIKE ''%丁%'' THEN ''丁班''

ELSE f_name END) AS NewNAME,tm.mes_jc_teamsid

FROM MES_JC_Teams tm)

T ON t.mes_jc_teamsid=R.mes_jc_teamsid

WHERE T.NewNAME='''||v_chejian||'''';

open v_cursorJZ FOR? V_SQLJZ;

loop

FETCH v_cursorJZ into v_jizhong;--機種

EXIT WHEN v_cursorJZ%NOTFOUND;

v_count:=v_count+1;

v_SQL1:='SELECT sys_guid(),'''||v_conditionId||''','''||v_count||''', NewNAME , f_Name||''設備'',f_Model,

to_char(nvl(yue1,0),''fm99999999999990.00'')||''%'' ,to_char(nvl(yue2,0),''fm99999999999990.00'')||''%'' ,to_char(nvl(yue3,0),''fm99999999999990.00'')||''%'' ,to_char(nvl(yue4,0),''fm99999999999990.00'')||''%'',TTT? FROM? ('||v_SQLSTR;

v_SQL2:=v_SQL1||' and NewNAME='''||v_chejian||''' and S.f_Name='''||v_jizhong||''' ) order by f_Model';

v_SQL3:='insert into dynaTempData(dynaTempDataId,conditionid,displayorder,T1,T2,T3,T4,T5,T6,T7,T8)'||v_SQL2;

execute immediate v_SQL3;

------機種合計--

v_count:=v_count+1;

v_jizhongName:=v_jizhong||'設備合計';

v_SQLHJ:='insert into dynaTempData(dynaTempDataId,conditionid,displayorder,T1,T2,T3,T4,T5,T6,T7,T8)

SELECT sys_guid(),'''||v_conditionId||''','''||v_count||''','''||v_chejian||''','''||v_jizhongName||''','' '',

to_char('||v_HJSQL1||' ) AND T.NewNAME='''||v_chejian||''' and SS.f_Name='''||v_jizhong||''' GROUP BY T.NewNAME,SS.f_Name,MC.f_Model,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL2||' ) AND T.NewNAME='''||v_chejian||''' and SS.f_Name='''||v_jizhong||''' GROUP BY T.NewNAME,SS.f_Name,MC.f_Model,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL3||' ) AND T.NewNAME='''||v_chejian||''' and SS.f_Name='''||v_jizhong||''' GROUP BY T.NewNAME,SS.f_Name,MC.f_Model,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL4||' ) AND T.NewNAME='''||v_chejian||''' and SS.f_Name='''||v_jizhong||''' GROUP BY T.NewNAME,SS.f_Name,MC.f_Model,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

''1'' as TTT? from dual';

execute IMMEDIATE v_SQLHJ;

/*INSERT INTO atest(title)

SELECT v_SQLHJ FROM dual;*/

COMMIT;

end loop;

close v_cursorJZ;--關閉游標

---班組合計

v_count:=v_count+1;

v_SQLCJHJ:='insert into dynaTempData(dynaTempDataId,conditionid,displayorder,T1,T2,T3,T4,T5,T6,T7,T8)

SELECT sys_guid(),'''||v_conditionId||''','''||v_count||''','''||v_chejian||''',''總計(卷接、包裝設備)'','' '',

to_char('||v_HJSQL1||' ) AND T.NewNAME='''||v_chejian||''' GROUP BY T.NewNAME,SS.f_Name,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL2||' ) AND T.NewNAME='''||v_chejian||''' GROUP BY T.NewNAME,SS.f_Name,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL3||' ) AND T.NewNAME='''||v_chejian||''' GROUP BY T.NewNAME,SS.f_Name,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL4||' ) AND T.NewNAME='''||v_chejian||''' GROUP BY T.NewNAME,SS.f_Name,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

''1'' as TTT? from dual';

execute IMMEDIATE v_SQLCJHJ;

end loop;

close v_cursorCJ;--關閉游標

--------------------計算車間平均

V_SQLJZ:='SELECT DISTINCT S.f_Name

FROM MES_SB_RunningRecord R

JOIN (SELECT * FROM mes_jc_jjzj_view UNION SELECT * FROM mes_jc_bzzj_view) S ON S.MES_JC_TZID=R.MES_JC_TZID';

open v_cursorPJ FOR? V_SQLJZ;

loop

FETCH v_cursorPJ into v_jizhong;--機種

EXIT WHEN v_cursorPJ%NOTFOUND;

v_count:=v_count+1;

v_SQL1:='SELECT sys_guid(),'''||v_conditionId||''','''||v_count||''', ''車間平均'' , f_Name||''設備'',f_Model,

to_char(nvl(yue1,0),''fm99999999999990.00'')||''%'' ,to_char(nvl(yue2,0),''fm99999999999990.00'')||''%'' ,to_char(nvl(yue3,0),''fm99999999999990.00'')||''%'' ,to_char(nvl(yue4,0),''fm99999999999990.00'')||''%'',TTT? FROM ('||v_SQLPJSTR;

v_SQL2:=v_SQL1||' and S.f_Name='''||v_jizhong||''' ) order by f_Model';

v_SQL3:='insert into dynaTempData(dynaTempDataId,conditionid,displayorder,T1,T2,T3,T4,T5,T6,T7,T8)'||v_SQL2;

execute immediate v_SQL3;

------機種合計--

v_count:=v_count+1;

v_jizhongName:=v_jizhong||'設備合計';

v_SQLHJ:='insert into dynaTempData(dynaTempDataId,conditionid,displayorder,T1,T2,T3,T4,T5,T6,T7,T8)

SELECT sys_guid(),'''||v_conditionId||''','''||v_count||''',''車間平均'','''||v_jizhongName||''','' '',

to_char('||v_HJSQL1||' )? and SS.f_Name='''||v_jizhong||''' GROUP BY SS.f_Name,MC.f_Model,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL2||' )? and SS.f_Name='''||v_jizhong||''' GROUP BY SS.f_Name,MC.f_Model,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL3||' )? and SS.f_Name='''||v_jizhong||''' GROUP BY SS.f_Name,MC.f_Model,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL4||' )? and SS.f_Name='''||v_jizhong||''' GROUP BY SS.f_Name,MC.f_Model,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

''1'' as TTT? from dual';

execute IMMEDIATE v_SQLHJ;

COMMIT;

end loop;

close v_cursorPJ;

--車間平均 總計

v_count:=v_count+1;

v_SQLCJHJ:='insert into dynaTempData(dynaTempDataId,conditionid,displayorder,T1,T2,T3,T4,T5,T6,T7,T8)

SELECT sys_guid(),'''||v_conditionId||''','''||v_count||''',''車間平均'',''總計(卷接、包裝設備)'','' '',

to_char('||v_HJSQL1||' )? GROUP BY T.NewNAME,SS.f_Name,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL2||' )? GROUP BY T.NewNAME,SS.f_Name,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL3||' )? GROUP BY T.NewNAME,SS.f_Name,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

to_char('||v_HJSQL4||' )? GROUP BY T.NewNAME,SS.f_Name,CT.f_Name,MC.F_DesignCapacity)),''fm999999999990.00'')||''%'',

''1'' as TTT? from dual';

execute IMMEDIATE v_SQLCJHJ;

-----------------------------------設置顏色-------------

vT:='T1';

vColumnSql:='T1=''''||DECODE('||vT||','' '',''　'','||vT||')||''''';

FOR R IN 2..8

LOOP

BEGIN

if R<9 then

begin

vT:=to_char('T'||R);

vColumnSql:=vColumnSql||','||vT||'=''''||DECODE('||vT||','' '',''　'','||vT||')||''''';

end;

end if;

END;

END LOOP;

vSql:='UPDATE dynatempdata SET '||vColumnSql||' WHERE conditionid='''||v_Conditionid||''' and T8=''1''';

EXECUTE IMMEDIATE vSql;

END IF;

end P_MES_SB_JBEffectCheck;

總結

以上是生活随笔為你收集整理的oracle分区存储过程示例,Oracle 存储过程示例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。