下载文档原格式
oracle时间相减函数Oracle是一个广泛使用的关系型数据库管理系统，它提供了许多强大的函数和操作符来处理日期和时间数据类型。
其中，时间相减函数是一种常用的功能，它可以用于计算两个时间之间的时间间隔。
在Oracle中，时间相减函数主要包括以下几种：DATEDIFF、TIMESTAMPDIFF、INTERVAL和EXTRACT。
下面将逐一介绍它们的用法和功能。
1. DATEDIFF函数DATEDIFF函数用于计算两个日期之间的天数、小时数、分钟数等时间间隔。
其基本语法如下：DATEDIFF(interval, start_date, end_date)其中，interval参数指定要计算的时间间隔单位，可以是YEAR、MONTH、DAY、HOUR、MINUTE等。
start_date和end_date参数分别指定两个日期。
例如，要计算两个日期之间的天数间隔，可以使用以下语句：SELECT DATEDIFF('DAY', '2022-01-01', '2022-01-10') AS days_diff FROM dual;上述语句将返回结果为9，表示2022年1月1日和2022年1月10日之间相差9天。
2. TIMESTAMPDIFF函数TIMESTAMPDIFF函数用于计算两个时间戳之间的时间间隔。
其基本语法如下：TIMESTAMPDIFF(interval, start_timestamp, end_timestamp)其中，interval参数指定要计算的时间间隔单位，可以是YEAR、MONTH、DAY、HOUR、MINUTE等。
start_timestamp和end_timestamp参数分别指定两个时间戳。
例如，要计算两个时间戳之间的小时数间隔，可以使用以下语句：SELECT TIMESTAMPDIFF('HOUR', '2022-01-01 12:00:00', '2022-01-01 18:00:00') AS hours_diff FROM dual;上述语句将返回结果为6，表示2022年1月1日12:00:00和2022年1月1日18:00:00之间相差6小时。
Oracle中包（Package）⼀、什么要使⽤包？在⼀个⼤型项⽬中，可能有很多模块，⽽每个模块⼜有⾃⼰的过程、函数等。
⽽这些过程、函数默认是放在⼀起的（如在PL/SQL中，过程默认都是放在⼀起的，即Procedures中），这些⾮常不⽅便查询和维护，甚⾄会发⽣误删除的事件。
PL/SQL为了满⾜程序模块化的需要，引⼊了包的构造。
通过使⽤包就可以分类管理过程和函数等。
（1）包是⼀种数据库对象，相当于⼀个容器。
将逻辑上相关的过程、函数、变量、常量和游标组合成⼀个更⼤的单位。
⽤户可以从其他 PL/SQL 块中对其进⾏引⽤（2）包类似于C++和JAVA语⾔中的类，其中变量相当于类中的成员变量，过程和函数相当于类⽅法。
把相关的模块归类成为包，可使开发⼈员利⽤⾯向对象的⽅法进⾏开发，具有⾯向对象程序设计语⾔的特点，（4）PL/SQL的包具有信息隐蔽性(information hiding)，仅在算法和数据结构设计有关层可见。
可将过程说明和过程体组成⼀个程序单位。
也可将过程说明与它的过程体分开。
也可在包中定义过程，⽽该过程在包说明中没有定义过程说明，这样定义的过程仅在包内使⽤。
（5）在PL/SQL程序设计中，使⽤包不仅可以使程序设计模块化，对外隐藏包内所使⽤的信息（通过使⽤私⽤变量），⽽写可以提⾼程序的执⾏效率。
因为，当程序⾸次调⽤包内函数或过程时，ORACLE将整个包调⼊内存，当再次访问包内元素时，ORACLE直接从内存中读取，⽽不需要进⾏磁盘I/O操作，从⽽使程序执⾏效率得到提⾼。
⼆、包结构⼀个包由两个分开的部分组成：包规范和包体2.1包定义（PACKAGE）（1）包定义（PACKAGE）：包定义部分是为应⽤程序的接⼝，声明包内数据类型、变量、常量、游标、⼦程序和异常错误处理等元素，这些元素为包的公有元素。
CREATE [OR REPLACE] PACKAGE package_name{IS | AS}[公有数据类型定义][公有游标声明][公有变量、常量声明][公有⼦程序声明]END [package_name];2.2包主体（PACKAGE BODY）包主体（PACKAGE BODY）：包主体则是包定义部分的具体实现，它定义了包定义部分所声明的游标和⼦程序，在包主体中还可以声明包的私有元素。
Oracle创建存储过程、创建函数、创建包⼀、Oracle创建存储过程1、基本语法create or replace procedure update_emp_sal(Name in out type,Name in out type, ...) isbeginend update_emp_sal;2、写⼀个简单的例⼦修改emp表的ename字段create or replace procedure update_emp(v_empno varchar2,v_ename varchar2) isbeginupdate emp set ename=v_ename where empno=v_empno;end update_emp;调⽤⽅法如下：SQL>exec update_emp('7935','test');2、有返回值的存储过程就写⼀个简单的返回empno=7935的sal值create or replace procedure emp_out_sal(v_empno in varchar2,v_sal out number) isvsal number(7,2);beginselect sal into vsal from emp where empno=v_empno;v_sal:=vsal;end;调⽤有返回值的过程SQL>var vsal numberSQL>exec emp_out_sal('7935',:vsal);PL/SQL procedure successfully completedvsal---------700SQL>var vsal numberSQL> call emp_out_sal('7935',:vsal);Method calledvsal---------700⼆、Oracle创建函数（function）1、基本语法规则如下：create or replace function (Name in type, Name in type, ...) return number isResult number;beginreturn (Result);end ;2、写⼀个简单的查询例⼦查询出empno=7935的sal值create or replace function ret_emp_sal(v_ename varchar2)return numberisv_sal number(7,2);beginselect nvl(sal,0) into v_sal from emp where lower(ename)=lower(v_ename);return v_sal;end;调⽤此函数：SQL>var vsla numberSQL> call ret_emp_sal('7935') into :vsal;Method calledvsal---------700三、Oracle创建包包⽤于组合逻辑相关的过程和函数，它由包规范和包体两个部分组成。
深⼊理解Oracle中的随机函数--Oracle中取随机值的函数1.dbms_random包dbms_random包提供了⼀系列的随机值获取函数以及相关存储过程。
下⾯详细讲解常⽤的函数和过程。
(1)dbms_random.random: --返回[-2^31, 2^31)范围内的整数随机值(2)dbms_random.value: --返回[0,1)精度最多为38 位的数字(返回⼩数)dbms_random.value (low in number,high in number): 返回为low<=result<high(3)dbms_radnom.string(opt char,len number): 返回随机字符串opt 是下列字符中的⼀个（长度只能是1）：'u', 'U' – 返回字符串是⼤写希腊字母'l', 'L' – 返回字符串是⼩写希腊字母'a', 'A' – 返回字符串混合⼤⼩写希腊字母'x', 'X' – 返回字符串混合⼤写希腊字母和数字'p', 'P' –返回字符串是任何可打印字符串其他字母返回的是⼤写希腊字母2.sys_guid函数: 返回32 位的16 进制表⽰，raw类型SQL>select sys_guid() from dual;SYS_GUID()--------------------------------D0C3CDC75677446FAF9EC1C5C26A0236对于raw类型,可以使⽤utl_raw包处理。
SQL>select utl_raw.cast_to_number(sys_guid()) from dual;UTL_RAW.CAST_TO_NUMBER(SYS_GUI------------------------------1.79662473137317E223.sample和sample block(1)sample 按⾏的百分⽐来读,对于⼤表⽐较快sample 就是读全部，不加⼀样sample(n),--n为[0.000001,100）当然读的也不是严格的百分⽐，有⼀定的误差。
Oracle之UTL_FILE包⽤法详解UTL_FILE包可以⽤来读写操作系统上的⽂本⽂件，UTL_FILE提供了在客户端（FORM等等）和服务器端的⽂件访问功能。
创建测试⽬录：新建⼀个command window；创建⽬录：（以system⽤户登录数据库）Sql代码收藏代码SQL> create or replace directory cux_log_dir as '/home/appltest/debug';Directory created赋权限。
Sql代码收藏代码SQL> grant read, write on directory cux_log_dir to public;Grant succeeded检查⽬录是否成功创建Sql代码收藏代码select * FROM all_directories dir WHERE dir.DIRECTORY_NAME = 'CUX_LOG_DIR';Ps:视图all_directories存放着我们能否访问的⽬录对象。
如果要删除⽬录，也需⽤system⽤户登录数据库，执⾏如下命令：Drop directory cux_log_dir;过程和函数：FOPEN描述：打开⼀个⽂件，基本上在我们对⽂件进⾏读写动作之前都需要先执⾏这个function来打开⽂件先。
语法：UTL_FILE.FOPEN ( location IN VARCHAR2,filename IN VARCHAR2,open_mode IN VARCHAR2,max_linesize IN BINARY_INTEGER DEFAULT 1024) RETURN FILE_TYPE;参数：location 略。
Filename 略。
open_mode 指明⽂件打开的模式。
有如下⼏种：■r –只读（⽂本）■ w – 只写（本⽂）■ a – 追加（⽂本）■ rb – 只读（字节）■ wb – 只写（字节）■ ab – 追加（字节）(注：当使⽤模式：a或者ab的时候，如果⽂件不存在，则会以write模式创建此⽂件)max_linesize 指定⽂件⽂本每⼀⾏存放的最⼤字符数。
oracle package语法Oracle中的包（Package）是一种数据库对象，允许将相关的过程、函数、变量和常量组织在一起，以提供更好的封装和可维护性。
以下是Oracle包的基本语法：1.创建包：创建包需要两个部分，包头（Package Header）和包体（Package Body）。
包头定义了包中可见的公共接口，包体包含了实际的过程和函数的实现。
```sqlCREATE OR REPLACE PACKAGE package_name IS--包头部分PROCEDURE procedure_name;FUNCTION function_name RETURN data_type;CONSTANT constant_name CONSTANT data_type:=value;END package_name;CREATE OR REPLACE PACKAGE BODY package_name IS--包体部分PROCEDURE procedure_name ISBEGIN--过程实现END procedure_name;FUNCTION function_name RETURN data_type ISBEGIN--函数实现END function_name;END package_name;```2.调用包中的过程和函数：包中的过程和函数可以通过包名来调用。
```sqlBEGINpackage_name.procedure_name;variable:=package_name.function_name;END;```3.访问包中的常量：包中的常量可以通过包名和常量名直接访问。
```sqlvariable:=package_name.constant_name;```4.包的替代和删除：可以使用`CREATE OR REPLACE PACKAGE`来替代已存在的包，使用`DROP PACKAGE`来删除包。
1、包头
程序包由两个部分组成：包头与包体。
包头又可以称为包规范，用于规定程序包中可以容纳的存储过程、自定义函数、游标等PL/SQL元素。
包体则用于编写包头中规定的PL/SQL块元素实现代码。
创建包时，包体的名称必须与包头的名称相同，这样Oracle就可以通过相同的名称将包头与包体组织到一起。
创建包
可以使用OR REPLACE关键字将原先的定义覆盖。
（2）pl_statement:用于规定包中存放的内容，如果是PL/SQL块子程序，这是声明该子程序
在包头中可以声明变量，游标，子程序等内容，下面通过一个示例来演示包头的定义，示例说明：创建一个名为pack_emp的包头，分别在包头中创建一个根据员工编号获取员工姓名的函数与根据职位和部门编号获取员工信息的存储过
2、包体
程序包中的包体主要用于实现包头中声明的PL/SQL块元素，只有包头编译成功后，包体才能编译。
在包头中声明的对象可以直接在包体中使用，包体的名称必须与包头相同。
此外包体中实现的存储过程、自定义函数等子程序必须要与
可以使用OR REPLACE关键字将原先的定义覆盖。
（2）pl_body：包体中的主体代码，用于实现包头中定义的存储过程、自定义函数等PL/SQL块子程序。
包头与包体一定要一一对应，并且先要有包头，才能有包体。
下面通过一个示例来演示包体的创建，示例说明：创建pack_emp的包体，实现包中的存储过程与自定义函数。
3、调用包里面的内容。
oracle哈希函数Oracle哈希函数是Oracle数据库中常用的一种函数，它可以将输入的数据转化为固定长度的哈希值。
哈希函数的主要作用是将输入的数据压缩成较短的字符串，以便于存储和比较。
本文将介绍Oracle哈希函数的基本原理、常用的哈希函数以及其在数据库中的应用。
一、Oracle哈希函数的原理哈希函数是一种将任意长度的输入转化为固定长度输出的算法。
在Oracle数据库中，哈希函数主要用于数据的存储和比较。
当我们需要在数据库中存储大量数据时，使用哈希函数可以将数据转化为一个固定长度的哈希值，从而节省存储空间。
同时，哈希函数还可以将数据的比较转化为哈希值的比较，提高比较的效率。
二、常用的Oracle哈希函数1. MD5哈希函数：MD5是一种常用的哈希函数，它可以将输入数据转化为128位的哈希值。
在Oracle数据库中，可以使用DBMS_CRYPTO包中的MD5哈希函数进行计算。
例如，可以使用以下语句计算字符串"Hello World"的MD5哈希值：SELECT DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW('Hello World'),2) FROM DUAL;2. SHA-1哈希函数：SHA-1是一种安全性较高的哈希函数，它可以将输入数据转化为160位的哈希值。
在Oracle数据库中，可以使用DBMS_CRYPTO包中的SHA-1哈希函数进行计算。
例如，可以使用以下语句计算字符串"Hello World"的SHA-1哈希值：SELECT DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW('Hello World'),3) FROM DUAL;3. SHA-256哈希函数：SHA-256是一种更安全的哈希函数，它可以将输入数据转化为256位的哈希值。
在Oracle数据库中，可以使用DBMS_CRYPTO包中的SHA-256哈希函数进行计算。
* SQL常用函数：数值函数：* ABSPurpose 返回绝对值Returns the absolute value of n.ExampleSELECT ABS(-15) "Absolute" FROM DUAL;Absolute----------15* CEILPurpose 取最小整数Returns smallest integer greater than or equal to n.ExampleSELECT CEIL(15.7) "Ceiling" FROM DUAL;Ceiling----------16* MODSyntaxMOD(m,n)Purpose 取余Returns remainder of m divided by n. Returns m if n is 0.ExampleSELECT MOD(11,4) "Modulus" FROM DUAL;Modulus----------3* ROUNDSyntaxROUND(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 1SELECT ROUND(15.193,1) "Round" FROM DUAL;Round----------15.2Example 2SELECT ROUND(15.193,-1) "Round" FROM DUAL;Round----------20* TRUNCPurpose 取截取后的信息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.ExamplesSELECT TRUNC(15.79,1) "Truncate" FROM DUAL;Truncate----------15.7SELECT TRUNC(15.79,-1) "Truncate" FROM DUAL;Truncate----------10字符函数：* CONCATSyntaxCONCAT(char1, char2)Purpose 合并字符串，相当于“||”Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (||). For information on this operator, see "Concatenation Operator".ExampleThis example uses nesting to concatenate three character strings: SELECT CONCAT( CONCAT(ename, ' is a '), job) "Job"FROM empWHERE empno = 7900;Job-----------------JAMES is a CLERK* LOWERPurpose 变为小写Returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2).ExampleSELECT LOWER('MR. SCOTT MCMILLAN') "Lowercase"FROM DUAL;Lowercase--------------------mr. scott mcmillan* LPADPurpose 左填充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.ExampleSELECT LPAD('Page 1',15,'*.') "LPAD example"FROM DUAL;LPAD example---------------*.*.*.*.*Page 1* LTRIMSyntaxLTRIM(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.ExampleSELECT LTRIM('xyxXxyLAST WORD','xyXLA') "LTRIM example"FROM DUAL;LTRIM exampl------------ST WORD* REPLACESyntaxREPLACE(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.ExampleSELECT REPLACE('JACK and JUE','J','BL') "Changes"FROM DUAL;Changes--------------BLACK and BLUE* RPADSyntaxRPAD(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.ExampleSELECT RPAD('MORRISON',12,'ab') "RPAD example"FROM DUAL;RPAD example-----------------MORRISONabab* RTRIMSyntaxRTRIM(char [,set]PurposeReturns char, with all the rightmost characters that appear in set removed; set defaults to a single blank. RTRIM works similarly to LTRIM. ExampleSELECT RTRIM('BROWNINGyxXxy','xy') "RTRIM e.g."FROM DUAL;RTRIM e.g-------------BROWNINGyxX* SUBSTRSyntaxSUBSTR(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, Oraclecounts 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 1SELECT SUBSTR('ABCDEFG',3,4) "Subs"FROM DUAL;Subs----CDEFExample 2SELECT SUBSTR('ABCDEFG',-5,4) "Subs"FROM DUAL;Subs----CDEF* TRANSLATESyntaxTRANSLATE(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 1The 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--------9XXX999Example 2The 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* UPPERSyntaxUPPER(char)Purpose 大写Returns char, with all letters uppercase. The return value has the same datatype as the argument char.ExampleSELECT UPPER('Large') "Uppercase"FROM DUAL;Upper-----LARGE* ASCIISyntaxASCII(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.ExampleSELECT ASCII('Q')FROM DUAL;ASCII('Q')----------81* INSTRSyntaxINSTR (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 1SELECT INSTR('CORPORATE FLOOR','OR', 3, 2)"Instring" FROM DUAL;Instring----------14Example 2SELECT INSTR('CORPORATE FLOOR','OR', -3, 2)"Reversed Instring"FROM DUAL;Reversed Instring-----------------2* LENGTHSyntaxLENGTH(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.ExampleSELECT LENGTH('CANDIDE') "Length in characters"FROM DUAL;Length in characters--------------------7* ADD_MONTHSSyntaxADD_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. ExampleSELECT TO_CHAR(ADD_MONTHS(hiredate,1),'DD-MON-YYYY') "Next month"FROM empWHERE ename = 'SMITH';Next Month-----------17-JAN-1981* LAST_DAYSyntaxLAST_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 1SELECT SYSDATE,LAST_DAY(SYSDATE) "Last",LAST_DAY(SYSDATE) - SYSDATE "Days Left"FROM DUAL;SYSDATE Last Days Left--------- --------- ----------23-OCT-97 31-OCT-97 8Example 2SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(hiredate),5),'DD-MON-YYYY') "Five months"FROM empWHERE ename = 'MARTIN';Five months-----------28-FEB-1982* MONTHS_BETWEENSyntaxMONTHS_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.ExampleSELECT 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_DAYSyntaxNEXT_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.ExampleThis 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* SYSDATESyntaxSYSDATEPurpose 取系统日期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.ExampleSELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS')"NOW"FROM DUAL;NOW-------------------10-29-1993 20:27:11转换函数：* CHARTOROWIDSyntaxCHARTOROWID(char)Purpose 将字符串转换为ROWIDConverts a value from CHAR or VARCHAR2 datatype to ROWID datatype. ExampleSELECT ename FROM empWHERE ROWID = CHARTOROWID('AAAAfZAABAAACp8AAO');ENAME----------LEWIS* TO_CHAR, date conversionSyntaxTO_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".ExampleSELECT TO_CHAR(HIREDATE, 'Month DD, YYYY')"New date format" FROM empWHERE ename = 'BLAKE';New date format------------------May 01, 1981* TO_CHAR, number conversionSyntaxTO_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 1In 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_DATESyntaxTO_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".ExampleINSERT 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_NUMBERSyntaxTO_NUMBER(char [,fmt [, 'nlsparams'] ])PurposeConverts 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.* NVLSyntaxNVL(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.ExampleSELECT ename, NVL(TO_CHAR(COMM), 'NOTAPPLICABLE')"COMMISSION" FROM empWHERE deptno = 30;ENAME COMMISSION---------- -------------------------------------ALLEN 300WARD 500MARTIN 1400BLAKE NOT APPLICABLETURNER 0JAMES NOT APPLICABLE* UIDSyntaxUIDPurpose 系统用户的标码Returns an integer that uniquely identifies the current user.* USERSyntaxUSERPurpose 系统用户户名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.ExampleSELECT USER, UID FROM DUAL;USER UID------------------------------ ----------SCOTT 19组函数：* AVGSyntaxAVG([DISTINCT|ALL] n)Purpose 取平均值Returns average value of n.ExampleSELECT AVG(sal) "Average"FROM emp;Average----------2077.21429* COUNTSyntaxCOUNT({* | [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 1SELECT COUNT(*) "Total"FROM emp;Total----------18Example 2SELECT COUNT(job) "Count"FROM emp;Count----------14Example 3SELECT COUNT(DISTINCT job) "Jobs"FROM emp;Jobs----------5* MAXSyntaxMAX([DISTINCT|ALL] expr)Purpose 取最大值Returns maximum value of expr.ExampleSELECT MAX(sal) "Maximum" FROM emp;Maximum----------5000* MINSyntaxMIN([DISTINCT|ALL] expr)PurposeReturns minimum value of expr.ExampleSELECT MIN(hiredate) "Earliest" FROM emp;Earliest---------17-DEC-80* SUMSyntaxSUM([DISTINCT|ALL] n)Purpose 求和Returns sum of values of n.ExampleSELECT SUM(sal) "Total"FROM emp;Total----------29081* DBMS_ALERT实现数据库间的警报。
