Mysql存储过程、函数和游标基本使用

数据库 专栏收录该内容
4 篇文章 0 订阅

实验6.1 存储过程实验

(1)实验目的

掌握数据库PL/SQL编程语言,以及数据库存储过程的设计和使用方法

(2)实验内容和要求

存储过程定义、存储过程运行,存储过程更名,存储过程删除,存储过程的参数传递。掌握PL/SQL编程语言和编程规范,规范设计存储过程。

(3)实验重点和难点

实验重点:存储过程定义和运行。

实验难点:存储过程的参数传递方法。

 

(1)存储过程的概念定义以及优缺点

SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

存储过程是可编程的函数,在数据库中创建并保存,可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟,它允许控制数据的访问方式。

存储过程的优点:

(1.1).增强SQL语言的功能和灵活性:存储过程可以用控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。

(1.2).标准组件式编程:存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。

(1.3).较快的执行速度:如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。

(1.4).减少网络流量:针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织进存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大减少网络流量并降低了网络负载。

(1.5).作为一种安全机制来充分利用:通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。

 

 

(2)创建存储过程并理解参数含义

(2.1)参数

存储过程根据需要可能会有输入、输出、输入输出参数,如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT:

  IN:参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不会影响原值。

  OUT:该值可在存储过程内部被改变,并且返回值为存储过程内部被改变的值。

  INOUT:调用时指定,并且可被改变和返回。

ps:过程体的开始与结束使用BEGIN与END进行标识,用户变量需要用@标识

 

(2.1.1)IN参数

#定义过程

DELIMITER //

 create procedure in_param(IN test_1 int)

   begin

   select test_1; # 显示刚开始的参数值

   set test_1=250;

   select test_1; # 显示修改后的参数值

   end;

//

DELIMITER ;

 

#开始调用过程

set @p_test = 1;

CALL in_param(@p_test);  

select @p_test;  #显示经过过程调用后的参数值

测试结果:

result1:                     result2:               result3:

         

综上可知,对于IN类型的参数,即使存储过程中改变它的值,也不会影响原值,类似于C语言函数中参数的复制。

(2.1.2)OUT参数

 

将函数参数类型改为OUT后,进行测试:

result1:                      result2:                     result3:  

        

综上结果可以看出,OUT参数不需要初始化,并且会自动抛弃之前的值,在存储过程中修改的值会直接返回到原参数。

(2.1.3)INOUT参数

result1:                      result2:                     result3:

        

综上可以得知,INOUT参数相当于C语言中的引用,既可以直接引入原值也可以对其直接进行修改。

 

(2.1.4)语法:DECLARE 变量名1[,变量名2...] 数据类型 [默认值];

(3)创建一个成绩表单,并创建相对完整的存储过程定义

(3.1)创建表单

成绩表单:

分类的分数计数表:

对mypro表的成绩分类结果:

 

 

 

 

 

实验6.2 自定义函数

(1)实验目的

掌握数据库PL/SQL编程语言以及数据库自定义函数的设计和使用方法。

(2)实验内容和要求

自定义函数定义、自定义函数运行,自定义函数更名,自定义函数删除,自定义函数的参数传递。掌握PL/SQL和编程规范,规范设计自定义函数。

(3)实验重点和难点

实验重点:自定义函数的定义和运行。

实验难点:自定义函数的参数传递方法。

(1)自定义显示时间函数

(2)计算成绩平均分

 

(ps:函数体内局部变量赋值用 select .. from .. into [ params. ]

用户变量赋值用:=

1)set @varname=value; 或者 set @varname :=value;

2)select @varname :=value; 或者 select @varname := 字段名 from 表名 where ......

使用set时 “=”和“:=”都可以,使用select时只能使用“:=”方式。

 

实验6.3 游标实验

(1)实验目的

掌握PL/SQL游标的设计、定义和使用方法,理解PL/SQL游标按行操作和SQL按结果集操作的区别和联系。

(2)实验内容和要求

游标定义、游标使用。掌握各种类型游标的特点、区别与联系。

 

 返回多行结果集的select语句,并可使用MySQL游标循环处理

(3)实验重点和难点

实验重点:游标定义和使用。

实验难点:游标类型。

 

ps:游标的使用一般分为5个步骤,主要是:定义游标->打开游标->使用游标->关闭游标->释放游标

  1. 定义游标
  2. 打开游标
  3. 使用游标
  4. 关闭游标
  5. 释放游标

查询成功,但是存在错误:

原因是没有对行数进行判断。

利用游标更新表单,并且解决循环报错的问题:

(ps:需要注意监听器需要在游标之前声明,并且变量需要在游标和监听器之前声明

初始状态的mypro表单:              执行存储过程游标更新之后的mypro表单:

        

 

但是可以看到上图中 id=14的行执行了两次 +10 操作。

原因是:当第16次进行 fetch mycursor  into  id,name,score;

会进入到handler操作,但是这时候还在if语句当中,所以仍然继续进行,并且id是上一轮留存的id值,于是最后一行被增加了两次。

改变代码逻辑后:

执行之前的表单:            执行之后的表单:

         

结果正确。

游标释放:

  • 3
    点赞
  • 1
    评论
  • 8
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 点我我会动 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值