Microsoft Word



Page 1 of 4

USING ORACLE SQL PLUS

B1. Starting SQL Command

Run “Run SQL Command”, you can find it by clicking “Start(All programs(Oracle Database 11g Express Edition(Run SQL command”. Then the SQL command window appears. Right now you should type the following:

connect sys/ as sysdba and press the enter button.

And then you are asking to enter the password, the password is the one you input when you install the software. Enter the password and the database has been connected.

[pic]

B2. Using CREATE to create a table

Tables may be created from the command prompt using SQL statements. Use the following statement to create a table named DEPT

with the command

CREATE TABLE DEPT (DEPTNO NUMBER(2) PRIMARY KEY, DEPTNAME CHAR(14));

Create a table named EMP with CREATE TABLE EMP (SSN CHAR(11) PRIMARY KEY, DEPTNO NUMBER(2), NAME CHAR(12)); The DEPT table will have two columns, DEPTNO with a datatype ‘number’ and DEPTNAME with a datatype ‘char’. Column DEPTNO is a primary key. EMP will have 3 columns with SSN being the primary key. (Be sure to terminate your statements with a semicolon. If you fail to terminate your command with a semicolon and hit return, Oracle will advance the cursor to a new line and expect you to continue typing the SQL statement. If you do this by mistake, just place a semicolon on the new line and hit enter again.)

\\babbage\Inetpub\CSE\cslab\Reference\oracle\OracleSQLPlus.doc

02/05/07

Page 2 of 4

You can also create tables by duplicating an existing table. To illustrate, use the command CREATE TABLE DEPT2 AS SELECT

* FROM DEPT; to create table DEPT2 by duplicating the table you just created.

B3. Using the DESCRIBE command

You can use DESCRIBE to list all the columns and column data types of a table. Use the following statements to list this information for the tables you created.

SQL> DESCRIBE DEPT; SQL> DESCRIBE DEPT2;

SQL> DESCRIBE EMP;

B4. Using DROP to delete tables

Enter DROP TABLE DEPT2; to delete the DEPT2 table.

B5. Using INSERT INTO to write data values into a table

Use INSERT INTO to write data values into your tables. Create a record in the DEPT table for the personnel department using the command INSERT INTO DEPT (DEPTNO, DEPTNAME VALUES (21,'SALES'));. If you are populating all columns and enter

your data in the order that you created columns in the table, you can omit the column names. Create some additional records with the

following statements

INSERT INTO DEPT VALUES (23, ‘PERSONNEL’); INSERT INTO DEPT VALUES (32, ‘SECURITY’); INSERT INTO DEPT VALUES (45, ‘PLANT’);

B6a. Using the SQLPlus editor

The SQLPlus editor can be invoked from the SQL command prompt with the ‘edit’ command. If you are entering several lines of data it is sometimes easier to enter the information from a script, than from the SQL command prompt. To illustrate this we will use the editor to write a script to make several entries into the EMP table. Enter EDIT temp.sql from the SQLPlus command prompt to open

a text file named temp.sql. After you have done so, a windows will open with a message indicating that the temp.sql file could not be found and asking if you wish to create the file. Answer yes, then enter the following script into the editor:

INSERT INTO EMP VALUES (‘111-11-1111’, 21, ‘FRED’)

/

INSERT INTO EMP VALUES (‘222-22-2222’, 21, ‘MARY’)

/

INSERT INTO EMP VALUES (‘555-55-5555’, 23, ‘ELSA’)

/

INSERT INTO EMP VALUES (‘666-66-6666’, 32, ‘MORRIS’)

/

INSERT INTO EMP VALUES (‘777-77-7777’, 32, ‘APRIL’)

/

You have now entered 5 separate SQL statements into the editor. Notice that each statement is terminated with ‘/’ rather than ‘;’ that you used when entering individual statements from the command line. Be sure that your statements are formed correctly, then use File-Save to save the file. Now, exit the notepad editor and return to the SQLPlus prompt.

To run the script that you created, enter START temp.sql; from the command prompt. The script should run and execute the 5 insert statements that you have written. If you receive error messages, you may open the editor and make corrections before running the script again. If your script contains an extraneous space, you may get an error message to the effect that something was ‘truncated’. The ‘truncated’ message can be ignored. In the next part of this exercise, you learn how to verify what has been written to the tables you created.

B7. Using Select

Oracle maintains a record of the tables you create in a table named Tab. To list the tables you have created in the database use the command SELECT * FROM TAB; . To retrieve all records in the DEPT table, enter SELECT * FROM DEPT;. The * is an SQL

wild card character which tells Oracle to retrieve everything. To retrieve the Personnel Department Employee records from EMP

enter SELECT * FROM EMP WHERE DEPTNO = 23;

If you only want the names of employees in the Personnel Department you can use WHERE to limit the number of records returned with SELECT NAME FROM EMP WHERE DEPTNO = 23;.

If you want to find the DEPTNO for the personnel department use SELECT DEPTNO FROM DEPT WHERE DEPTNAME =

‘PERSONNEL’; . Notice that in this example the literal ‘PERSONNEL’ capitalized and enclosed in single quotes. Char and varchar

\\babbage\Inetpub\CSE\cslab\Reference\oracle\OracleSQLPlus.doc

02/05/07

Page 3 of 4

datatype values are literals so they are case sensitive and must be enclosed in single quotes in SQL statements. The number datatype however, should not be quoted as indicated in the previous example. If your select statement does not return what you expected, use SELECT * FROM DEPT; to see if you capitalized the personnel entry when you entered it.

B8. Using Delete

Verify that you have a record in the EMP table for ELSA with the command SELECT * FROM EMP WHERE NAME =‘ELSA’;.

Now delete the record with DELETE FROM EMP WHERE DEPTNO = 23; . Verify that the record has been removed with SELECT * FROM EMP;

B9. Create a view

A view is a SQL query that you wish to name and save. Create a view named DEPT_NOS with the command CREATE VIEW

DEPT_NOS AS SELECT DEPTNO FROM DEPT;. This will create a view, called DEPT_NOS, with one column from DEPT, called DEPTNO. Display the content of view DEPT_NOS by entering SELECT * FROM DEPT_NOS; . Views are not tables and do not exist permanently in the database. Rather they are created from other tables whenever they are called.

B10. Using ALTER

You can modify tables you create with ALTER. Add a new column named HIREDATE to EMP with ALTER TABLE EMP ADD

(HIREDATE DATE);. The format of the DATE data type is DD-MON-YY. You can now insert a value into the HIREDATE column with UPDATE EMP SET HIREDATE = ‘03-SEP-96’; . Be sure to put single quotes around DD-MON-YY because it is a string value. You can NOT use ALTER to remove an existing column. You must create another table without HIREDATE from this table using a SELECT statement within a CREATE statement. Try to plan ahead and design your tables so they do not have to be altered.

B11. Printing Output

There are several ways to print output depending on the system you are using. On a Windows computer, if all of your output is contained on one screen, you can copy it to the windows clipboard and from there into a text processing utility like Windows notepad.

You can then copy current screen content to the clipboard by pressing the ‘print screen’ key on your keyboard. Then open a word

processor (MS Word, Wordpad, etc) and used ‘edit – paste’ to paste the clipboard content into a document that you can print.

Another is to capture screen output in the SQLPlus window, and then save it to a text file. The technique is similar to what you may have previously used in Windows HyperTerminal or other telnet facilities where you ‘turn on’ an option to write all screen output to a text file. The text file can then be opened in a word processor or text editor (MS Word, Notepad, Wordpad, etc) and printed in

the normal manner. To illustrate this, ‘turn on’ the spool option with the command SPOOL fileName;, where ‘filename’ is the full path of the file you would like to create for saving the output in the SQLPlus window. For example, if you want to create

‘OracleSpool.txt’ in the H drive, the command is going to be SPOOL H:\OracleSpool.txt;. If you don’t specify the directory and put only the file name instead of a full path, the file is to be created your home directory. To continue the example, after initiating the

Spool facility, enter SELECT * FROM EMP;. Then use the command SPOOL OFF; to turn off the spool,. The file OracleSpool.txt should now be in your home folder on the H drive. Open windows notepad (Program- Accessories-Notepad from the Windows Start

menu) and locate the file you created. You can now read and print the output you sent to the spool file in the normal manner.

B12. Commits and Rollbacks

Changes you make to an Oracle Database are initially stored in a pending status. This feature is used in processing distributed

database transactions and is useful for undoing mistakes. Distributed database transactions are covered in advanced database classes. For now, the important thing to understand is how to COMMIT or ROLLBACK the changes you make to the database.

You can think of a transaction as a series of SQL commands enclosed by a pair of commit and/or rollback commands. All of the changes you have made in this exercise thus far are in pending status because we have not issued any COMMIT or ROLLBACK commands. If you were to open another SQLPlus session now, you would not find any tables in your database, because uncommitted

changes are only visible to the session in which they were created. Before proceeding any further, make the changes that you have

made in this exercise permanent by issuing the following command:

COMMIT;

The database should respond with a ‘ commit complete’ message.

To demonstrate how ROLLBACK works, add a new department to the dept table with INSERT INTO DEPT VALUES (27,

‘RESEARCH’);. After doing so enter SELECT * FROM DEPT; to display the results. At this point you can elect to COMMIT the change you just made or to roll it back. Since we issued a COMMIT just prior to inserting the RESEARCH department record, issuing a ROLLBACK should erase only the RESEARCH record. To demonstrate, issue the command ROLLBACK;, Then view the

result by issuing another SELECT * FROM DEPT;. You should find that the RESEARCH entry has been deleted.

If you exit Oracle SQLPlus using the EXIT command, pending changes are automatically committed. Exiting by closing the SQL window without issuing an EXIT command, however, will cause your changes to be rolled back. Network failures can also cause unwanted ROLLBACKs to occur. Thus, it is wise to issue COMMIT’s periodically during long SQLPlus sessions. Also, to be on the safe side, always COMMIT before closing an SQLPlus session.

*B13. Exiting SQLPlus – To Exit Oracle SQLPlus, type EXIT; at the Oracle prompt and press return.

\\babbage\Inetpub\CSE\cslab\Reference\oracle\OracleSQLPlus.doc

02/05/07

Page 4 of 4

GO BACK

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download