oracle 和 mysql 的自定义函数的区别

本文主要探讨了MySQL和Oracle在创建自定义函数时的主要区别,包括整体格式、基础操作(如赋值、声明、循环、判断)以及内建函数的差异。例如,MySQL使用`CREATE FUNCTION`,而Oracle使用`CREATE [OR REPLACE] FUNCTION`。在基础操作中,赋值操作MySQL使用`set`,Oracle使用`:=`。在内建函数方面,讨论了如何在两个数据库系统中实现类似的功能,如`IFNULL()`与`NVL()`,`GROUP_CONCAT()`与Oracle的替代方法等。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

因为项目需要,我主要是把 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;
<think>嗯,用户想在Oracle数据库里用自定义函数实现rank功能。首先,我需要理解用户的需求。用户可能已经知道Oracle自带的RANK分析函数,但可能出于某些原因需要自己实现,比如定制化需求或者学习目的。不过,根据提供的引用资料,Oracle本身就有RANK()函数,所以用户可能想了解如何在不使用内置函数的情况下自己创建类似的功能。接下来,我需要考虑如何在Oracle中创建自定义函数来实现RANK。根据引用[3],用户提到在MySQL中通过存储过程实现rank功能,可能在Oracle中也可以使用类似的方法,比如使用PL/SQL编写函数或存储过程。但要注意OracleMySQL的语法差异。用户提供的引用[2][4]详细说明了RANK函数的使用方式dense_rank的区别,这可能对实现逻辑有帮助。比如,RANK函数在遇到相同值时会跳过后续排名,而dense_rank不会。因此,自定义函数需要处理相同分数的情况,并正确计算排名。可能的实现步骤包括: 1.创建一个函数,接收参数比如课程名称,然后对该课程的学生成绩进行排序。 2.使用游标遍历学生成绩,并根据分数高低分配排名,处理相同分数的情况。 3.需要考虑分区(比如不同课程分开排名),这可能需要动态SQL或者循环处理每个课程。需要注意的是,Oracle自定义函数通常用于计算并返回单个值,而排名可能需要返回结果集,这时候可能需要使用存储过程或者流水线函数(PIPELINEDFUNCTION)来返回多行数据。另外,引用[3]中的MySQL存储过程使用了循环会话变量来生成排名,可能在Oracle中可以用PL/SQL的游标变量来实现类似逻辑。例如,在函数内部维护一个计数器,当分数变化时更新排名,相同分数则保持相同排名,并跳过后续位置。 还需要考虑性能问题,尤其是处理大量数据时,自定义函数的效率可能不如内置的RANK函数,但用户可能有特殊需求,比如定制排名规则,这时候必须自己实现。 可能的代码结构如下: -创建一个包,包含游标变量来跟踪当前排名。-使用动态SQL根据输入参数(如课程)获取数据。-遍历结果集,计算排名,并返回结果。在测试阶段,需要验证不同情况下的排名是否正确,包括分数相同、不同课程的分区是否正确等。最后,需要向用户说明自定义实现的优缺点,比如复杂度高、性能可能较低,建议在可能的情况下优先使用内置的RANK函数。但如果用户确实需要自定义逻辑,比如不同的排名规则,那么提供示例代码并解释实现步骤是必要的。</think>### 在Oracle中使用自定义函数实现RANK功能 #### 一、实现思路 Oracle内置的`RANK()`函数属于分析函数,但通过PL/SQL可以模拟其核心逻辑: 1. **按指定字段分组排序**(如按课程分组,按分数降序) 2. **处理相同值的排名跳号**(如两个并列第2名后直接第4名) 3. **动态生成排名序号** #### 二、具体实现步骤 ##### 1. 创建基础表结构 假设存在学生成绩表`student`: ```sql CREATE TABLE student ( sno VARCHAR2(10), sname VARCHAR2(50), course VARCHAR2(50), score NUMBER ); ``` ##### 2. 创建自定义函数 通过PL/SQL流水线函数实现动态排名: ```sql CREATE OR REPLACE PACKAGE pkg_rank AS TYPE t_rank_rec IS RECORD ( sno VARCHAR2(10), sname VARCHAR2(50), course VARCHAR2(50), score NUMBER, rank NUMBER ); TYPE t_rank_tab IS TABLE OF t_rank_rec; FUNCTION custom_rank(p_course VARCHAR2) RETURN t_rank_tab PIPELINED; END pkg_rank; / CREATE OR REPLACE PACKAGE BODY pkg_rank AS FUNCTION custom_rank(p_course VARCHAR2) RETURN t_rank_tab PIPELINED IS v_prev_score NUMBER := -1; v_rank NUMBER := 0; v_counter NUMBER := 1; BEGIN FOR rec IN ( SELECT sno, sname, course, score FROM student WHERE course = p_course ORDER BY score DESC ) LOOP IF rec.score != v_prev_score THEN v_rank := v_counter; v_prev_score := rec.score; END IF; PIPE ROW(t_rank_rec(rec.sno, rec.sname, rec.course, rec.score, v_rank)); v_counter := v_counter + 1; END LOOP; RETURN; END; END pkg_rank; / ``` ##### 3. 调用自定义函数 ```sql SELECT * FROM TABLE(pkg_rank.custom_rank('Oracle')); ``` #### 三、原声RANK()函数的对比 | 特性 | 自定义函数 | 原生RANK()[^2] | |---------------------|------------------------|---------------------| | 语法复杂度 | 需编写PL/SQL代码 | 单行SQL直接调用 | | 性能 | 逐行处理效率较低 | 数据库引擎优化 | | 跳号逻辑 | 手动实现 | 自动处理相同值跳号 | | 分区支持 | 需修改函数逻辑 | 内置PARTITION BY子句| #### 四、应用场景建议 1. **特殊排名规则**:当需要非标准排名逻辑(如自定义权重)时 2. **教学研究**:理解分析函数的底层实现原理 3. **兼容旧版本**:Oracle 8i等不支持分析函数的版本 ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值