|
Oracle常用函數(shù)/過程說明 主要介紹Oracle的系統(tǒng)函數(shù)、過程和包。 * SQL常用函數(shù)： 數(shù)值函數(shù)： * ABS Purpose 返回絕對值 Returns the absolute value of n. Example SELECT ABS(-15) "Absolute" FROM DUAL; Absolute ---------- 15 * CEIL Purpose 取最小整數(shù) 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 字符函數(shù)： * 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 變?yōu)樾?span lang=EN-US> 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,‘a(chǎn)b‘) "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 在一定范圍內(nèi)轉(zhuǎn)換字符 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次出現(xiàn)的位置 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 取兩個日期間相隔的月數(shù) 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 取系統(tǒng)日期 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 轉(zhuǎn)換函數(shù)： * CHARTOROWID Syntax CHARTOROWID(char) Purpose 將字符串轉(zhuǎn)換為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 將日期轉(zhuǎn)換為字符串 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 將數(shù)值按一定格式轉(zhuǎn)換為字符串 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 將日期轉(zhuǎn)換為字符串 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 轉(zhuǎn)換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 系統(tǒng)用戶的標碼 Returns an integer that uniquely identifies the current user. * USER Syntax USER Purpose 系統(tǒng)用戶戶名 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 組函數(shù)： * 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 取記錄數(shù) 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 實現(xiàn)數(shù)據(jù)庫間的警報。 * DBMS_OUTPUT 在同一個事務(wù)中，從PL/SQL程序中發(fā)送信息到另一個PL/SQL程序。或在SQL*PLUS中顯示有關(guān)信息。 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; /* 當發(fā)出下列命令后，SQL*Plus將顯示payroll的計算值 */ SQL> SET SERVEROUTPUT ON SQL> VARIABLE num NUMBER SQL> EXECUTE calc_payroll(:num) * DBMS_PIPE 在Session之間異步地發(fā)送/接收信息。（雙向） * 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; /* 解析動態(tài)語句 */ 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（主要用于觸發(fā)器） 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程序塊從系統(tǒng)文件中讀/寫信息。 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 取得系統(tǒng)的錯誤代碼和錯誤文本信息。 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 設(shè)置斷點，以便回退。 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是與此命名相關(guān)的所需錯誤代碼 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（） 提交作業(yè) DBMS_JOB.SUBMIT( job OUT BINARY_INTEGER, -- 作業(yè)號 what IN ARCHAR2, -- 過程名 next_date IN DATE DEFAULT SYSDATE, --下次運行的時間 interval IN VARCHAR2 DEFAULT ‘null‘, -- 再次運行的時間函數(shù) no_parse IN BOOLEAN DEFAULT FALSE) -- 解析標志 * RUN 立即運行作業(yè) * REMOVE 刪除作業(yè) * CHANGE 修改作業(yè)的屬性，WHAT、NEXT-DAY、INTERVAL則是修改相應(yīng)的屬性。 * 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() 將事務(wù)設(shè)為只讀。 相當于SET TRANSACTION READ ONLY * READ_WRITE() 將事務(wù)改為讀寫。 相當于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 |
|
|
