Oracle PL/SQL Tutorial/Object Oriented/VALUE Function
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>