首页 / MYSQL / MySQL存储过程学习记录
MySQL存储过程学习记录
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL存储过程学习记录,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4498字,纯文字阅读大概需要7分钟。
内容图文
-- 创建存储过程 -- DELIMITER // CREATE PROCEDURE pro_first() BEGIN select NOW(); END -- // -- DELIMITER ; -- 删除存储过程 DROP PROCEDURE pro_first; -- 查看指定存储过程 SHOW CREATE PROCEDURE pro_first; -- 查看所有存储过程 SHOW PROCEDURE STATUS; -- 调用存储过程 CALL pro_first(); -- 练习 create procedure pro_practise() BEGIN select 1+3 from dual; END call pro_practise(); -- 变量 CREATE procedure pro_variable() BEGIN -- 声明局部变量 DECLARE num1 int; DECLARE username VARCHAR(20) DEFAULT '张三'; select num1, username; END CALL pro_variable(); -- 变量赋值 CREATE PROCEDURE pro_variable2() BEGIN -- 声明局部变量 DECLARE num1 int; DECLARE num2 int; DECLARE result int; -- 变量赋值 SET num1 = 10; SET num2 := 15; SET result = num1 + num2; select result; END CALL pro_variable2(); -- 定义用户变量(注意:使用用户变量的时候,不需要声明数据类型) set @userVar := '我是用户变量'; CREATE PROCEDURE pro_testuservar() BEGIN set @userVar = '用户变量值改了'; select @userVar; END DROP PROCEDURE pro_testuservar; CALL pro_testuservar(); CREATE PROCEDURE pro_testuservar2() BEGIN select @userVar; END CALL pro_testuservar2(); -- select into 语句(注意:使用时要保证select语句查回的数据只有一条) CREATE PROCEDURE pro_selectinto() BEGIN DECLARE vcity VARCHAR(40); select city into vcity from extend_mobile where pre = '1300000'; select vcity; END DROP PROCEDURE pro_selectinto; CALL pro_selectinto(); -- 练习 create PROCEDURE pro_selectinto_practise() BEGIN DECLARE vpre int; DECLARE vprovice VARCHAR(40); DECLARE vcity VARCHAR(40); select pre, provice, city into vpre, vprovice, vcity from extend_mobile where pre = '1300000'; select vpre, vprovice, vcity; END CALL pro_selectinto_practise(); -- 参数 in (不写,默认是in 输入参数) CREATE procedure pro_param(in p_pre int) BEGIN DECLARE vcity VARCHAR(40); SELECT city into vcity from extend_mobile where pre = p_pre; select vcity; END CALL pro_param('1300001'); -- 参数out set @provice = ''; create PROCEDURE pro_param_out(out p_out VARCHAR(40), in p_in INT) BEGIN select provice into p_out from extend_mobile where pre = p_in; END CALL pro_param_out(@provice, '1300002'); select @provice; -- 参数inout CREATE PROCEDURE pro_param_inout(INOUT num int) BEGIN SET num = num * 5; END set @userNum = 10; select @userNum; CALL pro_param_inout(@userNum); -- if语句1 CREATE PROCEDURE pro_if(in p_pre int) BEGIN DECLARE vcode int; select code into vcode from extend_mobile where pre = p_pre; IF vcode = 10 THEN select 1; ELSE select 2; END IF; END CALL pro_if('1300001'); -- if语句2 CREATE PROCEDURE pro_if2(in p_pre int) BEGIN DECLARE vcity VARCHAR(40); select city into vcity from extend_mobile where pre = p_pre; IF vcity = '北京' THEN select 1; ELSEIF vcity = '常州' THEN select 2; ELSE select 3; end if; END CALL pro_if2('1300002'); -- case when 语句 CREATE PROCEDURE pro_case_when(in p_pre int) BEGIN DECLARE vcity VARCHAR(40); select city into vcity from extend_mobile where pre = p_pre; CASE vcity when '北京' THEN select 1; when '常州' THEN select 2; ELSE select 3; END CASE; END CALL pro_case_when('1300002'); CREATE PROCEDURE pro_case_when2(in p_pre int) BEGIN DECLARE vcity VARCHAR(40); select city into vcity from extend_mobile where pre = p_pre; CASE when vcity = '北京' THEN select 1; when vcity = '常州' THEN select 2; ELSE select 3; END CASE; END CALL pro_case_when2('1300002'); -- case when 用于普通查询语句 select id, name, case sex when 0 then '女' when 1 then '男' else '未知' end as gender from t_person; -- while语句 create procedure pro_while() BEGIN DECLARE i INT DEFAULT 1; DECLARE sum INT DEFAULT 0; WHILE i < 11 DO set sum = sum + i; set i = i + 1; END WHILE; select sum; END CALL pro_while(); -- REPEAT语句 create PROCEDURE pro_repeat() BEGIN DECLARE i INT DEFAULT 1; DECLARE sum INT DEFAULT 0; REPEAT SET sum = sum + i; SET i = i + 1; until i > 10 END REPEAT; select sum; END CALL pro_repeat(); -- LOOP语句 CREATE PROCEDURE pro_loop() BEGIN DECLARE i INT DEFAULT 1; DECLARE sum INT DEFAULT 0; looplabel: LOOP SET sum = sum + i; SET i = i + 1; IF i > 10 THEN LEAVE looplabel; -- 跳出循环 END IF; END LOOP looplabel; select sum; END CALL pro_loop(); -- JDBC调用存储过程 CREATE PROCEDURE pro_jdbc(in p_pre int, OUT p_city VARCHAR(40)) BEGIN SELECT city into p_city from extend_mobile where pre = p_pre; END set @uCity = ''; CALL pro_jdbc('1300000', @uCity); SELECT @uCity; drop PROCEDURE pro_jdbc -- 登录业务 p_flag: 0 失败 1 成功 CREATE PROCEDURE pro_login(in p_username VARCHAR(255), in p_password VARCHAR(255), out p_flag INT) BEGIN DECLARE count int; SELECT count(*) into count FROM t_user where f_name = p_username and f_password = p_password; IF count = 1 THEN SET p_flag = 1; ELSE SET p_flag = 0; END IF; END
内容总结
以上是互联网集市为您收集整理的MySQL存储过程学习记录全部内容,希望文章能够帮你解决MySQL存储过程学习记录所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。