【头歌系统数据库实验】实验13 数据库编程-1

目录第1关：定义一个名为PROC_COUNT的无参数存储过程第2关：定义一个名为PROC_JNAME的有参数存储过程第3关：定义一个名为PROC_JINFO的有参数存储过程第4关：定义一个名为PROC_AVGGRADE的有参数存储过程第5关：定义一个名为PROC_SINFO的有参数存储过程第6关：定义一个名为PROC_JSEARCH的有参数存储过程第7关：定义一个名为PROC_SUPDATE的有参数存储过程第8关：定义一个名为PROC_AVGWEIGHT的有参数存储过程第9关：定义一个名为PROC_JGRADE的有参数存储过程如果对你有帮助的话，不妨点赞收藏评论一下吧，爱你么么哒😘❤️❤️❤️第1关：定义一个名为PROC_COUNT的无参数存储过程任务描述定义一个名为PROC_COUNT的无参数存储过程，查询工程名称中含有“厂”字的工程数量，并调用该存储过程。相关知识1、工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。 J表如下图：现已构建J表，结构信息如下：2、存储过程定义 定义一个存储过程语句的语法格式如下：CREATE [OR REPLACE ] PROCEDURE 模式名.存储过程名 [WITH ENCRYPTION][(参数名 参数模式 参数数据类型 [默认值表达式]{,参数名 参数模式 参数数据类型 [默认值表达式] })]AS | IS[说明语句端段]BEGIN执行语句段[Exception异常处理语句段]END;其中： （1）模式名.存储过程名：指明被创建的存储过程的名称。 （2）参数名：指明存储过程参数的名称。 （3） WITH ENCRYPTION：为可选项，如果指定 WITH ENCRYPTION 选项，则对 BEGIN 到 END 之间的语句块进行加密，防止非法用户查看其具体内容，加密后的存储过程或存储函数的定义可在 SYS.SYSTEXTS 系统表中查询。 （4）参数模式：指明存储过程参数的输入/输出方式。参数模式可设置为 IN、OUT 或 IN OUT（OUT IN），默认为 IN 类，IN 表示向存储过程传递参数，OUT 表示从存储过程返回参数。而 IN OUT 表示传递参数和返回参数。 （5）参数数据类型：指明存储过程参数的数据类型。 （6）说明语句端段：由变量、游标和子程序等对象的申明构成。 （7）执行语句段：由 SQL 语句和过程控制语句构成的执行代码。 （8）异常处理语句段：各种异常的处理程序，存储过程执行异常时调用，可默认。注意：使用该语句的用户必须是 DBA 或该存储过程的拥有者且具有 CREATE PROCEDURE 数据库权限的用户；参数的数据类型只能指定变量类型，不能指定长度。相关操作：① DECLARE定义变量DECLARE用于定义变量，在存储过程和函数中通过declare定义变量在BEGIN...END中，且在语句之前,定义变量语法为：DECLARE 变量名 变量类型 [DEFAULT 初始化值]；例如：DECLARE a, b INT DEFAULT 5;② SET定义用户变量SET语句可用于向系统变量或用户变量赋值，用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效，针对用户变量的定义如下：SET @var_name = expr [, @var_name = expr] ...例如：SET @name = 'abc', @weight = 20;③ 存储过程调用：CALL命令过程体调用CALL sp_name[(传参)];④ 查看存储过程：SHOW PROCEDURE STATUS命令SHOW PROCEDURE STATUS where db='数据库名'; 命令查看对应数据库中存在哪些存储过程⑤ 存储过程修改：ALTER PROCEDURE命令MySQL 中修改存储过程的语法格式如下： ALTER PROCEDURE 存储过程名 [ 特征 ... ] 注意：ALTER PROCEDURE 语句用于修改存储过程的某些特征。如果要修改存储过程的内容，可以先删除原存储过程，再以相同的命名创建新的存储过程；如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。⑥ 存储过程删除：DROP PROCEDURE命令存储过程被创建后，就会一直保存在数据库服务器上，直至被删除。当 MySQL 数据库中存在废弃的存储过程时，我们需要将它从数据库中删除。 MySQL 中使用 DROP PROCEDURE 语句来删除数据库中已经存在的存储过程。语法格式如下： DROP PROCEDURE [ IF EXISTS ] 过程名 语法说明如下： • 过程名：指定要删除的存储过程的名称。 • IF EXISTS：指定这个关键字，用于防止因删除不存在的存储过程而引发的错误。 注意：存储过程名称后面没有参数列表，也没有括号，在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。⑦ OUT参数输出：SELECT @变量名例：存储过程create procedure out_param(out p_out int)；调用后输出OUT参数方法如下： CALL out_param(@pp_out); SELECT @pp_out; 注意：pp_out可与 p_out同名⑧ 修改命令结束符：DELIMITER命令⑨ 使用SELECT …INTO语句为变量赋值在MySQL存储过程中，可以使用SELECT …INTO语句对变量进行赋值，该语句在数据库中进行查询，并将得到的结果赋值给变量。SELECT …INTO语句的语法格式如下：SELECT col_name[,...] INTO var_name[,...] table_exprcol_name：要从数据库中查询的列字段名； var_name：变量名，列字段名按照在列清单和变量清单中的位置对应，将查询得到的值赋给对应位置的变量； table_expr：SELECT语句其余部分，包括可选的FROM子句和WHERE子句。 需要注意的是，在使用SELECT …INTO语句时，变量名不能和数据表中的字段名不能相同，否则会出错。 例如：⑩ 将变量值返回给调用者在存储过程中定义的变量，经过一系列的处理之后，结果值可能需要返回给存储过程调用者。那么如何返回呢？方便的做法是使用SELECT语句将变量作为结果集返回，例如：创建存储过程样例：创建一个存储过程，查询某用户ID在T表中的用户名开始你的任务吧，祝你成功！USE mydata; #请在此处添加实现代码 ########## Begin ########## DELIMITER $ CREATE PROCEDURE PROC_COUNT() BEGIN SELECT COUNT(*) FROM J WHERE JNAME LIKE '%厂'; END ;$ DELIMITER ; CALL PROC_COUNT(); ########## End ##########第2关：定义一个名为PROC_JNAME的有参数存储过程任务描述定义一个名为PROC_JNAME的有参数存储过程，查询输入任意城市的工程项目名称。相关知识1、工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。 J表如下图：现已构建J表，结构信息如下：2、存储过程定义 定义一个存储过程语句的语法格式如下：CREATE [OR REPLACE ] PROCEDURE 模式名.存储过程名 [WITH ENCRYPTION][(参数名 参数模式 参数数据类型 [默认值表达式]{,参数名 参数模式 参数数据类型 [默认值表达式] })]AS | IS[说明语句端段]BEGIN执行语句段[Exception异常处理语句段]END;其中： （1）模式名.存储过程名：指明被创建的存储过程的名称。 （2）参数名：指明存储过程参数的名称。 （3） WITH ENCRYPTION：为可选项，如果指定 WITH ENCRYPTION 选项，则对 BEGIN 到 END 之间的语句块进行加密，防止非法用户查看其具体内容，加密后的存储过程或存储函数的定义可在 SYS.SYSTEXTS 系统表中查询。 （4）参数模式：指明存储过程参数的输入/输出方式。参数模式可设置为 IN、OUT 或 IN OUT（OUT IN），默认为 IN 类，IN 表示向存储过程传递参数，OUT 表示从存储过程返回参数。而 IN OUT 表示传递参数和返回参数。 （5）参数数据类型：指明存储过程参数的数据类型。 （6）说明语句端段：由变量、游标和子程序等对象的申明构成。 （7）执行语句段：由 SQL 语句和过程控制语句构成的执行代码。 （8）异常处理语句段：各种异常的处理程序，存储过程执行异常时调用，可默认。注意：使用该语句的用户必须是 DBA 或该存储过程的拥有者且具有 CREATE PROCEDURE 数据库权限的用户；参数的数据类型只能指定变量类型，不能指定长度。相关操作：① DECLARE定义变量DECLARE用于定义变量，在存储过程和函数中通过declare定义变量在BEGIN...END中，且在语句之前,定义变量语法为：DECLARE 变量名 变量类型 [DEFAULT 初始化值]；例如：DECLARE a, b INT DEFAULT 5;② SET定义用户变量SET语句可用于向系统变量或用户变量赋值，用户变量跟mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效，针对用户变量的定义如下：SET @var_name = expr [, @var_name = expr] ...例如：SET @name = 'abc', @weight = 20;③ 存储过程调用：CALL命令过程体调用CALL sp_name[(传参)];④ 查看存储过程：SHOW PROCEDURE STATUS命令SHOW PROCEDURE STATUS where db='数据库名'; 命令查看对应数据库中存在哪些存储过程⑤ 存储过程修改：ALTER PROCEDURE命令MySQL 中修改存储过程的语法格式如下： ALTER PROCEDURE 存储过程名 [ 特征 ... ] 注意：ALTER PROCEDURE 语句用于修改存储过程的某些特征。如果要修改存储过程的内容，可以先删除原存储过程，再以相同的命名创建新的存储过程；如果要修改存储过程的名称，可以先删除原存储过程，再以不同的命名创建新的存储过程。⑥ 存储过程删除：DROP PROCEDURE命令存储过程被创建后，就会一直保存在数据库服务器上，直至被删除。当 MySQL 数据库中存在废弃的存储过程时，我们需要将它从数据库中删除。 MySQL 中使用 DROP PROCEDURE 语句来删除数据库中已经存在的存储过程。语法格式如下： DROP PROCEDURE [ IF EXISTS ] 过程名 语法说明如下： • 过程名：指定要删除的存储过程的名称。 • IF EXISTS：指定这个关键字，用于防止因删除不存在的存储过程而引发的错误。 注意：存储过程名称后面没有参数列表，也没有括号，在删除之