SQL plus command (1)

Oracle SQL * plus is a client tool that interacts with oracle. In SQL * plus, you can run the SQL * plus command and the SQL * plus statement.
The DML, DDL, and DCL statements we usually call are SQL * plus statements. After they are executed, they can all be saved in
In the memory region called SQL buffer, the latest SQL statement can only be saved.
SQL statements in SQL buffer are modified and executed again. SQL * plus generally deals with databases.
Apart from SQL * plus statements, other statements executed in SQL * plus are called SQL * plus commands. They execute
After completion, it is not stored in the memory area of the SQL buffer. They are generally used to format and display the output results,
This allows you to easily create reports.
The following describes some common SQL * plus commands:
1. Execute an SQL script file
SQL> start file_name
SQL> @ file_name
You can save multiple SQL statements in a text file.
When using a sentence, use any of the following commands, which is similar to the batch processing in dos.
2. Edit the current input.
SQL> edit
3. Run the last SQL statement again.
SQL>/
4. output the displayed content to the specified file.
SQL> SPOOL file_name
All content on the screen is included in this file, including the SQL statement you entered.
5. Disable spool output
SQL> SPOOL OFF
The output content is displayed in the output file only when spool output is disabled.
6. display the structure of a table
SQL> desc table_name
7. COL command:
It mainly formats the display of columns.
This command has the following options:
COL [UMN] [{column | expr} [option...]
The Option can be the following clause:
ALI [AS] alias
CLE [AR]
FOLD_A [FTER]
FOLD_ B [EFORE]
FOR [MAT] format
HEA [DING] text
JUS [cipher] {L [EFT] | C [ENTER] | C [ENTRE] | R [IGHT]}
LIKE {expr | alias}
NEWL [INE]
NEW_V [ALUE] variable
NOPRI [NT] | PRI [NT]
NUL [L] text
OLD_V [ALUE] variable
ON | OFF
WRA [PPED] | WOR [D_WRAPPED] | TRU [NCATED]
1). Change the default Column Title
COLUMN column_name HEADING column_heading
For example:
SQL> select * from dept;
DEPTNO DNAME LOC
-----------------------------------------------
10 ACCOUNTING NEW YORK
SQL> col LOC heading location
SQL> select * from dept;
Deptno dname location
-----------------------------------------------
10 ACCOUNTING NEW YORK
2) change the NAME of the column to the new column NAME "EMPLOYEE" and put the new column NAME on two rows:
SQL> select * from emp
Department name Salary
------------------------------
10 aaa 11
SQL> COLUMN ENAME HEADING 'employee | name'
SQL> select * from emp
Employee
Department name Salary
------------------------------
10 aaa 11
Note: the col heading turn into two lines from one line.
3). Change the display length of the column:
FOR [MAT] format
SQL> select empno, ename, job from emp;
EMPNO ENAME JOB
-----------------------------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
SQL> col ename format a40
EMPNO ENAME JOB
-----------------------------------------------------------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
4). Set the column title alignment
JUS [cipher] {L [EFT] | C [ENTER] | C [ENTRE] | R [IGHT]}
SQL> col ename justify center
SQL>/
EMPNO ENAME JOB
-----------------------------------------------------------
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
For NUMBER-type columns, the column title is on the right by default, and for other types of columns, the column title is on the left by default.
5). prevent a column from being displayed on the screen.
NOPRI [NT] | PRI [NT]
SQL> col job noprint
SQL>/
EMPNO ENAME
--------------------------------------------------
7369 SMITH
7499 ALLEN
7521 WARD
6). format the display of NUMBER columns:
SQL> COLUMN SAL FORMAT $99,990
SQL>/
Employee
Department Name Salary Commission
---------------------------------------
30 ALLEN $1,600 300
7). If the column value is NULL when the column value is displayed, replace the NULL value with the text value.
Comm nul [L] text
SQL> COL COMM NUL [L] text
8). Set the rewinding Method for a column.
WRA [PPED] | WOR [D_WRAPPED] | TRU [NCATED]
COL1
-------------------
How are you?
SQL> COL COL1 FORMAT A5
SQL> COL COL1 WRAPPED
COL1
-----
HOW
RE YO
U?
SQL> COL COL1 WORD_WRAPPED
COL1
-----
HOW
ARE
YOU?
SQL> COL COL1 TRUNCATED
COL1
----
HOW
9). display the current display attribute value of the column
SQL> COLUMN column_name
10). Set the display attribute of all columns to the default value.
SQL> CLEAR COLUMNS
8. Block the same value displayed in a column
Break on break_column
SQL> BREAK ON DEPTNO
SQL> SELECT DEPTNO, ENAME, SAL
FROM EMP
Where sal <1, 2500
Order by deptno;
DEPTNO ENAME SAL
-----------------------------
10 CLARK 2450
MILLER 1300
20 SMITH 800
ADAMS 1100
9. Shielding the display of the same value displayed in a column above, insert n before the value change whenever the column value changes
Empty rows.
Break on break_column SKIP n
SQL> BREAK ON DEPTNO SKIP 1
SQL>/
DEPTNO ENAME SAL
------------------------------
10 CLARK 2450
MILLER 1300
20 SMITH 800
ADAMS 1100
10. Display BREAK settings
SQL> BREAK
11. Delete settings 6 and 7
SQL> CLEAR BREAKS