mysql 5.0存储过程学习总结

总结：

复杂的数据处理用存储过程如比较复杂的统计和汇总也要考虑。或者关联更新和删除，此时要考虑存储过程 
多条件多表联合查询,并做分页处理（上几十万条数据的分页操作）
当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；
当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；设计到项目多个表的数据维护
不建议在高并发的系统中使用存储过程

存储过程
缺点：
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会出现运行时错误

Java代码  

1. CREATE PROCEDURE grant_ticket(in_dian INT)  
2. BEGIN  
3. DECLARE _customer_id int(10);   
4. DECLARE _customer_email varchar(30);    
5. DECLARE _validaty int(10);   
6. DECLARE _id int(10);   
7. DECLARE _created int(10);   
8. DECLARE _ticketid int(10);   
9. DECLARE done int;  
10.   
11. -- 定义游标  
12. DECLARE rs_cursor CURSOR FOR   
13. SELECT customer_id,customer_email from susliks_gift_log gl left join susliks_gift sg on sg.gid = gl.gift where sg.value=in_dian;  
14. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;  
15.   
16. open rs_cursor;   
17. cursor_loop:loop  
18.    FETCH rs_cursor into _customer_id,_customer_email; -- 取数据  
19.      
20.    if done=1 then  
21.     leave cursor_loop;  
22.    end if;  
23.       
24. -- 更新表  
25.    SELECT id,validaty,created INTO _id, _validaty, _created from `ticket_type` where point=in_dian and tsn=CONCAT('201208280000',in_dian);  
26.    insert into ticket(`typeid`,`customer_id`,`email`,`number`,`point`,`created`,`grantdate`,`validaty`)values(_id,_customer_id,_customer_email,CONCAT("T903567969462505",in_dian),in_dian,UNIX_TIMESTAMP(),UNIX_TIMESTAMP(),_created+_validaty*86400);  
27.    SELECT LAST_INSERT_ID() into _ticketid;  
28.    insert into ticket_log(ticketid,status,message,created)values(_ticketid,0,'test',UNIX_TIMESTAMP());     
29.   
30. end loop cursor_loop;  
31. close rs_cursor;  
32.   
33. END;  

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

Java代码  

1. DECLARE l_dept_id BIGINT;  
2. DECLARE c_dept CURSOR FOR  
3.     SELECT department_id FROM departments;  
4.   
5. OPEN c_dept;  
6. dept_cursor: LOOP  
7.     FETCH c_dept INTO l_dept_id;  
8. END LOOP dept_cursor;  
9. CLOSE c_dept;  

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

Java代码  

1. DECLARE l_dept_id BIGINT;  
2. DECLARE l_last_row_fetched INT;  
3. DECLARE c_dept CURSOR FOR  
4.     SELECT department_id FROM departments;  
5. DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_last_row_fetched=1;  
6.   
7. SET l_last_row_fetched=0;  
8. OPEN c_dept;  
9. dept_cursor: LOOP  
10.     FETCH c_dept INTO l_dept_id;  
11.     IF l_last_row_fetched=1 THEN  
12.         LEAVE dept_cursor;  
13.     END IF;  
14. END LOOP dept_cursor;  
15. CLOSE c_dept;  
16. SET l_last_row_fetched=0;  

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

Java代码  

1. CREATE PROCEDCURE sp_get_all_users()  
2. BEGIN  
3.     SELECT user_name, age  
4.         FROM users;  
5. END;  

Java客户端:

Java代码  

1. private void getAllUsers(Connection c) throws SQLException {  
2.     CallableStatement s = c.prepareCall("{CALL sp_get_all_users()}");  
3.     s.execute();  
4.     ResultSet rs = s.getResultSet();  
5.     while(rs.next()) {  
6.         System.out.println(rs.getString("user_name"));  
7.     }  
8.     rs.close();  
9.     s.close();  
10. }  

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

Java代码  

1. mysql> PREPARE prod_insert_stmt FROM "INSERT INTO product_codes VALUES(?,?)";  
2. Query OK, 0 rows affected (0.00 sec)  
3. Statement prepared  
4.   
5. mysql>  
6. mysql> SET @code='QB';  
7. Query OK, 0 rows affected (0.00 sec)  
8.   
9. mysql> SET @name='MySQL Query Browser';  
10. Query OK, 0 rows affected (0.00 sec)  
11.   
12. mysql> EXECUTE prod_insert_stmt USING @code,@name;  
13. Query OK, 1 row affected (0.00 sec)  
14.   
15. mysql> SET @code='AD';  
16. Query OK, 0 rows affected (0.00 sec)  
17.   
18. mysql> SET @name='MySQL Administrator';  
19. Query OK, 0 rows affected (0.02 sec)  
20.   
21. mysql> EXECUTE prod_insert_stmt USING @code,@name;  
22. Query OK, 1 row affected (0.00 sec)  
23.   
24. mysql> DEALLOCATE PREPARE prod_insert_stmt;  
25. Query OK, 0 rows affected (0.00 sec)  

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

Java代码  

1. CREATE PROCEDURE set_col_value  
2.     (in_table     VARCHAR(128),  
3.      in_column    VARCHAR(128),  
4.      in_new_value VARCHAR(1000),  
5.      in_where     VARCHAR(4000))  
6.   
7. BEGIN  
8.     DECLARE l_sql VARCHAR(4000);  
9.     SET l_slq=CONCAT_ws(' ',  
10.                  'UPDATE', in_table,  
11.                  'SET', in_column, '=', in_new_value,  
12.                  'WHERE', in_where);  
13.     SET @sql=l_sql;  
14.     PREPARE s1 FROM @sql;  
15.     EXECUTE s1;  
16.     DEALLOCATE PREPARE s1;  
17. END;  

 6，声明HANDLER的语法

Java代码  

1. DECLARE {CONTINUE | EXIT} HANDLER FOR  
2.     {SQLSTATE sqlstate_code | MySQL error code | condition_name}  
3.     stored_program_statement