首页 > 代码库 > mysql存储过程快速入门
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 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(); }
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。