Day 9

Using SQL: INSERT,
SELECT, Advanced
Declarations, and
Tables

by Tom Luers

By definition, PL/SQL is SQL's procedural language extension. PL/SQL supports all of SQL's data manipulation commands (except for EXPLAIN PLAN), transaction control commands, functions, pseudocolumns, and operators. This chapter covers the usage of SQL's Data Manipulation Language (DML) commands within a PL/SQL block.

NEW TERM
Data Manipulation Language (DML) commands enable the user to query a database and to manipulate data in an existing database. The execution of a DML statement does not implicitly commit the current transaction. The user has an opportunity to roll back or save the transaction.

When a PL/SQL block executes a SQL DML command, the block passes the SQL command to the Oracle database for processing in its own SQL engine. Figure 9.1 illustrates this concept.

Figure 9.1.
The Oracle PL/SQL
server engine.

Because PL/SQL passes the DML statements to the RDBMS, the PL/SQL statements must conform to the required SQL syntax standards. This is one of the few times that you must be aware of the syntax differences between SQL and PL/SQL.

PL/SQL allows the use of SQL DML statements in order to provide an easy, safe, and flexible environment to manipulate data. In a simple program, SQL statements are processed one statement at a time, as diagrammed in Figure 9.2. If you have four separate SQL statements, Oracle would process these in four independent actions and return four different results. Through the use of PL/SQL, you can group these same SQL statements into a PL/SQL block and have them processed at one time, hence improving your overall performance.

Figure 9.2.
SQL versus PL/SQL
code processing.

Types of SQL Statements

PL/SQL allows you to use four different DML commands: INSERT, DELETE, UPDATE, and SELECT. This chapter discusses each of these and their use within PL/SQL. The INSERT statement adds new data to the database. The DELETE command removes data from the database. The UPDATE command modifies existing data in the database, and the SELECT statement retrieves data from the database.

This chapter covers the four basic statements just described: INSERT, SELECT, UPDATE, and DELETE. This chapter does not teach the use or syntax of SQL itself. Only the most basic SQL statements and queries are included to illustrate the use of SQL within PL/SQL. Refer to the book Teach Yourself SQL in 21 Days for more in-depth knowledge of SQL queries.

Creating Some Oracle Tables

You now need to create several Oracle tables to use in the remainder of the book. In order to create these tables, you must have the CREATE TABLE system privilege. You will create three tables in this chapter. These are the employee table, department table, and emp_dept table. Figure 9.3 shows an Entity Relationship Diagram (ERD) for these three tables. This diagram also shows a physical layout of these tables.

Figure 9.3.
ERD and physical
data model for the
three tables.

The first table you need to create is the employee table. This table will hold information
about the employees. Specifically, for each employee, it will hold the employee's name, supervisor's name, pay rate, pay type, and a key that indicates which department the employee belongs to.

The Syntax for the CREATE TABLE Command

CREATE TABLE table_name
(column_name column_datatype);

In this syntax, table_name is the name you assign to the table. column_name is the name of the column you assign, and column_datatype is the datatype for that column.

Go ahead and execute the code shown in Listing 9.1 to create this table.

INPUT
Listing 9.1. Creating the employee table.

 CREATE TABLE employee
       (emp_id           INTEGER,
         emp_name      VARCHAR2(32),
         supervised_by      INTEGER,
         pay_rate               NUMBER(9,2),
         pay_type              CHAR,
         emp_dept_id                 INTEGER);

ALTER TABLE employee
ADD CONSTRAINT pk_emp primary key (emp_id);

Take a look at some of the fields in the table you just created. The emp_id field will hold a unique numeric value that guarantees uniqueness across all rows of the table. For example, the emp_id field would be the only way to pick the correct row when you have two employees with the exact same name.

The supervised_by field will hold the value of the emp_id for that person's supervisors, as shown in the following example:

You can see that Jenny is the supervisor of Jack. This is indicated by the supervised_by value of 3 in Jack's record. This 3 represents the emp_id of the person who is Jack's supervisor. In this case, it is Jenny.

The emp_dept_id is the ID number of the department where they all work.

The next table you will create is the department table. This table will contain information about the department in which the employees work. It will hold the department's ID, the department name, and the number of employees. Execute the code shown in Listing 9.2 to create this table.

INPUT
Listing 9.2. Creating the department table.

CREATE TABLE department
       (dept_id           INTEGER,
        dept_name     VARCHAR2(32));

ALTER TABLE department
ADD CONSTRAINT PRIMARY KEY (dept_id);

The final table you need to create is the emp_dept table. This table tells you which employees work in which departments. Execute the code shown in Listing 9.3 to create the emp_dept table.

INPUT
Listing 9.3. Creating the emp_dept table.

CREATE TABLE emp_dept
   (emp_id           INTEGER,
    dept_id            INTEGER,
    CONSTRAINT unq_1 unique (emp_id, dept_id));

Now that your base tables are created, go ahead and use them. First you will insert data into the tables, and then you will retrieve that same data.

The INSERT Statement

The INSERT command is used to add new rows to an existing Oracle table or view. In this case, you will only be inserting data into a table.

The Syntax for the INSERT Command

INSERT into table_name  [column_name]  values (values)

In this syntax, table_name is the name of the table into which you're inserting data. column_name is the name of the column being inserted into the table, and values is the data that will be placed in the column.

Try working through two examples to illustrate the usage of the INSERT command. You might want to refer back to Figure 9.3 to refresh your memory of the employee table layout.