存储过程 存储过程概述 1.产生背景 开发过程总,经常会遇到重复使用某一功能的情况 2.解决办法 Mysql引人了存储过程(Stored Procedure)这一技术 3.存储过程 存储过程就是一条或
DROP TABLE IF EXISTS student;CREATE TABLE student (sid CHAR(6),sname VARCHAR(50),age INT,gender VARCHAR(50) DEFAULT 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
-- 改存储过程中依据指定年纪查询出符合条件的学生delimiter //create procedure procedureDemo01(in sage int)beginselect * from student where age>sage;end //delimiter ;
声明当前段分隔符
让编译器把两个//
之间的内容当做存储过程的代码
mysql 默认以 **;**为分隔符
把分隔符还原为默认分隔符;
DELIMITER也可以指定其他符号作为结束符
注意:DELIMITER与要设定的结束符之间一定要有一个空格,否则设定无效
过程体
存储过程的过程体以BEGIN开始以END结束
call 存储过程名(参数);
调用存储过程call procedureDemo01(15);
DROP PROCEDURE 存储过程名;
删除存储过程drop procedure procedureDemo01;
CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类型...])
-- findStudentByAge,该存储过程中有个IN参数,该参数为INT类型叫做sage delimiter //create procedure findStudentByAge(in sage int)beginselect * from student where age=sage;end //delimiter ;-- 调用存储过程call findStudentByAge(16);
@var_name
SET 用户变量=初始值;
SELECT 用户变量;
-- 存储过程countStudent,该存储过程中有个OUT参数,该参数为INT类型叫做total。-- 存储体中将统计的结果利用`INTO`存入total中。-- 调用存储过程中将用户变量@number作为参数传入,然后利用`SELECT @number`参看结果。delimiter //create procedure countStudent(out total int)beginselect count(*) into total from student;end //delimiter ;set @number=0;call countStudent(@number);select @number;
-- 存储过程searchStudentGender,该存储过程中有个INOUT参数,该参数为VARCHAR(50)类型叫做message。-- message既当做输入参数又当做输出参数,即输入参数为学生的姓名lili返回的是学生的性别male。-- 调用存储过程时将初始值为lili的用户变量@info传入存储过程,调用存储过程结束后再次查询@info的值为male。 delimiter //create procedure queryStudentGender(inout message varchar(50))beginselect gender into message from student where sname=message;end //delimiter ;set @info='lili';call queryStudentGender(@info);select @info;
DECLARE varName dataType [DEFAULT value];
SET varName = value;
delimiter //create procedure varTest01(in number1 int)begin-- 声明变量declare number2 int;-- 声明变量resultdeclare result int;-- 为变量number2赋值set number2 = 99;-- 为变量result赋值set result = number1 + number2;-- 显示resultselect result;end //delimiter ;-- 调用存储过程call varTest01(1);
-- 若存在存储过程findStudent,将之删除drop procedure if exists findStudent;delimiter //create procedure findStudent(in studentID char(6))begindeclare studentName varchar(59);declare studentGender varchar(50);-- 将查询结果保存至变量studentName和studentGender中select sname,gender into studentName,studentGender from student where sid=studentID;-- 显示studentName和studentGender中保存的值select studentName,studentGender;end //delimiter ;-- 调用存储过程call findStudent('S_1001');
IF expr_condition THEN statement_list [ELSE expr_condtion THEN statement_list] ... [ELSE statement_list]END IF
drop procedure if exists ifTest;delimiter //create procedure ifTest(in num int)begindeclare result varchar(20);if num < 0 then set result = 'negative number';elseif num = 0 thenset result = 'number is zero';else set result = 'positive number';end if;select result;end //delimiter;-- 调用存储过程call ifTest(-1);
CASE case_expr WHEN value THEN statement_list [WHEN value THEN statement_list] ... [ELSE statement_list]END CASE;
DROP PROCEDUREIFEXISTS testCase01;delimiter //create procedure testCase01(in num int)begindeclare result varchar(20);case numwhen num>0 set result = 'num is 正数';when num=0 set result = 'num is zero';else set result = 'num is 负数';end case;select result;end //delimiter ;call testCase01(1);
CASEWHEN expr_condition THEN statement_list[WHEN expr_condition THEN statement_list] ... [ELSE statement_list]END CASE;
delimiter //create procedure testCase02(in num int)begindeclare result varchar(20);casewhen num > 0 then set result = 'positive number';when num = 0 then set result = 'number is zero';else set result = 'positive number';end case;select result;end //delimiter ;call testCase02(1);
[loop_label:] LOOP statement_listEND LOOP [loop_label]
delimiter //create procedure testLoop(in start1 int ,in end1 int)begindeclare sumResult int default 0;add_loop: loopset sumResult = sumResult + start1;set start1 = start1 + 1;if start1 > end1 then leave add_loop; end if; end loop add_loop; select sumResult;end //delimiter ;call testLoop(0,100);
ITERATE label
DROP PROCEDURE IF EXISTS testITERATE; DELIMITER // CREATE PROCEDURE testITERATE(IN start1 INT,IN end1 INT) BEGIN DECLARE sumResult INT DEFAULT 0; add_loop: LOOP SET sumResult=sumResult+start1; SET start1=start1+1; IF start1 <= end1 THEN ITERATE add_loop; ELSE LEAVE add_loop; END IF; END LOOP add_loop; SELECT sumResult; END //DELIMITER ;call testITERATE(0,100);
[repeat_label:] REPEAT statement_listUNTIL expr_condtionEND REPEAT [repeat_label]
DELIMITER // CREATE PROCEDURE testREPEAT(IN start INT,IN end INT) BEGIN DECLARE sumResult INT DEFAULT 0; REPEAT SET sumResult = sumResult + start; SET start=start+1;UNTIL start > end END REPEAT;SELECT sumResult;END //DELIMITER ;call testREPEAT(0,100);
[while_label:] WHILE expr_condition DO statement_listEND WHILE [while_label]
DROP PROCEDURE IF EXISTS testWHILE;)DELIMITER //CREATE PROCEDURE testWHILE(IN start INT,IN end INT) BEGIN DECLARE sumResult INT DEFAULT 0; WHILE start <= end DO SET sumResult = sumResult + start; SET start=start+1; END WHILE; SELECT sumResult; END //DELIMITER ;call testWHILE(0,100);
declare 有标明 cursor for select_statement;
open 游标名;
declare 变量1 与对应列值相同的数据类型declare 变量2 与对应列值相同的数据类型declare 变量3 与对应列值相同的数据类型fetch next from 游标 [into 变量名1,变量名2,变量名3,...]
close 游标名;
deallocate 游标名;
delimiter //create procedure cursorTest01()begin-- 声明与对应列类型相同的4个变量declare studentID char(6);declare studentName varchar(50);declare studentAge int;declare studentGender varchar(50);-- 定义游标studentCursordeclare studentCursor cursor for select * from student;-- 打开游标open studentCursor;-- 使用游标fetch next from studentCursor into studentID,studentName,studentAge,studentGender;-- 显示结果select studentID,studentName,studentAge,studentGender;-- 关闭游标close studentCursor;end //delimiter ;-- 调用存储过程call cursorTest01();
drop procedure if exists cursorTest02;delimiter //create procedure cursorTest02()begin-- 声明与对应列类型相同的4个变量declare studentID char(6);declare studentName varchar(50);declare studentAge int;declare studentGender varchar(50);-- 声明计数器declare count int default 0;declare total int default 0;-- 定义游标studentCursordeclare studentCursor cursor for select * from student;set total = (select count(*) from student);-- 打开游标open studentCursor;-- 使用游标-- 利用 REPEAT 语句循环取出结果集中的数据REPEATfetch next from studentCursor into studentID,studentName,studentAge,studentGender;-- 显示结果select studentID,studentName,studentAge,studentGender;set count = count + 1;until count = totalend repeat;-- 关闭游标close studentCursor;end //delimiter ;-- 调用存储过程call cursorTest02();
来源地址:https://blog.csdn.net/weixin_53903929/article/details/132586107
--结束END--
本文标题: MySQL-存储过程、流程控制、游标
本文链接: https://www.lsjlt.com/news/428059.html(转载时请注明来源链接)
有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341
下载Word文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0