mysql 5.0存储过程学习总结

以下功能主要供学习。高并发场景不要使用mysql特性（触发器,存储过程,外键,自定义函数等）使用会降低系统的并发性，数据库升级困难，迁移困难诸多问题。

存储过程
缺点：
1、可移植性是存储过程和触发器最大的缺点。
2、占用服务器端太多的资源，对服务器造成很大的压力。
3、不能做DDL。
4、触发器排错困难，而且数据容易造成不一致，后期维护不方便。
优点：
1、预编译，已优化，效率较高。避免了SQL语句在网络传输然后再解释的低效率。
2、存储过程可以重复使用，减少开发人员的工作量。
3、业务逻辑封装性好，修改方便。数据库的优化好处理，如果发现速度慢了，可以很容易在数据库层面找到究竟是那个存储过程的第几行的那个sql写的不够好，我们可以对其优化，提高效率
4、安全。不会有SQL语句注入问题存在。

5.学习成本，存储过程掌握起来，简单容易单一；但是如果通过各种前台语言就麻烦了，有的项目用java,c#,，有用php...;而且还有那么多框架；项目实施到一半，经常换人；以后维护，也换人；存储过程人人都会；复杂的逻辑在存储过程里实现维护成本低

 

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

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

SELECT LAST_INSERT_ID() into _ticketid;

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;

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