CREATE EXCEPTION COUNTRY_EXIST '';
SET TERM ^;
CREATE PROCEDURE ADD_COUNTRY (
    ACountryName COUNTRYNAME,
    ACurrency VARCHAR(10) )
AS
BEGIN
  INSERT INTO country (country, currency)
  VALUES (:ACountryName, :ACurrency);

  WHEN SQLCODE -803 DO
    EXCEPTION COUNTRY_EXIST 'Un tel pays a déjà été ajouté !';
END^
SET TERM ^;

CREATE PROCEDURE ADD_COUNTRY (
    ACountryName COUNTRYNAME,
    ACurrency VARCHAR(10) )
AS
BEGIN
  INSERT INTO country (country,
                       currency)
  VALUES (:ACountryName,
          :ACurrency);
  WHEN ANY DO
  BEGIN
    -- Enregistrement de l'erreur
    IN AUTONOMOUS TRANSACTION DO
      INSERT INTO ERROR_LOG (PSQL_MODULE,
                             ERROR_TEXT,
                             EXCEPTION_NAME,
                             GDS_CODE,
                             SQL_CODE,
                             SQL_STATE)
      VALUES ('ADD_COUNTRY',
              RDB$ERROR(MESSAGE), -- texte du message d'erreur
              RDB$ERROR(EXCEPTION), -- le nom de l'exception de l'utilisateur
              GDSCODE,
              SQLCODE,
              SQLSTATE
      );
    -- Relancer l'erreur
    EXCEPTION;
  END
END

...
WHEN GDSCODE GRANT_OBJ_NOTFOUND,
	   GDSCODE GRANT_FLD_NOTFOUND,
	   GDSCODE GRANT_NOPRIV,
	   GDSCODE GRANT_NOPRIV_ON_BASE
DO
BEGIN
	EXECUTE PROCEDURE LOG_GRANT_ERROR(GDSCODE);
	EXIT;
END
...

EXECUTE BLOCK
AS
    DECLARE VARIABLE I INT;
BEGIN
  BEGIN
    I = 1 / 0;
    WHEN SQLSTATE '22003' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Numeric value out of range.';
    WHEN SQLSTATE '22012' DO
      EXCEPTION E_CUSTOM_EXCEPTION 'Division by zero.';
    WHEN SQLSTATE '23000' DO
      EXCEPTION E_CUSTOM_EXCEPTION
        'Integrity constraint violation.';
  END
END

CREATE OR ALTER TRIGGER TBU_STOCK
BEFORE UPDATE ON STOCK
AS
  -- Annonce d'un curseur nommé
  DECLARE C_COUNTRY CURSOR FOR (
    SELECT
      COUNTRY,
      CAPITAL
    FROM COUNTRY
  );
BEGIN
  /* Instructions PSQL */
END

EXECUTE BLOCK
RETURNS (
  N INT,
  RNAME CHAR(63))
AS
  -- Annonce d'un curseur défilant
  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
  /* Instructions PSQL */
END

SET TERM ^;
--
-- Sous-procédures dans EXECUTE BLOCK
--
EXECUTE BLOCK
RETURNS (
    name VARCHAR(63))
AS
  -- Sous-procédure retournant une liste de tableaux
  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

  -- Sous-procédure retournant une liste de vues
  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
  -- Pré-annonce P1.
  DECLARE PROCEDURE p1(i INTEGER = 1) RETURNS (o INTEGER);

  -- Pré-annonce P2.
  DECLARE PROCEDURE p2(i INTEGER) RETURNS (o INTEGER);

  -- Mise en œuvre de P1. Vous ne devez pas remplacer la valeur par défaut du paramètre
  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!

--
-- Une sous-fonction dans une fonction stockée
--
CREATE OR ALTER FUNCTION FUNC1 (n1 INTEGER, n2 INTEGER)
  RETURNS INTEGER
AS
  -- Sous-fonction
  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
  -- Sous-programme-fonction récursif sans déclaration préalable.
  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