实验游标和存储过程
MySQL中的游标操作与存储过程使用方法
MySQL中的游标操作与存储过程使用方法引言对于开发者来说，数据操作是一个非常重要的任务。
在MySQL中，游标操作和存储过程是两个非常常见的功能，它们可以帮助我们更高效、更灵活地操作和管理数据。
本文将介绍MySQL中的游标操作和存储过程的使用方法，帮助读者更好地应用这些功能。
第一部分：游标操作什么是游标？游标是一种数据库对象，它用于处理数据集。
通过游标，我们可以逐行处理查询结果，而不是一次性地将所有结果返回。
这对于处理大量数据或者需要在结果集上进行逐行处理的情况非常有用。
游标的基本使用方法在MySQL中，使用DECLARE语句声明游标，使用FETCH语句获取游标的下一行数据，使用CLOSE语句关闭游标。
下面是一个简单的示例：```DECLARE cursor_name CURSOR FOR SELECT column1, column2 FROMtable_name;OPEN cursor_name;FETCH cursor_name INTO variable1, variable2;CLOSE cursor_name;```在这个示例中，我们首先声明了一个名为"cursor_name"的游标，然后打开游标并获取第一行数据到变量"variable1"和"variable2"中，最后关闭游标。
游标的类型MySQL支持两种类型的游标：FORWARD_ONLY和SCROLL。
FORWARD_ONLY游标只能向前遍历结果集，而SCROLL游标可以以任何顺序遍历结果集，包括向前、向后和随机访问。
使用游标实现分页查询游标非常适合实现分页查询功能。
通过游标，我们可以在一个较大的结果集中，按照一定的页大小逐页取出数据，而不需要一次性将所有数据加载到内存中。
下面是一个使用游标实现分页查询的示例：```DECLARE page_cursor SCROLL CURSOR FOR SELECT column1, column2 FROM table_name LIMIT start_index, page_size;OPEN page_cursor;FETCH page_cursor INTO variable1, variable2;WHILE NOT done DO-- 处理当前行数据...FETCH page_cursor INTO variable1, variable2;-- 判断是否还有下一页数据IF no_more_data THENSET done = TRUE;END IF;END WHILE;CLOSE page_cursor;```在这个示例中，我们使用了SCROLL游标，并通过LIMIT子句指定了查询的起始位置和页大小。
实验6 游标与存储过程
实验6：游标与存储过程6.1 实验目的与要求(1) 掌握游标的定义和使用方法。
(2) 掌握存储过程的定义、执行和调用方法。
(3) 掌握游标和存储过程的综合应用方法。
6.2 实验案例下面以简单实例介绍游标的具体用法。
[例6.1] 利用游标选取业务科员工的编号、姓名、性别、部门和薪水字段，并逐行显示游标中的信息。
DECLARE SCROLL cur_emp CURSOR FORSELECT employeeno, employeename, sex, department, salaryFROM employeeWHERE department='业务科'ORDER BY employeeno /*定义游标*/OPEN cur_emp /*打开游标*/SELECT 'CURSOR内数据条数'=@@cursor_rows /*显示游标内记录的个数*/FETCH NEXT FROM cur_emp /*逐行提取游标中的记录*/WHILE (@@FETCH_status<>-1) /*判断FETCH语句是否执行成功*/BEGINSELECT 'cursor读取状态'=@@FETCH_status/*显示游标的读取状态*/FETCH NEXT FROM cur_emp/*提取游标下一行信息*/ENDCLOSE cur_emp/*关闭游标*/DEALLOCATE cur_emp/*释放游标*/本例中，@@cursor_rows是返回连接上最后打开的游标中当前存在的合格行的数量。
具体参数信息见表6-1所示。
@@FETCH_status是返回被FETCH语句执行的最后，而不是任何当前被连接打开的游标的状态。
具体参数见表6-2所示。
表6-2 @@FETCH_status参数返回值的描述表[例6.2] 利用游标选取业务科员工的编号、姓名、性别、部门和薪水字段，并以格式化的方式输出游标中的信息。
数据库游标实验报告
计算机系一、实验目的1、掌握创建游标的方法和步骤；2．掌握游标的使用方法；二、实验内容1、游标的创建；2、游标的使用方法。
三、实验步骤1、游标的创建。
1）使用S_C数据库中的S表、C表、SC表创建一个存储过程—sp_CURSOR1。
该存储过程的作用是：显示所有的课程信息，如果成绩>=90显示成绩本身；成绩>=80显示良；成绩>=70显示中；成绩>=60显示及格；成绩>=0显示不及格；如果没有成绩则显示无成绩。
信息还包含学号，姓名，课程和成绩，显示格式如下：学号---姓名---课程---成绩，如图1所示。
要求使用游标技术实现上述要求，使用Print语句实现显示。
图1 成绩显示格式sp_CURSOR1的创建语句：create proc sp_CURSOR1asDeclare @sname varchar(50)Declare @sno varchar(20)Declare @cno varchar(20)Declare @cname varchar(20)Declare @grade varchar(20)Declare SCursor Cursor ForSelect sno,cno,grade From SCOpen SCursorFetch Next From SCursor Into @sno,@cno,@gradeWhile@@FETCH_STATUS= 0beginselect @sname=sname From S where sno=@snoselect @cname=cname From C where cno=@cnoif(@grade ='')Print @sno+@sname+@cname+'null'else if(@grade >= 90)Print @sno+@sname+@cname+@gradeelse if(@grade >=80)Print @sno+@sname+@cname+'良'else if(@grade >=70)Print @sno+@sname+@cname+'中'else if(@grade >=60)Print @sno+@sname+@cname+'及格'elsePrint @sno+@sname+@cname+'不及格'Fetch Next From SCursor Into @sno,@cno,@gradeEndClose SCursorDeallocate Scursorgo结果描述：2、游标的使用。
MySQL存储过程和游标
MySQL存储过程和游标⼀、存储过程什么是存储过程，为什么要使⽤存储过程以及如何使⽤存储过程，并且介绍创建和使⽤存储过程的基本语法。
什么是存储过程：存储过程可以说是⼀个记录集，它是由⼀些T-SQL语句组成的代码块，这些T-SQL语句代码像⼀个⽅法⼀样实现⼀些功能（对单表或多表的增删改查），然后再给这个代码块取⼀个名字，在⽤到这个功能的时候调⽤他就⾏了。
存储过程的好处：1. 由于数据库执⾏动作时，是先编译后执⾏的。
然⽽存储过程是⼀个编译过的代码块，所以执⾏效率要⽐T-SQL语句⾼。
2. ⼀个存储过程在程序在⽹络中交互时可以替代⼤堆的T-SQL语句，所以也能降低⽹络的通信量，提⾼通信速率。
3. 通过存储过程能够使没有权限的⽤户在控制之下间接地存取数据库，从⽽确保数据的安全存储过程的基本语法：--------------------创建存储过程------------------------------------CREATE PROCEDURE procedure_name( IN|OUT variable data_type)BENGINsql_statement;......END;-- MySQL⽀持IN(传递给存储过程)、OUT(从存储过程传出)-- variable 变量-- data_type 参数的数据类型-- sql_statement 中 INTO parameter 的把值保存到相应的变量中（通过INTO关键字）--------------------执⾏存储过程------------------------------------CALL procedure_name(@parameters);--------------------删除存储过程------------------------------------DROP PROCEDURE procedure_name;-- 如果指定的过程不存在，则DROP PROCEDURE将会产⽣⼀个错误。
存储过程和游标
我们在进行pl/sql编程时打交道最多的就是存储过程了。
存储过程的结构是非常的简单的，我们在这里除了学习存储过程的基本结构外，还会学习编写存储过程时相关的一些实用的知识。
如：游标的处理，异常的处理，集合的选择等等1.存储过程结构1.1 第一个存储过程Java代码1.create or replace procedure proc1(2. p_para1 varchar2,3. p_para2 out varchar2,4. p_para3 in out varchar25.)as6. v_name varchar2(20);7.begin8. v_name := '三丰';9. p_para3 := v_name;10. dbms_output.put_line('p_para3:'||p_para3);11.end;上面就是一个最简单的存储过程。
一个存储过程大体分为这么几个部分:创建语句：create or replace procedure 存储过程名如果没有or replace语句，则仅仅是新建一个存储过程。
如果系统存在该存储过程，则会报错。
Create or replace procedure 如果系统中没有此存储过程就新建一个，如果系统中有此存储过程则把原来删除掉，重新创建一个存储过程。
存储过程名定义：包括存储过程名和参数列表。
参数名和参数类型。
参数名不能重复，参数传递方式:IN, OUT, IN OUTIN 表示输入参数，按值传递方式。
OUT 表示输出参数，可以理解为按引用传递方式。
可以作为存储过程的输出结果，供外部调用者使用。
IN OUT 即可作输入参数，也可作输出参数。
参数的数据类型只需要指明类型名即可，不需要指定宽度。
参数的宽度由外部调用者决定。
过程可以有参数，也可以没有参数变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。
实验07 游标,存储过程,触发器
实验七游标,存储过程，触发器Sqlplus /nologconn scott/tigerdeclarecursor mycur isselect * from emp;myrecord emp%ROWTYPE;beginopen mycur;fetch mycur into myrecord;while mycur%FOUND loopdbms_output.put_line(myrecord.empno||’,’||myrecord.ename); fetch mycur into myrecord;end loop;close mycur;end;/save c:\plsql_cursor01.txt带参数的游标,%NOTFOUND属性declarecursor cur_para(id varchar2) isselect ename from emp where empno=id;t_name emp.ename%TYPE;beginopen cur_para(‘7369’);loopfetch cur_para into t_name;exit when cur_para%NOTFOUND;dbms_output.put_line(t_name);end loop;close cur_para;end;/用FOR循环实现declarecursor cur_para(id varchar2) isselect ename from emp where empno=id;begindbms_output.put_line(‘*******结果*************’);for cur in cur_para(‘7369’) loopdbms_output.put_line(cur.ename);end loop;end;/%ISOPEN属性declaret_name emp.ename%TYPE;cursor cur(id varchar2) isselect ename from emp where empno=id;beginif cur%ISOPEN thendbms_output.put_line(‘游标已打开’);elseopen cur(‘7369’);end if;fetch cur into t_name;close cur;dbms_output.put_line(t_name);end;/%ROWCOUNT属性declaret_name varchar2(10);cursor mycur isselect dname from dept;beginopen mycur;loopfetch mycur into t_name; --一开始可以不要这句试下，然后要这一句试下。
SQL实验八 存储过程和游标
Go
(2)use教学
go
createprocedurenianji
@cnumberchar(12)
as
if((selectsno
fromstudent
wheresdept=@cnumber)>0)print'1'
elseprint'0'
whereClno=@clno
end
closecur_clage
deallocatecur_clage
use[0531]
go
execproc_clage1
实验总结（包括过程总结、心得体会及实验改进意见等）：
在SQL中创建储存过程时，用户只能在当前数据中创建存储过程，数据库的拥有者有默认的创建权限，权限也可以转让给其他用户。新建储存过程的名称必须符合标示符规则，且对于数据库及其所有者必须唯一；用户定义的存储过程只能在当前数据库中创建，但是临时存储过程通常是在tempdb数据库中创建的；存储过程最大不能超过128MB；在一条T-SQL语句中CREATE PROCEDURE不能与其他T-SQL语句一起使用；在修改存储过程时语句中的参数与CREATE PROCEDURE语句中的参数相同。
from student
group by clno;
open cur_clage;
while @@fetch_status = 0
begin
fetch next from cur_clage into @clno,@clage;
update class
set clage = @clage
where clno = @clno;
指导教师评语：
实验8游标包和存储过程
创建触发器
二、 定义存储过程
Hale Waihona Puke 调用存储过程三、 创建包规范
创建包主体
调用函数
调用存储过程
五、实验总结（对本实验结果进行分析，实验心得体会及改进意见） 游标包和存储过程还需要继续学习。
实验评语 实验成绩 指导教师签名： 年 月 日
2. 基于 student.sql 脚本建立的三张表 student，course，sc，编写一个存 储过程，输入学生的学号，输出其选修门数，平均分，最高分和最低分。 冰编写 PL/SQL 程序块调用该存储过程。 3. 编写一个名为 mypack 的包，包中包含一个名为 fun_classnum 的函数，该 函数的功能是输入班级名称，求出班级人数，和一个名为 pro_selectstudent 的存储过程，该存储过程的作用是输入学生学号，返 回学生的姓名和所在班级。并编写 PL/SQL 程序块，调用该包中的函数和 存储过程。 四、实验结果（本实验源程序清单及运行结果或实验结论、实验设计图） 一、 创建表
实验报告
课程名称 数据库应用技术 的创建 实验类型 □验证型 □综合型
√ 设计型
实验日期 4 月 12 日
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
实验九游标与存储过程1 实验目的与要求(1) 掌握游标的定义和使用方法。
(2) 掌握存储过程的定义、执行和调用方法。
(3) 掌握游标和存储过程的综合应用方法。
2 实验内容请完成以下实验内容：(1) 创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status的返回值。
输出格式如下：'客户编号'+'-----'+'客户名称'+'----'+'客户住址'+'-----'+'客户电话'+'------'+'邮政编码'(2) 利用游标修改OrderMaster表中orderSum的值。
(3) 创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水。
(4) 创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号。
(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额。
(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息。
(7)创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=======大客户中热销的前3种商品的销售信息================商品编号商品名称总销售数量P2******* 120GB硬盘 21.00P2******* 3.5寸软驱 18.00P2******* 网卡 16.00(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。
年终奖金＝年销售总额×提成率。
提成率规则如下：年销售总额5000元以下部分，提成率为10％，对于5000元及超过5000元部分，则提成率为15％。
(9) 创建存储过程，要求将OrderMaster表中每一个订单所对应的明细数据信息按规定格式输出，格式如图7-1所示。
===================订单及其明细数据信息====================--------------------------------------------------- 订单编号 200801090001--------------------------------------------------- 商品编号数量价格P2******* 5 403.50P2******* 3 2100.00P2******* 2 600.00--------------------------------------------------- 合计订单总金额 3103.50图7-1 订单及其明细数据信息(10) 请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。
输出格式如图7-2所示。
===================客户订单表====================--------------------------------------------------- 客户名称：统一股份有限公司客户地址：天津市总金额： 31121.86--------------------------------------------------- 商品编号总数量平均价格P2******* 5 80.70P2******* 19 521.05P2******* 5 282.00P2******* 2 320.00报表制作人陈辉制作日期 06 8 2012图7-2 客户订单表实验脚本：/*(1) 创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status的返回值。
输出格式如下：'客户编号'+'-----'+'客户名称'+'----'+'客户电话'+'-----'+'客户住址'+'------'+'邮政编码'*/declare @C_no char(9),@C_name char(18),@C_phone char(10),@C_add char(8),@C_zip char(6)declare @text char(100)declare cus_cur scroll cursor forselect*from Customer62select@text='================================Customer62表的记录===================='print @textselect@text='客户编号'+'------'+'客户名称'+'-----------'+'客户电话'+'-------'+'客户住址'+'------'+'邮政编码'print @textselect@text='======================================================================'print @textopen cus_curfetch cus_cur into @C_no,@C_name,@C_phone,@C_add,@C_zipwhile(@@fetch_status=0)beginselect@text=@C_no+' '+@C_name+' '+@C_phone+' '+@C_add+''+@C_zipprint @textfetch cus_cur into @C_no,@C_name,@C_phone,@C_add,@C_zip endclose cus_curdeallocate cus_cur/*(2) 利用游标修改OrderMaster表中orderSum的值*/declare @orderNo varchar(20),@total numeric(9,2)declare om_cur cursor forselect orderNo,sum(quantity*price)from OrderDetail62group by orderNoopen om_curfetch om_cur into @orderNo,@totalwhile(@@fetch_status=0)beginupdate OrderMaster62set orderSum=@totalwhere orderNo=@orderNofetch om_cur into @orderNo,@totalendclose om_curdeallocate om_cur/*(3) 创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水*/ declare @emNo varchar(8),@emNa char(8),@emse char(1),@emde varchar(10),@emhe varchar(8),@emsa numeric(8,2)declare @text char(100)declare em_cur scroll cursor forselect employeeNo,employeeName,sex,department,headShip,salaryfrom Employee62where sex='M'select @text='=====================================================' print @textselect @text='编号姓名性别所属部门职务薪水'print @textselect @text='=====================================================' print @textopen em_curfetch em_cur into @emNo,@emNa,@emse,@emde,@emhe,@emsawhile(@@fetch_status=0)beginselect @text=@emNo+' '+@emNa+' '+@emse+' '+@emde+' '+@emhe +' '+convert(char(10),@emsa)print @textfetch em_cur into @emNo,@emNa,@emse,@emde,@emhe,@emsaendclose em_curdeallocate em_cur/*(4) 创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号*/create table Rnum(number char(8)null,ename char(10)null)--先创建一张新表用来存储已经产生的员工编号create procedure no_tot(@name nvarchar(50))asbegindeclare @i int,@text char(100)set @i=1while(@i<1000)beginif exists(select numberfrom Rnumwherenumber=('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3))) beginset @i=@i+1continueendelsebegininsert Rnum values(('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3)),@name)select @text='员工编号'+' '+'员工姓名'print @textselect@text=('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3))+' '+@name--这里的两个数字'3' 就是我们要设置的id长度print @textbreakendendend/*执行过程*/exec no_tot 张三/*(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额*/ create procedure emli_tot @emNo char(8)asselect a.employeeNo 员工编号,b.orderNo 订单编号,b.orderSum 订单金额from Employee62 a,OrderMaster62 bwhere a.employeeNo=b.salerNo and a.employeeName like'@emNo'/*执行过程*/exec emli_tot '李%'/*(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息*/create procedure saler_totasselect top 3 salerNo 业务员编号,sum(orderSum)总销售业绩from OrderMaster62group by salerNoorder by sum(orderSum)desc/*执行过程*/exec saler_tot/*(7) 创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=======大客户中热销的前种商品的销售信息================商品编号商品名称总销售数量P2******* 120GB硬盘21.00P2******* 3.5寸软驱18.00P2******* 网卡16.00*/create procedure product_totasdeclare @proNo char(10),@proNa char(20),@total intdeclare @text char(100)declare sale_cur scroll cursor forselect top 3 a.productNo,a.productName,sum(c.quantity)from Product62 a,OrderMaster62 b,OrderDetail62 cwhere a.productNo=c.productNo and b.orderNo=c.orderNo andb.customerNo in(select top 5 m.customerNofrom OrderMaster62 m,OrderDetail62 nwhere m.orderNo=n.orderNogroup by m.customerNoorder by sum(quantity)desc)group by a.productNo,a.productNameorder by sum(c.quantity)descselect @text='=======大客户中热销的前种商品的销售信息======'print @textselect @text='商品编号商品名称总销售数量'print @textopen sale_curfetch sale_cur into @proNo,@proNa,@totalwhile(@@fetch_status=0)beginselect @text=@proNo+' '+@proNa+' '+convert(char(10),@total)print @textfetch sale_cur into @proNo,@proNa,@totalendclose sale_curdeallocate sale_cur/*执行过程*/exec product_tot/*(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。