Код: Выделить всё
SET TERM ^ ;
CREATE PROCEDURE TABLE_WORK_FACTORY_2 (
PARAM_DATE DATE)
RETURNS (
ACCEPTED_ORES_DAY DOUBLE PRECISION,
ACCEPTED_ORES_DAY_DIFFERENCE DOUBLE PRECISION,
ACCEPTED_ORES_BEGINNING_MONTH DOUBLE PRECISION,
ACCEPTED_ORES_BEGIN_MONTH_DIFF DOUBLE PRECISION,
PERCENT_FILLING_BUNKER_BEGIN DOUBLE PRECISION,
PERCENT_FILLING_BUNKER_END DOUBLE PRECISION,
PROCESSING_SCALES_SHOP2_DAY DOUBLE PRECISION,
PROCESSING_SCALES_SHOP2_DIFF_D DOUBLE PRECISION,
PROCESSING_SCALES_SHOP2_BM DOUBLE PRECISION,
PROCESSING_SCALES_SHOP2_DIFF_BM DOUBLE PRECISION,
PROCESSING_ON_BUNKER_DAY DOUBLE PRECISION,
PROCESSING_ON_BUNKER_DAY_DIFF DOUBLE PRECISION,
PROCESSING_ON_BUNKER_BM DOUBLE PRECISION,
PROCESSING_ON_BUNKER_BM_DIFF DOUBLE PRECISION,
HOURLY_AVERAGE_PROCESSING_DAY DOUBLE PRECISION,
HOURLY_AVERAGE_PROCESSING_BM DOUBLE PRECISION,
SAND_DAY DOUBLE PRECISION,
SAND_BM DOUBLE PRECISION,
PRODUCT1_DAY DOUBLE PRECISION,
OVERFALL_NACN_HEAD DOUBLE PRECISION,
OVERFALL_NACN_TAIL DOUBLE PRECISION,
PH_PULP DOUBLE PRECISION,
RECEIVED_PHOSPHORUS_FLOUR_DAY DOUBLE PRECISION,
RECEIVED_PHOSPHOR_CONCENTR_DAY DOUBLE PRECISION,
RECEIVED_PHOSPHOR_FLOUR_BM DOUBLE PRECISION,
RECEIVED_PHOSPHOR_CONCENTR_BM DOUBLE PRECISION,
SENT_PHOSPHOR_FLOUR_DAY DOUBLE PRECISION,
SENT_PHOSPHOR_FLOUR_DAY_BM DOUBLE PRECISION,
SENT_PHOSPHOR_CONCENTR_DAY DOUBLE PRECISION,
SENT_PHOSPHOR_CONCENTR_DAY_BM DOUBLE PRECISION,
TJ_THICKENED_UNDERFLOW DOUBLE PRECISION,
TJ_TAIL_UNDERFLOW DOUBLE PRECISION,
SPECIFIC_THICKENED DOUBLE PRECISION,
P137_6 DOUBLE PRECISION,
P138_12 DOUBLE PRECISION,
AU_IN_SOLID_PHASE DOUBLE PRECISION,
CLASS_074 DOUBLE PRECISION,
E1 DOUBLE PRECISION,
E12 DOUBLE PRECISION,
E25 DOUBLE PRECISION,
AU_FEEDING_SORBTION DOUBLE PRECISION,
PITCH_SORBTION DOUBLE PRECISION,
PITCH_REGENERATION DOUBLE PRECISION,
EDUCTION_REGEN DOUBLE PRECISION,
COMMODITY_REGEN DOUBLE PRECISION,
WEIGHT_WET DOUBLE PRECISION,
AMOUN_THICKENER DOUBLE PRECISION,
TJ_THICKENED_UNDERFLOW_BM DOUBLE PRECISION,
TJ_TAIL_UNDERFLOW_BM DOUBLE PRECISION,
P138_12_BM DOUBLE PRECISION,
AU_IN_SOLID_PHASE_BM DOUBLE PRECISION,
E1_BM DOUBLE PRECISION,
E12_BM DOUBLE PRECISION,
E25_BM DOUBLE PRECISION,
PULP_EXHAUST_DAY DOUBLE PRECISION,
PULP_EXHAUST_BM DOUBLE PRECISION)
AS
DECLARE VARIABLE CORRECTION_OTK_PROCESSING_SHOP1 DOUBLE PRECISION;
DECLARE VARIABLE CORRECTION_OTK_PROCESSING_SHOP2 DOUBLE PRECISION;
DECLARE VARIABLE VAR_DAY SMALLINT;
DECLARE VARIABLE VAR_MONTH SMALLINT;
DECLARE VARIABLE VAR_YEAR SMALLINT;
DECLARE VARIABLE VAR_PLAN_DAY DOUBLE PRECISION;
DECLARE VARIABLE VAR_CORRECTION_SHOP1_BM DOUBLE PRECISION;
DECLARE VARIABLE VAR_CORRECTION_SHOP2_BM DOUBLE PRECISION;
DECLARE VARIABLE NEW_TEMP1 DOUBLE PRECISION;
DECLARE VARIABLE NEW_TEMP2 INTEGER;
DECLARE VARIABLE PLAN_PROCESSING_ORE_BEGIN_MONTH DOUBLE PRECISION;
DECLARE VARIABLE VAR_I DATE;
DECLARE VARIABLE VAR_BUNKER_BEGIN DOUBLE PRECISION;
DECLARE VARIABLE VAR_BUNKER_END DOUBLE PRECISION;
DECLARE VARIABLE VAR_DRY_SHOP1 DOUBLE PRECISION;
DECLARE VARIABLE VAR_BEGIN_DATE DATE;
begin
var_day = EXTRACT (DAY FROM PARAM_DATE);
var_month = EXTRACT (MONTH FROM PARAM_DATE);
var_year = EXTRACT (YEAR FROM PARAM_DATE);
/* Определение начала месяца */
EXECUTE PROCEDURE begin_month (:var_month, :var_year) RETURNING_VALUES :var_begin_date;
SELECT PLAN_DAY
FROM PLAN_PROCESSING Plan_processing
WHERE (YEAR_REG = :var_year)
AND (MONTH_REG = :var_month)
INTO :var_plan_day;
plan_processing_ore_begin_month = var_plan_day * var_day;
SELECT PRODUCT_DAY, KORRECTIV_SHOP1, KORRECTIV_SHOP2, NACN_GOLOVA, NACN_TAIL,
REV_FOSFOR_MYKA, REV_FOSFOR_CONCNTRAT, SEND_FOSFOR_MYKA, SEND_FOSFOR_CONCENTRAT
from DAY_CONFIG
WHERE DATE_CONFIG=:PARAM_DATE
INTO :product1_day, :correction_otk_processing_shop1,
:correction_otk_processing_shop2, :overfall_nacn_head,
:overfall_nacn_tail, :received_phosphorus_flour_day, :received_phosphor_concentr_day,
:sent_phosphor_flour_day, :sent_phosphor_concentr_day;
SELECT Sum(KORRECTIV_SHOP1), Sum(KORRECTIV_SHOP2), Sum(REV_FOSFOR_MYKA),
Sum(REV_FOSFOR_CONCNTRAT), Sum(SEND_FOSFOR_MYKA), Sum(SEND_FOSFOR_CONCENTRAT)
FROM DAY_CONFIG
WHERE DATE_CONFIG BETWEEN :var_begin_date and :PARAM_DATE
INTO :var_correction_shop1_bm, :var_correction_shop2_bm, :received_phosphor_flour_bm,
:received_phosphor_concentr_bm, :SENT_PHOSPHOR_FLOUR_DAY_BM, :SENT_PHOSPHOR_CONCENTR_DAY_BM;
if (var_correction_shop1_bm is null) then
var_correction_shop1_bm = 0;
if (var_correction_shop2_bm is null) then
var_correction_shop2_bm = 0;
EXECUTE PROCEDURE shop1_day_dry_bunker(:PARAM_DATE)
RETURNING_VALUES
:accepted_ores_day,
:percent_filling_bunker_begin, :percent_filling_bunker_end;
accepted_ores_day_difference = accepted_ores_day - :var_plan_day;
processing_on_bunker_day = accepted_ores_day + (percent_filling_bunker_begin
- percent_filling_bunker_end) * 600;
processing_on_bunker_day_diff = processing_on_bunker_day - var_plan_day;
var_i = var_begin_date;
accepted_ores_beginning_month = accepted_ores_day;
processing_on_bunker_bm = processing_on_bunker_day;
WHILE (var_i < PARAM_DATE) do
begin
EXECUTE PROCEDURE shop1_day_dry_bunker(:var_i)
RETURNING_VALUES :var_dry_shop1, :var_bunker_begin, :var_bunker_end;
accepted_ores_beginning_month = accepted_ores_beginning_month + var_dry_shop1;
processing_on_bunker_bm = processing_on_bunker_bm + (var_dry_shop1
+ (var_bunker_begin - var_bunker_end) * 600);
var_i = var_i + 1;
end
accepted_ores_beginning_month = accepted_ores_beginning_month + var_correction_shop1_bm;
processing_on_bunker_bm = processing_on_bunker_bm + var_correction_shop1_bm;
accepted_ores_begin_month_diff = accepted_ores_beginning_month - plan_processing_ore_begin_month;
processing_on_bunker_bm_diff = processing_on_bunker_bm - plan_processing_ore_begin_month;
SELECT sum(dry_day), SUM(time_work_day), COUNT(class_mill) FROM mill_day_dryore_work
WHERE date_reg = :Param_date and class_mill = 1
INTO :processing_scales_shop2_day, :new_temp1, :new_temp2;
processing_scales_shop2_diff_d = processing_scales_shop2_day - var_plan_day;
IF (:new_temp1 <> 0) THEN
BEGIN
hourly_average_processing_day = processing_scales_shop2_day / new_temp1;
END
ELSE
begin
hourly_average_processing_day = 0;
END
SELECT SUM(dry_day), SUM(time_work_day), COUNT(class_mill) FROM mill_day_dryore_work
WHERE (date_reg BETWEEN :var_begin_date AND :Param_date) and (class_mill = 1)
INTO :processing_scales_shop2_bm, :new_temp1, :new_temp2;
processing_scales_shop2_bm = processing_scales_shop2_bm + var_correction_shop2_bm;
processing_scales_shop2_diff_bm = processing_scales_shop2_bm - plan_processing_ore_begin_month;
IF ((:new_temp1 IS NOT NULL) and (:new_temp1 <> 0)) THEN
BEGIN
hourly_average_processing_bm = processing_scales_shop2_bm / new_temp1;
END
ELSE
begin
hourly_average_processing_bm = 0;
END
SELECT avg(CEH2_SMENA.SAND)
FROM CEH2_SMENA
WHERE DAY_REG = :Param_date
INTO :sand_day;
SELECT avg(CEH2_SMENA.SAND)
FROM CEH2_SMENA
WHERE DAY_REG BETWEEN :var_begin_date AND :Param_date
INTO :sand_bm;
[b] SELECT avg(REPORT.PH)
FROM REPORT
WHERE
(REPORT.DAY_OF = :PARAM_DATE)
INTO :ph_pulp;[/b]
SELECT TJ_THICKENED_UNDERFLOW, TJ_TAIL_UNDERFLOW, SPECIFIC_THICKENED, P137_6,
P138_12, AU_IN_SOLID_PHASE, CLASS_074, E1, E12, E25, AU_FEEDING_SORBTION,
PITCH_SORBTION, PITCH_REGENERATION, EDUCTION_REGEN, COMMODITY_REGEN,
WEIGHT_WET, AMOUN_THICKENER
FROM PDO_DAY
WHERE
(PDO_DAY.DATE_REG = :param_date)
INTO :TJ_THICKENED_UNDERFLOW, :TJ_TAIL_UNDERFLOW, :SPECIFIC_THICKENED, :P137_6,
:P138_12, :AU_IN_SOLID_PHASE, :CLASS_074, :E1, :E12, :E25, :AU_FEEDING_SORBTION,
:PITCH_SORBTION, :PITCH_REGENERATION, :EDUCTION_REGEN, :COMMODITY_REGEN,
:WEIGHT_WET, :AMOUN_THICKENER;
SELECT avg(TJ_THICKENED_UNDERFLOW), avg(TJ_TAIL_UNDERFLOW), Sum(P138_12),
avg(AU_IN_SOLID_PHASE), avg(E1), avg(E12), avg(E25)
FROM PDO_DAY
WHERE
(PDO_DAY.DATE_REG BETWEEN :var_begin_date and :param_date)
INTO :tj_thickened_underflow_bm, :tj_tail_underflow_bm, :p138_12_bm,
:au_in_solid_phase_bm, :e1_bm, :e12_bm, :e25_bm;
EXECUTE PROCEDURE pulp_exhaust_day(:param_date)
RETURNING_VALUES :pulp_exhaust_day;
EXECUTE PROCEDURE pulp_exhaust_period(:var_begin_date, :param_date)
RETURNING_VALUES :pulp_exhaust_BM;
SUSPEND;
end
^
SET TERM ; ^
GRANT EXECUTE ON PROCEDURE BEGIN_MONTH TO PROCEDURE TABLE_WORK_FACTORY_2;
GRANT SELECT ON PLAN_PROCESSING TO PROCEDURE TABLE_WORK_FACTORY_2;
GRANT SELECT ON DAY_CONFIG TO PROCEDURE TABLE_WORK_FACTORY_2;
GRANT EXECUTE ON PROCEDURE SHOP1_DAY_DRY_BUNKER TO PROCEDURE TABLE_WORK_FACTORY_2;
GRANT SELECT ON MILL_DAY_DRYORE_WORK TO PROCEDURE TABLE_WORK_FACTORY_2;
GRANT SELECT ON CEH2_SMENA TO PROCEDURE TABLE_WORK_FACTORY_2;
GRANT SELECT ON REPORT TO PROCEDURE TABLE_WORK_FACTORY_2;
GRANT SELECT ON PDO_DAY TO PROCEDURE TABLE_WORK_FACTORY_2;
GRANT EXECUTE ON PROCEDURE PULP_EXHAUST_DAY TO PROCEDURE TABLE_WORK_FACTORY_2;
GRANT EXECUTE ON PROCEDURE PULP_EXHAUST_PERIOD TO PROCEDURE TABLE_WORK_FACTORY_2;
GRANT EXECUTE ON PROCEDURE TABLE_WORK_FACTORY_2 TO DEVELOPER;
GRANT EXECUTE ON PROCEDURE TABLE_WORK_FACTORY_2 TO SYSDBA;
[b]GRANT EXECUTE ON PROCEDURE TABLE_WORK_FACTORY_2 TO PDO_GROUP;[/b]
Может и так. А я подумал что не написал слово procedure в гранте ей прав на таблицы 
