|
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; SYSDATE Last Days Left --------- --------- ---------- 23-OCT-97 31-OCT-97 8 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 ------------------ May 01, 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; ENAME COMMISSION ---------- ------------------------------------- ALLEN 300 WARD 500 MARTIN 1400 BLAKE NOT APPLICABLE TURNER 0 JAMES NOT 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; USER UID ------------------------------ ---------- SCOTT 19 组函数： * 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 name CHAR(20); ans1 CHAR(3); ans2 CHAR(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( job OUT BINARY_INTEGER, -- 作业号 what IN ARCHAR2, -- 过程名 next_date IN DATE DEFAULT SYSDATE, --下次运行的时间 interval IN VARCHAR2 DEFAULT ‘null‘, -- 再次运行的时间函数 no_parse IN BOOLEAN 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 |
|
|
