系列文章目录
前言
上一篇文章我们介绍了存储过程的异常处理,以及异常的兜底策略。在本章节中,我们将介绍自定义函数。
一、自定义函数是什么?
1.定义
自定义函数是用户存储在数据库中的代码块,能够通过把参数传入进去,经过逻辑处理之后得到返回结果的程序;
2.系统函数
与自定义函数相对应的是系统函数,系统函数是数据库创建之后初始化到数据库中的函数,这些函数比较常用,比如to_char,to_date,substr等
二、自定义函数的使用
1.上代码
CREATE OR REPLACE FUNCTION "BI_GET_CUSTNOS"("V_CUST_NO" IN VARCHAR2(32767))
RETURN CUST_NO_TABLE
AUTHID DEFINER
IS
CUSTNO_ROW CUST_NO_ROW; -- 定义单行
CUSTNO_TABLE CUST_NO_TABLE := CUST_NO_TABLE(); -- 定义返回结果,并初始化
v_lev number;
BEGIN
select clt_level into v_lev from dm_nsclient_lev where CUST_NO=V_CUST_NO;
if v_lev =1 then
FOR CURROW IN (select CUST_NO,CUST_NO_lev2 CUST_NO_g from dm_nsclient_lev where CUST_NO_lev1 = V_CUST_NO ) -- 查询名字是参数的值的结果
LOOP
CUSTNO_ROW := CUST_NO_ROW(CURROW.CUST_NO, CURROW.CUST_NO_g); -- 获得一行
CUSTNO_TABLE.EXTEND; -- 表类型增加一行(EXTEND就是扩展的意思,相当于增加一行数据空间)
CUSTNO_TABLE(CUSTNO_TABLE.COUNT) := CUSTNO_ROW; -- 一行放进去
END LOOP;
elsif v_lev =2 then
FOR CURROW IN (select CUST_NO,CUST_NO_lev3 CUST_NO_g from dm_nsclient_lev where CUST_NO_lev2 = V_CUST_NO ) -- 查询名字是参数的值的结果
LOOP
CUSTNO_ROW := CUST_NO_ROW(CURROW.CUST_NO, CURROW.CUST_NO_g); -- 获得一行
CUSTNO_TABLE.EXTEND; -- 表类型增加一行(EXTEND就是扩展的意思,相当于增加一行数据空间)
CUSTNO_TABLE(CUSTNO_TABLE.COUNT) := CUSTNO_ROW; -- 一行放进去
END LOOP;
end if;
RETURN(CUSTNO_TABLE);
END;
2.代码详解
首先需要关注的要点是传入参数:V_CUST_NO 是传入参数的名称,类型是varchar2,传入参数可以写多个,通过逗号隔开,例如 (parm1 in para_type,parm2 in para_type...);
其次关注核心的代码逻辑处理:
代码中首先通过判断v_lev1的值走对应的逻辑
if v_lev =1 then
再使用游标获取查询的全部结果
FOR CURROW IN (select CUST_NO,CUST_NO_lev2 CUST_NO_g from dm_nsclient_lev where CUST_NO_lev1 = V_CUST_NO )
然后通过游标将每一行数据赋值到自定义行数据类型中
CUSTNO_ROW := CUST_NO_ROW(CURROW.CUST_NO, CURROW.CUST_NO_g); -- 获得一行
最后再将自定义行数据存储到自定义表中。
CUSTNO_TABLE.EXTEND; -- 表类型增加一行(EXTEND就是扩展的意思,相当于增加一行数据空间)
CUSTNO_TABLE(CUSTNO_TABLE.COUNT) := CUSTNO_ROW; -- 一行放进去
最后关注返回值,示例中的返回值是CUSTNO_TABLE,是用户自定义的数据类型,返回的结果是用户自定义的行数据;(也可以返回一般的数据类型,那么返回结果是数值,字符串等);
三、补充讲解
1.自定义行类型
CREATE "CUST_NO_ROW" AS OBJECT(cust_no varchar(32),cust_no_g varchar(32));
该类型是用户自定义类型,参数数量和参数类型都可以自定义,如果返回值是它,那么查询结果可以返回一行数据;
2.自定义表类型
CREATE TYPE "CUST_NO_TABLE" AS TABLE OF CUST_NO_ROW;
该类型是用户自定义类型,参数类型是表,参数内容的格式可以是自定义行类型,如果返回值是它,那么查询结果可以返回一组数据;
总结
本文主要介绍了达梦数据库中自定义函数的使用,在日常开发中可以将频繁使用到的sql逻辑封装成函数,再通过函数调用的方式获取返回结果,让sql代码看起来更简洁,高效。