![[SQL*Plus Login Screen]](SQLPlusTutorial_files/sqlplus_login.gif)
The Oracle Relational Database Management System (RDBMS) is an industry leading database system designed for mission critical data storage and retrieval. The RDBMS is responsible for accurately storing data and efficiently retrieving that data in response to user queries.
The Oracle Corporation also supplies interface tools to access data stored in an Oracle database. Two of these tools are known as SQL*Plus, a command line interface, and Developer/2000 (now called simply Developer), a collection of forms, reports and graphics interfaces. This technical working paper introduces the features of the SQL*Plus tool and provides a tutorial that demonstrates its salient features.
This tutorial is intended for students and database practitioners who require an introduction to SQL, an introduction to working with the Oracle SQL*Plus tool, or both.
This document is organized as follows. A brief overview of the suite of Oracle products is first presented in Section 2. In Section 3, we discuss the basics of working with the SQL*Plus tool. Structured Query Language (SQL), including data definition language (DDL) and data manipulation language (DML) is discussed in section 4. Advanced SQL*Plus commands are discussed in section 5 and a brief introduction to stored procedures and triggers is given in section 6.
Developing applications using an Oracle database requires access to a copy of the Oracle RDBMS (or a central Oracle RDBMS server), and one or more of the development tools. Third party development tools such as PowerBuilder, Visual Basic or Java can also be used for applications development.
Stand-alone development in a single user environment can be accomplished using the Personal Oracle or Personal Oracle Lite RDBMS in conjunction with Oracle Developer or a third party development tool.
Muli-user development in a shared environment can be accomplished using an Oracle RDBMS server running on a server machine. Distributed client PCs can develop the applications using any of the tools mentioned above.
Regardless of the development environment, used, the Oracle SQL*Plus utility is a convenient and capable tool for manipulating data in an Oracle database. In the following section, the SQL*Plus tool is introduced.
In the following sections, the basic functionality of SQL*Plus will be demonstrated along with sample input and output to demonstrate some of the many features of this product.
In this section, we give some general directions on how to get into the SQL*Plus program and connect to an Oracle database. Specific instructions for your installation may vary depending on the version of SQL*Plus being used, whether or not SQL*Net or Net8 is in use, etc.
Before using the SQL*Plus tool or any other development tool or utility, the user must obtain an Oracle account for the DBMS. This account will include a username, a password and, optionally, a host string indicating the database to connect to. This information can typically be obtained from the database administrator.
The following directions apply to two commonly found installations: Windows 95/98/NT/2000 or XP client (from here onwards, refered to simply as a Windows client) with an Oracle server, and a UNIX installation.
![[Start]](SQLPlusTutorial_files/win95start.gif)
button, Programs, Oracle -
OraHomeXX, Application Development and then SQL*Plus. The SQL*Plus login screen
will appear after roughly 15 seconds.
In the User Name: field, type in your Oracle username.
Press the
TAB key to move to the next field.
In the Password:
field, type your Oracle password.
Press the TAB key to move to
the next field.
In the Host String: field, type in the Service
Name of the Oracle host to connect to.
If the DBMS is Personal Oracle lite then this string might be
ODBC:POLITE. If the DBMS is a local Personal Oracle8, 8i or 9i
database, then the host string might be either beq-local or in some
cases, you can leave this field blank to connect to your local database
instance. Make certain your local instance is started. For Client/Server
installations with SQL*Net or Net8, this string will be the service name set up
by the SQL*Net or Net8 assistant software.
Finally, click on the OK button to complete the Oracle log in
process. SQL*Plus will then establish a session with the Oracle DBMS and
the SQL*Plus prompt (SQL> ) will appear. The following figure shows
the results of logging into Oracle using SQL*Plus:
![[SQL*Plus Running]](SQLPlusTutorial_files/sqlplus_running.gif)
There are a number of situations in which an error may occur:
In any of the above cases, an error message will be returned. If the Oracle server is not available or if you supply the wrong username or password, an error will be returned right away. If there is a networking problem, SQL*Plus may take several minutes before returning an error.
Here are some common error messages and some suggestions on how to resolve them:
Unfortunately, most versions of SQL*Plus will not re-display the login screen if your attempt to connect is unsuccessful. You should exit SQL*Plus completely by pulling down the File menu and choosing the Exit menu item. Then run SQL*Plus again from the beginning.
For users of a fresh installation of Peronsal Oracle8, 8i or 9i, a default username/password that is already set up is SCOTT/TIGER. The default DBA account for all Oracle databases is SYSTEM/MANAGER. However, you are strongly discouraged from practicing or doing development work in the SYSTEM schema as irreparable damage to your database can result.
For users of Personal Oracle Lite, there is a default database schema created upon installation of the software. To log into Personal Oracle Lite using SQL*Plus, supply the following values on the SQL*Plus login screen:
In the User Name: field, type in OOT_SCH
In the
Password: field, type in OOT_SCH
In the Host
String: field, type in ODBC:POLITE.
To run SQL*Plus under UNIX, log into your UNIX account and at the UNIX
command prompt (shown as unix% below), type the sqlplus
command followed by a carriage return. When prompted for a username, supply your
Oracle username (This may be the same as or different from your UNIX account
name). When prompted for a password, supply your Oracle account password (this
should not be the same as your UNIX account password).
To exit the SQL*Plus program (in any operating system), type EXIT and press Enter or carriage return:
Once a session has been established using the SQL*Plus tool, any SQL statements or SQL*Plus Commands may be issued. By default, all command are directed into the user's schema (which has the same name as the Oracle username). In the following section, the basic SQL*Plus Commands are introduced.
SQL*Plus commands allow a user to manipulate and submit SQL statements. Specifically, they enable a user to:
The following is a list of SQL*Plus commands and their functions. The most commonly used commands are emphasized in italics:
Examples of these SQL*Plus commands are given in the following sections.
Note the distinction made between SQL*Plus Commands and SQL Statements. SQL*Plus commands are proprietary to the Oracle SQL*Plus tool. SQL is a standard language that can be used is just about any Relational Database Management System (RDBMS).
The following two sections describe how to invoke help in SQL*Plus under Windows and under UNIX. The method you use to access help may differ according to how your software was installed.
To get HELP on any of the oracle tools, use the Oracle8 or Oracle8i
Documentation which is accessible through a web browser. To access the Oracle8
Documentation, click on the Windows button, then Programs, Oracle -
OraHomeXX and finally Oracle8 Documentation: 
. This will launch your local Web
Browser (Netscape Navigator/Communicator or Microsoft Internet Explorer) and the
Welcome to the Oracle8 Documentation Library! screen will be displayed. From
here, click on the "TEXT VERSION" link to get to the Oracle Product
Documentation Library.
Once in the Oracle8 Documentation main screen, click on Oracle8 Enterprise Edition and then SQL*Plus Getting Started for Windows NT/95. Other documentation you may find useful are:
| Help File/Link | Contents |
|---|---|
| SQL Reference | Comprehensive syntax for all SQL statements |
| SQL*Plus Getting Started for Windows NT/95 | Specific SQL*Plus commands and options for Windows 95 and NT users. |
| SQL*Plus Quick Reference | Quick reference guide to SQL*Plus commands. |
| SQL*Plus User's Guide and Reference | Comprehensive guide to using SQL*Plus. |
Each of these can be found on the same Oracle8 Enterprise Edition page.
Note the the arrangement of help screen and on-line documentation in other versions of Oracle8 and Oracle8i may be slightly different.
HELP SELECT as follows: As a final note, Oracle makes much of its documentation available on its web site at the Oracle Documentation Center at http://docs.oracle.com/
In the relational model, data is stored in structures called relations or tables. Each table has one or more attributes or columns that describe the table. In relational databases, the table is the fundamental building block of a database application. Tables are used to store data on Employees, Equipment, Materials, Warehouses, Purchase Orders, Customer Orders, etc. Columns in the Employee table, for example, might be Last Name, First Name, Salary, Hire Date, Social Security Number, etc.
SQL statements are issued for the purpose of:
The SQL language has been standardized by the ANSI X3H2 Database Standards Committee. Two of the latest standards are SQL-92 and SQL-99. Over the years, each vendor of relational databases has introduced new commands to extend their particular implementation of SQL. Oracle8i's implementation of the SQL language conforms to the "Entry Level" SQL-99 standards and is partially compliant with the Transitional, Intermediate, and Full levels of SQL-99. For more details on the exact compliant and non-compliant features, please refer to the Oracle8i or Oracle9i SQL Reference book.
Some brief examples of SQL statements follow. For all examples in this tutorial, key words used by SQL and Oracle are given in all uppercase while user-specific information, such as table and column names, is given in lower case.
To create a new table to hold employee data, we use the CREATE TABLE statement:
CREATE TABLE employee
(fname VARCHAR2(8),
minit VARCHAR2(2),
lname VARCHAR2(8),
ssn VARCHAR2(9) NOT NULL,
bdate DATE,
address VARCHAR2(27),
sex VARCHAR2(1),
salary NUMBER(7) NOT NULL,
superssn VARCHAR2(9),
dno NUMBER(1) NOT NULL) ;
To insert new data into the employee table, we use the INSERT statement:
INSERT INTO employee
VALUES ('BUD', 'T', 'WILLIAMS', '132451122',
'24-JAN-1954', '987 Western Way, Plano, TX',
'M', 42000, NULL, 5);
INSERT INTO employee
VALUES ('JANE', 'V', 'SMITH', '004321234',
'04-JUL-1963', '44 Forth St., Easytown, WA',
'F', 56000, '132451122', 4);
To retrieve a list of all employees with salary greater than 30000 from the employees table, the following SQL statement might be issued (Note that all SQL statements end with a semicolon):
To give each employee in department 5 a 4 percent raise, the following SQL statement might be issued:
UPDATE employee
SET salary = salary * 1.04
WHERE dno = 5;
To delete an employee record from the database, the following SQL statement might be issued:
DELETE FROM employee
WHERE ssn = '004321234' ;
The above statements are just an example of some of the many SQL statements and variations that are used with relational database management systems. The full syntax of these commands and additional examples are given below.
An Oracle database can contain one or more schemas. A schema is a collection of database objects that can include: tables, views, indexes and sequences. By default, each user has their own the schema which has the same name as the Oracle username. For example, a single Oracle database can have separate schemas for HOLOWCZAK, JONES, JSHIH, SMITH and GREEN.
Any object in the database must be created in only one schema. The object
name is prefixed by the schema name as in: schema.object_name
By
default, all objects are created in the user's own schema. For example, when
JONES creates a database object such as a table, it is created in her own
schema. If JONES creates an EMPLOYEE table, the full name of the table becomes:
JONES.EMPLOYEE. Thus database objects with the same name can be
created in more than one schema. This feature allows each user to have their own
EMPLOYEE table, for example.
Database objects can be shared among several users by specifying the schema name. In order to work with a database object from another schema, a user must be granted authorization. See the section below on GRANT and REVOKE for more details.
Please note that many of these database objects and options are not available under Personal Oracle Lite. For example, foreign key constraints are not supported. Please see the on-line documentation for Personal Oracle Lite for more details.
SQL*Plus accepts SQL statements that allow a user to create, alter and drop table, view and sequence definitions. These statements are all standard ANSI SQL statements with the exception of CREATE SEQUENCE.
ALTER TABLE has the following syntax for renaming a table:
ALTER TABLE has the following syntax for adding a new column to an existing table:
Note: In earlier versions of Oracle (before Oracle8), there is no single command to drop a column of a table. In order to drop a column from a table, you must create a temporary table containing all of the columns and records that will be retained. Then drop the original table and rename the temporary table to the original name. This is demonstrated below in the section on Creating, Altering and Dropping Tables.
Finally, ALTER TABLE can also be used to add a constraint to a table such as for a PRIMARY KEY, FOREIGN KEY or CHECK CONSTRAINT. The syntax to add a PRIMARY KEY is:
where sql select statement is in the form:
Additional information on the SELECT statement and SQL queries can be found in the next section.
Note that an ORDER BY clause may not be added to the sql select statement when defining a view.
In general, views are read-only. That is, one may query a view but it is normally the case that views can not be operated on with INSERT, UPDATE or DELETE. This is especially true in cases where views joing two or more tables together or when a view contains an aggregate function.
In the following section, each of the SQL DDL commands will be discussed in more detail.
A table is made up of one or more columns (also called attributes in relational theory). Each column is given a name and a data type that reflects the kind of data it will store. Oracle supports four basic data types called CHAR, NUMBER, DATE and RAW. There are also a few additional variations on the RAW and CHAR data types. The basic datatypes, uses and syntax, are as follows:
The VARCHAR data type is a synonym for VARCHAR2. It is recommended to use VARCHAR2 instead of VARCHAR.
A column may be specified with a NULL or NOT NULL constraint meaning the column may or may not be left blank, respectively. This check is made just before a new row is inserted into the table. By default, a column is created as NULL if no option is given.
In addition to specifying NOT NULL constraints, tables can also be created with constraints that enforce referential integrity (relationships among data between tables). Constraints can be added to one or more columns, or to the entire table.
Each table may have one PRIMARY KEY that consists of a single column containing no NULL values and no repeated values. A PRIMARY KEY with multiple columns can be designated using the ALTER TABLE command. We create primary keys with NOT NULL constraints to uphold entity integrity.
Up to 255 columns may be specified per table. Column names and table names must start with a letter and may not contain spaces or other punctuation except for the underscore character. Column names and table names are case insensitive. This means that you can specify the names of columns and tables in any way you like. For example, the following three SELECT statements are all identical:
In the following example, a new table called ``employee'' is created with ten columns of a variety of types. The columns indicated by NOT NULL will be mandatory while the other columns, by default, will be optional.
The numbers 2 through 11 before each line indicate the line number supplied by the SQL*Plus program as this statement was typed in. We will omit these numbers in the rest of the examples to facilitate copying and pasting this material directly into a live SQL*Plus session.
A new table can also be created with a subset of the columns in an existing table. In the following example, a new table called emp_department_1 is created with only the fname, minit, lname and bdate columns from the employee table. This new table is also populated with data from the employee table where the employees are from department number 1.
One can also create a new table with all of the columns from the original table, but with only a subset of the rows form the original table:
SQL> CREATE TABLE high_pay_emp
AS SELECT *
FROM employee
WHERE salary > 50000 ;
Table created.
DESCRIBE is an SQL*Plus command that displays the columns of a table and their data types. The syntax for the DESCRIBE command is:
The copying of data can be suppressed by giving a WHERE clause that always
evaluates to FALSE for each record in the source table. The following example
makes a duplicate of the employee table but does not copy any data
into it.
Constraints can be added to the table at the time it is created, or at a later time using the ALTER TABLE statement. Constraints can include:
Here is an example of creating a primary key constraint on the ssn column:
Note the name given to the constraint is pk_employee_ssn
Referential integrity constraints can also be added. Referential integrity
refers to the behavior of child records when the parent record is modified or
deleted. In the following example, the dno column in the
employee table references the dnumber column in the
department table. If a department is deleted, all employees that
reference the department are also deleted. This is given by the ON DELETE
CASCADE option. (be sure to DROP TABLE employee before you try the next
example).
In order to specify a foreign key constraint, the column in the parent (or
master) table (e.g., the dnumber column in the
department table in the above example) must be either the primary
key or a unique key for the table. Thus, the parent (or master) table must be
created first before the child (or detail) table is created using the above
constraints.
Additional CREATE TABLE constraint statements allow the specification of what
should happen when a row is deleted or updated in a parent table. In the above
example, deleting a department causes all employees in that department to also
be deleted. Other options include ON DELETE SET DEFAULT and
ON DELETE SET NULL. In addition, the behavior of child tables when
a parent table is updated can also be specified using an ON UPDATE
clause.
CHECK constraints can be added to check the values for a given column. This can be used to allow only a specific set of valid values for a column. In the following example, CHECK constraints are added to limit the valid values for the sex column and to check if the salary is greater than 10,000 (be sure to DROP TABLE employee before you try the next example).
The CHECK constraints are activated when inserting a new row or when updating existing data. In the following example, the value given for sex is 'm':
In the previous examples, constraints were given names with the following prefixes:
The ALTER TABLE command can be used to add a new column to an existing table
or to change the data type of an existing column. The following examples add a
new column manager to an existing table named
emp_department_1 and then modify the data type of the
fname column.
The ALTER TABLE command can also be used to change the datatype of column provided there is no data in the table. To get around this if there is data in the table, create a temporary table using all of the data from the existing table, delete the existing records from the original table, alter the datatype, and then insert the records from the temporary table back into the original table. For example, assume the emp_department_1 table has some records in it and we want to change the datatype for the MANAGER column:
The DROP TABLE command can be used to drop a table definition and all of its
data from the database. In the following example, the table
emp_department_1 created previously, is dropped from the database.
To see which tables are defined in a schema, submit a query to the USER_TABLES view of the database system catalog:
Create another table called COURSES with the following columns and data types:
Next, use the ALTER TABLE statement to add the following column to the STUDENTS table:
Use the ALTER TABLE statement to define the StudentID and CourseNumber (called a composite key) as the PRIMARY KEY for the COURSES table. To do this, list both of the column names separated by a comma.
Use the ALTER TABLE statement to define StudentID in the COURSES table as a FOREIGN KEY that references the primary key StudentID in the STUDENTS table.
Finally, add some data to the STUDENTS and COURSES tables (simply copy and
paste these statements into SQL*Plus to add the data):
A good naming convension to use for indexes is to start with the letters idx_ followed by the name of the table, and finally the name of the column(s) used to create the index.
It is also possible to create indexes on other columns of a table. For
example, if the employee table is frequently accessed by
superssn, an index can be created on that column as well:
To see which indexes are defined in a schema, submit a query to the USER_INDEXES view of the database system catalog:
Create an index on the COURSES table for the semester and year columns (together).
employee
table is created. When a view is defined, a SQL statement is associated with the
view name. Whenever the view is accessed, the SQL statement will be executed.
In the following example, the view emp_dno_1 is created as a
limited number of columns (fname, lname, dno) and limited set of data
( WHERE dno=1 ) from the employee table.
Once the view is created, it can be queried with a SELECT statement as if it were a table.
Views can be dropped in a similar fashion to tables. The DROP VIEW command provides this facility. In the following example, the view just created is dropped.
Views can also be created to join several tables together. The following is an example of creating a view that joins two tables:
As a final example, a view can be created that contains an aggregate function. In the following example, a view is created that returns the average salary of all employees per department.
In general, views are read-only as the above cases demonstrate.
To see which views are defined in a schema, submit a query to the USER_VIEWS view of the database system catalog:
Query the view and show the output.
Create another view called VW_COURSES_TAKEN based upon the following SQL SELECT statement:
Query the VW_COURSES_TAKEN view and show the output.
In this example, the sequence will begin its numbering at 1 and count up (in increments of 1 which is the default) until it reaches 9999. Once the MAXVALUE is reached, accessing the sequence will return an error.
Sequences are accessed using a SELECT statement with a special table called
Sequences can also be used in INSERT statements to automatically provide the
next value for a key. For example, to insert a new department row with the next
department id in the sequence, the following statement would be issued:
As with most database objects, Oracle Sequences can be dropped using a DROP
SEQUENCE command. Dropping a sequence and then re-creating it has the effect of
resetting the sequence to its START WITH number. In the following example, the
department number sequence created previously is dropped.
Sequences can also be altered to change the INCREMENT BY, MAXVALUE or START
WITH values. The ALTER SEQUENCE statement achieves these changes.
To see which sequences are defined in a schema, submit a query to the
USER_SEQUENCES view of the database system catalog:
Then, write an SQL INSERT statement to insert a new record for the following
person: The actions are defined as follows:
In addition to objects such as tables, the SELECT and UPDATE actions may also
be granted on individual columns in a table or view.
The general syntax for the GRANT statement is: At this point, user BOB may now issue SQL SELECT statements on the table
ALICE.employee. For example, user BOB may execute: The REVOKE statement reverses the authorization by removing privileges from a
subject (user). The syntax for REVOKE is: One can also REVOKE only a portion of the privileges from a user. For
example, suppose ALICE granted the following: A shortcut to revoking all of the privileges on an object can be given by
using the keyword ALL in the REVOKE statment. For example, if ALICE wishes to
revoke all privileges on department from BOB, she could submit: A special group of users called PUBLIC represents all of the users in a
database instance. GRANT can be used with the PUBLIC group to allow all users to
work with a table. For example the following GRANT statement allows any user to
select records from the employee table: The current authorizations in effect can be viewed by selecting from the
USER_TAB_PRIVS view. In the following example, the columns are first formatted
(more examples of this are given in a later section), and then the privileges
for the user (table owner) ALICE are displayed.
A quick way to generate a list of GRANT statements for every table in your
schema is to run a query on the catalog that forms the GRANT statements: The result of this query is something like the following: To see which privileges you have granted to others, submit a query to the
USER_TAB_PRIVS_MADE view of the database system catalog: To see which privileges have been grated to you, submit a query to the
USER_TAB_PRIVS_RECD view of the database system catalog: One additional caveat: When one user inserts, deletes or updates data in a
table, that user should issue an explicit COMMIT; so that these changes become
visible to others who may have permissions to view the table.
Then REVOKE the SELECT privilege from the STUDENTS table. Have your group
member try to query the table after you have revoked access and see what
happens.
Query the USER_TAB_PRIVS_RECD and USER_TAB_PRIVS_MADE views to see which
privileges have been granted to you and which ones you have granted to others.
One problem with this method is that if the tables move to another user's
schema, all of the references will need to change.
An alternative is to use Synonyms to provide a pointer to the schema
and database objects. A Synonym is like a pointer in that is has a name that is
recognized in the local schema that, when addressed, will resolve to the
schema.object name in another user's schema.
Synonyms are created with the CREATE SYNONYM command: For example, if Bob wishes to have access to Alice's employee table, first,
Alice would need to GRANT access to her table using the GRANT command, and then
Bob would create a synonym using: Now Bob can execute the following query: If the tables are moved to another schema such as Abe's schema, then only the
synonyms need to be dropped and recreated. All applications will run the same.
To generate a list of CREATE SYNONYM statements, use the following type of
query:
To see which synonyms have been created in your schema, query to the
USER_SYNONYMS view of the database system catalog: In this section, the SQL commands for creating, altering and deleting tables,
views and sequences, and granting and revoking access to database objects have
been introduced. A typical database may have a dozen or more related tables with
several columns each. To facilitate the creation and deletion of a large number
of tables, the CREATE statements can be placed into a file and executed using
the SQL*Plus START command.
SQL*Plus allows the user to enter SQL statements to select, insert, update
and delete rows in database tables. These are all standard SQL statements.
A commit is also done implicitly when the next SQL DDL statement is
executed or the user exits SQL*Plus.
If the WHERE clause is omitted, all rows in the table will be deleted.
If a value for each column in the table is supplied, then the columns do
not need to be listed in the first set of parenthesis. Values can be of 3
types: Character, Number or Date. Each one requires a slightly different
format when inserting:
All values, regardless of data type, must be separated by commas.
Another option for the INSERT statement is to pull some data from another
table. The syntax is: For example, assume table1 and table2 have the same number of columns and
the corresponding columns have the same data types. To insert all data
currently in table1 into table2: The WHERE clause, GROUP BY, HAVING and ORDER BY statements are optional. If
a WHERE clause if omitted, all rows in the table will be retrieved. If the
ORDER BY statement is omitted, there is no specific order in which the rows
will be displayed. GROUP BY and HAVING are used in conjunction with aggregate
functions (functions that operate on more than one record). If all columns in
the table are to be retrieved, an asterisk (*) may be substituted for the
entire list of columns after the SELECT key word.
More than one table can be specified in the FROM clause. The WHERE clause
typically contains logic expressions (such as A more complete syntax for the SELECT statement is: The expression can be either a single value or an arithmetic
expression including another column in the table. More than one column can be
updated at a time by adding additional column name =
expression pairs separated by commas. If the WHERE clause is omitted,
the update is applied to all rows in the table.
For example, to give all employees in the marketing department a 3% raise: In the following example, a new row is inserted into the
To check the contents of the In the next example, a row in the In the final example, a row is deleted from the In the final example, if the ROLLBACK command was given instead of the COMMIT
command, the rows would have been undeleted.
Note that many of these statements and commands will not work properly under
Personal Oracle Lite.
The Oracle Data Dictionary maintains a collection of USER_ views
that are accessible from each user's schema. The following table summarizes
these views:
A comprehensive list of user catalog views can be found in the Oracle Server
Reference guide.
Many of the view contain columns of type LONG. In order to display their
content, set the SQL*Plus variable LONG to a large number such as 4096
as follows: To find out the names of tables you have created, use the system view called
The column definitions for a table can be displayed using the
More detailed metadata can be retrieved from the tables To get information on columns of a table, use the following (substitute
'EMPLOYEE' with the name of the table in question): To see any constraints that are presently in effect on a table, use the
following (substitute 'EMPLOYEE' with the name of the table in question): A list of Indexes defined on tables in the user's schema can be displayed by
querying the USER_INDEXES table: Finally, a list of Views the user owns can be displayed by querying the
USER_VIEWS table: The following table lists the major pseudo columns:
To use an aggregate function and a regular non-aggregated column in the same
query, a GROUP BY clause must be added to the SELECT statement.
Examples of functions are given in the following section.
Example Table STUDENTS:
Another option is to enclose some text in quotes and concatenate that text
with the output of the SQL statement: Note the two aliases given to the students table: s1 and
s2. These allow us to refer to different views of the same table.
You may wish to sort the output based on the GPA. In this case, the output is
ordered by GPA in decending order (highest GPA will come first, etc.):
In MS Access:
The following example prints a tree structure modeled after the tutoring
relationships in the Students table. We will start with Mary's student id (102)
since no one tutors her. This assumes the subquery returns only one tuple as a result. In the above case, the subquery returns a set of tuples. The IN clause
returns true when a tuple matches a member of the set.
The above query shows all employees names and salaries where there is at
least one person who makes more money (the first exists) and at least one
person who makes less money (second exists).
The above query shows all employees for whom there does not exist an
employee who is paid less. In other words, the highest paid employee.
Show the departments with average salary greater than 33000. Show departments with 3 or more employees: Show all employees whose name starts with 'S' Show all employees whose name contains the letter 'E' and the letter 'L' in
that order: Show all employees whose name contains the letter 'E' and the letter 'L' in
any order:
The DECODE function can also be used to invert a table. For example to show
the department names along a single row we might use:
Please note that while SQL*Plus does
include the following commands to edit SQL statements directly within SQL*Plus,
you are likely to be far better off using a simple text editor such as pico or
emacs in UNIX, or Notepad in MS Windows to perform your editing. If you intend
to work in this fashion, then you can safely skip section 5.1. Having said all
of that, if you are still interested in learning SQL*Plus' editing techniques,
please read on ahead.
SQL*Plus has several commands to allow the user to edit or modify SQL
statements. Once a new SQL statement has been typed in (ending with a ;)
this statement is placed into a buffer and is considered to be the
current SQL statement. All of the following commands operate on the
current SQL statement in the buffer.
SQL statements may be typed with a free format. Spaces and
<cr> characters may be used to separate key words in a SQL
statement. SQL*Plus displays line numbers in the left hand margin indicating the
current line for a SQL statement that spans multiple lines.
In the following example, an erroneous SQL statement has been entered. After
the ; was typed, an error message was displayed indicating the
approximate location of the error and a brief error message description.
To correct line number 2, the user can type the line number followed by the
correct portion of the SQL statement. This corrects the SQL statement in the
buffer. The last step is to execute the SQL statement in the buffer by typing
the RUN command.
The LIST command can be used to display the current contents of the SQL
buffer. An asterisk (*) is used to mark the current line of the SQL statement
within the buffer.
The current line of the SQL statement in the buffer can be appended using the
APPEND command. The syntax is: APPEND new text. In the following example
the new text ``xyz'' is appended to line number 3 which is the current line in
the buffer.
Text on a line in the SQL statement can also be replaced using the CHANGE
command. The syntax for the CHANGE command is: CHANGE / old text / new
text /
In the following example, text on the current line number 3 will be replaced
with blank text:
To move to a different line of the SQL statement in the buffer, simply type
the line number.
The DEL command can be used to delete the current line of the SQL statement
out of the buffer as in the following example.
A SQL statement in the buffer can be saved to a file for later use. The SAVE
command serves this purpose. The syntax for the SAVE command is: SAVE
filename
In this example, the current contents of the buffer are saved to a file
called query.sql:
A directory and/or drive letter (for those using MS DOS or MS Windows) can be
placed in front of the file name in order to re-direct the file to another drive
or directory. For example, to save the current statement to a floppy disk: A SQL statement saved in a file can then be loaded and executed using the
START command. The syntax for the START command is: START filename
Here, the file query.sql created in the previous example is loaded and
executed using the START command:
Again, a drive letter and/or directory name can be placed in front of the
file name.
In many cases, it is easiest to create and edit a set of text files
containing the queries and then use the START command to execute them.
Instructions for this vary depending on the operating system. For example, under
a UNIX system, one can use a text editor such as Under MS Windows, one can use the Windows NotePad editor to create these same
types of files. If the files are stored on a floppy disk (for example, the
When working with SQL statements and SQL*Plus commands in a script file, be
sure and make backups of your disks and files.
The formatting commands include:
Note that none of these SQL*Plus formatting commands changes the underlying
table structures.
Perhaps the most useful command is The Some of the SQL*Plus variables of interest include The following example shows an SQL*Plus script file
(
The BREAK command is useful for formatting the output of queries
where the same values in a column will not be repeated in the output. For
example, assume the following query is executed: Additional columns can also participate in the groupings made by BREAK by
appending ON columnname to the command. For example: For this execise, take the following query and create a script file with
appropriate titles and column formatting. Use the BREAK ON command so that the
DNO and DNAME only appears once for each group of employees in the department. SQL*Plus has a command called The The Note that some SQL*Plus commands will not show up in the SPOOL file. To have
them echo to the SPOOL file, use the Also, when SPOOLing to a file, SQL*Plus makes each line 80 characters long by
padding with spaces. This can be shortened to fewer characters using the
The
For this exercise, take the script created for exercise 9 and add the
necessary SQL*Plus commands to spool the report to a text file. One in a text
file, load this file into a text editor or word processor and print it. Note
that if you are loading a text file into a word processor such as MS Word, you
should change the font of the text to Courier or Courier New (this is a fixed
width font) so that the output lines up correctly.
The following example shows the prompt/accept sequence for a query. When this script is executed, the following output is shown:
The first PROMPT command simply echoes out a line to the display. The second
command, ACCEPT, accepts input from the user. In this case, the variable that
will hold the input is called Once the user types the department number and presses enter, the variable
In the following example, additional commands have been added including the
SET VERIFY and REMARK commands to improve the script.
When this script is executed, the following output is shown:
For variables that are not numeric type, the data type is not required in the
ACCEPT command. For example, to search for a specific employee by name: In the above example, the UPPER() function is used to change both the column
values of lname and the text the user supplied for the
&name variable to all upper case letters. In this way, a match will
be made even if the user types in lower case letters for the &name.
variable.
Create a script that accepts a salary from the user and then returns a list
of employees who make more the amount provided by the user. The following is the
desireable output:
Starting with Oracle8i and continuing with Oracle9i, SQL*Plus has the ability
to generate HTML output in place of the normal text based output generated from
query results. This facility is especially useful when combined with a web
server. Through the use of SQL script files and the SPOOL command, one can
easily build a relatively robust query engine that accepts parameters from a web
browser, writes a query and additional formatting commands to a script file,
invokes SQL*Plus with the script file and then returns the resulting output file
(with HTML code) to the user's web browser. Below are a few techniques to get
started using HTML output.
The first step in generating HTML output is to work with the SET MARKUP
command in SQL*Plus. At the SQL> prompt, switch the MARKUp to HTML as
follows: Other SQL*Plus commands such as TTITLE will also produce appropriate HTML
markup.
For this exercise, use the script created in exercise 10 (with the ability to
SPOOL to a file named "report.htm") and add the necessary commands to generate
HTML output from the query. Load the resulting spool file into a web browser.
To check the execution plan for each SQL statement, turn the
AUTOTRACE option on with the following SQL*Plus command: To turn off AUTOTRACE, issue the following command: The next useful command for performance monitoring is called TIMING.
This command starts a timer that can be read at any interval, similar to how a
stopwatch operates. To set up a timer, issue the following command: To stop a timer, issue the TIMING STOP command. Note that AUTOTRACE
and TIMING should probably not be used in conjunction as it would be difficult
to separate the execution time for the SQL statement from the time taken to
generate the plan and statistics.
Produce the query plan and time the execution of the following SQL statement:
The TO_CHAR and TO_DATE functions can be used to convert dates to other
formats, however, this may become inconvenient, especially when inserting a
large number of rows.
The ALTER SESSION statement can be used to alter various characteristics of
the current SQL*Plus session including the default date format. This statement
is often used to format dates to conform to regional customs. The syntax of
ALTER SESSION for use with changing the default date format is as follows: The date_format can include the following codes:
For example, to change the default date to include a full four digit year,
issue the following ALTER SESSION statement: Note that this change only remains in effect for the current session. Logging
out of SQL*Plus and logging back in (or re-connecting to the Oracle database
using the connect command) will reset the date format back to its
default.
The basic PL/SQL programming unit is called a block. A block begins with the
BEGIN keyword and ends with the END; keyword. All statements
and blocks in PL/SQL must end with semicolons. Below is one example of a stored
procedure block: Note: To see the outputs from stored procedures, type the
command SET SERVEROUTPUT ON at the SQL> prompt in SQL*Plus. The
PUT_LINE procedure is like the printf function in "C" or the
System.out.println method in Java
Variables may be declared in a block. Data types for the variables match the
underlying datatypes supported by the DBMS. The most commonly used data types
are therefore: VARCHAR, NUMBER and DATE. For example: In addition to using these data types, one can also declare variables based
upon the data types of columns in database tables. This is done using the %TYPE
syntax. For example:
For this exercise, write a PL/SQL block that declares three variables: AGE,
MIDDLE_INITIAL and SALARY. For the MIDDLE_INITIAL and SALARY, use the data types
that correspond to the column data types in the employee table (the MINIT and
SALARY columns). Declare the AGE variable as NUMBER. Assign values to each of
these variables and then use the PUT_LINE procedure (as was done above) to
output each of them.
The PL/SQL language has all of the conditional (IF ...THEN) looping (WHILE),
assignment, variable declaration and other language constructs of a complete
programming language. SQL statements may be freely mixed in with the other
programming statements. Below is an example of a conditional statement
(IF...THEN): Below is an example of a looping construct (FOR): The major change to the SQL Data Manipulation Language is to the syntax of
the SELECT statement. All SELECT statements in PL/SQL must use the INTO clause
to redirect the rows returned by the SELECT into variables. The syntax of the
SELECT statement is: Variables named in the INTO clause correspond to the order of columns
selected in the SELECT clause. For example: The above PL/SQL block declares two variables and then executes a SELECT
statement returning the salary in PL/SQL variable empsalary and the
department number in PL/SQL variable empdepartment for employee SMITH. If
the empdepartment is equal to 1 then an SQL UPDATE statement is executed.
It is possible that a SELECT...INTO statement can return more than on row or
record, or no records at all. In such situations, the entire SELECT statement
will fail resulting in what is called an EXCEPTION. EXCEPTIONs in PL/SQL must be
handled (taken care of) by some code. Most all triggers and stored procedures
that use SELECT...INTO have EXCEPTION handling code.
The EXCEPTION code the following syntax:
To continue the above example, the exception code would appear as follows at
the end of the regular stored procedure code:
There are two main ways of storing PL/SQL code in the Oracle database: CREATE
PROCEDURE and CREATE TRIGGER. Triggers are procedures that are executed in
response to some event. Events include the execution of a DML statement on a
table (such as INSERT, UPDATE, DELETE, MODIFY). Procedures are typically used to
implement general program logic that can be shared across applications, triggers
and utilities. A procedure must be explicitly called by an application, trigger
or program.
It is common practice to store general business rule checking in procedures.
This allows applications to check data validity before a transaction is
submitted to the database. Triggers can also call the procedures to check data
at the database level. Since the business rules are coded in a single set of
procedures, maintenance of this code is simplified. In this section, we will
introduce the syntax for creating triggers and demonstrate the use of a trigger
to enforce a business rule.
Here is an example trigger called check_age used to check if an
employee is over the age of 16. This trigger will be executed in response to the
events of INSERT or DELETE on the employee table. The check_age trigger
code will be executed BEFORE the affects of the SQL statement are put into
place. Finally, check_age will execute FOR EACH ROW affected by the SQL
statement.
Lines starting with the double minus sign -- are comments and are
ignored by the trigger.
After this code has been entered in SQL*Plus, an additional line will appear
as if the SQL statement should continue. To complete entering the trigger code,
type a forward slash / and the code will be submitted.
One of three things will happen when a new procedure or trigger is created:
To view the compilation errors check the USER_ERRORS view: To see if the trigger compiled correctly, look in view USER_ERRORS as
follows: Alternately, use the SQL*Plus command SHOW ERRORS.
To view the trigger code: Or, in a more compact form: Once the trigger has been entered without syntax errors, it can be tested.
See what happens when we attempt to insert a new employee record where the
employee's birthdate is less than 16 years ago: The following example implements a simple inventory system. The Products
table holds a list of products with a productid as the key and a description.
The inventory location table holds a series of locations in the warehouse
including an identifier and the aisle, tier and bin. Finally, the intersection
of these two tables is the inventory table which takes a locationid and a
productid and gives the quantity of the product present at the location.
SQL Statements to create and populate tables
The SQL code to create and populate the three tables is given below:
First step: Create three tables and add constraints We can create a view to implement this query:
To see the output from the stored procedures, set the following options
(Note: You must do this each time you log into SQL*Plus). Stored Procedures to Add and Remove items From inventory
Adding a new product to an existing location requires the following:
Removing an existing product from inventory requires the following:
Note: When creating stored procedures in SQL*Plus, you must type a slash
character ( / ) on the line after the last END; of the
procedure. This lets SQL*Plus know to submit the CREATE PROCEDURE statement to
the database.
To see any errors from the compilation of the procedure use the show
errors command. To run the stored procedures, use the EXECUTE command
followed by the name of the procedure and any parameters. For example, to add 10
units of product P500 to location 106, execute the following: To remove 10 units of procedure P500 from location 106, execute the
following: In this section, we have shown some basic forms of triggers and stored
procedures. For additional information and examples on the PL/SQL language,
please refer to the Oracle PL/SQL User's Guide and Reference.
To start the Oracle SQL*Plus Worksheet under Windows, locate the Application
Development menu on the Windows Start menu (this is typically found within the
menu associated with the Oracle Home for the local database): As with most Oracle tools, there will be three prompts for Username, Password
and database (called Service in this tool). In this example, the
SCOTT/TIGER schema is used on a local instance of Oracle:
Once logged in, the following SQL*Plus Worksheet window should appear:
The icons down the left hand side are used to:
Finally, the EMP table is queried:
A nice feature of this tool is that any output shown in the bottom window can
be saved to a text file by pulling down the File menu and choosing Save
Output As.
DUAL. The seq_department_number sequence is retrieved: Exercise 4: Working with Sequences
For this exercise, start by creating
an Oracle Sequence called seq_student_id. Have the sequence start
numbering at 120 and go up to 999.
4.2.2 Grant and Revoke Statements
The GRANT and REVOKE statements allow
a user to control access to objects (Tables, Views, Sequences, Procedures, etc.)
in their schema. The Grant command grants authorization for a subject
(another user or group) to perform some action (SELECT, INSERT, UPDATE,
DELETE, ALTER, INDEX) on an object (Table, View, stored procedure,
sequence or synonym).
GRANT SELECT
ON employee
TO BOB ;
REVOKE SELECT
ON employee
FROM BOB ;
GRANT SELECT, INSERT, UPDATE, DELETE
ON department
TO BOB ;
However, later on she decides that BOB should only be able to select from
the department table. The following REVOKE statement would address the
situation: REVOKE INSERT, UPDATE, DELETE
ON department
FROM BOB ;
This would leave BOB with only the select privilege on the department
table.
REVOKE ALL
ON department
FROM BOB;
Exercise 5: GRANT and REVOKE
GRANT SELECT privileges to another member
of your group. Have them query your STUDENTS table.
4.2.3 Synonyms
In many cases, a schema is created under a single
username but other users must have access to the tables, sequences and stored
procedures. One possibility is to code all queries and applications to
specifically access these database objects by providing a schema name. For
example; 4.3 SQL Data Manipulation Language
In this section, we discuss SQL
statements that can be used to manipulate data in tables and views.
4.3.1 Select, Insert, Update, Delete,
Commit and Rollback Data
For example: 'Bill
Smith'
For example: 123, 44000.12
For
example: '26-JUN-1996'
Note that different versions of Oracle accept only
2 digit years for an INSERT statement. All newer versions (from Oracle8 on)
appear to default to using 4 digit years. INSERT INTO table2
SELECT * FROM table1;
WHERE salary >
40000) that are evaluated for each row in the table.
employee table. Since a value is supplied for each column, the
columns do not need to be explicitly listed.
employee table, a SELECT statement
is done on the table.
employee table is updated.
employee table.
4.3.2 Displaying Table Metadata (Data about the data)
Once database
objects have been created, it is often useful to query the data dictionary to
see the various characteristics of the objects. In this section, we describe
several ways to query the data dictionary to retrieve this information.
USER View
Contents
Typical Query
USER_TABLES
Table names and storage details about tables a user owns
SELECT table_name FROM USER_TABLES;
CAT or TAB
Brief list of tables and views for a user
SELECT * FROM CAT;
or
SELECT * FROM TAB;
COL
Column names and NOT NULL constraints.
SELECT colno, cname, coltype, width, scale, precision, nulls FROM col
WHERE tname = 'EMPLOYEE' ORDER BY col.colno;
USER_INDEXES
Indexes defined on tables the user owns
COLUMN table_owner FORMAT A12
SELECT index_name, table_owner,
table_name FROM USER_INDEXES ;
USER_VIEWS
View names and view definitions (queries) a user owns
SELECT view_name, text FROM USER_VIEWS;
USER_SEQUENCES
Sequence definitions and current values for sequences a user owns
SELECT * FROM USER_SEQUENCES ;
USER_TRIGGERS
Trigger names and definitions for triggers a user owns
SELECT trigger_name, trigger_body FROM USER_TRIGGERS;
USER_ERRORS
Contains information about the last error that occurred in a user's
schema due to a trigger or procedure compilation error.
SELECT * FROM USER_ERRORS;
USER_CONSTRAINTS
Constraints on tables a user owns. Includes column constraints such as
NOT NULL, CHECK and foreign key constraints.
SELECT constraint_name, table_name, search_condition FROM
USER_CONSTRAINTS WHERE table_name = 'EMPLOYEE';
USER_OBJECTS
All database objects a user owns. Includes tables, views, sequences,
indexes, procedures, triggers, etc.
COLUMN object_name FORMAT A35
SELECT object_name, object_type FROM
USER_OBJECTS ;
USER_SOURCE
Source code for stored procedures owned by the user.
To see which procedures exist: SELECT DISTINCT NAME from
USER_SOURCE;
To see the actual code: SELECT TEXT FROM USER_SOURCE WHERE
NAME = 'procedure_name' ORDER BY LINE;
Note: You may have to reduce the
ARRAYSIZE variable to avoid overflowing the bufer. e.g., SET ARRAYSIZE
2
USER_TS_QUOTAS
Quotas on tablespaces accessible to a user.
SELECT * FROM USER_TS_QUOTAS ; CAT in a SELECT statement: SELECT * FROM cat; . The
following is an example: TAB view was supported in older versions of Oracle and
may not be available in future releases of Oracle. In that case, try using the
CAT view instead of TAB.
DESCRIBE command in SQL*Plus: COL and
user_constraints.
SQL> COLUMN table_owner FORMAT A12
SQL> SELECT index_name, table_owner, table_name FROM USER_INDEXES ;
INDEX_NAME TABLE_OWNER TABLE_NAME
------------------------------ ------------ ------------------------------
ACCOUNTS_PK HOLOWCZA ACCOUNTS
AT_PK HOLOWCZA ACCOUNT_TYPES
COURSES_PK HOLOWCZA COURSES
CUSTOMER_PK HOLOWCZA CUSTOMERS
PK_DEPARTMENT HOLOWCZA DEPARTMENT
PK_EMP HOLOWCZA EMPLOYEE
UNQ_RNAME HOLOWCZA LOGREPORT
SQL> SET LONG 4096
SQL> SELECT view_name, text FROM USER_VIEWS;
VIEW_NAME
-------------------
TEXT
--------------------------------------------------------------
VACCOUNTS
SELECT c.fname, c.lname, ac.account_number, at.account_typeid,
at.interest_rate, at.minimum_balance,
ac.date_opened, ac.current_balance
FROM customers c, accounts ac, account_types at
WHERE c.customerid = ac.customerid
AND ac.account_typeid = at.account_typeid
V_COURSES_TAKEN
SELECT name, major, coursenumber, coursename,
semester, year, grade
FROM students, courses
WHERE students.studentid = courses.studentid
4.3.3 Oracle Pseudo-Columns
The Oracle implementation of SQL adds
several pseudo columns to each table. These columns do not exist in a
physical table, yet they can be used in any SQL statement for a variety of
purposes.
Exercise 6: Dispaying Metadata
For this exercise, query the USER_
tables and display the following metadata:
4.3.4 Oracle SQL Functions
The Oracle implementation of SQL provides a
number of functions that can be used in SELECT statements. Functions are
typically grouped into the following:
The following is an overview and brief description of single row
functions. x is some number, s is a string of characters and
c is a single character.
The following is an overview and
brief description of multiple row (group) functions. col is the
name of a table column (or expression) of type NUMBER.
ABS (x)
Absolute Value of x
CEIL (x)
Smallest integer greater than or equal to x.
COS (x)
Cosine of x
FLOOR (x)
Largest integer less than or equal to x.
LOG (x)
Log of x
LN (x)
Natural Log of x
ROUND (x, n)
Round x to n decimal places to the right of the
decimal point.
SIN (x)
Sine of x
TAN (x)
Tangent of x
TRUNC (x, n)
Truncate x to n decimal places to the right of the
decimal point.
CHR (x)
Character for ASCII value x.
INITCAP (s)
String s with the first letter of each word capitalized.
LOWER (s)
Converts string s to all lower case letters.
LPAD (s, x)
Pads string s with x spaces to the left.
LTRIM (s)
Removes leading spaces from s.
REPLACE (s1, s2, s3)
Replace occurrences of s2 with s3 in string
s1.
RPAD (s, x)
Pads string s with x spaces to the right.
RTRIM (s)
Removes trailing spaces from s.
SUBSTR (s, x1, x2)
Return a portion of string s starting at position x1
and ending with position x2. If x2 is omitted, it's value
defaults to the end of s.
UPPER (s)
Converts string s to all upper case letters.
s1 || s2
(two vertical bars or "pipe" symbols) Concatenates s1 with
s2
ASCII (c)
Returns the ASCII value of c
INSTR (s1, s2, x)
Returns the position of s2 in s1 where the search
starts at position x.
LENGTH (s)
Length of s
TO_CHAR (date, format)
Converts a date column to a string of characters. format is a
set of Date formatting codes where: YYYY is a 4 digit
year.MM is a month number.MONTH is the
full name of the month.MON is the abbreviated
month.DDD is the day of the year.DD is
the day of the month.D is the day of the
week.DAY is the name of the day.HH is
the hour of the day (12 hour clock)HH24 is the hour of
the day (24 hour clock)MI is the
minutes.SS is the seconds.
TO_CHAR (number, format)
Converts a numeric column to a string of characters. format
is a set of number formatting codes where: 9 indicates a
digit position. Blank if position value is 0.0
indicates a digit position. Shows a 0 if the position value is
0.$ displays a leading currency indicator.
TO_DATE (s, format)
Converts a character column (string s to a date.
format is a set of Date formatting codes as above.
TO_NUMBER (s, format)
Converts a character column (string s to a Number.
format is a set of Number formatting codes as
above.
SYSDATE
Returns the current date (and time if the TO_CHAR function is used)
from the system clock. For example:
SELECT TO_CHAR(SYSDATE,
'DD-MON-YYYY HH:MM:SS') FROM DUAL;
DECODE (s, search1, result1, search2,
result2)
Compares s with search1, search2, etc. and
returns the corresponding result when there is a match.
NVL (s, expression)
If s is NULL, return expression. If s is not
null, then return s.
USER
Returns the username of the current user.
For example: SELECT
USER FROM DUAL;
In addition the COUNT group function counts instances of
values. These values can be any type (CHAR, DATE or NUMBER):
AVG (col)
Returns the average of a group of rows for col
MAX (col)
Returns the maximum of a group of rows for col
MIN (col)
Returns the minimum of a group of rows for col
STDEV (col)
Returns the standard deviation of a group of rows for col
SUM (col)
Returns the sum (total) of a group of rows for col
VARIANCE (col)
Returns the variance of a group of rows for col
COUNT (columns)
Returns the number of instances of a group of rows for
(columns)
Exercise 7: Functions
For this exercise, use the various functions to
display the following:
Congratulations Bill, your grade point average is
3.45
You'll need to use the TO_CHAR function to convert the GPA column
(which is a NUMBER data type) to a set of characters.
Hint: You'll need to
use the SUBSTR function on the COURSENUMBER column to
extract the first three letters. Then compare this to 'CIS'. 4.3.5 Examples of SQL DML Statements
In this section, several examples
of SQL DML statements are given. Variations on WHERE clause, FROM clause and
using SQL functions are all demonstrated.
Basic Select Statements
CREATE TABLE students (studentid NUMBER(5,0), name VARCHAR2(25),
major VARCHAR2(15), gpa NUMBER(6,3),
tutorid NUMBER(5,0));
INSERT INTO students VALUES (101, 'Bill', 'CIS', 3.45, 102);
INSERT INTO students VALUES (102, 'Mary', 'CIS', 3.10, NULL);
INSERT INTO students VALUES (103, 'Sue', 'Marketing', 2.95, 102);
INSERT INTO students VALUES (104, 'Tom', 'Finance', 3.5, 106);
INSERT INTO students VALUES (105, 'Alex', 'CIS', 2.75, 106);
INSERT INTO students VALUES (106, 'Sam', 'Marketing', 3.25, 103);
INSERT INTO students VALUES (107, 'Jane', 'Finance', 2.90, 102);
Example table COURSES:
Create table courses(studentid NUMBER(5,0) NOT NULL,
coursenumber VARCHAR2(15) NOT NULL,
coursename VARCHAR2(25), semester VARCHAR2(10),
year NUMBER(4,0), grade VARCHAR2(2));
INSERT INTO courses VALUES (101, 'CIS3400', 'DBMS I', 'FALL', 1997, 'B+');
INSERT INTO courses VALUES (101, 'CIS3100', 'OOP I', 'SPRING', 1999, 'A-');
INSERT INTO courses VALUES (101, 'MKT3000', 'Marketing', 'FALL', 1997, 'A');
INSERT INTO courses VALUES (102, 'CIS3400', 'DBMS I', 'SPRING', 1997, 'A-');
INSERT INTO courses VALUES (102, 'CIS3500', 'Network I', 'SUMMER', 1997, 'B');
INSERT INTO courses VALUES (102, 'CIS4500', 'Network II', 'FALL', 1997, 'B+');
INSERT INTO courses VALUES (103, 'MKT3100', 'Advertizing', 'SPRING', 1998, 'A');
INSERT INTO courses VALUES (103, 'MKT3000', 'Marketing', 'FALL', 1997, 'A');
INSERT INTO courses VALUES (103, 'MKT4100', 'Marketing II', 'SUMMER', 1998, 'A-');
StudentID
Name
Major
GPA
TutorId
101
Bill
CIS
3.45
102
102
Mary
CIS
3.1
103
Sue
Marketing
2.95
102
104
Tom
Finance
3.5
106
105
Alex
CIS
2.75
106
106
Sam
Marketing
3.25
103
107
Jane
Finance
2.9
102
SELECT AVG(gpa)
FROM students;
AVG(GPA)
----------
3.12857143
SELECT AVG(gpa)
FROM students
WHERE major = 'CIS' OR major = 'Finance';
AVG(GPA)
----------
3.14
This is an example
of a subquerySELECT name, gpa
FROM students
WHERE gpa =
( SELECT MAX(gpa) FROM students );
NAME GPA
-------- ----------
Tom 3.5
SELECT 'The student with the highest GPA is ' || name
FROM students
WHERE gpa =
( SELECT MAX(gpa) FROM students );
NAME
------------------------------------------
The student with the highest grade is Tom
SELECT name, major, TO_CHAR(gpa, '9.00') AS GPA
FROM students s1
WHERE gpa =
(
SELECT max(gpa)
FROM students s2
WHERE s1.major = s2.major
);
NAME MAJOR GPA
------------------ --------------- -----
Bill CIS 3.45
Tom Finance 3.50
Sam Marketing 3.25
SELECT name, major, gpa
FROM students s1
WHERE gpa =
(
SELECT max(gpa)
FROM students s2
WHERE s1.major = s2.major
)
ORDER BY gpa DESC;
NAME MAJOR GPA
-------- ---------- ----------
Tom Finance 3.5
Bill CIS 3.45
Sam Marketing 3.25
Selecting from 2 or More Tables
Example table EMPLOYEE:
FNAME MI LNAME SSN BDATE ADDRESS S SALARY SUPERSSN DNO
-------- -- ------- --------- --------- ------------------------- - ------ --------- ---
JOHN B SMITH 123456789 09-JAN-55 731 FONDREN, HOUSTON, TX M 30000 333445555 5
FRANKLIN T WONG 333445555 08-DEC-45 638 VOSS,HOUSTON TX M 40000 888665555 5
ALICIA J ZELAYA 999887777 19-JUL-58 3321 CASTLE, SPRING, TX F 25000 987654321 4
JENNIFER S WALLACE 987654321 20-JUN-31 291 BERRY, BELLAIRE, TX F 43000 888665555 4
RAMESH K NARAYAN 666884444 15-SEP-52 975 FIRE OAK, HUMBLE, TX M 38000 333445555 5
JOYCE A ENGLISH 453453453 31-JUL-62 5631 RICE, HOUSTON, TX F 25000 333445555 5
AHMAD V JABBAR 987987987 29-MAR-59 980 DALLAS, HOUSTON, TX M 25000 987654321 4
JAMES E BORG 888665555 10-NOV-27 450 STONE, HOUSTON, TX M 55000 1
Example table DEPARTMENT:
DNAME DNUMBER MGRSSN MGRSTARTD
--------------- --------- --------- ---------
RESEARCH 5 333445555 22-MAY-78
ADMINISTRATION 4 987654321 01-JAN-85
HEADQUARTERS 1 888665555 19-JUN-71
Example Table DEPT_LOCATIONS:
DNUMBER DLOCATION
------- ---------------
1 HOUSTON
4 STAFFORD
5 BELLAIRE
5 SUGARLAND
5 HOUSTON
Example table DEPENDENT:
ESSN DEPENDENT_NAME SEX BDATE RELATIONSHIP
--------- --------------- --- --------- ------------
333445555 ALICE F 05-APR-76 DAUGHTER
333445555 THEODORE M 25-OCT-73 SON
333445555 JOY F 03-MAY-48 SPOUSE
123456789 MICHAEL M 01-JAN-78 SON
123456789 ALICE F 31-DEC-78 DAUGHTER
123456789 ELIZABETH F 05-MAY-57 SPOUSE
987654321 ABNER M 26-FEB-32 SPOUSE
SELECT employee.fname, employee.lname
FROM employee, dept_locations
WHERE employee.dno = dept_locations.dnumber
AND dept_locations.dlocation = 'HOUSTON' ;
FNAME LNAME
-------- --------
JOHN SMITH
FRANKLIN WONG
RAMESH NARAYAN
JOYCE ENGLISH
JAMES BORG
SELECT dept_locations.dlocation, department.dname,
employee.fname, employee.lname
FROM employee, department, dept_locations
WHERE employee.dno = department.dnumber
AND department.dnumber = dept_locations.dnumber
AND employee.dno = dept_locations.dnumber
ORDER BY dept_locations.dlocation, employee.lname;
Results:
DLOCATION DNAME FNAME LNAME
--------------- --------------- -------- --------
BELLAIRE RESEARCH JOYCE ENGLISH
BELLAIRE RESEARCH RAMESH NARAYAN
BELLAIRE RESEARCH JOHN SMITH
BELLAIRE RESEARCH FRANKLIN WONG
HOUSTON HEADQUARTERS JAMES BORG
HOUSTON RESEARCH JOYCE ENGLISH
HOUSTON RESEARCH RAMESH NARAYAN
HOUSTON RESEARCH JOHN SMITH
HOUSTON RESEARCH FRANKLIN WONG
STAFFORD ADMINISTRATION AHMAD JABBAR
STAFFORD ADMINISTRATION JENNIFER WALLACE
STAFFORD ADMINISTRATION ALICIA ZELAYA
SUGARLAND RESEARCH JOYCE ENGLISH
SUGARLAND RESEARCH RAMESH NARAYAN
SUGARLAND RESEARCH JOHN SMITH
SUGARLAND RESEARCH FRANKLIN WONG
16 rows selected.
SELECT MAX(employee.salary)
FROM employee, dept_locations
WHERE employee.dno = dept_locations.dnumber
AND dept_locations.dlocation = 'HOUSTON';
MAX(EMPLOYEE.SALARY)
--------------------
55000
SELECT *
FROM department, dept_locations ;
DNAME DNUMBER MGRSSN MGRSTARTD DNUMBER DLOCATION
--------------- ------- --------- --------- ------- ----------
RESEARCH 5 333445555 22-MAY-78 1 HOUSTON
ADMINISTRATION 4 987654321 01-JAN-85 1 HOUSTON
HEADQUARTERS 1 888665555 19-JUN-71 1 HOUSTON
RESEARCH 5 333445555 22-MAY-78 4 STAFFORD
ADMINISTRATION 4 987654321 01-JAN-85 4 STAFFORD
HEADQUARTERS 1 888665555 19-JUN-71 4 STAFFORD
RESEARCH 5 333445555 22-MAY-78 5 BELLAIRE
ADMINISTRATION 4 987654321 01-JAN-85 5 BELLAIRE
HEADQUARTERS 1 888665555 19-JUN-71 5 BELLAIRE
RESEARCH 5 333445555 22-MAY-78 5 SUGARLAND
ADMINISTRATION 4 987654321 01-JAN-85 5 SUGARLAND
HEADQUARTERS 1 888665555 19-JUN-71 5 SUGARLAND
RESEARCH 5 333445555 22-MAY-78 5 HOUSTON
ADMINISTRATION 4 987654321 01-JAN-85 5 HOUSTON
HEADQUARTERS 1 888665555 19-JUN-71 5 HOUSTON
15 rows selected.
SELECT DISTINCT dlocation
FROM dept_locations;
DLOCATION
---------------
BELLAIRE
HOUSTON
STAFFORD
SUGARLAND
SELECT department.dname, SUM( employee.salary )
FROM employee, department
WHERE employee.dno = department.dnumber
GROUP BY department.dname
Results:
DNAME SUM(EMPLOYEE.SALARY)
--------------- --------------------
ADMINISTRATION 93000
HEADQUARTERS 55000
RESEARCH 133000
SELECT department.dname, SUM( employee.salary ) AS TotalSalaries
FROM employee, department
WHERE employee.dno = department.dnumber
GROUP BY department.dname
Results:
DNAME TOTALSALARIES
--------------- -------------
ADMINISTRATION 93000
HEADQUARTERS 55000
RESEARCH 133000
SELECT fname, lname,
salary AS CurrentSalary,
(salary * 1.03) AS ProposedRaise
FROM employee;
FNAME LNAME CURRENTSALARY PROPOSEDRAISE
-------- -------- ------------- -------------
JOHN SMITH 30000 30900
FRANKLIN WONG 40000 41200
ALICIA ZELAYA 25000 25750
JENNIFER WALLACE 43000 44290
RAMESH NARAYAN 38000 39140
JOYCE ENGLISH 25000 25750
AHMAD JABBAR 25000 25750
JAMES BORG 55000 56650
8 rows selected.
Recursive Queries and Table Aliases
STUDENTS (studentid, name, major, grade, student_tutorid)
SELECT s1.name AS Student, tutors.name AS Tutor
FROM students s1, students tutors
WHERE s1.tutorid = tutors.studentid;
STUDENT TUTOR
------------------------- -----------
Bill Mary
Sue Mary
Jane Mary
Sam Sue
Tom Sam
Alex Sam
(+) after the join condition to indicate an outer join:
SELECT s1.name AS Student, tutors.name AS Tutor
FROM students s1 LEFT JOIN students tutors
ON s1.tutorid = tutors.studentid;
In Oracle: SELECT s1.name AS Student, tutors.name AS Tutor
FROM students s1, students tutors
WHERE s1.tutorid = tutors.studentid (+) ;
STUDENT TUTOR
------------------------- -------------
Bill Mary
Sue Mary
Jane Mary
Sam Sue
Tom Sam
Alex Sam
Mary
SELECT s1.name AS Student, NVL(tutors.name, 'No Tutor') AS Tutor
FROM students s1, students tutors
WHERE s1.tutorid = tutors.studentid (+) ;
STUDENT TUTOR
------------------------- -------------------------
Bill Mary
Sue Mary
Jane Mary
Sam Sue
Tom Sam
Alex Sam
Mary No Tutor
SELECT s1.name AS TutorName,
COUNT(tutors.tutorid) AS NumberTutored
FROM students s1, students tutors
WHERE s1.studentid = tutors.tutorid
GROUP BY s1.name;
TUTORNAME NUMBERTUTORED
------------------------- -------------
Mary 3
Sam 2
Sue 1
Tree Queries
Another form of recursive query is the tree query. A tree
query decomposes the table such that each row is a node the tree and nodes are
related in levels. Consider the Students table defined above.
Using the SQL SELECT statements CONNECT BY and
START WITH clauses, we can form a set of relationships between the rows of the
table that form a tree structure.
WHERE Clause Expressions
SELECT name, grade
FROM students
WHERE grade =
( SELECT MAX(grade) FROM students );
This is
typically used when aggregate functions are in the subquery.
SELECT employee.fname, department.dname
FROM employee, department
WHERE employee.dno = department.dnumber
AND department.dname IN ('HEADQUARTERS', 'RESEARCH');
FNAME DNAME
-------- ---------------
JAMES HEADQUARTERS
JOHN RESEARCH
JOYCE RESEARCH
RAMESH RESEARCH
FRANKLIN RESEARCH
SELECT employee.fname
FROM employee
WHERE employee.dno IN
(SELECT dept_locations.dnumber
FROM dept_locations
WHERE dept_locations.dlocation = 'STAFFORD');
FNAME
-------
ALICIA
JENNIFER
AHMAD
SELECT department.dname, AVG(salary)
FROM employee, department
WHERE employee.dno = department.dnumber
GROUP BY department.dname
HAVING AVG(salary) > 33000 ;
DNAME AVG(SALARY)
--------------- -----------
HEADQUARTERS 55000
RESEARCH 33250
SELECT department.dname, COUNT(employee.dno)
FROM department, employee
WHERE department.dnumber = employee.dno
GROUP BY department.dname
HAVING COUNT(employee.dno) >= 3;
DNAME COUNT(EMPLOYEE.DNO)
--------------- -------------------
ADMINISTRATION 3
RESEARCH 4
Use the LIKE operator to perform a partial string match.
Generally, the % character is used as the wild card although in some DBMS, the
* character is used.
SELECT fname, lname, salary
FROM employee
WHERE lname LIKE 'S%';
Result:
FNAME LNAME SALARY
-------- -------- ----------
JOHN SMITH 30000
Show all employees whose name contains the letters 'AR' SELECT fname, lname, salary
FROM employee
WHERE lname LIKE '%AR%';
Result:
FNAME LNAME SALARY
-------- -------- ----------
RAMESH NARAYAN 38000
AHMAD JABBAR 25000
Note that chatacters within quotes are case sensitive.
SELECT fname, lname, salary
FROM employee
WHERE lname LIKE '%E%L%';
Result:
FNAME LNAME SALARY
-------- -------- ----------
JOYCE ENGLISH 25000
SELECT fname, lname, salary
FROM employee
WHERE lname LIKE '%E%L%' OR
lname LIKE '%L%E%';
Result:
FNAME LNAME SALARY
-------- -------- ----------
JENNIFER WALLACE 43000
JOYCE ENGLISH 25000
Examples of SQL Functions
SELECT 'The oldest employee was born on ' ||
TO_CHAR( MIN(bdate), 'DD/MM/YYYY')
AS Sentence
FROM employee;
SENTENCE
----------------------------------------
The oldest employee was born on 10/11/1927
SELECT 'The oldest employee was born on ' ||
TO_CHAR( MIN(bdate), 'DD/MM/YYYY') || ' and is now' ||
TO_CHAR( (SYSDATE - MIN(bdate)) / 365, '99') ||
' years old.'
AS Sentence
FROM employee;
SENTENCE
-----------------------------------------------------------------
The oldest employee was born on 10/11/1927 and is now 70 years old.
SELECT fname || ' '|| lname || ' has ' ||
DECODE(COUNT(essn),
0, 'no dependents.',
1, 'one dependent.',
2, 'two dependents.',
3, 'three dependents.')
AS Sentence
FROM employee, dependent
WHERE employee.ssn = dependent.essn (+)
GROUP BY employee.fname, lname;
SENTENCE
---------------------------------------
AHMAD JABBAR has no dependents.
ALICIA ZELAYA has no dependents.
FRANKLIN WONG has three dependents.
JAMES BORG has no dependents.
JENNIFER WALLACE has one dependent.
JOHN SMITH has three dependents.
JOYCE ENGLISH has no dependents.
RAMESH NARAYAN has no dependents.
8 rows selected.
SELECT MAX(DECODE(dnumber, 1, dname)) AS Department,
MAX(DECODE(dnumber, 4, dname)) AS Department,
MAX(DECODE(dnumber, 5, dname)) AS Department
FROM department ;
DEPARTMENT DEPARTMENT DEPARTMENT
--------------- --------------- ---------------
HEADQUARTERS ADMINISTRATION RESEARCH
Deleting Tuples with DELETE
DELETE employee;
DELETE employee
WHERE salary > 50000;
DELETE employee
WHERE employee.dno IN
(SELECT dept_locations.dnumber
FROM dept_locations
WHERE dlocation = 'HOUSTON');
For example, consider the DNO attribute in the Employee
table as a Foreign Key.
Removing a department would then be contingent upon
no employees working in that department.
This is what we call enforcing
Referential Integrity
Change Values using UPDATE
UPDATE employee
SET lname = 'SMITH'
WHERE lname = 'JONES';
UPDATE employee
SET salary = salary * 1.05
WHERE fname = 'JOYCE' AND lname = 'ENGLISH' ;
UPDATE EMPLOYEE
SET SALARY = SALARY * 1.02
WHERE TO_NUMBER( ( SYSDATE - bdate) / 365) >= 50;
Exercise 8: Update and Delete
For this exercise, write the SQL UPDATE
and DELETE statements to:
5. Advanced SQL*Plus Commands
This section introduces some of the
advanced features of SQL*Plus including editing the SQL command buffer,
formatting output from SQL SELECT statements, saving the output from SQL
statements and collecting performance statistics on the execution of SQL
statements.
5.1 Editing The SQL Buffer
SQL> SELECT tname, tabtype
2 FRO
3 tab;
FRO
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected
SQL> 2 FROM
SQL> RUN
TNAME TABTYPE
------------------------------ -------
MACHINE TABLE
EMPLOYEE TABLE
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tab
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tab
SQL> APPEND xyz
3* tabxyz
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tabxyz
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tabxyz
SQL> CHANGE/xyz//
3* tab
SQL> LIST
1 SELECT tname, tabtype
2 FROM
3* tab
VI,
Emacs or Pico to create text files with the create
statements to create the tables, insert statements to add data and select
statements to perform some queries.
a: drive), then the START command can be used as follows:
5.2 Formatting SQL*Plus Output
SQL*Plus contains several commands that
can alter the appearance of the output. These commands are only in effect for
the current SQL*Plus session. They can also be included in SQL script files and
can be executed using the START command.
COLUMN which changes the
appearance of data for a given column. The syntax for the COLUMN
command is as follows: COLUMN column_name option1 option2 ...
Where option can be one or more of the following:
FORMAT format - Changes the format for the column. For
example, to only display the first 10 characters of an employee's last name
(column LNAME), use the following: COLUMN lname FORMAT A10
This indicates to format the lname
column as an Ascii column with only 10 characters. Numbers can be formatted
using "9" to indicate digits. For example: COLUMN salary FORMAT $9,999,990.99
HEADING heading_text - changes the heading for a
column.
JUSTIFY LEFT or JUSTIFY CENTER or JUSTIFY
RIGHT - aligns the output with the left, center or right of the column.
NULL text - Indicates the text that should be
displayed in place of a NULL value.
WRAPPED or WORD_WRAPPED or
TRUNCATED - Indicates how text that is longer than the displayed
column width should be handled. TRUNCATED means it will be cut
off at the maximum width of the field. WRAPPED will wrap the
value down to the next line of output. WORD_WRAPPED does the same
as Wrapped but breaks the value up on white space. SET and SHOW commands can also be useful. To
see a listing of all of the SQL*Plus variables, type SHOW ALL BTITLE and
TTITLE described above, Other useful variables include:
OFF meaning SQL*Plus commands will not be echoed.
ON.
LONG column will be
displayed. The default is 80 bytes.
PAGESIZE lines have been displayed. The default is
OFF.
LINESIZE. The default is ON meaning the
output will be trimmed (e.g., will not be padded with spaces).
LINESIZE. The default is
ON meaning long output will be wrapped. myquery.sql) that utilizes some of the above formatting commands.
The output of this script is displayed afterwards. Exercise 9: Creating a Report Script
5.3 Saving SQL*Plus Output using the
SPOOL CommandSPOOL that can send the output
from any SQL statement to a file. Indeed, anything that is displayed in SQL*Plus
can be echoed to this spool file.
SPOOL command is invoked with the name of a file that will
contain the output. Once this has been executed, the output from all subsequent
SQL statements will be copied to the file. To end capturing the output, issue
the SPOOL OFF command. The following is an example: SPOOL OFF command turns the output off. Everything between
SPOOL a:\myfile.out and SPOOL OFF will be in the file
myfile.out. This is a simple ASCII text file that can be read by Windows
Notepad, MS Word, or just about any word processor, e-mail package, etc. To
print, load this file into MS Word, set the font to Courier and print
as you would with any other document.
SET ECHO ON option.
SET LINESIZE option. For example, SET LINESIZE 70 will
pad each line of output to 70 characters. SET TRIMOUT and SET TABS options offer other
ways to change the spooled output.
Exercise 10: Creating a Report Script and Saving the output
5.4 Prompting and Accepting user Input
SQL*Plus has several commands
that can be used to prompt the user for input, accept input from the user and
store it in a variable, and then use that variable in a query.
dept. The input should be of type
NUMBER, and the PROMPT Department Number: should be
displayed.
dept takes on the value. The following two lines (starting with old
and new) are verifying the values used for the dept variable. To
suppress the display of this verification, use the SET VERIFY OFF
command before running the script or as one of the first commands in the script.
Exercise 11: Prompting and Accepting User Input
5.5 Generating HTML output from SQL*Plus
Exercise 12: Generating HTML output
5.6 Collecting Statistics On SQL Statements
SQL*Plus has several
commands that monitor the execution of SQL statements. The commands can be used
to gather statistical information for performance monitoring purposes. The first
command is called AUTOTRACE and is used to trace the execution plan for
an SQL statement. To use AUTOTRACE, a special table must be created in
the schema to hold the statistical information. Execute the following CREATE
TABLE command in your schema: SET AUTOTRACE ON
Then execute an SQL statement: SET AUTOTRACE OFF
Exercise 13: Generating Statistics
5.7 Using the ALTER SESSION Statement for Date Formats
In the previous
examples of SQL statements, the default format of data of type DATE has been in
the form: DD-MON-YY
YY
A 2 digit year such as 98.
YYYY
A 4 digit year such as 1998.
NM
A month number.
MONTH
The full name of the month.
MON
The abbreviated month (Jan, Feb, Mar).
DDD
The day of the year. For use is Julian dates.
DD
The day of the month.
D
The day of the week.
DAY
The name of the day.
HH
The hour of the day (12 hour clock)
HH24
The hour of the day (24 hour clock)
MI
The minutes.
SS
The seconds.
6. Stored Procedures and Triggers
The Oracle RDBMS has the ability to
store procedures within the data dictionary and execute procedures in the RDBMS.
Procedures (Program Units) are written in the PL/SQL language (Procedural
Language), which is proprietary to Oracle. PL/SQL runs in both the database
engine as well as in many of Oracle's development tools such as Oracle
Developer.
Exercise 14: Basic PL/SQL Procedure
6.1 Trigger Syntax
Creating a trigger is accomplished with the CREATE
TRIGGER statement. There are numerous options for a trigger that specify when
the trigger should fire. These options include:
6.2 Trigger Example
SELECT *
FROM USER_ERRORS
or use the SHOW ERRORS SQL*Plus command.
6.3 Stored Procedure Example
CREATE TABLE inventory_locations (
locationid NUMBER(10) NOT NULL,
aisle NUMBER(10),
tier NUMBER(10),
bin NUMBER(10) );
ALTER TABLE inventory_locations
ADD CONSTRAINT il_pk PRIMARY KEY (locationid);
CREATE TABLE products (
productid VARCHAR(10) NOT NULL,
description VARCHAR(35) );
ALTER TABLE products ADD CONSTRAINT prod_pk
PRIMARY KEY (productid);
CREATE TABLE inventory (
locationid NUMBER(10) NOT NULL,
productid VARCHAR(10) NOT NULL,
quantity NUMBER(10) );
ALTER TABLE inventory ADD CONSTRAINT inventory_pk
PRIMARY KEY (locationid, productid);
Next step: Add some data to the three tables INSERT INTO inventory_locations VALUES (101, 1, 1, 1);
INSERT INTO inventory_locations VALUES (102, 1, 1, 2);
INSERT INTO inventory_locations VALUES (103, 1, 1, 3);
INSERT INTO inventory_locations VALUES (104, 1, 2, 1);
INSERT INTO inventory_locations VALUES (105, 1, 2, 2);
INSERT INTO inventory_locations VALUES (106, 1, 2, 3);
INSERT INTO inventory_locations VALUES (107, 2, 1, 1);
INSERT INTO inventory_locations VALUES (108, 2, 1, 2);
INSERT INTO products VALUES ('P500', 'HP LaserJet 6L');
INSERT INTO products VALUES ('P510', 'HP DeskJet 855');
INSERT INTO products VALUES ('P520', 'IBM Aptiva');
INSERT INTO products VALUES ('P530', 'Compaq Presario');
INSERT INTO inventory VALUES (101, 'P500', 5);
INSERT INTO inventory VALUES (102, 'P510', 10);
INSERT INTO inventory VALUES (103, 'P500', 10);
INSERT INTO inventory VALUES (104, 'P520', 1);
INSERT INTO inventory VALUES (105, 'P530', 5);
The following query shows the current state of the inventory: SELECT i.locationid, aisle, tier, bin, i.productid, description, quantity
FROM inventory i, inventory_locations il, products p
WHERE i.locationid = il.locationid
AND i.productid = p.productid;
CREATE VIEW vw_inventory AS
SELECT i.locationid, aisle, tier, bin, i.productid, description, quantity
FROM inventory i, inventory_locations il, products p
WHERE i.locationid = il.locationid AND i.productid = p.productid;
To see the current state of the inventory, simply query the view: SELECT * FROM vw_inventory;
SET SERVEROUTPUT ON
SET ARRAYSIZE 2
The
following Oracle PL/SQL implements the add_to_inventory procedure: CREATE OR REPLACE PROCEDURE add_to_inventory (
new_locationid IN NUMBER,
new_productid IN VARCHAR,
new_quantity IN NUMBER) AS
-- Declare a new variable called 'current_quantity' that is of the same
-- type as the column quantity in the inventory table
current_quantity inventory.quantity%TYPE;
BEGIN
-- Initialize current_quantity to 0
current_quantity := 0;
-- See if some quantity exists at the current location
-- If not, then raise EXCEPTION and insert a new record
-- If so, then continue on to the UPDATE statement
SELECT inventory.quantity
INTO current_quantity
FROM inventory
WHERE inventory.locationid = new_locationid
AND inventory.productid = new_productid;
-- If we get this far, then there must already exist
-- an inventory record with this locationid and productid
-- So update the inventory by adding the new quantity.
IF (current_quantity > 0) THEN
UPDATE inventory
SET quantity = quantity + new_quantity
WHERE inventory.locationid = new_locationid
AND inventory.productid = new_productid;
END IF;
-- If the first SELECT statement above fails to return any
-- records at all, then the NO_DATA_FOUND exception will be
-- signalled. The following code reacts to this exception.
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
-- Since an inventory record matching the locationid and
-- productid can not be found, we must INSERT a new
-- inventory record.
INSERT INTO inventory
(locationid, productid, quantity)
VALUES (new_locationid, new_productid, new_quantity);
END;
END;
EXECUTE add_to_inventory(106, 'P500', 10)
EXECUTE remove_from_inventory(106, 'P500', 10)
Oracle SQL*Plus Worksheet
Starting with Oracle version 8i, Oracle has
included a version of SQL*Plus with a more graphical user interface that is
integrated into the Oracle Enterprise Manager (OEM) suite of tools. The Oracle
SQL*Plus Worksheet has all of the same capabilities as the command-line version
used for demonstration purposes in this tutorial. Below are some example screen
shots taken of the SQL*Plus Worksheet in action.
In the following example, the cat view is
queried to see what tables this user has:
