首页 > 数据库 >九,MYSQL之存储过程,实际就是用slq写函数,封装方法

九,MYSQL之存储过程,实际就是用slq写函数,封装方法

时间：2024-07-09 13:01:49浏览次数：18  
标签：varchar name slq delimiter MYSQL 封装 my declare select

目录

一,概念

        1,介绍.

        2,特性

        3,作用

二,格式

        简单的存储过程案例

基本格式:

三,变量

        1,局部变量

        2,变量赋值

         3,会话变量

 

3,系统变量

四,参数

        1,in参数        

        2,out参数

        3,inout参数

五,条件分支

        1,if语句判断条件

        2 ,case语句判断条件

六,循环

1,while循环

leave 用法

        iterate用法 相当于python中的 continue

2,repeat循环

3,loop循环

七,游标

八,异常处理

一,概念

        1,介绍.

        存储过程其实就是python中的函数.        

        从mysql5.0以上开始支持存储过程.存储过程就是一组sql语句集,内部可以实现比较复杂的逻辑功能,可以输入参数,可以输出参数.

        相当于编程语言(比如python,比如JavaScript)中的函数用法.

        存储过程就是对数据库sql语言层面的代码进行模块化的封装和复用.

        2,特性

         封装性：即将一系列的操作或计算封装在一个可重用的单元中。这样，你可以通过调用这个单元（存储过程或函数）来执行封装好的代码，而不需要每次都重写相同的逻辑。

        参数传递：可以接受参数，也可以返回结果（对于函数来说通常是单个值，而存储过程可以返回多个值，尽管方式可能不同）。

        可重用性：两者都是为了提高代码的可重用性而设计的。

        3,作用

        除了首次执行需要编译和优化步骤,后续可以直接调用.

二,格式

        简单的存储过程案例

       假如有一个emp表


create database my_def;  #建库
use my_def;   #建表


delimiter $$$   #自定义分割符号,这个符号表示存储过程从这个符号这里开始,结束的时候也从这个符号结束
create procedure my_fn()  #定义存储过程名称
begin   #开始
    select * from emp;  #内部执行的sql语句
end $$$   #存储过程结束
delimiter;  #恢复sql默认的分隔符号  ;

call my_fn();   #调用这个存储过程

 以上代码 call 调用这个存储过程就可以查询emp表所有的信息.

基本格式:

delimiter 自定义分隔符号   #这个符号必须和end的分割符号相等
create procedure 函数名称(参数1,参数2,参数3……)
begin
    sql 语句;
end 自定义分割符号   #必须和之前的分割符号相等
delimiter ;

三,变量

        在存储过程中也支持自定义变量.

        变量和其他编程语言一样,也分局部和全局.

        1,局部变量

                可以自定义,但是仅仅在begin和end之间生效,外部是无法访问的.

        语法:

declare 变量名 类型 [default 默认值];
#示例一
declare var_flag int default 1;
#示例二
declare var_name varchar(20)

          案例

---------------------------定义局部变量---------------------
delimiter ##
create procedure my_fn1()
begin
    declare name varchar(20) default '凡梦';
    select name;
end ##
delimiter;

call my_fn1()

 结果

               2,变量赋值

                语法:set 变量=值

----------------修改局部变量--------------------
delimiter $$
create procedure my_fn2()
begin
    declare name varchar(20) default '林宝玉';
    set name = '林黛玉';
    select name;
end $$
delimiter ;
call  my_fn2()

结果

        3,变量赋值二

        从sql语句里面赋值

        语法;select name into 变量名 from 表名;

假如有一个部门表 里面有个id是1001的人

--------------查询的时候带赋值------------
delimiter $$
create procedure my_fn3()
begin
    declare name1 varchar(30);
    select ename into name1 from emp where empno='1001';
    select name1;
end $$
delimiter ;
call  my_fn3();

结果

查询的时候带赋值并且修改值

----------------查询的时候带赋值并且修改值---------------
delimiter $$
create procedure my_fn4()
begin
    declare name1 varchar(30);
    select ename into name1 from emp where empno='1001';
    set name1='甘宁1';
    select name1;
end $$
delimiter ;
call  my_fn4();

         3,会话变量

        用户自定义的变量,当前会话 连接有效,关闭本次会话,则变量失效.这个变量是存在内存之中的.

语法:  @变量名     会话变量不需要提前声明,使用即声明.

        会话变量可以在存储过程中使用,也可以在存储过中外使用.

-------------------定义会话变量-----------@变量名--------
--当前会话生效 变量存储在内存中
delimiter $$   --定义函数开始的符号
create procedure my_fn5()
begin
    set @var_name='张三';  --设置全局变量 也就是会话变量
    select @var_name;  ---查询这个会话变量
end $$;  --符号结束
delimiter ;  --恢复到默认的分隔符号
call my_fn5();  ---调用函数
select @var_name; ----外面查看全局变量的值

      

会话变量对子查询的应用

--------------------子查询对变量的运用----------------------------
delimiter $$
create procedure my_fn6()
begin
    declare ename1 varchar(20);  ---定义一个ename1  局部变量
    declare ejob1 varchar(20);  ---ejob1 局部变量
    select ename,job into ename1,ejob1 from emp where empno='1001';
    --查询 当部门编号是1001的时候  查询他们的姓名和工作岗位
    select * from emp where job = ejob1;
    --查询 当工作岗位是 1001的编号的 工作岗位的时  拿到这个信息
end $$;
delimiter ;
call my_fn6();

结果 

------------------------多个参数 查看--------------------
delimiter $$
create procedure my_fn7()
begin
    declare ename1 varchar(20);
    declare ejob1 varchar(20);
    select ename,job into ename1,ejob1 from emp where empno='1001';
    select ename1,ejob1;
end $$;
delimiter ;
call my_fn7()

3,系统变量

        系统变量其实严格来说,应该是系统变量和全局变量.

        系统变量在mysql文件夹中的my.ini配置文件中.如果要修改,可以更改这个配置文件,从而修改配置文件,让所有的用户都生效.

        全局变量,就是我们在启动mysql的时候,服务器会自动将系统变量复制一份给我们当做会话变量.这个会话变量也是可以在存储过程中和存储过程外使用的.

        语法: @@变量名

        赋值语法:set @@变量名 = 值

-----------------------全局变量------------------------------------
show global variables ;  -----查看全局变量
select @@auto_increment_increment;  ----查看指定全局变量
系统变量存储在my.ini 配置文件中  我们每次开启一个会话，会复制一份系统变量作为会话变量供这个会话使用;
有的是只读的 有的是可以修改的 修改仅仅生效与这个会话

四,参数

        1,in参数        

        in参数 ,表示是可以传入的数值或者变量, 传入了参数之后,我们在函数内部就可以使用这个变量了. 

        语法: 函数名(in 参数名 类型)

      案例演示:

封装一个有参数的存储过程,存入员工编号,查找员工信息

--------------传入参数 封装函数 查询-------------
--封装存储过程  传入员工编号 查询员工信息
delimiter $$
create procedure my_fn8(in p_id varchar(20))   ---传入参数
begin
    select * from emp where empno =p_id;  ---使用参数
end $$;
delimiter ;
call my_fn8('1001');  ----

以上代码演示了 传入参数 1001 作为id被函数内的sql语句使用了.

下面代码演示传入多个参数的时候.

---------------传入多个参数 封装函数 做多表查询-----------------------
--封装存储过程，传入部门名和薪资 查询指定部门且薪资在某个范围内的值
delimiter $$
create procedure my_fn9(in p_name varchar(20),in p_sal varchar(20))
begin
    select * from emp e,dept d
             where e.deptno=d.deptno
               and d.dname=p_name
               and e.sal > p_sal;
end $$;
delimiter ;
call my_fn9('学工部',10000);

        2,out参数

     out 表示从函数内部返回值给调用者.

        语法: 函数名(out 变量名 类型)

跟通俗的理解可以 理解为 

return

案例演示:

------------------------返回值训练2 ----------------------------------
        ----out相当于是返回值  我们用一个容器接受 然后用会话变量来使用
-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure my_fn11(in p_id varchar(20),out o_name varchar(20),out o_sal varchar(20))
begin
    select emp.ename,emp.sal into o_name,o_sal from emp where empno=p_id;
end $$
delimiter ;
call my_fn11('1002',@emp_name,@emp_sal);
select @emp_name,@emp_sal;

 

------------------------返回值训练2 ----------------------------------
        ----out相当于是返回值  我们用一个容器接受 然后用会话变量来使用
-- 封装有参数的存储过程，传入员工编号，返回员工名字和薪资
delimiter $$
create procedure my_fn11(in p_id varchar(20),out o_name varchar(20),out o_sal varchar(20))
begin
    select emp.ename,emp.sal into o_name,o_sal from emp where empno=p_id;
end $$
delimiter ;
call my_fn11('1002',@emp_name,@emp_sal);
select @emp_name,@emp_sal;

 

        3,inout参数

           inout参数  表示外部传入的参数经过修改之后可以返回的变量,既可以使用传入变量的值也可以修改变量的值.即使函数执行完毕.

-----------------------------inout 传入值返回值一体-------------------------------------------
---- 传入员工名，拼接部门号，传入薪资，求出年薪
delimiter $$  ---定义分隔符
create procedure  my_fn12(inout io_name varchar(40),inout io_sal varchar(20))
--创建   存储过程    名称     参数 inout   名字 类型
begin
    select concat(empno,'~',ename) into io_name from emp where ename =io_name;
---查询我们传入的参数 然后拼接之后 再返回值给他
    set io_sal =io_sal *12 ;
--使用我们传入的参数 然后*12赋值给他
end $$;
delimiter ;

set @myname='关羽';  --创建一个会话变量
set @mysal=3000;   --创建一个会话变量
call my_fn12(@myname,@mysal)  --调用我们定义的存储过程
select @myname,@mysal;  --查看我们的2个会话变量

五,条件分支

        1,if语句判断条件

        和其他编程语言一样,sql的存储过程中也有if和else的用法.

        格式:

if 条件1 then 结果1;
elseif 条件2 then 结果2;
elseif 条件3 then 结果3;
else 以上条件都不满足返回的结果
end if;

案例.输入学会的成绩,判断成绩的级别

delimiter $$
create procedure my_fn13(in soure int)
begin
    if soure <60 then select '不及格';
        elseif soure <80 then select '良好';
        elseif soure <90 then select '优秀';
        elseif soure <100 then select '优秀';
        else select '未知成绩';
    end if;
end $$;
delimiter ;

call my_fn13(0);

返回结果: 不及格

案例演示2 在sql查询语句中运用

     比如输入员工名称,来判断工资的情况
 

-- 输入员工的名字，判断工资的情况。
###----------------------- 语句------ if else if 语句----------------------；
delimiter $$
create procedure my_fn14(in i_name varchar(20))
begin
    declare res varchar(30);
    declare i_sal int;
    select sal into i_sal from emp where ename = i_name;
    if i_sal < 2000 then set res ='穷鬼';
    elseif i_sal<3000 then set res ='有点钱';
    else set res='超级有钱';
    end if;
    select res;
end $$
delimiter ;

call my_fn14('关羽');

        2 ,case语句判断条件

        跟python中的case语法几乎一样.

case 条件 然后匹配固定值

语法:

case 字段
    when 值1 then 结果1;
    when 值2 then 结果2;
    else 以上都不满足的结果;
end case;

##--根据姓名 查询部门名称 不连接多表；
   # ------------------固定值案例
#case + when +then +else +end case 语句
delimiter $$
create procedure my_fn15(in i_name varchar(20),out res varchar(30))
begin
    declare p_deptno varchar(30);
    select deptno into p_deptno from emp where ename = i_name;
    case p_deptno
        when '10' then set res ='教研部';
        when '20' then set res ='学工部';
        when '30' then set res ='销售部';
        when '40' then set res ='财务部';
        else set res ='未知部门';
    end case;
end $$
delimiter ;

call my_fn15('关羽',@ee_name);
select @ee_name;

 

案例二 case条件 

##-------------------语句-------------------------
#case语句   条件筛选案例
delimiter $$
create procedure my_fn16(in soure int)
begin
    case
    when soure <60 then select '不及格';
    when soure<80 then select '良好';
    when soure<100 then select '优秀';
    else select '未知成绩';
    end case;
end $$
delimiter ;

call my_fn16(40);
call my_fn16(20);
call my_fn16(80);

六,循环

使用循环最大的好处就是我们可以批量创建表格,批量创建,修改,删除数据.

1,while循环

        和常规编程语言一样,用法格式逻辑基本一样.

        语法:

while 条件 
do 
循环体 
循环终止条件
end while

代码案例  批量添加数据. 


### 循环案例  while循环
create table user(
    id int primary key auto_increment,
    name varchar(30),
    psd varchar(30)
);

delimiter $$
create procedure my_fn17(in i_count int)
begin
    declare i int default 1;
    while i < i_count do
        insert into user value (i,concat('user_',i),round(rand()*1000*i,0) );
        set i = i+1;
    end while;
end $$
delimiter ;

call my_fn17(5);
truncate table user;

leave 用法

        相当于python中break. 终于循环

案例:

#while循环案例 leave 演示
delimiter $$
create procedure my_fn18(in i_count int)
begin
    declare i int default 1;
    lable:while i<=i_count do
        insert into user value (i,concat('user-',i),'123456');
        if i = 5 then leave lable;  #--结束循环
        end if;
        set i = i+1;
        end while lable;
end $$
delimiter ;

truncate table user;
call my_fn18(10);  #只有五条

iterate用法
 相当于python中的 continue

案例:

#while 循环案例 iterate演示  相当于跳出
delimiter $$
create procedure my_fn19(in i_count int)
begin
    declare i int default 0;
    l1:while i<=i_count do
        set i =i+1;
        if i =5 then iterate l1;  #如果循环到我这里 就跳过 后面的不执行了 然后循环在开启 继续在执行
        end if;
        insert into user value(i,concat('user-',i),'123456');
        end while l1;
end $$
delimiter ;

truncate table user;
call my_fn19(10);




2,repeat循环


这个循环用法,无论条件如何都会执行一次


delimiter $$
create procedure my_fn20(in i_count int)
begin
    declare i int default 1;
    repeat
        insert into user values (i,concat('user-',i),'1234');
        set i =i+1;
    until  i>i_count
        end repeat;
end $$
delimiter ;

truncate table user;
call my_fn20(10);


3,loop循环


loop循环本质是一个死循环,需要添加if条件 来终止循环


案例:


#loop 案例
delimiter $$
create procedure my_fn21(in i_count int)
begin
    declare i int default 1;
    li:loop
        insert into user value (i,concat('user',i,i),'12345');
        if i<i_count then leave li;
        end if;
    end loop li;
end $$
delimiter ;



truncate table user;
call my_fn20(10);






七,游标


游标是用来存储查询结果集的数据类型.在存储过程中和函数中可以使用游标 对结果集进行循环的处理,游标的使用包括,对永彪的声明,打开,取值,关闭


语法:


#声明游标
declare 游标名称 cursor for sql语句
#打开游标
open 游标名称
#取值游标
fatch 游标名称 into 变量1,变量2,变量3 ……
#关闭游标
close 游标名称


案例演示:  通过循环 使用游标抓取每一条数据


#--游标
use my_procedure;
delimiter $$
create procedure my_fn100(in i_name varchar(20))
begin
    declare p_deptno varchar(20);
    declare p_dnam varchar(20);
    declare p_loc varchar(30);
    declare i_count int;
    declare i int default 1;
    #创建游标
    declare youbiao cursor for select empno , ename, sal
    from  dept a ,emp b
    where a.deptno = b.deptno and a.dname = i_name;

    #创建一个sql 获取终止条件
    select count(*) into i_count
    from  dept a ,emp b
    where a.deptno = b.deptno and a.dname = i_name;
    #打开游标
    open youbiao;
    #取值游标
    while i<i_count do
        fetch youbiao into p_deptno,p_dnam,p_loc;
        select p_deptno,p_dnam,p_loc;
        set i = i+1;
        end while;
    #关闭游标
    close youbiao;
end $$
delimiter ;

call my_fn100('销售部');






八,异常处理


和其他编程语言一样  存储过程中也可以使用异常处理,来捕捉异常,然后避免报错,从而继续执行后续的代码.


语法:


特别注意： 在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。


案例:


use my_procedure;
delimiter $$
create procedure my_fn101(in i_name varchar(20))
begin
    declare p_deptno varchar(20);
    declare p_dnam varchar(20);
    declare p_loc varchar(30);
    declare i int default 1;
    #创建游标
    declare youbiao cursor for select empno , ename, sal
    from  dept a ,emp b
    where a.deptno = b.deptno and a.dname = i_name;

    #添加异常   handler 捕捉异常
#     declare continue handler for NOT FOUND set i = 0;
    #另外一种模式
    declare exit handler for NOT FOUND set i = 0;


    #打开游标
    open youbiao;
    #取值游标
    li:loop
        fetch youbiao into p_deptno,p_dnam,p_loc;
        if i=1 then
        select p_deptno,p_dnam,p_loc;
        else leave li;
        end if;
        end loop li ;
    #关闭游标
    close youbiao;
end $$
delimiter ;

call my_fn101('销售部');



结果和游标结果一样










标签：varchar,name,slq,delimiter,MYSQL,封装,my,declare,select	

From： https://blog.csdn.net/qq_55006020/article/details/140277062
      

      




      

      

        
相关文章

        

          
    
 
  • 十三,mysql的优化,详细篇
    目录一,从设计上优化二,从查询上优化三,从索引上优化四,从存储上优化一,从设计上优化    1,合理的进行数据库设计,通过规范化设计可以避免数据冗余,也可以适当的反规范化设计提高查询性能.    2,选择合适的数据类型,确保使用最合适的数据类型来存......
     
  • mysql注入总结
    1.SQL注入漏洞概述什么是SQL注入SQL注入（SQLi）是一种网络安全漏洞，允许攻击者干扰应用程序对其数据库的查询。通过浏览器或者其他客户端将恶意SQL语句插入到网站参数中，而网站应用程序未对其进行过滤，SQL语句带入数据库使恶意SQL语句得以执行可以查看通常无法检索的数据。这可能包括......
     
  • MySQL 进阶（二）【索引详解】
    前言    程序员避不开和数据库打交道，大数据更是如此，不管是MySQL、Oracle、SQLServer这些OLTP数据库，还是Greeplum、StarRocks、Hive、SparkSQL、FlinkSQL、ClickHouse等OLAP数据库，SQL都是最基础最重要的能力，数据库知识也是每一个程序员必备的知识。  ......
     
  • mysql 误更新记录恢复 update更新字段值恢复  mysql数据库更新字段值恢复
    近日极佳mysql数据库恢复软件加入mysql的 update更新字段值的恢复例如 updatejnx_clean_ordersetno='qwertyuiop'将 jnx_clean_order 表的 no字段值 全部变成了 qwertyuiop  这种情况 1可以从 mysqlbin 日志恢复, 如果没有开启binlog 那就比较麻烦了。2......
     
  • mysql的主挂了，从怎么将多出来的数据同步到主？
    在MySQL的复制环境中，通常主库（Master）是数据的主要来源，而从库（Slave）从主库复制数据以实现数据的同步。但是，如果你的场景中出现了从库包含了主库中没有的数据（这种情况较为罕见，可能是人为错误或配置错误导致的），将这部分数据同步回主库将需要一些特殊操作，因为MySQL复制机制默认是从主......
     
  • 【MySQL MGR管理】MySQL5.7 MGR集群切换测试
    一、服务器环境MySQL5.7MGR集群切换目标：故障切换，启用备选主之后。还原MGR，切换回原主服务器信息[root@0321-mysqlmgr3~]#cat/etc/hosts127.0.0.1localhostlocalhost.localdomainlocalhost4localhost4.localdomain4::1localhostlocalhost.localdomainloc......
     
  • docker安装mysql8.0.23
    拉取镜像dockerpullmysql:8.0.23创建挂载文件mkdir-p/home/docker/mysql/confmkdir-p/home/docker/mysql/datamkdir-p/home/docker/mysql/logcd/home/docker/mysql/conftouchmy.cnf编辑my.cnfvimy.cnf内容如下：default-character-set=utf8[mysql]defa......
     
  • Ubuntu安装MySQL8步骤
    在Ubuntu系统上搭建MySQL的过程：1、首先先将系统通过命令更新到最新，命令如下sudoapt-getupdatesudoapt-getupgrade 2、执行命令行安装MySQL8sudoapt-getinstallmysql-server 3、第一次进入需要用权限进入，可以跳过输入密码的过程，然后等进入MySQL以后通过命令修改......
     
  • redis如何与mysql数据保持一致？
    redis如何与mysql数据保持一致？同步双写：cacheasidepattern，读：先读缓存再读数据库，一个缓存的过期时间，实现起来简单好用极限情况还会有数据不一致的风险。CAP定理：c一致性a可用性p分区容错性，cp或者是ap异步双写：基于消息队列实现，写：生产者：先更新数据库，向队列发消息，消费者：监听消......
     
  • json数据写入到mysql数据中
    importpymysql#json文件中格式一个列表包含一个个的字典数据#[{"title":"胖猫事件-21岁游戏代练胖猫跳江身亡，PUA捞女女主谭竹遭网友报告视频","cover":"https://suvip888.com/20240516/U8NEMN2P/1.jpg","m3u8_url":"https://vodvip888.com/20240516/U8NEMN2P/......
     
    •            
          

        

      

    

    

      
赞助商

      




      

      
阅读排行

      

        
      

    

  





  
 
    
网站主页关于我们联系我们网站地图 

    
本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。 

    
Copyright © 2020-2023  IPS99  版权所有 IPS99