Connect to a Database

There are multiple ways to connect to a database from SQL*Plus. Before you can connect, however, you likely need to have entries for the databases to which you need to connect entered in the $ORACLE_HOME/network/admin/tnsnames.ora file. Two common ways to supply your connection information when you start SQL*Plus are shown in Listing 1-3; another is to use the SQL*Plus connect command after SQL*Plus starts, as shown in Listing 1-4.

Listing 1-3.  Connecting to SQL*Plus from the Windows Command Prompt

$ sqlplus hr@ora12c

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 7 12:32:36 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Enter password:

Last Successful login time: Tue May 07 2013 12:29:09 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

Listing 1-4.  Connecting to SQL*Plus and Logging in to the Database from the SQL> Prompt

$ sqlplus /nolog

SQL*Plus: Release 12.1.0.1.0 Production on Tue May 7 12:34:21 2013

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SQL> connect hr@ora12c
Enter password:
Connected.
SQL>

When starting SQL*Plus using the 12c version, you will notice a new feature that displays your last login time by default. If you don’t want this to appear, simply use the option –nologintime.

To start SQL*Plus without being prompted to log in to a database, start SQL*Plus with the /nolog option.

Configuring the SQL*Plus Environment

SQL*Plus has numerous commands that allow you to customize the working environment and display options. The SQL*Plus help data must be installed by the database administrator or it may not be available. There are three help script files found in $ORACLE_HOME/SQLPLUS/ADMIN/HELP/ that drop, create, and populate the SQL*Plus help tables: HLPBLD.SQL, HELPDROP.SQL, and HELPUS.SQL. Listing 1-5 shows the SQL*Plus commands available after entering the SQL*Plus help index command at the SQL> prompt.

Listing 1-5.  SQL*Plus Command List

SQL> help index

Enter Help [topic] for help.

The set command is the primary command used for customizing your environment settings. Listing 1-6 shows the help text for the set command.

Listing 1-6.  SQL*Plus SET Command

SQL> help set

 SET
 ---

 Sets a system variable to alter the SQL*Plus environment settings
 for your current session. For example, to:
     -   set the display width for data
     -   customize HTML formatting
     -   enable or disable printing of column headings
     -   set the number of lines per page

 SET system_variable value
 where system_variable and value represent one of the following clauses:

APPI[NFO]{OFF|ON|text}                       NEWP[AGE] {1|n|NONE}
ARRAY[SIZE] {15|n}                                  NULL text
AUTO[COMMIT] {OFF|ON|IMM[EDIATE]|n}          NUMF[ORMAT] format
AUTOP[RINT] {OFF|ON}                         NUM[WIDTH] {10|n}
AUTORECOVERY {OFF|ON}                        PAGES[IZE] {14|n}
AUTOT[RACE] {OFF|ON|TRACE[ONLY]}                    PAU[SE] {OFF|ON|text}
   [EXP[LAIN]] [STAT[ISTICS]]                RECSEP {WR[APPED]|EA[CH]|OFF}
BLO[CKTERMINATOR] {.|c|ON|OFF}                      RECSEPCHAR {_|c}
CMDS[EP] {;|c|OFF|ON}                        SERVEROUT[PUT] {ON|OFF}
COLSEP {_|text}                                             [SIZE {n | UNLIMITED}]
CON[CAT] {.|c|ON|OFF}                                [FOR[MAT]  {WRA[PPED] |
COPYC[OMMIT] {0|n}                                          WOR[D_WRAPPED] |
COPYTYPECHECK {ON|OFF}                               TRU[NCATED]}]
DEF[INE] {&|c|ON|OFF}                        SHIFT[INOUT] {VIS[IBLE] |
DESCRIBE [DEPTH {1|n|ALL}]                                         INV[ISIBLE]}
   [LINENUM {OFF|ON}] [INDENT {OFF|ON}]      SHOW[MODE] {OFF|ON}
ECHO {OFF|ON}                                       SQLBL[ANKLINES] {OFF|ON}
EDITF[ILE] file_name[.ext]                   SQLC[ASE] {MIX[ED] |
EMB[EDDED] {OFF|ON}                                  LO[WER] | UP[PER]}
ERRORL[OGGING] {ON|OFF}                             SQLCO[NTINUE] {> | text}
   [TABLE [schema.]tablename]                SQLN[UMBER] {ON|OFF}
   [TRUNCATE] [IDENTIFIER identifier]        SQLPLUSCOMPAT[IBILITY]
                                                            {x.y[.z]}
ESC[APE] {|c|OFF|ON}                        SQLPRE[FIX] {#|c}
ESCCHAR {@|?|%|$|OFF}                        SQLP[ROMPT] {SQL>|text}
EXITC[OMMIT] {ON|OFF}                        SQLT[ERMINATOR] {;|c|ON|OFF}
FEED[BACK] {6|n|ON|OFF}                             SUF[FIX] {SQL|text}
FLAGGER {OFF|ENTRY|INTERMED[IATE]|FULL}      TAB {ON|OFF}
FLU[SH] {ON|OFF}                                    TERM[OUT] {ON|OFF}
HEA[DING] {ON|OFF}                                  TI[ME] {OFF|ON}
HEADS[EP] {||c|ON|OFF}                       TIMI[NG] {OFF|ON}
INSTANCE [instance_path|LOCAL]                      TRIM[OUT] {ON|OFF}
LIN[ESIZE] {80|n}                                   TRIMS[POOL] {OFF|ON}
LOBOF[FSET] {1|n}                                   UND[ERLINE] {-|c|ON|OFF}
LOGSOURCE [pathname]                         VER[IFY] {ON|OFF}
LONG {80|n}                                                 WRA[P] {ON|OFF}
LONGC[HUNKSIZE] {80|n}                       XQUERY {BASEURI text|
MARK[UP] HTML [OFF|ON]                              ORDERING{UNORDERED|
   [HEAD text] [BODY text] [TABLE text]             ORDERED|DEFAULT}|
   [ENTMAP {ON|OFF}]                                NODE{BYVALUE|BYREFERENCE|
   [SPOOL {OFF|ON}]                                 DEFAULT}|
   [PRE[FORMAT] {OFF|ON}]                           CONTEXT text}
SQL>

Given the number of commands available, you can customize your environment easily to suit you best. One thing to keep in mind is that the set commands aren’t retained by SQL*Plus when you exit/close the tool. Instead of typing in each of the set commands you want to apply each time you use SQL*Plus, you can create a file named login.sql. There are actually two files that SQL*Plus reads by default each time you start it. The first is glogin.sql and it can be found in the directory $ORACLE_HOME/sqlplus/admin. If this file is found, it is read and the statements it contains are executed. This allows you to store the SQL*Plus commands and SQL statements that customize your experience across SQL*Plus sessions.

After reading glogin.sql, SQL*Plus looks for the login.sql file. This file must exist in either the directory from which SQL*Plus was started or in a directory included in the path to which the environment variable SQLPATH points. Any commands in login.sql will take precedence over those in glogin.sql. Since version 10g, Oracle reads both glogin.sql and login.sql each time you either start SQL*Plus or execute the connect command from within SQL*Plus. Prior to 10g, the login.sql script was only executed when SQL*Plus started. The contents of a common login.sql file are shown in Listing 1-7.

Listing 1-7.  A Common login.sql File

SET LINES 3000
--Sets width of display line (default 80 characters)
SET PAGES 1000
-Sets number of lines per page (default 14 lines)
SET TIMING ON
--Sets display of elapsed time (default OFF)
SET NULL <null>
--Sets display of nulls to show <null> (default empty)
SET SQLPROMPT '&_user@&_connect_identifier> '
--Sets the prompt to show connected user and instance

Note the use of the variables _user and _connect_identifier in the SET SQLPROMPT command. These are two examples of predefined variables. You may use any of the following predefined variables in your login.sql file or in any other script file you may create:

•    _connect_identifier (connection identifier used to make the database connection)
•    _date (current date)
•    _editor (the editor that is started when you use the EDIT command)
•    _o_version (current version of the installed Oracle database)
•    _o_release (full release number of the installed Oracle database)
•    _privilege (privilege level of the current connection)
•    _sqlplus_release (full release number of the installed SQL*Plus component)
•    _user (username used to make the connection)

Executing Commands

There are two types of commands that can be executed within SQL*Plus: SQL statements and SQL*Plus commands. The SQL*Plus commands shown in Listings 1-5 and 1-6 are specific to SQL*Plus and can be used for customizing the environment and executing commands that are specific to SQL*Plus, such as DESCRIBE and CONNECT. Executing a SQL*Plus command requires only that you type the command at the prompt and press Enter. The command is executed automatically. On the other hand, to execute SQL statements, you must use a special character to indicate you wish to execute the entered command. You may use either a semicolon or a forward slash to do this. A semicolon may be placed directly at the end of the typed command or on a following blank line. The forward slash must be placed on a blank line to be recognized. Listing 1-8 shows how these two execution characters are used.

Listing 1-8.  Execution Character Usage

SQL>select empno, deptno from scott.emp where ename = 'SMITH' ;
     EMPNO     DEPTNO
---------- ----------
      7369         20
SQL>select empno, deptno from scott.emp where ename = 'SMITH'
  2  ;
     EMPNO     DEPTNO
---------- ----------
      7369         20
SQL>select empno, deptno from scott.emp where ename = 'SMITH'
  2  /
     EMPNO     DEPTNO
---------- ----------
      7369         20
SQL>select empno, deptno from scott.emp where ename = 'SMITH'
  2
SQL>/
     EMPNO     DEPTNO
---------- ----------
      7369         20
SQL>select empno, deptno from scott.emp where ename = 'SMITH'/
  2
SQL>l
  1* select empno, deptno from scott.emp where ename = 'SMITH'/
SQL>/
select empno, deptno from scott.emp where ename = 'SMITH'/
                                                         *
ERROR at line 1:
ORA-00936: missing expression

Notice the fifth example that puts / at the end of the statement. The cursor moves to a new line instead of executing the command immediately. Then, if you press Enter again, the statement is entered into the SQL*Plus buffer but is not executed. To view the contents of the SQL*Plus buffer, the list command is used (abbreviated to l). If you then attempt to execute the statement in the buffer using /, which is how the / command is intended to be used, you get an error because, originally, you placed / at the end of the SQL statement line. The forward slash is not a valid SQL command and thus causes an error when the statement attempts to execute.

Another way to execute commands is to place them in a file. You can produce these files with the text editor of your choice outside of SQL*Plus or you may invoke an editor directly from SQL*Plus using the EDIT command. The EDIT command either opens a named file or creates a file if it doesn’t exist. The file must be in the default directory or you must specify the full path of the file. To set the editor to one of your choice, simply set the predefined _editor variable using the following command: define _editor='/<full path>/myeditor.exe'. Files with the extension .sql will execute without having to include the extension and can be run using either the @ or START command. Listing 1-9 shows the use of both commands.

Listing 1-9.  Executing .sql Script Files

SQL> @list_depts
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
SQL>
SQL> start list_depts
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
SQL>
SQL>l
  1* select * from scott.dept
SQL>

SQL*Plus has many features and options—far too many to cover here. For what we need in this book, the previous overview should suffice. However, the Oracle documentation provides guides for SQL*Plus use and there are numerous books, including Beginning Oracle SQL (as mentioned earlier), that go in to more depth if you’re interested.

The FROM Clause

The FROM clause lists the source objects from which data are selected. This clause can contain tables, views, materialized views, partitions, or subpartitions, or may specify a subquery that identifies objects. If multiple source objects are used, this logical processing phase also applies to each join type and ON predicate. We explore join types in more detail later, but note that, as joins are processed, they occur in the following order:

1. Cross join, also called a Cartesian product
2. Inner join
3. Outer join

In the example query in Listing 1-11, the FROM clause lists two tables: customers and orders. They are joined on the customer_id column. So, when this information is processed, the initial dataset that is produced by the FROM clause includes rows in which customer_id matches in both tables. The result set contains 105 rows at this point. To verify this is true, simply execute only the first four lines of the example query, as shown in Listing 1-12.

Listing 1-12.  Partial Query Execution through the FROM Clause Only

SQL> select c.customer_id cust_id, o.order_id ord_id, c.gender
  2  from oe.customers c
  3  join oe.orders o
  4  on c.customer_id = o.customer_id;

CUST_ID ORD_ID G  CUST_ID ORD_ID G  CUST_ID ORD_ID G
------- ------ -  ------- ------ -  ------- ------ -
    147   2450 F      101   2430 M      109   2394 M
    147   2425 F      101   2413 M      116   2453 M
    147   2385 F      101   2447 M      116   2428 M
    147   2366 F      101   2458 M      116   2369 M
    147   2396 F      102   2431 M      116   2436 M
    148   2451 M      102   2414 M      117   2456 M
    148   2426 M      102   2432 M      117   2429 M
    148   2386 M      102   2397 M      117   2370 M
    148   2367 M      103   2437 F      117   2446 M
    148   2406 M      103   2415 F      118   2457 M
    149   2452 M      103   2433 F      118   2371 M
    149   2427 M      103   2454 F      120   2373 M
    149   2387 M      104   2438 F      121   2374 M
    149   2368 M      104   2416 F      122   2375 M
    149   2434 M      104   2355 F      123   2376 F
    150   2388 M      104   2354 F      141   2377 M
    151   2389 M      105   2439 F      143   2380 M
    152   2390 M      105   2417 F      144   2445 M
    153   2391 M      105   2356 F      144   2422 M
    154   2392 F      105   2358 F      144   2382 M
    155   2393 M      106   2441 M      144   2363 M
    156   2395 F      106   2418 M      144   2435 M
    157   2398 M      106   2359 M      145   2448 M
    158   2399 M      106   2381 M      145   2423 M
    159   2400 M      107   2442 F      145   2383 M
    160   2401 M      107   2419 F      145   2364 M
    161   2402 M      107   2360 F      145   2455 M
    162   2403 M      107   2440 F      119   2372 M
    163   2404 M      108   2443 M      142   2378 M
    164   2405 M      108   2420 M      146   2449 F
    165   2407 M      108   2361 M      146   2424 F
    166   2408 F      108   2357 M      146   2384 F
    167   2409 M      109   2444 M      146   2365 F
    169   2411 F      109   2421 M      146   2379 F
    170   2412 M      109   2362 M      168   2410 M
105 rows selected.

The WHERE Clause

The WHERE clause provides a way to limit conditionally the rows emitted to the query’s final result set. Each condition, or predicate, is entered as a comparison of two values or expressions. The comparison matches (evaluates to TRUE) or does not match (evaluates to FALSE). If the comparison is FALSE, then the row is not included in the final result set.

I need to digress just a bit to cover an important aspect of SQL related to this step. Actually, the possible values of a logical comparison in SQL are TRUE, FALSE, and UNKNOWN. The UNKNOWN value occurs when a null is involved. Nulls compared with anything or nulls used in expressions evaluate to null, or UNKNOWN. A null represents a missing value and can be confusing because of inconsistencies in how nulls are treated within different elements of the SQL language. I address how nulls affect the execution of SQL statements throughout the book, although I mention the topic only briefly at this point. What I stated previously is still basically true; comparisons return either TRUE or FALSE. What you’ll find is that when a null is involved in a filter comparison, it is treated as if it is FALSE.

In our example, there is a single predicate used to limit the result only to females who have placed orders. If you review the intermediate result after the FROM clause was processed (see Listing 1-12), you’ll note that only 31 of the 105 rows were placed by female customers (gender = ‘F’). Therefore, after the WHERE clause is applied, the intermediate result set is reduced from 105 rows to 31 rows.

After the WHERE clause is applied, the detailed result set is ready. Note that I use the phrase detailed result set. What I mean is the rows that satisfy the query requirements are now available. Other clauses may be applied (GROUP BY, HAVING) that aggregate and limit the final result set further that the caller receives, but it is important to note that, at this point, all the data your query needs to compute the final answer are available.

The WHERE clause is intended to restrict, or reduce, the result set. The less restrictions you include, the more data your final result set contains. The more data you need to return, the longer the query takes to execute.

The GROUP BY Clause

The GROUP BY clause aggregates the filtered result set available after processing the FROM and WHERE clauses. The selected rows are grouped by the expression(s) listed in this clause to produce a single row of summary information for each group. You may group by any column of any object listed in the FROM clause, even if you don’t intend to display that column in the list of output columns. Conversely, any nonaggregate column in the select list must be included in the GROUP BY expression.

There are two additional operations that can be included in a GROUP BY clause: ROLLUP and CUBE. The ROLLUP operation is used to produce subtotal values. The CUBE operation is used to produce cross-tabulation values. If you use either of these operations, you get more than one row of summary information. Both these operations are discussed in detail in Chapter 7.

In the example query, the requested grouping is customer_id. This means that there is only one row for each distinct customer_id. Of the 31 rows that represent the females who placed orders that made it through the WHERE clause processing, there are 11 distinct customer_id values, as shown in Listing 1-13.

Listing 1-13.  Partial Query Execution through the GROUP BY Clause

SQL> select c.customer_id, count(o.order_id) as orders_ct
  2  from oe.customers c
  3  join oe.orders o
  4  on c.customer_id = o.customer_id
  5 where gender = 'F'
  6 group by c.customer_id;

CUSTOMER_ID  ORDERS_CT
----------- ----------
        156          1
        123          1
        166          1
        154          1
        169          1
        105          4
        103          4
        107          4
        104          4
        147          5
        146          5
11 rows selected.

Notice that the output from the query, although grouped, is not ordered. The display makes it appear as though the rows are ordered by order_ct, but this is more coincidence and not guaranteed behavior. This is an important item to remember; the GROUP BY clause does not ensure ordering of data. If you want the list to display in a specific order, you have to specify an ORDER BY clause.

The HAVING Clause

The HAVING clause restricts the grouped summary rows to those for which the condition(s) in the clause are TRUE. Unless you include a HAVING clause, all summary rows are returned. The GROUP BY and HAVING clauses are actually interchangeable positionally (it doesn’t matter which one comes first). However, it seems to make more sense to code them with the GROUP BY clause first, because GROUP BY is processed logically first. Essentially, the HAVING clause is a second WHERE clause that is evaluated after GROUP BY occurs, and is used to filter on grouped values. Because the WHERE clause is applied before the GROUP BY occurs, you cannot filter grouped results in the WHERE clause; you must use the HAVING clause instead.

In our example query, the HAVING clause, HAVING COUNT(o.order_id) > 4, limits the grouped result data of 11 rows down to two rows. You can confirm this by reviewing the list of rows returned after GROUP BY is applied, as shown in Listing 1-13. Note that only customers 146 and 147 have placed more than four orders. The two rows that make up the final result set are now ready.

The SELECT List

The SELECT list is where the columns included in the final result set from your query are provided. A column can be an actual column from a table, an expression, or even the result of a SELECT statement, as shown in Listing 1-14.

Listing 1-14.  Example Query Showing SELECT List Alternatives

SQL> select c.customer_id, c.cust_first_name||' '||c.cust_last_name,
  2  (select e.last_name from hr.employees e where e.employee_id = c.account_mgr_id) acct_mgr)
  3  from oe.customers c;

    CUSTOMER_ID CUST_NAME                                 ACCT_MGR
--------------- ----------------------------------------- --------------
            147 Ishwarya Roberts                          Russell
            148 Gustav Steenburgen                        Russell
...
            931 Buster Edwards                            Cambrault
            981 Daniel Gueney                             Cambrault
319 rows selected.

When another SELECT statement is used to produce the value of a column, the query must return only one row and one column value. These types of subqueries are referred to as scalar subqueries. Although this can be very useful syntax, keep in mind that the scalar subquery is executed once for each row in the result set. There are optimizations available that may eliminate some duplicate executions of the subquery, but the worst-case scenario is that each row requires this scalar subquery to be executed. Imagine the possible overhead involved if your result set had thousands, or millions, of rows! I review scalar subqueries later in the book and discuss how to use them optimally.

Another option you may need to use in the SELECT list is the DISTINCT clause. The example provided here doesn’t use it, but I want to mention it briefly. The DISTINCT clause causes duplicate rows to be removed from the dataset produced after the other clauses have been processed.

After the SELECT list is processed, you now have the final result set for your query. The only thing that remains to be done, if it is included, is to sort the result set into a desired order.

The ORDER BY Clause

The ORDER BY clause is used to order the final set of rows returned by the statement. In this case, the requested sort order was to be by orders_ct and customer_id. The orders_ct column is the value computed using the COUNT aggregate function in the GROUP BY clause. As shown in Listing 1-13, there are two customers that each placed more than four orders. Because each customer placed five orders, the order_ct is the same, so the second ordering column determines the final display order. As shown in Listing 1-15, the final sorted output of the query is a two-row dataset ordered by customer_id.

Listing 1-15.  Example Query Final Output

SQL> select c.customer_id, count(o.order_id) as orders_ct
  2  from oe.customers c
  3  join oe.orders o
  4  on c.customer_id = o.customer_id
  5  where c.gender = 'F'
  6  group by c.customer_id
  7  having count(o.order_id) > 4
  8  order by orders_ct, c.customer_id
  9  /
CUSTOMER_ID  ORDERS_CT
----------- ----------
        146          5
        147          5

When ordered output is requested, Oracle must take the final set of data after all other clauses have been processed and sort them as specified. The size of the data that needs to be sorted is important. When I say size, I mean total bytes of data in the result set. To estimate the size of the dataset, multiply the number of rows by the number of bytes per row. The bytes per row are determined by summing the average column lengths of each of the columns in the SELECT list.

The example query requests only the customer_id and orders_ct column values in the SELECT list. Let’s use ten as our estimated bytes-per-row value. In Chapter 6, I show you where to find the optimizer’s estimate for this value. So, given that we only have two rows in the result set, the sort size is actually quite small, approximately 20 bytes. Remember, this is only an estimate, but the estimate is an important one.

Small sorts should be accomplished entirely in memory whereas large sorts may have to use temporary disk space to complete the sort. As you may likely deduce, a sort that occurs in memory is faster than a sort that must use disk. Therefore, when the optimizer estimates the effect of sorting data, it has to consider how big the sort is to adjust how to accomplish getting the query result in the most efficient way. In general, consider sorts as a fairly expensive overhead to your query processing time, particularly if the size of your result set is large.

Single-Table Inserts

The first example in Listing 1-16 illustrates a single-table insert using the VALUES clause. Each column value is entered explicitly. The column list is optional if you include values for each column defined in the table. However, if you only want to provide values for a subset of the columns, you must specify the column names in the column list. A good practice is to include the column list regardless of whether you specify values for all the columns. Doing so acts to self-document the statement and also helps reduce possible errors that might occur in the future should someone add a new column to the table.

Listing 1-16.  Single-Table Insert

SQL> insert into hr.jobs (job_id, job_title, min_salary, max_salary)
  2  values ('IT_PM', 'Project Manager', 5000, 11000) ;
1 row created.

SQL> insert into scott.bonus (ename, job, sal)
  2  select ename, job, sal * .10
  3  from scott.emp;

14 rows created.

The second example in Listing 1-16 illustrates an insert using a subquery, which is a very flexible option for inserting rows. The subquery can be written to return one or more rows. Each row returned is used to supply column values for the new rows to be inserted. The subquery can be as simple or as complex as needed to satisfy your needs. In this example, we use the subquery to compute a 10 percent bonus for each employee based on his or her current salary. The bonus table actually has four columns, but we only populate three of them with this insert. The comm column isn’t populated with a value from the subquery and we do not include it in the column list. Because we don’t include this column, the value for it is null. Note that if the comm column had a NOT NULL constraint, you would get a constraint error and the statement would fail.

Multitable Inserts

The multitable insert example in Listing 1-17 illustrates how rows returned from a single subquery can be used to insert rows into more than one table. We start with three tables: small_customers, medium_customers, and large_customers. Let’s populate these tables with customer data based on the total amount of orders a customer has placed. The subquery sums the order_total column for each customer, and then the insert places a row conditionally in the proper table based on whether the customer is considered to be small (less than $10,000 of total orders), medium (between $10,000 and $99,999.99), or large (greater than or equal to $100,000).

Listing 1-17.  Multitable Insert

SQL> select * from small_customers ;

no rows selected

SQL> select * from medium_customers ;

no rows selected

SQL> select * from large_customers ;

no rows selected

SQL> insert all
  2  when sum_orders < 10000 then
  3  into small_customers
  4  when sum_orders >= 10000 and sum_orders < 100000 then
  5  into medium_customers
  6  else
  7  into large_customers
  8  select customer_id, sum(order_total) sum_orders
  9  from oe.orders
 10  group by customer_id ;

47 rows created.

SQL> select * from small_customers ;

CUSTOMER_ID SUM_ORDERS
----------- ----------
        120        416
        121       4797
        152     7616.8
        157     7110.3
        160      969.2
        161        600
        162        220
        163        510
        164       1233
        165       2519
        166        309
        167         48

12 rows selected.

SQL> select * from medium_customers ;

CUSTOMER_ID SUM_ORDERS
----------- ----------
        102    69211.4
        103    20591.4
        105    61376.5
        106    36199.5
        116      32307
        119    16447.2
        123    11006.2
        141    38017.8
        142    25691.3
        143    27132.6
        145    71717.9
        146    88462.6
        151      17620
        153    48070.6
        154      26632
        155    23431.9
        156      68501
        158    25270.3
        159    69286.4
        168      45175
        169    15760.5
        170      66816

22 rows selected.

SQL> select * from large_customers ;

CUSTOMER_ID SUM_ORDERS
----------- ----------
        101   190395.1
        104   146605.5
        107   155613.2
        108   213399.7
        109   265255.6
        117   157808.7
        118   100991.8
        122   103834.4
        144   160284.6
        147   371278.2
        148   185700.5
        149   403119.7
        150   282694.3

13 rows selected.

Note the use of the ALL clause after the INSERT keyword. When ALL is specified, the statement performs unconditional multitable inserts, which means that each WHEN clause is evaluated for each row returned by the subquery regardless of the outcome of a previous condition. Therefore, you need to be careful about how you specify each condition. For example, if I had used WHEN sum_orders < 100000 instead of the range I specified, the medium_customers table would have included the rows that were also inserted into small_customers.

Specify the FIRST option to cause each WHEN to be evaluated in the order it appears in the statement and to skip subsequent WHEN clause evaluations for a given subquery row. The key is to remember which option, ALL or FIRST, best meets your needs, then use the one most suitable.