Db2: Stored procedures examples in SQL

Simple examples of SQL stored procedures in Db2.

Hello world

A trivial hello_world() procedure:

CREATE PROCEDURE hello_world()
    LANGUAGE SQL
BEGIN
    SELECT 'Hello, world!' FROM sysibm.sysdummy1;
END%

An IF example

CREATE PROCEDURE maybe_hello_world(IN in_really BOOLEAN)
    LANGUAGE SQL
BEGIN
    IF (in_really = TRUE) THEN
        SELECT 'Hello, world!' AS msg FROM sysibm.sysdummy1;
    ELSE
        SELECT '' AS msg FROM sysibm.sysdummy1;
    END IF;
END%

A WHILE loop example

Let’s write a simple stored procedure that inserts the current timestamp and a random value into a table multiple times, depending on an argument.

CREATE PROCEDURE loop_demo(IN in_iterations INTEGER)
    LANGUAGE SQL
BEGIN
    DECLARE v_now TIMESTAMP DEFAULT CURRENT TIMESTAMP;
    WHILE (in_iterations > 0) DO
        SET in_iterations = in_iterations - 1;
        INSERT INTO mytab (when, num) VALUES (v_now, RAND(1));
    END WHILE;
END%

A cursor example

Let’s run a query and loop over its results using a cursor.

CREATE PROCEDURE hello_cursor()
    LANGUAGE SQL
BEGIN
    DECLARE v_eof BOOLEAN DEFAULT FALSE;
    DECLARE v_username VARCHAR(50);
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE c1 CURSOR FOR
        SELECT username FROM users
    ;
    DECLARE CONTINUE HANDLER FOR not_found                    (2)
           SET v_eof = TRUE;
    OPEN cur;
    fetch_loop: WHILE (eof = FALSE) DO
        FETCH cur INTO v_username;
        IF v_eof = TRUE THEN
            LEAVE fetch_loop;                                 (3)
        END IF;
        -- do something with the current row
    END LOOP;
    CLOSE cur;
END

Reference

From Db2 documentation: