建设部规范网站,网页打不开摄像头怎么设置,做一借款撮合网站,深圳网站建设设计平台五、企业级开发技术
5.1 存储过程
关于存储过程我只能说请看下图#xff0c;这是阿里巴巴发布的《阿里巴巴Java开发手册#xff08;终极版#xff09;v1.3版本》在 MySQL 第七条中强制指出禁止使用存储过程 所以对于存储过程不必深究#xff0c;做到会写能看懂即可 [外链…五、企业级开发技术
5.1 存储过程
关于存储过程我只能说请看下图这是阿里巴巴发布的《阿里巴巴Java开发手册终极版v1.3版本》在 MySQL 第七条中强制指出禁止使用存储过程 所以对于存储过程不必深究做到会写能看懂即可 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-GdHDI4jj-1680502271512)(./assets/%E6%90%9C%E7%8B%97%E6%88%AA%E5%9B%BE20230329084802.png)]
5.1.1 什么是存储过程
Stored Procedure
是一组为了完成特定功能的 SQL 语句集合经编译后保存在数据库中通过指定存储过程的名字并给出参数的值MySQL5.0 版本开始支持存储过程使数据库引擎更加灵活和强大
5.1.2 存储过程可以包含
可带参数也可返回结果可包含数据操纵语句、变量、逻辑控制语句等
5.1.3 存储过程的优缺点
优点
减少网络流量提升执行速度减少数据库连接次数安全性高复用性高
缺点
可移植性差 SQL 最大的缺点还是 SQL 语言本身的局限性 SQL 本身是一种结构化查询语言我们不应该用存储过程处理复杂的业务逻辑让 SQL 回归它结构化查询语言的功用。复杂的业务逻辑还是交给代码去处理吧 5.1.4 创建存储过程
CREATE[DEFINER { user | CURRENT_USER }] # 定义DEFINER默认为当前用户
PROCEDURE 存储过程名[SQL SECURITY { DEFINER | INVOKER } | …]# 指定DEFINER或INVOKER权限
BEGIN…
END特性说明LANGUAGE SQL表示存储过程语言默认SQL{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}表示存储过程要做的工作类别默认值为CONTAINS SQLSQL SECURITY { DEFINER | INVOKER }指定存储过程的执行权限默认值是DEFINERDEFINDER使用创建者的权限INVOKER用执行者的权限COMMENT ‘string’存储过程的注释信息如果省略 SQL SECURITY 特性则使用 DEFINER 属性指定调用者且调用者必须具有 EXECUTE 权限必须在 mysql.user 表中如果将 SQL SECURITY 特性指定为 INVOKER则 DEFINER 属性无效 5.1.5 定义存储过程的参数
IN指输入参数
该参数的值必须在调用存储过程时指定存储过程中可以使用该参数但它不能被返回
OUT指输出参数
该参数可以在存储过程中发生改变并可以返回
INOUT指输入输出参数
该参数的值在调用存储过程时指定在存储过程中可以被改变和返回 如果需要定义多个参数需要使用,进行分隔 5.1.6 调用存储过程
CALL 存储过程名([参数1,参数2, …]);
# 根据存储过程的定义包含相应的参数存储过程调用类似于Java中的方法调用 5.1.7 查看存储过程状态
SHOW PROCEDURE STATUS[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WlNvBJq2-1680502271515)(./assets/image-20230403133422488.png)]
5.1.8 查看存储创建代码
SHOW CREATE PROCEDURE 存储过程名[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HaxrdHeW-1680502271515)(./assets/image-20230403133612810.png)]
5.1.9 修改存储过程
ALTER PROCEDURE 存储过程名[特性………][外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-IZHB4851-1680502271515)(./assets/image-20230403133809483.png)]
5.1.10 删除存储过程
DROP PROCEDURE 存储过程名[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yLgE2dkr-1680502271516)(./assets/image-20230403133956314.png)]
5.1.11 存储过程中的变量
与Java语言类似定义存储过程时可以使用变量
DECLARE 变量名[,变量名...] 数据类型 [DEFAULT 值];给变量进行赋值
SET 变量名 表达式值[,变量名表达式...] ;定义存储过程时所有局部变量的声明一定要放在存储过程体的开始否则会提示语法错误 系统变量
指 MySQL 全局变量以开头形式为变量名
用户自定义变量
局部变量 一般用于SQL的语句块中如存储过程中的BEGIN和END语句块作用域仅限于定义该变量的语句块内生命周期也仅限于该存储过程的调用期间在存储过程执行到END时局部变量就会被释放 会话变量 是服务器为每个客户端连接维护的变量与MySQL客户端是绑定的也称作用户变量可以暂存值并传递给同一连接中其他SQL语句进行使用当MySQL客户端连接退出时用户变量就会被释放用户变量创建时一般以开头形式为变量名
演示案例
根据病人名称和检查项目ID输出最后一次检查时间
CREATE DEFINERrootlocalhost PROCEDURE proc_exam_GetLastExamDateByPatientNameAndDepID(IN patient_name VARCHAR(50), IN dep_id INT,OUT last_exam_date DATETIME)
BEGIN#Routine body goes here...DECLARE patient_id INT; #声明局部变量SELECT patientID INTO patient_id FROM patient WHERE patientName patient_name;SELECT patient_id; #输出病人的IDSELECT MAX(examDate) INTO last_exam_date FROM prescription WHERE patientID patient_id AND depID dep_id;
END调用存储过程
SET patient_name夏颖;
SET dep_id 1;
CALL proc_exam_GetLastExamDateByPatientNameAndDepID(patient_name, dep_id, last);SELECT last;5.1.12 存储过程控制语句
与Java语言的流程控制语句类似MySQL提供的控制语句
条件语句 IF-ELSE IF-ELSE 条件语句CASE 条件语句 循环语句 WHILE 循环LOOP 循环REPEAT循环 迭代语句 5.1.13 IF-ELSE 条件语句
IF 条件 THEN 语句列表[ELSEIF 条件 THEN 语句列表][ELSE 语句列表]
END IF;根据病人的家庭收入返还补贴不同比例的医疗费用 家庭年收入在5000元以下的返还当年总医疗费用的20%家庭年收入在10000以下的返还当年总医疗费用的15%家庭年收入在30000以下的返还总医疗费用的5%30000元以上或未登记的不享受医疗费用返还输入病人编号和年份计算该患者当年的应返还的医疗费用 CREATE DEFINERrootlocalhost PROCEDURE Proc_CH05_4(IN patient_ID INT ,IN in_year VARCHAR(50),OUT ou_subsidy FLOAT )
BEGINDECLARE tital_Cost FLOAT;DECLARE totial_income FLOAT;SELECT incomeMoney INTO totial_income FROM income WHERE patientID patient_ID;SELECT sum(checkItemCost) INTO tital_Cost FROM prescription
INNER JOIN checkitem ON prescription.checkItemIDcheckitem.checkItemID
WHERE patientIDpatient_ID AND examDate CONCAT(in_year,-01-01)
AND examDate CONCAT(in_year,-12-31);IF totial_income0 AND totial_income5000 THENSET ou_subsidy tital_Cost*0.2;
ELSEIF totial_income5000 AND totial_income10000 THENSET ou_subsidy tital_Cost*0.15;
ELSEIF totial_income10000 AND totial_income30000 THENSET ou_subsidy tital_Cost*0.05;
ELSESET ou_subsidy 0;
END IF;
END5.1.14 CASE 条件语句
CASEWHEN 条件 THEN 语句列表[WHEN 条件 THEN 语句列表][ELSE 语句列表]
END CASE;CASE 列名WHEN 条件值 THEN 语句列表[WHEN 条件值 THEN 语句列表][ELSE 语句列表]
END CASE;使用CASE语句实现返还补贴不同比例的医疗费用 CREATE DEFINERrootlocalhost PROCEDURE Proc_CH05_5(IN patient_ID INT ,IN in_year VARCHAR(50),OUT ou_subsidy FLOAT )
BEGINDECLARE tital_Cost FLOAT;DECLARE totial_income FLOAT;SELECT incomeMoney INTO totial_income FROM income WHERE patientID patient_ID;SELECT sum(checkItemCost) INTO tital_Cost FROM prescription
INNER JOIN checkitem ON prescription.checkItemIDcheckitem.checkItemID
WHERE patientIDpatient_ID AND examDate CONCAT(in_year,-01-01)
AND examDate CONCAT(in_year,-12-31);CASE WHEN totial_income0 AND totial_income5000 THENSET ou_subsidy tital_Cost*0.2;WHEN totial_income5000 AND totial_income10000 THENSET ou_subsidy tital_Cost*0.15;WHEN totial_income10000 AND totial_income30000 THENSET ou_subsidy tital_Cost*0.05;WHEN totial_income30000 AND totial_income0 THENSET ou_subsidy 0;
END CASE;END在某种情况下例如做等值判断使用第二种写法更加简洁但是因为CASE后面有列名功能上会有一些限制 5.1.15 WHILE 循环语句
[label:] WHILE 条件 DO语句列表
END WHILE [label]首先判断条件是否成立。如果成立则执行循环体label为标号用于区分不同的循环可省略用在begin、repeat、while 或者loop 语句前 假设有测试表test有Id字段、Val字段 根据输入的行数要求批量插入测试数据 DECLARE rand_val FLOAT;
WHILE rows 0 DOSELECT RAND() INTO rand_val;INSERT INTO test VALUES(NULL, rand_val);SET rows rows - 1;
END WHILE;5.1.16 LOOP 循环语句
[label:] LOOP语句列表
END LOOP [label] ;不需判断初始条件直接执行循环体 LEAVE label ;遇到 LEAVE 语句退出循环 批量插3个新的检查项目检查项目名称为胃镜、肠镜和支气管纤维镜各项检查的价格均为70元 CREATE DEFINERrootlocalhost PROCEDURE proc_checkitem_insert( IN checkitems VARCHAR(100))
BEGINDECLARE comma_pos INT;DECLARE current_checkitem VARCHAR(20);loop_label: LOOPSET comma_pos LOCATE(,, checkitems);SET current_checkitem SUBSTR(checkitems, 1, comma_pos-1);IF current_checkitem THENSET checkitems SUBSTR(checkitems, comma_pos1);ELSESET current_checkitem checkitems;END IF;INSERT INTO checkitem(checkItemName,checkItemCost) VALUES(current_checkitem,70);IF comma_pos0 OR current_checkitem THENLEAVE loop_label;# 退出loop_label标识的程序块END IF;END LOOP loop_label;# LOOP循环结束
END5.1.17 REPEAT 循环语句
[label:] REPEAT语句列表
UNTIL 条件
END REPEAT [label]先执行循环操作再判断循环条件与 LOOP 循环语句相比较相同点不需要初始条件直接进入循环体不同点REPEAT 语句可以设置退出条件 使用REPEAT循环语句编码实现根据输入的行数要求向测试表test中批量插入测试数据 CREATE DEFINERrootlocalhost PROCEDURE Proc_CH05_7(IN rows INT )
BEGINDECLARE rand FLOAT;REPEATSELECT RAND() INTO rand;INSERT INTO test (val)VALUES(rand);SET rows rows -1 ;UNTIL rows 0 END REPEAT;END5.1.18 迭代语句
ITERATE label;从当前代码处返回到程序块开始位置重新执行ITERATE关键字可以嵌入到LOOP、WHILE和REPEAT程序块中 输入需增加数据行数随机产生的测试数据必须大于0.5 CREATE DEFINERrootlocalhost PROCEDURE Proc_CH05_8(IN rows INT)
BEGIN DECLARE rand FLOAT; random_lbl : REPEATSELECT RAND() INTO rand; IF rand 0.5 THEN ITERATE random_lbl; END IF;INSERT INTO test (val) VALUES (rand);SET rowsrows-1; UNTIL rows0 END REPEAT;
END
