mysql 简单的添加存储过程

语法

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;