About Oracle (2)

SQL * Plus has two meanings: one refers to the SQL dialect of Oracle, and the other is the command line interface that only executes the dialect of oracle or similar command lines.
PL/SQL is complicated. For the orcale data programming language, please read Baidu encyclopedia.
The SQL * Plus command is a lot easier for others to Google.
This is also a conversion.
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 stored in an SQL BufferMemoryAnd only one recently executed SQL statement can be saved. We can modify the SQL statement stored in SQL buffer and execute it again. SQL * Plus generally deals with databases.

Apart from SQL * Plus statements, other statements executed in SQL * Plus are called SQL * Plus commands. After they are executed, they are not stored in the memory area of the SQL Buffer. They are generally used to format and display the output results to facilitate the preparation of reports.

The following describes some common SQL * Plus commands:

1. Execute an SQL script file

SQL> Start file_name

SQL> @ file_name

We can save multiple SQL statements in a text file, so that when we want to execute all the SQL statements in this file, we can use any of the following commands, which is similar to batch processing in DOS.

What is the difference between @ and?

@ Equals to the start command, used to run an SQL script file.

@ Command to call the script file in the current directory, or specify the full path, or you can use the sqlpath environment variable to search for the script file. This command is generally used to specify the full path of the file to be executed, otherwise the specified file is read from the default path (specified by the sqlpath variable.

@ Is used in the SQL script file to indicate that the SQL script file executed with @ is in the same directory as the file where @ is located, instead of specifying the full path of the SQL script file to be executed, or finding the SQL script file from the path specified by the sqlpath environment variable, this command is generally used in the script file.

For example, in the C:/TEMP directory, the start. SQL and nest_start. SQL files and the start. SQL script files are as follows:

@ Nest_start. SQL--equivalent to @ C:/temp/nest_start. SQL

In SQL * Plus, run the following command:

SQL> @ C:/temp/start. SQL

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

InScreenAll content on 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
Rising Kaka Free Download
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 column name to the new column name "employee name" 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 word_wrapped
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. The display of the same values displayed in a column is shielded above. Each time the column value changes, N blank rows are inserted before the value changes.
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