发布日期:2015-11-05 11:04 来源: 标签: 数据库 oracle教程 oracle函数 oracle表集合与表达
本章我们主要学习oracle数据库实际应用中常用表集合与表达有哪些?下面我们就做一下具体讲解,希望大家多多支持中国站长网络学院。
1、General Information
嵌套表是在另一个表的结构中存储的表。
1)数据字典对象
collection$  type$  dba_types all_types user_types 
2)系统权限
   系统                    权限
CREATE TYPE            CREATE TABLE 
CREATE ANY TYPE        CREATE ANY TABLE 
DROP ANY TYPE          DROP ANY TABLE 
3)创建类型和表
您可以执行DML操作嵌套的表格,如果他们被定义为一个表的列。因此,当插入,删除或更新的query_table_expression_clause,声明是一个table_collection_expression,集合表达式必须是一个查询使用表格功能来选择表中的嵌套表列。遵循的例子是基于以下场景:
如果数据库包含一个列department_id,位置,表hr_info和manager_id,和一列嵌套表类型的人具有last_name,department_id,和每个经理的所有员工的工资列:
CREATE OR REPLACE TYPE people_typ AS OBJECT (
last_name     VARCHAR2(25),
department_id NUMBER(4),
salary        NUMBER(8,2));
/

desc people_typ

SELECT text
FROM user_source
WHERE name = 'PEOPLE_TYP';

SELECT typecode, attributes, incomplete, final, instantiable
FROM user_types
WHERE type_name LIKE 'PEOPLE%TYP';

CREATE OR REPLACE TYPE people_tab_typ AS TABLE OF people_typ;
/

desc people_tab_typ

SELECT text
FROM user_source
WHERE name = 'PEOPLE_TAB_TYP';

SELECT typecode, attributes, incomplete, final, instantiable
FROM user_types
WHERE type_name LIKE 'PEOPLE%TYP';

CREATE TABLE hr_info (
department_id NUMBER(4),
location_id   NUMBER(4),
manager_id    NUMBER(6),
people        people_tab_typ)
NESTED TABLE people STORE AS people_stor_tab;

desc hr_info

set describe depth all linenum on indent on

desc hr_info

col data_type format a30

SELECT column_name, data_type
FROM user_tab_cols
WHERE table_name = 'HR_INFO';

2、DML with Collections
1)插入
INSERT INTO hr_info
(department_id, location_id, manager_id, people)
VALUES
(280, 1800, 999, people_tab_typ());

INSERT INTO hr_info
(department_id, location_id, manager_id, people)
VALUES
(205, 2000, 881, people_tab_typ());

SELECT * FROM hr_info;

-- The following example inserts into the people nested 
-- table column of hr_info table's department numbered 280:

INSERT INTO TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 280)
VALUES ('Smith', 280, 1750);

INSERT INTO TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 205)
VALUES ('Smith', 999, 1750);

COMMIT;

SELECT * FROM hr_info;

-- another insert into the people table nested in hr_info 
INSERT INTO TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 280)
VALUES ('Jones', 280, 919);

SELECT * FROM hr_info;

SELECT cardinality(people)
FROM hr_info;
2)更新所有嵌套行
UPDATE TABLE(
SELECT <alias.column_name>
FROM <able_name alias>
WHERE <alias.column_name> = <value)
VALUES (<value, value, value>); 
eg:
-- Updates department 280's people nested table:

UPDATE TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 280) p
SET p.salary = p.salary + 100;

SELECT * FROM hr_info; 
3)更新选定的嵌套行
Updates only Smith's record  
eg:
UPDATE TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 280) p
SET p.salary = p.salary - 500
WHERE p.last_name = 'Smith';

SELECT * FROM hr_info; 
4)select(unnesting collection)
要从嵌套表列中选择数据,使用表函数来处理嵌套表作为表的列。这个过程被称为集合unnesting。
你可以从hr_info所有行,这是在前面的示例中创建的所有行,并从人的嵌套表列hr_info使用以下语句。
SELECT t1.department_id, t2.*
FROM hr_info t1, TABLE(t1.people) t2
WHERE t2.department_id = t1.department_id;
现在假设人是不是一个嵌套表列hr_info,而是一列last_name,department_id,单独的表地址,姓名和雇佣,工资。你可以在前面的例子中提取同一行,这一语句。
CREATE TABLE t (
department_id NUMBER(4),
last_name     VARCHAR2(25),
salary        NUMBER(8,2));

INSERT INTO t
SELECT t1.department_id, t2.last_name, t2.salary
FROM hr_info t1, TABLE(t1.people) t2
WHERE t2.department_id = t1.department_id;

SELECT *
FROM t;
5)删除
Delete from Department 280's people nested table 
eg:
DELETE TABLE(
  SELECT h.people
  FROM hr_info h
  WHERE h.department_id = 280) p
WHERE p.salary > 1200; 

3、Comparing Collections
1)比较集合
Collections cannot be directly compared for equality or inequality. For instance, the following IF condition is not allowed:

set serveroutput on

DECLARE
 TYPE clientele IS TABLE OF VARCHAR2(64);
 group1 clientele := clientele('Customer 1', 'Customer 2'); 
 group2 clientele := clientele('Customer 1', 'Customer 3'); 
BEGIN
  -- Equality test causes compilation error
  IF group1 = group2 THEN
    dbms_output.put_line('Equal');
  ELSE
    dbms_output.put_line('Not Equal');
  END IF;
END;
/

This restriction also applies to implicit comparisons. For example, collections cannot appear in a DISTINCT, 
GROUP BY, or ORDER BY list. Individual elements, however can be compared. For example:

DECLARE
 TYPE Clientele IS TABLE OF VARCHAR2(64);
 group1 Clientele := Clientele('Customer 1', 'Customer 2'); 
 group2 Clientele := Clientele('Customer 1', 'Customer 3'); 
BEGIN
  -- Equality test causes compilation error
  IF group1(1) = group2(1) THEN
    dbms_output.put_line('Equal');
  ELSE
    dbms_output.put_line('Not Equal');
  END IF;
END;
/

and

DECLARE
 TYPE Clientele IS TABLE OF VARCHAR2(64);
 group1 Clientele := Clientele('Customer 1', 'Customer 2'); 
 group2 Clientele := Clientele('Customer 1', 'Customer 3'); 
BEGIN
  -- Equality test causes compilation error
  IF group1(2) = group2(2) THEN
    dbms_output.put_line('Equal');
  ELSE
    dbms_output.put_line('Not Equal');
  END IF;
END;
/

so ...

DECLARE
 TYPE Clientele IS TABLE OF VARCHAR2(64);
 group1 Clientele := Clientele('Customer 1', 'Customer 2'); 
 group2 Clientele := Clientele('Customer 1', 'Customer 3'); 
BEGIN
  -- Equality test causes compilation error
  FOR i IN 1..2
  LOOP
    IF group1(i) = group2(i) THEN
      dbms_output.put_line('Equal');
    ELSE
      dbms_output.put_line(group1(i) || 
      ' Is Not The Same As ' || group2(i));
    END IF;
  END LOOP;
END;

相关评论

专题信息
    ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能;本教程主要是针对oracle应用过程中我们常用的函数进行深入讲解,让大家能够通过代码实例更快、更有效的掌握oracle函数,希望对大家有所帮助。