oracle字符表事例
create table tt(id number,a varchar2(10));insert into TT (ID, A) values (1, '0a000000');insert into TT (ID, A) values (1, 'c0000000');insert into TT (ID, A) values (1, '00000b00');insert into TT (ID, A) values (1, '000a0000');insert into TT (ID, A) values (2, '0000000d');insert into TT (ID, A) values (2, '00000a00');insert into TT (ID, A) values (3, '0c000000');insert into TT (ID, A) values (3, '0000b000');insert into TT (ID, A) values (3, '000000g0');commit;create type tab_str is table of varchar2(100);create or replace function f_merge(p tab_str) return varchar2is lr raw(100);begin if p.count <= 0 then return null; end if; lr := utl_raw.copies('FF',length(p(1))); for i in 1..p.count loop lr := utl_raw.bit_and(lr,utl_raw.cast_to_raw(replace(p(i),'0',chr(255)))); end loop; return replace(utl_raw.cast_to_varchar2(lr),chr(255),'0');end;/select id,f_merge(cast(multiset(select a from tt where id = x.id) as tab_str)) a from (select id from tt group by id) x; ID A-------- ---------- 1 ca0a0b00 2 00000a0d 3 0c00b0g0drop function f_merge;drop type tab_str;drop table tt;
对象表
对象表
基于类型（Type）创建的表，而不是作为列的集合。创建语法： CREATE TABLE t OF some_type; 对于下例： CREATE OR REPLACE TYPE address_type AS OBJECT (city VARCHAR2(30), street VARCHAR2(30), state VARCHAR2(2), zip NUMBER ); CREATE OR REPLACE TYPE person_type AS OBJECT (name VARCHAR2(30), dob DATE, home_address address_type, work_address address_type ); CREATE TABLE people OF person_type; 通过执行如下语句，可以看到数据库中实际存放的结构： SELECT name,segcollength FROM SYS.COL$ WHERE obj#=(SELECT object_id FROM user_objects WHERE object_name='PEOPLE')；PEOPLE SYS_NC_OID$ 16 SYS_NC_ROWINFO$ 1 NAME 30 DOB 7 HOME_ADDRESS 1 SYS_NC00006$ 30 SYS_NC00007$ 30 SYS_NC00008$ 2 SYS_NC00009$ 22 WORK_ADDRESS 1 SYS_NC00011$ 30 SYS_NC00012$ 30 SYS_NC00013$ 2 SYS_NC00014$ 22 SYS_NC_OID$是系统为表产生的Object ID，RAW(16)，其上有唯一性索引。它是一主键为基础，并不是系统产生的，是一个伪列，且没有在硬盘上真正消耗空间； SYS_NC_ROWINFO$类似于嵌套表中，可作为单独一列返回整行； NAME, DOB是表中原有标量； HOME_ADDRESS, WORK_ADDRESS可作为单个对象，返回所代表的列的集合； SYS_NCnnnnn$是内嵌对象类型的标量实现。
