Page 37
SQL*PLUS is Oracle's core product; it gives developers direct access to the Oracle7 database. Developers can perform an array of functions with this product. This includes retrieving data, modifying the database, and managing the database objects. The commands and functions available for use with this product are based on the SQL language. These commands and functions are used extensively in the Developer 2000 products, especially Oracle Forms and Oracle Report Writer.
The designer/developer uses this product to retrieve data and modify the database. Generally, the database objects are managed by a database administrator. In order to perform the database functions, you must learn how to execute commands in SQL*PLUS. You must also become familiar with the SQL language. The easiest way to learn SQL and SQL*PLUS is to begin with the SELECT command. In this chapter, you learn how to retrieve data in the SQL*PLUS environment. Subsequent chapters will increase the complexity of the SQL. You learn to retrieve records from multiple tables through the use of a join. The
Page 38
final two chapters of this part cover data definition language (DDL) and data modification language (DML) commands. DDL consists of commands used to maintain the database objects. DML commands are used to add, update, and delete database records.
The examples and exercises in the remainder of the book were executed against an Oracle7 database. This database is contained in Appendix D, which contains instructions for two different installation methods. The database may be installed in any Oracle7 product. It can be loaded on a server at your place of employment or on your PC by using a copy of Personnel Oracle7. If you have access to Oracle7 on a server at school or work, contact the DBA to gain a user id. If you do not have access to Oracle7, Oracle has been willing to supply a 60-day trial copy of the Oracle7 database and Developer 2000 product. Oracle may be contacted on its web site at www.oracle.com.
Logging on to SQL*PLUS is as simple as double-clicking the SQL*PLUS icon. As Figure 3.1 shows, the SQL*PLUS icon is located in the Oracle Icons group on your PC.
FIG. 3.1
The Oracle Icons Group,
which contains the
SQL*PLUS icon.
This causes a password entry dialog box to appear (see Figure 3.2). Enter your user id in the top box, your password in the middle box, and your connect string in the bottom box. The host or connect string tells Oracle on which server your id is located.
FIG. 3.2
The SQL*PLUS Log On dialog box.
NOTE |
The user id and connect string depends upon where the database is installed. If you are using the trial Personnel Oracle7 database, the default user id/password is "scott/tiger." The DBA id on the Personnel Oracle7 database is "system/manager." The connect string is needed only if the database is located on a remote server. When this has occurred, a DBA probably created the user id and established a database. The DBA will know the parameters to log on to the database. |
Page 39
After successfully entering the security requirements, the SQL*PLUS environment appears. You know when you are in this product when the sql> appears (see Figure 3.3).
FIG. 3.3
The SQL*PLUS Prompt
and Editor.
Now that you have logged on to SQL*PLUS, you need to learn how to enter and run commands. Two editors are available in SQL*PLUS. The first is the buffer editor. This editor enables you to enter and change commands interactively from the sql> prompt. The second is any external editing product such as Windows Notepad. The two editing environments work together. A script is a block of SQL statements. Scripts developed and saved in the external editor may be loaded into the buffer and edited through the buffer editor. Scripts contained in the buffer editor may be loaded into an external editor for further work.
The first script you can enter is select * from employees. After pressing Enter, a 2 appears. This indicates that the first line was placed into the buffer, and SQL*PLUS is ready for you to enter the remainder of the script. Additional line numbers continue to appear until the script is completed. SQL command statements are completed by placing a semicolon (;) at the end. Upon seeing the semicolon, Oracle executes the statement. Listing 3.1 demonstrates these series of steps.
Listing 3.1L_03_01.TXTEntering a Query into the SQL*PLUS Editor
SQL> select * from employee 2 ; PAYROLL_NUMBER LAST_NAME FIRST_NAME ABSENCES WAGES STREET -------------- --------------- -------------- --------- --------- ------------- CITY ST PHONE SOCIAL_SECU EMPLOYMEN BIRTH_DAT CURRENT_POSITIO --------------- -- ------------- ----------- --------- --------- --------------- ÂFK_D GE ---- -- 25 COOLIDGE CALVIN 0 9500 12 MAPLE ROAD PLYMOUTH VT 435-897-3546 100-02-0500 07-AUG-21 01-JUL.-72 JANITOR NIT . . . 19 rows selected.
Page 40
You have been entering your statement into the SQL*PLUS buffer. This statement is deleted when a new statement is entered or loaded into the buffer. You can view a buffer statement by entering the List Editor command. This consists of the letter `L' after the SQL> prompt. This causes the editor to list the current script in the buffer. Oracle has a number of buffer commands. Table 3.1 contains these commands.
Table 3.1SQL*PLUS Editor Commands
Command | Description |
Ln |
Displays or (lists) linenumber n of the current SQL statement in the buffer. If n is omitted, L will list the entire buffer. |
linenumber `text' |
Replaces existing line n with text. The text must be preceded by a line number, but does not have to be enclosed by any symbols. |
C/old/new |
Changes old to new on the current line. |
C/old.../new/ |
Changes from first occurrence of old through end of line to new. |
A text |
Appends text to the end of the current line. |
DEL(ete) |
Delete the current line. Entering "delete" will erase the entire buffer. Entering "del" will erase the current line. |
I | Insert after the current line. |
/ or ®UN | Execute contents of the buffer. |
SAVE filename |
Save contents of the buffer in filename.sql. |
GET filename |
Load contents of filename.sql into the buffer. |
START filename or @filename | Load filename.sql and execute. |
Listing 3.2 shows several of these commands in action. The first command lists the existing script. The next command changes the tablename from employee to department. The third command lists the buffer script again to show the changes.