Oracle SQL * Plus set & spool introduction (1)

1) set whether the current session will automatically submit the modified data: Set auto [Commit] {on | off | Imm [ediate] | n}
SQL> set autocommit off

 

2) When you use the start command to execute an SQL script, whether to display the SQL statement being executed in the script: Set echo {on | off}
SQL> set echo on

 

3). Whether to display the number of rows queried or modified by the current SQL statement
SQL> Set feed [Back] {6 | n | on | off}
By default, only rows with more than 6 results are displayed. If set feedback 1 is returned, no matter how many rows are queried. When it is off, the number of queried rows is not displayed.

SQL> Set feed off
SQL> select * from dual;

D
-
X
SQL> Set feed on
SQL> select * from dual;

D
-
X

Select 1 line. (The difference is that)

 

4). Whether to display the column title
SQL> set hea [Ding] {on | off}
When set heading off, Column Titles are not displayed on each page, instead of blank rows.
SQL> set hea on
SQL> select * From;

Id sname age (column name)
----------------------------------------------------------------------
1. Do not boot 900
SQL> set heading off
SQL> select * From;

1. Do not boot 900

 

5) set the number of characters that a row can hold
SQL> set LiN [esize] {80 | n}
If the output content of a row is greater than the number of characters that can be accommodated in a set row, the line is displayed.

SQL> set Lin 20
SQL> select * From;

1
Do not start
900

 

6). Set the separation between pages
SQL> set newp [age] {1 | n | none}
When set Newpage 0, a small black box is displayed at the beginning of each page.

SQL> set Newpage 0
SQL> select * From;
1. Do not boot 900
When set Newpage N is set, there are n blank lines between the page and the page.

SQL> set Newpage 3
SQL> select * From;

 

1. Do not boot 900 (there are 3 blank lines in front)
When set Newpage none is set, there is no interval between the page and the page.

7). Replace the null value set null text with the text value during display (text indicates the text you want to fill in)
SQL> set null (fill empty fields with 'null)
SQL> select * From;
1. Do not boot 900
1 short-circuit 200
8 10 minutes null

 

8). Set the number of rows on a page: Set pages [ize] {24 | n}
If it is set to 0, all output content is one page and the column title is not displayed.

SQL> set pages 2 () -- Set 2 rows per page
SQL> set Newpage 0 -- set page Separation
SQL> select * From;
1. Do not boot 900
1 short-circuit 200
8 10 minutes null

 

9). Whether to display the output information using the dbms_output.put_line package. : Set serverout [put] {on | off}
When writing a stored procedure, we sometimes use dbms_output.put_line to output necessary information to debug the stored procedure. The information can be displayed on the screen only after the serveroutput variable is set to on.

SQL> set serveroutput on
SQL> begin
2 dbms_output.put_line ('output sucess ');
3 end;
4/
Output sucess --- (Display output, not displayed by default)

The PL/SQL process is successfully completed.

 

10) SQL> set wra [p] {on | off}
When the length of the output line is greater than the length of the Set line (set with the set linesize n command), when set wrap on, more than the characters of the output line will be displayed in another line, otherwise, the output line is removed from the output line and will not be displayed.

SQL> set line 30
SQL> set wrap off
SQL> select * From;
The row is truncated.

1. If set warp on is set, a new line is displayed)

 

11) When an empty row is encountered, the statement is not considered to have ended. The statement will be read from subsequent rows. : Set sqlblanklines on
In SQL * Plus, empty rows in the middle of SQL statements are not allowed, which is troublesome when copying scripts from other places to SQL * Plus. This command can solve this problem.

12). Whether to display the output content on the screen, mainly used in combination with spool. : Set term [out] {on | off}
When you use the spool command to output the content in a large table to a file, it takes a lot of time to output the content on the screen. After setting set termspool off, the output content is saved only in the output file and not displayed on the screen, greatly improving the spool speed.

13) Remove unnecessary spaces behind each row in the spool output: Set trims [out] {on | off}

14) display the execution time of each SQL statement: set timing {on | off}

SQL> set timing on
SQL> select * From;
1. Do not boot 900

Select 1 line.

Used time: 00: 00: 00.00

 

15). The output data is in HTML Format: Set markup html

SQL> set markup HTML on

SQL & gt; select * From;
<Br>
<P>
<Table border = '1' width = '000000' align = 'center' summary = 'script output'>
<Tr>
<TD align = "right">
1
</TD>
<TD>
Do not start
</TD>
<TD align = "right">
900
</TD>
</Tr>
</Table>
<P>

2 rows have been selected. <Br>
<Br>

<HTML data format is displayed>
Used time: 00: 00: 00.01