在MySQL存储程序里使用SQL

1，使用非SELECT语句
UPDATE/INSERT/SET、DDL、DML等非查询语句可以随意在存储程序里执行

CREATE PROCEDURE simple_sqls()
BEGIN
    DECLARE i INT DEFAULT 1;

    /* Example of a utility statement */
    DROP TABLE IF EXISTS test_table
    CREATE TABLE test_table
        (id INT PRIMARY KEY, some_data VARCHAR(30)) ENGINE=innodb;

    /* Example of an INSERT using a procedure variable */
    WHILE (i<=10) DO
        INSERT INTO TEST_TABLE VALUES(i, CONCAT("record ", i));
        SET i=i+1;
    END WHILE

    /* Example of an UPDATE using procedure variabless */
    SET i =5;
    UPDATE test_table
        SET some_data=CONCAT("I updated row ", i)
    WHERE id=i;

    /* DELETE with a procedure variable */
    DELETE FROM test_table
        WHERE id>i;

END;

2，使用INTO
如果SELECT语句只返回一行记录，则可以使用INTO语句来把结果存入一个变量
如果SELECT语句返回多行记录，则使用INTO会出现运行时错误

CREATE PROCEDURE get_customer_details(in_customer_id INT)
BEGIN
    DECLARE l_customer_name     VARCHAR(30);
    DECLARE l_contact_surname   VARCHAR(30);
    DECLARE l_contact_firstname VARCHAR(30);

    SELECT customer_name, contact_surname, contact_firstname
        INTO l_customer_name, l_contact_surname, contact_firstname
        FROM customers
        WHERE customer_id=in_customer_id;

    /* Do something with the customer record */
END;

3，创建和使用Cursor
可以使用Cursor来处理SELECT语句返回多好记录时的场景
Cursor提供对查询结果集的访问，并且可以循环结果集的每一行，然后每行单独处理

DECLARE l_dept_id BIGINT;
DECLARE c_dept CURSOR FOR
    SELECT department_id FROM departments;

OPEN c_dept;
dept_cursor: LOOP
    FETCH c_dept INTO l_dept_id;
END LOOP dept_cursor;
CLOSE c_dept;

存在的第一个问题：变量声明必须在CURSOR声明之前
存在的第二个问题：上面的程序在FETCH结果集遇到最后一条之后会报错"no data to fetch" error (MySQL error 1329; SQLSTATE 02000)
为了避免第二个问题，我们需要声明一个HANDLER

DECLARE l_dept_id BIGINT;
DECLARE l_last_row_fetched INT;
DECLARE c_dept CURSOR FOR
    SELECT department_id FROM departments;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;

SET l_last_row_fetched=0;
OPEN c_dept;
dept_cursor: LOOP
    FETCH c_dept INTO l_dept_id;
    IF l_last_row_fetched=1 THEN
        LEAVE dept_cursor;
    END IF;
END LOOP dept_cursor;
CLOSE c_dept;
SET l_last_row_fetched=0;

4，使用UNBOUNED SELECT语句返回数据给调用者
sp:

CREATE PROCEDCURE sp_get_all_users()
BEGIN
    SELECT user_name, age
        FROM users;
END;

Java客户端:

private void getAllUsers(Connection c) throws SQLException {
    CallableStatement s = c.prepareCall("{CALL sp_get_all_users()}");
    s.execute();
    ResultSet rs = s.getResultSet();
    while(rs.next()) {
        System.out.println(rs.getString("user_name"));
    }
    rs.close();
    s.close();
}

5，使用Prepared Statements处理动态SQL
MySQL支持server-side prepared statements，PREPARE创建，EXECUTE执行，DEALLOCATE销毁:

mysql> PREPARE prod_insert_stmt FROM "INSERT INTO product_codes VALUES(?,?)";
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql>
mysql> SET @code='QB';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @name='MySQL Query Browser';
Query OK, 0 rows affected (0.00 sec)

mysql> EXECUTE prod_insert_stmt USING @code,@name;
Query OK, 1 row affected (0.00 sec)

mysql> SET @code='AD';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @name='MySQL Administrator';
Query OK, 0 rows affected (0.02 sec)

mysql> EXECUTE prod_insert_stmt USING @code,@name;
Query OK, 1 row affected (0.00 sec)

mysql> DEALLOCATE PREPARE prod_insert_stmt;
Query OK, 0 rows affected (0.00 sec)

这样一来就可以在sp里做一些手脚，让sp更flexible:

CREATE PROCEDURE set_col_value
    (in_table     VARCHAR(128),
     in_column    VARCHAR(128),
     in_new_value VARCHAR(1000),
     in_where     VARCHAR(4000))

BEGIN
    DECLARE l_sql VARCHAR(4000);
    SET l_slq=CONCAT_ws(' ',
                 'UPDATE', in_table,
                 'SET', in_column, '=', in_new_value,
                 'WHERE', in_where);
    SET @sql=l_sql;
    PREPARE s1 FROM @sql;
    EXECUTE s1;
    DEALLOCATE PREPARE s1;
END;

6，声明HANDLER的语法

DECLARE {CONTINUE | EXIT} HANDLER FOR
    {SQLSTATE sqlstate_code | MySQL error code | condition_name}
    stored_program_statement