数据库高级应用技术03-(存储过程).ppt

《数据库高级应用技术03-(存储过程).ppt》由会员分享，可在线阅读，更多相关《数据库高级应用技术03-(存储过程).ppt（67页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、 高等职业技术院校教材高等职业技术院校教材 数据库高级应用技术数据库高级应用技术 主编：温立辉主编：温立辉1 存储过程存储过程本单元教学目标本单元教学目标了解存储过程的种类认识、了解存储过程的作用掌握存储过程的开发语法熟练使用数据库IDE开发、调试存储过程2 存储过程概念（一）nSQL语句：nSQL语句在执行的时候需要先编译先编译先编译先编译，然后执行然后执行然后执行然后执行n存储过程：n存储过程（Stored Procedure）n n是一组为了完成特定功能的是一组为了完成特定功能的是一组为了完成特定功能的是一组为了完成特定功能的SQLSQL语句集语句集语句集语句集n n经编译后存储在数据库

2、中经编译后存储在数据库中经编译后存储在数据库中经编译后存储在数据库中n用户通过指定存储过程的名字与参数存储过程的名字与参数存储过程的名字与参数存储过程的名字与参数（如果该存储过程带有参数）来调用执行它。3 存储过程概念（二）n一个存储过程是一个可编程可编程可编程可编程的函数函数函数函数n它在数据库中创建并保存n它由SQLSQL语句语句语句语句和一些特殊的控制结构控制结构控制结构控制结构组成n存储过程适用于以下场合n在不同的平台（应用程序）不同的平台（应用程序）不同的平台（应用程序）不同的平台（应用程序）上执行相同的函数n n封装特定功能封装特定功能封装特定功能封装特定功能n是数据库编程中面向对

3、象方法的模拟4 存储过程优点（一）存储过程优点（一）n(1).存储过程增强了存储过程增强了SQL语言的功能和灵活性语言的功能和灵活性n存储过程可以用流控制语句编写，有很强的灵活性n可以完成复杂的判断和较复杂的运算n(2).存储过程是允许编程的标准组件存储过程是允许编程的标准组件n被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句n数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响n(3).存储过程能实现较快的执行速度存储过程能实现较快的执行速度n如果某一操作包含大量的SQL代码或分别被多次执行，n存储过程比批处理执行速度快很多，因存储过程是预编译的n批处理的

4、SQL语句在每次运行时要进行编译，速度相对慢5 存储过程优点（二）存储过程优点（二）n(4).存储过程能过减少网络流量。存储过程能过减少网络流量。n当同一操作所涉及的众多SQL语句被编码到存储过程中n在客户机上调用该存储过程时，网络中只需传送该调用语句n从而大大减少了网络流量并降低了网络负载n(5).存储过程被作为一种安全机制来充分利用存储过程被作为一种安全机制来充分利用n系统管理员通过执行某一存储过程的权限进行限制n能够实现对相应的数据的访问权限的限制，n避免了非授权用户对数据的访问，从而保证了数据的安全6 存储过程的创建存储过程的创建nMySQL格式：格式：n nCREATE PROCED

5、URECREATE PROCEDURE 存储过程名存储过程名存储过程名存储过程名 (过过过过程参数程参数程参数程参数,.,.)n nBEGINBEGINnn过程控制语句（Transaction-SQL）nn nEND END 7 存储过程案例存储过程案例-创建创建User表表nCREATE DATABASE IF NOT EXISTS mydb;nUSE mydb;nDROP TABLE IF EXISTS user;nCREATE TABLE user(n user_id int(10)unsigned NOT NULL auto_increment,n user_name varchar(

6、45)NOT NULL,n pass_word varchar(45)NOT NULL,n email varchar(45)NOT NULL,n phone varchar(45)NOT NULL,n sex char(1)NOT NULL,n score int(10)unsigned NOT NULL,n PRIMARY KEY (user_id)n)ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;nINSERT INTO user(user_id,user_name,pass_word,email,phone,sex,score

7、)VALUES n(1,LiMing,LiMing,LiM,83278904,0,60),n(2,ZhuangPing,ZhuangPing,ZhuangP,83278678,0,70),n(3,LuMei,LuMei,LuM,83278904,1,80),n(4,QiaoBing,QiaoBing,QiaoB,83278452,1,70),n(5,Kerry,Kerry,K,83278678,1,50),n(6,Jetty,Jetty,J,83278904,0,90),n(7,Lucy,Lucy,L,83278904,0,40),n(8,Honey,Honey,H,83278904,1,80

8、),n(9,Wendy,Wendy,W,83278452,0,50),n(10,Rose,Rose,R,83278904,0,30);8 存储过程案例存储过程案例-开发编码开发编码nDELIMITER/nCREATE PROCEDURE proc1(in s int)nBEGIN nupdate user set score=s where user_id in(2,4,6,8,10);nEND n/nDELIMITER;9 案例语句解释案例语句解释nDELIMITER/n表示把MySQL分隔符修改为：/nMySQL默认以“;”为分隔符n如果没有声明分割符，编译器会把存储过程中的”;”当成SQ

9、L语句结束分隔符进行处理，则存储过程的编译过程会报错；所以要事先用DELIMITER关键字声明当前段分隔符，这样MySQL才会将“;”当做存储过程中的代码，而不是语句MySQL中语句的结束分隔符n nDELIMITER;DELIMITER;n用完了之后要把分隔符还原为：;n存储过程根据需要可能会有输入、输出、输入输出参数存储过程根据需要可能会有输入、输出、输入输出参数n本案例中有一个输入参数s，类型是int型n如果有多个参数用,分割开n n过程体的开始与结束使用过程体的开始与结束使用过程体的开始与结束使用过程体的开始与结束使用BEGINBEGIN与与与与ENDEND进行标识。进行标识。进行标识

10、。进行标识。10 存储过程调用存储过程调用n调用格式如下：n ncall+call+存储过程名存储过程名存储过程名存储过程名+参数参数参数参数ncall proc1(100);n调用后看到user表中user_id为：2、4、6、8、10 的记录的score字段值变为了100n删除存储过程格式：n ndrop+PROCEDURE+drop+PROCEDURE+存储过程名存储过程名存储过程名存储过程名 ndrop PROCEDURE proc1;11 存储过程参数存储过程参数n n存储过程共有三种参数类型存储过程共有三种参数类型存储过程共有三种参数类型存储过程共有三种参数类型,IN,OUT,IN

11、OUT,IN,OUT,INOUT,形式如：形式如：形式如：形式如：nCREATE PROCEDURE(IN|OUT|INOUT 参数名参数名 数据数据类形类形.)nIN 输入参数输入参数:n表示该参数值必须在调用存储过程时指定，在存储过程中修改该参数值将不被返回，为默认值nOUT 输出参数输出参数:n该值可在存储过程内部被改变，并可返回nINOUT 输入输出参数输入输出参数:n调用时指定，并且可被改变和返回12 IN参数例子参数例子nDELIMITER/nCREATE PROCEDURE demo_in_parameter(IN p_id int,IN p_score_add int)nBEG

12、IN nupdate user set score=(score+p_score_add)where user_id=p_id;nEND;n/nDELIMITER;13 存储过程调用（存储过程调用（IN参数）参数）n n直接调用：直接调用：直接调用：直接调用：n nCALL demo_in_parameter (10,5000);CALL demo_in_parameter (10,5000);n n通过预定义参数变量调用：通过预定义参数变量调用：通过预定义参数变量调用：通过预定义参数变量调用：n n先定义两个参数变量先定义两个参数变量先定义两个参数变量先定义两个参数变量n nset p_id

13、=10;set p_id=10;n nset p_score_add=5000;set p_score_add=5000;n n再把变量作为参数调用再把变量作为参数调用再把变量作为参数调用再把变量作为参数调用n nCALL demo_in_parameter(p_id,p_score_add);CALL demo_in_parameter(p_id,p_score_add);调用后可看到user_id 为10的score字段的 值增加了500014 OUT参数例子参数例子nDELIMITER/nCREATE PROCEDURE demo_out_parameter(OUT p_out int)

14、nBEGIN nSELECT COUNT(*)INTO p_out FROM user;nEND n/nDELIMITER;15 存储过程调用（存储过程调用（OUT参数）参数）n n通过预定义输出参数变量调用：通过预定义输出参数变量调用：通过预定义输出参数变量调用：通过预定义输出参数变量调用：n n先定义输出参数变量先定义输出参数变量先定义输出参数变量先定义输出参数变量n nset p_out=0;set p_out=0;n n再把变量作为参数调用再把变量作为参数调用再把变量作为参数调用再把变量作为参数调用n nCALL demo_out_parameter(p_out);CALL demo_

15、out_parameter(p_out);可以看到：调用前p_out的变量值为0调用后p_out的变量值为1016 INOUT参数例子参数例子nDELIMITER/nCREATE PROCEDURE demo_inout_parameter(INOUT p_inout_num int,INOUT p_inout_str varchar(50)nBEGIN nDECLARE id int default 0;nSET id=p_inout_num;nUPDATE user SET email=p_inout_str WHERE user_id=id;nSELECT score INTO p_in

16、out_num FROM user WHERE user_id=id;nSELECT phone INTO p_inout_str FROM user WHERE user_id=id;nEND n/nDELIMITER;17 INOUT参数例子参数例子n n通过预定义输出参数变量调用：通过预定义输出参数变量调用：n n先定义输出参数变量先定义输出参数变量先定义输出参数变量先定义输出参数变量n nset p_inout_num=5;set p_inout_num=5;n nset p_inout_str=T;set p_inout_str=T;n n再把变量作为参数调用再把变量作为参数调用再把

17、变量作为参数调用再把变量作为参数调用n ncall call demo_inout_parameter(p_inout_num,demo_inout_parameter(p_inout_num,p_inout_str);p_inout_str);18 INOUT参数例子（结果分析）参数例子（结果分析）调用前：p_inout_num 的变量值为5 p_inout_str 的变量值为 调用后：p_inout_num 的变量值为50 对应score字段值 p_inout_str 的变量值为83278678 对应phone字段值19 课堂练习课堂练习1n写一个存储过程，实现以下功能：n把上面USRE表

18、中积分（score）大于60的用户同步到一个VIP_USER表中ninsert into new_table(column1,column2column1,column2)select column1,column2 column1,column2 from old_tablen给积分60以下的女性会员（sex字段值为0），每人加5分n返回以上操作以后男会员的平均分与女会员的平均分20 变量变量nmysql存储过程中，定义变量有两种方式：存储过程中，定义变量有两种方式：n n会话变量：也叫用户变量，会话变量：也叫用户变量，会话变量：也叫用户变量，会话变量：也叫用户变量，使用set直接赋值，变量

19、名以 开头n例如:set num=1;set num=1;n可以在一个客户端会话的任何地方声明，作用域是整个会话。n n存储过程变量：存储过程变量：存储过程变量：存储过程变量：以 DECLARE 关键字声明的变量，只能在存储过程中使用，n例如：DECLARE mynum INT DEFAULT 0;DECLARE mynum INT DEFAULT 0;n主要用在存储过程中，或者是给存储传参数中。n两者的区别是：n以DECLARE声明的变量都会被初始化为 NULLn而会话变量（即开头的变量）则不会被再初始化n在一个会话内，只须初始化一次n会话断开后，会话变量也就消失21 存储过程变量存储过程变

20、量n变量定义n nDECLARE+DECLARE+变量名变量名变量名变量名+数据类型数据类型数据类型数据类型+DEFAULT value;+DEFAULT value;n数据类型为MySQL的数据类型n如:int,float,date,varchar(length)n默认值：DEFAULT value，可有可无n例如:n nDECLARE my_int int default 4000000;DECLARE my_int int default 4000000;n nDECLARE my_numeric number(8,2)DEFAULT 9.95;DECLARE my_numeric nu

21、mber(8,2)DEFAULT 9.95;n nDECLARE my_date date DEFAULT 1999-12-31;DECLARE my_date date DEFAULT 1999-12-31;n nDECLARE my_datetime datetime DEFAULT 1999-12-31 DECLARE my_datetime datetime DEFAULT 1999-12-31 23:59:59;23:59:59;n nDECLARE my_varchar varchar(255)DEFAULT This will not be DECLARE my_varchar

22、varchar(255)DEFAULT This will not be padded;padded;22 存储过程变量存储过程变量n变量赋值 n nSET SET 变量名变量名变量名变量名=表达式值表达式值表达式值表达式值 n例如:n nSET my_int=100SET my_int=100n nSET my_numeric=11.02SET my_numeric=11.02n nSET my_date=2009-11-21 SET my_date=2009-11-21 n nSET my_datetime=2009-11-21 20:50:50SET my_datetime=2009-1

23、1-21 20:50:50n nSET my_varchar=HelloSET my_varchar=Hellon变量取值n n直接调用变量名即可取得变量值直接调用变量名即可取得变量值直接调用变量名即可取得变量值直接调用变量名即可取得变量值n例如:nINSERT INTO table1 VALUES(my_intmy_int)n上面的INSERT语句直接用前面定义的my_intmy_int 变量名，即可取得变量值10010023 变量案例变量案例nDELIMITER/nCREATE PROCEDURE proc_declare_demo(IN p_in INTEGER)nBEGIN n nDE

24、CLARE mystr CHAR(10);DECLARE mystr CHAR(10);nIF p_in=17 THEN nSET mystrmystr=-birds-;nELSE nSET mystrmystr=-beasts-;nEND IF;nINSERT INTO user(user_name,pass_word,email,phone,sex,score)VALUES n(mystrmystr,mystrmystr,-,81234567,0,111);nEND n/nDELIMITER;24 变量作用域变量作用域n变量的作用范围：n往上回溯，从最靠近变量的第一个从最靠近变量的第一个从

25、最靠近变量的第一个从最靠近变量的第一个beginbegin开始开始开始开始n往下延伸，到最靠近变量的第一个到最靠近变量的第一个到最靠近变量的第一个到最靠近变量的第一个endend结束结束结束结束n n内部的变量比外部变量内部的变量比外部变量内部的变量比外部变量内部的变量比外部变量在其作用域范围内享有更高更高更高更高的优先权的优先权的优先权的优先权25 变量作用域案例变量作用域案例nDELIMITER/nCREATE PROCEDURE proc3()nbegin n ndeclare x1 varchar(5)default outer;declare x1 varchar(5)default

26、 outer;n begin n n declare x1 varchar(5)default inner;declare x1 varchar(5)default inner;n n select x1;select x1;n end;n nselect x1;select x1;nend;n/nDELIMITER;26 课程练习课程练习2n写一个存储过程，实现以下功能：n给上面用户表(User表)，增加一个备注字段(mark)n nALTER TABLE user ADD COLUMN mark VARCHAR(45)AFTER scoreALTER TABLE user ADD COLU

27、MN mark VARCHAR(45)AFTER scoren用DECLAREDECLARE定义几个存储过程变量n变量1值为：“不活跃会员”n变量2值为：“普通会员”n变量3值为：“高级会员”n根据上面定义的变量填充mark字段n如果积分在60分以下，填充变量1的值n如果积分在60-79分，填充变量2的值n如果积分在80分以上，填充变量3的值n返回以上操作以后每个等级会员的个数27 注释注释nMySQL存储过程可使用两种风格的注释n双模杠：n n -+空格空格空格空格n该风格一般用于单行单行单行单行注释n编程风格：n n/*/*/*/n一般用于多行多行多行多行注释28 注释样例注释样例29 条

28、件语句条件语句(ifthen)nifthen elseifthenelse语句nif后面跟判断条件判断条件判断条件判断条件nelseif后面跟判断条件判断条件判断条件判断条件nthen后面跟条件分支语句块条件分支语句块条件分支语句块条件分支语句块n可以有多个多个多个多个elseifthenelseifthen语句块，也可以没有nelse表示以上条件均不满足以上条件均不满足以上条件均不满足以上条件均不满足时会执行的语句块执行的语句块执行的语句块执行的语句块n整个条件语句块的最后面以end ifend if表示结束30 条件语句案例条件语句案例(ifthen)nDELIMITER/nCREATE

29、PROCEDURE proc_if_else(IN if_parameter int)nbegin ndeclare condition_para int;nset condition_para=if_parameter;n nif if condition_para=1 thenthen nINSERT INTO user(user_name,pass_word,email,phone,sex,score)VALUES n(if_user,if_user,if_,88888888,0,100);n nelseifelseif condition_para=2 condition_para=2

30、 thenthen nupdate user set score=200 where user_id5;n nelse else nupdate user set score=1000;n nend ifend if;nend;n/nDELIMITER;31 课程练习课程练习3n写一个存储过程：n如果输入参数值为1n把user表数据同步到user_info表n如果输入参数值为2n删除user_info表数据n如果输入参数值为3n把user表数据同步到user_bak表n如果输入参数值为4n删除user_bak表数据n如果输入参数值为5n同时删除user_info、user_bak表数据nuse

31、r、user_info、user_bak 三个表结构相同n用ifthen elseifthenelse语句实现32 SELECT中的中的IF条件语句条件语句nIF表达式：n nIF(expr1,expr2,expr3)IF(expr1,expr2,expr3)n如果 expr1expr1 是TRUE 则 IF()的返回值为expr2expr2n否则返回值则为 expr3expr3nIF()的返回值为数字值或字符串值，具体情况视其所在语境而定n nselect if(sex=0,select if(sex=0,女女女女,男男男男)from user;)from user;33 条件语句条件语句(

32、casewhen)ncasewhenthenelse语句ncase后面跟条件变量条件变量条件变量条件变量nwhen后面跟条件值条件值条件值条件值nthen后面跟条件分支语句块条件分支语句块条件分支语句块条件分支语句块n可以有多个多个多个多个whenthenwhenthen语句块，也可以只有有一个nelse表示以上条件均不满足以上条件均不满足以上条件均不满足以上条件均不满足时会执行的语句块执行的语句块执行的语句块执行的语句块n整个条件语句块最后面以end caseend case表示结束34 条件语句案例条件语句案例(casewhen)nDELIMITER/nCREATE PROCEDURE p

33、roc_case_when(IN case_parameter int)nbegin ndeclare condition_para int;nset condition_para=case_parameter;n ncasecase condition_paracondition_paran nwhenwhen 1 1 thenthen nINSERT INTO user(user_name,pass_word,email,phone,sex,score)VALUES(case_user,case_user,case_,7777777,0,200);n nwhenwhen 2 2 thent

34、hen nupdate user set score=300 where user_id5;n nelseelse nupdate user set score=2000;n nend caseend case;nend;n/nDELIMITER;35 课程练习课程练习4n用 casewhenthenelse 语句改写课课程练习程练习336 SELECT中的中的CASEWHENn语句格式：nselect case whencase when A条件 thenthen 字段1 whenwhen B条件 thenthen 字段2 elseelse 字段0 endend from tbn nSELE

35、CT CASE WHEN SEX=0 THEN SELECT CASE WHEN SEX=0 THEN 女女女女 WHEN WHEN SEX=1 THEN SEX=1 THEN 男男男男 ELSE ERROR END FROM USER;ELSE ERROR END FROM USER;37 循环语句循环语句(whiledo)nwhiledo语句nwhile后面跟条件表达式条件表达式条件表达式条件表达式ndo后面跟条件分支语句块条件分支语句块条件分支语句块条件分支语句块n整个条件语句块最后面以end whileend while表示结束38 循环语句案例循环语句案例(whiledo)nDELI

36、MITER/nCREATE PROCEDURE proc_while_do()nbegin ndeclare a int;nset a=0;n nwhilewhile a100a=200b=200 n nend repeatend repeat;nend;n/nDELIMITER;42 课程练习课程练习6n用 repeatuntil语句改写课程练习课程练习543 循环语句循环语句(loopleave)nloopleave语句nloop循环不需要初始条件nloop循环格式：循环标识循环标识循环标识循环标识 +:+loop +:+loopn n如：如：如：如：LOOP_LABLE :loop LO

37、OP_LABLE :loop n不需要结束条件,leave语句即表示跳出循环nleave的格式是：leave+leave+循环标识循环标识循环标识循环标识n n如：如：如：如：leave LOOP_LABLEleave LOOP_LABLEn整个条件语句块最后面以end loopend loop表示结束44 循环语句案例循环语句案例(loopleave)nDELIMITER/nCREATE PROCEDURE proc_loop()nbegin ndeclare c int;nset c=0;n nLOOP_LABLE:loopLOOP_LABLE:loop nINSERT INTO user

38、(user_name,pass_word,email,phone,sex,score)VALUES(loop_user,loop_user,loop_,22222222,0,c);nset c=c+1;nif c=400 then n nleave LOOP_LABLE;leave LOOP_LABLE;nend if;n nend loop;end loop;nend;n/nDELIMITER;45 课程练习课程练习7n用 loopleave语句改写课程练习课程练习546 游标游标(cursor)n游标的作用及属性n游标的作用是：n n对查询数据库所返回的记录进行遍历对查询数据库所返回的记录

39、进行遍历对查询数据库所返回的记录进行遍历对查询数据库所返回的记录进行遍历n游标如下属性：n游标是只读的只读的只读的只读的，也就是不能更新它；n游标是不能滚动的不能滚动的不能滚动的不能滚动的，也就是只能在一个方向上进只能在一个方向上进只能在一个方向上进只能在一个方向上进行遍历行遍历行遍历行遍历，不能在记录之间随意进退，不能跳过某些记录；n避免在已经打开游标的表上更新数据47 如何使用游标如何使用游标n首先用首先用DECLARE语句声明一个游标语句声明一个游标n格式：格式：n nDECLARE+DECLARE+游标名游标名游标名游标名+CURSOR FOR +CURSOR FOR +SELECTS

40、ELECT语句语句语句语句n n例如，声明一个叫例如，声明一个叫例如，声明一个叫例如，声明一个叫mycoursormycoursor的游标：的游标：的游标：的游标：nDECLARE mycoursor CURSOR FOR select user_name,password from user48 如何使用游标如何使用游标n其次需要使用其次需要使用OPEN语句来打开定义的游标语句来打开定义的游标n格式：格式：n nOPEN+OPEN+游标名游标名游标名游标名n n例如，打开一个叫例如，打开一个叫例如，打开一个叫例如，打开一个叫mycoursormycoursor的游标：的游标：的游标：的游标：

41、nOPEN mycoursor49 如何使用游标如何使用游标n接下来可以用接下来可以用FETCH语句来获得下一行数据语句来获得下一行数据n游标将移动到对应的记录上游标将移动到对应的记录上n类似类似java里面的里面的iteratorn格式：格式：n nFETCH+FETCH+游标名游标名游标名游标名+INTO +INTO +变量列表变量列表变量列表变量列表n n例如：例如：例如：例如：n n把把把把mycoursormycoursor游标的一行记录装进游标的一行记录装进游标的一行记录装进游标的一行记录装进my_namemy_name、my_pwd my_pwd 变量变量变量变量nFETCH m

42、ycoursor INTO my_name,my_pwdnmy_name、my_pwd 两个变量需提前定义好两个变量需提前定义好50 如何使用游标如何使用游标n最后操作都结束后要把游标释放掉最后操作都结束后要把游标释放掉n格式：格式：n nCLOSE+CLOSE+游标名游标名游标名游标名n n例如：关闭例如：关闭例如：关闭例如：关闭mycoursormycoursor游标游标游标游标nCLOSE mycoursor51 如何使用游标如何使用游标n定义一个定义一个NOT FOUND条件处理函数条件处理函数n游标到达记录的末尾时避免出现游标到达记录的末尾时避免出现“no data to fetch

43、”这样的错误这样的错误n格式：格式：n nDECLARE CONTINUE HANDLER FOR NOT FOUND DECLARE CONTINUE HANDLER FOR NOT FOUND+需要执行的语句需要执行的语句需要执行的语句需要执行的语句n例如：例如：n nDECLARE CONTINUE HANDLER FOR NOT FOUNDDECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products=1SET no_more_products=152 游标使用案例游标使用案例-建表建表nCREATE DATABASE IF NO

44、T EXISTS mydb;nUSE mydb;nDROP TABLE IF EXISTS products;nCREATE TABLE products(n id int(10)unsigned NOT NULL auto_increment,n name varchar(45)NOT NULL,n category varchar(45)NOT NULL,n price float NOT NULL,n code varchar(45)NOT NULL,n quantity int(10)unsigned NOT NULL,n PRIMARY KEY (id)n)ENGINE=InnoDB

45、 AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;nINSERT INTO products(id,name,category,price,code,quantity)VALUES n(1,cake,dessert,10,112011,323),n(2,cheese,dairy,12.5,112010,55),n(3,cookie,dessert,50,112012,323),n(4,carpet,furniture,100,113010,323),n(5,couch,furniture,1250,113011,15);53 游标使用案例游标使用案例n/*游标使用演

46、示获取库存量小于100的产品的代码code*/nDELIMITER$nDROP PROCEDURE IF EXISTS CursorProc$nCREATE PROCEDURE CursorProc()nBEGINn DECLARE no_more_products,quantity_in_stock INT DEFAULT 0;n DECLARE prd_code VARCHAR(255);n DECLARE cur_product CURSOR FOR SELECT code FROM products;/*First:Delcare a cursor,首先这里对游标进行定义*/n DEC

47、LARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products=1;/*when not found occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/n/*for loggging information 创建个临时表格来保持*/n CREATE TEMPORARY TABLE infologs(n Id int(11)NOT NULL AUTO_INCREMENT,n Msg varchar(255)NOT NULL,n PRIMARY KEY(Id)n);n OPEN cur_product;/*S

48、econd:Open the cursor 接着使用OPEN打开游标*/n FETCH cur_product INTO prd_code;/*Third:now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了记录的第一行*/n REPEATn SELECT quantity INTO quantity_in_stockn FROM productsn WHERE code=prd_code;n n IF quantity_in_stock 100 THENn INSERT INTO infologs(msg)n VALUES (prd_code);n E

49、ND IF;n FETCH cur_product INTO prd_code;n UNTIL no_more_products=1n END REPEAT;n CLOSE cur_product;/*Finally:cursor need be closed 用完后记得用CLOSE把资源释放掉*/n SELECT*FROM infologs;n DROP TABLE infologs;nEND$nDELIMITER;54 课程练习课程练习7n改用游标功能实现课程练习课程练习155 基本函数基本函数-字符串类字符串类nCHARSET(str)/返回字串字符集nCONCAT(string2,st

50、ring3,.)/连接字串nINSTR(string,substring)/返回substring首次在string中出现的位置,不存在返回0nLCASE(string2)/转换成小写nUCASE(string2)/转换成大写nLEFT(string2,length)/从string2中的左边起取length个字符nRIGHT(string2,length)/从string2中的右边起取length个字符nLENGTH(string)/string长度nLTRIM(string2)/去除前端空格nRTRIM(string2)/去除后端空格nTRIM(string2)/去除前后端空格nSPACE