MySQL 存储过程

MySQL 存储过程


存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象

存储过程就是具有名字的一段代码,用来完成一个特定的功能。
创建的存储过程保存在数据库的数据字典中

优点:
存储过程可封装,并隐藏复杂的商业逻辑。
存储过程可以回传值,并可以接受参数。
存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同。
存储过程可以用在数据检验,强制实行商业逻辑等。
缺点:
存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。
存储过程的性能调校与撰写,受限于各种数据库系统。

存储过程的参数 :
参数类型:
IN输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)

MYSQL 存储过程中的关键语法:

delimiter//                            声明语句结束符,可以自定义(默认分号)
create procedure 名称(声明参数1,...)                声明存储过程
begin...end                            存储过程开始和结束符号
declare 变量名 变量类型 (unsigned default 默认值)        定义变量
set 变量名=变量值                        变量赋值
参数类型(in|out|inout) 参数名 数据类型(int、varchar等)    声明参数


例子:
delimiter //                -- 将SQL语句的结束标记设置为//
drop procedure if exists testprint;    -- 如果存在该名称的存储过程则删除
create procedure testprint()        -- 创建一个名字为testprint的存储过程
begin                     -- 代码部分开始
select '您好!' as infos;        -- 存储过程的代码部分
end;                     -- 代码部分结束
//                    -- 结束标记
    
delimiter ;                -- 编写变异结束后将SQL语句的结束标记还原
call testprint;                -- 调用存储过程

in输入参数:
多个参数用逗号分开,SQL语句最后需加分号
delimiter //
create procedure addteacher(in p_tname varchar(20),in p_tsex varchar(4))
begin 
insert into teacher (tname,tsex) values(p_tname,p_tsex);
end;//
--调用存储过程使用call
--@pp_tname临时变量:@+变量名
delimiter ;
set @pp_tname='王五';
set @pp_tsex='男';
call addteacher(@pp_tname,@pp_tsex);

OUT输出参数:
delimiter //
drop procedure if exists selecttname;
create PROCEDURE selecttname(in p_sname varchar(20),out p_tname varchar(20))
begin 
--使用select ... into ...,将查询结果赋值给参数
select tname into p_tname from teacher 
inner join class on ctid=tid
inner join student on scid=cid
where sname=p_sname;
end;
//
delimiter ;
set @sname='一一';
--调用带有out输出参数的存储过程,作为输出参数的变量需要被赋值
set @tname='';
call selecttname(@sname,@tname);
select @tname;

条件语句

if-then-else 语句
以if开始 + 条件 + then + 操作 (+ else + 操作)+ 以end if结尾

delimiter //
create procedure proc(in parameter int)  
begin 
declare var int;  
set var=parameter+1;  
if var=0 then 
insert into t values(17);  
end if;  
if parameter=0 then 
update t set s1=s1+1;  
else 
update t set s1=s1+2;  
end if;  
end;  
//

case语句
case + 变量|参数 + when + 值1 + then + 操作 + when + 值2 + then + 操作... + else + 操作 + end case

delimiter //
create procedure proc(in parameter int)  
begin 
declare var int;  
set var=parameter+1; 
case var  
when 0 then   
insert into t values(17);  
when 1 then   
insert into t values(18);  
else   
insert into t values(19);  
end case;  
end;  
//  

循环语句
一:
while 条件 do  循环体 end while

delimiter //
create procedure proc()  
begin 
declare var int;  
set var=0;  
while var<6 do  
insert into t values(var);  
set var=var+1;  
end while;  
end;  
//  

二:
repeat 循环体 until 循环条件  end repeat;

delimiter //
create procedure proc()  
begin 
declare v int;  
set v=0;  
repeat  
insert into t values(v);  
set v=v+1;  
until v>=5  
end repeat;  
end;  
//
  
三:
名称(自定义):loop 循环体 if 条件 leave 名称 end if end loop

delimiter //
create procedure proc()  
begin 
declare v int;  
set v=0;  
LOOP_LABLE:loop  
insert into t values(v);  
set v=v+1;  
if v >=5 then 
leave LOOP_LABLE;  
end if;  
end loop;  
end;  
//  

JDBC调用存储过程

不带输出参数:

//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获得连接
String url = "jdbc:mysql://127.0.0.1:3306/lianxi1";
String user = "root";
String userPass = "123456";
Connection con = DriverManager.getConnection(url, user, userPass);
//创建存储过程的对象  
java.sql.CallableStatement  cs = con.prepareCall("{call addstu(?,?)}");
//给存储过程的参数设置值
cs.setString(1, "张三");
cs.setInt(2, "男");
//执行存储过程 
n = cs.executeUpdate();

带输出参数:
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获得连接
String url = "jdbc:mysql://127.0.0.1:3306/lianxi1";
String user = "root";
String userPass = "123456";
Connection con = DriverManager.getConnection(url, user, userPass);
//创建存储过程的对象  ,querystu根据id查姓名
java.sql.CallableStatement  cs = con.prepareCall("{call querystu(?,?)}");
//给存储过程的参数设置值,第一个参数为id
cs.setInt(1, 2);
//第二个参数,为out name varchar(20)
//注册存储过程的第二个参数registerOutParameter(),
cs.registerOutParameter(2, java.sql.Types.VARCHAR);
//执行存储过程
cs.execute();
//得到存储过程的输出参数值,如果输出为int类型则使用getInt();
String name = cs.getString(2);


Mybatis调用存储过程

有输出参数时:
XML映射文件内书写语句需要加属性statementType="CALLABLE"
注释书写SQL语句需要声明:@Options(statementType=StatementType.CALLABLE)
输出参数写法:#{name,mode=OUT,jdbcType=VARCHAR} ,OUT、VARCHAR需要大写

XML文件内书写
不带输出参数:statementType="CALLABLE"可以省略    
<insert id="addclass" parameterType="map" statementType="CALLABLE">
    call addstu(#{name},#{sex})
</insert>

带输出参数:
注意!传入的参数类型直接为:parameterType="String"无法获得输出参数
<select id="selecttname" statementType="CALLABLE" parameterType="map">
    call selectname(#{id},#{name,mode=OUT,jdbcType=VARCHAR})
</select>
带输出参数的调用:
String str = "config/mybatis-config.xml";
InputStream is = Resources.getResourceAsStream(str);
SqlSessionFactory ss = new SqlSessionFactoryBuilder().build(is);
SqlSession s = ss.openSession();
Map<Object,String> map = new HashMap<>();
map.put("id", 1);
s.selectOne("selectname",map);
System.out.println(map.get("name"));

注解模式,带输出参数写法:
@Select("call selectname(#{id},#{name,mode=OUT,jdbcType=VARCHAR})")
@Options(statementType=StatementType.CALLABLE)
public void selecttname1(Map<Object,String> map);

posted @ 2018-12-10 20:56  别念茶茶  阅读(406)  评论(0编辑  收藏  举报