因为项目需要,我主要是把 mysql 的自定义函数 迁移到 oracle上, 所以,这里重点实在oracle。
#而他俩的区别,主要在几个部分:整体格式; 基础操作 和 内建函数,还有其他需要注意的。
整体格式
mysql
create function functionName(param) returns datatType
begin
// more work to do
return 【result】;
end;
oracle
create [or replace ] function functionName([param]) return resultType
as|is
begin
// more work to do
return result;
end;
其实大体格式差不多的,网络也一堆,这里只是稍微说一下!
基础操作
赋值
- mysql : set ,比如 set a = ‘’;
- oracle: := , 比如 a:= ‘’;
- 他们共有的使用查询语句赋值,这中在游标和过程中也常用到
- 比如 select sysdate into time from dual;
声明
- mysql: 生命在函数体中,也就是在begin 之后,用DECLARE 关键字,比如: DECLARE sTemp VARCHAR(4000);
- oracle : 不用DECLARE,直接书写,并且在as或者是is之后,begin之前, 比如 : sTemp VARCHAR(4000);
- 这里既然提到as 和 is, 说下,在oracle的过程和函数中,as 和 is 是没有去别的。
循环
- mysql: while 条件 do … end while;
- oracle: while 条件 loop … end loop;
判断
都是: if 条件 then … end if;
内建函数
这里约定一下,先说mysql 的 函数, 然后 说明 oracle 用什么函数对应。
- ifnull() 对应 nvl() : 意思为如果为null显示什么,不为 null
显示什么,建议掌握oracle这类万能的decode()函数 - concat ,俩个数据库都有,但是 如果oracle多余2个参数,势必报错,可以用 || 连字符 解决,或者 concat函数嵌套,而mysql 就没有这样的限值。
- group_concat(字段), 函数 意思 ,用字段分组,并且用‘,’号分割开,oracle 中 对应的函数,在12c前: vm_concat(字段) 即可, 12c之后,改函数废弃,代替函数为 :
listagg(字段, char) within group( order by 字段) -- 改剧中2个字段为同一个字段,是一样的`,char 代表 分割符,是自己可定义的
- find_in_set(temp, src) 意义为temp 字符串 是否在 src 字符串中,src 字符串用‘,’分割,返回的是boolean类型数值,所以它本身即使一个判断, 在oracle 可以用instr(stc, temp) 代替,但因为instr函数 返回的 是temp 在src 中的下标,所以,我们需要用根据实际业务判断。
- insert(src, indexstart, leng, replacestr) , 作用 是 在src 字符串中, 从indexstart 位开始的 leng 位,用replacestr 字符串代替 。 这个在oracle 中可以完美代替的函数是没有的,我这里有个例子,大家可以参考一下:
# 要求 用 ‘’代替指定位数的字符串,我用的oracle 中的字符串截取
#原 需求 set c1 = INSERT(c1,1,instr(c1,split1),'');
SELECT substr(temp, instr(temp, split1) + length(split1)) into temp from dual;
- uuid() ,函数作用是生成uuid,oracle 代替位sys_guid(),Oracle官方说sys_guid()生成的uuid是全球唯一的,占用128位,所以耗费资源很多。
其他注意
- oracle 中, 形参 在 函数体内不能在进行赋值操作;
CREATE OR REPLACE
FUNCTION "getParentIds"(pid IN VARCHAR2)
RETURN VARCHAR2
IS
sTemp VARCHAR(4000);
BEGIN
sTemp:='$';
pid := sTemp; -- 这种事不正确的
RETURN sTemp;
END;
-
在oracle 中,空(’’)就是 null
测试方法,生命局部变量之后,赋值为空(’’), 然后,查看改局部变量显示的值,或者 用 if … then 判断均可。 -
oracle 的函数名称
函数名称可以分为是否用双引号括住,如果用双引号括住,说明oracle 数据严格按照字符串来匹配,区分大小写,如果不用双引号或者是单引号,就是让oracle 自己识别,oracle会同意处理为大写名称。 -
设计insert 关键字
在oracle 函数中,设计insert 关键字插入数据,最好用事务,结构:
PRAGMA AUTONOMOUS_TRANSACTION; ... commit;
例子:
CREATE OR REPLACE
FUNCTION "fn_split" (c1 VARCHAR, split1 VARCHAR)
RETURN VARCHAR
AS
PRAGMA AUTONOMOUS_TRANSACTION; #### 事务开始
uuid varchar(36);
temp VARCHAR2(36);
BEGIN
temp := c1;
select rawtohex(SYS_guid()) into uuid from dual;
while(instr(temp,split1)<>0) loop
insert into split_tab (id,col,dataTime) values (uuid,substr(temp,1,instr(temp,split1)-1),SYSDATE());
SELECT substr(temp, instr(temp, split1) + length(split1)) into temp from dual;
END loop;
insert INTO split_tab (id,col,dataTime) values (uuid,c1,SYSDATE());
commit; ### 事务提交
return uuid;
END;