|
안녕하세요
firebird 2.0, IBExpert 환경에서 프로시져를 만들고 있는데 해결되지 않는 점이 있어 문의 글을 남김니다.
제가 프로시져에서 할려고 하는 것은
- insert할때 작성된 트리거를 사용하여 일련번호를 부여 해줄려고 하는 것입니다.
문제는
- 트리거는 작성되어 있는데 프로져 생성간 해당 트리거를 알수 없다고 ㅡ_ㅜ
- 해당 트리거는 SET_WEATHER_RAW_SEQ 이넘입니다.
도움 요청드리겠습니다. (_ _)
---------------------------------------------------
테이블
---------------------------------------------------
CREATE TABLE WEATHER_RAW
(
WEATHER_SEQ INTEGER NOT NULL
, WEATHER_DT VARCHAR(14) NOT NULL
, WIND_DIRECTION NUMERIC(8,3) NOT NULL
, WIND_SPEED NUMERIC(8,3) NOT NULL
, TEMPERATURE NUMERIC(8,3) NOT NULL
, HUMIDITY INTEGER NOT NULL
, AIR_PRESSURE INTEGER NOT NULL
, SOLAR_RADIATION NUMERIC(8,3) NOT NULL
, SEA_TEMPERATURE NUMERIC(8,3) NOT NULL
, PRIMARY KEY (WEATHER_SEQ, WEATHER_DT)
);
-------------------------
제너레이터
-------------------------
CREATE GENERATOR WEATHER_RAW_SEQ_GEN;
SET GENERATOR WEATHER_RAW_SEQ_GEN TO 0;
-------------------------
트리거
-------------------------
CREATE TRIGGER SET_WEATHER_RAW_SEQ FOR WEATHER_RAW
BEFORE INSERT AS
BEGIN
NEW.WEATHER_SEQ = GEN_ID(WEATHER_RAW_SEQ_GEN, 1);
END;
-------------------------
프로시져
-------------------------
SET TERM ^ ;
CREATE PROCEDURE INPUT_WEATHER (
sDateTime VARCHAR(14),
fWindDirection NUMERIC(8,3),
fWindSpeed NUMERIC(8,3),
fTemperature NUMERIC(8,3),
nHumidity INTEGER,
nAirPressure INTEGER,
fSolarRadiation NUMERIC(8,3),
fSeaTemperature NUMERIC(8,3)
)
AS
DECLARE VARIABLE nRowCnt INTEGER; /* 검색된 열 */
DECLARE VARIABLE nAvgWindDirection SMALLINT;
DECLARE VARIABLE fAvgWindSpeed NUMERIC(8,3);
DECLARE VARIABLE fAvgTemperature NUMERIC(8,3);
DECLARE VARIABLE nAvgHumidity SMALLINT;
DECLARE VARIABLE nAvgAir_Pressure SMALLINT;
DECLARE VARIABLE fAvgSolar_Radiation NUMERIC(8,3);
DECLARE VARIABLE fAvgSeaTemperature NUMERIC(8,3);
BEGIN
/* 기초 테이블에 입력 */
INSERT INTO WEATHER_RAW
VALUES (SET_WEATHER_RAW_SEQ,
sDateTime,
fWindDirection,
fWindSpeed,
fTemperature,
nHumidity,
nAirPressure,
fSolarRadiation,
fSeaTemperature
);
SELECT COUNT(WEATHER_DT) FROM WEATHER_10MIN
WHERE WEATHER_DT = SUBSTR(:sDateTime, 1, 12) INTO :nRowCnt;
SELECT AVG(WIND_DIRECTION),
AVG(WIND_SPEED),
AVG(TEMPERATURE),
AVG(HUMIDITY),
AVG(AIR_PRESSURE),
AVG(SOLAR_RADIATION),
AVG(SEA_TEMPERATURE)
FROM WEATHER_RAW
WHERE WEATHER_DT BETWEEN (SUBSTR(:sDateTime, 1, 12) || '00')
AND
(SUBSTR(:sDateTime, 1, 12) || '09')
INTO :nAvgWindDirection, :fAvgWindSpeed, :fAvgTemperature,
:nAvgHumidity, :nAvgAir_Pressure, :fAvgSolar_Radiation,
:fAvgSeaTemperature;
IF (nRowCnt > 0) THEN
BEGIN
UPDATE WEATHER_10MIN
SET WIND_DIRECTION = :nAvgWindDirection,
WIND_SPEED = :fAvgWindSpeed,
TEMPERATURE = :fAvgTemperature,
HUMIDITY = :nAvgHumidity,
AIR_PRESSURE = :nAvgAir_Pressure,
SOLAR_RADIATION = :fAvgSolar_Radiation,
SEA_TEMPERATURE = :fAvgSeaTemperature
WHERE WEATHER_DT = SUBSTR(:sDateTime, 1, 12);
END
ELSE BEGIN
INSERT INTO WEATHER_10MIN
VALUES (SET_WEATHER_10MIN_SEQ,
SUBSTR(:sDateTime, 1, 12),
:nAvgWindDirection,
:fAvgWindSpeed,
:fAvgTemperature,
:nAvgHumidity,
:nAvgAir_Pressure,
:fAvgSolar_Radiation,
:fAvgSeaTemperature
);
END
SUSPEND;
END
^
SET TERM ; ^
|
