Editing SQL Statements


As you may have noticed, it becomes tedious to have to repeatedly type similar SQL statements into SQL*Plus. You will be pleased to know SQL*Plus stores your previous SQL statement in a buffer. You can then edit the lines that make up your SQL statement stored in the buffer. Some of the editing commands are listed in the following table. Notice the optional part of each command indicated using square brackets; for example, you can abbreviate the APPEND command to A .

Command

Description

A[PPEND] text

Appends text to the current line.

C[HANGE] / old / new

Changes the text specified by old to new in the current line.

CL[EAR] BUFF[ER]

Clears all lines from the buffer.

DEL

Deletes the current line.

DEL x

Deletes the line specified by the line number x (line numbers start with 1).

L[IST]

Lists all the lines in the buffer.

L[IST] x

Lists line number x .

R[UN]or/

Runs the statement stored in the buffer. You can also use / to run the statement.

x

Makes the line specified by the line number x the current line.

Let s take a look at some examples of using the SQL*Plus editing commands. First, enter the following SELECT statement into SQL*Plus:

 SQL>  SELECT customer_id, first_name, last_name  2  FROM customers  3  WHERE customer_id = 1;  

SQL*Plus automatically increments and displays the line number when your SQL statement spans more than one line. Make line 1 the current line by entering 1 at the prompt:

 SQL>  1  1* SELECT customer_id, first_name, last_name 

Notice that SQL*Plus displays the current line. Add the dob column to the list of columns to retrieve using the APPEND command:

 SQL>  APPEND , dob  1* SELECT customer_id, first_name, last_name, dob 

Next, list all the lines in the buffer using the LIST command:

 SQL>  LIST  1 SELECT customer_id, first_name, last_name, dob  2 FROM customers  3* WHERE customer_id = 1 

Notice that the current line has been changed to the last line, as indicated by the asterisk character (*). Change the final line to select the customer where the customer_id column is 2 using the CHANGE command. Notice that the line that has been changed is displayed after the command is run:

 SQL>  CHANGE /customer_id = 1/customer_id = 2  3* WHERE customer_id = 2 

Finally, execute the query using the RUN command. Notice that the text of the query is repeated before the returned row:

 SQL>  RUN  1 SELECT customer_id, first_name, last_name, dob  2 FROM customers  3* WHERE customer_id = 2 CUSTOMER_ID FIRST_NAME LAST_NAME DOB ----------- ---------- ---------- ---------  2 Cynthia Green 05-FEB-68 

You can also use a forward slash character (/) to run the SQL statement stored in the buffer. For example:

 SQL>  /  CUSTOMER_ID FIRST_NAME LAST_NAME DOB ----------- ---------- ---------- ---------  2 Cynthia Green 05-FEB-68 



Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net