语法
CREATE PROCEDURE 储存名([这里可以写参数])
begin
执行的sql语句 ;
end;
实例
CREATE PROCEDURE add_student(in snum varchar(8),in sname varchar(20) ,in gender varchar(2),in age int , in cid int ,in remark varchar(255))
BEGIN
INSERT into students(stu_num,stu_name,stu_gender,stu_age,cid,remark) VALUES
(snum,sname,gender,age,cid,remark);
END;
CALL add_student('20210108','小丽','女',18,1,'添加学生存储过程');
call 调用存储过程
CALL add_student(‘20210108’,‘小丽’,‘女’,18,1,‘添加学生存储过程’); 当执行过后 表里面就会多一条数据。
简单的借书存储过程
-- 创建图书信息表:
create table books(
book_id int primary key auto_increment,
book_name varchar(50) not null,
book_author varchar(20) not null,
book_price decimal(10,2) not null,
book_stock int not null,
book_desc varchar(200)
);
-- 添加图书信息
insert into books(book_name,book_author,book_price,book_stock,book_desc)
values('Java程序设计','亮亮',38.80,12,'亮亮老师带你学Java');
insert into books(book_name,book_author,book_price,book_stock,book_desc)
values('Java王者之路','威哥',44.40,9,'千锋威哥,Java王者领路人');
-- 创建学生信息表
create table students(
stu_num char(4) primary key,
stu_name varchar(20) not null,
stu_gender char(2) not null,
stu_age int not null
);
-- 添加学生信息
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1001','张三','男',20);
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1002','李四','女',20);
insert into students(stu_num,stu_name,stu_gender,stu_age) values('1003','王五','男',20);
-- 借书记录表:
create table records(
rid int primary key auto_increment,
snum char(4) not null,
bid int not null,
borrow_num int not null,
is_return int not null, -- 0表示为归还 1 表示已经归还
borrow_date date not null,
constraint FK_RECORDS_STUDENTS foreign key(snum) references students(stu_num),
constraint FK_RECORDS_BOOKS foreign key(bid) REFERENCES books(book_id)
);
-- 使用存储过程完成借书操作 a学号 b编号 num借书的数量
-- state借书的状态 1:借书成功 2:学号不存在 3:图书不存在 4:库存不足
create PROCEDURE proc_borrow_book(in a char(4),in b int,in num int,out state int)
BEGIN
DECLARE stu_count int DEFAULT 0 ; -- 判断学号是否存在
DECLARE book_count int DEFAULT 0 ;-- 判断图书是否存在
DECLARE stock int DEFAULT 0;-- 库存
-- 判断学号是否存在
SELECT COUNT(stu_num) INTO stu_count FROM students where stu_num=a;
IF stu_count>0 THEN -- 判断学号是否存在
SELECT COUNT(book_id) INTO book_count FROM books where book_id =b;
IF book_count>0 THEN -- 判断图书是否存在
SELECT book_stock INTO stock FROM books where book_id =b;
IF (stock-num)>0 THEN -- 判断库存是否充足
UPDATE books set book_stock = (stock-num);
INSERT into records VALUES(0,a,b,num,0,NOW());
set state = 1;
ELSE
set state = 4; -- 库存不足
END IF;
ELSE
set state = 3; -- 图书不存在
END IF;
ELSE
set state = 2; -- 学号不存在
END IF;
END;
set @state=0; -- 创建一个全局变量接收 存储过程返回值
CALL proc_borrow_book('1001',8,99,@state);
SELECT @state from DUAL;