A Practical Guide to Exception Handling and Flow Control in GBase 8a Stored Procedures

Stored procedures are the backbone of business logic encapsulation in GBase 8a. Effective exception handling and flow control not only prevent unexpected crashes but also make your code far more maintainable. This guide walks through diagnostics commands, exception handlers, loops, branches, and cursors in GBASE's China‑domestically developed MPP cluster database.

Diagnostics Commands

GBase 8a provides the GET DIAGNOSTICS statement to retrieve execution metadata without relying on implicit variables.

Number of Errors / Warnings

GET DIAGNOSTICS @a = NUMBER;

Stores the total count of warnings and errors for the current session into @a.

Rows Affected by the Last DML

GET DIAGNOSTICS @a = ROW_COUNT;

Reflects only the last INSERT, UPDATE, or DELETE operation.

Detailed Error Information

Capture the error number, SQLSTATE, and message text of the most recent error with CONDITION 1:

GET DIAGNOSTICS CONDITION 1 @errno = GBASE_ERRNO,
                       @state = RETURNED_SQLSTATE,
                       @msg   = MESSAGE_TEXT;

Exception Handlers

Declare handlers at the beginning of a procedure to intercept specific conditions and prevent uncontrolled termination.

DECLARE done INT DEFAULT 0;
DECLARE errno INT;
DECLARE sstate VARCHAR(10922);
DECLARE message VARCHAR(10922);

-- Set a flag when no data is found
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

-- Capture full error details on any SQL exception
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    GET DIAGNOSTICS CONDITION 1
        errno = GBASE_ERRNO,
        sstate = RETURNED_SQLSTATE,
        message = MESSAGE_TEXT;
END;

Note: A CONTINUE HANDLER allows the procedure to continue execution after the condition has been handled.

Flow Control in Action

The following example integrates multiple control structures into a single procedure p3. Required variables have been explicitly declared.

DROP PROCEDURE IF EXISTS p3;
DELIMITER //
CREATE PROCEDURE p3()
BEGIN
    DECLARE p1 INT DEFAULT 20;
    DECLARE v_a INT;
    DECLARE done INT DEFAULT 0;
    DECLARE cur_1 CURSOR FOR SELECT DISTINCT a FROM t1 ORDER BY a DESC;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    -- WHILE loop
    SET @a = 10;
    WHILE @a > 1 DO
        SELECT @a;
        SET @a = @a - 1;
    END WHILE;

    -- IF / ELSEIF / ELSE
    IF @a <= 3 THEN
        SELECT 'Grade A';
    ELSEIF @a <= 7 THEN
        SELECT 'Grade B';
    ELSE
        SELECT 'Grade C';
    END IF;

    -- LOOP with ITERATE and LEAVE
    SET p1 = 0;
    label1: LOOP
        SET p1 = p1 + 1;
        IF p1 < 10 THEN
            SELECT p1;
            ITERATE label1;   -- skip remaining statements, continue loop
        END IF;
        LEAVE label1;         -- exit loop when condition is met
    END LOOP label1;
    SET @x = p1;
    SELECT @x;

    -- CASE WHEN inside a WHILE
    WHILE p1 > 1 DO
        SELECT CASE WHEN p1 >= 80 THEN CONCAT(p1, ' Excellent')
                    WHEN p1 >= 60 THEN CONCAT(p1, ' Pass')
                    ELSE CONCAT(p1, ' Fail')
               END;
        SET p1 = p1 - 10;
    END WHILE;

    -- REPEAT loop
    SET @a = 0;
    REPEAT
        SET @a = @a + 1;
        SELECT @a;
    UNTIL @a = 5 END REPEAT;

    -- Static cursor
    OPEN cur_1;
    REPEAT
        FETCH cur_1 INTO v_a;
        IF NOT done THEN
            SELECT v_a;
        END IF;
    UNTIL done END REPEAT;
    CLOSE cur_1;

END //
DELIMITER ;

Cursor Extra: Dynamic Cursors

While the static cursor is tied to a fixed query, dynamic cursors let you supply the SQL text at runtime—ideal for parameter‑driven reporting. Their usage mirrors static cursors, with additional steps to prepare and open the statement dynamically.

Whether you're validating ETL results or generating complex reports, combining these exception handling and flow control techniques will make your GBase 8a stored procedures both robust and easy to maintain in any gbase database environment.