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):
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 NameIn 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 FoundWhen 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
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
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
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 EventsRecall 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.
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.
ConclusionThis 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.
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.