任务4.3编写和调用自定义函数处理数据采集平台数据库中的数.pptxVIP

openGauss数据库技术及应用任务4.3编写和调用自定义函数处理数据采集平台数据库中的数据

任务描述为了简化复杂查询及提高查询性能，在“数据采集平台”的数据库系统中，需创建查询设备信息的自定义函数及对这些函数进行管理。本任务实现查询设备信息自定义函数的相关操作。首先自定义查询某设备名称、某公司所有设备名称和某公司所有设备编号、名称及某日工作效率的函数，然后调用执行查询某公司所有设备编号、名称及某日工作效率的函数，最后对这些函数进行修改、删除等相关操作。

编写和调用自定义函数处理数据采集平台数据库中的数据自定义函数创建自定义函数调用自定义函数修改自定义函数删除

openGauss数据库支持自定义函数的创建和使用。自定义函数是用户根据业务需求编写的一段特定功能的SQL代码，这段代码在数据库中可以被调用执行。自定义函数

（1）自定义函数创建CREATE[ORREPLACE]FUNCTIONfunction_name[({[argname][argmode]argtype[{DEFAULT|:=|=}expression]}[,...])]RETURNSrettype[DETERMINISTIC][...]{IS|AS}$$[DECLAREvariable_namedata_type[:=initial_value];]BEGINplsql_body[RETURNvalue;]END;$$LANGUAGEplpgsql;使用CREATEFUNCTION语句创建自定义函数，其语法格式如下：参数说明：·这里的ORREPLACE表示当存在同名的函数时，可以替换它。·function_name为函数名称。·argname是参数名称。·argmode为参数模式，取值为：IN（输入参数）、OUT（输出参数）、INOUT（输入输出参数），默认是IN。?·argtype为参数类型。·{DEFAULT|:=|=}可以给输入参数设置默认值，其中DEFAULT、:=、=作用是一样的。·expression是参数的默认表达式。·rettype是函数返回值的数据类型。·IS或AS，两者作用相同，但必须写其中一个，标志着函数体的开始。·plsql_body为函数内容。

实践：创建自定义函数查询某设备名称。（1）创建自定义函数语句：CREATEFUNCTIONget_name_by_equipmentnumber(equipment_numINVARCHAR,name_outOUTVARCHAR)AS$$BEGINSELECTnameINTOname_outFROMinstallinfosWHEREequipmentnumber=e?quipment_num;END;$$LANGUAGEplpgsql;

实践：创建自定义函数查询某公司所有设备名称。（1）创建自定义函数语句：CREATEFUNCTIONget_company_equipment_names(company_idINVARCHAR)RETURNSVARCHAR［］AS$$DECLAREnamesVARCHAR［］;BEGINSELECTARRAY_AGG()INTOnamesFROMcompanyinfosaINNERJOINinstallinfosbONa.selfid=panyidWHEREa.selfid=company_id;RETURNnames;END;$$LANGUAGEplpgsql;

实践：创建自定义函数查询某公司所有设备的每日工作效率。（1）创建自定义函数语句：CREATEFUNCTIONget_company_equipment_all(company_idVARCHAR,report_dateVARCHAR)RETURNSTABLE(companynameVARCHAR,equipmentnumberVARCHAR,equipmentnameVARCHAR,efficiencyNUMERIC)AS$$BEGINRETURNQUERYSELECTAScompanyname,b.equipmentnumber,ASequipmentname,c.worktime/NULLIF(c.worktime+c.standbytime,0)ASefficiencyFROMcompanyinfosaINNERJOINinstallin