EXECUTE BLOCK
RETURNS (
SCRIPT BLOB SUB_TYPE TEXT)
AS
DECLARE VARIABLE FIELDS VARCHAR(8191);
DECLARE VARIABLE FIELD_NAME TYPE OF RDB$FIELD_NAME;
DECLARE VARIABLE RELATION RDB$RELATION_NAME;
DECLARE VARIABLE SOURCE TYPE OF COLUMN RDB$RELATIONS.RDB$VIEW_SOURCE;
-- named cursor
DECLARE VARIABLE CUR_R CURSOR FOR (
SELECT
RDB$RELATION_NAME,
RDB$VIEW_SOURCE
FROM
RDB$RELATIONS
WHERE
RDB$VIEW_SOURCE IS NOT NULL);
-- named cursor with local variable
DECLARE CUR_F CURSOR FOR (
SELECT
RDB$FIELD_NAME
FROM
RDB$RELATION_FIELDS
WHERE
-- Important! The variable has to be declared earlier
RDB$RELATION_NAME = :RELATION);
BEGIN
OPEN CUR_R;
WHILE (1 = 1) DO
BEGIN
FETCH CUR_R
INTO :RELATION, :SOURCE;
IF (ROW_COUNT = 0) THEN
LEAVE;
FIELDS = NULL;
-- The CUR_F cursor will use
-- variable value of RELATION initialized above
OPEN CUR_F;
WHILE (1 = 1) DO
BEGIN
FETCH CUR_F
INTO :FIELD_NAME;
IF (ROW_COUNT = 0) THEN
LEAVE;
IF (FIELDS IS NULL) THEN
FIELDS = TRIM(FIELD_NAME);
ELSE
FIELDS = FIELDS || ', ' || TRIM(FIELD_NAME);
END
CLOSE CUR_F;
SCRIPT = 'CREATE VIEW ' || RELATION;
IF (FIELDS IS NOT NULL) THEN
SCRIPT = SCRIPT || ' (' || FIELDS || ')';
SCRIPT = SCRIPT || ' AS ' || ASCII_CHAR(13);
SCRIPT = SCRIPT || SOURCE;
SUSPEND;
END
CLOSE CUR_R;
END