Особенности Использования Курсора

CREATE OR ALTER TRIGGER TBU_STOCK
BEFORE UPDATE ON STOCK
AS
  -- Объявление именованного курсора
  DECLARE C_COUNTRY CURSOR FOR (
    SELECT
      COUNTRY,
      CAPITAL
    FROM COUNTRY
  );
BEGIN
  /* Операторы PSQL */
END

EXECUTE BLOCK
RETURNS (
  N INT,
  RNAME CHAR(63))
AS
  -- Объявление прокручиваемого курсора
  DECLARE C SCROLL CURSOR FOR (
    SELECT
      ROW_NUMBER() OVER(ORDER BY RDB$RELATION_NAME) AS N,
      RDB$RELATION_NAME
    FROM RDB$RELATIONS
    ORDER BY RDB$RELATION_NAME);
BEGIN
  /* Операторы PSQL */
END

SET TERM ^;
--
-- Подпроцедуры в EXECUTE BLOCK
--
EXECUTE BLOCK
RETURNS (
    name VARCHAR(63))
AS
  -- Подпроцедура, возвращающая список таблиц
  DECLARE PROCEDURE get_tables
  RETURNS(table_name VARCHAR(63))
  AS
  BEGIN
    FOR
      SELECT
        rdb$relation_name
      FROM
        rdb$relations
      WHERE
        rdb$view_blr IS NULL
      INTO table_name
    DO SUSPEND;
  END

  -- Подпроцедура, возвращающая список представлений
  DECLARE PROCEDURE get_views
  RETURNS(view_name  VARCHAR(63))
  AS
  BEGIN
    FOR
      SELECT
        rdb$relation_name
      FROM
        rdb$relations
      WHERE
        rdb$view_blr IS NOT NULL
      INTO view_name
    DO SUSPEND;
  END

BEGIN
  FOR
    SELECT
        table_name
    FROM
        get_tables
    UNION ALL
    SELECT
        view_name
    FROM
        get_views
    INTO name
  DO SUSPEND;
END^

EXECUTE BLOCK RETURNS (o INTEGER)
AS
  -- Предварительное объявление P1.
  DECLARE PROCEDURE p1(i INTEGER = 1) RETURNS (o INTEGER);

  -- Предварительное объявление P2.
  DECLARE PROCEDURE p2(i INTEGER) RETURNS (o INTEGER);

  -- Реализация P1. Вы не должны переопределять значение параметра по умолчанию
  DECLARE PROCEDURE p1(i INTEGER) RETURNS (o INTEGER)
  AS
  BEGIN
    EXECUTE PROCEDURE p2(i) RETURNING_VALUES o;
  END

  DECLARE PROCEDURE p2(i INTEGER) RETURNS (o INTEGER)
  AS
  BEGIN
    o = i;
  END
BEGIN
  EXECUTE PROCEDURE p1 RETURNING_VALUES o;
  SUSPEND;
END!

--
-- Подфункция внутри хранимой функции
--
CREATE OR ALTER FUNCTION FUNC1 (n1 INTEGER, n2 INTEGER)
  RETURNS INTEGER
AS
  -- Подфункция
  DECLARE FUNCTION SUBFUNC (n1 INTEGER, n2 INTEGER)
    RETURNS INTEGER
  AS
  BEGIN
    RETURN n1 + n2;
  END

BEGIN
  RETURN SUBFUNC(n1, n2);
END ^

EXECUTE BLOCK RETURNS (i INTEGER, o INTEGER)
AS
  -- Рекусривная подпрограмма-функция без предварительного объявления.
  DECLARE FUNCTION fibonacci(n INTEGER) RETURNS INTEGER
  AS
  BEGIN
    IF (n = 0 OR n = 1) THEN
      RETURN n;
    ELSE
      RETURN fibonacci(n - 1) + fibonacci(n - 2);
  END
BEGIN
  i = 0;

  WHILE (i < 10)
  DO
  BEGIN
    o = fibonacci(i);
    SUSPEND;
    i = i + 1;
  END
END!

SET TERM ^;
CREATE OR ALTER PROCEDURE DEPT_BUDGET (
    DNO CHAR(3))
RETURNS (
    TOT DECIMAL(12,2))
AS
    DECLARE VARIABLE SUMB DECIMAL(12,2);
    DECLARE VARIABLE RDNO CHAR(3);
    DECLARE VARIABLE CNT  INTEGER;
BEGIN
  TOT = 0;

  SELECT
      BUDGET
  FROM
      DEPARTMENT
  WHERE DEPT_NO = :DNO
  INTO :TOT;

  SELECT
      COUNT(BUDGET)
  FROM
      DEPARTMENT
  WHERE HEAD_DEPT = :DNO
  INTO :CNT;

  IF (CNT = 0) THEN
    SUSPEND;

  FOR
      SELECT
          DEPT_NO
      FROM
          DEPARTMENT
      WHERE HEAD_DEPT = :DNO
      INTO :RDNO
  DO
  BEGIN
    EXECUTE PROCEDURE DEPT_BUDGET(:RDNO)
    RETURNING_VALUES :SUMB;
    TOT = TOT + SUMB;
  END

  SUSPEND;
END^
SET TERM ;^

...
IF (FIRST IS NOT NULL) THEN
  LINE2 = FIRST || ' ' || LAST;
ELSE
  LINE2 = LAST;
...

...
IF (INT_VALUE = 1) THEN
  STRING_VALUE = 'one';
ELSE IF (INT_VALUE = 2) THEN
  STRING_VALUE = 'two';
ELSE IF (INT_VALUE = 3) THEN
  STRING_VALUE = 'three';
ELSE
  STRING_VALUE = 'too much';
...

CREATE PROCEDURE SUM_INT (I INTEGER)
RETURNS (S INTEGER)
AS
BEGIN
  s = 0;
  WHILE (i > 0) DO
  BEGIN
    s = s + i;
    i = i - 1;
  END
END

EXECUTE PROCEDURE SUM_INT(4);

S
==========
10