MYSQL——基础语法——存储过程

MYSQL——基础语法——存储过程

1.存储过程的产生原因

增强了SQL语言的灵活性和功能性。

提高了大量数据下操作的速度，比通过Java等第三方语言编写的效率高

而且可以离开服务进行数据的操作方便维护。

2.语法结构

CREATE PROCEDURE procedure_name() #也可以是代参数进程 
BEGIN
代码块
END
call procedure_name(); #调用函数

例

CREATE PROCEDURE procedure_name(in student_no char(50))
BEGIN
SELECT * FROM student WHERE sno=student_no;
END
call  procedure_name('001');

3.变量及赋

语法

declare var_name type(容量);

例

DECLARE result char(50) DEFAULT '结果';#初始赋值为 结果
set result ='没结果'

3.1 用户变量

特点：不需要提前声明，使用即声明

大量使用会破坏程序可读性

set @user ='用户变量';

3.2 系统变量

系统变量又分为****全局变量*与*会话变量****。

show session variables; #输出所有会话变量
show global variables; #输出所有全局变量

4.入参出参

4.1 入参

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

#in入参，虽然在进程中被修改 但是并不会影响 该参数原本的值
CREATE PROCEDURE procedure_name(in p_in int)
BEGIN 
SELECT p_in;
SET p_in=2;
SELECT p_in;
END
SET @p_in=1;
call procedure_name(@p_in);
SELECT @p_in

4.2 出参

OUT 输出参数:该值可在存储过程内部被改变，并可返回

 CREATE PROCEDURE demo_out_parameter(OUT p_out int)
 BEGIN
 SELECT p_out;
 SET p_out=2;
 SELECT p_out;
 END
 SET @p_out=1;
 CALL demo_out_parameter(@p_out);
 SELECT @p_out;

4.3入出参

INOUT 输入输出参数:调用时指定，并且可被改变和返回

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) ;

5.流程控制

5.1判断

1. if判断

语法

IF 条件 
then 
语句
elseif 条件
语句
then
语句
end if

例子

 CREATE PROCEDURE proc1(IN parameter1 INTEGER)
 BEGIN
 DECLARE variable1 CHAR(10);
 IF parameter1 < 18 THEN
 SET variable1 = 'boy';
 ELSE
 SET variable1 = 'man';
 END IF;
 SELECT variable1;
 END

2. case判断

语法1:

case var
when 0 then
语句1;
when 1 then
语句2;
wehn 3 then
语句3;
else
不符合条件语句;
end case;

例1：

CREATE PROCEDURE proc2 (in parameter int)
 begin
 declare var int;
 declare age int DEFAULT 18;
 set var=parameter+1;
 case var
 when 0 then
set age =20;
 when 1 then
set age=21;
 else
set age=18;
 end case;
 SELECT age;
 end;

语法2：

case 
when 条件 then
语句1;
when 条件 then
语句2;
wehn 条件 then
语句3;
else
不符合条件语句;
end case;

例2：

 CREATE PROCEDURE proc3 (in parameter int)
 begin
 declare var int ;
 DECLARE con char(50);
 set var=parameter+1;
 case 
 when var <18 then
set con ='未成年' ;
 when var<50 then
set con='壮年';
 else
set con='暮年';
 end case;
 SELECT con;
 end;

5.2循环

1. loop循环

loop循环不需要初始条件,是一个死循环，需要leave进行结束

loop_name:loop
循环内容
if 条件 then
leave loop_name;
end if;
end loop

例子

 CREATE PROCEDURE proc4 ()
 begin
 declare v int;
 set v=0;
 LOOP_LABLE:loop
 set v=v+1;
 if v >=5 then
 leave LOOP_LABLE;
 end if;
 end loop;
 end;

2. repeat循环

类似do while 循环 先执行在判断

repeat 
循环内容
until 条件
end repeat;

CREATE PROCEDURE proc5 ()
 begin
 declare v int;
 set v=0;
 repeat
 set v=v+1;
 until v>=5
 end repeat;
 SELECT v;
 end
 call proc5();

3. while循环

语法

while 条件 do
循环内容
end while;

例子

CREATE PROCEDURE proc4()
 begin
 declare var int;
 set var=0;
 while var<6 do
 set var=var+1;
end while;
SELECT var;
 end
 call proc4;

5.3流程控制——退出，继续循环

退出 leave

类似 java中的break;

需要配合label标签用。

label:流程	

leave label_name;

例子

CREATE PROCEDURE proc4()
 begin
 declare var int;
 set var=0;
 whiel: while var<6 do
 set var=var+1;
 if var=3 then 
 leave whiel;
 end if;
end while;
SELECT var;
 end
 call proc4;

继续循环 iterate

类似 java中的continue

需要配合label标签用。

leave label_name;

例子

CREATE PROCEDURE proc4()
 begin
 declare var int;
 set var=0;
 whiel: while var<6 do
 set var=var+1;
 if var=3 then 
 iterate whiel;
 end if;
end while;
SELECT var;
 end
 call proc4;

6.游标

类比Java 中的ResultSet 获得结果集

流程

--声明
DECLARE cursor_name CURSOR FOR 查询的语句
--打开
OPEN cursor_name;
--取值
FETCH cursor_name into 新建变量对象字段的值,...
--关闭
CLOSE cursor_name

例子

CREATE procedure pro7()
BEGIN
declare stu_no char(50);
declare stu_name char(50);
declare stu_ssex char(50);
DECLARE cursor_student CURSOR for 
SELECT sno,sname,ssex FROM student;
OPEN cursor_student;
FETCH cursor_student into stu_no,stu_name,stu_ssex;
SELECT stu_no,stu_name,stu_ssex;
END
call  pro7();
#出现的问题，一次遍历一个结果，需要使用循环进行遍历
#但是遍历不知道有多少 可以使用handler进行

7. handler

类似于：try catch捕获异常

declare [continue|exit] handler for [错误状态码|错误类型 No found] 报错后执行的代码

#continue 捕获错误后继续执行代码
#exit 捕获错误后结束运行

注意：在语法中，变量声明、游标声明，handler声明必须按照先后顺序书写否则报错*

CREATE procedure proc7(gender char(1))
BEGIN
#第一步：创建变量用于存储遍历的结果
declare s_name char(50);
declare s_dept char(50);
declare age int;
#创建一个状态：用于判断是否结束循环
declare status_stu boolean DEFAULT true;
#第二步：创建游标
declare student_cursor cursor for
SELECT stu.sname,stu.sdept,stu.sage FROM student stu WHERE ssex=gender;
#第三步：创建handler 并改变状态码
declare continue HANDLER FOR Not found set status_stu=false;
#第四步：打开handler
open student_cursor;
#创建循环遍历
stu_loop:loop
FETCH student_cursor into s_name,s_dept,age;
if status_stu then
SELECT s_name,s_dept,age;
ELSE
#关闭游标，退出循环
CLOSE student_cursor;
leave stu_loop;
end if;
end loop;
END
call proc7('男');

8.常用函数

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个字符,

(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为小数位数]

(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) //小时