WildSeryМне вот интересно, что за вычисления такие в процедуре SP1 происходят, а то вдруг их можно в запросе сделать.
Выборка из одной-единственной таблицы, даже соединять ничего не надо.
WildSery,
может и можно, оцените сами.
Главная причина, вынесение кода в отдельную SP1, это то, что вычисления вызываются из различных мест БД, и при изменении алгоритма логично менять только в одном месте, в SP1.
SP1
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
create or alter procedure SP1 (
ID integer,
NO_CL smallint)
returns (
A DOM_FLOAT_Q,
B DOM_FLOAT_Q,
C DOM_FLOAT_Q,
D DOM_FLOAT_Q)
as
declare variable T0 type of column INPUT_HOUR_DATA.T_IN_01;
declare variable T1 type of column INPUT_HOUR_DATA.T1_CL_03;
declare variable T2 type of column INPUT_HOUR_DATA.T2_CL_03;
declare variable T3 type of column INPUT_HOUR_DATA.T3_CL_03;
declare variable T4 type of column INPUT_HOUR_DATA.T4_CL_03;
declare variable FV type of column INPUT_HOUR_DATA.FL_CL_03;
declare variable KOEF DOM_FLOAT_3;
declare variable DT_ type of column INPUT_HOUR_DATA.T1_CL_03;
declare variable DT10 type of column INPUT_HOUR_DATA.T1_CL_03;
declare variable DT21 type of column INPUT_HOUR_DATA.T1_CL_03;
declare variable DT32 type of column INPUT_HOUR_DATA.T1_CL_03;
declare variable DT43 type of column INPUT_HOUR_DATA.T1_CL_03;
begin
KOEF = (select Q_KOEF_WT_KKAL from main_const where (id = 0));
select (t_in_01 + t_in_02 + t_in_03 + t_in_04 + t_in_05 + t_in_06) / 6 from input_hour_data where (id = :id) into :t0; -- dafault
if ((:no_cl>= 3) and (:no_cl<=27)) then -- ДЛЯ 03-27 РАСЧЕТ ТИПОВОЙ
begin
select tin from MODEL_CALC_TIN_BY_CL (:id, 1, :no_cl) into :t0;
-- Выбор параметров
if (:no_cl=03) then select FL_CL_03, T1_CL_03, T2_CL_03, T3_CL_03, T4_CL_03 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=04) then select FL_CL_04, T1_CL_04, T2_CL_04, T3_CL_04, T4_CL_04 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=05) then select FL_CL_05, T1_CL_05, T2_CL_05, T3_CL_05, T4_CL_05 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=06) then select FL_CL_06, T1_CL_06, T2_CL_06, T3_CL_06, T4_CL_06 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=07) then select FL_CL_07, T1_CL_07, T2_CL_07, T3_CL_07, T4_CL_07 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=08) then select FL_CL_08, T1_CL_08, T2_CL_08, T3_CL_08, T4_CL_08 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=09) then select FL_CL_09, T1_CL_09, T2_CL_09, T3_CL_09, T4_CL_09 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=10) then select FL_CL_10, T1_CL_10, T2_CL_10, T3_CL_10, T4_CL_10 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=11) then select FL_CL_11, T1_CL_11, T2_CL_11, T3_CL_11, T4_CL_11 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=12) then select FL_CL_12, T1_CL_12, T2_CL_12, T3_CL_12, T4_CL_12 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=13) then select FL_CL_13, T1_CL_13, T2_CL_13, T3_CL_13, T4_CL_13 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=14) then select FL_CL_14, T1_CL_14, T2_CL_14, T3_CL_14, T4_CL_14 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=15) then select FL_CL_15, T1_CL_15, T2_CL_15, T3_CL_15, T4_CL_15 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=16) then select FL_CL_16, T1_CL_16, T2_CL_16, T3_CL_16, T4_CL_16 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=17) then select FL_CL_17, T1_CL_17, T2_CL_17, T3_CL_17, T4_CL_17 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=18) then select FL_CL_18, T1_CL_18, T2_CL_18, T3_CL_18, T4_CL_18 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=19) then select FL_CL_19, T1_CL_19, T2_CL_19, T3_CL_19, T4_CL_19 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=20) then select FL_CL_20, T1_CL_20, T2_CL_20, T3_CL_20, T4_CL_20 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=21) then select FL_CL_21, T1_CL_21, T2_CL_21, T3_CL_21, T4_CL_21 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=22) then select FL_CL_22, T1_CL_22, T2_CL_22, T3_CL_22, T4_CL_22 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=23) then select FL_CL_23, T1_CL_23, T2_CL_23, T3_CL_23, T4_CL_23 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=24) then select FL_CL_24, T1_CL_24, T2_CL_24, T3_CL_24, T4_CL_24 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=25) then select FL_CL_25, T1_CL_25, T2_CL_25, T3_CL_25, T4_CL_25 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=26) then select FL_CL_26, T1_CL_26, T2_CL_26, T3_CL_26, T4_CL_26 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=27) then select FL_CL_27, T1_CL_27, T2_CL_27, T3_CL_27, T4_CL_27 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
-- расчет
-- ПРОВЕРКИ и ВООСТАНОВЛЕНИЕ НЕРАБОТАЮЩИХ
-- Для всех холодильников проверка на наличие нуля
if ((:T1 <= 0) and (:T0 > 0) and (:T2 > 0)) then T1 = (T0 + T2) / 2;
if ((:T2 <= 0) and (:T1 > 0) and (:T3 > 0)) then T2 = (T1 + T3) / 2;
if ((:T3 <= 0) and (:T2 > 0) and (:T4 > 0)) then T3 = (T2 + T4) / 2;
-- Для температур Т1, Т2 и Т3 проверка чтоб не было Тi < Тi-1
if (:T1 <= :T0) then T1 = (T0 + T2) / 2;
if (:T2 <= :T1) then T2 = (T1 + T3) / 2;
if (:T3 <= :T2) then T3 = (T2 + T4) / 2;
if (:T3 <= :T2) then T2 = (T1 + T3) / 2;
-- Корректировка перепадов
dT10 = T1 - T0;
dT21 = T2 - T1;
dT32 = T3 - T2;
dT43 = ABS (:T4 -:T3 );
-- Корректировка перепадов
if (:dT32 < 0.000) then dT32 = 0;
if (:dT21 > :dT10) then dT21 = ( dT10 + dT32 ) / 2;
if (:dT32 > :dT10) then dT32 = dT32 / 2;
dT_ = ( dT10 + dT21 + dT32 ) / 3;
if (:dT21 < 0.200) then dT21 = dT_;
if (:dT32 < 0.200) then dT32 = dT_;
if (:dT32 > :dT10) then dT32 = dT32 / 2;
if (:dT43 > :dT32) then dT43 = dT43 / 2;
-- Расчет по формулам
D = 3 * KOEF * fv * ( dT10 - ( dT21 + dT32 ) / 2 ); -- D = 3 * KOEF * 1 * fv * (T0 + T3);
C = 3 * KOEF * fv * dT21; -- C = 3 * KOEF * 2 * fv * (T1 + T2 - T0 - T3);
B = 3 * KOEF * fv * dT32; -- B = 3 * KOEF * 2 * fv * (T3 - T2);
A = 3 * KOEF * fv * dT43; -- A = 3 * KOEF * 1 * fv * (T4 - T3);
end
else -- ДЛЯ TH
begin
-- ВГ ВЛ НЛ
if (:no_cl=28) then select FV_CL_28, T1_CL_28, T2_CL_28, FV_VG_28, T1_VG_28 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=29) then select FV_CL_29, T1_CL_29, T2_CL_29, FV_VG_29, T1_VG_29 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=30) then select FV_CL_30, T1_CL_30, T2_CL_30, FV_VG_30, T1_VG_30 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=31) then select FV_CL_31, T1_CL_31, T2_CL_31, FV_VG_31, T1_VG_31 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=32) then select FV_CL_32, T1_CL_32, T2_CL_32, FV_VG_32, T1_VG_32 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=01) then select FV_CL_01, T1_CL_01, T2_CL_01, FV_VG_01, T1_VG_01 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:no_cl=02) then select FV_CL_02, T1_CL_02, T2_CL_02, FV_VG_02, T1_VG_02 from input_hour_data where (id = :id) into :fv, :t1, :t2, :t3, :t4;
if (:T1 <= 0) then T1 = ( T0 + T2 ) / 2; -- ПРОВЕРКИ и ВООСТАНОВЛЕНИЕ НЕРАБОТАЮЩИХ
select tin from MODEL_CALC_TIN_BY_CL (:id, 4, :no_cl) into :t0; D = 1 * KOEF * T3 * ( T4 - T0); -- D = KOEF * T3 * (T4 - T0);
select tin from MODEL_CALC_TIN_BY_CL (:id, 2, :no_cl) into :t0; B = 3 * KOEF * fv * ( T1 - T0); -- B = KOEF * fv * (T1 - T0);
select tin from MODEL_CALC_TIN_BY_CL (:id, 1, :no_cl) into :t0; A = 3 * KOEF * fv * abs(:T2 - :T1); -- A = KOEF * fv * (T2 - T1);
-- НГ (для семи по-разному)
select tin from MODEL_CALC_TIN_BY_CL (:id, 3, :no_cl) into :t0;
if (:no_cl=28) then C = (select :KOEF * fv_ng_28 * (t1_ng_28 - :t0) from input_hour_data where (id = :id));
if (:no_cl=29) then C = (select :KOEF * ( (fv_th_29_1 * (t1_th_29_1 - :t0)) +
(fv_th_29_2 * (t1_th_29_2 - :t0)) ) from input_hour_data where (id = :id));
if (:no_cl=30) then C = (select :KOEF * ( (fv_th_30_1 * (t1_th_30_1 - :t0)) +
(fv_th_30_2 * (t1_th_30_2 - :t0)) ) from input_hour_data where (id = :id));
if (:no_cl=31) then C = maxvalue ( (select :KOEF * (fv_ng_31_1 * (t1_ng_31_1 - :t0)) from input_hour_data where (id = :id)) ,
(select :KOEF * (fv_ng_31_2 * (t1_ng_31_2 - :t0)) from input_hour_data where (id = :id)) );
if (:no_cl=32) then C = (select :KOEF * ( (fv_th_32_1 * (t1_th_32_1 - :t0)) +
(fv_th_32_2 * (t1_th_32_2 - :t0)) ) from input_hour_data where (id = :id));
if (:no_cl=01) then C = (select :KOEF * ( (fv_th_01_1 * (t1_th_01_1 - :t0)) +
(fv_th_01_2 * (t1_th_01_2 - :t0)) ) from input_hour_data where (id = :id));
if (:no_cl=02) then C = (select :KOEF * fv_ng_02 * (t1_ng_02 - :t0) from input_hour_data where (id = :id));
end
suspend;
end
|
