MassBot1: Created page with "=Oracle 7.3.2 SQL ----= This document highlights some of the differences between the SQL2 standard and the SQL dialect of Oracle 7.3.2. Please share with us any addition..."

2013-11-05T05:35:36Z

Created page with "=Oracle 7.3.2 SQL ----= This document highlights some of the differences between the SQL2 standard and the SQL dialect of Oracle 7.3.2. Please share with us any addition..."

New page

=Oracle 7.3.2 SQL ----=

This document highlights some of the differences between the SQL2 standard and the SQL dialect of Oracle 7.3.2. Please share with us any additional differences that you may find.

* [[yurttas/PL/DBL/oracle/F/R/#basic sql features|Basic SQL Features]]
* [[yurttas/PL/DBL/oracle/F/R/#data types|Data Types]]
* [[yurttas/PL/DBL/oracle/F/R/#indexes|Indexes]]
* [[yurttas/PL/DBL/oracle/F/R/#views|Views]]
* [[yurttas/PL/DBL/oracle/F/R/#constraints|Constraints]]
* [[yurttas/PL/DBL/oracle/F/R/#transactions|Transactions]]
* [[yurttas/PL/DBL/oracle/F/R/#timing sql commands|Timing SQL Commands]]

===---- Basic SQL Features===

----

Oracle does not support <tt>AS</tt> in <tt>FROM</tt> clauses, but you can still specify tuple variables without <tt>AS</tt>:

    from Relation1 u, Relation2 v

On the other hand, Oracle does support <tt>AS</tt> in <tt>SELECT</tt> clauses, although the use of <tt>AS</tt> is completely optional.

----

The set-difference operator in Oracle is called <tt>MINUS</tt> rather than <tt>EXCEPT</tt>. There is no bag-difference operator corresponding to <tt>EXCEPT ALL</tt>. The bag-intersection operator <tt>INTERSECT ALL</tt> is not implemented either. However, the bag-union operator <tt>UNION ALL</tt> ''is'' supported.

----

In Oracle, you must always prefix an attribute reference with the table name whenever this attribute name appears in more than one table in the <tt>FROM</tt> clause. For example, suppose that we have tables <tt>R(A,B)</tt> and <tt>S(B,C)</tt>. The following query does not work in Oracle, even though <tt>B</tt> is unambiguous because <tt>R.B</tt> is equated to <tt>S.B</tt> in the <tt>WHERE</tt> clause:

    select B from R, S where R.B = S.B;    /* ILLEGAL! */

Instead, you should use:

    select R.B from R, S where R.B = S.B;

----

In Oracle, the negation logical operator (<tt>NOT</tt>) should go in front of the boolean expression, not in front of the comparison operator. For example, "<tt>NOT A = ANY (<subquery>)</tt>" is a valid <tt>WHERE</tt> condition, but "<tt>A NOT = ANY (<subquery>)</tt>" is not. (Note that "<tt>A <> ANY (<subquery>)</tt>" is also a valid condition, but means something different.) There is one exception to this rule: You may use either "<tt>NOT A IN (<subquery>)</tt>" or "<tt>A NOT IN (<subquery>)</tt>".

----

Oracle does not support <tt>JOIN</tt> or <tt>OUTER JOIN</tt> expressions, either stand-alone or in <tt>FROM</tt> clauses. Instead, outerjoins are supported by a special operator "<tt>(+)</tt>" in <tt>WHERE</tt> clauses. For example, suppose that we have relations R(A, B) and S(C, D). Then,

    R full outer join S on R.B = S.C;
    R left outer join S on R.B = S.C;
    R right outer join S on R.B = S.C;

can be rewritten as:

    select * from R, S where R.B(+) = S.C(+);
    select * from R, S where R.B = S.C(+);
    select * from R, S where R.B(+) = S.C;

respectively. Basically, "<tt>(+)</tt>" next to a column means that this column can be padded with NULL's in the outerjoin result. You cannot outerjoin the same table to more than one other table in a single <tt>SELECT</tt> statement.

----

Oracle does support subqueries in <tt>FROM</tt> clauses as specified in the SQL2 standard.

----

Oracle <tt>ALTER TABLE</tt> statement does not allow you to drop columns, although you can add columns.

===---- Data Types===

----

<tt>BIT</tt> type is not supported. There is a <tt>BOOLEAN</tt> type in PL/SQL (see [[yurttas/PL/DBL/oracle/F/R/or-plsql.html|Using Oracle PL/SQL]] for details), but it cannot be used for a database column.

----

Domains (''i.e.'', type aliases) are not supported.

----

Dates and times are supported differently in Oracle. For details, please refer to [[yurttas/PL/DBL/oracle/F/R/or-time.html|Oracle Dates and Times]], available from the class web page.

===---- Indexes===

----

To create an index in Oracle, use the standard SQL2 syntax:

    create [unique] index <index_name> on <table_name>(<list_of_attrs>);

In general, <tt><list_of_attrs></tt> could contain more than one attribute. Such an index allows efficient retrieval of tuples with given values for <tt><list_of_attrs></tt>. The optional keyword <tt>UNIQUE</tt>, if specified, declares <tt><list_of_attrs></tt> to be duplicate-free, which in effect makes <tt><list_of_attrs></tt> a key of <tt><table_name></tt>.

To get rid of an index, again use the standard SQL2 syntax:

    drop index <index_name>;

----

Oracle automatically creates an index for each <tt>UNIQUE</tt> or <tt>PRIMARY KEY</tt> declaration. For example, if you create a table foo as follows:

    create table foo (a int primary key,
                      b varchar(20) unique);

Oracle will automatically create one index on <tt>foo.a</tt> and another on <tt>foo.b</tt>. Note that you cannot drop indexes for <tt>UNIQUE</tt> and <tt>PRIMARY KEY</tt> attributes. These indexes are dropped automatically when you drop the table or the key constraints (see the section on [[yurttas/PL/DBL/oracle/F/R/#constraints|Constraints]]).

----

To find out what indexes you have, use

    select index_name from user_indexes;

<tt>USER_INDEXES</tt> is another system table just like <tt>USER_TABLES</tt>. This can become especially helpful if you forget the names of your indexes and therefore cannot drop them. You might also see weird names of the indexes created by Oracle for <tt>UNIQUE</tt> and <tt>PRIMARY KEY</tt> attributes, but you will not be able to drop these indexes.

----

On the Stanford Oracle installation, there are two "tablespaces", one for data, the other for indexes. Every time you create an index (either explicitly with <tt>CREATE INDEX</tt> or implicitly with a <tt>UNIQUE</tt> or <tt>PRIMARY KEY</tt> declaration), you should (on the Stanford Oracle) follow the declaration by <tt>TABLESPACE CSINDX</tt>. In addition, if you are implicitly creating the index, you need the phrase <tt>USING INDEX</tt> before <tt>TABLESPACE CSINDX</tt>. For example:

create index RAindex on R(A) tablespace csindx;

create table foo (a int primary key using index tablespace csindx,
                  b varchar(20) unique using index tablespace csindx);

===---- Views===

----

Oracle supports views as specified in SQL2. To find out what views you have created, use:

    select view_name from user_views;

===---- Constraints===

----

To find out what constraints are defined in your database, use:

    select constraint_name from user_constraints;

----

Oracle supports key constraints as specified in SQL2. For each table, there can be only one <tt>PRIMARY KEY</tt> declaration, but many <tt>UNIQUE</tt> declarations. Each <tt>PRIMARY KEY</tt> (or <tt>UNIQUE</tt>) declaration can have multiple attributes, which means that these attributes ''together'' form a primary key (or a key, respectively) of the table.

----

Oracle supports foreign key constraints, and allows an optional <tt>ON DELETE CASCADE</tt> after a <tt>REFERENCES</tt> clause in a table declaration. However, it does not allow <tt>ON UPDATE</tt> or <tt>SET NULL</tt> options.

----

Oracle supports attribute- and tuple-based constraints, but does not allow <tt>CHECK</tt> conditions to use subqueries. Thus, there is no way for an attribute- or tuple-based constraint to reference anything else besides the attribute or tuple that is being inserted or updated.

----

Domain constraints are not supported since domains are not supported.

----

As for general constraints, <tt>ASSERTION</tt> is not supported. However, a <tt>TRIGGER</tt> close to the SQL3 trigger ''is'' supported. See [[yurttas/PL/DBL/oracle/F/R/or-plsql.html|Using Oracle PL/SQL]] for details.

----

In the <tt>ALTER TABLE</tt> statement, Oracle supports <tt>ADD</tt>ing columns and table constraints, <tt>MODIFY</tt>ing column properties and column constraints, and <tt>DROP</tt>ping constraints. However, you cannot <tt>MODIFY</tt> an attribute-based <tt>CHECK</tt> constraint. Here are some examples:

create table bar (x int, y int, constraint XYcheck check (x > y));
alter table bar add (z int, w int);
alter table bar add primary key (x);
alter table bar add constraint YZunique unique (y, z);
alter table bar modify (w varchar(2) default 'AM'
                                     constraint Wnotnull not null);
alter table bar add check (w in ('AM', 'PM'));
alter table bar drop constraint YZunique;
alter table bar drop constraint XYcheck;
alter table bar drop constraint Wnotnull;
alter table bar drop primary key cascade;

Dropping constraints generally requires knowing their names (only in the special case of primary or unique key constraints can you drop them without specifying their names). Thus, it is always a good idea to name all your constraints.

===---- Transactions===

----

Oracle supports transactions as defined by the SQL2 standard. A transaction is a sequence of SQL statements that Oracle treats as a single unit of work. As soon as you connect to the database with <tt>sqlplus</tt>, a transaction begins. Once the transaction begins, every SQL DML (Data Manipulation Language) statement you issue subsequently becomes a part of this transaction. A transaction ends when you disconnect from the database, or when you issue a <tt>COMMIT</tt> or <tt>ROLLBACK</tt> command.

<tt>COMMIT</tt> makes permanent any database changes you made during the current transaction. Until you commit your changes, other users cannot see them. <tt>ROLLBACK</tt> ends the current transaction and undoes any changes made since the transaction began.

After the current transaction has ended with a <tt>COMMIT</tt> or <tt>ROLLBACK</tt>, the first executable SQL statement that you subsequently issue will automatically begin another transaction.

For example, the following SQL commands have the final effect of inserting into table <tt>R</tt> the tuple (3, 4), but not (1, 2):

insert into R values (1, 2);
rollback;
insert into R values (3, 4);
commit;

----

Oracle also supports the <tt>SAVEPOINT</tt> command. The command <tt>SAVEPOINT <sp_name></tt> establishes a ''savepoint'' named <tt><sp_name></tt> which marks the current point in the processing of a transaction. This savepoint can be used in conjunction with the command <tt>ROLLBACK TO <sp_name></tt> to undo parts of a transaction.

For example, the following commands have the final effect of inserting into table <tt>R</tt> tuples (5, 6) and (11, 12), but not (7, 8) or (9, 10):

insert into R values (5, 6);
savepoint my_sp_1;
insert into R values (7, 8);
savepoint my_sp_2;
insert into R values (9, 10);
rollback to my_sp_1;
insert into R values (11, 12);
commit;

----

Oracle automatically issues an implicit <tt>COMMIT</tt> before and after any SQL DDL (Data Definition Language) statement (even if this DDL statement fails) .

===---- Timing SQL Commands===

----

Oracle provides a <tt>TIMING</tt> command for measuring the running time of SQL commands. To activate this feature, type

set timing on;

Then, Oracle will automatically display the elapsed wall-clock time for each SQL command you run subsequently. Note that timing data may be affected by external factors such as system load, ''etc''. To turn off timing, type

set timing off;

You can also create and control multiple timers; type <tt>HELP TIMING</tt> in <tt>sqlplus</tt> for details.

----

<font size="-2">This document is written originally for Prof. Jeff Ullman's CS145 class in Autumn, 1997; revised by Jun Yang for Prof. Jennifer Widom's CS145 class in Spring, 1998.</font>

Yurttas/PL/DBL/oracle/F/R/or-sql.html - Revision history

MassBot1: Created page with "=Oracle 7.3.2 SQL ----= This document highlights some of the differences between the SQL2 standard and the SQL dialect of Oracle 7.3.2. Please share with us any addition..."