The first example is

INSERT into employee values
     ( 1, ` Jessica Loraine', 2, 8.50, `H', 3,
        2, ` Kurt Roberts', 5, 100.00, `S', 3);

When this statement is executed, the table will have two rows in it and look like this:

Note that there were no column_name references in the INSERT statement. The reason is that SQL will make a one-to-one match of the column_name to the data included in the INSERT statement. If you want, you can insert data into selected columns, as shown in the second example.

Here's the second example:

INSERT into employee (emp_id, emp_name) values
        ( 1, ` Jessica Loraine',
           2, ` Kurt Roberts');

In the second example, you placed data only in the emp_id and emp_name columns. All other columns would be blank.

Inserting Some Data

You will now insert data into the employee table for use in the remainder of the book. Type in the PL/SQL block shown in Listing 9.4, and then compile and execute it. When you run this block of code, it will ask you for an employee's name and related information and in turn insert this data into the employee table. Run this anonymous PL/SQL block multiple times

in order to end up with roughly 10 employees' worth of data loaded. Your goal here is to input data that represents the typical organizational chart shown in Figure 9.4. You want data loaded for all levels of the org chart. While inserting data, feel free to use any names you like.

Figure 9.4.
Organization chart.

Listing 9.4 shows the PL/SQL anonymous block that you can run to insert the necessary data.

INPUT
Listing 9.4. Inserting records with PL/SQL code.

DECLARE               -- insert department data first
i_dept_id  INTEGER,
i_dept_name,
BEGIN
INSERT into department values
(&i_dept_id,'&dept_name');
END;

COMMIT;            -- save the department data

DECLARE             -- insert employee and emp_dept data
i_id   INTEGER;
e_id  INTEGER;
i_name  VARCHAR2(32);
i_super  INTEGER;
i_rate    NUMBER(9,2);
i_type   CHAR;
i_emp_dept INTEGER;
e_emp_dept INTEGER;
BEGIN
e_id:=&employee_id;
e_emp_dept:=&employee_department_id;

                                                         continues

Listing 9.4. continued

INSERT into employee values
(e_id, `&i_name',&i_super,&i_rate,'&i_type',e_emp_dept);
INSERT into emp_dept values (e_id,e_emp_dept);
END;

COMMIT;         -- save employee  and emp_dept datadata

ANALYSIS The code in Listing 9.4 is used to insert data first into the department table, then the employee table, and finally the emp_dept table.

Singleton SELECT Statement

The SELECT statement is one of the ways to get data out of the database. In order to use the SELECT statement, you must have SELECT system privileges. Depending on how you design and use the SELECT statement, you can retrieve a single (singleton) row or multiple rows of data from the database. Sometimes you'll want only a single row returned; otherwise, you want your PL/SQL block to handle the multiple rows without terminating. This chapter discusses methods of handling unexpected errors in your program in the section "Types of Exceptions."

The Syntax for the SELECT Statement

SELECT column_name
from table_name
WHERE condition
ORDER BY expression

In this syntax, column_name is the name of the column or columns from which you want data. table_name is the name of the table or tables in which the previous columns belong. The condition statement is used to specify the criteria to retrieve specific rows. The ORDER BY clause enables you to define the order in which to display the retrieved rows. For example, you might want to display the rows in alphabetical order or in numeric sequence. Both the WHERE and the ORDER BY clauses are optional.

Some SELECT Examples

The first example is a simple SELECT statement to retrieve all the rows from the employee table:

SELECT emp_id, emp_name, supervised_by, pay_rate, pay_type
from employee
ORDER BY emp_name

This statement will return all rows from the employee table sorted in ascending order by the employee name. Because this statement grabs all columns and rows from the table, you could use the wildcard * to achieve the same result:

SELECT * from employee ORDER BY emp_name

A more complex, but realistic, example would be

SELECT * from employee
WHERE pay_type = `S'
ORDER BY pay_rate desc

This SELECT statement will return

• All columns from the table
• All rows from the table that have the pay_type equal to `S'

The returned rows will be in pay_rate descending order.

Finally, Listing 9.5 is an example of a singleton SELECT. An assumption is made here that you only have one employee with the name of Jack Richards. You might want your program to indicate to you if you have multiple occurrences of a specific employee. The bottom line is that you don't want processing to halt if this happens.

INPUT
Listing 9.5. Multiple-row SELECT command.

DECLARE
v_emp_id      INTEGER;
BEGIN
SELECT emp_id
into v_emp_id
from employee
WHERE emp_name = `Jack Richards';
exception
when too_many_rows THEN
emp_name:='TOO MANY EMPLOYEES FOUND';
END;

ANALYSIS In this example, an exception is raised when more than one row is returned by the SELECT statement.

Types of Exceptions

Exceptions are errors that occur during runtime processing. These exceptions can arise due to different situations. Normally, PL/SQL processing will terminate as soon as it encounters an exception. Fortunately, PL/SQL gives you several tools to handle these exceptions so that processing does not terminate. After an exception is detected, processing is transferred to your handling routine within the PL/SQL block. Refer to Day 8, "Procedures, Packages, Errors, and Exceptions," for additional information about Oracle's exception-handling capabilities.