oracle cast function

cast函数

In Oracle/PLSQL, the cast function converts one datatype to another.

The syntax for the cast function is:

cast ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

The following casts are allowed:

TO	FROM
	char, varchar2	number	datetime / interval	raw	rowid, urowid	nchar, nvarchar2
char, varchar2	X	X	X	X	X
number	X	X
datetime / interval	X		X
raw	X			X
rowid, urowid	X				X
nchar, nvarchar2		X	X	X	X	X

For example:

select cast( '22-Aug-2003' AS varchar2(30) )
from dual;

Oracle CAST Function

Version 10.2

CAST With Collections Using Multiset With A VARRAY CAST(MULTISET() AS ) CREATE OR REPLACE TYPE cust_address_t OID '53A970B3F5024BEC8EFD4F84CAD5E09E' AS OBJECT ( street_address VARCHAR2(40), postal_code VARCHAR2(10), city VARCHAR2(30), state_province VARCHAR2(2), country_id VARCHAR(2)); / CREATE OR REPLACE TYPE address_book_t AS TABLE OF cust_address_t; / CREATE TABLE cust_address ( custno NUMBER(10), street_address VARCHAR2(40), postal_code VARCHAR2(10), city VARCHAR2(30), state_province VARCHAR2(2), country_id VARCHAR2(2)); INSERT INTO cust_address VALUES (1,'123 Main St.','98040','Mercer Island','WA','US'); INSERT INTO cust_address VALUES (2,'1 Broadway','10202','New York','NY','US'); INSERT INTO cust_address VALUES (3,'2462 Edgar Crest','V6L 2C4','Vancouver','BC','CN'); COMMIT; CREATE TABLE cust_short ( custno NUMBER(10), name VARCHAR2(30)); INSERT INTO cust_short VALUES (1,'Morgan'); INSERT INTO cust_short VALUES (2,'Cline'); INSERT INTO cust_short VALUES (3,'Scott'); 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; Using Multiset With a PL/SQL Table CAST(MULTISET() AS ) CREATE OR REPLACE TYPE project_table_t AS TABLE OF VARCHAR2(25); / CREATE TABLE projects ( person_id NUMBER(10), project_name VARCHAR2(20)); CREATE TABLE pers_short ( person_id NUMBER(10), last_name VARCHAR2(25)); INSERT INTO projects VALUES (1, 'Teach'); INSERT INTO projects VALUES (1, 'Code'); INSERT INTO projects VALUES (2, 'Code'); INSERT INTO pers_short VALUES (1, 'Morgan'); INSERT INTO pers_short VALUES (2, 'Cline'); INSERT INTO pers_short VALUES (3, 'Scott'); COMMIT; SELECT * FROM projects; SELECT * FROM pers_short; SELECT e.last_name, CAST(MULTISET( SELECT p.project_name FROM projects p WHERE p.person_id = e.person_id ORDER BY p.project_name) AS project_table_t) FROM pers_short e; Using Multiset With A Multi-column Collection CAST(MULTISET() AS ) CREATE OR REPLACE TYPE uob_type AS OBJECT ( object_name VARCHAR2(128), object_type VARCHAR2(18)); / CREATE OR REPLACE TYPE t_uob_type AS TABLE OF uob_type; / set serveroutput on DECLARE x t_uob_type; BEGIN SELECT CAST(MULTISET( SELECT object_name, object_type FROM user_objects WHERE rownum <10) AS t_uob_type) INTO x FROM dual; FOR i IN 1 .. x.COUNT LOOP dbms_output.put_line(x(i).object_name || ' - ' || x(i).object_type); END LOOP; END; / Converting a Varray Type Column Into A Nested Table CAST( AS ) CREATE OR REPLACE TYPE district_t AS OBJECT ( region_no NUMBER(2), title VARCHAR2(35), cost NUMBER(7,2)); / CREATE TYPE DistList_t AS TABLE OF district_t; / CREATE TYPE DistrictList AS VARRAY(10) OF district_t; / CREATE TABLE region_tab ( reg_id NUMBER(2), reg_name VARCHAR2(15), district DistrictList); set describe depth all linenum on indent on desc region_tab SELECT * FROM region_tab; INSERT INTO region_tab VALUES(30, 'Northwest', DistrictList (District_t(1, 'Alaska', 3250), District_t(2, 'Washington', 12350), District_t(3, 'Oregon', 2750), District_t(4, 'Idaho', 1425))); INSERT INTO region_tab VALUES(40, 'Southwest', DistrictList (District_t(1, 'Arizona', 3250), District_t(2, 'California', 12350), District_t(3, 'Nevada', 2750), District_t(4, 'New Mexico', 1425))); SELECT CAST(s.district AS DistList_t) FROM region_tab s WHERE s.reg_id = 30; CAST With Dates Date CAST( AS ) SELECT CAST('01-JAN-2004' AS DATE) CDate FROM dual; Timestamp CAST( AS ) SELECT CAST(SYSDATE AS TIMESTAMP WITH LOCAL TIME ZONE) DTWTZ FROM dual; CAST With Numbers Number CAST( AS ) SELECT 1 + CAST(3.14 * 0.152 AS NUMBER(10,7)) FLOATING FROM dual; CAST With Strings Varchar2 CAST( AS ) SELECT object_name FROM user_objects; SELECT CAST(object_name AS VARCHAR2(30)) OBJ_NAME FROM user_objects;

Related Topics Types VArrays

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472920/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472920/