Oracle PL/SQL Tutorial/Object Oriented/Object Table — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 13:04, 26 мая 2010

Accessing the object table by reference

The built-in function ref returns a pointer to the object.

The variable you need to declare is not of type employeeType, but ref employeeType.



   <source lang="sql">

SQL> SQL> SQL> create type employeeType is object ( 

 2      empNo    NUMBER,
 3      eName    VARCHAR2(10),
 4      job      VARCHAR2(9),
 5      mgr      NUMBER,
 6      hireDate DATE,
 7      sal      NUMBER,
 8      comm     NUMBER,
 9      deptNo   NUMBER,
10      member procedure p_changeName (i_newName_tx VARCHAR2),
11      member function  f_getIncome_nr  return VARCHAR2
12  )
13  /

SP2-0816: Type created with compilation warnings SQL> SQL> create or replace type body employeeType as 

 2      member function f_getIncome_nr return VARCHAR2 is
 3      begin
 4          return sal+comm;
 5      end f_getIncome_nr;
 6      member procedure p_changeName(i_newName_tx VARCHAR2) is
 7      begin
 8          eName:=i_newName_tx;
 9      end p_changeName;
10  end;
11  /

SP2-0818: Type Body created with compilation warnings SQL> SQL> create table t_emp of employeeType; Table created. SQL> SQL> SQL> SQL> declare 

 2      v_emp_oref ref employeeType;
 3      v_employeeType employeeType;
 4
 5  begin
 6      v_employeeType:=employeeType(100,"TestEmp",null,null,sysdate,1000,500,10);
 7      insert into t_emp values v_employeeType;
 8
 9
10      select ref(t) into v_emp_oref from t_emp t where empNo=100;
11
12      update t_emp t set sal=sal+100 where ref(t)=v_emp_oref;
13  end;
14  /

PL/SQL procedure successfully completed. SQL> SQL> select * from t_emp; 

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- --------- ---------- --------- ---------- ---------- ---------- 
      100 TestEmp                         09-JUN-07       1100        500         10

SQL> SQL> drop table t_emp; Table dropped. SQL> drop type employeeType; Type dropped. SQL></source>


Alter table to add a user-defined type column

   <source lang="sql">

SQL> SQL> create or replace type Address_Type 

 2  as object
 3  (  street_addr1   varchar2(25),
 4     street_addr2   varchar2(25),
 5     city           varchar2(30),
 6     state          varchar2(2),
 7     zip_code       number
 8  )
 9  /

Type created. SQL> SQL> alter type Address_Type 

 2  REPLACE
 3  as object
 4  (  street_addr1   varchar2(25),
 5     street_addr2   varchar2(25),
 6     city           varchar2(30),
 7     state          varchar2(2),
 8     zip_code       number,
 9     member function toString return varchar2,
10     map member function mapping_function return varchar2
11  )
12  /

Type altered. SQL> SQL> create or replace type body Address_Type 

 2  as
 3      member function toString return varchar2
 4      is
 5      begin
 6          if ( street_addr2 is not NULL )
 7          then
 8              return street_addr1 || " " ||
 9                     street_addr2 || " " ||
10                     city || ", " || state || " " || zip_code;
11          else
12              return street_addr1 || " " ||
13                     city || ", " || state || " " || zip_code;
14          end if;
15      end;
16
17      map member function mapping_function return varchar2
18      is
19      begin
20          return to_char( nvl(zip_code,0), "fm00000" ) ||
21                 lpad( nvl(city," "), 30 ) ||
22                 lpad( nvl(street_addr1," "), 25 ) ||
23                 lpad( nvl(street_addr2," "), 25 );
24      end;
25  end;
26  /

Type body created. SQL> SQL> SQL> create table people 

 2  ( name           varchar2(10),
 3    home_address   address_type,
 4    work_address   address_type
 5  )
 6  /

Table created. SQL> SQL> SQL> create or replace type Address_Array_Type 

 2  as varray(50) of Address_Type
 3  /

Type created. SQL> SQL> alter table people add previous_addresses Address_Array_Type 

 2  /

Table altered. SQL> select name, prev.city, prev.state, prev.zip_code 

 2    from people p, table( p.previous_addresses ) prev
 3   where prev.state = "PA";

no rows selected SQL> / no rows selected SQL> SQL> drop table people; Table dropped. SQL> drop type Address_Array_Type; Type dropped. SQL> drop type address_type; Type dropped.</source>


Create a table based on an Object with methods

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE address AS OBJECT 

 2              (line1 VARCHAR2(20),
 3               line2 VARCHAR2(20),
 4               city VARCHAR2(20),
 5               state_code VARCHAR2(2),
 6               zip VARCHAR2(13),
 7    MEMBER FUNCTION get_address RETURN VARCHAR2,
 8    MEMBER PROCEDURE set_address
 9              (addressLine1 VARCHAR2,
10               addressLine2 VARCHAR2,
11               address_city VARCHAR2,
12               address_state VARCHAR2,
13               address_zip VARCHAR2)
14  );
15  /

Type created. SQL> CREATE OR REPLACE TYPE BODY address AS 

 2    MEMBER FUNCTION get_address RETURN VARCHAR2
 3    IS
 4    BEGIN
 5      RETURN (SELF.line1||" "||SELF.line2||" "||SELF.city||", "||SELF.state_code||" "||SELF.zip);
 6    END get_address;
 7    MEMBER PROCEDURE set_address (addressLine1 VARCHAR2,
 8                  addressLine2 VARCHAR2,
 9                  address_city VARCHAR2,
10                  address_state VARCHAR2,
11                  address_zip VARCHAR2)
12    IS
13    BEGIN
14      line1 :=addressLine1;
15
16      line2 :=addressLine2;
17
18      city :=address_city;
19
20      state_code :=address_state;
21
22      zip :=address_zip;
23
24    END set_address;
25  END;
26  /

Type body created. SQL> SQL> CREATE TABLE address_master OF address; Table created. SQL> SQL> INSERT INTO address_master VALUES (address("19 J","R Rd","Vancouver","NJ","00000")); 1 row created. SQL> SQL> select * from address_master; LINE1 LINE2 CITY ST

-------------------- -------------------- --

ZIP

19 J R Rd Vancouver NJ 00000

1 row selected. SQL> SQL> drop table address_master; Table dropped. SQL></source>


Insert data just as one would ordinarily do with a common SQL table:

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE addressType as OBJECT( 

 2  street VARCHAR2(20),
 3  city VARCHAR2(20),
 4  state CHAR(2),
 5  zip CHAR(5))
 6  /

Type created. SQL> SQL> CREATE TABLE emp (empno NUMBER(3), 

 2  name VARCHAR2(20),
 3  address addressType)
 4  /

Table created. SQL> SQL> SQL> INSERT INTO emp VALUES (101, "Adam", 

 2  addressType("1 A St.","Mobile","AL","36608"));

1 row created. SQL> SQL> select * from emp; 

    EMPNO NAME                 ADDRESS(STREET, CITY, STATE, ZIP)
-------------------- -------------------------------------------------- 
      101 Adam                 ADDRESSTYPE("1 A St.", "Mobile", "AL", "36608")

SQL> SQL> SQL> CREATE TABLE address_table OF addressType; Table created. SQL> SQL> SQL> INSERT INTO address_table VALUES ("4 D St.", "Gulf","FL","32563"); 1 row created. SQL> SQL> SQL> SELECT city 

 2  FROM address_table;

CITY

Gulf SQL> SQL> SQL> drop table address_table; Table dropped. SQL> drop table emp; Table dropped. SQL> SQL> drop type addresstype; Type dropped. SQL></source>


Inserting a row into an object table

   <source lang="sql">

SQL> SQL> SQL> CREATE Or Replace TYPE ProductType AS OBJECT ( 

 2    id          NUMBER,
 3    name        VARCHAR2(15),
 4    description VARCHAR2(22),
 5    price       NUMBER(5, 2),
 6    days_valid  NUMBER
 7  )
 8  /

Type created. SQL> SQL> CREATE TABLE object_products OF ProductType 

 2  /

Table created. SQL> SQL> SQL> INSERT INTO object_products VALUES ( 

 2    ProductType(1, "AAA", "BBB", 3.95, 10)
 3  );

1 row created. SQL> SQL> select * from object_products; 

ID NAME            DESCRIPTION                 PRICE DAYS_VALID

--- --------------- ---------------------- ---------- ---------- 

 1 AAA             BBB                          3.95         10

SQL> SQL> drop table object_products; Table dropped. SQL> SQL></source>


INSERT Values into a Table that Contains Row Objects (TCRO)

One way to begin is to insert into the client table and null the addressType:



   <source lang="sql">

SQL> SQL> SQL> CREATE OR REPLACE TYPE addressType as OBJECT( 

 2  street VARCHAR2(20),
 3  city VARCHAR2(20),
 4  state CHAR(2),
 5  zip CHAR(5))
 6  /

Type created. SQL> SQL> CREATE TABLE address_table OF addressType 

 2  /

Table created. SQL> SQL> CREATE TABLE client (name VARCHAR2(20), 

 2    address REF addressType scope is address_table)
 3  /

Table created. SQL> SQL> DESC client; 

Name          Null?    Type
----------
NAME                   VARCHAR2(20)
ADDRESS                REF OF ADDRESSTYPE

SQL> SQL> INSERT INTO client VALUES ("Jones",null); 1 row created. SQL> SQL> SQL> SELECT * 

 2  FROM client;

NAME ADDRESS

--------------------------------------------------

Jones SQL> SQL> SQL> drop table address_table; Table dropped. SQL> drop table client; Table dropped. SQL> SQL> drop type addresstype; Type dropped. SQL></source>


One-step INSERTs into a Table that Contains Row Objects

   <source lang="sql">

SQL> SQL> SQL> CREATE OR REPLACE TYPE addressType as OBJECT( 

 2  street VARCHAR2(20),
 3  city VARCHAR2(20),
 4  state CHAR(2),
 5  zip CHAR(5))
 6  /

Type created. SQL> SQL> CREATE TABLE address_table OF addressType 

 2  /

Table created. SQL> SQL> CREATE TABLE client (name VARCHAR2(20), 

 2    address REF addressType scope is address_table);

Table created. SQL> SQL> SQL> DESC client; 

Name       Null?    Type
----------
NAME                VARCHAR2(20)
ADDRESS             REF OF ADDRESSTYPE

SQL> SQL> INSERT INTO client 

 2  SELECT "Walsh", REF(aa)
 3  FROM address_table aa
 4  WHERE zip = "32563";

0 rows created. SQL> SQL> SELECT name, DEREF(address) 

 2  FROM client;

no rows selected SQL> SQL> SQL> select * from client; no rows selected SQL> SQL> --DEREF (Dereference) the Row Addresses SQL> SELECT name, DEREF(address) 

 2  FROM client;

no rows selected SQL> SQL> SQL> drop table address_table; Table dropped. SQL> drop table client; Table dropped. SQL> SQL> drop type addressType; Type dropped.</source>


SELECT from the object-oriented table

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE addressType as OBJECT( 

 2  street VARCHAR2(20),
 3  city VARCHAR2(20),
 4  state CHAR(2),
 5  zip CHAR(5))
 6  /

Type created. SQL> SQL> CREATE TABLE address_table OF addressType 

 2  /

Table created. SQL> SQL> CREATE TABLE client (name VARCHAR2(20), 

 2    address REF addressType scope is address_table);

Table created. SQL> SQL> DESC client; 

Name      Null?    Type
----------    
NAME               VARCHAR2(20)
ADDRESS            REF OF ADDRESSTYPE

SQL> SQL> INSERT INTO client VALUES ("Jones",null); 1 row created. SQL> SQL> SQL> SELECT * 

 2  FROM client;

NAME ADDRESS

--------------------------------------------------

Jones SQL> SQL> SQL> UPDATE client SET address = 

 2  (SELECT REF(aa)
 3  FROM address_table aa
 4  WHERE aa.city LIKE "Mob%")
 5  WHERE name = "Jones"
 6

SQL> select * from client; NAME ADDRESS

--------------------------------------------------

Jones SQL> SQL> drop table address_table; Table dropped. SQL> drop table client; Table dropped. SQL> drop type addresstype; Type dropped. SQL> SQL></source>


SELECTing Individual Columns in Table that Contains Row Objects

   <source lang="sql">

SQL> SQL> SQL> CREATE OR REPLACE TYPE addressType as OBJECT( 

 2  street VARCHAR2(20),
 3  city VARCHAR2(20),
 4  state CHAR(2),
 5  zip CHAR(5))
 6  /

Type created. SQL> SQL> CREATE TABLE address_table OF addressType 

 2  /

Table created. SQL> SQL> CREATE TABLE client (name VARCHAR2(20), 

 2    address REF addressType scope is address_table);

Table created. SQL> SQL> SQL> DESC client; 

Name      Null?    Type
---------
NAME               VARCHAR2(20)
ADDRESS            REF OF ADDRESSTYPE

SQL> SQL> INSERT INTO client 

 2  SELECT "Walsh", REF(aa)
 3  FROM address_table aa
 4  WHERE zip = "32563";

0 rows created. SQL> SQL> SELECT c.name, c.address.city 

 2  FROM client c;

no rows selected SQL> SQL> SQL> drop table address_table; Table dropped. SQL> drop table client; Table dropped. SQL> SQL> drop type addressType; Type dropped. SQL></source>


Selecting Rows from the object Table

   <source lang="sql">

SQL> SQL> SQL> CREATE or Replace TYPE ProductType AS OBJECT ( 

 2    id          NUMBER,
 3    name        VARCHAR2(15),
 4    description VARCHAR2(22),
 5    price       NUMBER(5, 2),
 6    days_valid  NUMBER
 7  )
 8  /

Type created. SQL> SQL> CREATE TABLE object_products OF ProductType 

 2  /

Table created. SQL> SQL> INSERT INTO object_products ( 

 2    id, name, description, price, days_valid
 3  ) VALUES (
 4    2, "AAA", "BBB", 2.99, 5
 5  );

1 row created. SQL> SQL> select * from object_products; 

ID NAME            DESCRIPTION                 PRICE DAYS_VALID

--- --------------- ---------------------- ---------- ---------- 

 2 AAA             BBB                          2.99          5

SQL> SQL> drop table object_products; Table dropped. SQL> SQL></source>


UPDATE a Table that Contains Row Objects (TCRO)

   <source lang="sql">

SQL> SQL> CREATE OR REPLACE TYPE addressType as OBJECT( 

 2  street VARCHAR2(20),
 3  city VARCHAR2(20),
 4  state CHAR(2),
 5  zip CHAR(5))
 6  /

Type created. SQL> SQL> CREATE TABLE address_table OF addressType 

 2  /

Table created. SQL> SQL> CREATE TABLE client (name VARCHAR2(20), 

 2    address REF addressType scope is address_table);

Table created. SQL> SQL> SQL> DESC client; 

Name        Null?    Type
----------
NAME                 VARCHAR2(20)
ADDRESS              REF OF ADDRESSTYPE

SQL> SQL> INSERT INTO client VALUES ("Jones",null); 1 row created. SQL> SQL> SQL> SELECT * 

 2  FROM client;

NAME ADDRESS

--------------------------------------------------

Jones SQL> SQL> SQL> UPDATE client SET address = 

 2  (SELECT REF(aa)
 3  FROM address_table aa
 4  WHERE aa.city LIKE "Mob%")
 5  WHERE name = "Jones"
 6

SQL> select * from client; NAME ADDRESS

--------------------------------------------------

Jones SQL> SQL> drop table address_table; Table dropped. SQL> drop table client; Table dropped. SQL> drop type addresstype; Type dropped.</source>


Updating a Row in the object Table

   <source lang="sql">

SQL> SQL> SQL> SQL> CREATE Or Replace TYPE ProductType AS OBJECT ( 

 2    id          NUMBER,
 3    name        VARCHAR2(15),
 4    description VARCHAR2(22),
 5    price       NUMBER(5, 2),
 6    days_valid  NUMBER
 7  )
 8  /

Type created. SQL> SQL> CREATE TABLE object_products OF ProductType 

 2  /

Table created. SQL> SQL> INSERT INTO object_products ( 

 2    id, name, description, price, days_valid
 3  ) VALUES (
 4    1, "AAA", "BBB", 2.99, 5
 5  );

1 row created. SQL> SQL> select * from object_products; 

ID NAME            DESCRIPTION                 PRICE DAYS_VALID

--- --------------- ---------------------- ---------- ---------- 

 1 AAA             BBB                          2.99          5

SQL> SQL> UPDATE object_products 

 2  SET description = "new"
 3  WHERE id = 1;

1 row updated. SQL> select * from object_products; 

ID NAME            DESCRIPTION                 PRICE DAYS_VALID

--- --------------- ---------------------- ---------- ---------- 

 1 AAA             new                          2.99          5

SQL> SQL> drop table object_products; Table dropped. SQL></source>


You can also use an object type to define an entire table, and the table is known as an object table.

   <source lang="sql">

SQL> SQL> SQL> CREATE or replace TYPE EmployeeType AS OBJECT ( 

 2    id          NUMBER,
 3    name        VARCHAR2(15),
 4    description VARCHAR2(22),
 5    salary       NUMBER(5, 2)
 6  );
 7  /

Type created. SQL> CREATE TABLE object_employee OF EmployeeType; Table created. SQL> SQL> desc object_employee; 

Name                Null?    Type
ID                           NUMBER
NAME                         VARCHAR2(15)
DESCRIPTION                  VARCHAR2(22)
SALARY                       NUMBER(5,2)

SQL> SQL> drop table object_employee; Table dropped. SQL> SQL> SQL></source>

Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/Object_Oriented/Object_Table&oldid=2983»