oracle数据库函数和包,常用Oracle的系统函数、过程和包。

Oracle常用函数/过程说明

主要介绍Oracle的系统函数、过程和包。

* SQL常用函数：

数值函数：

* ABS

Purpose返回绝对值

Returns the absolute value of n.

Example

SELECT ABS(-15) "Absolute" FROM DUAL;

Absolute

----------

15

* CEIL

Purpose取最小整数

Returns smallest integer greater than or equal to n.

Example

SELECT CEIL(15.7) "Ceiling" FROM DUAL;

Ceiling

----------

16

* MOD

Syntax

MOD(m,n)

Purpose取余

Returns remainder of m divided by n. Returns m if n is 0.

Example

SELECT MOD(11,4) "Modulus" FROM DUAL;

Modulus

----------

3

* ROUND

Syntax

ROUND(n[,m])

Purpose取四舍五入信息

Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer.

Example 1

SELECT ROUND(15.193,1) "Round" FROM DUAL;

Round

----------

15.2

Example 2

SELECT ROUND(15.193,-1) "Round" FROM DUAL;

Round

----------

20

* TRUNC

Purpose取截取后的信息

Returns n truncated to m decimal places; if m is omitted, to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point.

Examples

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

Truncate

----------

15.7

SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;

Truncate

----------

10

字符函数：

* CONCAT

Syntax

CONCAT(char1, char2)

Purpose合并字符串，相当于“||”

Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||). For information on this operator, see "Concatenation Operator".

Example

This example uses nesting to concatenate three character strings:

SELECT CONCAT( CONCAT(ename, ‘ is a ‘), job) "Job"

FROM emp

WHERE empno = 7900;

Job

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

JAMES is a CLERK

* LOWER

Purpose变为小写

Returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2).

Example

SELECT LOWER(‘MR. SCOTT MCMILLAN‘) "Lowercase"

FROM DUAL;

Lowercase

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

mr. scott mcmillan

* LPAD

Purpose左填充

Returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.

The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.

Example

SELECT LPAD(‘Page 1‘,15,‘*.‘) "LPAD example"

FROM DUAL;

LPAD example

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

*.*.*.*.*Page 1

* LTRIM

Syntax

LTRIM(char [,set])

Purpose左截取

Removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. Oracle begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result.

Example

SELECT LTRIM(‘xyxXxyLAST WORD‘,‘xyXLA‘) "LTRIM example"

FROM DUAL;

LTRIM exampl

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

ST WORD

* REPLACE

Syntax

REPLACE(char,search_string[,replacement_string])

Purpose替换

Returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned. This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE allows you to substitute one string for another as well as to remove character strings.

Example

SELECT REPLACE(‘JACK and JUE‘,‘J‘,‘BL‘) "Changes"

FROM DUAL;

Changes

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

BLACK and BLUE

* RPAD

Syntax

RPAD(char1, n [,char2])

Purpose右填充

Returns char1, right-padded to length n with char2, replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n.

The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string.

Example

SELECT RPAD(‘MORRISON‘,12,‘ab‘) "RPAD example"

FROM DUAL;

RPAD example

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

MORRISONabab

* RTRIM

Syntax

RTRIM(char [,set]

Purpose

Returns char, with all the rightmost characters that appear in set removed; set defaults to a single blank. RTRIM works similarly to LTRIM.

Example

SELECT RTRIM(‘BROWNINGyxXxy‘,‘xy‘) "RTRIM e.g."

FROM DUAL;

RTRIM e.g

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

BROWNINGyxX

* SUBSTR

Syntax

SUBSTR(char, m [,n])

Purpose截取字符串

Returns a portion of char, beginning at character m, n characters long. If m is 0, it is treated as 1. If m is positive, Oracle counts from the beginning of char to find the first character. If m is negative, Oracle counts backwards from the end of char. If n is omitted, Oracle returns all characters to the end of char. If n is less than 1, a null is returned.

Floating-point numbers passed as arguments to substr are automatically converted to integers.

Example 1

SELECT SUBSTR(‘ABCDEFG‘,3,4) "Subs"

FROM DUAL;

Subs

----

CDEF

Example 2

SELECT SUBSTR(‘ABCDEFG‘,-5,4) "Subs"

FROM DUAL;

Subs

----

CDEF

* TRANSLATE

Syntax

TRANSLATE(char, from, to)

Purpose在一定范围内转换字符

Returns char with all occurrences of each character in from replaced by its corresponding character in to. Characters in char that are not in from are not replaced. The argument from can contain more characters than to. In this case, the extra characters at the end of from have no corresponding characters in to. If these extra characters appear in char, they are removed from the return value. You cannot use an empty string for to to remove all characters in from from the return value. Oracle interprets the empty string as null, and if this function has a null argument, it returns null.

Example 1

The following statement translates a license number. All letters ‘ABC...Z‘ are translated to ‘X‘ and all digits ‘012 . . . 9‘ are translated to ‘9‘:

SELECT TRANSLATE(‘2KRW229‘,

‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ‘,

‘9999999999XXXXXXXXXXXXXXXXXXXXXXXXXX‘) "License"

FROM DUAL;

License

--------

9XXX999

Example 2

The following statement returns a license number with the characters removed and the digits remaining:

SELECT TRANSLATE(‘2KRW229‘,

‘0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ‘,

‘0123456789‘)

"Translate example"

FROM DUAL;

Translate example

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

2229

* UPPER

Syntax

UPPER(char)

Purpose大写

Returns char, with all letters uppercase. The return value has the same datatype as the argument char.

Example

SELECT UPPER(‘Large‘) "Uppercase"

FROM DUAL;

Upper

-----

LARGE

* ASCII

Syntax

ASCII(char)

Purpose取字符的ASCII值

Returns the decimal representation in the database character set of the first character of char. If your database character set is 7-bit ASCII, this function returns an ASCII value. If your database character set is EBCDIC Code Page 500, this function returns an EBCDIC value. Note that there is no similar EBCDIC character function.

Example

SELECT ASCII(‘Q‘)

FROM DUAL;

ASCII(‘Q‘)

----------

81

* INSTR

Syntax

INSTR (char1,char2 [,n[,m]])

Purpose从char1中第n个字符开始char2第m次出现的位置

Searches char1 beginning with its nth character for the mth occurrence of char2 and returns the position of the character in char1 that is the first character of this occurrence. If n is negative, Oracle counts and searches backward from the end of char1. The value of m must be positive. The default values of both n and m are 1, meaning Oracle begins searching at the first character of char1 for the first occurrence of char2. The return value is relative to the beginning of char1, regardless of the value of n, and is expressed in characters. If the search is unsuccessful (if char2 does not appear m times after the nth character of char1) the return value is 0.

Example 1

SELECT INSTR(‘CORPORATE FLOOR‘,‘OR‘, 3, 2)

"Instring" FROM DUAL;

Instring

----------

14

Example 2

SELECT INSTR(‘CORPORATE FLOOR‘,‘OR‘, -3, 2)

"Reversed Instring"

FROM DUAL;

Reversed Instring

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

2

* LENGTH

Syntax

LENGTH(char)

Purpose取字符串的长度

Returns the length of char in characters. If char has datatype CHAR, the length includes all trailing blanks. If char is null, this function returns null.

Example

SELECT LENGTH(‘CANDIDE‘) "Length in characters"

FROM DUAL;

Length in characters

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

7

* ADD_MONTHS

Syntax

ADD_MONTHS(d,n)

Purpose取N个月后的日期

Returns the date d plus n months. The argument n can be any integer. If d is the last day of the month or if the resulting month has fewer days than the day component of d, then the result is the last day of the resulting month. Otherwise, the result has the same day component as d.

Example

SELECT TO_CHAR(

ADD_MONTHS(hiredate,1),

‘DD-MON-YYYY‘) "Next month"

FROM emp

WHERE ename = ‘SMITH‘;

Next Month

-----------

17-JAN-1981

* LAST_DAY

Syntax

LAST_DAY(d)

Purpose取D所在月份的最后一天

Returns the date of the last day of the month that contains d. You might use this function to determine how many days are left in the current month.

Example 1

SELECT SYSDATE,

LAST_DAY(SYSDATE) "Last",

LAST_DAY(SYSDATE) - SYSDATE "Days Left"

FROM DUAL;

SYSDATELastDays Left

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

23-OCT-97 31-OCT-978

Example 2

SELECT TO_CHAR(

ADD_MONTHS(

LAST_DAY(hiredate),5),

‘DD-MON-YYYY‘) "Five months"

FROM emp

WHERE ename = ‘MARTIN‘;

Five months

-----------

28-FEB-1982

* MONTHS_BETWEEN

Syntax

MONTHS_BETWEEN(d1, d2)

Purpose取两个日期间相隔的月数

Returns number of months between dates d1 and d2. If d1 is later than d2, result is positive; if earlier, negative. If d1 and d2 are either the same days of the month or both last days of months, the result is always an integer; otherwise Oracle calculates the fractional portion of the result based on a 31-day month and considers the difference in time components of d1 and d2.

Example

SELECT MONTHS_BETWEEN

(TO_DATE(‘02-02-1995‘,‘MM-DD-YYYY‘),

TO_DATE(‘01-01-1995‘,‘MM-DD-YYYY‘) ) "Months"

FROM DUAL;

Months

----------

1.03225806

* NEXT_DAY

Syntax

NEXT_DAY(d, char)

Purpose取D下一个weekday的日期

Returns the date of the first weekday named by char that is later than the date d. The argument char must be a day of the week in your session‘s date language-either the full name or the abbreviation. The minimum number of letters required is the number of letters in the abbreviated version; any characters immediately following the valid abbreviation are ignored. The return value has the same hours, minutes, and seconds component as the argument d.

Example

This example returns the date of the next Tuesday after March 15, 1992.

SELECT NEXT_DAY(‘15-MAR-92‘,‘TUESDAY‘) "NEXT DAY"

FROM DUAL;

NEXT DAY

---------

17-MAR-92

* SYSDATE

Syntax

SYSDATE

Purpose取系统日期

Returns the current date and time. Requires no arguments. In distributed SQL statements, this function returns the date and time on your local database. You cannot use this function in the condition of a CHECK constraint.

Example

SELECT TO_CHAR

(SYSDATE, ‘MM-DD-YYYY HH24:MI:SS‘)"NOW"

FROM DUAL;

NOW

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

10-29-1993 20:27:11

转换函数：

* CHARTOROWID

Syntax

CHARTOROWID(char)

Purpose将字符串转换为ROWID

Converts a value from CHAR or VARCHAR2 datatype to ROWID datatype.

Example

SELECT ename FROM emp

WHERE ROWID = CHARTOROWID(‘AAAAfZAABAAACp8AAO‘);

ENAME

----------

LEWIS

* TO_CHAR, date conversion

Syntax

TO_CHAR(d [, fmt [, ‘nlsparams‘] ])

Purpose将日期转换为字符串

Converts d of DATE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt. If you omit fmt, d is converted to a VARCHAR2 value in the default date format. For information on date formats, see "Format Models".

Example

SELECT TO_CHAR(HIREDATE, ‘Month DD, YYYY‘)

"New date format" FROM emp

WHERE ename = ‘BLAKE‘;

New date format

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

May01, 1981

* TO_CHAR, number conversion

Syntax

TO_CHAR(n [, fmt [, ‘nlsparams‘] ])

Purpose将数值按一定格式转换为字符串

Converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, n is converted to a VARCHAR2 value exactly long enough to hold its significant digits. For information on number formats, see "Format Models".

Example 1

In this example, the output is blank padded to the left of the currency symbol.

SELECT TO_CHAR(-10000,‘L99G999D99MI‘) "Amount"

FROM DUAL;

Amount

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

$10,000.00-

* TO_DATE

Syntax

TO_DATE(char [, fmt [, ‘nlsparams‘] ])

Purpose将日期转换为字符串

Converts char of CHAR or VARCHAR2 datatype to a value of DATE datatype. The fmt is a date format specifying the format of char. If you omit fmt, char must be in the default date format. If fmt is ‘J‘, for Julian, then char must be an integer. For information on date formats, see "Format Models".

The ‘nlsparams‘ has the same purpose in this function as in the TO_CHAR function for date conversion.

Do not use the TO_DATE function with a DATE value for the char argument. The returned DATE value can have a different century value than the original char, depending on fmt or the default date format.

For information on date formats, see "Date Format Models".

Example

INSERT INTO bonus (bonus_date)

SELECT TO_DATE(

‘January 15, 1989, 11:00 A.M.‘,

‘Month dd, YYYY, HH:MI A.M.‘,

‘NLS_DATE_LANGUAGE = American‘)

FROM DUAL;

* TO_NUMBER

Syntax

TO_NUMBER(char [,fmt [, ‘nlsparams‘] ])

Purpose

Converts char, a value of CHAR or VARCHAR2 datatype containing a number in the format specified by the optional format model fmt, to a value of NUMBER datatype.

* NVL

Syntax

NVL(expr1, expr2)

Purpose转换null值

If expr1 is null, returns expr2; if expr1 is not null, returns expr1. The arguments expr1 and expr2 can have any datatype. If their datatypes are different, Oracle converts expr2 to the datatype of expr1 before comparing them. The datatype of the return value is always the same as the datatype of expr1, unless expr1 is character data, in which case the return value‘s datatype is VARCHAR2.

Example

SELECT ename, NVL(TO_CHAR(COMM), ‘NOT

APPLICABLE‘)

"COMMISSION" FROM emp

WHERE deptno = 30;

ENAMECOMMISSION

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

ALLEN300

WARD500

MARTIN1400

BLAKENOT APPLICABLE

TURNER0

JAMESNOT APPLICABLE

* UID

Syntax

UID

Purpose系统用户的标码

Returns an integer that uniquely identifies the current user.

* USER

Syntax

USER

Purpose系统用户户名

Returns the current Oracle user with the datatype VARCHAR2. Oracle compares values of this function with blank-padded comparison semantics.

In a distributed SQL statement, the UID and USER functions identify the user on your local database. You cannot use these functions in the condition of a CHECK constraint.

Example

SELECT USER, UID FROM DUAL;

USERUID

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

SCOTT19

组函数：

* AVG

Syntax

AVG([DISTINCT|ALL] n)

Purpose取平均值

Returns average value of n.

Example

SELECT AVG(sal) "Average"

FROM emp;

Average

----------

2077.21429

* COUNT

Syntax

COUNT({* | [DISTINCT|ALL] expr})

Purpose取记录数

Returns the number of rows in the query.

If you specify expr, this function returns rows where expr is not null. You can count either all rows, or only distinct values of expr.

If you specify the asterisk (*), this function returns all rows, including duplicates and nulls.

Example 1

SELECT COUNT(*) "Total"

FROM emp;

Total

----------

18

Example 2

SELECT COUNT(job) "Count"

FROM emp;

Count

----------

14

Example 3

SELECT COUNT(DISTINCT job) "Jobs"

FROM emp;

Jobs

----------

5

* MAX

Syntax

MAX([DISTINCT|ALL] expr)

Purpose取最大值

Returns maximum value of expr.

Example

SELECT MAX(sal) "Maximum" FROM emp;

Maximum

----------

5000

* MIN

Syntax

MIN([DISTINCT|ALL] expr)

Purpose

Returns minimum value of expr.

Example

SELECT MIN(hiredate) "Earliest" FROM emp;

Earliest

---------

17-DEC-80

* SUM

Syntax

SUM([DISTINCT|ALL] n)

Purpose求和

Returns sum of values of n.

Example

SELECT SUM(sal) "Total"

FROM emp;

Total

----------

29081

* DBMS_ALERT

实现数据库间的警报。

* DBMS_OUTPUT

在同一个事务中，从PL/SQL程序中发送信息到另一个PL/SQL程序。或在SQL*PLUS中显示有关信息。

CREATE PROCEDURE calc_payroll (payroll IN OUT REAL) AS

CURSOR c1 IS SELECT sal,comm FROM emp;

BEGIN

payroll := 0;

FOR c1rec IN c1 LOOP

c1rec.comm := NVL(c1rec.comm, 0);

payroll := payroll + c1rec.sal + c1rec.comm;

END LOOP;

/* Display debug info. */

dbms_output.put_line(‘payroll: ‘ || TO_CHAR(payroll));

END calc_payroll;

/*当发出下列命令后，SQL*Plus将显示payroll的计算值*/

SQL> SET SERVEROUTPUT ON

SQL> VARIABLE num NUMBER

SQL> EXECUTE calc_payroll(:num)

* DBMS_PIPE

在Session之间异步地发送/接收信息。(双向)

* DBMS_SQL

CREATE PROCEDURE drop_table (table_name IN VARCHAR2) AS

cid INTEGER;

BEGIN

/* Open new cursor and return cursor ID. */

cid := DBMS_SQL.OPEN_CURSOR;

/*解析动态语句*/

DBMS_SQL.PARSE(cid, ‘DROP TABLE ‘ || table_name, dbms_sql.v7);

/* Close cursor. */

DBMS_SQL.CLOSE_CURSOR(cid);

EXCEPTION

/* If an exception is raised, close cursor before exiting. */

WHEN OTHERS THEN

DBMS_SQL.CLOSE_CURSOR(cid);

RAISE;-- reraise the exception

END drop_table;

* DBMS_STANDARD

* FUNCTION(主要用于触发器)

INSERTING

UPDATING(colname)

DELETING

* RAISE_APPLICATION_ERROR用户自定义出错信息，与Oracle错误信息传递的作用是一样的。错误号的范围是20000-20999。

CREATE PROCEDURE raise_salary (emp_id NUMBER, increase NUMBER) AS

current_salary NUMBER;

BEGIN

SELECT sal INTO current_salary FROM emp

WHERE empno = emp_id;

IF current_salary IS NULL THEN

/* Issue user-defined error message. */

raise_application_error(-20101, ‘Salary is missing‘);

ELSE

UPDATE emp SET sal = current_salary + increase

WHERE empno = emp_id;

END IF;

END raise_salary;

* UTL_FILE

允许PL/SQL程序块从系统文件中读/写信息。

FOPEN

Open a file for input or output. Create an output file if it does not exist.

IS_OPEN

Determine if a file handle refers to an open file.

FCLOSE

Close a file.

FCLOSE_ALL

Close all open file handles.

GET_LINE

Read a line of text from an open file.

PUT

Write a line to a file. Do not append a line terminator.

PUT_LINE

Write a line to a file. Append an OS-specific line terminator.

PUTF

A PUT procedure with formatting.

NEW_LINE

Write one or more OS-specific line terminators to a file.

FFLUSH

Physically write all pending output to a file.

* SQLCODE、SQLERRM

取得系统的错误代码和错误文本信息。

DECLARE

err_num NUMBER;

err_msg VARCHAR2(100);

BEGIN

...

EXCEPTION

...

WHEN OTHERS THEN

err_num := SQLCODE;

err_msg := SUBSTR(SQLERRM, 1, 100);

INSERT INTO errors VALUES (err_num, err_msg);

* SAVEPOINT、ROLLBACK

设置断点，以便回退。

DECLARE

nameCHAR(20);

ans1CHAR(3);

ans2CHAR(3);

ans3 CHAR(3);

suffix NUMBER := 1;

BEGIN

...

LOOP-- could be FOR i IN 1..10 LOOP to allow ten tries

BEGIN-- sub-block begins

SAVEPOINT start_transaction;-- mark a savepoint

/* Remove rows from a table of survey results. */

DELETE FROM results WHERE answer1 = ‘NO‘;

/* Add a survey respondent‘s name and answers. */

INSERT INTO results VALUES (name, ans1, ans2, ans3);

-- raises DUP_VAL_ON_INDEX if two respondents

-- have the same name (because there is a unique

-- index on the name column)

COMMIT;

EXIT;

EXCEPTION

WHEN DUP_VAL_ON_INDEX THEN

ROLLBACK TO start_transaction;-- undo changes

suffix := suffix + 1;-- try to fix

name := name || TO_CHAR(suffix);-- problem

...

END;-- sub-block ends

END LOOP;

END;

* EXCEPTION_INIT

在PL/SQL，编译指示器(pragma)通过EXCEPTION_INIT告诉编译器给自定义的异常处理分配一个Oracle错误代码。异常处理必须先定义。

语法格式如下：

PRAGMA EXCEPTION_INIT(exception_name, Oracle_error_number);

----Oracle_error_number是与此命名相关的所需错误代码

DECLARE

deadlock_detected EXCEPTION;

PRAGMA EXCEPTION_INIT(deadlock_detected, -60);

BEGIN

...

EXCEPTION

WHEN deadlock_detected THEN

-- handle the error

...

END;

* DBMS_JOB

可以定时运行PL/SQL例程。通过SNP进程管理。

* SUBMIT()提交作业

DBMS_JOB.SUBMIT( jobOUTBINARY_INTEGER,--作业号

whatINARCHAR2,--过程名

next_dateINDATE DEFAULT SYSDATE,--下次运行的时间

intervalINVARCHAR2 DEFAULT ‘null‘,--再次运行的时间函数

no_parseINBOOLEAN DEFAULT FALSE)--解析标志

* RUN立即运行作业

* REMOVE删除作业

* CHANGE修改作业的属性，WHAT、NEXT-DAY、INTERVAL则是修改相应的属性。

* DBMS_UTILITY

* COMPILE_SCHEMA(用户名)

注意：

Note 1: not allowed in triggers

Note 2: not allowed in procedures called from SQL*Forms

Note 3: not allowed in read-only transactions

Note 4: not allowed in remote (coordinated) sessions

Note 5: not allowed in recursive sessions

Note 6: not allowed in stored procedures

* DBMS_TRANSACTION

* READ_ONLY()将事务设为只读。

相当于SET TRANSACTION READ ONLY

* READ_WRITE()将事务改为读写。

相当于SET TRANSACTION READ WRITE

* USE_ROLLBACK_SEGMENT(rb_name varchar2)

相当于SET TRANSACTION USE ROLLBACK SEGMENT

* SAVEPOINT(savept varchar2)

相当于SAVEPOINT

* rollback_savepoint(svpt varchar2)

相当于ROLLBACK ... TO SAVEPOINT ...

* A