mysql 存储过程入门

 

  Mysql 的存储过程

一、 存储过程

存储过程（ Stored procedure ）是一组 sql 语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数来执行它。

Mysql 数据库中实现了两种类型的存储过程 ; 存储过程 (stored procedure) 和存储函数（ stored routine ） , 二者统称为存储例程

二、 初步认识

1.       存储过程基本语法

创建：

       CREATE PROCEDURE 存储过程名 ( 过程参数 […] )

        过程体

删除：

        DROP PROCEDURE [ IF EXISTS ] 存储过程名

2 ．例子

   2.1 创建

  ( 1 ) 编写过程：

        一下程序都是 mysql 的命令行输入，得先修改下端结束符 ： DELIMITER $$

        CREATE PROCEDURE helloworld( in d int)

        BEGIN

        SELECT d;

        END$$

    ( 2 ) 调用过程：

        CALL helloworld(3);

    ( 3 ) 显示：

         +------+

          | d    |

         +------+

          |    3 |

         +------+

        1 row in set (0.00 sec)

    ( 4 ) 解释：

        1. 过程参数： in d int

        in 用来指定该参数是向过程传递信息的，此外还有 out 和 inout 两种类型

            out— 从过程中传出信息   inout— 不仅传信息给过程，而且也从过程中传出信息

        d 参数名

        int 参数类型， 有效的参数类型

          

      2. 过程体

         BEGIN

        Sql 语句

   END

2.2 删除

            mysql> DROP PROCEDURE IF EXISTS helloworld;

     Query OK, 0 rows affected (0.03 sec)

    3. 存储函数

与存储过程类似

      3.1 基本语法

创建：

        CREATE FUNCTION   函数名 ( 函数参数 […] )

        RETURNS 返回类型

        函数体

删除：

              DROP PROCEDURE [ IF EXISTS ] 存储过程名

      3.2 例子

    CREATE FUNCTION F1(d int)

       RETURNS int

       BEGIN

       RETURN d;

      END$$

   3.3调用：

SELECT F1(10)$$

   3.4结果：

+--------+

| F1(10) |

+--------+

|     10 |

+--------+

1 row in set (0.00 sec)

三、进一步

1. 过程参数中的 IN 、 OUT 、 INOUT

   1.1 IN 是默认值

   1.2 对于任何申明为 OUT 或 INOUT 的参数，当调用存储过程时需要在参数名前加上 @ 符号，这样改参数就可以在过程外调用了

   1.3 IN 参数是值传递

              1.3.1 例子

CREATE PROCEDURE p_in(in id int)

       BEGIN

           if(id is not null) then

            set id=id+1;

           end if;

           select id as id_inner;

END$$

      1.3.2 调用

   set @id=10;

select @id as id_out;

call p_in(@id)$$

  1.3.3 结果

+--------+

| id_out |

+--------+

|     10 |

+--------+

1 row in set (0.00 sec)

 

+----------+

| id_inner |

+----------+

|        11 |

+----------+

1 row in set (0.01 sec)

1.4 ． OUT ，无论调用时是否传递有参数， out 关键字修饰的词初始值都为 null

   1.4.1 例子

CREATE PROCEDURE p_out(out id int)

  BEGIN

  select id as id_inner_1;

  if(id is not null) then

  set id=id+1;

  select id as id_inner_2;

  else

  select 1 into id;

  end if;

  select id as id_inner_3;

  END$$

  1.4.2 调用

set @id=10;

call p_out(@id)$$

select @id as id_out$$

  1.4.3 结果

 

+------------+

| id_inner_1 |

+------------+

|       NULL |

+------------+

1 row in set (0.04 sec)

 

+------------+

| id_inner_3 |

+------------+

|          1 |

+------------+

+--------+

| id_out |

+--------+

|      1 |

+--------+

 

1.5inout

      1.5.1 程序

CREATE PROCEDURE p_inout(out id int)

  BEGIN

  select id as id_inner_1;

  if(id is not null) then

  set id=id+1;

  select id as id_inner_2;

  else

  select 1 into id;

  end if;

  select id as id_inner_3;

  END

  $$

1.5.2 调用

set @id=10;

call p_out(@id)$$

select @id as id_inout$$

1.5.3 结果

 

+------------+

| id_inner_1 |

+------------+

|         10 |

+------------+

1 row in set (0.01 sec)

 

+------------+

| id_inner_2 |

+------------+

|         11 |

+------------+

1 row in set (0.01 sec)

 

+------------+

| id_inner_3 |

+------------+

|         11 |

+------------+

四、JDBC调用mysql的存储过程

 

 package jdbc; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Types; /** * * @Project: J2SEDemo * @Desciption: JDBC操作数据库中的存储过程 * @Author: LinYiSong * @Date: 2010-12-29~2010-12-29 */ public class JDBCProcedure { public static void main(String[] args) { Connection conn=null; CallableStatement callStm=null; try { //连接数据库 Class.forName("com.mysql.jdbc.Driver"); String url="jdbc:mysql://localhost:3306/student"; String user="root"; String password="root"; conn=DriverManager.getConnection(url, user, password); //调用存储过程 String sql="{call p_inout(?)}"; callStm=conn.prepareCall(sql); callStm.registerOutParameter(1,Types.INTEGER); callStm.setInt(1, 10); callStm.execute(); //取得返回值 System.out.println(callStm.getInt(1)); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }finally{ //关闭连接 try { if(callStm!=null){ callStm.close(); callStm=null; } if(conn!=null){ conn.close(); conn=null; } } catch (SQLException e) { e.printStackTrace(); } } } }