全球云服务器

MySql存储过程实例(附完整注释)

发布时间：2022-07-20 11:51:02 作者：tangyx 阅读量：4138

昨天的文章《初学者都能看懂的mysql教程》让初学者对MySql有了一定的认识，今天分享一个简单的MySql存储过程实例，供大家参考学习。
                                                 
                                                   /********************* 创建表 *****************************/
                                                    delimiter //
                                                    
                                                    DROP TABLE if exists test //
                                                    
                                                    CREATE TABLE test(
                                                      id int(11) NULL
                                                    ) //
                                                    
                                                    /********************** 最简单的一个存储过程 **********************/
                                                    drop procedure if exists sp//
                                                     CREATE PROCEDURE sp() select 1 //
                                                     
                                                     call sp()//
                                                     
                                                    /********************* 带输入参数的存储过程  *******************/
                                                    
                                                    drop procedure if exists sp1 //
                                                    
                                                    create procedure sp1(in p int)
                                                    comment 'insert into a int value'
                                                    begin
                                                      /* 定义一个整形变量 */
                                                      declare v1 int;
                                                      
                                                      /* 将输入参数的值赋给变量 */
                                                      set v1 = p;
                                                      
                                                      /* 执行插入操作 */
                                                      insert into test(id) values(v1);
                                                    end
                                                    //
                                                    
                                                    /* 调用这个存储过程  */
                                                    call sp1(1)//
                                                    
                                                    /* 去数据库查看调用之后的结果 */
                                                    select * from test//
                                                    
                                                     /****************** 带输出参数的存储过程 ************************/
                                                    
                                                    drop procedure if exists sp2 //
                                                    create procedure sp2(out p int)
                                                    /*这里的DETERMINISTIC子句表示输入和输出的值都是确定的,不会再改变.我一同事说目前mysql并没有实现该功能,因此加不加都是NOT DETERMINISTIC的*/
                                                    DETERMINISTIC
                                                    begin
                                                      select max(id) into p from test;
                                                    end
                                                    //
                                                    
                                                    /* 调用该存储过程，注意：输出参数必须是一个带@符号的变量 */
                                                    call sp2(@pv)//
                                                    
                                                    /* 查询刚刚在存储过程中使用到的变量 */
                                                    select @pv//                                                    
                                                    
                                                    /******************** 带输入和输出参数的存储过程 ***********************/
                                                    
                                                    drop procedure if exists sp3 //
                                                    create procedure sp3(in p1 int , out p2 int)
                                                    begin
                                                    
                                                      if p1 = 1 then
                                                        /* 用@符号加变量名的方式定义一个变量，与declare类似 */
                                                        set @v = 10;
                                                      else
                                                        set @v = 20;
                                                      end if;
                                                      
                                                      /* 语句体内可以执行多条sql，但必须以分号分隔 */
                                                      insert into test(id) values(@v);
                                                      select max(id) into p2 from test;
                                                      
                                                    end
                                                    //
                                                    
                                                    /* 调用该存储过程，注意：输入参数是一个值，而输出参数则必须是一个带@符号的变量 */
                                                    call sp3(1,@ret)//
                                                    
                                                    select @ret//
                                                    
                                                    /***************** 既做输入又做输出参数的存储过程 ***************************************/
                                                    
                                                    drop procedure if exists sp4 //
                                                    create procedure sp4(inout p4 int)
                                                    begin
                                                       if p4 = 4 then
                                                          set @pg = 400;
                                                       else
                                                          set @pg = 500;
                                                       end if; 
                                                       
                                                       select @pg;
                                                       
                                                    end//
                                                    
                                                    call sp4(@pp)//
                                                    
                                                    /* 这里需要先设置一个已赋值的变量，然后再作为参数传入 */
                                                    set @pp = 4//
                                                    call sp4(@pp)//

***本网站图片，文字之类版权申明，因为网站可以由注册用户自行上传图片或文字，本网站无法鉴别所上传图片或文字的知识版权，如果侵犯，请及时通知我们，本网站将在第一时间及时删除。

我要评论

网友评论

---

---

---

---

---

---

---

常见ftp命令 FTP命令使用详解双硬盘组建Raid0磁盘阵列图文教程用友软件客户端连接不上服务器的解决办法？WebService实例开发、运维不可不看的Linux调测工具如何使用Charles抓取Websocket的消息DNS服务的启动和停止如何用ODBC访问SQL数据库Linux视频教程：用户管理命令useraddWindows Server 2003中如何启动ASP.NET扩展服务IIS中站点主属性配置如何搭建平台【超赞】专为小白整理的adobe xd入门教程小旋风asp服务器怎么使用将基于域名的虚拟主机转换为基于IP的虚拟主机设置DHCP数据库路径快云存储技术服务器集群技术的分类什么是双线托管，比较便宜的双线托管有哪些重庆电信服务器托管注意事项IBM欲出售低端服务器业务是为何意Linux-PAM的工作流程基于域名的虚拟主机配置的两种方法分布式与集群是一回事儿么？大神教你5招加速Python英特尔称服务器业务目前已基本恢复英特尔三季度全球微处理器市场份额达83.7%【划重点】我对TopCoder的一些看法服务器技术之Pcrl语言文件的读写IDC：中国HPC发展迅猛曙光HPC销售额进全球前十强