首页 / MYSQL / MySql存储过程的作用及语法
MySql存储过程的作用及语法
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySql存储过程的作用及语法，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含14990字，纯文字阅读大概需要22分钟。
内容图文
MySQL使用存储过程的作用
1、使用了存过程，很多相似性的删除，更新，新增等操作就变得轻松了，并且以后也便于管理！
2、存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。
3、存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因。
4、存储过程运行比较稳定，不会有太多的错误。只要一次成功，以后都会按这个程序运行。
5、存储过程主要是在服务器上运行，减少对客户机的压力。
6、存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。
7、存储过程可以在单个存储过程中执行一系列SQL语句。
8、存储过程可以从自己的存储过程内引用其它存储过程，这可以简化一系列复杂语句。
MySQL存储过程的创建
先定义一个结束分隔符，控制sql语句的执行
delimiter $ //意思是用$作为sql语句的结束符
参数
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回
①IN参数例子
创建:
DELIMITER $ CREATE PROCEDURE demo(IN p_in int) BEGIN SELECT p_in; SET p_in=2; SELECT p_in; END$
执行结果:
SET @p_in=1; CALL demo(@p_in); +------+ | p_in | +------+ | 1 | +------+ +------+ | p_in | +------+ | 2 | +------+ SELECT @p_in; +-------+ | @p_in | +-------+ | 1 | +-------+
以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值
②OUT参数例子
创建:
DELIMITE $ CREATE PROCEDURE demo(OUT p_out int) BEGIN SELECT p_out; SET p_out=2; SELECT p_out; END$
执行结果:
SET @p_out=1; CALL demo(@p_out); +-------+ | p_out | +-------+ | NULL | +-------+ +-------+ | p_out | +-------+ | 2 | +-------+ SELECT @p_out; +-------+ | p_out | +-------+ | 2 | +-------+
③INOUT参数例子
创建:
DELIMITER $ CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int) BEGIN SELECT p_inout; SET p_inout=2; SELECT p_inout; END$
执行结果:
SET @p_inout=1; CALL demo_inout_parameter(@p_inout) ; +---------+ | p_inout | +---------+ | 1 | +---------+ +---------+ | p_inout | +---------+ | 2 | +---------+ SELECT @p_inout; +----------+ | @p_inout | +----------+ | 2 | +----------+
变量
①定义变量
例如:
DECLARE l_int int unsigned default 4000000; DECLARE l_numeric number(8,2) DEFAULT 9.95; DECLARE l_date date DEFAULT ‘1999-12-31‘; DECLARE l_datetime datetime DEFAULT ‘1999-12-31 23:59:59‘; DECLARE l_varchar varchar(255) DEFAULT ‘This will not be padded‘;
②变量赋值
SET 变量名 = 表达式值 [,variable_name = expression ...]
③用户变量
ⅰ. 在MySQL客户端使用用户变量
SELECT ‘Hello World‘ into @x; SELECT @x; +-------------+ | @x | +-------------+ | Hello World | +-------------+ SET @y=‘Goodbye Cruel World‘; SELECT @y; +---------------------+ | @y | +---------------------+ | Goodbye Cruel World | +---------------------+ SET @z=1+2+3; SELECT @z; +------+ | @z | +------+ | 6 | +------+
ⅱ. 在存储过程中使用用户变量
CREATE PROCEDURE GreetWorld( ) BEGIN SELECT CONCAT(@greeting,‘ World‘); SET @greeting=‘Hello‘; END$ CALL GreetWorld( ); +----------------------------+ | CONCAT(@greeting,‘ World‘) | +----------------------------+ | Hello World | +----------------------------+
④MySQL存储过程的修改
ALTER PROCEDURE
更改用CREATE PROCEDURE 建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。
⑤MySQL存储过程的删除
删除一个存储过程比较简单，和删除表一样：
DROP PROCEDURE
从MySQL的表格中删除一个或多个存储过程。
⑥MySQL存储过程的控制语句
(1). 变量作用域
内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储
过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派
给会话变量来保存其值。
CREATE PROCEDURE proc3() begin declare x1 varchar(5) default ‘outer‘; begin declare x1 varchar(5) default ‘inner‘; select x1; end; select x1; end$
(2). 条件语句
A:if-then -else语句
CREATE PROCEDURE proc2(IN parameter int) begin declare var int; set var=parameter+1; if var=0 then insert into t values(17); end if; if parameter=0 then update t set s1=s1+1; else update t set s1=s1+2; end if; end$
B:case语句：
CREATE PROCEDURE proc3 (in parameter int) begin declare var int; set var=parameter+1; case var when 0 then insert into t values(17); when 1 then insert into t values(18); else insert into t values(19); end case; end$
C:loop ·····end loop:
loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。
CREATE PROCEDURE proc6 () begin declare v int; set v=0; LOOP_LABLE:loop insert into t values(v); set v=v+1; if v >=5 then leave LOOP_LABLE; end if; end loop; end$
D：while语句 while ···· end while：
CREATE PROCEDURE proc4() begin declare var int; set var=0; while var<6 do insert into t values(var); set var=var+1; end while; end$
E:repeat 语句 repeat···· end repeat：
它在执行操作后检查结果，而while则是执行前进行检查。
CREATE PROCEDURE proc5 () begin declare v int; set v=0; repeat insert into t values(v); set v=v+1; until v>=5 end repeat; end$
⑦ITERATE迭代
ITERATE:
通过引用复合语句的标号,来从新开始复合语句
CREATE PROCEDURE proc10 () begin declare v int; set v=0; LOOP_LABLE:loop if v=3 then set v=v+1; ITERATE LOOP_LABLE; end if; insert into t values(v); set v=v+1; if v>=5 then leave LOOP_LABLE; end if; end loop; end$
MySQL存储过程的基本函数
(1).字符串类
CHARSET(str) //返回字串字符集 CONCAT (string2 [,... ]) //连接字串 INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0 LCASE (string2 ) //转换成小写 LEFT (string2 ,length ) //从string2中的左边起取length个字符 LENGTH (string ) //string长度 LOAD_FILE (file_name ) //从文件读取内容 LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置 LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length LTRIM (string2 ) //去除前端空格 REPEAT (string2 ,count ) //重复count次 REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length RTRIM (string2 ) //去除后端空格 STRCMP (string1 ,string2 ) //逐字符比较两字串大小, SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
select substring(‘abcd‘,0,2); +-----------------------+ | substring(‘abcd‘,0,2) | +-----------------------+ +-----------------------+ select substring(‘abcd‘,1,2); +-----------------------+ | substring(‘abcd‘,1,2) | +-----------------------+ | ab | +-----------------------+
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符 UCASE (string2 ) //转换成大写 RIGHT(string2,length) //取string2最后length个字符 SPACE(count) //生成count个空格
(2).数学类
ABS (number2 ) //绝对值 BIN (decimal_number ) //十进制转二进制 CEILING (number2 ) //向上取整 CONV(number2,from_base,to_base) //进制转换 FLOOR (number2 ) //向下取整 FORMAT (number,decimal_places ) //保留小数位数 HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(‘DEF‘)返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2 [,..]) //求最小值 MOD (numerator ,denominator ) //求余 POWER (number ,power ) //求指数 RAND([seed]) //随机数 ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]
注：返回类型并非均为整数，如：
(1)默认变为整形值
select round(1.23); +-------------+ | round(1.23) | +-------------+ | 1 | +-------------+ select round(1.56); +-------------+ | round(1.56) | +-------------+ | 2 | +-------------+
(2)可以设定小数位数，返回浮点型数据
select round(1.567,2); +----------------+ | round(1.567,2) | +----------------+ | 1.57 | +----------------+
(3).日期时间类
ADDTIME (date2 ,time_interval ) //将time_interval加到date2 CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区 CURRENT_DATE ( ) //当前日期 CURRENT_TIME ( ) //当前时间 CURRENT_TIMESTAMP ( ) //当前时间戳 DATE (datetime ) //返回datetime的日期部分 DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间 DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间 DATEDIFF (date1 ,date2 ) //两个日期差 DAY (date ) //返回日期的天 DAYNAME (date ) //英文星期 DAYOFWEEK (date ) //星期(1-7) ,1为星期天 DAYOFYEAR (date ) //一年中的第几天 EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分 MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串 MAKETIME (hour ,minute ,second ) //生成时间串 MONTHNAME (date ) //英文月份名 NOW ( ) //当前时间 SEC_TO_TIME (seconds ) //秒数转成时间 STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示 TIMEDIFF (datetime1 ,datetime2 ) //两个时间差 TIME_TO_SEC (time ) //时间转秒数] WEEK (date_time [,start_of_week ]) //第几周 YEAR (datetime ) //年份 DAYOFMONTH(datetime) //月的第几天 HOUR(datetime) //小时
视频学习中的例子
1
create procedure p1() begin select ‘hello‘ from dual; end$ call p1()$
2
create procedure p2() begin declare age int default 18; declare height int default 180; select concat(‘年龄是‘,age,‘身高是‘,height); end$
3
create procedure p3() begin declare age int default 18; set age:= age+20; select concat(‘20年后年龄是‘,age); end$
4
create procedure p4() begin declare age int default 18; if age >=18 then select ‘成年‘; else select ‘未成年‘; end if; end$
5
create procedure p5(width int ,height int) begin select concat(‘你的面积是‘, width * height ) as area; if width >height then select ‘你挺胖‘;; elseif width < height then select ‘你挺瘦’; else select ‘你挺方‘; endif; end$
6
create procedure p6() begin declare total int default 0; declare num int default 0; while num <100 do set num:=num+1; set total:= total +num; end while; select total; end$
7
create procedure p7(in n int) begin declare total int default 0; declare num int default 0; while num <n do set num:=num+1; set total:= total +num; end while; select total; end$
8
create procedure p8(in n int ,out total int) begin declare num int default 0; set total :=0; #null 碰到任何操作都会变成null while num<n do set num:=num+1; set total :=total+num; end while; end$ call p8(100,@summary)$ select @summary
9
create procedure p9(inout age int) begin set age:=age +20; end$ set @currage =18$ call p9(@currage)$ select @currage$
10
create procedure p10() begin declare pos int default 0; set pos := floor(5*rand()); case pos when 1 then select ‘still flying‘; when 2 then select ‘fall in sea‘; when 3 then select ‘in the island‘; else select ‘I don‘t know‘; end case; end$
11
create procedure p11() begin declare total int default 0; declare i int default 0; repeat set i:=i+1; set total :=total +i; until i>=100 end repeat; select total; end$
12 游标
create procedure p12() begin declare row_gid int; declare row_num int; declare row_name varchar(20); declare getgoods cursor for select gid,num,name from goods; open getgoods; fetch getgoods into row_gid,row_num,row_name; select row_num,row_name; close getgoods; end$
13
create procedure p13() begin declare row_gid int; declare row_num int; declare row_name varchar(20); declare getgoods cursor for select gid, num , name from goods; open getgoods; fetch getgoods into row_gid,row_num,row_name; #cat select row_num,row_name; fetch getgoods into row_gid,row_num,row_name;#dog select row_num,row_name; fetch getgoods into row_gid,row_num,row_name;#pig select row_num,row_name; fetch getgoods into row_gid,row_num,row_name;#error select row_num,row_name; close getgoods; end$
#逻辑处理游标越界
14
create procedure p14() begin declare row_gid int; declare row_num int; declare row_name varchar(20); declare cnt int default 0; declare i int default 0; declare getgoods cursor for select gid, num , name from goods; select count(*) into cnt from goods; open getgoods; repeat set fetch getgoods into row_gid,row_num,row_name; #cat select row_num,row_name; until i >= cnt end repeat; close getgoods; end$
15
游标越界的时候，在mysql cursor中有一个标示，可以declare continue handler 来操作一个越界标示
declare continue handler for NOT FOUND statement; create procedure p15() begin declare row_gid int; declare row_num int; declare row_name varchar(20); declare you int default 1; declare getgoods cursor for select gid, num , name from goods; select count(*) into cnt from goods; declare continue handler for NOT FOUND set you :=0; //如果发生not found事件，把you 改为 0 open getgoods; repeat set fetch getgoods into row_gid,row_num,row_name; select row_num,row_name; until you=0 end repeat; close getgoods; end$ call p15()$ show warnings$
16：上条BUG修复，continue和 exit的区别（exit触发后后面的代码不在执行，而continue继续执行）
create procedure p16() begin declare row_gid int; declare row_num int; declare row_name varchar(20); declare you int default 1; declare getgoods cursor for select gid, num , name from goods; select count(*) into cnt from goods; declare exit handler for NOT FOUND set you :=0;#这里的handler类型改成了exit open getgoods; repeat set fetch getgoods into row_gid,row_num,row_name; select row_num,row_name; until you=0 end repeat; close getgoods; end$
17：正确严谨的逻辑
#接下来的存储过程才应该是游标的正确使用方式：
#一定要用continue handler ，通过逻辑来控制。
create procedure p17() begin declare row_gid int; declare row_num int; declare row_name varchar(20); declare you int default 1; declare getgoods cursor for select gid, num , name from goods; select count(*) into cnt from goods; declare continue handler for NOT FOUND set you :=0; open getgoods; fetch getgoods into row_gid,row_num,row_name; repeat set select row_num,row_name; fetch getgoods into row_gid,row_num,row_name; until you=0 end repeat; close getgoods; end$
#换成while 循环
18
create procedure p18() begin declare row_gid int; declare row_num int; declare row_name varchar(20); declare you int default 1; declare getgoods cursor for select gid, num , name from goods; select count(*) into cnt from goods; declare continue handler for NOT FOUND set you :=0; open getgoods; fetch getgoods into row_gid,row_num,row_name; while you =1 do select row_num,row_name; fetch getgoods into row_gid,row_num,row_name; end while; close getgoods; end$
MySql存储过程的作用及语法
标签：进制转换 参数 显示 gre ror 修改 select 文件读取 char
本文系统来源：http://www.cnblogs.com/wt645631686/p/6868170.html
内容总结
以上是互联网集市为您收集整理的MySql存储过程的作用及语法全部内容，希望文章能够帮你解决MySql存储过程的作用及语法所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】