iBase.ru Forum

Код: Выделить всё

FOR SELECT Field1 FROM Table
  WHERE Field1 = 10
  FOR UPDATE
  INTO :VAR AS CURSOR TTT
  DO BEGIN
    UPDATE Table SET
      Field1 = Field1 + 100
    WHERE CURRENT OF TTT;
  END

Код: Выделить всё

CREATE OR ALTER PROCEDURE SET_TEMP_ENTRY(
  DT_ID INTEGER,
  KT_ID INTEGER,
  KOL NUMERIC(18, 3),
  SUMMA NUMERIC(18, 0),
  SKD1 INTEGER,
  SKD2 INTEGER,
  SKD3 INTEGER,
  SKK1 INTEGER,
  SKK2 INTEGER,
  SKK3 INTEGER,
  CAPACITY NUMERIC(18, 3))
AS
DECLARE VARIABLE FLAG INTEGER;
DECLARE VARIABLE R_ID INTEGER;
BEGIN
  FLAG = 0;
  FOR SELECT DISTINCT TEMP_ENTRY.DT_ID FROM TEMP_ENTRY
  WHERE
    (TEMP_ENTRY.DT_ID = :DT_ID) AND
    (TEMP_ENTRY.KT_ID = :KT_ID) AND
    (TEMP_ENTRY.SKD1 = :SKD1) AND
    (TEMP_ENTRY.SKD2 = :SKD2) AND
    (TEMP_ENTRY.SKD3 = :SKD3) AND
    (TEMP_ENTRY.SKK1 = :SKK1) AND
    (TEMP_ENTRY.SKK2 = :SKK2) AND
    (TEMP_ENTRY.SKK3 = :SKK3)
  INTO :R_ID AS CURSOR TTT
  DO BEGIN
    UPDATE TEMP_ENTRY SET
      TEMP_ENTRY.SUMMA = TEMP_ENTRY.SUMMA + :SUMMA,
      TEMP_ENTRY.KOL = TEMP_ENTRY.KOL + :KOL
    WHERE CURRENT OF TTT;
    FLAG = 1;
  END
  IF (:FLAG = 0) THEN BEGIN
    INSERT INTO TEMP_ENTRY (
      DT_ID,
      KT_ID,
      KOL,
      SUMMA,
      SKD1,
      SKD2,
      SKD3,
      SKK1,
      SKK2,
      SKK3,
      CAPACITY)
    VALUES (
      :DT_ID,
      :KT_ID,
      :KOL,
      :SUMMA,
      :SKD1,
      :SKD2,
      :SKD3,
      :SKK1,
      :SKK2,
      :SKK3,
      :CAPACITY);
  END
END

Код: Выделить всё

CREATE TABLE TEMP_ENTRY (
  DT_ID DOC_ID DEFAULT 0 NOT NULL,
  KT_ID DOC_ID DEFAULT 0 NOT NULL,
  KOL COUNT_6 NOT NULL,
  SUMMA COST DEFAULT 0 NOT NULL,
  SKD1 DOC_ID DEFAULT 0 NOT NULL,
  SKD2 DOC_ID DEFAULT 0 NOT NULL,
  SKD3 DOC_ID DEFAULT 0 NOT NULL,
  SKK1 DOC_ID DEFAULT 0 NOT NULL,
  SKK2 DOC_ID DEFAULT 0 NOT NULL,
  SKK3 DOC_ID DEFAULT 0 NOT NULL,
  CAPACITY COUNT DEFAULT 0 NOT NULL);


CREATE INDEX IDX_TEMP_ENTRY ON TEMP_ENTRY(DT_ID,KT_ID);

CREATE INDEX IDX_TEMP_ENTRY1 ON TEMP_ENTRY(DT_ID);

CREATE INDEX IDX_TEMP_ENTRY10 ON TEMP_ENTRY(SKD1,SKD2);

CREATE INDEX IDX_TEMP_ENTRY11 ON TEMP_ENTRY(SKK1,SKK2);

CREATE INDEX IDX_TEMP_ENTRY12 ON TEMP_ENTRY(SKD1,SKD2,SKK1,SKK2);

CREATE INDEX IDX_TEMP_ENTRY2 ON TEMP_ENTRY(KT_ID);

CREATE INDEX IDX_TEMP_ENTRY3 ON TEMP_ENTRY(SKD1);

CREATE INDEX IDX_TEMP_ENTRY4 ON TEMP_ENTRY(SKD2);

CREATE INDEX IDX_TEMP_ENTRY5 ON TEMP_ENTRY(SKD3);

CREATE INDEX IDX_TEMP_ENTRY6 ON TEMP_ENTRY(SKK1);

CREATE INDEX IDX_TEMP_ENTRY7 ON TEMP_ENTRY(SKK2);

CREATE INDEX IDX_TEMP_ENTRY8 ON TEMP_ENTRY(SKK3);

CREATE INDEX IDX_TEMP_ENTRY9 ON TEMP_ENTRY(SKD1,SKK1);