oracle编写函数

长度大于等于7的返回其剩余字符串,剩余

create or replace function my_fun(item varchar2) return varchar2 is

begin

  if length(item) >= 7 then

    return(substr(item, 7 + 1));

  else

    return(substr(item, 3, 1));

  end if;

end;

在ORACLE中,函数的调用方式有位置表示法调用函数、命名表示法调用函数、混合使用位置表示法和命名表示法调用函数、排除表示法、ql调用表示法 --混合表示法。

甲骨文股份有限公司（Oracle）是全球大型数据库软件公司，总部位于美国加州红木城的红木岸。在2008年，甲骨文股份有限公司是继Microsoft及IBM后，全球收入第三多的软件公司。Oracle数据库产品为财富排行榜上的前1000家公司所采用，许多大型网站也选用了Oracle系统。甲骨文股份有限公司于1989年正式进入中国，在北京、上海、广州和成都均设立了分支机构。

常用的函数有：

1、COUNT ()返回查寻的行数

例如：select count() from table;

2、MAX() 返回表达式的最大值

例如：select a, max(b) from table group by a;

3、MIN() 返回表达式的最小值

例如：select a, min(b) from table group by a;

4、SUM() 返回表达式的总合

例如：select a, sum(b) from table group by a;

5、AVG() 返回表达式的平均值

例如：select a, avg(b) from table group by a。

函数

函数在数学上的定义：给定一个非空的数集A,对A施加对应法则f,记作f（A）,得到另一数集B,也就是B=f（A）那么这个关系式就叫函数关系式,简称函数。

sum字段数据加和。

count字段个数统计。

to_date将字符串类型数据转换为日期类型数据。

to_char将其它类型转换为字符串类型。

nvl如果字段不为null返回当前字段的值，如果字段为null返回函数中指定的值。

substr字符串截取。

-- 定义一个对象类型

CREATE OR REPLACE TYPE ty_row_str_split as object (strValue VARCHAR2 (4000));

/

-- 定义一个 表/数组类型， 内容是前面定义的那个对象

CREATE OR REPLACE TYPE ty_tbl_str_split IS TABLE OF ty_row_str_split;

/

--------------------

-- 字符分割函数

-- 参数1：  被分割的源字符串

-- 参数2：  用于拆分的字符串。

--------------------

CREATE OR REPLACE FUNCTION fn_split(

  p_str       IN VARCHAR2,

  p_delimiter IN VARCHAR2)

RETURN ty_tbl_str_split IS

  j         INT := 0;

  i         INT := 1;

  -- 被分割的源字符串 的长度

  len       INT := 0;

  -- 分隔字符串的长度

  len1      INT := 0;

  -- 暂存的中间每一个单元的文本信息

  str       VARCHAR2(4000);

  -- 预期返回结果

  str_split ty_tbl_str_split := ty_tbl_str_split();

BEGIN

  -- 被分割的源字符串 的长度

  len   := LENGTH(p_str);

  -- 分隔字符串的长度

  len1 := LENGTH(p_delimiter);

  -- 遍历 被分割的源字符串

  WHILE j < len LOOP

    -- 在被分割的源字符串中， 查询 分隔字符串

    j := INSTR(p_str, p_delimiter, i);

    IF j = 0 THEN

      -- j=0 意味着没有找到

  -- 可以理解为是查询到最后一个单元了

  -- 设置 j := len， 让外部的循环处理可以结束了

      j  := len;

      -- 获取最后一个单元的内容

      str := SUBSTR(p_str, i);

      -- 结果追加一行

      str_splitEXTEND;

      -- 设置结果内容

      str_split(str_splitCOUNT) := ty_row_str_split(strValue => str);

      IF i >= len THEN

        EXIT;

      END IF;

    ELSE

      -- 如果在被分割的源字符串中，找到了 分隔字符串

      -- 首先，获取分割的内容

      str := SUBSTR(p_str, i, j - i);

      -- 然后设置索引， 下一次再查找的时候，从指定的索引位置开始（不是从0开始找了）

      i := j + len1;

      -- 结果追加一行

      str_splitEXTEND;

      -- 设置结果内容

      str_split(str_splitCOUNT) := ty_row_str_split(strValue => str);

    END IF;

  END LOOP;

  RETURN str_split;

END fn_split;

/

函数创建完毕以后，可以开始做查询的处理

SQL> select to_char(strvalue) as Value from table(fn_split('aa,bb,cc',','));

VALUE

-------------------------------------------------------------------------------

aa

bb

cc

登录这个网站可以查询到你要的详细清单

docsoraclecom

下面是部分函数清单及简要说明，仅供参考

Oracle SQL Functions

Functions:

ABS(n) Absolute value of number

ACOS(n) arc cosine of n

ADD_MONTHS(date,num_months)

Returns date + num_months

ASCII(char) Converts char into a decimal ascii code

ASIN(n) arc sine of n

ATAN(n) arc tangent of n

ATAN2(nm) arc tangent of n and m

AVG([DISTINCT]n)

Averge value of 'n' ignoring NULLs

BETWEEN value AND value

Where 'x' between 25 AND 100

BFILENAME('directory','filename')

Get the BFILE locator associated with a physical LOB binary file

CASE Group the data into sub-sets

CEIL(n) Round n up to next whole number

CHARTOROWID(char) Converts a Char into a rowid value

CHR(n) Character with value n

CONCAT(s1,s2) Concatenate string1 and string2

CONVERT(char_to_convert, new_char_set, old_char_set)

Convert a string from one character set to another

COS(n) Cosine of number

COSH(n) Hyperbolic Cosine of number

COUNT() Count the no of rows returned

COUNT([DISTINCT] expr)

Count the no of rows returned by expr

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

DECODE IF x THEN return y ELSE return z

DENSE_RANK Calculate the rank of a value in a group

DEREF(e) Return the object reference of argument e

DUMP(expr,fmt[,start,length])

Convert to dec/hex/oct and display char set

EMPTY_BLOB Return an empty LOB locator (use to empty a column or variable)

EMPTY_CLOB Return an empty LOB locator (use to empty a column or variable)

EXISTS Return TRUE if a subquery returns at least one row

EXP(n) Exponential (e to 'n'th power)

EXTRACT Extract part of a DATE (Year,Month,Day,Second,etc)

FLOOR(n) Round n down to the next whole number

GREATEST(expression, expression)

Returns the largest in a list of expressions

GROUPING Grouping for superaggregate rows=NULL

(see GROUP BY ROLLUP/CUBE)

HEXTORAW(char) Convert char containing hex digits to a raw value

IN (list of comma separated values)

Effectively a shorthand for ['x' = y OR 'x' = z] ie

Where 'x' IN ('sales','marketing','recruitment')

INITCAP(char) String with Initial Capitals

INSTR(str, chars[,s[,n]])

Find the 'n'th occurence of 'chars' in 'str'

Starting at position 's'

n and s default to 1

INSTRB (str, chars[,s[,n]])

The same as INSTR, except that 's' and the return value are expressed in bytes,

use for double-byte char sets

IS NULL Check for NULL (empty) values (Select from demo Where x IS NULL;)

IS NOT NULL Check for items that contain a value (Select from demo Where x IS NOT NULL;)

LAST_DAY(date)Returns the last day of month in Date

LEAST(expression, expression)

Returns the smallest in a list of expressions

LENGTH(char) Returns the number of characters in char

LENGTHB(char) Returns the number of bytes in char (use for double-byte char sets)

LIKE wildcard/value

Wildcards are [% = any chars] [ _ = any one char]

Where 'x' LIKE 'smith%' [will find 'Smithson']

Where 'x' LIKE 'smith_' [will find 'Smithy']

LN(n) Natural Log of n, where n>0

LOG(b,n) log of n, base b

LOWER(char) Returns character string in lowercase

LPAD(char, n[,PadChar])

Left Pad char with n spaces [or PadChars]

LTRIM(char[,set])

Left Trim char - remove leading spaces [or char set]

MAKE_REF(table,key)

Create a REF to a row of an OBJECT view/table

MAX([DISTINCT] expr)

Maximum value returned by expr

MIN([DISTINCT] expr)

Minimum value returned by expr

MOD(x,y) Remainder of x divided by y

MONTHS_BETWEEN(end_date, start_date)

Number of months between the 2 dates (integer)

NEW_TIME(date, zone1, zone2)

Convert between GMT and US time zones (but not CET)

NEXT_DAY(date,day_of_week)

'12-OCT-01','Monday' will return the next Mon after 12 Oct

NLS_CHARSET_DECL_LEN (bytecount,charset)

Returns the declaration width (no of chars) of an NCHAR column

NLS_CHARSET_ID(varchars)

Returns the char set ID given a charset name

NLS_CHARSET_NAME(charset_id)

Returns the char set name given a charset id

NLS_INITCAP(char[,'NLS_SORT = sort_sequence'])

Returns char in Initial Caps, using an NLS sort_sequence

either the session default or specified directly

NLS_LOWER(char[,'NLS_SORT = sort_sequence'])

Returns char in lower case, using an NLS sort_sequence

either the session default or specified directly

NLSSORT(char[,'NLS_SORT = sort_sequence'])

Return the string of bytes used to sort char, using an NLS sort_sequence

either the session default or specified directly

NLS_UPPER(char[,'NLS_SORT = sort_sequence'])

Returns char in UPPER case, using an NLS sort_sequence

either the session default or specified directly

NVL(expression, value_if_null)

If expression is null, returns value_if_null; if expression is not null, returns expression

The arguments can have any datatype (Oracle will perform implicit conversion where needed)

PERCENT_RANK Calculate the percent rank of a value in a group

POWER(m,n) m raised to the nth power

RANK Calculate the rank of a value in a group

RAWTOHEX(raw) Convert raw to a character value containing its hex equivalent

REF(table_alias)

Returns a REF value for an object instance (bound to the variable or row)

The table alias (correlation variable) is associated with

one row of an object table or an object view in an SQL statement

REFTOHEX(ref) Convert ref (object type) to a char value containing its hex equivalent

REPLACE(char, search_str[, replace_str])

ANSI alternative to decode() Replace every occurrence of search_str

with replace_str, replace_str defaults to null

ROUND(n,d) n rounded to d decimal places (d defaults to 0)

ROUND(date,fmt)

date rounded to fmt

ROWIDTOCHAR(rowid)

Convert a rowid value to VARCHAR2

ROW_NUMBER Assign a unique number to each row of results

RPAD(char, n[,PadChar])

Right Pad char with n spaces [or PadChars]

RTRIM(char[,set])

Right Trim char - remove trailing spaces [or char set]

SIGN(n) positive = 1, zero = 0, negative = -1

SIN(n) Sine of n in Radians

SINH(n) Hyperbolic Sine of n in Radians

SOUNDEX(char) Returns a char value representing the sound of the words

SQRT(n) Square Root (returns NULL for negative no's)

STDDEV([DISTINCT] n)

Standard deviation of n

SUBSTR(char, s[,l])

A substring of char, starting at character s, length l

SUBSTRB(char, s[,l])

A substring of char, starting at character s, length l

The same as SUBSTR, except that 's', 'l' and the return value are expressed in bytes,

use for double-byte char sets

SUM([DISTINCT] n)

Sum of values of n, ignoring NULLs

SYS_CONTEXT('namespace','attribute_name')

Examine the package associated with the context namespace

Possible attributes are: NLS_TERRITORY, NLS_CURRENCY, NLS_CALENDAR

NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, SESSION_USER, CURRENT_USER

CURRENT SCHEMAID,SESSION_USERID, CURRENT_USERID, CURRENT_SCHEMA

note: CURRENT_USER may be different from SESSION_USER within a stored procedure

(eg an invoker-rights procedure)

SYS_CONTEXT ('USERENV','IP_ADDRESS')

SYS_GUID() Returns a globally unique identifier (16 byte RAW value)

SYSDATE The current system date & time

TAN(n) Tangent of n in Radians

TANH(n) Hyperbolic tangent of n in Radians

TO_BLOB(Raw_col) Convert LONG RAW and RAW values to BLOB

TO_CHAR Convert to a character String

TO_CLOB Convert character or NCLOB values to the database character set

TO_DATE Convert to date value

TO_LOB(long) Convert LONG values to CLOB or NCLOB values

or convert LONG RAW values to BLOB values

TO_MULTI_BYTE(char)

Convert single-byte char to multi-byte char

TO_NCHAR(expr) Convert a TEXT expression, date, or number to NTEXT in a specified format

Mostly used to format output data

TO_NCLOB Convert any character string (including LOBs) to the national character set

TO_NUMBER Convert to numeric format

TO_SINGLE_BYTE(char)

Convert multi-byte char to single-byte character

TO_TIME Convert to time value

TO_TIME_TZ Convert to time zone

TO_TIMESTAMP Convert to timestamp

TO_TIMESTAMP_TZ

TO_YMINTERVAL Convert a character string to an INTERVAL YEAR TO MONTH type

TRANSLATE('char','search_str','replace_str')

Replace every occurrence of search_str with replace_str

unlike REPLACE() if replace_str is NULL the function returns NULL

TRANSLATE (text USING charset)

Convert text into a specific character set

Use this instead of CONVERT() if either the input or output datatype

is NCHAR or NVARCHAR2

TRIM(LEADING|TRAILING|BOTH trim_char FROM trim_source)

Return trim_source as a VARCHAR2 with leading/trailing items removed

trim_char defaults to a space ' ' but may be numeric or char 'A'

TRUNC(i,d) Truncate i to d decimal places (d defaults to 0)

TRUNC(date,fmt) Truncate Date to nearest fmt

UID User id (a unique number)

UPPER(char) Return characters in uppercase

USER Return the current Username

USERENV('option')

Can return any of the options: ENTRYID, SESSIONID,

TERMINAL, LANGUAGE, ISDBA, LANG, INSTANCE, CLIENT_INFO

VALUE(correlation_variable)

Return the object instance for a row of an object table

as associated with the correlation_variable (table alias)

VARIANCE([DISTINCT] n)

Variance of n, ignoring NULLs

VSIZE(expr) Value Size, returns the number of bytes used by each row of expr

Examples

Return the left 4 characters from the column prod_code, like a left() function in other languages:

SQL> select substr(prod_code,1,4) from sales;

Return the right 3 characters from the column prod_code, like a right() function in other languages:

SQL> select substr(prod_code,-3) from sales;

Return the leftmost 2 digits of idnum:

SQL> select substr(to_char(idnum),1,2) from mytable;

This page is not an exhaustive list of all the functions available - to find a complete list of functions for a particular release of Oracle see docsoraclecom or run this query:

SELECT distinct object_name

FROM all_arguments

WHERE package_name = 'STANDARD';

在平时的工作中，由于主要接触ERP方面的业务数据处理，Oracle的分析函数一直没怎么使用，但随着公司开始做数据分析，Oracle的分析函数出现的越来越频繁。因此准备把分析函数中重要的几个常用函数用法分析下。

Lag(): 在查询中取出同一字段前N行的数据作为独立的列。

表达式理解为：按column2进行分组且根据column3进行排序，取column1前N行(往前数第N行)的列值，如果为空则用xxxx进行默认。

由此可以想到，采用该方式可以计算环比以及同比，同比与表间join，该函数将大大减少SQL的量。

Lead(): 在查询中取出同一字段后N行的数据作为独立的列。

表达式理解为：按column2进行分组且根据column3进行排序，取column1后N行(往后数第N行)的列值，如果为空则用xxxx进行默认。

lag和lead的函数，主要应用于查找前后行的列记录，使用这两个分析函数，可以减少子查询或表关联，并且能够大大提升sql语句性能。

oracle的NVL函数的功能室如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。

NVL(E1,E2)的功能为：如果E1为NULL，则函数返回E2，否则返回E1本身。但此函数有一定局限，所以就有了NVL2函数。

NVL2函数的格式如下：NVL2(expr1,expr2,expr3)

含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。

NVL2函数:Oracle/PLSQL中的一个函数，NVL2(E1,E2,E3)的功能为：如果E1为NULL，则函数返回E3，若E1不为null，则返回E2。

扩展资料：

NVL函数的功能是实现空值的转换，根据第一个表达式的值是否为空值来返回响应的列名或表达式，主要用于对数据列上的空值进行处理，语法格式如：NVL(string1,replace_with)

如果第一个参数的值为空值，则返回第二个参数的值，否则返回第一个参数的值。如果两个参数的值都为空值，则返回空值。

第一个参数和第二个参数可以是任何类型的数据，但两个参数的数据类型必须相同（或能够由Oracle隐式转换为相同的类型）。

--nvl函数