cast函数

cast函数

转自：http://blog.csdn.net/nvhaixx/article/details/6731936

作用：进行数据类型转换。

注意：可以直接将几个列直接转换成table，varray，Collection，NestedTable。此时需结合multiset集合函数一起使用。

1、转换一个列或值

语法：cast( 列名/值 as 数据类型 )

参数解释：1)列名/值              表中的列名
         2)数据类型             转换后的数据类型

范例：select cast('2323' as char(6)) from dual;

     select sysdate,cast(sysdate as timestamp with local time zone )from dual;

     select cast(to_date('20110419010101','yyyy-mm-dd hh24:mi:ss') asdate) from dual;
     
     转换成Nested Table
     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);
     
     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)));
     
     commit;

     set describe depth all linenum on indent on;

     desc region_tab;

     select* from region_tab;
     
     select cast(s.district as DistList_t)
     from region_tab s
     where s.reg_id = 30;

注意：不能指定显示的数据格式，且如果原本的数据值不满足转换的值的条件时，会报错。

范例：select cast(to_date('20110419','yyyy-mm-dd ') as date) fromdual;

2、转换一个集合

语法：cast( multiset(查询语句) as 数据类型 )

参数解释：1)查询语句              查询语句
         2)数据类型              转换后的数据类型
         3)multiset               查询返回的是多行

        
范例：1)转换成table

 create or replace type project_table_t as tableof varchar2(25);

 create table projects (person_idnumber(10),project_name varchar2(20));
 
 insert into projects values(1, 'Teach');
 insert into projects values(1, 'Code');
 insert into projects values(2, 'Code');
 commit;

 create table pers_short (person_idnumber(10),last_name varchar2(25));
 
 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 a.last_name,
        cast(multiset(select b.project_name
                  from projects b
                where b.person_id = a.person_id
                order by p.project_name) as project_table_t)
   from pers_short a;


2)转换成varray

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 OFcust_address_t;

create table cust_address(custno number(10), street_addressvarchar2(40), postal_code varchar2(10), city varchar2(30), state_province varchar2(2), country_idvarchar2(2));

insert into cust_address values(1, '123 Main St.', '98040', 'MercerIsland', 'WA', 'US');
insert into cust_address values(2, '1 Broadway', '10202', 'NewYork', 'NY', 'US');
insert into cust_address values(3, '2462 Edgar Crest', 'V6L 2C4','Vancouver', 'BC', 'CN');
commit;

create table cust_short(custno number(10), namevarchar2(30));

insert into cust_short values(1, 'Morgan');
insert into cust_short values(2, 'Cline');
insert into cust_short values(3, 'Scott');
commit;

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;


3)转换成Collection

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 ;