一、创建存储过程
创建存储过程(一)
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(); }