More Object Database Concepts
This article provides a comprehensive overview of key database concepts, including the use of Value, DREF, and REF functions. Learn how to retrieve specific values from rows and get references to address rows using SQL commands. Explore VARRAY for creating variable-length arrays and understand practical SQL queries to manipulate and retrieve data effectively. Examples are provided for clarity, demonstrating how to utilize PERSON_T types and referencing with REF and DEREF functions in SQL. Enhance your database skills through these fundamental concepts.
More Object Database Concepts
E N D
Presentation Transcript
Basic Functions and datatypes • Value, DREF and REF • VARRAY
Value (returns value of a row) • SQL> select value(p) from person p; VALUE(P)(PID, FIRSTNAME, LASTNAME, DOB) ------------------------------------------------------------------ PERSON_T('121111111', 'Joe', 'Smith', '21-OCT-61') PERSON_T('121222222', 'John', 'Kelsey', '15-DEC-66')
REF (returns the address of the row) SQL> select ref(p) from person p where lastname='Grand'; • REF(P) ----------------------------------------------------- 000040030A003C0F3289B74EAC4CA1BB4D0DD5C4F1F78900000017260
DEREF (given a reference to a row, gets the row); • SELECT DEREF(s_obj.super) INTO p_obj FROM DUAL;
Dual: dummy table • SQL> select sysdate from dual • SYSDATE • --------- • 22-SEP-03
VARRAY: Variable length array • CREATE OR REPLACE TYPE student_array AS VARRAY(50) OF REF student_t;
