《数据库高级应用技术(存储过程)》由会员分享，可在线阅读，更多相关《数据库高级应用技术(存储过程)（67页珍藏版）》请在金锄头文库上搜索。
1、 高等职业技术院校教材 数据库高级应用技术 主编：温立辉Date1存储过程本单元教学目标了解存储过程的种类认识、了解存储过程的作用掌握存储过程的开发语法熟练使用数据库IDE开发、调试存储过程Date2存储过程概念（一）nSQL语句：nSQL语句在执行的时候需要先编译先编译，然后执行然后执行n存储过程：n存储过程（Stored Procedure）n n是一组为了完成特定功能的是一组为了完成特定功能的SQLSQL语句集语句集n n经编译后存储在数据库中经编译后存储在数据库中n用户通过指定存储过程的名字与参数存储过程的名字与参数（如果该存储 过程带有参数）来调用执行它。Date3存储过程概念（二）
2、n一个存储过程是一个可编程可编程的函数函数n它在数据库中创建并保存n它由SQLSQL语句语句和一些特殊的控制结构控制结构组成n存储过程适用于以下场合n在不同的平台（应用程序）不同的平台（应用程序）上执行相同的函数n n封装特定功能封装特定功能n是数据库编程中面向对象方法的模拟Date4存储过程优点（一）n(1).存储过程增强了SQL语言的功能和灵活性n存储过程可以用流控制语句编写，有很强的灵活性n可以完成复杂的判断和较复杂的运算n(2).存储过程是允许编程的标准组件n被创建后，可以在程序中被多次调用，而不必重新编写该存 储过程的SQL语句n数据库专业人员可以随时对存储过程进行修改，对应用程序
3、源代码毫无影响n(3).存储过程能实现较快的执行速度n如果某一操作包含大量的SQL代码或分别被多次执行，n存储过程比批处理执行速度快很多，因存储过程是预编译的n批处理的SQL语句在每次运行时要进行编译，速度相对慢Date5存储过程优点（二）n(4).存储过程能过减少网络流量。n当同一操作所涉及的众多SQL语句被编码到存储过程中n在客户机上调用该存储过程时，网络中只需传送该调用语句n从而大大减少了网络流量并降低了网络负载n(5).存储过程被作为一种安全机制来充分利用n系统管理员通过执行某一存储过程的权限进行限制n能够实现对相应的数据的访问权限的限制，n避免了非授权用户对数据的访问，从而保证了数据
4、的安全Date6存储过程的创建nMySQL格式：n nCREATE PROCEDURECREATE PROCEDURE 存储过程名存储过程名 ( 过过 程参数程参数,.,.)n nBEGINBEGINnn过程控制语句（Transaction-SQL）nn nEND END Date7存储过程案例-创建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,
5、n user_name varchar(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,pas
6、s_word,email,phone,sex,score) 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
7、,40),n (8,Honey,Honey,H,83278904,1,80),n (9,Wendy,Wendy,W,83278452,0,50),n (10,Rose,Rose,R,83278904,0,30);Date8存储过程案例-开发编码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 ;Date9案例语句解释nDELIMITER /n表示把MySQL分隔符修改为： “/“nMySQL
8、默认以“;”为分隔符n如果没有声明分割符，编译器会把存储过程中的 ”;” 当成SQL语句结束分隔符 进行处理，则存储过程的编译过程会报错；所以要事先用DELIMITER关键字 声明当前段分隔符，这样MySQL才会将 “;” 当做存储过程中的代码，而不是 语句MySQL中语句的结束分隔符n nDELIMITER ;DELIMITER ;n用完了之后要把分隔符还原为：“ ; “n存储过程根据需要可能会有输入、输出、输入输出参数n本案例中有一个输入参数s，类型是int型n如果有多个参数用“,“分割开n n过程体的开始与结束使用过程体的开始与结束使用BEGINBEGIN与与ENDEND进行标识。进行标
9、识。Date10存储过程调用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;Date11存储过程参数n n存储过程共有三种参数类型存储过程共有三种参数类型,IN,OUT,INOUT,IN,OUT,INOUT,形式如：形式如：nCREATE PROCEDURE
10、(IN |OUT |INOUT 参数名 数据 类形.)nIN 输入参数:n表示该参数值必须在调用存储过程时指定，在存储过程中修改该 参数值将不被返回，为默认值nOUT 输出参数:n该值可在存储过程内部被改变，并可返回nINOUT 输入输出参数:n调用时指定，并且可被改变和返回Date12IN参数例子nDELIMITER / nCREATE PROCEDURE demo_in_parameter(IN p_id int, IN p_score_add int) nBEGIN nupdate user set score=(score+p_score_add) where user_id=p_id
11、; nEND; n/ nDELIMITER ;Date13存储过程调用（ IN参数）n n直接调用：直接调用：n nCALL CALL demo_in_parameterdemo_in_parameter (10,5000) ; (10,5000) ;n n通过预定义参数变量调用：通过预定义参数变量调用：n n先定义两个参数变量先定义两个参数变量n nset set p_idp_id=10 ;=10 ;n nset set p_score_addp_score_add=5000 ;=5000 ;n n再把变量作为参数调用再把变量作为参数调用n nCALL CALL demo_in_parame
12、ter(p_id,p_score_adddemo_in_parameter(p_id,p_score_add) ;) ;调用后可看到user_id为10的score字段的值增加了5000Date14OUT参数例子nDELIMITER / nCREATE PROCEDURE demo_out_parameter(OUT p_out int) nBEGIN nSELECT COUNT(*) INTO p_out FROM user; nEND n/ nDELIMITER ;Date15存储过程调用（ OUT参数）n n通过预定义输出参数变量调用：通过预定义输出参数变量调用：n n先定义输出参数变量
13、先定义输出参数变量n nset set p_outp_out=0;=0;n n再把变量作为参数调用再把变量作为参数调用n nCALL CALL demo_out_parameter(p_outdemo_out_parameter(p_out););可以看到： 调用前p_out的变量值为0 调用后p_out的变量值为10Date16INOUT参数例子nDELIMITER / nCREATE PROCEDURE demo_inout_parameter(INOUT p_inout_num int,INOUT p_inout_str varchar(50) nBEGIN nDECLARE id in
14、t default 0; nSET id=p_inout_num;nUPDATE user SET email=p_inout_str WHERE user_id=id;nSELECT score INTO p_inout_num FROM user WHERE user_id=id; nSELECT phone INTO p_inout_str FROM user WHERE user_id=id;nEND n/ nDELIMITER ;Date17INOUT参数例子n n通过预定义输出参数变量调用：通过预定义输出参数变量调用：n n先定义输出参数变量先定义输出参数变量n nset p_in
15、out_num=5;set p_inout_num=5;n nset p_inout_str=T;set p_inout_str=T;n n再把变量作为参数调用再把变量作为参数调用n ncall call demo_inout_parameter(p_inout_num,demo_inout_parameter(p_inout_num, p_inout_strp_inout_str););Date18INOUT参数例子（结果分析）调用前：p_inout_num的变量值为5p_inout_str的变量值为T调用后：p_inout_num的变量值为50对应score字段值p_inout_str的变量值为83278678对应phone字段值Date19课堂练习1n写一个存储过程，实现以
