Oracle SQL

 

 

Contents

  1. Overview
  2. Character, Large Object, and Number Data Types
  3. Dates
  4. Pseudo-Columns
  5. Data Conversion
  6. Arithmetic Operators
  7. Concatenation Operator
  8. Comparison Operators
  9. Logical Operators
  10. Set Operators
  11. The SQL Operation
  12. The Target
  13. Joins
  14. Outer join
  15. The Condition
  16. SQL Statements

 

 

Overview

SQL is a language used manipulate relational databases. SQL is generally not case-sensitive, except for literals, which are enclosed in quotes.

 

 

Character, Large Object, and Number Data Types

Type Description
CHAR [(length)] Fixed-length char data. Max length: 2000 bytes.
VARCHAR2 [(length)] Variable-length char data. Max length: 4000 bytes.
NCHAR [(length)] Fixed-length National Character Language (NLS) data. Max length: 2000 bytes
NVARCHAR2 [(length)] Variable-length National Language Support (NLS) data. Max length: 4000 bytes
LONG Variable-length char data. Max length: 2 gigabytes.
RAW Raw binary data. Max length: 2000 bytes.
LONG RAW Raw binary data. Max length: 2 gigabytes.
BLOB Raw binary data. Max length: 4 gigabytes. When a BLOB column is referenced, a LOB locator is returned.
CLOB Large char data. Max length: 4 gigabytes.
NCLOB Large National Language Support (NLS) char data. Max length: 4 gigabytes.
BFILE References a binary OS file external to the Oracle database. Max size: 4 gigabytes.
NUMBER [(precision[,scale])] Values between 10-130 and 9.99999...x 10125. Precision is the number of digits in the number. Max digits: 38. Scale is the number of digits to the right of the decimal point. Range is -84 to 127. If scale is omitted, the number is treated as an integer number, and no decimal portion is stored. If both scale and precision are omitted, the number is treated as a floating-point number.
DATE Date format.
ROWID 18-character string that uniquely identifies the physical location of any single row of data. The value can change if the row is stored differently, for example, an export and an import of data will make the ROWID inaccurate.
UROWID 18-character string that uniquely identifies the logical ROWID. Oracle strongly recommends that you use the UROWID datatype in place of ROWID.

 

 

Dates

Oracle DATE data uses the NLS_DATE_FORMAT specified within the init.ora file. The default NLS_DATE_FORMAT is DD-MON-YY. To insert the date Feb 21, 1961 into a table column called b_day:

insert INTO tablename (b_day)
VALUES ('21-FEB-61');

You can use the TO_DATE to convert dates into the proper format. For example, if your NLS_DATE_FORMAT uses slashes, you could run the following to convert and insert a data with dashes: 

insert into tablename (b_day)
VALUES (TO_DATE('21-FEB-61','MM/DD/YY'));

Oracle provides several datatypes whose primary purpose is to provide efficient reference to objects stored both within and outside the database.

 

 

Pseudo-Columns

While not actual datatypes, Oracle supports several special-purpose data elements. These elements are not actually contained in a table, but are available for use in SQL statements as though they were part of the table.

ROWNUM For each row of data returned by a SQL query, ROWNUM will contain a number indicating the order in which the row was retrieved. For example, the first row retrieved will have a ROWNUM of 1, the second row will have a ROWNUM of 2, and so on. This approach can be useful for limiting the number of rows returned by a query. To display only ten rows of the table table, the following SQL statement makes use of the ROWNUM pseudo-column:

select *
from table
where ROWNUM < 11;

ROWNUM returns a number indicating the order in which the row was retrieved from the table, but this is not always the order in which a row is displayed. For example, if a SQL statement includes an ORDER BY clause, rows will not be displayed in ROWNUM sequence, since ROWNUM is assigned before the sort operation.
CURRVAL When using Oracle SEQUENCE values, (see "CREATE/ALTER/DROP SEQUENCE" in Chapter 2, Data Definition Statements), the pseudo-column CURRVAL returns the current value of the sequence. To reference CURRVAL, it must be associated with a sequence:

[schema.]sequence_name.CURRVAL

NEXTVAL When using Oracle SEQUENCE values, the pseudo-column NEXTVAL returns the next value of the sequence and causes the sequence to increment by one. You can only reference NEXTVAL if it is associated with a sequence:

[schema.]sequence_name.NEXTVAL

Oracle will only increment the sequence once in a given SQL statement, so if a statement contains multiple references to NEXTVAL, the second and subsequent reference will return the same value as CURRVAL.
LEVEL For each row returned by a hierarchical query (using the CONNECT BY clause), LEVEL returns 1 for a root node, 2 for a child of a root, and so on. A root node is the highest node within an inverted tree. A child node is any nonroot node, a parent node is any node that has children, and a leaf node is any node without children.
USER This pseudo-column will always contain the Oracle username under which you are connected to the database.
SYSDATE The contain the current date and time. This column is a standard Oracle DATE datatype. The date and time contained in SYSDATE comes from the server that processes the query, not the client from which the query is run. So if you connect to a server in Tokyo from a client workstation in London, the date and time will be that of the server in Tokyo. If you return a SYSDATE column via a database link (for example, select SYSDATE from dual@london), the date and time will be returned from the server you are connected to, not the remote server referenced by the database link.

 

 

Data Conversion

There are multiple ways to represent data in a database. For example, a bary, which is normally considered a numeric value such as 25,000 can be represented easily as a character string such as "25000". Likewise, an employee ID can be represented as a number (500) or a string ("500"). If you attempt to perform an arithmetic operation on a character value in most computer languages, an error will occur. Not so with SQL. Oracle automatically performs a data conversion when it is necessary (and possible) to complete a requested operation. In the following SQL statement, assume that b is defined in the database as CHAR(6)--a character string with a fixed length of six bytes:

select ename, b * 1.1
from user.emp;

The character string (sal) is multiplied by a numeric constant (1.1). To perform this operation, Oracle first converts the string into a number, and then performs the multiplication. This type of automatic conversion is an implicit data conversion.

While convenient, implicit data conversion also has a cost in CPU utilization, so be careful when deciding how to store data elements in the database.

Implicit data conversion can have an unexpected negative impact on performance, since it can dramatically affect the way the Oracle query optimizer generates an execution plan. For example, use of an index may be suppressed due to implicit data conversion, resulting in a full table scan.

SQL also provides several functions that perform explicit data conversion:

TO_CHAR Numeric-to-character and date-to-character conversions
TO_NUMBER Character-to-numeric conversion
TO_DATE Character-to-date conversion

 

 

Arithmetic Operators

Operator Description Example
+ Addition sal + comm
- Subtraction sal - comm
* Multiplication sal * 1.1
/ Division sal / 12
- Negation -sal
+ Identity +sal

 

 

Concatenation Operator

The concatenation operator (||) combines two character strings. Consider the following SQL statement:

select fname || ' ' || lname
from table2;

This statement returns (for each row) a single string consisting of the first name, a space, and the last name.

Although most Oracle platforms use solid vertical bars (||) as the concatenation operator, some platforms, most notably IBM platforms using the EBCDIC character set, use the broken vertical bars ( ). When converting between ASCII and EBCDIC character sets, the conversion of these characters may not be correct.

If one of two concatenated strings is NULL, the result is a non-NULL string. The NULL string is treated as an tablety string. If both strings being concatenated are NULL, then the resulting string is NULL. If either of the operands is a VARCHAR2 datatype, the resulting string is a VARCHAR2 datatype as well.

A concatenated string may not be longer than 2000 characters if the operands are CHAR datatypes, or 4000 characters otherwise. Other character types, like LONG and CLOB, cannot be concatenated.

 

 

Comparison Operators

Operator Use Description Example
= a = b Tests for equality of two operands. select * from table
where b =500
!= a != b Tests for inequality of two operands. select * from table
where b !=500

^=

 a ^= b Tests for inequality of two operands. select * from table
where b ^=500
<> a <> b Tests for inequality of two operands. select * from table
where b <>500
< a < b Tests that operand a is less than operand b.

select * from table
where b <500

!< a !< b Tests that operand a is not less than operand b. This is the same as >=. select * from table
where b !<500
> a > b Tests that operand a is greater than operand b. select * from table
where b >500
!> a !> b Tests that operand a is not greater than operand b. This is the same as <=. select * from table
where b !>500
<= a <= b Tests that operand a is less than or equal to operand b. This is the same as !>. select * from table
where b <=500
>= a >= b Tests that operand a is greater than or equal to operand b. This is the same as !<. select * from table
where b >=500
IN a IN (b,c...) Tests that operand a matches at least one element of the list provided (operand b, operand c, etc.). select * from table
where b IN (500,600,700)
not IN a not IN (b,c...) Tests that operand a does not match any element of the list provided (operand b, operand c, etc.). select * from table
where b not IN (500,600,700)
ANY a = ANY (b,c...)
a < ANY (b,c...)
a > ANY (b,c...), etc. 		Tests that the relationship specified (e.g., =, <>, <, >, etc.) is true for at least one element of the list provided (operand b, operand c, etc.). When testing for equality, this is equivalent to IN. select * from table
where b = ANY (500,600,700)
SOME a = SOME (b,c...)
a < SOME (b,c...)
a > SOME (b,c...), etc. 		Tests that the relationship specified (e.g., =, <>, <, >, etc.) is true for at least one element of the list provided (operand b, operand c, etc.). When testing for equality, this is equivalent to IN. select * from table
where b = SOME (500,600,700)
all a = all (b,c...)
a < all (b,c...)
a < all (b,c...), etc. 		Tests that the relationship specified (e.g., =, <>, <, >, etc.) is true for every element of the list provided (operand b, operand c, etc.). select * from table
where b > all (500,600,700)
between a between b and c Tests that operand a is greater than or equal to operand b and less than or equal to operand c. select * from table
where b between 400 AND 600
not between a not between b and c Tests that operand a is less than operand b or greater than operand c. select * from table
where b not between 400 and 600
exists exists (query) Tests that the query returns at least one row. select * from table e
where exists
(select a
from table1 d
where a= e.a)

not exists

not exists (query)

 Tests that the query does not return a row. select * from table e
where not exists
(select a
from table1 d
where a=e.a)
LIKE a LIKE b Tests that operand a matches pattern operand b. The pattern may contain _, which matches a single character in that position, or %, which matches all characters. select * from table
where ename LIKE
`SMI%'
not LIKE a not LIKE b Tests that operand a does not match pattern operand b. The pattern may contain _, which matches a single character in that position, or %, which matches all characters. select * from table
where ename not
LIKE `SMI%'
IS NULL a IS NULL Tests that operand a is NULL. select * from table
where comm IS
NULL
IS not NULL a IS not NULL Tests that operand a is not NULL. select * from table
where comm IS not
NULL

 

 

Logical Operators

SQL provides logical operators that are similar to those available in most other programming languages. The logical operators AND and OR combine the results of two Boolean values to produce a single result based on them, while the logical operator not inverts a result. The Boolean values may be any expression that can be evaluated to TRUE or FALSE. Usually the values come from comparison expressions.

Operator Operand 1 Operand 2 Result
AND TRUE TRUE TRUE
  FALSE FALSE FALSE
  TRUE FALSE FALSE
  FALSE TRUE FALSE
  TRUE NULL NULL
  FALSE NULL FALSE
  NULL TRUE NULL
  NULL FALSE FALSE
  NULL

 

NULL

 

NULL

 

OR TRUE TRUE TRUE
  FALSE FALSE FALSE
  TRUE FALSE TRUE
  FALSE TRUE TRUE
  TRUE NULL TRUE
  FALSE NULL NULL
  NULL TRUE TRUE
  NULL FALSE NULL
  NULL

 

NULL

 

NULL

 

not TRUE   FALSE
  FALSE   TRUE
  NULL   NULL

 

 

Set Operators

Operator Description Example
union Combines all rows returned by two queries and eliminates duplicate rows. select * from table where a=50
union
select * from table where b > 500
union all Combines all rows returned by both queries and includes duplicate rows. select * from table where a=50
union all
select * from table where b > 500
minus Takes the rows returned by the first query, removes rows that are also returned by the second query, and returns the rows that remain. select * from table
minus
select * from table where b > 500
intersect Returns only the rows returned by both queries. select * from table where a = 10
intersect
select * from table where b>500

 

 

The SQL Operation

There are four basic operations performed by a SQL DML statement. Each of these is discussed in this section. Each operation is also the name of a SQL statement, and the detailed syntax for each statement can be found in Chapter 3, Data Manipulation and Control Statements.

select Retrieve data from the database. If no condition is specified, all rows of the target table(s) and/or view(s) are returned.
insert Create new rows of data in a target database table or view. The condition component does not apply to an insert.
update Modify data already in a database table or view. If no condition is specified, all rows of the target table are updated.
delete Remove rows from a database table or view. If no condition is specified, all rows of the target table or view are removed.

 

 

The Target

All SQL DML statements operate on one or more database tables or views. The purpose of the target component is to identify those tables or views. This component takes a different form depending on the statement with which it's being used. For example, the select and DELETE statements have similar target structures:

select *
from table          --This is the target component
where a = 10
 
DELETE
from table          --This is the target component 
where a = 10

The insert and update statements, however, use the target differently:

insert INTO table   
   (empno, ename, b, hiredate)  
VALUES ('1234','Dave Kreines',500,'06-01-00')
 
update table       
SET b = 600
where tableno = '1234'

 

 

Joins

When two or more tables or views are referenced as the target of a select statement, this is called a join. One of the fundamental concepts of a relational database is the ability to combine two or more tables into a single result set by specifying how the tables are related (thus the term relational ). Two or more tables or views are typically related to each other by one or more columns that share common data. Such a column is called a key column. An example of a key column might be a department number.

The target component specifies the tables or views to be included in a join, and the condition component tells Oracle how to relate the tables or views to each other.

The following example joins the table and table1 tables:

select ename, location
from table, table1
where table.a = table1.a

This statement instructs Oracle to return the name and location for each employee from the database by first forming all possible combinations of data rows from the two tables, and then returning all rows where the two department numbers match. Note that the number of rows in each table can be different; it is the data value that is used for the match. One row from the table1 table can, and probably will, match multiple rows of the table table.

The idea of forming all possible combinations of all rows by joining two tables is conceptual. Oracle almost always finds a more optimal way to generate the join results.

If no condition is specified for a join, all possible combinations of rows from the two tables are returned as the result. In other words, every row of the first table is matched with every row of the second table (assuming that two tables are joined). This result is known as a Cartesian product, and it is usually something you want to avoid. The number of rows returned is equivalent to the number of rows in the first table multiplied by the number of rows in the second. If your tables are large to begin with, the number of rows in the Cartesian product can become extremely large. Performance will suffer greatly by having to generate those rows, and they are not likely be of much use anyway. Cartesian products usually represent a mistake in writing a query.

The existence of two or more tables or views in the from clause, with no corresponding set of join conditions in the where clause, always results in a Cartesian product.

 

 

Outer join

In the standard join, rows are only returned when there are corresponding rows in each of the joined tables or views. An outer join allows data to be returned even if no matching row exists in one of the tables. The outer join is specified by adding (+) to the end of the column names for the table that you want to make optional. In other words, (+) means "add a phantom row to this table that contains NULL values for all columns if a matching row does not exist." Here is an example of an outer join that displays a NULL location if there is no matching department number in the table1 table:

select ename, location
from table, table1
where table.a = table1.a(+)

Normally, a query joining the table and table1 tables would return rows for only the employee who had been assigned to a valid department. By adding (+) to the end of the table1.a column name, we make the join into an outer join. Rows are now returned for all employee, whether or not they have a valid department assignment.

 

 

The Condition

The condition component, which is specified using the where clause, identifies the specific rows to be operated on by a select, update, or DELETE statement. While a where clause may be very complex, it ultimately evaluates to either TRUE or FALSE for each row of data, and that action controls whether or not the operation takes place for each row. Consider the following query:

select * 
from table
where b > 500

Oracle will look at each row of data in the table table and evaluate the condition b > 500. Rows for which this expression evaluates to TRUE will be returned, while those for which this condition evaluates to FALSE or unknown (those with values of b that are less than or equal to 500 or that contain NULL) will not be returned.

Another important use for the where clause is to identify the columns that relate one table to another to perform a join. Here is a query that includes a simple join:

select ename, loc
from table, table1
where table.a = table1.a

Both the table and table1 tables have a column called a, which contains the department number. The columns do not need to have the same name, but they do have to contain data with the same meaning. In this example, each table row contains a department number in a column named a. The department number also exists in the a column of the table1 table. Since we know that a department number in the table table has the same meaning as one in the table1 table, these columns can be used to specify a join condition.

In the example, the column name (a) was prefixed by the name of the table (emp or table1). This prefix occurs because both tables in the join have identically named columns. As a result, you need to qualify the column names with the table names so Oracle knows which column you are referring to. Oracle would return an error if you failed to do this because the column names would then be ambiguous. What is intended may be obvious to you, but not to Oracle!

 

 

SQL Statements

Statement Description
ALTER CLUSTER Redefines future storage allocations or allocates an extent for a cluster
ALTER DATABASE Changes one or more characteristics of an existing database
ALTER FUNCTION Recompiles a stored PL/SQL function
ALTER INDEX Changes the characteristics of an index
ALTER MATERIALIZED VIEW Changes the storage characteristics or automatic refresh characteristics of a materialized view or snapshot
ALTER MATERIALIZED VIEW LOG Changes the storage characteristics of a materialized view log
ALTER PACKAGE Recompiles a PL/SQL package
ALTER PROCEDURE Recompiles a PL/SQL stored procedure
ALTER PROFILE Adds, changes, or removes a resource limit from an existing profile
ALTER RESOURCE COST Modifies the formula calculating the total resource cost used in a session
ALTER ROLE Changes the authorization level required to enable a role
ALTER ROLLBACK SEGMENT Changes the online status of a rollback segment or modifies its storage characteristics
ALTER SEQUENCE Changes the characteristics of an Oracle sequence
ALTER SESSION Changes the functional characteristics of the current database session
ALTER SNAPSHOT Changes the storage characteristics or automatic refresh characteristics of a snapshot
ALTER SNAPSHOT LOG Changes the storage characteristics of a snapshot log
ALTER SYSTEM Makes dynamic changes to the database instance
ALTER table Modifies the characteristics of a table
ALTER tableSPACE Changes the characteristics of an existing tablespace
ALTER TRIGGER Recompiles a PL/SQL trigger
ALTER USER Changes the security and storage characteristics of a user
ALTER VIEW Recompiles a view
ANALYZE Collects or deletes statistics about an object in the database
ASSOCIATE STATISTICS Associates a method of statistics computation with database objects
AUDIT Sets up auditing for specific SQL statements in subsequent user sessions
AUDIT Sets up auditing for a specific schema object
CALL Executes a stored PL/SQL procedure
COMMENT Adds a comment about a table, view, snapshot, or column
CREATE CLUSTER Creates a cluster that contains at least one table with one or more columns in common
CREATE CONtrOLFILE Recreates a control file, allowing changes to some parameters
CREATE DATABASE Creates a database and specifies parameters associated with it
CREATE DATABASE LINK Creates a database link to provide access to objects on a remote database
CREATE DIMENSION Creates a dimension that defines a parent-child relationship between pairs of column sets
CREATE DIRECTORY Creates a directory object that specifies an operating system directory for storing BFILE objects
CREATE FUNCTION Creates a stored PL/SQL function
CREATE INDEX Creates an index on at least one column of a table or cluster
CREATE MATERIALIZED VIEW Creates a materialized view, also called a snapshot
CREATE MATERIALIZED VIEW LOG Creates a materialized view log
CREATE PACKAGE Creates a PL/SQL package
CREATE PROCEDURE Creates a PL/SQL stored procedure
CREATE PROFILE Creates a profile to set limits on database resources
CREATE ROLE Creates a role, which is a set of privileges that can be granted to users
CREATE ROLLBACK SEGMENT Creates a rollback segment, which is used by Oracle to store data necessary to roll back changes made by transactions
CREATE SCHEMA Creates multiple tables and/or views, and issues grants in a single statement
CREATE SEQUENCE Creates an Oracle sequence used to automatically generate sequential numbers
CREATE SNAPSHOT Creates a snapshot (or materialized view)
CREATE SNAPSHOT LOG Creates a snapshot log
CREATE table Creates a table by specifying the structure or referencing an existing table
CREATE tableSPACE Creates a new tablespace, optionally specifying default storage characteristics for objects subsequently created in the tablespace
CREATE TRIGGER Creates a PL/SQL trigger
CREATE TEMPORARY tableSPACE Creates a temporary tablespace
CREATE USER Creates a new database user and assigns security and storage properties
CREATE VIEW Create a view
CREATE SYNONYM Creates a public or private synonym for a database object
DROP SYNONYM Removes a public or private synonym from the database
DELETE Deletes one or more rows from a table, view, or snapshot
DISASSOCIATE STATISTICS Disassociates a method of statistics computation from database objects
DROP CLUSTER Removes a cluster from the database
DROP DATABASE LINK Removes a database link from the database
DROP DIMENSION Removes a dimension from the database
DROP DIRECTORY Removes a directory object from the database
DROP FUNCTION Removes a stored PL/SQL function
DROP INDEX Removes an index from the database
DROP MATERIALIZED VIEW Removes a materialized view (or snapshot) from the database
DROP MATERIALIZED VIEW LOG Removes a materialized view log from the database
DROP PACKAGE Removes a PL/SQL package from the database
DROP PROCEDURE Removes a PL/SQL stored procedure from the database
DROP PROFILE Removes a profile from the database
DROP ROLE Removes a role from the database
DROP ROLLBACK SEGMENT Removes a rollback segment from the database
DROP SEQUENCE Removes a sequence from the database
DROP SNAPSHOT Removes a snapshot from the database
DROP SNAPSHOT LOG Removes a snapshot log from the database
DROP table Removes a table from the database
DROP tableSPACE Removes a tablespace from the database
DROP TRIGGER Removes a PL/SQL trigger from the database
DROP USER Removes a user from the database
DROP VIEW Remove a view from the database
EXPLAIN PLAN Creates an explanation of the execution plan for a SQL statement
GRANT Grants a system privilege or role to one or more users and/or roles
GRANT Grants privileges on a database object to one or more users or roles
insert Inserts a row of data into a table or view
NOAUDIT Stops auditing defined by a prior AUDIT statement for schema objects
NOAUDIT Stops auditing defined by a prior AUDIT statement for SQL statements
RENAME Changes the name of an existing table, view, sequence, or private synonym
REVOKE Removes a system privilege or role from one or more users and/or roles
REVOKE Revokes privileges on a database object from one or more users or roles
SAVEPOINT Identifies a point in a transaction to which you can roll back using the ROLLBACK command
select Retrieves data from a table, view, or snapshot
SET CONSTRAINT Specifies at the transaction level how constraints are checked
SET ROLE Enables or disables roles for the current session
SET TRANSACTION Establishes the current transaction as read-only or read-write, or specifies the rollback segment to be used by the transaction
TRUNCATE Removes all rows from a table or cluster
update Changes the value stored in column of data in one or more tables, views, or snapshots