SELECT Only One Column in the Composite : Select « Object Oriented Database « Oracle PL / SQL






SELECT Only One Column in the Composite



SQL> -- SELECTing Only One Column in the Composite
SQL>
SQL> CREATE OR REPLACE TYPE address_obj 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 ADDRESS_OBJ);

Table created.

SQL>
SQL>
SQL> INSERT INTO emp VALUES (101, 'Adam', ADDRESS_OBJ('1 A St.','Mobile','AL','36608'));

1 row created.

SQL>
SQL> COLUMN name FORMAT a9
SQL> COLUMN empno FORMAT 999999
SQL> COLUMN address FORMAT a50
SQL>
SQL>
SQL> SELECT name, e.address.city FROM emp e;

NAME      ADDRESS.CITY
--------- --------------------
Adam      Mobile

SQL>
SQL>
SQL> drop table emp;

Table dropped.

SQL>
SQL>
           
       








Related examples in the same category

1.Display the New Table (SELECT * and SELECT by Column Name)
2.Query object table
3.Use type member function in select statement
4.SELECT with a WHERE Clause
5.Query data from table based on object
6.SELECTing Individual Columns in TCROs
7.Use the function we created: use the table alias in our SELECT as well as the qualifier
8.Use * to reference column with user-defined type
9.demonstrates SQL operations on object types.
10.Select the object type from the table, rather than the columns.
11.Use %ROWTYPE to select from the relational table.