MySQL function & procedure

2022-02-01 08:56:01

show procedure status where db=‘employees‘\G

alter procedure sp sql security invoker;

select * from information_schema.Routines where ROUTINE_NAME=‘sp_name‘\G

alter procedure ui modifies sql data sql security invoker comment ‘uiop‘;

contains sql(default)
no sql
reads sql data
modifies sql data

sql security { definer | invoker }

procedure输出变量,直接select

CREATE DEFINER=`root`@`%` PROCEDURE `nm`()
BEGIN
    #Routine body goes here...
    declare b int default 11;
    select b;

END

function输出变量,必须使用return,直接select会被认为返回的是结果集

CREATE DEFINER=`root`@`%` FUNCTION `nm`() RETURNS int
    NO SQL
BEGIN
    #Routine body goes here...
    declare b int default 22;

    return (select b);
END

function与procedure的区别,函数只会返回一个值,不允许返回一个结果集,函数强调返回值,所以函数不允许返回多个值,即时是查询语句

CREATE DEFINER=`root`@`%` FUNCTION `cc`() RETURNS int
BEGIN
    #Routine body goes here...
    select emp_no into @b from employees where emp_no=10060;

    RETURN @b;
END

CREATE DEFINER=`root`@`%` FUNCTION `cc`() RETURNS int
BEGIN
    #Routine body goes here...
    declare b int default null;
    select emp_no into b from employees where emp_no=10060;

    RETURN b;
END

局部变量b需使用declare最先声明

传参

CREATE DEFINER=`root`@`%` FUNCTION `cc`(lastName varchar(60)) RETURNS int
    DETERMINISTIC
BEGIN
    #Routine body goes here...
    declare b int default null;
    select emp_no into b from employees where last_name=lastName collate utf8mb4_general_ci;

    RETURN b;
END

使用navicat查询时,出现如下错误,故使用collate明确指定collation
MySQL function & procedure

no sql function

CREATE DEFINER=`root`@`%` FUNCTION `cc`(x int,y int) RETURNS int
    NO SQL
BEGIN
    #Routine body goes here...
    declare b int default null;
    set b=x+y;
    RETURN b;
END

使用用户变量,累加求和

CREATE DEFINER=`root`@`%` FUNCTION `cc`(x int) RETURNS int
    READS SQL DATA
BEGIN
    #Routine body goes here...
    set @i=1;
    set @sum=0;
    while @i<=x do
        set @sum=@sum+@i;
        set @i=@i+1;
    end while;
    return @sum;
END

使用局部变量,累加求和,跳过5的倍数

CREATE DEFINER=`root`@`%` FUNCTION `cc`(x int) RETURNS int
    READS SQL DATA
BEGIN
    #Routine body goes here...
    declare i int default 1;
    declare sum int default 0;
    
    sumwhile:while i <= x do
        if i % 5 =0 then
            set i=i+1;
            iterate sumwhile;
        end if;
        
        set sum=sum+i;
        set i=i+1;
    end while;
    return sum;
END

条件判断 if

CREATE DEFINER=`root`@`%` PROCEDURE `nm`(n int,_gender char(1) charset utf8mb4 collate utf8mb4_general_ci)
BEGIN
    #Routine body goes here...
    if _gender=‘m‘ then
        select * from employees where gender=_gender limit n;
    elseif _gender=‘f‘ then
        select * from employees where gender=_gender limit n;
    else
        select * from employees limit n;
    end if;
END

CREATE DEFINER=`root`@`%` PROCEDURE `loop4`(in x int)
BEGIN
    #Routine body goes here...
    if x=3 then select * from employees where emp_no like concat(‘%‘,x) limit x;
    elseif x=4 then select * from employees where emp_no like concat(‘%‘,x) limit x;
    elseif x=5 then select * from employees where emp_no like concat(‘%‘,x) limit x;
    else select * from employees limit x;
    end if;

END

case 有两种格式

条件判断

CREATE DEFINER=`root`@`%` PROCEDURE `nm`(x int)
BEGIN
    #Routine body goes here...
    declare p int;
    set p=8;
    
    case 
    when x<p then select * from employees limit x;
    when x=p then select * from dept_emp limit x;
    when x>p then select * from departments limit x;
    else select ‘x is invalid‘;
    end case;
END

CREATE DEFINER=`root`@`%` PROCEDURE `oo`(in x int)
BEGIN
    #Routine body goes here...
    declare y char(11) default null;
    select gender into y from employees where emp_no=x;
    case
    when y=‘m‘ then update employees set first_name=‘qwert‘ where emp_no=x;
    when y=‘f‘ then update employees set first_name=‘zxcc‘ where emp_no=x;
    else
    update employees set first_name=‘Unkown‘ where emp_no=x;
    end case;
    
END

单值判断

CREATE DEFINER=`root`@`%` PROCEDURE `nm`(x int)
BEGIN
    #Routine body goes here...
    declare p int;
    set p=8;
    
    case x
    when 5 then select * from employees limit x;
    when 6 then select * from dept_emp limit x;
    when 7 then select * from departments limit x;
    else select ‘x is invalid‘;
    end case;
END

CREATE DEFINER=`root`@`%` PROCEDURE `loop4`(in x int)
BEGIN
    #Routine body goes here...
    declare v int default null;
    select gender into v from employees where emp_no=x;
    
    case v
    when 1 then update employees set first_name=‘uiop‘ where emp_no=x;
    when 2 then update employees set first_name=‘vbnm‘ where emp_no=x;
    else
    update employees set first_name=‘Unkown‘ where emp_no=x;
    end case;
    
END

loop

CREATE DEFINER=`root`@`%` PROCEDURE `nm`()
BEGIN
    #Routine body goes here...
    declare i int default 0;
    
    add_loop:loop
    set i=i+1;
    if i>=10 then 
        leave add_loop;
    end if;
    end loop add_loop;
    
    select i;
END

CREATE DEFINER=`root`@`%` PROCEDURE `nm`()
BEGIN
    #Routine body goes here...
    declare i int default null;
    set i=0;
    
    _loop:loop
        insert into p (name) values (concat(‘zxc‘,i));
        set i=i+1;
        if i>=5 then
            leave _loop;
        end if;
    end loop _loop;
    
    select * from p;
    
END

leave
基本格式

leave LABEL

CREATE DEFINER=`root`@`%` PROCEDURE `nm`()
BEGIN
    #Routine body goes here...
    set @count=0;
    
    add_loop:loop
    set @count=@count+1;
    if @count=50 then
        leave add_loop;
    end if;
    end loop add_loop;
    
END

iterate
iterate 语句将执行顺序转到label位置,格式如下,只可以出现在loop,repeat,while内

iterate LABEL

CREATE DEFINER=`root`@`%` PROCEDURE `nm`()
BEGIN
    #Routine body goes here...
    declare b int default 0;
    
    my_loop:loop
    set b=b+1;
    
    if b<10 then
        iterate my_loop;
    elseif b>20 then
        leave my_loop;
    end if;
    
    select b;
    
    end loop my_loop;
    
END

repeat 类似于 do while

CREATE DEFINER=`root`@`%` PROCEDURE `nm`()
BEGIN
    #Routine body goes here...
    declare i int default 0;
    set i=0;
    
    _repeat:repeat
        set i=i+1;
    until i>10
    end repeat _repeat;
    
    select i;
    
END

while

CREATE DEFINER=`root`@`%` PROCEDURE `nm`(in n smallint)
BEGIN
    #Routine body goes here...
    declare i int default 0;
    declare s int default 0;
    
    while i <= n do
        set s=s+i,i=i+1;
    end while;
    
    select s;
    
END

cursor 游标

CREATE DEFINER=`root`@`%` PROCEDURE `nm`(in n smallint)
BEGIN
    #Routine body goes here...
    declare b int default null;
    declare p date default null;
    declare i int default 0;
    declare _cursor cursor for select emp_no,birth_date from employees limit n;
    
    open _cursor;
    
    my_loop:loop
        if i>=5 then
            leave my_loop;
        end if;
        set i=i+1;
        fetch _cursor into b,p;
        select b,p;
    end loop my_loop;
    
    close _cursor;
    
END

使用需要加判断,否则会出现如下错误
MySQL function & procedure

存储过程输入参数 & 输出参数

CREATE DEFINER=`root`@`%` PROCEDURE `nm`(in x int,out y smallint)
BEGIN
    #Routine body goes here...
    select count(*) into y from employees where emp_no < x;
    
END

调用

call nm(10060,@b);
select @b;

自定义字符串处理函数

CREATE DEFINER=`root`@`%` FUNCTION `nm`(b varchar(60)) RETURNS varchar(80) CHARSET utf8mb4
    NO SQL
BEGIN
    #Routine body goes here...
    return concat(‘uioop ‘,b,‘1‘);
END
select nm(‘vbnm‘) into @vv;
select @vv as string;

rand() 向表中插入随机数值

CREATE DEFINER=`root`@`%` PROCEDURE `nm`(in x int)
BEGIN
    #Routine body goes here...
    declare i int default 0;
    declare gender tinyint;
    declare dept tinyint;
    set i=1;
    
    while i <= x do
        set gender=round(rand()+1);
        set dept=floor(rand()*3+1);
        
        insert into zz (id,gender,dept) values (i,gender,dept);
        
        set i=i+1;
    end while;
        
END

round(rand()+1) 1 or 2
floor(rand()*3+1) 1 or 2 or 3

MySQL function & procedure

将查询到的值赋予用户变量

CREATE DEFINER=`root`@`%` PROCEDURE `nm`(in x int)
BEGIN
    #Routine body goes here...
    select avg(emp_no),max(emp_no) into @x,@y from (select emp_no from employees limit x) as m;
END

生成临时表,并插入1000条数据

CREATE DEFINER=`root`@`%` PROCEDURE `nm`(in x int)
BEGIN
    #Routine body goes here...
    declare i int default 0;
    set @table_name=concat(‘b‘,x);
    set @x=x;
    -- drop table 
    set @sqlStr=concat(‘drop table if exists ‘,@table_name);
    prepare stmt from @sqlStr;
    execute stmt;
    deallocate prepare stmt;
    
    -- create table
    set @sqlStr=concat(‘create table ‘,@table_name,‘ (c‘,@x,‘ int)‘);
    select @sqlStr;
    prepare stmt from @sqlStr;
    execute stmt;
    deallocate prepare stmt;
    
    -- generate data
    set i=1;
    _while:while i <= 1000 do
        set @sqlStr=concat(‘insert into ‘,@table_name,‘ values (?)‘);
        prepare stmt from @sqlStr;
        set @i=i;
        execute stmt using @i;
        deallocate prepare stmt;
        set i=i+1;
    end while _while;
        
END

占位符的使用

CREATE DEFINER=`root`@`%` PROCEDURE `loop4`(in x varchar(44),in y int)
BEGIN
    #Routine body goes here...
    set @table_name=x;
    set @y=y;
    set @sqlStr=concat(‘select * from ‘,@table_name,‘ limit ?‘);
    prepare stmt from @sqlStr;
    execute stmt using @y;
    deallocate prepare stmt;

END

MySQL function & procedure

码农公寓

相关文章