Oracle Learning Chapter III Common Sql*plus commands--01

Let's start a new chapter to learn!

When an SQL statement is entered, the statement is present in the SQL buffer (that is, a memory area). This SQL buffer is small and can only have one SQL statement, and the SQL statement that was originally in the buffer is overwritten when the next SQL statement is entered. Sql*plus is a tool (environment). As we have seen, we can use it to enter SQL statements. For efficient input and editing of SQL statements, Sql*plus also provides some common commands. Unlike SQL statements, Sql*plus commands can be abbreviated. Here is a brief introduction to some common sql*plus commands.

3.1 Desc[ribe] Command

In general we always want to know the structure of the table before we operate. We can use the Desc[ribe] command to do this. You can use example 3-1 to show the structure of an EMP table.

Example 3-1

sql> DESC EMP

Example 3-1 results

The results from example 3-1 Show restraint: The structure of a table is the number of columns contained in the table, the data type of each column and its maximum length, and whether the column can be empty (null) (also called constraint, which will be further explained in later studies).

The results shown in example 3-1 tell us that the EMP table contains 8 columns, where only the empno column cannot be empty, and the column data types are as follows:

■ Empno listed as integers, with a maximum length of 4 bits

The ename column is a long character type with a maximum length of 10 characters.

The JOB column is also a long character type with a maximum length of 9 characters.

The MGR column is an integer with a maximum length of 4 bits.

HireDate is listed as a date type (we'll cover this data type in detail in a later section).

Sal is a floating-point number (that is, it contains decimals), with a maximum length of 7 bits, of which two bits are decimals.

The Comm column is also a floating-point number, with a maximum length of 7 bits, and two bits for the booing.

■ The Deptno column is an integer with a maximum length of two bits.

You can also use example 3-2 to display the structure of a dept table.

Example 3-2

Sql> DESC Dept

Example 3-2 results

The knot shown in example 3-2 tells us that the Dept table contains 3 columns, where only the Deptno column cannot be empty. The data types for each column are as follows:

■ The Deptno is an integer with a maximum length of two bits.

■ The dname column is a variable-length character type with a maximum length of 14 characters.

■ The LOC column is also a variable-length character type with a maximum length of 13 characters.

As can be seen from the above example 3-1 and example 3-2, the end of the Sql*plus command may not use a semicolon (;).

The Desc[ribe] command is a frequently used Sql*plus command. Typically experienced developers (programmers) use the Desc[ribe] command to review the structure of the table the SQL statement is working on before using the SQL statement Development program, because once the developer knows the structure of the table, the probability of a program error can be significantly reduced.

3.2 SET line[size]{ the|N} command

Another useful sql*plus command is set line[size]{|n}, where n is the natural number and 80 is the default value. The command is to set the display output of the display to a width of n characters and 80 characters to the default width for this command.

If you want to use example 3-3 's SQL statement to display all the columns in the EMP table, you will find that the displayed results are difficult to read.

Example 3-3

Sql> SELECT *  2 from  EMP;

Example 3-3 results

If your screen is large enough, then you can use the Sql*plus command set line 100, as in example 3-4.

Example 3-4

Sql> SET Line 100

At this point, you will find that the display output is much better because each row of data is displayed on the same line, instead of the same row as in example 3-3, and the result of the same line is displayed on two different lines.

3.3 L (LIST) command and N Text command

In order to contact Sql*plus's command, we enter an SQL statement for example 3-5.

Example 3-5

Sql> SELECT empno,ename,job,sal  2 from  dept  3  WHERE sal >=1500  4  ORDER by Job,sal DESC;

Example 3-5 results

The result shown in example 3-5 tells us that this statement is obviously wrong, because all of the columns we want to display are in the EMP table instead of in the Dept table.

Chairman Mao said: "Mistakes are always inevitable, as long as the improvement is a good comrade." "Oracle's design philosophy and Chairman Mao's teachings are same strain." Perhaps the great minds think alike, perhaps inherited the greatness of Mao Zedong Thought, Oracle's Sql*plus provides several commands to help us find errors and correct errors. One of the most commonly used commands is the L (LIST) command, which is used to display the contents of the SQL buffer. For example, you can use the L (LIST) command to display the SQL statement you just entered, such as example 3-6.

Example 3-6

Sql> L

Example 3-6 results

You can then use the N Text command to modify the error section, where n is the line number of the SQL statement in the SQL buffer, and text is the SQL statement that replaces the part of the error. Since the L (LIST) command is shown to indicate that the second line is in addition to the error, now enter the command for example 3-7 to modify the mistake.

Example 3-7

Sql> 2 from EMP

You should then use Example 3-8, L (LIST), to display the contents of the SQL buffer to check if your modifications are correct.

Example 3-8

Sql> L

Example 3-8 results

Example 3-8 results table name The changes you made are accurate. So how do we run this statement?

3.4 '/' (RUN) command

You certainly do not need to reenter this statement because this statement is already in the SQL buffer. Oracle provides the Slq*plus command '/' (run) to rerun the SQL statement in the SQL buffer. You can then enter the Sql*plus command for example 3-9 to rerun the SQL statement you just modified.

Example 3-9

Sql>/

Example 3-9 results

Several of the above Sql*plus commands are undoubtedly convenient for us to modify the error. But this type of sql*plus command provided by Oracle is far more than that.

---------------------Split Line---------------------

Today's study is here, today the main learning, SQL buffer only one SQL statement. Four commands. The DESC command displays a table structure, a statement that displays a SQL buffer, n text modifies the corresponding statement,/runs a SQL buffer statement.

Weekend, I hope you have a nice weekend.

Oracle Learning Chapter III Common Sql*plus commands--01