cast函数 oracle 日期_ORACLE中非常好用的类型转换函数CAST

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

SELECTCAST(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)));

SELECTCAST(s.district AS DistList_t)

FROM region_tab s

WHERE s.reg_id = 30;

CAST With Dates

Date CAST( AS )

SELECTCAST('01-JAN-2004' AS DATE) CDate

FROM dual;

Timestamp CAST( AS )

SELECTCAST(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;

SELECTCAST(object_name AS VARCHAR2(30)) OBJ_NAME

FROM user_objects;

Related Topics

Types

VArrays