首页 > 代码库 > mysql存储过程快速入门

mysql存储过程快速入门

2024-07-15 07:29:13   221人阅读

一、创建存储过程

创建存储过程(一)

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 typecharacteristic参数有多个取值：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(); }

 


联系
我们