关于mysql存储过程的学习

定义：

我的理解就是将sql语句 放到一起定义可以用来使用；它中间是可以添加一些特殊的方法和语句块。

特点：

1.灵活性：可以用流程控制语句编写，也可以像java方法那样封装起来多个sql语句实现一个复杂的功能，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

2.重复性：存储过程在创建以后就永久的保存到了数据库中，可以随时调用，方便快捷。

3.高效性：存储过程在刚被创建以后就会编译，以后每次使用不会再次编译，SQL语句在每次运行时都要进行编译和优化，提高运行效率。

4安全性：系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

一、CREATE      PROCEDURE （创建）
1.定义格式:

CREATE  +  PROCEDURE 存储过程名(参数列表)
  BEGIN
  SQL语句代码块
  END

调用: 

 CALL  +  存储过程名(参数列表);


2.例子：
mysql>DELIMITER //  
mysql>CREATE PROCEDURE proc1()  
   ->BEGIN 
   -> SELECT COUNT(*) FROM user;  
   ->END 
   ->//  
mysql>DELIMITER ; 
 
mysql>CALL proc1();
注：
（1）

DELIMITER(后面跟的符号     //     ;    ) : 

         两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
（2）过程体的开始与结束使用BEGIN与END进行标识。

3.查看存储过程命令:
1.show procedure status
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等


2.show create procedure sp_name
显示某一个MySQL存储过程的详细信息


3.删除存储过程
DROP PROCEDURE IF EXISTS sp_name

二、存储过程几种


MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名数据类形...])


IN输入参数:

表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数:

该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数:

调用时指定，并且可被改变和返回

1.多参数的例子：

mysql> CREATE PROCEDURE proc_all(IN p int, OUT c int)
         -> BEGIN
         -> SELECT MAX(e_salary) INTO c FROM employe WHERE e_no=p;
     -> END
     -> //
Query OK, 0 rows affected

mysql> DELIMITER ;
mysql> select * from employe;
+------+--------+-------+---------+----------+----------+-----------+
| e_no | e_name | e_sex | dept_no | e_job    | e_salary | hire_date |
+------+--------+-------+---------+----------+----------+-----------+
| 1001 | 李强   | 1     |      20 | 会计     |      800 | 2015-9-9  |
| 1002 | 王刚   | 1     |      30 | HR       |     1600 | 2014-10-9 |
| 1003 | 姗姗   | 0     |      30 | HR       |     1250 | 2014-11-7 |
| 1004 | 张阳   | 1     |      20 | 出纳     |     2975 | 2015-4-9  |
| 1005 | 小丽   | 0     |      40 | 销售经理 |     2850 | 2015-2-10 |
| 1006 | 王二   | 1     |      10 | 产品策划 |     2450 | 2015-4-23 |
| 1007 | 小冬   | 1     |      40 | 销售经理 |     2750 | 2015-3-10 |
+------+--------+-------+---------+----------+----------+-----------+
7 rows in set


mysql> set @p=1002;
Query OK, 0 rows affected


mysql> CALL proc_all(@p,@c);
Query OK, 0 rows affected


mysql> SELECT @c AS 对应工资;
+----------+
| 对应工资 |
+----------+
|     1600 |
+----------+

/**
* 如何在程序中实现储存过程proc_all(IN p int, OUT c int)（两个参数）上表为对应的mysql数据库操作过程
*/
public void executeProcedureInOut(){
String sql = "CALL proc_all(?,?)";
Connection connect = null;
CallableStatement cs = null;
try {
connect=ConnectDB.getConnection();//这里的getConnection方法是我自己封装的一个静态方法，作用是用来建立连接
cs = connect.prepareCall(sql);
cs.setInt(1, 1002);  //设置输入参数
cs.registerOutParameter(2, Types.INTEGER); //设置输出参数类型
cs.execute();

int max = cs.getInt(2);  //第2参数据为输出参数，用第2个输出参数接收返回结果
System.out.println("编号1002对应工资="+ max);

} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
if (cs != null)
cs.close();
} catch (SQLException e) {
e.printStackTrace();
}
ConnectDB.closeConnection(connect);//这里是关闭连接
}
}

对应输出的结果为如下

编号1002对应工资=1600

2.无参数例子：

mysql>DELIMITER //  
mysql>CREATE PROCEDURE proc1()  
   ->BEGIN 
    -> SELECT  *  FROM employe;  
   ->END 
   ->// 

Query OK, 0 rows affected

mysql> DELIMITER ;

mysql> CALL p_addms;
+------+--------+-------+---------+----------+----------+-----------+
| e_no | e_name | e_sex | dept_no | e_job    | e_salary | hire_date |
+------+--------+-------+---------+----------+----------+-----------+
| 1001 | 李强   | 1     |      20 | 会计     |      800 | 2015-9-9  |
| 1002 | 王刚   | 1     |      30 | HR       |     1600 | 2014-10-9 |
| 1003 | 姗姗   | 0     |      30 | HR       |     1250 | 2014-11-7 |
| 1004 | 张阳   | 1     |      20 | 出纳     |     2975 | 2015-4-9  |
| 1005 | 小丽   | 0     |      40 | 销售经理 |     2850 | 2015-2-10 |
| 1006 | 王二   | 1     |      10 | 产品策划 |     2450 | 2015-4-23 |
| 1007 | 小冬   | 1     |      40 | 销售经理 |     2750 | 2015-3-10 |
+------+--------+-------+---------+----------+----------+-----------+
7 rows in set


Query OK, 0 rows affected

对应的程序实现为：

/**
* 显示所有的员工的信息
*/
public void startCall(){
String sql="{CALL P_addms}";
Connection connect=null;
PreparedStatement ps=null;
ResultSet rs=null;//结果集
try {
connect=ConnectDB.getConnection();
ps=connect.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()){
int number=rs.getInt("e_no");
String name=rs.getString("e_name");
int sex=rs.getInt("e_sex");
int number1=rs.getInt("dept_no");
String job=rs.getString("e_job");
int salary=rs.getInt("e_salary");
String date=rs.getString("hire_date");
System.out.println(number+"\t"+name+"\t"+sex+"\t"+number1+"\t"+job+"\t"+salary+"\t"+date);
}

} catch (SQLException e) {


e.printStackTrace();
}
ConnectDB.closeConnection(connect);
}

其输出结果为：

1001李强 1 20 会计 800        2015-9-9
1002 王刚 1 30 HR1600 2014-10-9
1003 姗姗 0 30 HR1250 2014-11-7
1004 张阳 1 20 出纳2975 2015-4-9
1005 小丽 0 40 销售经理2850 2015-2-10
1006 王二 1 10 产品策划2450 2015-4-23
1007 小冬 1 40 销售经理2750 2015-3-10

3. 带输出参数返回值的存储过程：
--删除存储过程
DROP PROCEDURE IF EXISTS proc_getcount
--设置分割符为//
mysql> DELIMITER //
--创建存储过程
mysql> CREATE PROCEDURE proc_getcount(out count int)
    -> BEGIN
    -> SELECT COUNT(*) INTO count FROM employee;
    -> END
    -> //
--设置分割符为;
mysql> DELIMITER ;
--调用存储过程
mysql> CALL proc_getcount(@count);
--查看输出变量值
mysql> SELECT @count;
+--------+
| @count |
+--------+
|      7 |
+--------+


4.带输入参数的存储过程：
--设置分割符为//
mysql> DELIMITER //
--创建存储过程
mysql> CREATE PROCEDURE proc_find_by_id(in n int)
    -> BEGIN
    -> SELECT * FROM employee WHERE e_no = n;
    -> END
    -> //
--设置分割符为;
mysql> DELIMITER ;
--定义变量
mysql> SET @n=1002;
--调用存储过程
mysql> CALL proc_find_by_id(@n);
+------+--------+-------+---------+-------+----------+-----------+
| e_no | e_name | e_sex | dept_no | e_job | e_salary | hire_date |
+------+--------+-------+---------+-------+----------+-----------+
| 1002 | 王刚       | 1     |      30 | HR    |     1600 | 2014-10-9 |
+------+--------+-------+---------+-------+----------+-----------+

5.带输入输出参数


mysql> DELIMITER //
mysql> CREATE PROCEDURE proc_inout(INOUT p int)
    -> BEGIN
    -> SELECT p;
    -> SET p = 10;
    -> SELECT p;
    -> END
    -> //
mysql> DELIMITER ;
mysql> SET @p = 2;
mysql> CALL proc_inout(@p);
+------+
| p    |
+------+
|    2 |
+------+
1 row in set (0.00 sec)


+------+
| p    |
+------+
|   10 |
+------+


mysql> SELECT @p;
+------+
| @p   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

三、代码调用


四、变量
DECLARE 变量名  变量类型  [DEFAULT  默认值]
如:
 declare x varchar(5) default 'outer';


七、基本控制语句
1.if语句 
if(条件成立){
    语句 
}


IF 条件成立 THEN 
   语句
END IF


if-else语句
if(){
  语句1;
}else{
  语句2;
}


IF 条件 THEN
   语句1;
ELSE
   语句2;
END IF


mysql> CREATE PROCEDURE proc_declare()
    -> BEGIN
    -> DECLARE x int default 1;
    -> SELECT x;

    -> IF x = 1 THEN
    ->    SELECT * FROM employee WHERE e_no = 1001;
    -> ELSE
    ->    SELECT COUNT(*) FROM employee;
    -> END IF;

    -> END;
    -> //

2.多分支语句
switch(值){
 case 值1：
    语句块1;
 case 值2:
    语句块2
}


CASE 值
WHEN 值1 THEN
   语句块1;
WHEN 值2 THEN
   语句块2;
WHEN 值3 THEN
   语句块3;
END CASE;
 
 ->case var  
 ->when 0 then   
 ->  insert into t values(17);  
 ->when 1 then   
 ->  insert into t values(18);  
 ->else   
 ->  insert into t values(19);  
 -> end case;  


3.循环语句
while(条件){
  语句块;
} 


WHILE 条件  do
  语句块;
END WHILE;


  ->while var<6 do  
  ->   insert into t values(var);  
  ->   set var=var+1;  
  ->end while;