Master Looping and Variables in MySQL & PostgreSQL Stored Procedures

Why learn variables + loops?

In real business scenarios you often need batch insert/update/delete, row‑by‑row processing of a table, or dynamic SQL generation and task execution, which all rely on variables and loops inside stored procedures.

Batch insert/update/delete

Row‑by‑row traversal of a table

Dynamic SQL, looped task execution

Variables / Loops (e.g., WHILE , FOR )

Goal of this article

Traverse the users table, print people younger than 30, and send them a "system welcome message".

MySQL version (using WHILE )

1. Table structure preparation

CREATE DATABASE IF NOT EXISTS test_sp;
USE test_sp;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    age INT
);

INSERT INTO users (username, age) VALUES
    ('Alice', 25),
    ('Bob', 30),
    ('Charlie', 22),
    ('Daisy', 28),
    ('Edward', 40);

2. Create procedure (loop + variables)

DELIMITER $$
CREATE PROCEDURE loop_young_users()
BEGIN
    DECLARE finished INT DEFAULT 0;
    DECLARE uid INT;
    DECLARE uname VARCHAR(50);
    DECLARE uage INT;
    DECLARE user_cursor CURSOR FOR
        SELECT id, username, age FROM users WHERE age < 30;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

    OPEN user_cursor;
    read_loop: LOOP
        FETCH user_cursor INTO uid, uname, uage;
        IF finished = 1 THEN
            LEAVE read_loop;
        END IF;
        SELECT CONCAT('已向 ', uname, '（年龄：', uage, '）发送系统欢迎消息') AS info;
    END LOOP;
    CLOSE user_cursor;
END $$
DELIMITER ;

3. Call stored procedure

CALL loop_young_users();

Sample output:

| info                                 |
+--------------------------------------+ 
| 已向 Alice（年龄：25）发送系统欢迎消息 |
| 已向 Charlie（年龄：22）发送系统欢迎消息 |
| 已向 Daisy（年龄：28）发送系统欢迎消息 |

PostgreSQL version (using FOR loop)

1. Same table structure

CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    age INT
);

INSERT INTO users (username, age) VALUES
    ('Alice', 25),
    ('Bob', 30),
    ('Charlie', 22),
    ('Daisy', 28),
    ('Edward', 40);

2. Create procedure (PL/pgSQL syntax)

CREATE OR REPLACE PROCEDURE loop_young_users()
LANGUAGE plpgsql
AS $$
DECLARE rec RECORD;
BEGIN
    FOR rec IN SELECT id, username, age FROM users WHERE age < 30 LOOP
        RAISE NOTICE '已向 %（年龄：%）发送系统欢迎消息', rec.username, rec.age;
    END LOOP;
END;
$$;

3. Execute procedure

CALL loop_young_users();

Output (PostgreSQL NOTICE):

NOTICE:  已向 Alice（年龄：25）发送系统欢迎消息
NOTICE:  已向 Charlie（年龄：22）发送系统欢迎消息
NOTICE:  已向 Daisy（年龄：28）发送系统欢迎消息

MySQL vs PostgreSQL syntax comparison

Key differences include procedure creation syntax, loop constructs (explicit cursor with

LOOP

in MySQL vs simple

FOR

in PostgreSQL), and output methods (

SELECT

vs

RAISE NOTICE

).