Movatterモバイル変換

Syntax

Purpose

CAST lets you convert built-in data types or collection-typed values of one type into another built-in data type or collection type. You can cast an unnamed operand (such as a date or the result set of a subquery) or a named collection (such as a varray or a nested table) into a type-compatible data type or named collection. Thetype_name must be the name of a built-in data type or collection type and the operand must be a built-in data type or must evaluate to a collection value.

For the operand,expr can be either a built-in data type, a collection type, or an instance of anANYDATA type. Ifexpr is an instance of anANYDATA type, thenCAST tries to extract the value of theANYDATA instance and return it if it matches the cast target type, otherwise, null will be returned.MULTISET informs Oracle Database to take the result set of the subquery and return a collection value.Table 7-1 shows which built-in data types can be cast into which other built-in data types. (CAST does not supportLONG,LONGRAW, or the Oracle-supplied types.)

CAST does not directly support any of the LOB data types. When you useCAST to convert aCLOB value into a character data type or aBLOB value into theRAW data type, the database implicitly converts the LOB value to character or raw data and then explicitly casts the resulting value into the target data type. If the resulting value is larger than the target type, then the database returns an error.

When you useCAST ...MULTISET to get a collection value, each select list item in the query passed to theCAST function is converted to the corresponding attribute type of the target collection element type.

Table 7-1 Casting Built-In Data Types

Note 1: Datetime/interval includesDATE,TIMESTAMP,TIMESTAMPWITHTIMEZONE,TIMESTAMPWITHLOCALTIMEZONE,INTERVALDAYTOSECOND, andINTERVALYEARTOMONTH.

Note 2: You cannot cast aUROWID to aROWID if theUROWID contains the value of aROWID of an index-organized table.

Note 3: You can specify theDEFAULTreturn_valueONCONVERSIONERROR clause for this type of conversion. You can specify thefmt andnlsparam clauses for this type of conversion with the following exceptions: you cannot specify fmt when converting toINTERVALDAYTOSECOND, and you cannot specifyfmt ornlsparam when converting toINTERVALYEARTOMONTH.

If you want to cast a named collection type into another named collection type, then the elements of both collections must be of the same type.

MULTISET

If the result set ofsubquery can evaluate to multiple rows, then you must specify theMULTISET keyword. The rows resulting from the subquery form the elements of the collection value into which they are cast. Without theMULTISET keyword, the subquery is treated as a scalar subquery.

Restriction on MULTISET

If you specify theMULTISET keyword, then you cannot specify theDEFAULTreturn_valueONCONVERSIONERROR,fmt, ornlsparam clauses.

DEFAULTreturn_value ON CONVERSION ERROR

This clause allows you to specify the value returned by this function if an error occurs while convertingexpr totype_name. This clause has no effect if an error occurs while evaluatingexpr.

This clause is valid ifexpr evaluates to a character string of typeCHAR,VARCHAR2,NCHAR, orNVARCHAR2, andtype_name isBINARY_DOUBLE,BINARY_FLOAT,DATE,INTERVALDAYTOSECOND,INTERVALYEARTOMONTH,NUMBER,TIMESTAMP,TIMESTAMPWITHTIMEZONE, orTIMESTAMPWITHLOCALTIMEZONE.

Thereturn_value can be a string literal, null, constant expression, or a bind variable, and must evaluate to null or a character string of typeCHAR,VARCHAR2,NCHAR, orNVARCHAR2. Ifreturn_value cannot be converted totype_name, then the function returns an error.

fmt andnlsparam

Thefmt argument lets you specify a format model and thenlsparam argument lets you specify NLS parameters. If you specify these arguments, then they are applied when convertingexpr andreturn_value, if specified, totype_name.

You can specifyfmt andnlsparam iftype_name is one of the following data types:

• BINARY_DOUBLE

  If you specifyBINARY_DOUBLE, then the optionalfmt andnlsparam arguments serve the same purpose as for theTO_BINARY_DOUBLE function. Refer toTO_BINARY_DOUBLE for more information.

• BINARY_FLOAT

  If you specifyBINARY_FLOAT, then the optionalfmt andnlsparam arguments serve the same purpose as for theTO_BINARY_FLOAT function. Refer toTO_BINARY_FLOAT for more information.

• DATE

  If you specifyDATE, then the optionalfmt andnlsparam arguments serve the same purpose as for theTO_DATE function. Refer toTO_DATE for more information.

• NUMBER

  If you specifyNUMBER, then the optionalfmt andnlsparam arguments serve the same purpose as for theTO_NUMBER function. Refer toTO_NUMBER for more information.

• TIMESTAMP

  If you specifyTIMESTAMP, then the optionalfmt andnlsparam arguments serve the same purpose as for theTO_TIMESTAMP function. If you omitfmt, thenexpr must be in the default format of theTIMESTAMP data type, which is determined explicitly by theNLS_TIMESTAMP_FORMAT parameter or implicitly by theNLS_TERRITORY parameter. Refer toTO_TIMESTAMP for more information.

• TIMESTAMPWITHTIMEZONE

  If you specifyTIMESTAMPWITHTIMEZONE, then the optionalfmt andnlsparam arguments serve the same purpose as for theTO_TIMESTAMP_TZ function. If you omitfmt, thenexpr must be in the default format of theTIMESTAMPWITHTIMEZONE data type, which is determined explicitly by theNLS_TIMESTAMP_TZ_FORMAT parameter or implicitly by theNLS_TERRITORY parameter. Refer toTO_TIMESTAMP_TZ for more information.

• TIMESTAMPWITHLOCALTIMEZONE

  If you specifyTIMESTAMPWITHLOCALTIMEZONE then the optionalfmt andnlsparam arguments serve the same purpose as for theTO_TIMESTAMP function. If you omitfmt, thenexpr must be in the default format of theTIMESTAMP data type, , which is determined explicitly by theNLS_TIMESTAMP_FORMAT parameter or implicitly by theNLS_TERRITORY parameter. Refer toTO_TIMESTAMP for more information.

Built-In Data Type Examples

The following examples use theCAST function with scalar data types. The first example converts text to a timestamp value by applying the format model provided in the session parameterNLS_TIMESTAMP_FORMAT. If you want to avoid dependency on this NLS parameter, then you can use theTO_DATE as shown in the second example.

SELECT CAST('22-OCT-1997'       AS TIMESTAMP WITH LOCAL TIME ZONE)   FROM DUAL;SELECT CAST(TO_DATE('22-Oct-1997', 'DD-Mon-YYYY')       AS TIMESTAMP WITH LOCAL TIME ZONE)  FROM DUAL;

In the preceding example,TO_DATE converts from text toDATE, andCAST converts fromDATE toTIMESTAMPWITHLOCALTIMEZONE, interpreting the date in the session time zone (SESSIONTIMEZONE).

SELECT product_id, CAST(ad_sourcetext AS VARCHAR2(30)) text  FROM print_media  ORDER BY product_id;

The following examples return a default value if an error occurs while converting the specified value to the specified data type. In these examples, the conversions occurs without error.

SELECT CAST(200       AS NUMBER       DEFAULT 0 ON CONVERSION ERROR)  FROM DUAL;

SELECT CAST('January 15, 1989, 11:00 A.M.'       AS DATE       DEFAULT NULL ON CONVERSION ERROR,       'Month dd, YYYY, HH:MI A.M.')  FROM DUAL;

SELECT CAST('1999-12-01 11:00:00 -8:00'       AS TIMESTAMP WITH TIME ZONE       DEFAULT '2000-01-01 01:00:00 -8:00' ON CONVERSION ERROR,       'YYYY-MM-DD HH:MI:SS TZH:TZM',       'NLS_DATE_LANGUAGE = American')  FROM DUAL;

In the following example, an error occurs while converting'N/A' to aNUMBER value. Therefore, theCAST function returns the default value of0.

SELECT CAST('N/A'       AS NUMBER       DEFAULT '0' ON CONVERSION ERROR)  FROM DUAL;

Collection Examples

TheCAST examples that follow build on thecust_address_typ found in the sample order entry schema,oe.

CREATE TYPE address_book_t AS TABLE OF cust_address_typ;/CREATE TYPE address_array_t AS VARRAY(3) OF cust_address_typ;/CREATE TABLE cust_address (  custno            NUMBER,   street_address    VARCHAR2(40),   postal_code       VARCHAR2(10),   city              VARCHAR2(30),  state_province    VARCHAR2(10),   country_id        CHAR(2));CREATE TABLE cust_short (custno NUMBER, name VARCHAR2(31));CREATE TABLE states (state_id NUMBER, addresses address_array_t);

This example casts a subquery:

SELECT s.custno, s.name,       CAST(MULTISET(SELECT ca.street_address,                               ca.postal_code,                             ca.city,                             ca.state_province,                             ca.country_id                       FROM cust_address ca                       WHERE s.custno = ca.custno)       AS address_book_t)  FROM cust_short s  ORDER BY s.custno;

CAST converts a varray type column into a nested table:

SELECT CAST(s.addresses AS address_book_t)  FROM states s   WHERE s.state_id = 111;

The following objects create the basis of the example that follows:

CREATE TABLE projects   (employee_id NUMBER, project_name VARCHAR2(10));CREATE TABLE emps_short   (employee_id NUMBER, last_name VARCHAR2(10));CREATE TYPE project_table_typ AS TABLE OF VARCHAR2(10);/

The following example of aMULTISET expression uses these objects:

SELECT e.last_name,       CAST(MULTISET(SELECT p.project_name                       FROM projects p                        WHERE p.employee_id = e.employee_id                       ORDER BY p.project_name)       AS project_table_typ)  FROM emps_short e  ORDER BY e.last_name;