sqlplus

SQLplus

It is useful to examine the SQLplus interface to Oracle because the HS sql method follows this basic syntax. Note that the HS sql method provides capabilities beyond SQLplus.

Connecting to Oracle

To get an Oracle SQLplus command-line interface for issuing SQL commands (on VMS), connect to SQLplus as follows:

$ sqlplus username/password
SQL*Plus: Release 3.3.2.0.0 - 
Production on Tue Aug  5 17:09:36 1997
 
Copyright (c) Oracle Corporation 1979, 1994.
All rights reserved.
 
Connected to:
Oracle7 Server Release 7.3.2.1.0 - Production Release
With the distributed and parallel query options
PL/SQL Release 2.3.2.0.0 - Production
 
SQL>
``

_

Using SQLplus Commands

At the "SQL>" prompt you can issue Oracle SQL commands. Some of the more general SQL commands will be illustrated here, but for more detailed command descriptions, refer to manual on SQLplus.

To create a table "TEST", with various record data types, a typical SQL command could be:

SQL> create table test (string char(20), intval number(15,2),
      floatval number(7,2), dateval date);
``

_

To view the table description, use the DESCRIBE command:

SQL> describe test
Name                            Null?    Type 
------------------------------- -------- ---- 
STRING                                   CHAR(20) 
INTVAL                                   NUMBER(15)
FLOATVAL                                 NUMBER(7,2)
DATEVAL                                  DATE
``

_

To insert values into the table, use the INSERT command:

SQL> insert into test values ('funny', 10, 5.2, '11-SEP-97' ) ;
SQL> insert into test values ('happy', 20, 10.8, '23-MAY-69' ) ;
``

_

To update values in the table, use the UPDATE command:

SQL> update test set STRING = 'silly' where STRING = 'funny' ;
``

_

The above examples for CREATE, INSERT, and UPDATE are relatively simple. To learn about more sophisticated commands, you should refer to a SQL command reference book.

The COMMIT command is necessary if you want the table changes to be saved in the database.

SQL> commit ;

Commit complete.
``

_

Lastly, the SELECT statement is used to retrieve value from the table. Continuing with the above examples, the SELECT statement in its most general form is:

SQL> select * from test ;
STRING               INTVAL     FLOATVAL   DATEVAL 
-------------------- ---------- ---------- --------- 
silly                10         5.2        11-SEP-97 
happy                20         10.8       23-MAY-69
``

_

SELECT statements can be much more sophisticated, and often include a WHERE clause to filter the data.

SQL> select STRING, FLOATVAL from TEST where INTVAL  20 ;

STRING               FLOATVAL
-------------------- ----------
happy                10.8
``

_