oracle data Format Models---二(轉)

zhouwf0726發表於2019-03-18
OracleORM

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 ElementAdditional 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

AttributeDatatypePurpose

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章