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

Today the Run command went on yesterday.

3.5 N (set current line) command and A (ppend) (Attach) command

Imagine that you entered the query statement for example 3-10

Example 3-10

Sql> SELECT ename  2 from  EMP;

Example 3-10 results

When you see the above output, you find that you have forgotten job,sal in the SELECT clause. How do you modify your SELECT clause at this point? First you should use the Sql*plus L (list) command to display the contents of the SQL buffer.

Example 3-11

Sql> L

Example 3-11 results

In the result shown in example 3-11, the "*" After 2 indicates the second behavior of the current row. The result shown in example 3-11 finds that SELECT ename is the first row in the SQL buffer. In order to add ", job,sal" after ename, you should set the first line to the current line. Then enter 1, as in example 3-12, which resets the first line to the current line.

Example 3-12

Sql> 1

Example 3-12 results

Example 3-12 shows the result that the first row of the SQL buffer has been successfully set to the current row. You can now add ", Job,sal" to the select ename by using example 3-13 of the A command (additional command).

Example 3-13

Sql> A,job,sal

Example 3-13 results

You should then use the l command in example 3-14 to check if the changes you made are correct

Example 3-14

Sql> L

Example 3-14 results

Seeing the results shown in example 3-14, you find that the modified query statement is exactly what you want, so you enter the Execute command (/or R) again to rerun the query statement in the SQL buffer. This time you will get the results you need, such as example 3-15

Example 3-15

Sql> R

Example 3-15 results

This is exactly the same as the result of executing the SQL statement immediately after we enter it.

n is used to specify the current line of nth behavior, where n is the natural number. So what if you want to insert a row of data before the first line? You can use 0 text to insert a row of data before the first line.

What if we find that the contents of a row in the SQL buffer need to be removed?

3.6 del command

You can use del N to delete the nth row. If n is not specified, the current row is deleted. You can also use the del m,n command to delete all the contents of rows from M to N. To demonstrate how to use this sql*plus command, you can re-enter SQL statements like example 3-16 that are almost identical to example 3-5.

Example 3-16

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

To determine exactly which line number you want to delete, you can use the Sql*plus command for example 3-17 again.

Example 3-17

Sql> L

Example 3-17 results

Assuming that the EMP table is a large table, in order to improve the efficiency of the query, you decide to remove the ORDER BY clause, you can use the Sql*plus command in example 3-18 to complete this decision.

Example 3-18

Sql> del 4

It is still time to use the Sql*plus l command in example 3-19 to check if the operation you have made is successful.

Example 3-19

Sql> L

Example 3-19 results

Example 3-19 shows that the result table name has successfully deleted the fourth row in the SQL buffer that contains the Orde by clause. At this point, you can run the sentence again using the Sql*plus "/" command in Example 3-20.

Example 3-20

Sql>/

Example 3-20 results

It is obvious that example 3-20 shows that the result is unordered and its legibility is reduced. But sometimes for the overall efficiency of the system, it is inevitable to sacrifice some of the results of the query readability. This is the same as what society often says, "sacrifice small family for everyone".

Here we do not give an example of the Del M,n command and the Del command, because their usage is very similar to the del M command. If the reader is willing to go, you can try it yourself.

In addition to the modifications and deletions described above, are there any other Sql*plus commands to complete the relevant operation?

3.7 C (hange) command

You can also use the C (hange)/original/new command to modify the statements in the SQL buffer. is to replace the original text with "new body" in the current line.

To demonstrate the use of this command, you can re-enter the exact same SQL statement as in example 3-5, example 3-21.

Example 3-21

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

Example 3-21 results

Now try to change the dept of the second row in the SQL buffer to EMP with the C[hange command you just learned, using the Sql*plus command like Example 2-22.

Example 3-22

Sql> c/dept/emp

Example 3-22 results

Example 3-22 shows the result you are surprised. Because you have an amazing memory, you are confident that the Sql*plus command you entered does not have any errors. In fact, example 3-22 of the Sql*plus command is completely correct, but the current row is not the second row, that is, does not contain dept, so it caused the "string not found" error. You can now enter the Sql*plus command for example 3-23 to set the second row of the SQL buffer to the current row.

Example 3-23

Sql> 2

Example 3-23 results

You can then re-enter exactly the same as example 2-22, as in example 2-24 of the Sql*plus command.

Example 3-24

Sql> c/dept/emp

Example 3-24 results

Example 3-24 shows the result table name you have successfully modified the Dept of the second row in the SQL buffer to EMP. But for the sake of caution, you should still use the L command in example 3-25 to verify this.

Example 3-25

Sql> L

Example 3-25 results

If you then use the "/" command in Example 3-26, you will get exactly the same result as example 3-9.

Example 3-26

Sql> R

Example 3-26 results

If the demerit you want to output is sorted by size only by salary (SAL), you should first use the Sql*plus command in example 3-27 to set the fourth row in the SQL buffer to the current row.

Example 3-27

Sql> 4

Example 3-27 results

You can then use the C command in example 3-28 to delete the job from line Fourth of the SQL buffer.

Example 3-28

Sql> c/job,/

Example 3-28 results

You should now use the L command in example 3-29 to verify that the modification was successful.

Example 3-29

Sql> L

Example 3-29 results

Finally, you can use the/command in example 3-30 to run the statements in the buffer.

Example 3-30

Sql>/

Example 3-30 results

As you can see from the discussion in this section, it may be more convenient to use the C command in some cases to modify or delete an operation than to use a different command.

3.8 How to generate a script

In order to demonstrate the multiply build script file, you can re-enter the query statement for example 3-31.

Example 3-31

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

Example 3-31 results

Now you can enter the Sql*plus command in Example 3-32, save the SQL buffer statement in the D:\SQL\SAMPLE.sql file, which is also called the script file.

Note : Before executing the sqlxplus command, you will first use system commands to create the D:\SQL directory (ask a price clip), meaning that the sql*plus command does not automatically create a nonexistent folder for you.

Example 3-32

Sql> SAVE D:\SQL\SAMPLE

Example 3-32 results

The Save command stores the contents of the SQL buffer in the specified file, which is called a script file. At this point, if you use the Body Editor to open the file D:\SQL\SAMPLE.sql, you will see the following in the file

Example 3-33

To test the Sqlxplus l command, you will see that the SQL statement you entered previously is not sql*plus command save D:\SQL\SAMPLE. This indicates that the Sql*plus command is not stored in the SQL buffer. Take a look at example 3-34.

Example 3-34

Sql> L

Example 3-34 results

Now if you enter the SQL statement for example 3-35, what changes will be found in the SQL buffer?

Example 3-35

Sql> SELECT *  2 from  dept;

Example 3-35 results

After the Sql*plus l command of Example 3-36, you will find that the contents of the SQL buffer inch have changed to the statement you just entered.

Example 3-36

Sql> L

Example 3-36 results

The above example also proves that the SQL buffer can only store one SQL statement.

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

Today is Sunday, learn some more. ^_^

-----------------------------------------------------------

Previous: Oracle Learning Chapter three commonly used Sql*plus command--01

Next article:

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