网站建设前的市场分析,没有备案的网站百度不收录,在火炉做网站公园坐什么车,手机网站自适应代码什么是存储过程我们前面所学习的MySQL语句都是针对一个表或几个表的单条 SQL 语句#xff0c;但是在数据库的实际操作中#xff0c;并非所有操作都那么简单#xff0c;经常会有一个完整的操作需要多条SQL语句处理多个表才能完成。例如#xff0c;为了确认学生能否毕业…什么是存储过程 我们前面所学习的MySQL语句都是针对一个表或几个表的单条 SQL 语句但是在数据库的实际操作中并非所有操作都那么简单经常会有一个完整的操作需要多条SQL语句处理多个表才能完成。例如为了确认学生能否毕业需要同时查询学生档案表、成绩表和综合表此时就需要使用多条 SQL 语句来针对几个数据表完成这个处理要求。存储过程可以有效地完成这个数据库操作。 存储过程是数据库存储的一个重要的功能但是 MySQL 在 5.0 以前并不支持存储过程这使得 MySQL 在应用上大打折扣。好在 MySQL 5.0 终于开始已经支持存储过程这样即可以大大提高数据库的处理速度同时也可以提高数据库编程的灵活性。 存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL语句写好并用一个指定名称存储起来这个过程经编译和优化后存储在数据库服务器中因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时只需调用“CALL存储过程名字”即可自动完成。 常用操作数据库的 SQL 语句在执行的时候需要先编译然后执行。存储过程则采用另一种方式来执行 SQL 语句。 一个存储过程是一个可编程的函数它在数据库中创建并保存一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时存储过程尤为合适。存储过程通常有如下优点封装性 存储过程被创建后可以在程序中被多次调用而不必重新编写该存储过程的 SQL 语句并且数据库专业人员可以随时对存储过程进行修改而不会影响到调用它的应用程序源代码。可增强 SQL 语句的功能和灵活性 存储过程可以用流程控制语句编写有很强的灵活性可以完成复杂的判断和较复杂的运算。可减少网络流量 由于存储过程是在服务器端运行的且执行速度快因此当客户计算机上调用该存储过程时网络中传送的只是该调用语句从而可降低网络负载。高性能 存储过程执行一次后产生的二进制代码就驻留在缓冲区在以后的调用中只需要从缓冲区中执行二进制代码即可从而提高了系统的效率和性能。提高数据库的安全性和数据的完整性 使用存储过程可以完成所有数据库操作并且可以通过编程的方式控制数据库信息访问的权限。创建存储过程 MySQL 存储过程是一些 SQL 语句的集合比如有的时候我们可能需要一大串的 SQL 语句或者说在编写 SQL语句的过程中还需要设置一些变量的值这个时候我们就完全有必要编写一个存储过程。下面我们来介绍一下如何创建一个存储过程。 基本语法 可以使用 CREATE PROCEDURE 语句创建存储过程。语法格式如下CREATE PROCEDURE 过程名 ( [过程参数[,…] ] ) 过程体 [过程参数[,…] ] 格式 [ IN | OUT | INOUT ] 参数名 类型语法说明如下过程名 存储过程的名称默认在当前数据库中创建。若需要在特定数据库中创建存储过程则要在名称前面加上数据库的名称即 db_name.sp_name。需要注意的是名称应当尽量避免选取与 MySQL 内置函数相同的名称否则会发生错误。过程参数 存储过程的参数列表。其中参数名为参数名类型为参数的类型可以是任何有效的MySQL 数据类型。当有多个参数时参数列表中彼此间用逗号分隔。存储过程可以没有参数此时存储过程的名称后仍需加上一对括号也可以有 1 个或多个参数。MySQL 存储过程支持三种类型的参数即输入参数、输出参数和输入/输出参数分别用 IN、OUT 和 INOUT三个关键字标识。其中输入参数可以传递给一个存储过程输出参数用于存储过程需要返回一个操作结果的情形而输入/输出参数既可以充当输入参数也可以充当输出参数。需要注意的是参数的取名不要与数据表的列名相同否则尽管不会返回出错信息但是存储过程的 SQL 语句会将参数名看作列名从而引发不可预知的结果。 过程体 存储过程的主体部分也称为存储过程体包含在过程调用的时候必须执行的 SQL 语句。这个部分以关键字 BEGIN 开始以关键字 END 结束。若存储过程体中只有一条 SQL 语句则可以省略 BEGINEND 标志。在存储过程的创建中经常会用到一个十分重要的 MySQL 命令即 DELIMITER 命令特别是对于通过命令行的方式来操作 MySQL 数据库的使用者更是要学会使用该命令。在 MySQL 中服务器处理 SQL 语句默认是以分号作为语句结束标志的。然而在创建存储过程时存储过程体可能包含有多条 SQL 语句这些 SQL 语句如果仍以分号作为语句结束符那么 MySQL 服务器在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符而不再去处理存储过程体中后面的 SQL 语句这样显然不行。为解决这个问题通常可使用 DELIMITER 命令将结束命令修改为其他字符。语法格式如下DELIMITER $$语法说明如下 $$ 是用户定义的结束符通常这个符号可以是一些特殊的符号如两个“?”或两个“”等。 当使用 DELIMITER 命令时应该避免使用反斜杠“\”字符因为它是 MySQL 的转义字符。在 MySQL 命令行客户端输入如下SQL语句。mysql DELIMITER ??成功执行这条 SQL 语句后任何命令、语句或程序的结束标志就换为两个问号“??”了。若希望换回默认的分号“;”作为结束标志则在 MySQL 命令行客户端输入下列语句即可mysql DELIMITER ;注意DELIMITER 和分号“;”之间一定要有一个空格。在创建存储过程时必须具有 CREATE ROUTINE 权限。可以使用 SHOW PROCEDURE STATUS 命令查看数据库中存在哪些存储过程若要查看某个存储过程的具体信息则可以使用 SHOW CREATE PROCEDURE 存储过程名。创建不带参数的存储过程 【实例 1】创建名称为 ShowStuScore 的存储过程存储过程的作用是从学生成绩信息表中查询学生的成绩信息输入的 SQL 语句和执行过程如下所示。mysql DELIMITER //
mysql CREATE PROCEDURE ShowStuScore()- BEGIN- SELECT * FROM tb_students_score;- END //
Query OK 0 rows affected (0.09 sec)创建存储过程 ShowStuScore 后通过 CALL 语句调用该存储过程的 SQL 语句和执行结果如下所示。mysql DELIMITER ;
mysql CALL ShowStuScore();
-----------------------------
| student_name | student_score |
-----------------------------
| Dany | 90 |
| Green | 99 |
| Henry | 95 |
| Jane | 98 |
| Jim | 88 |
| John | 94 |
| Lily | 100 |
| Susan | 96 |
| Thomas | 93 |
| Tom | 89 |
-----------------------------
10 rows in set (0.00 sec)
Query OK, 0 rows affected (0.02 sec)创建带参数的存储过程 【实例 2】创建名称为 GetScoreByStu 的存储过程输入参数是学生姓名。存储过程的作用是通过输入的学生姓名从学生成绩信息表中查询指定学生的成绩信息输入的 SQL 语句和执行过程如下所示。mysql DELIMITER //
mysql CREATE PROCEDURE GetScoreByStu- (IN name VARCHAR(30))- BEGIN- SELECT student_score FROM tb_students_score- WHERE student_namename;- END //
Query OK, 0 rows affected (0.01 sec)创建存储过程 GetScoreByStu 后通过 CALL 语句调用该存储过程的 SQL 语句和执行结果如下所示。mysql DELIMITER ;
mysql CALL GetScoreByStu(Green);
---------------
| student_score |
---------------
| 99 |
---------------
1 row in set (0.03 sec)
Query OK, 0 rows affected (0.03 sec)修改存储过程 在实际开发过程中业务需求修改的情况时有发生这样不可避免的需要修改 MySQL 中存储过程的特征 。基本语法 可以使用 ALTER PROCEDURE 语句修改存储过程的某些特征。语法格式如下ALTER PROCEDURE 过程名 [ 特征 … ]提示这个语法用于修改存储过程的某些特征如要修改存储过程的内容可以先删除该存储过程再重新创建。修改存储过程的内容和名称 修改存储过程的内容可以通过删除原存储过程再以相同的命名创建新的存储过程。 修改存储过程的名称可以通过删除原存储过程再以不同的命名创建新的存储过程。删除存储过程当 MySQL 数据库中存在废弃的存储过程是我们需要将它从数据库中删除。 基本语法 存储过程被创建后保存在数据库服务器上直至被删除。可以使用 DROP PROCEDURE 语句删除数据库中已创建的存储过程。语法格式如下DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] 过程名语法说明如下过程名 指定要删除的存储过程的名称。IF EXISTS 指定这个关键字用于防止因删除不存在的存储过程而引发的错误。 注意存储过程名称后面没有参数列表也没有括号在删除之前必须确认该存储过程没有任何依赖关系否则会导致其他与之关联的存储过程无法运行。删除存储过程 删除存储过程 GetScoreByStu查看存储过程的运行结果如下所示。mysql DROP PROCEDURE GetScoreByStu;
Query OK, 0 rows affected (0.00 sec)
mysql CALL GetScoreByStu(Green);
ERROR 1305 (42000): PROCEDURE test_db.GetScoreByStu does not exist判断存储过程IF语法如下CREATEPROCEDURE sp_name ([proc_parameter[,...]])routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typeroutine_body:Valid SQL routine statementIF search_condition THEN statement_list[ELSEIF search_condition THEN statement_list] ...[ELSE statement_list]
END IF 创建一个student表并插入三条数据mysql create table student(- id int,- name varchar(30),- score float);
Query OK, 0 rows affected (0.01 sec)mysql insert into student values(1,z3,30),(2,l4,60),(3,w5,90);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0 创建存储过程输入“a”返回大于等于60分数的学生信息输入“b”返回大于等于80分数的学生信息输入“c”返回大于等于90分数的学生信息输入其他字符串则返回全部学生信息。mysql delimiter $$
mysql create procedure t1(in chars varchar(10))- begin- if charsa then- select * from student where score60;- elseif charsb then- select * from student where score80;- elseif charsc then- select * from student where score90;- else select * from student;- end if;- end $$
Query OK, 0 rows affected (0.08 sec)mysql delimiter ;
mysql call t1(a);
-------------------
| id | name | score |
-------------------
| 2 | l4 | 60 |
| 3 | w5 | 90 |
-------------------
2 rows in set (0.00 sec)Query OK, 0 rows affected (0.01 sec)mysql call t1(b);
-------------------
| id | name | score |
-------------------
| 3 | w5 | 90 |
-------------------
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql call t1(c);
-------------------
| id | name | score |
-------------------
| 3 | w5 | 90 |
-------------------
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql call t1(abc);
-------------------
| id | name | score |
-------------------
| 1 | z3 | 30 |
| 2 | l4 | 60 |
| 3 | w5 | 90 |
-------------------
3 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)CASE语法如下CREATEPROCEDURE sp_name ([proc_parameter[,...]])routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typeroutine_body:Valid SQL routine statementCASE case_valueWHEN when_value THEN statement_list[WHEN when_value THEN statement_list] ...[ELSE statement_list]
END CASE 同样上面使用的if语法创建存储过程这次使用case语法创建。输入“a”返回大于等于60分数的学生信息输入“b”返回大于等于80分数的学生信息输入“c”返回大于等于90分数的学生信息输入其他字符串则返回全部学生信息。mysql create procedure t2(in chars varchar(10))- begin- case chars- when a then select * from student where score60;- when b then select * from student where score80;- when c then select * from student where score90;- else select * from student;- end case;- end$$
Query OK, 0 rows affected (0.01 sec)mysql delimiter ;
mysql call t2(a);
-------------------
| id | name | score |
-------------------
| 2 | l4 | 60 |
| 3 | w5 | 90 |
-------------------
2 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql call t2(b);
-------------------
| id | name | score |
-------------------
| 3 | w5 | 90 |
-------------------
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql call t2(c);
-------------------
| id | name | score |
-------------------
| 3 | w5 | 90 |
-------------------
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql call t2(d);
-------------------
| id | name | score |
-------------------
| 1 | z3 | 30 |
| 2 | l4 | 60 |
| 3 | w5 | 90 |
-------------------
3 rows in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)循环存储过程WHILE语法如下CREATEPROCEDURE sp_name ([proc_parameter[,...]])routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typeroutine_body:Valid SQL routine statementName: DECLARE VARIABLE
Description:
Syntax:
DECLARE var_name [, var_name] ... type [DEFAULT value]Syntax:
WHILE search_condition DOstatement_list
END WHILE 创建循环存储过程实现1234...100我算过了和为5050while先判断后执行条件满足情况下则执行循环体内容不满足则结束循环详情看下面命令吧。mysql create procedure t3()- begin- declare num int default 0; #定义num默认值为0- declare summ int default 0; #定义summ默认值为0- while num100 do #num小于等于100后停止- set summsummnum; #第一次循环为0第二次为num上次循环的结果- set numnum1; #num1继续循环- end while; #跳过满足循环后跳出- select summ; #打印出summ循环相加的结果- end $$ #结束
Query OK, 0 rows affected (0.00 sec)mysql delimiter ;
mysql call t3();
------
| summ |
------
| 5050 |
------
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec) 上面的命令虽然实现了循环但是局限性很大只能打印出加到100也就是说每次打印的都是5050如果这次我们需要的更多呢下面可以使用参数代替。mysql delimiter $$
mysql create procedure t4(in a int)- begin- declare num int default 0;- declare summ int default 0;- while numa do- set summsummnum;- set numnum1;- end while;- select summ;- end $$
Query OK, 0 rows affected (0.00 sec)mysql delimiter ;
mysql call t4(10);
------
| summ |
------
| 55 |
------
1 row in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)mysql call t4(1000);
--------
| summ |
--------
| 500500 |
--------
1 row in set (0.01 sec)Query OK, 0 rows affected (0.01 sec)REPEAT语法如下CREATEPROCEDURE sp_name ([proc_parameter[,...]])routine_bodyproc_parameter:[ IN | OUT | INOUT ] param_name typeroutine_body:Valid SQL routine statementSyntax:
REPEATstatement_list
UNTIL search_condition
END REPEAT 下面的循环也是根据123...100但是和while不同的是repeat的结构是先执行后判断先执行相关操作 再判断是否满足条件满足结束循环。mysql delimiter $$
mysql create procedure t5(in a int)- begin- declare num int default 0;- declare summ int default 0;- repeat- set summsummnum;- set numnum1;- until numa end repeat;- select summ;- end $$
Query OK, 0 rows affected (0.05 sec)mysql delimiter ;
mysql call t5(10);
------
| summ |
------
| 55 |
------
1 row in set (0.02 sec)Query OK, 0 rows affected (0.03 sec)mysql call t5(100);
------
| summ |
------
| 5050 |
------
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)学习不是一两天的事活到老学到老坚持下去总有一天会有成果。MySQL专栏持续更新对你有帮助的话收藏起来吧
