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();
 }

 

mysql存储过程快速入门,布布扣,bubuko.com

mysql存储过程快速入门

上一篇:spring boot aop @Before注解 里面多层参数


下一篇:自定义注解