Datasheet

Writing Simple Queries 45
The following example categorizes the salary as Low, Medium, and High using a
searched
CASE expression:
SELECT first_name, department_id, salary,
CASE WHEN salary < 6000 THEN ‘Low’
WHEN salary < 10000 THEN ‘Medium’
WHEN salary >= 10000 THEN ‘High’ END Category
FROM employees
WHERE department_id <= 30
ORDER BY first_name;
FIRST_NAME DEPARTMENT_ID SALARY CATEGO
-------------------- ------------- ---------- ------
Alexander 30 3100 Low
Den 30 11000 High
Guy 30 2600 Low
Jennifer 10 4400 Low
Karen 30 2500 Low
Michael 20 13000 High
Pat 20 6000 Medium
Shelli 30 2900 Low
Sigal 30 2800 Low
9 rows selected.
Oracle uses the & (ampersand) character to substitute values at runtime. In the next sec-
tion, I will discuss how to create SQL statements that can be used to get a different set of
results based on values passed during execution time.
Finding the Current Sessions and Program Name
As a DBA you may have to query the V$SESSION dictionary view to find the current ses-
sions in the database. This view has several columns that show various information about
the session; often the DBA is interested in finding out the username and which program
is connecting to the database. If the DBA wants to find out what SQL is executed in the
session, the
SID and SERIAL# columns can be queried to enable tracing using the DBMS_
TRACE
package.
I’ll review in this example how to query the
V$SESSION view using the simple SQL state-
ments you learned in this chapter.
95127c01.indd 45 2/18/09 6:37:11 AM