在MySQL存储程序里使用SQL

1，使用非SELECT语句
UPDATE/INSERT/SET、DDL、DML等非查询语句可以随意在存储程序里执行
[code]
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;
[/code]

2，使用INTO
如果SELECT语句只返回一行记录，则可以使用INTO语句来把结果存入一个变量
如果SELECT语句返回多行记录，则使用INTO会出现运行时错误
[code]
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;
[/code]

3，创建和使用Cursor
可以使用Cursor来处理SELECT语句返回多好记录时的场景
Cursor提供对查询结果集的访问，并且可以循环结果集的每一行，然后每行单独处理
[code]
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;
[/code]
存在的第一个问题：变量声明必须在CURSOR声明之前
存在的第二个问题：上面的程序在FETCH结果集遇到最后一条之后会报错"no data to fetch" error (MySQL error 1329; SQLSTATE 02000)
为了避免第二个问题，我们需要声明一个HANDLER
[code]
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;
[/code]

4，使用UNBOUNED SELECT语句返回数据给调用者
sp:
[code]
CREATE PROCEDCURE sp_get_all_users()
BEGIN
SELECT user_name, age
FROM users;
END;
[/code]
Java客户端:
[code]
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();
}
[/code]

5，使用Prepared Statements处理动态SQL
MySQL支持server-side prepared statements，PREPARE创建，EXECUTE执行，DEALLOCATE销毁:
[code]
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)
[/code]
这样一来就可以在sp里做一些手脚，让sp更flexible:
[code]
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;
[/code]

6，声明HANDLER的语法
[code]
DECLARE {CONTINUE | EXIT} HANDLER FOR
{SQLSTATE sqlstate_code | MySQL error code | condition_name}
stored_program_statement
[/code]