oracle函数整理---cast函数

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

1、转换一个栏位或值

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

参数解释：1)栏位名/值               表中的栏位名

                    2)数据类型                转换后的数据类型

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

      selectsysdate,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 ') asdate) 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;
selecta.last_name,
         cast(multiset (selectb.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 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;
         declarex 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 ;