Home » SQL & PL/SQL » SQL & PL/SQL » Example for only clause? (ORACLE 10G)
icon5.gif  Example for only clause? [message #308565] Mon, 24 March 2008 22:03 Go to next message
lzfhope
Messages: 67
Registered: July 2006
Member
HI,
I hear that there's a type of clause called "only clause".
The clause like this: select * from only View,....
I do not know how to use this keyword in the select clause.So could you tell me how to use it? and the examples?
Re: Example for only clause? [message #308567 is a reply to message #308565] Mon, 24 March 2008 22:18 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>I hear that there's a type of clause called "only clause".
>The clause like this: select * from only View,....
It is the corollary to the NEVER FROM clause
SELECT * FROM NEVER FROM ID10T, .....
Re: Example for only clause? [message #308569 is a reply to message #308567] Mon, 24 March 2008 22:52 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#sthref9778
Re: Example for only clause? [message #308578 is a reply to message #308569] Mon, 24 March 2008 23:44 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
And here is an example. Notice the different results of the last two queries. The last one does not include rows from the subviews of the object view hierarchy.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE person_typ AS OBJECT
  2  ( ssn NUMBER,
  3    name VARCHAR2(30)) NOT FINAL;
  4  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE student_typ UNDER person_typ
  2  ( deptid NUMBER,
  3  	major VARCHAR2(30)) NOT FINAL;
  4  /

Type created.

SCOTT@orcl_11g> CREATE OR REPLACE TYPE employee_typ UNDER person_typ
  2  ( empid NUMBER,
  3    mgr VARCHAR2(30));
  4  /

Type created.

SCOTT@orcl_11g> CREATE TABLE persons
  2  ( ssn NUMBER,
  3    name VARCHAR2(30))
  4  /

Table created.

SCOTT@orcl_11g> CREATE TABLE students
  2  ( ssn NUMBER,
  3    name VARCHAR2(30),
  4    deptid NUMBER,
  5    major VARCHAR2(30))
  6  /

Table created.

SCOTT@orcl_11g> CREATE TABLE employees
  2  ( ssn NUMBER,
  3    name VARCHAR2(30),
  4    empid NUMBER,
  5    mgr VARCHAR2(30))
  6  /

Table created.

SCOTT@orcl_11g> CREATE OR REPLACE VIEW Person_v OF person_typ
  2    WITH OBJECT OID(ssn) AS
  3    SELECT *
  4    FROM persons
  5  /

View created.

SCOTT@orcl_11g> CREATE OR REPLACE VIEW Student_v OF student_typ UNDER Person_v
  2    AS
  3    SELECT *
  4    FROM students;

View created.

SCOTT@orcl_11g> /

View created.

SCOTT@orcl_11g> CREATE OR REPlACE VIEW Employee_v OF employee_typ UNDER Person_v
  2    AS
  3    SELECT *
  4    FROM employees
  5  /

View created.

SCOTT@orcl_11g> INSERT INTO persons (ssn, name) VALUES (123456789, 'name1')
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO persons (ssn, name) VALUES (345678912, 'name4')
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO students (ssn, name, deptid, major) VALUES (234567891, 'name2', 10, 'ENGLISH')
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO employees (ssn, name, empid, mgr) VALUES (345678912, 'name3', 3, null )
  2  /

1 row created.

SCOTT@orcl_11g> INSERT INTO employees (ssn, name, empid, mgr) VALUES (345678912, 'name4', 4,  3)
  2  /

1 row created.

SCOTT@orcl_11g> COMMIT
  2  /

Commit complete.

SCOTT@orcl_11g> SELECT * FROM Person_v
  2  /

       SSN NAME
---------- ------------------------------
 123456789 name1
 345678912 name4
 345678912 name3
 345678912 name4
 234567891 name2

SCOTT@orcl_11g> SELECT * FROM ONLY (Person_v)
  2  /

       SSN NAME
---------- ------------------------------
 123456789 name1
 345678912 name4

SCOTT@orcl_11g> 

Re: Example for only clause? [message #308595 is a reply to message #308578] Tue, 25 March 2008 00:50 Go to previous messageGo to next message
lzfhope
Messages: 67
Registered: July 2006
Member
Smile
Thank you very much!
Re: Example for only clause? [message #308613 is a reply to message #308578] Tue, 25 March 2008 01:10 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
./fa/1578/0/ Awesome Barbara
Previous Topic: Subquery in where clauses
Next Topic: Extract Numeric Value
Goto Forum:
  


Current Time: Thu Dec 08 08:23:15 CST 2016

Total time taken to generate the page: 0.04959 seconds