oracle data Format Models---二(轉)
Table 2-17 shows whether the following statement meets the matching conditions for different values of char and 'fmt' using FX (the table named table has a column date_column of datatype DATE):
UPDATE table SET date_column = TO_DATE(char, 'fmt');
Table 2-17 Matching Character Data and Format Models with the FX Format Model Modifier
char | 'fmt' | Match or Error? |
---|---|---|
'15/ JAN /1998' | 'DD-MON-YYYY' | Match |
' 15! JAN % /1998' | 'DD-MON-YYYY' | Error |
'15/JAN/1998' | 'FXDD-MON-YYYY' | Error |
'15-JAN-1998' | 'FXDD-MON-YYYY' | Match |
'1-JAN-1998' | 'FXDD-MON-YYYY' | Error |
'01-JAN-1998' | 'FXDD-MON-YYYY' | Match |
'1-JAN-1998' | 'FXFMDD-MON-YYYY' | Match |
Format of Return Values: Examples You can use a format model to specify the format for Oracle to use to return values from the database to you.
The following statement selects the salaries of the employees in Department 80 and uses the TO_CHAR function to convert these salaries into character values with the format specified by the number format model '$99,990.99'
SELECT last_name employee, TO_CHAR(salary, '$99,990.99') FROM employees WHERE department_id = 80;
Because of this format model, Oracle returns salaries with leading dollar signs, commas every three digits, and two decimal places.
The following statement selects the date on which each employee from Department 20 was hired and uses the TO_CHAR function to convert these dates to character strings with the format specified by the date format model 'fmMonth DD, YYYY':
SELECT last_name employee, TO_CHAR(hire_date,'fmMonth DD, YYYY') hiredate FROM employees WHERE department_id = 20;
With this format model, Oracle returns the hire dates without blank padding (as specified by fm), two digits for the day, and the century included in the year.
See Also: "Format Model Modifiers" for a description of the fm format element |
Supplying the Correct Format Model: Examples When you insert or update a column value, the datatype of the value that you specify must correspond to the column datatype of the column. You can use format models to specify the format of a value that you are converting from one datatype to another datatype required for a column.
For example, a value that you insert into a DATE column must be a value of the DATE datatype or a character string in the default date format (Oracle implicitly converts character strings in the default date format to the DATE datatype). If the value is in another format, then you must use the TO_DATE function to convert the value to the DATE datatype. You must also use a format model to specify the format of the character string.
The following statement updates Hunold's hire date using the TO_DATE function with the format mask 'YYYY MM DD' to convert the character string '1998 05 20' to a DATE value:
UPDATE employees SET hire_date = TO_DATE('1998 05 20','YYYY MM DD') WHERE last_name = 'Hunold';
String-to-Date Conversion Rules
The following additional formatting rules apply when converting string values to date values (unless you have used the FX or FXFM modifiers in the format model to control exact format checking):
You can omit punctuation included in the format string from the date string if all the digits of the numerical format elements, including leading zeros, are specified. In other words, specify 02 and not 2 for two-digit format elements such as MM, DD, and YY.
You can omit time fields found at the end of a format string from the date string.
If a match fails between a datetime format element and the corresponding characters in the date string, then Oracle attempts alternative format elements, as shown in Table 2-18.
Table 2-18 Oracle Format Matching
Original Format Element | Additional Format Elements to Try in Place of the Original |
---|---|
'MM' | 'MON' and 'MONTH' |
'MON | 'MONTH' |
'MONTH' | 'MON' |
'YY' | 'YYYY' |
'RR' | 'RRRR' |
XML Format Model
The SYS_XMLGEN function returns an instance of type XMLType containing an XML document. Oracle provides the XMLFormat object, which lets you format the output of the SYS_XMLGEN function.
Table 2-19 lists and describes the attributes of the XMLFormat object. The function that implements this type follows the table.
See Also:
|
Table 2-19 Attributes of the XMLFormat Object
Attribute | Datatype | Purpose |
---|---|---|
enclTag | VARCHAR2(100) | The name of the enclosing tag for the result of the SYS_XMLGEN function. If the input to the function is a column name, the default is the column name. Otherwise the default is ROW. When schemaType is set to USE_GIVEN_SCHEMA, this attribute also gives the name of the XMLSchema element. |
schemaType | VARCHAR2(100) | The type of schema generation for the output document. Valid values are 'NO_SCHEMA' and 'USE_GIVEN_SCHEMA'. The default is 'NO_SCHEMA'. |
schemaName | VARCHAR2(4000) | The name of the target schema Oracle uses if the value of the schemaType is 'USE_GIVEN_SCHEMA'. If you specify schemaName, then Oracle uses the enclosing tag as the element name. |
targetNameSpace | VARCHAR2(4000) | The target namespace if the schema is specified (that is, schemaType is GEN_SCHEMA_*, or USE_GIVEN_SCHEMA) |
dburl | VARCHAR2(2000) | The URL to the database to use if WITH_SCHEMA is specified. If this attribute is not specified, then Oracle declares the URL to the types as a relative URL reference. |
processingIns | VARCHAR2(4000) | User-provided processing instructions, which are appended to the top of the function output before the element. |
The function that implements the XMLFormat object follows:
STATIC FUNCTION createFormat( enclTag IN varchar2 := 'ROWSET', schemaType IN varchar2 := 'NO_SCHEMA', schemaName IN varchar2 := null, targetNameSpace IN varchar2 := null, dburlPrefix IN varchar2 := null, processingIns IN varchar2 := null) RETURN XMLGenFormatType deterministic parallel_enable, MEMBER PROCEDURE genSchema (spec IN varchar2), MEMBER PROCEDURE setSchemaName(schemaName IN varchar2), MEMBER PROCEDURE setTargetNameSpace(targetNameSpace IN varchar2), MEMBER PROCEDURE setEnclosingElementName(enclTag IN varchar2), MEMBER PROCEDURE setDbUrlPrefix(prefix IN varchar2), MEMBER PROCEDURE setProcessingIns(pi IN varchar2), CONSTRUCTOR FUNCTION XMLGenFormatType ( enclTag IN varchar2 := 'ROWSET', schemaType IN varchar2 := 'NO_SCHEMA', schemaName IN varchar2 := null, targetNameSpace IN varchar2 := null, dbUrlPrefix IN varchar2 := null, processingIns IN varchar2 := null) RETURN SELF AS RESULT deterministic parallel_enable . . . http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34510
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/756652/viewspace-242191/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- OAF export data from VO in xlsx formatExportORM
- TN002: Persistent Object Data Format(永久物件的資料格式) (轉)ObjectORM物件
- Oracle 的 data block研讀(二)OracleBloC
- Oracle Transparent Data Encryption 透明加密(二)Oracle加密
- oracle工具 awr formatOracleORM
- Mysql date_format 與 Oracle to_char(date,’format’)MySqlORMOracle
- (轉)修改oracle預設日期格式 nls_date_formatOracleORM
- [Data Guard]Oracle10g Data Guard學習筆記(二)Oracle筆記
- oracle毛病(二) (轉)Oracle
- ORACLE 11G Data Guard 角色轉換Oracle
- Oracle data guard常用維護操作命令(轉)Oracle
- [轉載]Oracle等待事件Data file init writeOracle事件
- Oracle Data Guard Feature 12cR2系列(二)Oracle
- Oracle NLS_TIMESTAMP_FORMAT引數OracleORM
- Overview of Data Blocks(二)ViewBloC
- 【ASK_ORACLE】Oracle Data Guard(二)物理備庫的概念和優勢Oracle
- Oracle Data BufferOracle
- oracle data guard!!Oracle
- oracle data pumpOracle
- ORACLE備份策略二(轉)Oracle
- ORACLE碎片整理二(轉載)Oracle
- [轉載]ORA-01578: ORACLE data block corruptedOracleBloC
- oracle 引數LOG_ARCHIVE_FORMAT 的格式OracleHiveORM
- 初探data pump export (二)Export
- linux解壓報錯gzip: stdin: invalid compressed data--format violatedLinuxORM
- Format and un-format money/currency in JavaScriptORMJavaScript
- Oracle Flashback Data ArchiveOracleHive
- Oracle Data Guard配置Oracle
- c#訪問oracle幾種方法(轉載)_data accessC#Oracle
- oracle資料型別data type與儲存空間大小(二)Oracle資料型別
- oracle全文索引之FILTER_3_FORMAT_COLUMNOracle索引FilterORM
- 【轉】Python格式化字串str.format()Python字串ORM
- Linux和DOS的FORMAT命令區別(轉)LinuxORM
- Data Science | Numpy基礎(二)
- 在 RHEL3 上配置 Oracle 10g Data Guard(轉)Oracle 10g
- Data Guard Broker系列之二:Data Guard Broker配置實戰
- 介紹ORACLE DATA GUARD——DATA GUARD概念和管理Oracle
- Oracle 10G的log_archive_format引數Oracle 10gHiveORM