mysql存储过程快速入门

一、创建存储过程

创建存储过程(一)

create PROCEDURE pro1()
BEGIN
 select 'Hello World';
END;

//执行存储过程
call proc1();

创建存储过程的基本形式

CREATE PROCEDURE sp_name([proc_parameter[,…]])
[characteristic …] routine_body

其中：
sp_name:存储过程名称
proc_parameter:存储过程参数，可以为in,out,inout参数，其形式如下
[in | out | inout ] param_name type
characteristic参数有多个取值：
LANGUAGE SQL:说明routine_body部分是由SQL语言的语句组成，这也是数据库系统默认的语言。
[NOT] DETERMINISTIC:指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是非确定的，相同的输入可能得到不同的输出。默认情况下是非确定的。
…

创建存储过程(二)

create PROCEDURE proc2() COMMENT 'This is Hello World Procedure!'
BEGIN
 select 'Hello World';
END;

//查看存储过程
select * from information_schema.ROUTINES t where t.ROUTINE_SCHEMA = ‘your_db_name’;
//结果表中的ROUTINE_COMMENT显示了proc2的说明。

二、删除存储过程

删除存储过程(一)

drop PROCEDURE pro1;

//查看存储过程
select * from information_schema.ROUTINES t where t.ROUTINE_SCHEMA = ‘your_db_name’;

删除存储过程(二)

drop PROCEDURE if exists vehicle.proc2;

//查看存储过程
select * from information_schema.ROUTINES t where t.ROUTINE_SCHEMA = ‘your_db_name’;

三、 带参数的存储过程

带参数的存储过程

//功能是 查看指定名称的存储过程是否存在
drop PROCEDURE if EXISTS isExistsProc;
create PROCEDURE isExistsProc(dbname varchar(10),procname varchar(10))
begin 
    select count(*) from information_schema.ROUTINES t where t.ROUTINE_SCHEMA = dbname
    and t.ROUTINE_NAME = procname into @pro_count;
    select @pro_count as 'procedure count';
end;

//调用存储过程
call vehicle.isExistsProc('vehicle','proc2');

带out参数的存储过程

drop PROCEDURE if EXISTS proc_add;
create PROCEDURE proc_add(a int,b int,out sum int)
BEGIN
    set sum = a + b;
END;

call proc_add(1,2,@sum);
select @sum;

四、变量的使用

drop PROCEDURE if EXISTS proc3;
create PROCEDURE proc3()
BEGIN
    declare res int default 10;
    select res;
END;

call proc3();

五、游标的使用

drop PROCEDURE if exists proc4;
create PROCEDURE proc4()
BEGIN
    DECLARE user_name varchar(10);
    DECLARE v_password varchar(10);
    DECLARE cur_user CURSOR for select name,password from t_user;
    OPEN cur_user;
    FETCH cur_user into user_name,v_password;
#   FETCH cur_user into user_name,v_password;
    select user_name as 'name',v_password as 'password';
    CLOSE cur_user;
END;

call proc4();

六、一道综合题

//写一个存储过程，输入表名，如果表存在，则返回1，否则返回0.如果表存在，则通过out参数将表的总行数返回
DROP PROCEDURE IF EXISTS getRowCountOfTable;
create PROCEDURE getRowCountOfTable(tablename VARCHAR(10),OUT r_count int)
BEGIN
    declare t int default 0;
    select count(*) from information_schema.`TABLES` t where TABLE_SCHEMA = 'vehicle'
    and TABLE_NAME = tablename into t;
    if( t > 0) then
                set @s = CONCAT('select count(*) from ',tablename,' into @rcount');
                PREPARE stmt from @s;
                set @tname = tablename;
                set @rcount = 0;
                EXECUTE stmt;
                DEALLOCATE PREPARE stmt;
                set r_count = @rcount;
                select 1;
    ELSE
            select 0;
    end if;
END;

七、Jdbc调用sql

ApplicationContext ctx = new FileSystemXmlApplicationContext("src/xml/DbConfig.xml");
BasicDataSource dataSource = (BasicDataSource)ctx.getBean("dataSource");
Connection conn = null;
CallableStatement callStmt = null;
try
{
  conn = dataSource.getConnection();
  callStmt = conn.prepareCall("{call getRowCountOfTable(?,?)}");
  callStmt.setString(1, "t_user");
  callStmt.registerOutParameter(2, Types.INTEGER);
            
  callStmt.execute();
  ResultSet rs = callStmt.getResultSet();
  int res = 0;
  if(rs.next())
  res = rs.getInt(1);
  if(res > 0)
  {
     System.out.println("table \"t_user\" is exists!");
     int rowCount = callStmt.getInt(2);
                
     System.out.println("the count of rows in the table is " + rowCount);
  }
  else
  {
      System.out.println("table \"t_user\" is not exists!");
  }
}
catch(SQLException e)
{
  e.printStackTrace();
 }