Introduction to common Oracle SQL * Plus commands

It is estimated that SQL * Plus is used almost at the beginning in Oracle,

After all, this tool can run on any platform running Oracle,

You can install this tool on the client or on the server,

To install the client, install the client software provided by Oracle,

On the server side, it is automatically installed when Oracle is installed,

SQL * Plus is a C/S-based SQL development tool, including the customer layer and service layer,

It can execute SQL statements or execute files containing SQL statements,

At the same time, it can also execute PL/SQL statements, so it is very convenient,

In this blog, I will introduce some common SQL * Plus commands,

Note that it is a common command, not a common command that is not very common !!!

Some of these commands are also very practical !!!

1. Connect to Oracle commands

You can run commands such as SqlPlus or SqlPlus/nolog in the DOS window,

However, to run the preceding command in DOS, you must set the environment variable,

In essence, the path of the command you run is

D: \ app \ XiaoZhen\ Product \ 11.1.0 \ db_1 \ BINUnderSqlPlus.exe

(This path may vary depending on the installation path of the random device)

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb.png "width = 555 height = 131 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb.png ">

2. log on to the Oracle command

Use the connect user name/password, or directly run connect (the connect command can be abbreviated to conn ),

If you directly use the connect command, you will be prompted to enter the user name and password in subsequent operations,

Directly run the connect command. A major advantage is that the password you entered will be automatically hidden and invisible to users,

For example, in the following example, the password line is hidden.

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_1.png "width = 221 height = 124 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_1.png ">

3. "/" command

One thing that needs to be mentioned here is that when running SQL * Plus, Oracle will keep the last execution command in the buffer zone,

You can use the symbol "/" to quickly access the stored commands in this buffer zone,

You can use the "/" command to run the last command again.

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_2.png "width = 349 height = 189 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_2.png ">

4. List Command for SQL * Plus line editing

Here, we still need to mention that SQL * Plus provides a row editing function,

This function is mainly used to quickly modify SQL statements in the SQL buffer and execute them again.

Note that only the last SQL statement you executed is saved in the buffer zone,

Row editing is for this statement, because this SQL statement may have n rows,

List command, there are three forms in total, and List -- List all the rows in the buffer,

List n -- List the nth row in the buffer, List * -- List the current row,

Then, you need to note which row is the current row. What about the current row? After using List to List,

There will be a * sign before it, and the current line will change with your choice.

In the following example, there are a total of three SQL statements. You can note the * Changes

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_3.png "width = 239 height = 407 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_3.png ">

5. Del command for SQL * Plus line editing

This Del command is used to delete the specified row of the SQL statement stored in the buffer zone.

There are three forms,

Del -- delete all rows, Del n -- delete specified rows, Del * -- delete current row

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_4.png "width = 254 height = 512 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_4.png ">

6. Input command for SQL * Plus line editing

Previously, we introduced how to edit and delete rows. Here we will introduce how to add new rows to the rows in the buffer zone,

This is implemented using the Input command. For details, refer to the demo process below.

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_5.png "width = 208 height = 448 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_5.png ">

7. Change command for SQL * Plus line editing

This Change command is used to modify a line in the SQL Buffer.

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_7.png "width = 270 height = 443 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_7.png ">

8. Clear SQL Buffer command

Clear Buffer: delete all rows in the SQL Buffer, that is, Clear the Buffer.

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_8.png "width = 411 height = 265 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_8.png ">

9. Define SQL editor commands

Define_editor

Ladies and gentlemen, it is estimated that it is difficult to enter an SQL statement in the black box of SQL * Plus,

However, the following describes how to customize an editor to edit SQL statements,

The following example defines your editor first. I define a Notepad program (you can also define it as another editor ),

Then I leave an SQL statement in the buffer zone (that is, executing an SQL statement ),

Call ed (the abbreviation of edit) to call up a notepad window,

The content in the SQL Buffer is automatically written to the notepad editor,

You can edit your SQL statement in Notepad. The content is automatically saved to the buffer zone,

Then run "/" in SQL * Plus to call the content in the SQL Buffer (that is, the content defined in Notepad ),

For more information, see the following demo.

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_9.png "width = 470 height = 193 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_9.png ">

Then I changed the SQL statement to select max (empno) from emp;

Save the content in notepad and call "/" again.

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_10.png "width = 357 height = 163 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_10.png ">

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_11.png "width = 310 height = 221 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_11.png ">

10. Save command

The Save command saves the content in the SQL Buffer to the file in the specified path (by default, the suffix of. SQL ).

If the file already exists, writing will fail. Of course, if you want to overwrite the file,

You can add the Replace command, for example, save d: \ test replace.

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_12.png "width = 311 height = 232 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_12.png ">

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_13.png "width = 331 height = 298 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_13.png ">

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_14.png "width = 325 height = 132 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_14.png ">

11. Use the annotation command

In SQL * Plus, you can use three methods for annotation,

First, the Remark command is used to implement single-line comments, and is used in the command file (that is, in. SQL)

The second is to implement multiline comments,

Third, use -- to implement single-line Annotation

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_17.png "width = 391 height = 273 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_17.png ">

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_18.png "width = 289 height = 282 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_18.png ">

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_16.png "width = 252 height = 357 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_16.png ">

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_15.png "width = 232 height = 421 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_15.png ">

12. Run Command

The run (r) command is used to list the SQL statements in the current buffer zone,

Execute this statement at the same time and compare it with "/". The "/" command only executes the statements in the buffer zone,

The SQL statements in the buffer zone are not displayed.

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_19.png "width = 334 height = 192 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_19.png ">

13. @ command

This command is used to run the SQL statements (including PL/SQL) defined in the file with the specified path.

This command can be used either at the Session Layer or at the command layer. What is the Session Layer,

That is, when a user logs on to Oracle, a session is automatically established, which is the Session Layer,

What is the command Layer? Naturally, it is connected to Oracle by using the SqlPlus command without user logon,

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_21.png "width = 257 height = 388 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_21.png ">

14. Start command

This command is similar to the "@" command, but it is not as powerful as the "@" command,

It can only run on the Session Layer, but cannot work on the command layer.

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_20.png "width = 252 height = 406 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_20.png ">

15. Get command

The Get command reads the content in the. SQL file in the specified path, but does not execute the content.

OfSQL * PlusIntroduction to Common commands [convert] "border = 0 alt = image src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_22.png "width = 385 height = 58 XXXXX =" border-right-width: 0px; display: inline; border-top-width: 0px; border-bottom-width: 0px; border-left-width: 0px "real_src =" http://images.cnblogs.com/cnblogs_com/QinBaoBei/WindowsLiveWriter/OracleSqlPlus_11C6F/image_thumb_22.png ">

In SQL * Plus, there are actually many commands, such as formatting columns,

Set the number of records displayed on each page, and set the width of each row,

Set the width of the specified name column,

I just want to write some examples, because these are too common and need to be used almost every time,

I guess everyone is familiar with it, so I will not introduce it.

Col ename for a50

Set pagesize 100

Set linesize 200