什么是存储过程
1、mysql5.0开始支持存储过程
2、存储过程就是一组sql语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于Java语言中的方法
3、存储过程就数据sql语言层面的代码封装与重用
存储过程的特性
1、有输入输出参数,可以声明变量,有if/else,case ,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能
2、函数的普通特性:模块化,封装,代码复用
3、速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省去以上步骤。
格式:
delimiter 自定义结束符号create procedure 存储名( [in ,out ,inout] 参数名 数据类型...)beginsql 语句end 自定义的结束符号delimiter;DELIMITER $$CREATE PROCEDURE pro1()BEGINSELECT NAME,age FROM emp2;END $$DELIMITER;CALL pro1(); ——调用存储过程
1、局部变量(用户自定义,在begin/end块中有效)
语法:declare var_name type[default var_value];举例:declare nickname varchar(32);DELIMITER $$CREATE PROCEDURE pro3()BEGINDECLARE var_name VARCHAR(32) DEFAULT 'aaa';SET var_name='张三';#select var_name;SELECT NAME,age FROM emp2WHERE NAME=var_name;END $$DELIMITER;CALL pro3();还可以使用select...into 语句为变量赋值,基本语法如下:select col_name [...] into var_name[...] from table_name where condition当将查询结果赋值给变量时,该查询语句返回结果只能是单行单列。
2、用户变量(当前会话有效,类比java的成员变量)
语法:@var_name 不需要提前声明,使用即声明DELIMITER $$CREATE PROCEDURE proc6()BEGINSET @var_name='张三';SELECT @var_name;END $$delimiter;CALL proc6()SELECT @var_name ——在begin外也可以使用
3、系统变量
1、系统变量分为全局变量和会话变量
2、全局变量在mysql启动时由服务器自动初始化为默认值,可以通过my.ini这个文件来更改
3、会话变量在新建连接时,由mysql来初始化,会将全局变量复制一份,作为会话变量,没进行修改的话,所有值是跟全局变量一样的
4、会话变量修改只影响当前连接会话,全局变量会影响整个服务器
5、有些可以利用语句进行动态修改,有些系统变量的值是只读的,对于那些可以修改的系统变量,我们可以用set进行更改。
语法@@global.var_name(全局变量)show global variables; ——查看全局变量select @@global.auto_incerment_increment; ——查看某全局变量set global sort_buffer_size=40000; ——初始化或修改全局变量的值set @@global.sort_buffer_size =30000; ——初始化或修改全局变量的值(为32768,不能小于这个值)语法@@session.var_name(会话变量)show session variables; ——查看会话变量select @@session.auto_incerment_increment; ——查看某会话变量set session sort_buffer_size=40000; ——初始化或修改会话变量的值set @@session.sort_buffer_size =30000; ——初始化或修改会话变量的值(为32768,不能小于这个值)
存储过程传参
1、(in,可以传多个参数)(in param1 varchar(20),in param2 int ...)
in表示传入的参数,可以传入数值或者变量,即使传入变量,并不会更改变量的值,可以内部更改,仅仅作用在函数范围内。
--输入员工的工号,返回员工的信息delemiter $$create procedure dec_param(in param_empno varchar(20))beginselect * from emp where empno=param_empno;end $$delimiter ;call dec_param('1001');
2、(out)
表示从存储过程内部传值给调用者
--传入员工的编号,返回员工的姓名(返回单个值)delimiter $$create procedure proc(in empno int ,out out_name varchar())beginselect ename into out_name from emp where emp.empno=empno;end $$delimiter;call proc(1001,@o_ename);select @o_ename;传入员工的编号,返回员工的姓名和薪资(返回多个值)delimiter $$create procedure proc(in empno int ,out out_name varchar(),out out_sal decimal(7,2))beginselect ename,salinto out_name,out_salfrom emp where emp.empno=empno;end $$delimiter;call proc(1001,@o_ename,@o_sal);select @o_ename,@o_sal;
3、inout
inout表示从外部传入的参数经过修改后可以返回的变量,既可以使用传入的值也可以修改变量的值(即使函数执行完)
--传入员工名,拼接部门号,传入薪资,求出年薪delimiter $$create procedure proc(inout inout_ename varchar(50),inout inout_sal int)beginselect concat(deptno,"_",inout_ename) into inout_ename from emp where ename=inout_ename;set inout_sal=inout_sal*12;end $$delimiter;set @inout_ename='关羽';set @inout_sal=3000;call proc(@inout_ename,@inout_sal );select @inout_ename;select @inout_sal;