首页 / MYSQL / mysql-存储过程
mysql-存储过程
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了mysql-存储过程,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含4725字,纯文字阅读大概需要7分钟。
内容图文
、数据存储:把多个sql语句封装成一个方法 delimiter // 修改结束符 create procedure proc() begin select * from student; end // call 调用方法 变量的定义: declare 变量名 type default 默认值; 修改变量的值 set 变量名= 值 或 Drop procedure proc2; delimiter // Create procedure proc2() Begin Declare var1 varchar(10) default null; Declare var2 int default 0; select name ,age into var1, var2 from student where id =1; Result consisted of more than one row(这种错误出现的原因是没指定id 的值,结果多余变量的个数) Select var1; Select var2; End // Delimiter ; delimiter // drop procedure if exists proc2; Create procedure proc2() Begin Declare var1 varchar(10) default null; declare var2 int default 0; select name ,age into var1, var2 from student where id =1; Select var1 as 姓名, var2 as 年龄; End // Delimiter ; 1、存储过程: Delimiter // 修改结束符 Drop procedure if exists pro1; 判断,如果存在pro1 则删除 Create procedure pro1() 创建 procedure 存储过程 Begin End// Delimiter ; Call pro1; 2、游标: 语法: ( 声明游标 declare cursor_name cursor for select_statement; 打开游标(在使用游标之前) open cursor_name; 获取游标中的数据 into 变量 fetch cursor_name into var_name1,var_name2,...; 关闭游标(在使用游标之后) close cursor_name; ) delimiter // drop procedure if exists pro; create procedure pro() begin declare var varchar(20) default null; declare cursor_name cursor for select name from student where id=1; open cursor_name; fetch cursor_name into var; close cursor_name; select var as 姓名; end // delimiter ; call pro; procedure 程序、declare 声明、 delimiter // Drop procedure if exists pro// create procedure pro() begin declare var1 char(10) default null; declare var2 int default 0; declare cursor1 cursor for select name ,age from student where id=1; open cursor1; fetch cursor1 into var1, var2; close cursor1; select var1 as ‘姓名’, var2 as ‘年龄’; end // delimiter ; call pro; 3、存储过程中的if判断语句 语法: ( if ..... then ......; else if ...... then ......; else .......; end if; ) delimiter // drop procedure if exists pro// Create procedure pro() begin declare var int; if var is null then select ‘var is null‘ as 结果; Else select var as 结果; end if; Set var=10; If var is null then select ‘var is null‘ as 结果; Else select var as 结果; End if; End// Delimiter ; Call pro; 4、存储过程中的case判断语句 语法: ( case 要判断的变量 when .... then .... ; when .... then .... ; ....; else ....; end case; ) delimiter // drop procedure if exists pro; create procedure pro() begin declare var int default 0; set var=5; case var when 1 then select * from student where id=var; when 2 then select * from student where id=var; when 3 then select * from student where id=var; else select ‘no such case‘ as 结果; end case; end// delimiter ; call pro; 5、存储过程中的loop循环语句 语法: ( loop sql_statement; if ... then leave/iterate; end if; end loop; ) delimiter // drop procedure if exists pro// create procedure pro() begin declare var int default 0; myloop:loop insert student values(null,‘Rose‘,var,‘女‘); if var >=100 then leave myloop; end if; set var=var+1; end loop; end// delimiter ; call pro; 注意:在sql语句中没有 “==”,直接用“=”来作为判断等于的符号。 6、存储过程中的repeat循环语句 语法: ( repeat_label:repeat sql_statement; until ... end repeat repeat_label; ) delimiter // select * from student// drop procedure if exists pro; create procedure pro() begin declare var int default 1; my_repeat:repeat insert into student values(null,‘Rose‘,var,‘女‘); set var=var+1; until var>100 end repeat my_repeat; end// delimiter ; call pro; select * from student; 7、存储过程中的while循环 语法: ( while_label:while ...(循环条件) do sql_statement; end while while_label; ) delimiter // select * from student// drop procedure if exists pro// create procedure pro() begin declare var int default 719; my_while:while var<=818 do delete from student where id = var; set var=var+1; end while my_while; end// delimiter ; call pro; delimiter // drop procedure if exists pro// create procedure pro() begin declare var int default 1; my_while:while var<=100 do insert into student values(null,‘Rose‘,var,‘女‘); set var=var+1; end while my_while; end// delimiter ; call pro; 8、查看存储过程 语法: ( show {procedure/function} status [like ‘pattern‘]; show create procedure pro_name; ) show procedure status; show create procedure pro; 9、修改存储过程 10、删除存储过程 语法: ( drop {procedure/function} if exists pro_name; ) drop procedure if exists pro; 11、存储过程出错处理 定义错误情况 declare condition_name condition for condition_type;
mysql-存储过程
标签:sel label 默认值 delete int stat blog 循环 ...
本文系统来源:http://www.cnblogs.com/oural-yan/p/6952666.html
内容总结
以上是互联网集市为您收集整理的mysql-存储过程全部内容,希望文章能够帮你解决mysql-存储过程所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。