Oracle函数语法指南

Oracle函数语法指南

概述

Oracle数据库提供了许多内置函数，用于执行各种操作和计算，这些函数可以简化SQL查询，提高代码的可读性和可维护性，本指南将介绍Oracle函数的基本语法和常用函数的用法。

函数语法

1、函数定义

在Oracle中，可以使用CREATE FUNCTION语句来创建自定义函数，函数定义的基本语法如下：

CREATE [OR REPLACE] FUNCTION function_name (parameter1 datatype, parameter2 datatype, ...)
RETURN return_datatype
IS
   局部变量声明
BEGIN
   函数体
END;

2、参数列表

函数参数列表中的每个参数都需要指定数据类型，参数可以是输入参数（IN）、输出参数（OUT）或输入/输出参数（IN OUT），默认情况下，所有参数都是输入参数。

CREATE FUNCTION add_numbers (num1 IN NUMBER, num2 IN NUMBER, result OUT NUMBER)
RETURN NUMBER IS
BEGIN
   result := num1 + num2;
   RETURN result;
END;

3、返回值类型

函数必须指定返回值的数据类型，返回值类型可以是任何有效的Oracle数据类型，包括标量类型、复合类型和对象类型。

CREATE FUNCTION get_employee_salary (emp_id IN NUMBER)
RETURN NUMBER IS
   salary NUMBER;
BEGIN
   SELECT salary INTO salary FROM employees WHERE employee_id = emp_id;
   RETURN salary;
END;

常用函数

1、字符串函数

CONCAT：连接两个或多个字符串。SELECT CONCAT('Hello', ' ', 'World') FROM dual;

INSTR：查找子字符串在主字符串中的位置。SELECT INSTR('Hello World', 'World') FROM dual;

SUBSTR：从主字符串中提取子字符串。SELECT SUBSTR('Hello World', 1, 5) FROM dual;

UPPER：将字符串转换为大写。SELECT UPPER('hello') FROM dual;

LOWER：将字符串转换为小写。SELECT LOWER('HELLO') FROM dual;

LTRIM：删除字符串左侧的空格。SELECT LTRIM(' Hello') FROM dual;

RTRIM：删除字符串右侧的空格。SELECT RTRIM('Hello ') FROM dual;

REVERSE：反转字符串。SELECT REVERSE('Hello') FROM dual;

LOCATE：查找子字符串在主字符串中的位置，支持正则表达式。SELECT LOCATE('o', 'Hello World') FROM dual;

REPLACE：替换主字符串中的子字符串。SELECT REPLACE('Hello World', 'World', 'Oracle') FROM dual;

TRANSLATE：根据翻译表替换主字符串中的字符。SELECT TRANSLATE('Hello World', 'HW', 'XY') FROM dual;

INITCAP：将字符串的首字母大写，其余字母小写。SELECT INITCAP('hello world') FROM dual;

LENGTH：返回字符串的长度。SELECT LENGTH('Hello World') FROM dual;

DBMS_UTILITY.LAST_NAME：获取用户名称的姓氏部分。SELECT DBMS_UTILITY.LAST_NAME FROM DUAL;

DBMS_UTILITY.REGEXP_SUBSTR：使用正则表达式提取子字符串。SELECT DBMS_UTILITY.REGEXP_SUBSTR('1234567890', 'd+d+d+') FROM dual;

2、数值函数

ROUND：四舍五入数字到指定的小数位数。SELECT ROUND(3.14159, 2) FROM dual;

TRUNC：截断数字到指定的小数位数。SELECT TRUNC(3.14159, 2) FROM dual;

NEG：对数字取负数。SELECT NEG(3.14159) FROM dual;

SQRT：计算数字的平方根。SELECT SQRT(9) FROM dual;

MOD：计算两个数字相除的余数。SELECT MOD(10, 3) FROM dual;

FLOOR：向下取整数字。SELECT FLOOR(3.14159) FROM dual;

CHR：将数字转换为ASCII字符。SELECT CHR(65) FROM dual;

ASCII：返回字符的ASCII码值。SELECT ASCII('A') FROM dual;

CONV：将数字从一个进制转换为另一个进制。SELECT CONV(10, 16) FROM dual;