最近面试，面试官提出了一个问题，编写一个oracle函数来实现对应表内的映射转换，例如‘1’对应‘哈尔滨’，‘2’对应‘北京’这样子，对应关系按照类型不同都可以加以区分。本身自己存储过程函数的编写就很少用到，所以有点为难，之后回家想了想各种方案，最终认为有一种方案可以实现，但是后续还可以进行改进。
两张对应关系表如下图:
这是转换样例：
将product_total字段数字转换成对应关系的类型。
实现方案1：
CREATE OR REPLACE FUNCTION CONT_NAME(NAME_CL VARCHAR2, CLA_TYPE VARCHAR2) RETURN VARCHAR2 AS V_TIMES NUMBER; V_RESULT VARCHAR2(20); X NUMBER; RESULT VARCHAR2(40); NUM NUMBER; STR char(1); BEGIN /*计算出这个字段存在几个对应类型*/ SELECT ROUND(LENGTH(NAME_CL) / 2) INTO V_TIMES FROM DUAL; X := 0; RESULT:=''; /*this is city_class 判断出这个转换类型是哪一种即（对应的哪一张表）*/ IF CLA_TYPE='CITY_CLASS' THEN /*内部进行循环 对每一个数字进行转换，转换后拼接字符串*/ WHILE X < V_TIMES LOOP X := X + 1; NUM:=X+X-1; SELECT SUBSTR(NAME_CL,NUM,1) INTO STR FROM DUAL; SELECT TYPE_NAME INTO V_RESULT FROM CITY_CLASS T WHERE T.TYPE_NUM = STR; RESULT:=RESULT||'|'||V_RESULT; END LOOP; /*this is coun_class*/ ELSIF CLA_TYPE='COUN_CLASS' THEN WHILE X < V_TIMES LOOP X := X + 1; NUM:=X+X-1; SELECT SUBSTR(NAME_CL,NUM,1) INTO STR FROM DUAL; SELECT TYPE_NAME INTO V_RESULT FROM COUN_CLASS T WHERE T.TYPE_NUM = STR; RESULT:=RESULT||'|'||V_RESULT; END LOOP; END IF; /*去除最左边的‘|’*/ select ltrim(result,'|') into result from dual; RETURN RESULT; END CONT_NAME;后续还要进行加入版本2进行改进。
怎么样看一下感觉上逻辑是不是有点多，而且效率上应该不是很高，因为当每次使用一次这个函数，内部都会嵌套很多次查询，所以我们后续对齐进行改进一下，针对不同的对应关系，我们使用不同的函数来进行转换（省去转换时中间进行的判断，但是代价是存在多少对应关系，就应该写多少个函数，优点是提升了函数执行的效率）。那么第二个改变是我们不在动态支持修改对应关系后函数就会支持修改，在这里我们将函数内对应关系查询替换为DCODE函数，目的也是提升函数执行的效率。
将CITY_CLASS改进后如下：
CREATE OR REPLACE FUNCTION CITY_CONT_NAME(NAME_CL VARCHAR2) RETURN VARCHAR2 AS V_TIMES NUMBER; X NUMBER; RESULT VARCHAR2(40); NUM NUMBER; STR CHAR(1); V_RESULT VARCHAR2(20); BEGIN SELECT ROUND(LENGTH(NAME_CL) / 2) INTO V_TIMES FROM DUAL; X := 0; RESULT:=''; WHILE X < V_TIMES LOOP X := X + 1; NUM:=X+X-1; SELECT SUBSTR(NAME_CL,NUM,1) INTO STR FROM DUAL; SELECT DECODE(STR,'1','haerbin','2','beijing','3','shanghai') INTO V_RESULT FROM DUAL; RESULT:=RESULT||','||V_RESULT; END LOOP; SELECT LTRIM(RESULT,',') INTO RESULT FROM DUAL; RETURN(RESULT); END CITY_CONT_NAME;完成了。
