You can connect to Oracle Database only through a client program, such as SQL*Plus or SQL Developer.
- Download Toad For Oracle Version
- Toad For Oracle 14.1 Download
- Download Toad For Oracle Freeware 12.1 (x64 & X32) Free
- Toad For Oracle Download
- Toad For Oracle 12 Youtube
Note:
Then, get your free, 30-day trial of Toad® to save hours, increase accuracy and enjoy easier workdays. If you are a licensed customer and are looking to upgrade to our latest version of Toad, please visit the support portal. For more details on what’s available for each Toad for Oracle Edition, please refer to this data sheet.
After connecting to Oracle Database through a client program, you enter and run commands in that client program. For details, see the documentation for your client program.This chapter contains:
Connecting to Oracle Database from SQL*Plus
Jan 24, 2013 Toad for Oracle is a comprehensive database tool for development and administration that enables you to perform daily tasks efficiently and accurately. Only Toad combines the deepest functionality. Toad For Oracle Freeware 12.1 Download Toad® for Oracle is a comprehensive database tool for development and administration that enables you to perform daily tasks efficiently and accurately. It has grown over the past 10 years into the most widely-used tool of its kind. Toad ® for Oracle is the only developer tool that helps you simplify your workflow, reduce code defects and improve code quality and performance while supporting team collaboration. Automate administration tasks and proactively manage your databases while embracing performance optimization and risk mitigation.
SQL*Plus is a client program with which you can access Oracle Database. This section shows how to start SQL*Plus and connect to Oracle Database.
Note:
For steps 3 and 4 of the following procedure, you need a user name and password.To connect to Oracle Database from SQL*Plus:
If you are on a Windows system, display a Windows command prompt.
At the command prompt, type
sqlplus
and then press the key Enter.At the user name prompt, type your user name and then press the key Enter.
At the password prompt, type your password and then press the key Enter.
Note:
For security, your password is not visible on your screen.The system connects you to an Oracle Database instance.
You are in the SQL*Plus environment. At the
SQL>
prompt, you can enter and run SQL*Plus commands, SQL statements, PL/SQL statements, and operating system commands.To exit SQL*Plus, type
exit
and press the key Enter.Note:
Exiting SQL*Plus ends the SQL*Plus session, but does not shut down the Oracle Database instance.Example 2-1 starts SQL*Plus, connects to Oracle Database, runs a SQL
SELECT
statement, and exits SQL*Plus. User input is bold.
Example 2-1 Connecting to Oracle Database from SQL*Plus
See Also:
'About SQL*Plus' for a brief description of SQL*Plus
SQL*Plus User's Guide and Reference for more information about starting SQL*Plus and connecting to Oracle Database
Connecting to Oracle Database from SQL Developer
SQL Developer is a client program with which you can access Oracle Database. With Oracle Database 11g Release 2 (11.2), Oracle recommends using SQL Developer version 4.0 or later, which you can download from:
This section assumes that SQL Developer is installed on your system, and shows how to start it and connect to Oracle Database. If SQL Developer is not installed on your system, then see Oracle Database SQL Developer User's Guide for installation instructions.
Note:
For the following procedure:If you're using a SQL Developer kit that does not include the JDK, then the first time you start SQL Developer on your system, you must provide the full path to
java
.exe
in step 1.For step 3, you need a user name and password.
To connect to Oracle Database from SQL Developer:
Start SQL Developer.
For instructions, see Oracle Database SQL Developer User's Guide.
If this is the first time you have started SQL Developer on your system, you are prompted to enter the full path to
java
.exe
(for example,C:Program FilesJavajdk1.6.0_021binjava.exe
). Either type the full path after the prompt or browse to it, and then press the key Enter.In the Connections frame, click the icon New Connection.
In the New/Select Database Connection window:
Type the appropriate values in the fields Connection Name, Username, and Password.
For security, the password characters that you type appear as asterisks.
Near the Password field is the check box Save Password. By default, it is deselected. Oracle recommends accepting the default.
If the Oracle pane is not showing, click the tab Oracle.
In the Oracle pane, accept the default values.
(The default values are: Connection Type, Basic; Role, default, Hostname, localhost; Port, 1521; SID option, selected; SID field, xe.)
Click the button Test.
The connection is tested. If the connection succeeds, the Status indicator changes from blank to
Success
.
Description of the illustration success.gifIf the test succeeded, click the button Connect.
The New/Select Database Connection window closes. The Connections frame shows the connection whose name you entered in the Connection Name field in step 3.
You are in the SQL Developer environment.
To exit SQL Developer, select Exit from the File menu.
Note:
Exiting SQL Developer ends the SQL Developer session, but does not shut down the Oracle Database instance. The next time you start SQL Developer, the connection you created using the preceding procedure still exists. SQL Developer prompts you for the password that you supplied in step 3 (unless you selected the check box Save Password).See Also:
'About SQL Developer' for a brief description of SQL Developer
Oracle Database SQL Developer User's Guide for more information about using SQL Developer to create connections to Oracle Database
Connecting to Oracle Database as User HR
This section shows how to unlock the HR
account and connect to Oracle Database as the user HR
, who owns the HR
sample schema that the examples and tutorials in this document use.
To do the tutorials and examples in this document, you must connect to Oracle Database as the user HR
.
This section contains:
Unlocking the HR Account
This section shows how to unlock the HR
account and reset its password.
By default, when the HR
schema is installed, the HR
account is locked and its password is expired. You can connect to Oracle Database as the user HR
only if the HR
account is unlocked.
Note:
For the following procedure, you need the name and password of a user who has theALTER
USER
system privilege.To unlock the HR account and reset its password:
Using SQL*Plus, connect to Oracle Database as a user with the
ALTER
USER
system privilege.At the
SQL>
prompt, unlock theHR
account and reset its password:Caution:
Choose a secure password. For guidelines for secure passwords, see Oracle Database Security Guide.The system responds:
The
HR
account is unlocked and its password ispassword
.
Now you can connect to Oracle Database as user HR
with the password password
.
See Also:
Oracle Database 2 Day + Security Guide for information about predefined user accounts provided by Oracle Database, including
HR
Oracle Database SQL Developer User's Guide for information about accessing SQL*Plus within SQL Developer
Connecting to Oracle Database as User HR from SQL*Plus
This section shows how to connect to Oracle Database as the user HR
from SQL*Plus.
Note:
If the HR account is locked, see 'Unlocking the HR Account' and then return to this section.To connect to Oracle Database as user HR from SQL*Plus:
Note:
For this task, you need the password for theHR
account.If you are connected to Oracle Database, close your current connection.
Follow the directions in 'Connecting to Oracle Database from SQL*Plus', entering the user name
HR
at step 3 and the password for theHR
account at step 4.You are now connected to Oracle Database as the user
HR
.
See Also:
SQL*Plus User's Guide and Reference for an example of using SQL*Plus to create anHR
connectionConnecting to Oracle Database as User HR from SQL Developer
This section shows how to connect to Oracle Database as the user HR
from SQL Developer.
Note:
If the HR account is locked, see 'Unlocking the HR Account' and then return to this section.To connect to Oracle Database as user HR from SQL Developer:
Note:
For this task, you need the password for theHR
account.Follow the directions in 'Connecting to Oracle Database from SQL Developer', entering the following values at steps 3:
For Connection Name, enter
hr_conn
.(You can enter a different name, but the tutorials in this document assume that you named the connection
hr_conn
.)For Username, enter
HR
.For Password, enter the password for the
HR
account.
You are now connected to Oracle Database as the user HR
.
Exploring Oracle Database with SQL*Plus
This section contains:
Note:
If you are not connected to Oracle Database as userHR
from SQL*Plus, see 'Connecting to Oracle Database as User HR from SQL*Plus' and then return to this section.Viewing HR Schema Objects with SQL*Plus
With SQL*Plus, you can view the objects that belong to the HR
schema by querying the static data dictionary view USER_OBJECTS
.
Example 2-2 shows how to view the names and data types of the objects that belong to the HR
schema.
Example 2-2 Viewing HR Schema Objects with SQL*Plus
Result is similar to:
See Also:
Oracle Database Reference for information about
USER_OBJECTS
'Selecting Table Data' for information about using queries to view table data
'About Sample Schema HR' for general information about the schema
HR
Viewing EMPLOYEES Table Properties and Data with SQL*Plus
With SQL*Plus, you can use the SQL*Plus command DESCRIBE
to view the properties of the columns of the EMPLOYEES
table in the HR
schema and the SQL statement SELECT
to view the data. To view other properties of the table, use static data dictionary views (for example, USER_CONSTRAINTS
, USER_INDEXES
, and USER_TRIGGERS
).
Example 2-3 shows how to view the properties of the EMPLOYEES
table in the HR
schema.
Example 2-3 Viewing EMPLOYEES Table Properties with SQL*Plus
Result:
Example 2-4 shows how to view some data in the EMPLOYEES
table in the HR
schema.
Example 2-4 Viewing EMPLOYEES Table Data with SQL*Plus
Result is similar to:
See Also:
SQL*Plus User's Guide and Reference for information about
DESCRIBE
'Selecting Table Data' for information about using queries to view table data
Oracle Database Reference for information about static data dictionary views
Exploring Oracle Database with SQL Developer
This section contains:
Tutorial: Viewing HR Schema Objects with SQL Developer
This tutorial shows how to use SQL Developer to view the objects that belong to the HR
schema—that is, how to browse the HR
schema.
Note:
If you are not connected to Oracle Database as userHR
from SQL Developer, see 'Connecting to Oracle Database as User HR from SQL Developer' and then return to this tutorial.To browse the HR schema:
In the Connections frame, to the left of the
hr_conn
icon, click the plus sign (+).If you are not connected to the database, the Connection Information window opens. If you are connected to the database, the
hr_conn
information expands (see the information that follows 'Click OK' in step 2).If the Connection Information window opens:
In the User Name field, enter
hr
.In the Password field, enter the password for the user
hr
.Click OK.
The
hr_conn
information expands: The plus sign becomes a minus sign (-), and under thehr_conn
icon, a list of schema object types appears—Tables, Views, Indexes, and so on. (If you click the minus sign, thehr_conn
information collapses: The minus sign becomes a plus sign, and the list disappears.)
Download Toad For Oracle Version
See Also:
Oracle Database SQL Developer User's Guide for more information about the SQL Developer user interface
'About Sample Schema HR' for general information about schema
HR
Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer
This tutorial shows how to use SQL Developer to view the properties and data of the EMPLOYEES
table in the HR
schema.
Note:
If you are not browsing theHR
schema, see 'Tutorial: Viewing HR Schema Objects with SQL Developer' and then return to this tutorial.To view the properties and data of the EMPLOYEES table:
In the Connections frame, expand Tables.
Under Tables, a list of the tables in the
HR
schema appears.Select the table EMPLOYEES.
In the right frame of the Oracle SQL Developer window, in the Columns pane, a list of all columns of this table appears. To the right of each column are its properties—name, data type, and so on. (To see all column properties, move the horizontal scroll bar to the right.)
In the right frame, click the tab Data.
The Data pane appears, showing a numbered list of all records in this table. (To see more records, move the vertical scroll bar down. To see more columns of the records, move the horizontal scroll bar to the right.)
In the right frame, click the tab Constraints.
The Constraints pane appears, showing a list of all constraints on this table. To the right of each constraint are its properties—name, type, search condition, and so on. (To see all constraint properties, move the horizontal scroll bar to the right.)
Explore the other properties by clicking on the appropriate tabs.
To see the SQL statement for creating the
EMPLOYEES
table, click the SQL tab. The SQL statement appears in a pane namedEMPLOYEES
. To close this pane, click the x to the right of the nameEMPLOYEES
.
See Also:
Oracle Database SQL Developer User's Guide for more information about the SQL Developer user interfaceSelecting Table Data
This section contains:
Note:
To do the tutorials and examples in this section, you must be connected to Oracle Database as the userHR
from SQL Developer. For instructions, see 'Connecting to Oracle Database as User HR from SQL Developer'.About Queries
A query, or SQL SELECT
statement, selects data from one or more tables or views.
The simplest form of query has this syntax:
The select_list
specifies the columns from which the data is to be selected, and the source_list
specifies the tables or views that have these columns.
A query nested within another SQL statement is called a subquery.
In the SQL*Plus environment, you can enter a query (or any other SQL statement) after the SQL>
prompt.
In the SQL Developer environment, you can enter a query (or any other SQL statement) in the Worksheet.
Note:
When the result of a query is displayed, records can be in any order, unless you specify their order with theORDER
BY
clause. For more information, see 'Sorting Selected Data'.See Also:
Oracle Database SQL Language Reference for more information about queries and subqueries
Oracle Database SQL Language Reference for more information about the
SELECT
statementSQL*Plus User's Guide and Reference for more information about the SQL*Plus command line interface
Oracle Database SQL Developer User's Guide for information about using the Worksheet in SQL Developer
Running Queries in SQL Developer
This section explains how to run queries in SQL Developer, by using the Worksheet.
Note:
The Worksheet is not limited to queries; you can use it to run any SQL statement.To run queries in SQL Developer:
If the right frame of SQL Developer shows the
hr_conn
pane:If the Worksheet subpane does not show, click the tab Worksheet.
Go to step 3.
Click the icon SQL Worksheet.
If the Select Connection window opens:
If the Connection field does not have the value
hr_conn
, select that value from the menu.Click OK.
A pane appears with a tab labeled
hr_conn
and two subpanes, Worksheet and Query Builder. In the Worksheet, you can enter a SQL statement.In the Worksheet, type a query (a
SELECT
statement).Click the icon Run Statement.
The query runs. Under the Worksheet, the Query Result pane appears, showing the query result.
Under the
hr_conn
tab, click the icon Clear.The query disappears, and you can enter another SQL statement in the Worksheet. When you run another SQL statement, its result appears in the Query Result pane, replacing the result of the previously run SQL statement.
See Also:
Oracle Database SQL Developer User's Guide for information about using the Worksheet in SQL DeveloperTutorial: Selecting All Columns of a Table
This tutorial shows how to select all columns of the EMPLOYEES
table.
To select all columns of the EMPLOYEES Table:
If a pane with the tab
hr_conn
is there, select it. Otherwise, click the icon SQL Worksheet, as in 'Running Queries in SQL Developer'.In the Worksheet, enter this query:
Click the icon Run Statement.
The query runs. Under the Worksheet, the Query Result pane appears, showing all columns of the
EMPLOYEES
table.
Caution:
Be very careful about usingSELECT
*
on tables with columns that store sensitive data, such as passwords or credit card information.See Also:
'Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer' for information about another way to view table data with SQL DeveloperTutorial: Selecting Specific Columns of a Table
This tutorial shows how to select only the columns FIRST_NAME
, LAST_NAME
, and DEPARTMENT_ID
of the EMPLOYEES
table.
To select only FIRST_NAME, LAST_NAME, and DEPARTMENT_ID:
If a pane with the tab
hr_conn
is there, select it. Otherwise, click the icon SQL Worksheet, as in 'Running Queries in SQL Developer'.If the Worksheet pane contains a query, clear the query by clicking the icon Clear.
In the Worksheet, enter this query:
Click the icon Run Statement.
The query runs. Under the Worksheet, the Query Result pane appears, showing the results of the query, which are similar to:
Displaying Selected Columns Under New Headings
When query results are displayed, the default column heading is the column name. To display a column under a new heading, specify the new heading (alias) immediately after the name of the column. The alias renames the column for the duration of the query, but does not change its name in the database.
The query in Example 2-5 selects the same columns as the query in 'Tutorial: Selecting Specific Columns of a Table', but it also specifies aliases for them. Because the aliases are not enclosed in double quotation marks, they are displayed in uppercase letters.
Example 2-5 Displaying Selected Columns Under New Headings
Result is similar to:
If you enclose column aliases in double quotation marks, case is preserved, and the aliases can include spaces, as in Example 2-6.
Example 2-6 Preserving Case and Including Spaces in Column Aliases
Result is similar to:
See Also:
Oracle Database SQL Language Reference for more information about theSELECT
statement, including the column alias (c_alias
)Selecting Data that Satisfies Specified Conditions
To select only data that matches a specified condition, include the WHERE
clause in the SELECT
statement. The condition in the WHERE
clause can be any SQL condition (for information about SQL conditions, see Oracle Database SQL Language Reference).
The query in Example 2-7 selects data only for employees in department 90.
Example 2-7 Selecting Data from One Department
Result is similar to:
To select data only for employees in departments 100, 110, and 120, use this WHERE
clause:
The query in Example 2-8 selects data only for employees whose last names start with 'Ma'.
Example 2-8 Selecting Data for Last Names that Start with the Same Substring
Result is similar to:
To select data only for employees whose last names include 'ma', use this WHERE
clause:
The query in Example 2-9 tests for two conditions—whether the salary is at least 11000, and whether the commission percentage is not null.
Example 2-9 Selecting Data that Satisfies Two Conditions
Result is similar to:
See Also:
Oracle Database SQL Language Reference for more information about the
SELECT
statement, including theWHERE
clauseOracle Database SQL Language Reference for more information about SQL conditions
Sorting Selected Data
When query results are displayed, records can be in any order, unless you specify their order with the ORDER
BY
clause.
The query results in Example 2-10 are sorted by LAST_NAME
, in ascending order (the default).
Alternatively, in SQL Developer, you can omit the ORDER
BY
clause and double-click the name of the column to sort.
Example 2-10 Sorting Selected Data by LAST_NAME
Result:
The sort criterion need not be included in the select list, as Example 2-11 shows.
Example 2-11 Sorting Selected Data by an Unselected Column
Result:
See Also:
Oracle Database SQL Language Reference for more information about theSELECT
statement, including the ORDER
BY
clauseSelecting Data from Multiple Tables
Suppose that you want to select the FIRST_NAME
, LAST_NAME
, and DEPARTMENT_NAME
of every employee. FIRST_NAME
and LAST_NAME
are in the EMPLOYEES
table, and DEPARTMENT_NAME
is in the DEPARTMENTS
table. Both tables have DEPARTMENT_ID
. You can use the query in Example 2-12. Such a query is called a join.
Example 2-12 Selecting Data from Two Tables (Joining Two Tables)
Result:
Table-name qualifiers are optional for column names that appear in only one table of a join, but are required for column names that appear in both tables. The following query is equivalent to the query in Example 2-12:
To make queries that use qualified column names more readable, use table aliases, as in the following example:
Although you create the aliases in the FROM
clause, you can use them earlier in the query, as in the following example:
See Also:
Oracle Database SQL Language Reference for more information about joinsUsing Operators and Functions in Queries
The select_list
of a query can include SQL expressions, which can include SQL operators and SQL functions. These operators and functions can have table data as operands and arguments. The SQL expressions are evaluated, and their values appear in the results of the query.
This section contains:
See Also:
Oracle Database SQL Language Reference for more information about SQL operators
Oracle Database SQL Language Reference for more information about SQL functions
Using Arithmetic Operators in Queries
SQL supports the basic arithmetic operators: +
(addition), -
(subtraction), *
(multiplication), and /
(division).
The query in Example 2-13 displays LAST_NAME
, SALARY
(monthly pay), and annual pay for each employee in department 90, in descending order of SALARY
.
Example 2-13 Using an Arithmetic Expression in a Query
Result:
Using Numeric Functions in Queries
Numeric functions accept numeric input and return numeric values. Each numeric function returns a single value for each row that is evaluated. The numeric functions that SQL supports are listed and described in Oracle Database SQL Language Reference.
The query in Example 2-14 uses the numeric function ROUND
to display the daily pay of each employee in department 100, rounded to the nearest cent.
The query in Example 2-15 uses the numeric function TRUNC
to display the daily pay of each employee in department 100, truncated to the nearest dollar.
Example 2-15 Truncating Numeric Data
Result:
See Also:
Oracle Database SQL Language Reference for more information about SQL numeric functionsUsing the Concatenation Operator in Queries
The concatenation operator (||) combines two strings into one string, by appending the second string to the first. For example, 'a'||'b'='ab'
. You can use this operator to combine information from two columns or expressions in the same column of the report, as in the query in Example 2-16.
See Also:
Oracle Database SQL Language Reference for more information about the concatenation operatorUsing Character Functions in Queries
Character functions accept character input. Most return character values, but some return numeric values. Each character function returns a single value for each row that is evaluated. The character functions that SQL supports are listed and described in Oracle Database SQL Language Reference.
The functions UPPER
, INITCAP
, and LOWER
display their character arguments in uppercase, initial capital, and lowercase, respectively.
The query in Example 2-17 displays LAST_NAME
in uppercase, FIRST_NAME
with the first character in uppercase and all others in lowercase, and EMAIL
in lowercase.
Example 2-17 Changing the Case of Character Data
Result:
See Also:
Oracle Database SQL Language Reference for more information about SQL character functionsUsing Datetime Functions in Queries
Datetime functions operate on DATE
, time stamp, and interval values. Each datetime function returns a single value for each row that is evaluated. The datetime functions that SQL supports are listed and described in Oracle Database SQL Language Reference.
For each DATE
and time stamp value, Oracle Database stores this information:
Year
Month
Date
Hour
Minute
Second
For each time stamp value, Oracle Database also stores the fractional part of the second, whose precision you can specify. To store the time zone also, use the data type
TIMESTAMP
WITH
TIME
ZONE
orTIMESTAMP
WITH
LOCAL
TIME
ZONE
.
For more information about the DATE
data type, see Oracle Database SQL Language Reference.
For more information about the TIMESTAMP
data type, see Oracle Database SQL Language Reference.
For information about the other time stamp data types and the interval data types, see Oracle Database SQL Language Reference.
The query in Example 2-18 uses the EXTRACT
and SYSDATE
functions to show how many years each employee in department 100 has been employed. The SYSDATE
function returns the current date of the system clock as a DATE
value. For more information about the SYSDATE
function, see Oracle Database SQL Language Reference. For information about the EXTRACT
function, see Oracle Database SQL Language Reference.
Example 2-18 Displaying the Number of Years Between Dates
Result:
The query in Example 2-19 uses the SYSTIMESTAMP
function to display the current system date and time. The SYSTIMESTAMP
function returns a TIMESTAMP
value. For information about the SYSTIMESTAMP
function, see Oracle Database SQL Language Reference.
The table in the FROM
clause of the query, DUAL
, is a one-row table that Oracle Database creates automatically along with the data dictionary. Select from DUAL
when you want to compute a constant expression with the SELECT
statement. Because DUAL
has only one row, the constant is returned only once. For more information about selecting from DUAL
, see Oracle Database SQL Language Reference.
Example 2-19 Displaying System Date and Time
Results depend on current SYSTIMESTAMP
value, but have this format:
See Also:
Oracle Database SQL Language Reference for more information about SQL datetime functionsUsing Conversion Functions in Queries
Conversion functions convert one data type to another. The conversion functions that SQL supports are listed and described in Oracle Database SQL Language Reference.
The query in Example 2-20 uses the TO_CHAR
function to convert HIRE_DATE
values (which are of type DATE
) to character values that have the format FMMonth
DD
YYYY
. FM
removes leading and trailing blanks from the month name. FMMonth
DD
YYYY
is an example of a datetime format model. For information about datetime format models, see Oracle Database SQL Language Reference.
Example 2-20 Converting Dates to Characters Using a Format Template
Result:
The query in Example 2-21 uses the TO_NUMBER
function to convert POSTAL_CODE
values (which are of type VARCHAR2
) to values of type NUMBER
, which it uses in calculations.
Example 2-21 Converting Characters to Numbers
Result:
See Also:
Oracle Database SQL Language Reference for more information about SQL conversion functions
Using Aggregate Functions in Queries
An aggregate function takes a group of rows and returns a single result row. The group of rows can be an entire table or view. The aggregate functions that SQL supports are listed and described in Oracle Database SQL Language Reference.
Aggregate functions are especially powerful when used with the GROUP
BY
clause, which groups query results by one or more columns, with a result for each group.
The query in Example 2-22 uses the COUNT
function and the GROUP
BY
clause to show how many people report to each manager. The wildcard character, *
, represents an entire record.
Example 2-22 Counting the Number of Rows in Each Group
Result:
Example 2-22 shows that one employee does not report to a manager. The following query selects the first name, last name, and job title of that employee:
Result:
To have the query return only rows where aggregate values meet specified conditions, use an aggregate function in the HAVING
clause of the query.
The query in Example 2-23 shows how much each department spends annually on salaries, but only for departments for which that amount exceeds $1,000,000.
Example 2-23 Limiting Aggregate Functions to Rows that Satisfy a Condition
Result:
The query in Example 2-24 uses several aggregate functions to show statistics for the salaries of each JOB_ID
.
Example 2-24 Using Aggregate Functions for Statistical Information
Result:
See Also:
Oracle Database SQL Language Reference for more information about SQL aggregate functionsUsing NULL-Related Functions in Queries
The NULL
-related functions facilitate the handling of NULL
values. The NULL
-related functions that SQL supports are listed and described in Oracle Database SQL Language Reference.
The query in Example 2-25 returns the last name and commission of the employees whose last names begin with 'B'
. If an employee receives no commission (that is, if COMMISSION_PCT
is NULL
), the NVL
function substitutes 'Not Applicable' for NULL
.
Example 2-25 Substituting a String for a NULL Value
Result:
The query in Example 2-26 returns the last name, salary, and income of the employees whose last names begin with 'B'
, using the NVL2
function: If COMMISSION_PCT
is not NULL
, the income is the salary plus the commission; if COMMISSION_PCT
is NULL
, income is only the salary.
Example 2-26 Specifying Different Expressions for NULL and Not NULL Values
Result:
See Also:
Oracle Database SQL Language Reference for more information about the
NVL
functionOracle Database SQL Language Reference for more information about the
NVL2
function
Using CASE Expressions in Queries
A CASE
expression lets you use IF
... THEN
... ELSE
logic in SQL statements without invoking subprograms. There are two kinds of CASE
expressions, simple and searched.
The query in Example 2-27 uses a simple CASE
expression to show the country name for each country code.
Example 2-27 Using a Simple CASE Expression in a Query
Result:
Toad For Oracle 14.1 Download
The query in Example 2-28 uses a searched CASE
expression to show proposed salary increases, based on length of service.
Example 2-28 Using a Searched CASE Expression in a Query
Result:
See Also:
Oracle Database SQL Language Reference for more information about
CASE
expressionsOracle Database PL/SQL Language Reference for more information about
CASE
expressions
Using the DECODE Function in Queries
The DECODE
function compares an expression to several search values. Whenever the value of the expression matches a search value, DECODE
returns the result associated with that search value. If DECODE
finds no match, then it returns the default value (if specified) or NULL
(if no default value is specified).
The query in Example 2-29 uses the DECODE
function to show proposed salary increases for three different jobs. The expression is JOB_ID
; the search values are 'PU_CLERK'
, 'SH_CLERK'
, and 'ST_CLERK'
; and the default is SALARY
.
Example 2-29 Using the DECODE Function in a Query
Download Toad For Oracle Freeware 12.1 (x64 & X32) Free
Result:
Note:
The arguments of theDECODE
function can be any of the SQL numeric or character types. Oracle automatically converts the expression and each search value to the data type of the first search value before comparing. Oracle automatically converts the return value to the same data type as the first result. If the first result has the data type CHAR
or if the first result is NULL
, then Oracle converts the return value to the data type VARCHAR2
Toad For Oracle Download
.See Also:
Toad For Oracle 12 Youtube
Oracle Database SQL Language Reference for information about the
DECODE
function