自定义View系列教程00–推翻自己和过往,重学自定义View 探索Android软键盘的疑难杂症 Android多分辨率适配框架(1)— 核心基础 Android程序员C语言自学完备手册 版权声明
存储过程概述在开发过程中,我们经常会遇到重复使用某一功能的情况。为此,MySQL引人了存储过程(Stored Procedure)这一技术。所谓存储过程指的就是一条或多条SQL语句的集合。存储过程可以将一些列复杂操作封装成一个代码块,以便重复使用,从而极大地减少数据库开发人的工作量提升开发效率。SQL语句需要先编译然后执行,而存储过程可将为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字通过传参的方式对其进行调用。存储过程可看做是编程的函数,它允许以传参调用的访问方式。 存储过程入门示例第一步 准备数据,代码如下: DROP TABLE IF EXISTS student;CREATE TABLE student ( sid CHAR(6), sname VARCHAR(50), age INT, gender VARCHAR(50) DEFAULT 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
创建学生表并插入数据。 第二步 编写存储过程,代码如下:
在该存储过程中我们期望依据传入的指定年纪查询出符合条件的学生;详解如下:
图示如下: 调用存储过程,代码如下: call procedureHelloWorld(15);
利用 结果如下: 至此,我们完成了存储过程的入门程序。假若您没有完全看懂该代码也无关紧要,毕竟只是让大家对存储过程先有个大概了解;接下来,我们将从基础语法开始全面深入地学习存储过程。 存储过程的参数MySQL存储过程的定义中可不传参,假若传参则有三种类型:IN,OUT,INOUT,形式如下:
IN参数示例代码如下: mysql> DELIMITER //mysql> CREATE PROCEDURE findStudent(IN sage INT) -> BEGIN -> SELECT * FROM student WHERE age=sage; -> END //Query OK, 0 rows affected (0.01 sec)mysql> DELIMITER ;mysql> call findStudent(16);
在此定义存储过程findStudent,该存储过程中有个IN参数,该参数为INT类型叫做sage 图示如下: 其实,我们还可以在调用存储过程中使用用户变量。 用户变量语法,形式如下:
创建用户变量,方式如下: SET 用户变量=初始值;
查看用户变量的值,方式如下:
当定义用户变量后可方便开发过程的代码编写,只要连接未关闭我们均可直接使用该变量。当然,当连接关闭时所有客户变量将自动释放。 示例如下: mysql> SET @age=16;Query OK, 0 rows affected (0.00 sec)mysql> call findStudent(@age);
OUT参数示例代码如下:
定义存储过程countStudent,该存储过程中有个OUT参数,该参数为INT类型叫做total。在存储体中将统计的结果利用 图示如下: INOUT参数示例代码如下: mysql> DROP PROCEDURE IF EXISTS searchStudentGender;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> CREATE PROCEDURE searchStudentGender(INOUT message VARCHAR(50)) -> BEGIN -> SELECT gender INTO message FROM student WHERE sname=message; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql>mysql> SET @info='lili';Query OK, 0 rows affected (0.00 sec)mysql> CALL searchStudentGender(@info);Query OK, 1 row affected (0.00 sec)mysql> SELECT @info;
定义存储过程searchStudentGender,该存储过程中有个INOUT参数,该参数为VARCHAR(50)类型叫做message。在该示例中message既当做输入参数又当做输出参数,即输入参数为学生的姓名lili返回的是学生的性别male。在调用存储过程时将初始值为lili的用户变量@info传入存储过程,调用存储过程结束后再次查询@info的值为male。 图示如下: 变量在编写存储过程中有时需要使用变量保存数据处理过程中的值。这些变量的作用范围为BEGIN…END,语法如下:
在该语法中:关键字 在定义变量之后,可使用 SET varName = value;
变量使用示例1 代码如下:
图示如下: 变量使用示例2 代码如下: DROP PROCEDURE IF EXISTS findStudent;DELIMITER //CREATE PROCEDURE findStudent(IN studentID CHAR(6))BEGINDECLARE studentName VARCHAR(50);DECLARE studentGender VARCHAR(50);-- 将查询结果保存至变量studentName和studentGender中SELECT sname,gender INTO studentName,studentGender FROM student WHERE sid=studentID;-- 显示studentName和studentGender中保存的值SELECT studentName,studentGender;END //DELIMITER ;call findStudent('S_1001');
图示如下: 流程控通过之前的学习,我已经对存储过程有了基本的了解。接下来,我们来学习在编写存储过程中非常重要的流程控制。流程控制语句用于将多个SQL语句划分或组合成符合业务逻辑的代码块。MySQL中的流程控制语句包括:IF语句、CASE语句、 LOOP 语句、WHILE 语句、LEAVE 语句、ITERATE 语句、REPEAT 语句、WHILE语句。 IF IF语句包含多个条件判断,根据结果为TRUE、FALSE执行语句;它与我们在编程语言中学的if、else if、else非常类似;语法格式如下:
示例如下: mysql> DROP PROCEDURE IF EXISTS testIF;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> CREATE PROCEDURE testIF(IN num INT) -> BEGIN -> DECLARE result VARCHAR(20); -> IF num < -1 THEN -> SET result = 'negative number'; -> ELSEIF num = 0 THEN -> SET result = 'number is zero'; -> ELSE -> SET result = 'positive number'; -> END IF; -> SELECT result; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testIF(-9527);
图示如下: CASE是另一个条件判断的语句,该语句有两种语法格式。 第一种语法格式如下:
示例如下: mysql> DELIMITER //mysql> CREATE PROCEDURE testCASE1(IN num INT) -> BEGIN -> DECLARE result VARCHAR(20); -> CASE num -> WHEN 1 THEN SET result = 'num is 1'; -> WHEN 2 THEN SET result = 'num is 2'; -> ELSE SET result = 'num not 1 or 2'; -> END CASE; -> -> SELECT result; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testCASE1(1);
图示如下:
示例如下: mysql> DROP PROCEDURE IF EXISTS testCASE2;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> CREATE PROCEDURE testCASE2(IN num INT) -> BEGIN -> DECLARE result VARCHAR(20); -> CASE -> WHEN num < 0 THEN SET result = 'negative number'; -> WHEN num = 0 THEN SET result = 'number is zero'; -> ELSE SET result = 'positive number'; -> END CASE; -> -> SELECT result; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testCASE2(1);
图示如下: LOOP LOOP是一种循环语句,用来重复执行某些语句。在执行过程中可使用LEAVE语句跳出循环,也可以嵌套IF等判断语句。语法格式如下:
在该语法中,loop_label表示LOOP语句的标注名称,该参数可省略。 示例如下: mysql> DROP PROCEDURE IF EXISTS testLOOP;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> -- 利用存储过程求某区间的总和mysql> CREATE PROCEDURE testLOOP(IN start INT,IN end INT) -> BEGIN -> DECLARE sumResult INT DEFAULT 0; -> add_loop: LOOP -> SET sumResult=sumResult+start; -> SET start=start+1; -> IF start > end THEN -> LEAVE add_loop; -> END IF; -> END LOOP add_loop; -> -> SELECT sumResult; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testLOOP(0,100);
图示如下: ITERATE ITERATE表示再次循环,该语句作用是将执行顺序转到语句段开头处再执行,它与编程语言的continue非常类似;请注意:该语句只可出现在LOOP、REPEAT、WHILE语句内;语法格式如下:
示例如下: mysql> DROP PROCEDURE IF EXISTS testITERATE;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> -- 利用存储过程求某区间的总和mysql> CREATE PROCEDURE testITERATE(IN start INT,IN end INT) -> BEGIN -> DECLARE sumResult INT DEFAULT 0; -> add_loop: LOOP -> SET sumResult=sumResult+start; -> SET start=start+1; -> IF start <= end THEN -> ITERATE add_loop; -> ELSE LEAVE add_loop; -> END IF; -> END LOOP add_loop; -> -> SELECT sumResult; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testITERATE(0,100);
结果如下: REPEAT语句 REPEAT表示带判断条件的循环过程。每次语句执行完毕之后,会对条件表达式进行判断,若表达式为TRUE则循环结束,否则重复执行。REPEAT非常类似于do…while,语法格式如下:
在该语法中,repeat_label表示REPEAT的标注名称,该参数可省略。 示例如下: mysql> DROP PROCEDURE IF EXISTS testREPEAT;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> -- 利用存储过程求某区间的总和mysql> CREATE PROCEDURE testREPEAT(IN start INT,IN end INT) -> BEGIN -> DECLARE sumResult INT DEFAULT 0; -> REPEAT -> SET sumResult = sumResult + start; -> SET start=start+1; -> UNTIL start > end -> END REPEAT; -> -> SELECT sumResult; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testREPEAT(0,100);
图示如下: WHILE用于带判断条件的循环过程。它与REPEAT不同,WHILE语句先判断表达式,为真则执行循环内的语句,否则退出循环。WHILE与编程语言的while非常类似;语法格式如下:
在该语法中,while_label表示WHILE的标注名称,该参数可以省略。 示例如下: mysql> DROP PROCEDURE IF EXISTS testWHILE;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> -- 利用存储过程求某区间的总和mysql> CREATE PROCEDURE testWHILE(IN start INT,IN end INT) -> BEGIN -> DECLARE sumResult INT DEFAULT 0; -> WHILE start <= end DO -> SET sumResult = sumResult + start; -> SET start=start+1; -> END WHILE; -> -> SELECT sumResult; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> call testWHILE(0,100);
图示如下: 游标在数据库中游标(cursor)是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段。就本质而言,游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。例如,在编写存储过程时,查询语句可能会返回多条记录或大量记录,此时则需要使用游标来逐条读取查询结果集中的记录。 游标使用步骤为:定义游标、打开游标、使用游标、关闭游标、释放游标;在此,我们对一一进行介绍。 定义游标 语法如下:
打开游标 语法如下: OPEN 游标名;
使用游标 语法如下:
在该语法中,利用DECLARE声明变量用于存储查询出来的数据;利用FETCH 获取数据并保存至DECLARE声明的变量中;其中,FETCH NEXT表示获取下一行数据 在使用游标时请注意:在初始状态中,游标默认指向数据集的第一行数据之前。所以,在使用游标时应该先执行FETCH NEXT操作让其指向第一行数据。 关闭游标 语法如下: CLOSE 游标名;
释放游标 语法如下:
在正式进入到游标的学习之前,我们先准备测试数据,代码如下: DROP TABLE IF EXISTS student;CREATE TABLE student ( sid CHAR(6), sname VARCHAR(50), age INT, gender VARCHAR(50) DEFAULT 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1001', 'lili', 14, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1002', 'wang', 15, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1003', 'tywd', 16, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1004', 'hfgs', 17, 'female');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1005', 'qwer', 18, 'male');INSERT INTO student (sid,sname,age,gender) VALUES ('S_1006', 'zxsd', 19, 'female');
游标使用示例1 代码如下:
在该存储过程中,查询后得到的结果集为表中所有数据。在此,利用 图示如下: 游标使用示例2 代码如下: mysql> DROP PROCEDURE IF EXISTS cursorTest2;Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER //mysql> CREATE PROCEDURE cursorTest2() -> BEGIN -> -- 声明与对应列类型相同的4个变量 -> DECLARE studentID CHAR(6); -> DECLARE studentName VARCHAR(50); -> DECLARE studentAge INT; -> DECLARE studentGender VARCHAR(50); -> -> -- 声明计数器 -> DECLARE count INT DEFAULT 0; -> -> DECLARE total INT DEFAULT 0; -> -> -- 定义游标studentCursor -> DECLARE studentCursor cursor for SELECT * FROM student; -> -> set total = (SELECT count(*) FROM student); -> -> -- 打开游标 -> OPEN studentCursor; -> -- 利用REPEAT语句循环取出结果集中的数据 -> REPEAT -> FETCH next FROM studentCursor INTO studentID,studentName,studentAge,studentGender; -> SELECT studentID,studentName,studentAge,studentGender; -> SET count=count+1; -> UNTIL count=total -> END REPEAT; -> -> -- 关闭游标 -> CLOSE studentCursor; -> END //Query OK, 0 rows affected (0.00 sec)mysql> DELIMITER ;mysql> -- 调用存储过程mysql> CALL cursorTest2();
在该示例中利用REPEAT语句循环取出结果集中的每条数据并进行展示。在循环过程中FETCH next不断地指向结果集的下一条数据。 结果如下: |
|
来自: 佬总图书馆 > 《区块链、物联网、虚拟数字货币》