数据库技术与应用（达梦）课件 任务4.3 使用存储函数查询某个用户某个月份的电费

《数据库原理与应用》任务4.3使用存储函数查询某个用户某个月份的电费项目4：数据库编程主讲人：汪菊琴第14讲数据库模块化设计—函数任务4.1中已经实现了计算每户每月的用电量及电费的功能，本任务使用存储函数的方式进一步实现查询某个用户某个月份的电费。查询“1000000001”用户“2024-07”月份的电费素质目标能力目标知识目标1）了解DM数据库中存储模块的含义和优点。2）掌握DM数据库常用的内置函数和使用方法。3）掌握存储函数的创建、调用和管理。1）能够根据需求选择合适的内置函数。2）能够创建满足业务需求的存储函数并调用。1）培养模块化设计和代码复用的工程思维。存储模块是指由用户使用DMSQL程序语言创建的存储过程或存储函数。它们是预先编译并存储在数据库中的可重用SQL代码块，运行在服务器端。4.3.1存储模块执行效率高：在创建时编译，后续调用直接执行，减少SQL解析时间。调用时，客户端只需传递参数，避免传输大量SQL语句，可降低网络传输。提高代码可维护性：用户定义的存储模块在达梦数据库中集中存放，其业务逻辑集中管理，修改时只需调整存储模块，无需更改应用程序代码。增强数据安全性：可将用户与具体的内部数据操作进行隔离。管理员可限制用户仅能通过存储模块访问数据，避免直接操作表。

4.3.2DM数据库常用内置函数数值函数字符串函数日期时间函数空值判断函数类型转换函数数值函数函数名功能简要说明ABS(n)返回数值n的绝对值CEIL(n)返回大于或等于n的最小整数（向上取整）FLOOR(n)返回小于或等于n的最大整数（向下取整）ROUND(n[,d])将n四舍五入保留d位小数，d默认值为0MOD(m,n)返回m除以n的余数（取模运算）POWER(m,n)返回m的n次幂SQRT(n)返回n的平方根（n必须为非负数）SIGN(n)返回n的符号，正数返回1，负数返回-1，0返回0TRUNC(n[,d])截断n保留d位小数，不进行四舍五入，d默认值为0RAND([seed])返回0到1之间的随机数，seed为可选的种子值表4-1常用数值函数2.字符串函数函数名功能简要说明SUBSTRING(str,pos,len)从str的pos位置开始截取长度为len的子串（pos从1开始）LENGTH(str)返回字符串str的长度（按字符数计算）CONCAT(str1,str2[,...])拼接多个字符串为一个字符串LTRIM(str[,trim_str])去除字符串str左侧的指定字符（默认去除空格）RTRIM(str[,trim_str])去除字符串str右侧的指定字符（默认去除空格）TRIM([trim_strFROM]str)去除字符串str首尾的指定字符（默认去除空格）UPPER(str)将字符串str转换为大写LOWER(str)将字符串str转换为小写REPLACE(str,old,new)将str中所有old子串替换为new子串INSTR(str,substr[,pos[,occ]])返回substr在str中从pos位置开始第occ次出现的位置（未找到返回0）LPAD(str,len[,pad_str])用pad_str在str左侧填充，使总长度为len（默认用空格填充）RPAD(str,len[,pad_str])用pad_str在str右侧填充，使总长度为len（默认用空格填充）表4-2常用字符串函数3.日期函数函数名功能简要说明ADD_DAYS(date,n)返回日期加上n天后的新日期ADD_MONTHS(date,n)在输入日期上加上指定的几个月返回一个新日期SYSDATE()返回当前系统日期和时间SYSTIMESTAMP()返回当前系统日期、时间（精确到微秒）及时区DATEADD(datepart,n,date)对date增加n个指定时间单位（如年、月、日等）DATEDIFF(datepart,date1,date2)计算date2与date1之间的指定时间单位差值DAY(date)返回日期中的天数DAYNAME(date)返回日期的星期名称DAYOFMONTH(date)返回日期为所在月份中的第几天DAYOFWEEK(date)返回日期为所在星期中的第几天DAYOFYEAR(date)返回日期为所在年中的第几天DAYS_BETWEEN(date1,date2)返回两个日期之间的天数EXTRACT(时间单位FROMdate)返回日期的年份、月份等数值，时间单位可以是YEAR、MONTH、DAY等TO_CHAR(date[,fmt])将日期型数据按fmt格式转换为字符串TO_DATE(str[,fmt])将字符串按fmt格式转换为日期型表4-3常用日期函数4.空值判断函数函数名功能简要说明COALESCE(n1,n2,…nx)返回第一个非空的值IFNULL(n1,n2)当n1为非空时，返回n1；若n1为空，则返回n2ISNULL(n1,n2)当n1为非空时，返回n1；若n1为空，则返回n2NULLIF(n1,n2)如果n1=n2返回NULL，否则返回n1表4-4空值判断函数5.类型转换函数函数名功能简要说明CAST(exprAStype)将expr转换为指定的数据类型type（如数值转字符串、日期转字符串等）TO_CHAR(n[,fmt])将数值n按fmt格式转换为字符串TO_NUMBER(str[,fmt])将字符串str按fmt格式转换为数值型TO_DATE(str[,fmt])将字符串按fmt格式转换为日期型（同日期函数中的TO_DATE）TO_TIMESTAMP(str[,fmt])将字符串按fmt格式转换为带时区的时间戳类型CONVERT(expr,type)与CAST类似，将expr转换为指定类型（兼容特定数据库语法）表4-5类型转换函数【例4-20】以年月日格式输出当前日期。--提取当前日期的年、月、日并格式化4.3.2DM数据库常用内置函数SELECTTO_CHAR(SYSDATE,'YYYY-MM-DD')AS年月日格式FROMDUAL;1.创建存储函数语法格式如下：CREATEFUNCTION[<模式名>.]<存储函数名> [(<参数名><参数模式><参数类型>[<默认值表达式>{,…})]RETURN<返回数据类型><AS|IS> [<声明部分>]BEGIN <执行部分>END[存储函数名];4.3.3创建和调用存储函数1.创建存储函数格式说明：模式名：指明被创建的存储函数所属模式的名字，默认为当前模式名。存储函数名：指明被创建的存储函数的名字。参数模式：可设置为IN、OUT或INOUT（OUTIN），默认为IN类型。IN表示输入参数，用来将数据传送给函数；OUT表示输出参数，用来从模块返回数据到进行调用的模块；INOUT表示既作为输入参数，也作为输出参数。返回数据类型：指明存储函数返回值的数据类型。AS|IS：达梦数据库为了兼容不同数据库产品的语法而提供的两种形式，在使用上没有实质性区别。函数体：从声明部分到函数定义结束处为存储函数的函数体，也即语句块。4.3.3创建和调用存储函数2.调用存储函数语法格式如下： [<模式名>.]<存储函数名>[(<参数>)]

存储函数的调用可以出现CALL语句后，也可以出现在SELECT语句中。两者的区别是：跟在CALL语句后调用时，不会返回存储函数的返回值，仅执行其中的操作。通过SELECT语句调用存储函数时，不仅执行其中的操作，也会返回存储函数的返回值。但SELECT语句调用的存储函数不支持含有OUT、INOUT模式参数。4.3.3创建和调用存储函数【例4-21】创建存储函数F_CONSUME_AVG，计算每日平均用电量。再调用该函数输出每日平均用电量。（1）创建计算每日平均用电量的存储函数F_CONSUME_AVG。4.3.3创建和调用存储函数CREATEFUNCTIONEPDMS.F_CONSUME_AVG()RETURNDECIMAL(20,2)ASv_avgDECIMAL(20,2);BEGINSELECTAVG(CONSUME_NUM)INTOv_avgFROMEPDMS.T_CONSUME;RETURNv_avg;END;【例4-21】创建存储函数F_CONSUME_AVG，计算每日平均用电量。再调用该函数输出每日平均用电量。（2）调用F_CONSUME_AVG函数输出每日平均用电量。

调用无参函数时，也可省略函数名后的括号。该SELECT语句执行后，显示每日平均用电量为150.71。也可采用CALL语句调用，能成功运行，但不会输出每日平均用电量。4.3.3创建和调用存储函数SELECTEPDMS.F_CONSUME_AVG()AS每日平均用电量;CALLEPDMS.F_CONSUME_AVG();【例4-22】创建存储函数F_CONSUME_AVG_USER，根据参数用户编号计算该用户每日平均用电量。再调用该函数输出编号“1000000001”用户的每日平均用电量。（1）创建计算某个用户每日平均用电量的存储函数，该函数带有用户编号的参数。（2）调用F_CONSUME_AVG_USER函数输出“1000000001”用户的每日平均用电量。4.3.3创建和调用存储函数CREATEFUNCTIONEPDMS.F_CONSUME_AVG_USER(u_idVARCHAR(20))RETURNDECIMAL(20,2)ASv_avgDECIMAL(20,2);BEGINSELECTAVG(CONSUME_NUM)INTOv_avgFROMEPDMS.T_CONSUMEWHEREUSER_ID=u_id;RETURNv_avg;END;SELECTEPDMS.F_CONSUME_AVG_USER('1000000001')AS每日平均用电量;1.编译存储函数语法格式如下：ALTERFUNCTION[<模式名>.]<存储函数名>COMPILE[CASCADE][DEBUG];说明：当指定CASCADE后，将级联重新编译所有直接或间接引用该存储模块的对象，建议谨慎使用。语法中的DEBUG没有实际作用，仅语法支持。该语句只是对视图的定义重新进行分析和编译，如果编译出错，可判断视图依赖的基表是否已被删除或修改了表定义。【例4-23】重新编译存储函数F_CONSUME_AVG_USER。4.3.4管理存储函数ALTERFUNCTIONEPDMS.F_CONSUME_AVG_USERCOMPILE;2.删除存储函数语法格式如下：DROPFUNCTION[IFEXISTS][<模式名>.]<存储函数名>;【例4-24】删除存储函数F_CONSUME_AVG_USER。4.3.4管理存储函数DROPFUNCTIONEPDMS.F_CONSUME_AVG_USER;[任务实施]

本任务使用存储函数的方式进一步实现查询某个用户某个月份的电费。1.技术分析（1） 创建带参数的存储函数：参数为用户编号和查询月份。（2） 调用带参数的存储函数。[任务实施]2.实现步骤（1） 创建电费计算存储函数F_GET_CONSUMEMONEY，函数带有用户编号和查询年月两个参数，返回类型为DECIMAL(10,2)。（2） 编写存储函数函数体的声明部分，进行变量的定义。（3） 编写存储函数函数体的执行部分，从电力计费表中查询阶梯电价的每月的用电额度和电费单价，并赋给相应的变量。代码同任务4.1【任务实施】中实现步骤的第（