Oracle PL/SQL Tutorial/Object Oriented/VALUE Function

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

Use the built-in Oracle database VALUE() function to select a row from an object table

Value() treats the row as an actual object and returns the attributes for the object within a constructor for the object type.

The VALUE() function accepts a parameter containing a table alias



   <source lang="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> SELECT VALUE(op)

 2  FROM object_products op;

VALUE(OP)(ID, NAME, DESCRIPTION, PRICE, DAYS_VALID)


PRODUCTTYPE(2, "AAA", "BBB", 2.99, 5) SQL> SQL> SQL> drop table object_products; Table dropped. SQL></source>


VALUE operator

   <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      line2 :=addressLine2;
16      city :=address_city;
17      state_code :=address_state;
18      zip :=address_zip;
19    END set_address;
20  END;
21  /

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> DECLARE

 2    addressValue address;
 3  BEGIN
 4    SELECT VALUE(a) INTO addressValue FROM address_master a WHERE a.city ="Vancouver";
 5  END;
 6  /

PL/SQL procedure successfully completed. SQL> SQL> drop table address_master; Table dropped. SQL></source>