Database, SQL and PL/SQL

Sequential Additions and Different Points of View

Part 7 in a second series on the basics of the relational database and SQL

By Melanie Caffrey

January/February 2017

This article is the seventh in a series that helps you build on the fundamentals you learned in the 12-part SQL 101 series in Oracle Magazine. The previous Beyond SQL 101 article, “Defining, Constraining, and Manipulating Your Entities,” introduced you to common constraints, including primary key, foreign key, not null, check, and unique constraints. You learned how these constraints can be violated when data is inserted that doesn’t conform to the constraint conditions. You discovered how to create a table with the same structure as another table by using the CREATE TABLE AS SELECT (CTAS) method. You also discovered how default values can be used for columns you want to be regularly populated. Finally, you were introduced to column definition manipulation via the ALTER TABLE command and several of its options.

In this article, you’ll learn more about using Oracle data definition language (DDL):

  • How to rename tables
  • How to drop tables
  • How to recover tables by using the recycle bin and how purging syntax affects table recovery
  • The difference between dropping and truncating tables
  • How a virtual column is created and how it is used

You’ll also be introduced to sequences and IDENTITY columns and learn how they can be used to help guarantee unique values for primary keys. Last, you’ll discover how you can use a view to simplify query writing and data hiding.

To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Enterprise Edition 12c Release 1 (12.1.0.2.0). If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_201 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for the SYS and SYSTEM users and make a note of them.

Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_201 schema used for this article’s examples. (View the script in a text editor for execution instructions.)

An Entity by Any Other Name

In the previous Beyond SQL 101 article, “Defining, Constraining, and Manipulating Your Entities,” you learned how to rename a table column. Similarly, you can rename a table. In Listing 1, for example, the EMPLOYEE_EXAMPLE table is renamed EMPLOYEE_EXTRA and users can no longer query the table with its former name.

Code Listing 1: renaming the EMPLOYEE_EXAMPLE table EMPLOYEE_EXTRA

SQL> set lines 10000
SQL> ALTER TABLE employee_example RENAME TO employee_extra;
Table renamed.

SQL> select *
  2    from employee_example;
  from employee_example
       *
ERROR at line 2:
ORA-00942: table or view does not exist

SQL> select *
  2    from employee_extra;

EMPLOYEE_ID FIRST_NAME LAST_NAME  SALARY   MANAGER DEPARTMENT_ID LAST_UPDA
——————————— —————————— ————————— ———————  ———————— ————————————— —————————
          1 Ram        Burela     100000                         11-SEP-16
          2 Seema      Patel                                     12-SEP-16
2 rows selected.

A table’s constraint names are not changed when the table is renamed. However, a view (introduced later in this article) and other database objects and code—such as synonyms and PL/SQL (to be outlined in subsequent articles in this series)—can be affected and might require recompilation. (See the “Recompilation” sidebar for information on code and compiling.) Additionally, a table rename has no impact on any privileges a user may have had on the table before it was renamed. A user who could query EMPLOYEE_EXAMPLE can similarly query EMPLOYEE_EXTRA.

What Is Lost Can Be Found

When you no longer need a table, you can drop (remove) it. As Listing 2 shows, a dropped table can no longer be queried; the table, its data and constraints, and other dependent objects are all removed.

Code Listing 2: Dropping the EMP2 table

SQL> select *
  2    from emp2;
no rows selected

SQL> drop table emp2;
Table dropped.

SQL> select *
  2    from emp2;
  from emp2
       *
ERROR at line 2:
ORA-00942: table or view does not exist

Oracle Database puts tables that have been dropped via the syntax in Listing 2 in a recycle bin similar to the one on a computer running the Windows operating system. You can restore deleted items from the recycle bin, and you can delete dropped items permanently by purging them from the recycle bin. Note, however, that dropped tables and other objects remain in the Oracle Database recycle bin only if the recycle bin has sufficient free space for them. Your dropped objects might be in the recycle bin for years or for a millisecond, depending on the amount of free space. The query in Listing 3 shows that the dropped EMP2 table is in the recycle bin.

Code Listing 3: Locating the EMP2 table in the recycle bin

SQL> select object_name, original_name, type
  2    from user_recyclebin;

OBJECT_NAME                      ORIGINAL_NAME                TYPE
———————————————————————————————— ———————————————————————————— ———————
BIN$P7tLNcHrFG3gUwEAAH/yFg==$0   EMP2                         TABLE
1 row selected.

If you want to reinstate the EMP2 table, you can flash it back and effectively undo your drop action. Oracle Database contains flashback technology that enables users to

  • Query data as it existed in the past
  • Undo a statement or a transaction
  • Restore a dropped table
  • Restore a database to its pre-existing state

Code Listing 4: Restoring the EMP2 table

SQL> FLASHBACK TABLE emp2 TO BEFORE DROP;
Flashback complete.

SQL> select *
  2    from emp2;
no rows selected

To be able to restore a dropped table, the user must have either the privilege to flash back a specific table or to flash back any table. Listing 4 shows how to flash a table back to its original state. Note that database objects and code such as indexes and triggers (to be outlined in subsequent articles in this series) and constraints do not automatically revert to their original names when they are restored to their original state. Instead, they are restored with the <object_name> from the recycle bin. You must manually ensure that they are renamed correctly after you’ve executed the FLASHBACK TABLE <table_name> TO BEFORE DROP statement. Once the table is restored, it can be queried without returning the following error message:

ORA-00942: table or view does not exist

To drop a table permanently, you can write a DROP TABLE command similar to the one shown in Listing 5. When you issue the DROP TABLE command with the PURGE option, you can’t use the recycle bin to restore a table and its objects, because the table, its contents, and its dependents are permanently removed and the space they occupied is reclaimed. Alternatively, you can purge a dropped table from the recycle bin by issuing the PURGE RECYCLEBIN command as shown in Listing 6. You can read about additional commands and options for purging the recycle bin in the Oracle documentation.

Code Listing 5: Using the PURGE option to permanently drop a table, its contents, and its dependents

SQL> drop table emp2 PURGE;
Table dropped.

SQL> select object_name, original_name, type
  2    from user_recyclebin;
no rows selected

Code Listing 6: Purging a single table and purging the entire recycle bin

SQL> purge table emp2;
Table purged.

SQL> purge recyclebin;
Recyclebin purged.

The TRUNCATE command removes all data from a table but retains the table’s structure along with permissions and certain dependent objects. Like the DROP TABLE command, TRUNCATE does not generate any UNDO information and, when invoked, implicitly issues a COMMIT statement. A TRUNCATE action cannot be flashed back. To recover from an erroneously executed TRUNCATE command, you must either

  • Restore (a single table, if so desired) from a backup (to be outlined in subsequent articles in this series).
  • Flash back the entire database to the point in time just before the TRUNCATE command was issued. Be aware that performing this action causes you to lose all data manipulation language (DML) and DDL actions that were performed after the erroneous TRUNCATE statement was issued.
Virtual Generation

Suppose that one of your business requirements is to provide a running calculation of whether your employees are eligible for a cost-of-living wage increase. For example, if the current requirement is that anyone earning a salary value less than 100000 is eligible and anyone earning more than 100000 isn’t, you could write a query similar to the one in Listing 7. Another way to obtain the same running calculation and simplify any queries that would require this calculation is to create a virtual column on the EMPLOYEE table. A virtual column is a column that is usually derived from the other columns of the table but can also be a constant expression or the result of a function. The example in Listing 8 demonstrates how to add a virtual column to the EMPLOYEE table.

Code Listing 7: Creating a running calculation on the EMPLOYEE table

SQL> select first_name||' '||last_name, salary,
           (case when salary < 100000 then 'Cost
            of Living Increase Eligible' else 'No
            Raise Yet' end) "Wage Increase Worthiness"
  2    from employee
  3  order by first_name||' '||last_name;

FIRST_NAME||''||LAST_NAME       SALARY   Wage Increase Worthiness
——————————————————————————————— ———————— —————————————————————————————————
Betsy James                      60000   Cost of Living Increase Eligible
Donald Newton                    80000   Cost of Living Increase Eligible
Emily Eckhardt                  110000   No Raise Yet
Frances Newton                   82500   Cost of Living Increase Eligible
Lori Dovichi                             No Raise Yet
Marcy Tamra                              No Raise Yet
Mary Streicher                  200000   No Raise Yet
Matthew Michaels                 70000   Cost of Living Increase Eligible
Roger Friedli                    60000   Cost of Living Increase Eligible
Sasha Meyer                      85000   Cost of Living Increase Eligible
Theresa Wong                     70000   Cost of Living Increase Eligible
Thomas Jeffrey                  300000   No Raise Yet
mark leblanc                     65000   Cost of Living Increase Eligible
michael peterson                 90000   Cost of Living Increase Eligible
14 rows selected.

Code Listing 8: Defining a VIRTUAL column for EMPLOYEE

SQL> alter table employee add (wage_increase_worthiness varchar2(40)
  2                            GENERATED ALWAYS AS
  3                           (case when salary < 100000 then 'Cost
                               of Living Increase Eligible' else 'No
                               Raise Yet' end) VIRTUAL);
Table altered.

You can use the new virtual column to write simpler queries against the EMPLOYEE table that can obtain the value for WAGE_INCREASE_WORTHINESS without needing to include the code for the necessary calculation in the SELECT list each time. The results from the query in Listing 9 are the same as the results returned in Listing 7. The query in Listing 9 does not need to specify the code for the calculation. Because it specifies the column name, WAGE_INCREASE_WORTHINESS, the derived column value calculations associated with that column are executed and returned. The concatenation of the FIRST_NAME and LAST_NAME columns can also be considered a computation that could be added to the EMPLOYEE table as another virtual column. Adding such a virtual column to the EMPLOYEE table further simplifies the query in Listing 9, as demonstrated in Listing 10. Query simplification isn’t the only reason to include virtual columns in your tables, as you’ll see in subsequent articles in this series.

Code Listing 9: A query selecting from a virtual column

SQL> select first_name||' '||last_name "Employee", salary "Salary",
wage_increase_worthiness "Wage Increase Worthiness"
  2    from employee
  3  order by first_name||' '||last_name;

Employee                       Salary Wage Increase Worthiness
———————————————————————————— ———————— —————————————————————————————————
Betsy James                     60000 Cost of Living Increase Eligible
Donald Newton                   80000 Cost of Living Increase Eligible
Emily Eckhardt                 110000 No Raise Yet
Frances Newton                  82500 Cost of Living Increase Eligible
Lori Dovichi                          No Raise Yet
Marcy Tamra                           No Raise Yet
Mary Streicher                 200000 No Raise Yet
Matthew Michaels                70000 Cost of Living Increase Eligible
Roger Friedli                   60000 Cost of Living Increase Eligible
Sasha Meyer                     85000 Cost of Living Increase Eligible
Theresa Wong                    70000 Cost of Living Increase Eligible
Thomas Jeffrey                 300000 No Raise Yet
mark leblanc                    65000 Cost of Living Increase Eligible
michael peterson                90000 Cost of Living Increase Eligible
14 rows selected.

Code Listing 10: The concatenation of FIRST_NAME and LAST_NAME virtualized

SQL> alter table employee add (emp_full_name varchar2(70)
  2  GENERATED ALWAYS AS
  3  (first_name||' '||last_name) VIRTUAL);
Table altered.

SQL> select emp_full_name "Employee",
salary "Salary", wage_increase_worthiness "Wage Increase Worthiness"
  2    from employee
  3  order by emp_full_name;

Employee                        Salary Wage Increase Worthiness
————————————————————————————— ———————— —————————————————————————————————
Betsy James                      60000 Cost of Living Increase Eligible
Donald Newton                    80000 Cost of Living Increase Eligible
Emily Eckhardt                  110000 No Raise Yet
Frances Newton                   82500 Cost of Living Increase Eligible
Lori Dovichi                           No Raise Yet
Marcy Tamra                            No Raise Yet
Mary Streicher                  200000 No Raise Yet
Matthew Michaels                 70000 Cost of Living Increase Eligible
Roger Friedli                    60000 Cost of Living Increase Eligible
Sasha Meyer                      85000 Cost of Living Increase Eligible
Theresa Wong                     70000 Cost of Living Increase Eligible
Thomas Jeffrey                  300000 No Raise Yet
mark leblanc                     65000 Cost of Living Increase Eligible
michael peterson                 90000 Cost of Living Increase Eligible
14 rows selected.

You need to consider a few limitations and restrictions if you plan to use virtual columns. You cannot update a virtual column, and you can only create a virtual column that is derived from other nonvirtual columns in the table within which it is defined. Also, deriving and storing results physically on disk might be a better strategy if

  • Your derived value is computationally complex (and therefore CPU-intensive)
  • You invoke the calculation for the derived value often
  • You are retrieving many rows at a time

However, in many more cases, a virtual column will give you the same flexibility and performance you obtain from a normal scalar-value column.

Creating a Sequence of Events

Recall that each record in an entity must be uniquely identified and separate from every other record in the same entity and that a primary key helps enforce such uniqueness. The most common data for a primary key value is numeric. Although it’s possible to keep a running tally of every value used in a primary key column for an entity, that process is tedious and error-prone, particularly when such a primary key value is referenced by any foreign key values. To generate the type of artificial or surrogate key value found in the EMPLOYEE_ID primary key column of the EMPLOYEE table, you can use a sequence. A sequence is an Oracle Database object used to generate unique integers.

The example in Listing 11 outlines the simplest way to create a sequence. Listing 12 demonstrates how this sequence is used to help create a new record and a new EMPLOYEE_ID value for the EMPLOYEE table. Instead of supplying a literal numeric value, you supply the pseudocolumn, nextval (employee_id_seq.nextval), of the EMPLOYEE_ID_SEQ sequence. The sequence automatically generates the next integer value it has available to it and supplies it to the nextval pseudocolumn each time you access it. If you create a sequence without a specific start value, the sequence will automatically begin generating integers, starting at 1.

Code Listing 11: Creating a sequence for the EMPLOYEE table’s EMPLOYEE_ID

SQL> create sequence employee_id_seq;
Sequence created.

Code Listing 12: Using a sequence to create a record in the EMPLOYEE table

SQL> insert into employee (employee_id, first_name, last_name,
                           hire_date, salary, manager, department_id,
                           wage_increase_worthiness)
  2  values (employee_id_seq.nextval, 'Don', 'Rose', sysdate, 95000,
             6576, 40, default);
1 row created.

SQL> commit;
Commit complete.

SQL> select *
  2    from employee
  3   where first_name = 'Don';

EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY MANAGER DEPARTMENT_ID WAGE_INCREASE_WORTHINESS
——————————— —————————— ————————— ————————— —————— ——————— ————————————— ————————————————————————————————
          1 Don        Rose      26-OCT-16  95000    6576            40 Cost of Living Increase Eligible
1 row selected.

Note also that in the INSERT statement in Listing 12, the value specified for the virtual column, WAGE_INCREASE_WORTHINESS, is default. If you do not explicitly mention this column in your INSERT statement, your virtual column will still generate the default computation assigned to it. As with any other column with a DEFAULT value, you need not explicitly mention it in your DML statements if you want the DEFAULT value generated.

If you intend to use a sequence to generate integers for a particular column, you can simplify your DML statements that create these column values by ensuring that such column values are populated by default. The example in Listing 13 demonstrates adding a sequence value as a DEFAULT value to the EMPLOYEE table. Alternatively, you can define a column with an IDENTITY clause for a table column defined with a numeric type. The example in Listing 14 illustrates one of the syntax options for using IDENTITY-generated values for the EMPLOYEE_ID data as opposed to the EMPLOYEE_ID_SEQ values. You can read more about Oracle sequences in the Oracle documentation. You can find more information about the Oracle IDENTITY clause in the Oracle documentation by searching for the “identity” keyword.

Code Listing 13: Using a sequence as a DEFAULT value for EMPLOYEE_ID

SQL> alter table employee modify (employee_id NUMBER
                                  DEFAULT employee_id_seq.nextval);
Table altered.

SQL> insert into employee (first_name, last_name, hire_date, salary,
manager, department_id)
  2  values ('Gerald', 'Sowell', sysdate, 100000, 6576, 40);
1 row created.

SQL> commit;
Commit complete.

SQL> select *
  2    from employee
  3   where first_name = 'Gerald';

EMPLOYEE_ID FIRST_NAME LAST_NAME HIRE_DATE SALARY MANAGER DEPARTMENT_ID WAGE_INCREASE_WORTHINESS EMP_FULL_NAME
——————————— —————————— ————————— ————————— —————— ——————— ————————————— ———————————————————————— —————————————
         21 Gerald     Sowell    29-OCT-16 100000    6576            40 No Raise Yet             Gerald Sowell
1 row selected.

Code Listing 14: Using an IDENTITY column for unique integer generation

SQL> create table employee_identity as
  2  select * from employee;
Table created.

SQL> alter table employee_identity drop column employee_id;
Table altered.

SQL> alter table employee_identity
     add (employee_id number generated always as identity);
Table altered.

SQL> select employee_id, first_name, last_name
  2    from employee_identity
  3  order by employee_id;

EMPLOYEE_ID FIRST_NAME           LAST_NAME
——————————— ———————————————— —————————————
          1 Marcy                    Tamra
          2 Sasha                    Meyer
          3 Gerald                   Sowell
          4 Emily                    Eckhardt
          5 Frances                  Newton
          6 Donald                   Newton
          7 Matthew                  Michaels
          8 Roger                    Friedli
          9 Betsy                    James
         10 michael                  peterson
         11 mark                     leblanc
         12 Thomas                   Jeffrey
         13 Theresa                  Wong
         14 Lori                     Dovichi
         15 Mary                     Streicher
         16 Don                      Rose
16 rows selected.
Another Point of View

Another way to simplify query writing is to create a view. A view is a stored SELECT statement whose structure is a virtual table with columns and rows. Its query definition accesses one or more base tables, which can be either actual tables or other views. Consider the example in Listing 15. You’ll need the appropriate system privilege granted to the SQL_201 user to create a view similar to the one shown in Listing 15. The necessary steps and syntax for obtaining this privilege are illustrated in Listing 15.

 
Recompilation

Any DDL or DML you write is also referred to as source code. Oracle Database takes source code and hands it to a program called a compiler, which transforms the source code into object code (also referred to as bytecode, much like that used by the Java programming language) that is readable by Oracle Database. This transformation action is known as compilation.

When you change your source code (such as by renaming a table), certain database objects that are dependent on that source code may require recompilation. For example, views, synonyms, and PL/SQL are dependent upon table names. When these objects are accessed, Oracle Database checks to ensure that their definition and the definition of the database objects upon which they are dependent have not changed. If a change has taken place, Oracle Database will recompile the objects before fulfilling an access request.

The CREATE OR REPLACE keywords can be used to create a view for the first time or to update the definition of an existing view. The view in Listing 15 is composed of a query that returns a list of employees with their assigned managers and departments and their wage-increase worthiness. Once a view is created, it is not necessary to rewrite the view’s query to obtain the same result. As the last query in Listing 15 demonstrates, a view can be queried just like a regular table. The structure and complexity of the underlying query are contained only in the view and are, therefore, hidden from the user.

Code Listing 15: Simplifying query creation with views

--Need to be a privileged user to grant sql_201 permissions to create a view
SQL> connect / as sysdba
Connected.

--If you are using a pluggable database,EXAMPLE: alter session set container = <the container (pluggable database)of your choosing>;
SQL> alter session set container = dbim;
Session altered.

--Grant the appropriate system privilege to sql_201
SQL> grant create view to sql_201;
Grant succeeded.

--Reconnect as the sql_201 user and proceed with your view creation
SQL> connect sql_201@dbim
Enter password:
Connected.
SQL> create or replace view emp_manager_overview as
  2  select mgr.first_name||' '||mgr.last_name manager,
emp.first_name||' '||emp.last_name employee, dep.name "Department",
emp.wage_increase_worthiness
  3    from employee emp, employee mgr, department dep
  4   where emp.manager       = mgr.employee_id
  5     and emp.department_id = dep.department_id
  6  order by manager, employee;
View created.

SQL> select *
  2    from emp_manager_overview;

MANAGER           EMPLOYEEE        Department  WAGE_INCREASE_WORTHINESS
————————————————— ———————————————— ——————————— ————————————————————————————————
Donald Newton     Frances Newton   Accounting  Cost of Living Increase Eligible
Emily Eckhardt    Betsy James      Accounting  Cost of Living Increase Eligible
Emily Eckhardt    Donald Newton    Accounting  Cost of Living Increase Eligible
Emily Eckhardt    Lori Dovichi     Accounting  No Raise Yet
Emily Eckhardt    Marcy Tamra      Accounting  No Raise Yet
Emily Eckhardt    Matthew Michaels Accounting  Cost of Living Increase Eligible
Emily Eckhardt    Roger Friedli    Accounting  Cost of Living Increase Eligible
Mary Streicher    Don Rose         Marketing   Cost of Living Increase Eligible
Mary Streicher    Sasha Meyer      Marketing   Cost of Living Increase Eligible
Thomas Jeffrey    Theresa Wong     IT          Cost of Living Increase Eligible
michael peterson  mark leblanc     Payroll     Cost of Living Increase Eligible
11 rows selected.

Because the data retrieved from a view consists of only those columns listed in the SELECT list of the underlying query, views can be a helpful tool if you want to take secure measures to hide data. Consider the example in Listing 16. The EMP_WAGE_INCREASE_IT_VW view is created from the EMP_MANAGER_OVERVIEW view and consists of a small subset of data from its underlying view. The example in Listing 16 illustrates how columns in view definitions can be given names that differ from those in the base table or the underlying view.

Code Listing 16: Using views for hiding data

SQL> create or replace view emp_wage_increase_IT_vw as
  2  select employee "Reviewed Employee",
wage_increase_worthiness "Wage Increase Determination"
  3    from emp_manager_overview
  4   where "Department" = 'IT'
  5  order by employee;
View created.

SQL> select *
  2    from emp_wage_increase_IT_vw;

Reviewed Employee                  Wage Increase Determination
—————————————————————————————————— —————————————————————————————————
Theresa Wong                       Cost of Living Increase Eligible
1 row selected.

SQL> select "Reviewed Employee"
  2    from emp_wage_increase_IT_vw;

Reviewed Employee
—————————————————————————————————————————————————————————————
Theresa Wong
1 row selected.

SQL> select "Reviewed Employee"
  2    from emp_wage_increase_IT_vw
  3   where "Wage Increase Determination" = 'No Raise Yet';
no rows selected

Listing 16 also demonstrates how these new column names can be used in WHERE clauses in SQL statements that access the newly created view. Anyone granted access to the new views but not to the underlying base tables has access only to the data and column names returned from the new views. Note that a view can become invalid after you alter one of its base tables and might require recompilation. The syntax for recompiling the EMP_MANAGER_OVERVIEW view is

ALTER VIEW emp_manager_overview COMPILE;

Recompilation of a view should almost never be necessary in practice, however. Oracle Database will automatically recompile the view for you when you attempt to access it and the database detects that changes have been made to the base tables.

Virtual columns and views are similar in terms of their query-simplification and data-hiding capabilities, but virtual columns can also be indexed, constrained, and used as partition keys in a partitioning option (all concepts that will be discussed in subsequent articles in this series). These capabilities might make virtual columns a choice that’s superior to views. You can read more about Oracle views in the Oracle documentation.

Conclusion

This article has illustrated more about DDL, including how to drop and rename tables and how to recover tables by using the recycle bin. You also learned how purging syntax affects table recovery and the difference between truncating and dropping a table. You discovered how to use virtual columns to help simplify query writing. You also discovered how sequences and IDENTITY columns can be used for generating surrogate key values. Last, you were introduced to views and how they can assist with query writing and data hiding. The next article in this series will introduce you to indexes.

Next Steps

READ SQL 101, Parts 1–12.

LEARN more about relational database design and concepts.

DOWNLOAD the sample script for this article.

READ more Beyond SQL 101.

 

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.