MYSQL的存储过程

 

 

 

 

 

-- 存储过程
create database mysql7_procedure;
use mysql7_procedure;
-- 1:创建存储过程
-- delimiter自定义结束符号
-- create procedure 储存名([ in,out ,inout ] 参数名 数据类形...)
-- begin
-- sql语句
-- end 自定义的结束符合
-- delimiter;

存储数据

create table dept(
deptno int primary key,
dname varchar(20),
loc varchar(20)
);
insert into dept values(10, '教研部','北京'),
(20, '学工部','上海'),
(30, '销售部','广州'),
(40, '财务部','武汉');

create table emp(
empno int primary key,
ename varchar(20),
job varchar(20),
mgr int,
hiredate date,
sal numeric(8,2),
comm numeric(8, 2),
deptno int,
-- FOREIGN KEY (mgr) REFERENCES emp(empno),
FOREIGN KEY (deptno) REFERENCES dept(deptno) ON DELETE SET NULL ON UPDATE CASCADE
);
insert into emp values
(1001, '甘宁', '文员', 1013, '2000-12-17', 8000.00, null, 20),
(1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000.00, 3000.00, 30),
(1003, '殷天正', '销售员', 1006, '2001-02-22', 12500.00, 5000.00, 30),
(1004, '刘备', '经理', 1009, '2001-4-02', 29750.00, null, 20),
(1005, '谢逊', '销售员', 1006, '2001-9-28', 12500.00, 14000.00, 30),
(1006, '关羽', '经理', 1009, '2001-05-01', 28500.00, null, 30),
(1007, '张飞', '经理', 1009, '2001-09-01', 24500.00, null, 10),
(1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000.00, null, 20),
(1009, '曾阿牛', '董事长', null, '2001-11-17', 50000.00, null, 10),
(1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000.00, 0.00, 30),
(1011, '周泰', '文员', 1008, '2007-05-23', 11000.00, null, 20),
(1012, '程普', '文员', 1006, '2001-12-03', 9500.00, null, 30),
(1013, '庞统', '分析师', 1004, '2001-12-03', 30000.00, null, 20),
(1014, '黄盖', '文员', 1007, '2002-01-23', 13000.00, null, 10);

create table salgrade(
grade int primary key,
losal int,
hisal int
);
insert into salgrade values
(1, 7000, 12000),
(2, 12010, 14000),
(3, 14010, 20000),
(4, 20010, 30000),
(5, 30010, 99990);

delimiter $$
create procedure proc01()
begin
select empno,ename from emp;
end $$
delimiter ;#恢复结束符为分号
-- 调用存储过程
call proc01();

 

 

-- 定义局部变量
/*
语法1：声明变量 declare var_name type [default var_value];
语法2： select col_name [...] into var_name
[,...)
from table_name wehre condition
*/
delimiter $$
create procedure proc02()
begin
declare var_name01 varchar(20) default 'aaa';#声明变量
set var_name01 = 'zhangsan';#给变量赋值
select var_name01;#输出变量
end $$
delimiter ;
call proc02()

 

 

 

 

delimiter $$
create procedure proc03()
begin
declare my_ename varchar(20);#声明变量
select ename into my_ename from emp where empno =1001;#给变量赋值
select my_ename;#输出变量
end $$
delimiter ;
call proc03()

 

 

-- 用户变量
delimiter $$
create procedure proc04()
begin
set @var_nam01 = 'beijing';
select @var_nam01;
end $$
delimiter ;
call proc04()
select @var_nam01;#在外部也可以使用用户变量

 

 

 

 

-- 系统变量
-- 全局变量
use mysql7_procedure;
-- 查看全局变量
show global variables;-- 查看某全局变量
select @@global.auto_increment_increment;
-- 修改全局变量的值
set global sort_buffer_size = 40000;
set @@global.sort_buffer_size = 34000;
select @@global.sort_buffer_size;

-- 会话变量
-- 查看会话变量
show session variables;
-- 查看某会话变量
select @@session.auto_increment_increment;
-- 修改会话变量的值
set session sort_buffer_size = 50000;
set @@session.sort_buffer_size = 50000;

 

 

-- 传入参数：in
-- 封装有参数的存储过程，传入员工编号，查找员工信息
delimiter $$
create procedure proc06(in param_empno int)
begin
select * from emp where empno = param_empno;
end $$
delimiter ;
call proc06(1001);
call proc06(1002);#在外部也可以使用用户变量
-- 封装有参数的存储过程，可以通过传入部门名和薪资，查询指定部门，并且薪资大于指定员工信息
delimiter $$
create procedure proc07(in param_dname varchar(50),in param_sal decimal(7,2))
begin
select * from dept a, emp b where a.deptno=b.deptno and a.dname = param_dname and b.sal >param_sal;
end $$
delimiter ;
call proc07('学工部',20000)