cast函数

cast函数

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

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

注意：可以直接将几个列直接转换成table，varray，Collection，Nested Table。此时需结合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') as date) 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) from dual;

2、转换一个集合

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

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

             
范例：1)转换成table

  create or replace type project_table_t as table of varchar2(25);

  create table projects (person_id number(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_id number(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 '53A970B3F5024BEC8EFD4F84 CAD5E09E' 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');
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 ;