第7转换函数.doc

第7讲 转换函数，特殊函数目的：1. 掌握Oracle转换函数的语法2. 掌握转换函数的使用授课内容：转换函数：1. 数值转为字符串：TO_CHAR(number) 无格式2. 数值转换位字符串，有格式：TO_CHAR(number, format)： 经常用于报表的数据显示，如财务，条件表要求的格式一般是带千分号，和小数点的格式。234，222，111。09数值格式说明9每个9标识一个有效位。,999,999,999 在指定的位置上，放置，号。一般用于千分号.在指定的位置上，放置. 号，用于小数点的位置00999返回前导0，而不是空格9990 返回后继0，而不是空格$返回带美元符号的数值L返回带本地货币的数值，如L999,999.99. RMBRM返回数值的大写的罗马格式rm返回数值的小写的罗马格式S返回带+,-号的格式S9999: 前面有+,-号9999S: 后面有+,-号例子：select to_char(8012.9998,$999,999,999.99) from dual$8,012.98select to_char(8012.97778,$999,999,999.99) from dual$8,012.98 （自动进行四舍五入）3. 日期转为字符串：TO_CHAR(date, format)：将给定的日期按格式转换为字符串， 格式参照to_date()的日期格式。在实际应用中，此函数是用的最多的函数，用于数据的统计。4. 字符串转为数值：TO_NUMBER(String)无格式的字符串转换位数值的函数，字符串中只能包含数字，小数点，正，负号。如：select to_number(+9222.989) from dual 5. TO_NUMBER(String, number_format)允许有格式的字符串，转换为数值。select to_number(+9222.00,S9999999.99) from dualselect to_number(RMB999,999,L999,999.00) from dual如果只写select to_number(RMB999,999) from dual由于没有格式就是非法的。6. TO_DATE(String, format)： 将字符串按格式转换为日期。7. TO_CLOB(String)： 将String 转换为CLOB的值。 8. TO_LOB(long_column)： 将long类型的列转换为LOB类型，自动判断，将long转换为CLOB, 将LONG RAW转换为BLOB.9. TO_TIMESTAMP(String, format) ： 将字符串按日期的格式转换为TIMESTAMP时间。没有指定格式，按系统当前的默认格式。select to_timestamp(2000,yyyy) from dual2000-6-1 0:00:00.000000Oracle9i内置的特殊函数10. UID : 标识登录用户的整数。 select UID from dual11. USER：返回当前用户名称的varchar2值Select USER from dual12. VSIZE(x) 返回x的字节数select vsize(comm) from emp13. NVL(x1,x2) null 处理函数如果x1是null, 则返回是x2, 否则是x1自己14. NVL2（x1,x2,x3） null处理函数如果x1不是null, 返回x2, 否则返回x3 select nvl2(comm,comm,0) from emp15. NULLIF(X1,X2) ：如果X1等于X2， 返回null, 否则返回X116. EMPTY_CLOB()返回空的字符位置指针，用于在增加新的记录时，对CLOB的字段赋初值。17. EMPTY_BLOB()返回空的二进制位置指针，用于在增加新的记录时，对BLOB的字段赋初值。18. DECODE(): 条件判断函数Decode ( X, X1, Y1, X2, Y2, Xn, Yn, Y) :如果X的值是X1, 则返回Y1, X2, 则返回Y2, Xn, 则返回Yn否则返回Y.省略Y, 则没有else值。DECODE函数的主要作用是用于统计报表。产生多栏的统计报表。练习：select round(sysdate) from dualselect months_between( min(hiredate),max(hiredate)from empselect to_clob(aaaa) from dualselect nvl(comm,0) from empselect nvl2(comm,comm,0) from empselect UID from dualselect SYS_GUID from dualselect vsize(comm) from empselect empty_clob() from dualselect empty_blob() from dualselect ename, decode(job,SALESMAN, S,O)from empselect nullif(1,2) from dualselect to_char(-999,999.99S) from dualselect to_number(RMB999,999) from dualselect to_timestamp(2000-01-10 22:20:20.85252,yyyy-mm-dd hh24:mi:ss.SSSSS) from dualSELECT product_id, product_type_id, CASE product_type_id WHEN 1 THEN Book WHEN 2 THEN Video WHEN 3 THEN DVD WHEN 4 THEN CD ELSE Magazine ENDFROM products;PRODUCT_ID PRODUCT_TYPE_ID CASEPROD- - - 1 1 Book 2 1 Book 3 2 Video 4 2 Video 5 2 Video 6 2 Video 7 3 DVD 8 3 DVD 9 4 CD 10 4 CD 11 4 CD 12 MagazineSELECT product_id, product_type_id, CASE WHEN product_type_id = 1 THEN Book WHEN product_type_id = 2 THEN Video WHEN product_type_id = 3 THEN DVD WHEN product_type_id = 4 THEN CD ELSE Magazine ENDFROM products;PRODUCT_ID PRODUCT_TYPE_ID CASEPROD- - - 1 1 Book 2 1 Book 3 2 Video 4 2 Video 5 2 Video 6 2 Video 7 3 DVD 8 3 DVD 9 4 CD 10 4 CD 11 4 CD12 Magazine13SELECT product_id, price, CASE WHEN price 15 THEN Expensive ELSE Cheap ENDFROM products;PRODUCT_ID PRICE CASEWHENP- - - 1 19.95 Expensive 2 30 Expensive 3 25.99 Expensive 4 13.95 Cheap 5 49.99 Expensive 6 14.95 Cheap 7 13.49 Cheap 8 12.99 Cheap 9 10.99 Cheap 10 15.99 Expensive 11 14.99 Cheap 12 13.49 Cheap课后作业：下节前提问：1. 查询员工表，显示员工名称，职位，加入公司的季度（一季度，二季度，三季度，四季度），没有加入公司日期，显示不知道。2. 以每月15日为准，15日之前为上半月，15日以后包括15日为下半月，显示员工的编号，名称，职位，加入公司的半月情况。3. 员工的工资以2500为界，高于2500为高工资，低于2500为低工资。等于2500为中等工资。查询员工的工资情况，显示员工姓名，工资情况。附录1：转换函数列表Table 3-3: Conversion Functions FunctionDescriptionASCIISTR(x) Converts x to an ASCII string, where x may be a string in any character set.BIN_TO_NUM(x) Converts x to a binary number. Returns a NUMBER.CAST(x AS type_name) Converts a value in x from one data type to another specified in type_name.CHARTOROWID(x) Converts x to a ROWID.COMPOSE(x) Converts x to a Unicode string in its fully normalized form in the same character set as x. Unicode uses a 2-byte character set and can represent over 65,000 characters; it may also be used to represent non-English characters.CONVERT(x, source_char_set, dest_char_set) Converts x from source_char_set to dest_char_set.DECODE(x, search, result, default) Compares x with the value in search; if equal, DECODE() returns search, otherwise the value in default is returned.DECOMPOSE(x) Converts x to a Unicode string after decomposition in the same character set as x.HEXTORAW(x) Converts the character x containing hexadecimal digits (base 16) to a binary number (RAW). This function returns the returns RAW number.NUMTODSINTERVAL(x) Converts the number x to an INTERVAL DAY TO SECOND. Youll learn about date and time intervalrelated functions in the next chapter.NUMTOYMINTERVAL(x) Convert the number x to an INTERVAL YEAR TO MONTH.RAWTOHEX(x) Converts the binary number (RAW) x to a VARCHAR2 character containing the equivalent hexadecimal number.RAWTONHEX(x) Converts the binary number (RAW) x to an NVARCHAR2 character containing the equivalent hexadecimal number. An NVARCHAR2 is used to store strings in the national character set.ROWIDTOCHAR(x) Converts the ROWID x to a VARCHAR2 character.ROWIDTONCHAR(x) Converts the ROWID x to an NVARCHAR2 character.TO_BINARY_DOUBLE(x) New for Oracle Database 10g. Converts x to a BINARY_DOUBLE.TO_BINARY_FLOAT(x) New for Oracle Database 10g. Converts x to a BINARY_FLOAT.TO_CHAR(x , format) Converts x to a VARCHAR2 string. You can supply an optional format that indicates the format of x.TO_CLOB(x) Converts x to a character large object (CLOB). A CLOB is used to store large amounts of character data.TO_DATE(x , format) Converts x to a DATE.TO_DSINTERVAL(x) Convert the string x to an INTERVAL DAY TO SECOND.TO_MULTI_BYTE(x) Converts the single-byte characters in x to their corresponding multi-byte characters. The return type is the same as the type for x.TO_NCHAR(x) Converts x in the database character set to an NVARCHAR2.TO_NCLOB(x) Converts x to an NCLOB. An NCLOB is used to store large amounts of national language character data.TO_NUMBER(x , format) Converts x to a NUMBER.TO_SINGLE_BYTE(x) Converts the multi-byte characters in x to their corresponding single-byte characters. The return type is the same as the type for x.TO_TIMESTAMP(x) Converts the string x to a TIMESTAMP.TO_TIMESTAMP_TZ(x) Converts the string x to a TIMESTAMP WITH TIME ZONE.TO_YMINTERVAL(x) Converts the string x to an INTERVAL YEAR TO MONTH.TRANSLATE(x, from_string, to_string) Converts all occurrences of from_string in x to to_string.UNISTR(x) Converts the characters in x to the national language character set (NCHAR).附录2：数值转换为字符的格式 to_char(x, format)Table 3-4: Format Parameters ParameterFormat ExamplesDescription9 999 Returns digits in specified positions with leading negative sign if the number is negative.0 0999 9990 0999: Returns a number with leading zeros.9990: Returns a number with trailing zeros. 999.99 Returns a decimal point in the specified position., 9,999 Returns a comma in the specified position.$ $999 Returns a leading dollar sign.B B9.99 If the integer part of a fixed point number is zero, returns spaces for the zeros.C C999 Returns the ISO currency symbol in the specified position. The symbol comes from the NLS_ISO_CURRENCY parameter.D 9D99 Returns the decimal point symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter (default is a period character).EEEE 9.99EEEE Returns number using the scientific notation.FM FM90.9 Removes leading and trailing spaces from number.G 9G999 Returns the group separator symbol in the specified position. The symbol comes from the NLS_NUMERIC_CHARACTER parameter.L L999 Returns the local currency symbol in the specified position. The symbol comes from the NLS_CURRENCY parameter.MI 999MI Returns a negative number with a trailing minus sign. Returns a positive number with a trailing space.PR 999PR Returns a negative number in angle brackets (). Returns a positive number with leading and trailing spaces.RN rn RN rn Returns number as Roman numerals. RN returns uppercase numerals; rn returns lowercase numerals. Number must be an integer between 1 and 3999.S S999 999S S999: Returns a negative number with a leading negative sign; returns a positive number with a leading positive sign.999S: Returns a negative number with a trailing negative sign; returns a positive number with a trailing positive sign.TM TM Returns a number using the minimum number of characters. Default is TM9, which returns the number using fixed notation unless the number of characters is greater than 64. If greater than 64, the number is returned using scientific notation.U U999 Returns the dual currency symbol (Euro, for example) in the specified position. The symbol comes from the NLS_DUAL_CURRENCY parameter.V 99V99 Returns number multiplied by 10x where x is the number of 9 characters after the V. If necessary, the number is rounded.X XXXX Returns the number in hexadecimal. If the number is not an integer, the number is rounded to an integer.附录3：模式匹配符 （Oracle10g 支持）Table 3-5: Regular Expression Metacharacters MetacharactersMeaningExamples Indicates that the match character is a special character, a literal, or a backreference. (A backreference repeats the previous match.)n matches the newline character matches ( matches ( ) matches ) Matches the position at the start of the string.A matches A if A is the first character in the string.$ Matches the position at the end of the string.$B matches B if B is the last character in the string.* Matches the preceding character zero or more times.ba*rk matches brk, bark, baark, and so on.+ Matches the preceding character one or more times.ba+rk matches bark, baark, and so on, but not brk.? Matches the preceding character zero or one time.ba?rk matches brk and bark only.n Matches a character exactly n times, where n is an integer.hob2it matches hobbit.n,m Matches a character at least n times and at most m times, where n and m are both integers.hob2,3it matches hobbit and hobbbit only. Matches any single character except null.hob.it matches hobait, hobbit, and so on.(pattern) A subexpression that matches the specified pattern. You use subexpressions to build up complex regular expressions. You can access the individual matches, known as captures, from this type of subexpression.anatom(y|ies) matches anatomy and anatomies.x|y Matches x or y, where x and y are one or more characters.war|peace matches war or peace.abc Matches any of the enclosed cha