Oracle's SQL * PLUS is a client tool that interacts with Oracle. In SQL * Plus, you can run the SQL * plus command to the SQL * PLUS statement.
We usually say DML, DDL, DCL statements are SQL * PLUS statements, they can be saved in a memory area called SQL Buffer, and can only save a recently executed SQL statement, we You can modify the SQL statement saved in SQL Buffer and then execute again, SQL * Plus is generally dealing with the database.
In addition to the SQL * PLUS statement, other statements executed in SQL * Plus We call SQL * Plus commands. After they are executed, they are not saved in the memory area of SQL Buffer, which is generally used to format the results of the output to make reports.
Let's introduce some common SQL * Plus commands:
1. Perform a SQL script file
SQL> Start file_name
SQL> @ file_name
We can save multiple SQL statements in a text file so that when all SQL statements in this file are executed, use any of the above commands, which is similar to the batch in DOS.
2. Edit the current input
SQL> Edit
3. Run the last run SQL statement
SQL> /
4. Output 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. Turn off Spool output
SQL> Spool off
Only the output content is only seen in the output file only if the Spool output is turned off.
6. Display a structure of a table
SQL> Desc Table_Name
7. COL command:
The display form of the main formatted column.
There are many options that this command is as follows:
Col [umn] [{colorn | evr} [option ...]]
The Option option can be the following clause:
Ali [as] alias
CLE [AR]
Fold_a [fter]
Fold_b [efore]
For [Mat] Format
HEA [ding] text
Jus [TIFY] {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 header
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 ENAME to the new column name Employee Name and put the new column name on both lines:
SQL> Select * from EmpDepartment Name Salry
---------- --------------------
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 alignment of the column header
Jus [TIFY] {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, column headers are default on the right, other types of columns are default on the left
5) Don't let a column 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 Types:
SQL> Column Sal Format $ 99,990
SQL> /
EMPLOYEE
Department Name Salary Commsion
---------- ----------------------------
30 Allen $ 1,600 300
7). When the column value is displayed, if the column value is a null value, use the text value to replace the null value comm Nul [l] text
SQL> Colone Nul [L] Text
8). Set up a column winding method
WRA [PPED] | Wor [D_Wrapped] | TRU [ncated]
COL1
--------------------
How are you?
SQL> Col Col1 Format A5
SQL> Col col1 wrapped
COL1
-----
How a
Re yo
U?
SQL> col col1 word_wrapped
COL1
-----
HOW
ARE
YOU?
SQL> col col1 word_wrapped
COL1
-----
How a
9). Display the current display attribute value of the column
SQL> Column Column_name
10). Set all columns to the display attribute to default
SQL> Clear Columns
8. Shield off the same value displayed in a column
Break on Break_Column
SQL> BREAK on Deptno
SQL> Select Deptno, ENAME, SAL
From EMP
WHERE SAL <2500
ORDER by deptno;
Deptno ename
---------- -------------------
10 CLARK 2450
Miller 1300
20 Smith 800
ADAMS 1100
9. In the display of the same value displayed in a column, the NS is inserted before the value change is changed.
Break on Break_Column Skip N
SQL> Break on Deptno Skip 1
SQL> /
Deptno ename
---------- -------------------
10 CLARK 2450
Miller 1300
20 Smith 800
ADAMS 1100
10. Show settings to BREAK
SQL> BREAK
11. Delete 6,7 settings
SQL> CLEAR BREAKS
12. SET command:
This command contains many subcommands:
Set System_Variable Value
System_variable value can be one of the following clauses:
Appi [NFO] {ON | OFF | TEXT}
Array [Size] {15 | N}
Auto [Commit] {ON | OFF | IMM [ediate] | n}
AutoP [RINT] {ON | OFF}
Autorecovery [ON | OFF]
Autot [RACE] {ON | OFF | Trace [Only]} [Exp [Lain]] [stat [iStics]]
BLO [ckterminator] {. | C}
CMDS [EP] {; | C | ON | OFF}
Colsep {_ | text}
COM [PATIBILITY] {V7 | V8 | Native}
CON [CAT] {. | C | ON | OFF}
Copyc [ommit] {0 | n}
CopyTypeCheck {on | OFF}
DEF [INE] {& | C | ON | OFF}
Describe [Depth {1 | N | All}] [LINENUM {on | OFF}] [indent {on | OFF}]
Echo {on | OFF}
Editf [il] file_name [.ext]
EMB [edde] {on | OFF}
ESC [APE] {/ | C | ON | OFF}
Feed [back] {6 | N | on | OFF}
Flagger {OFF | Entry | Intermediate [IATE] | FULL}
Flu [SH] {ON | OFF}
HEA [ding] {on | OFF}
Heads [EP] {|| c | on | OFF}
Instance [instance_path | local]
Lin [eSize] {80 | n}
Lobof [fset] {n | 1}
Logsource [Pathname]
Long {80 | n}
Longc [hunksize] {80 | n}
Mark [UP] HTML [ON | OFF] [Head Text] [Body Text] [ENTMAP {ON | Off}] [SPOOL
{On | OFF}] [pre [format] {on | OFF}]
NEWP [AGE] {1 | N | NONE}
Null text
Numf [omat] format
Num [width] {10 | n}
Pages [Ize] {24 | N}
PAU [SE] {ON | OFF | text}
Recsep {WR [APPED] | EA [CH] | OFF}
Recsepchar {_ | C}
Serverout [PUT] {on | Off} [size n] [for [MAT] {WRA [PPED] | Wor [D_
Wrapped] | TRU [ncated]}]
Shift [inout] {VIS [ible] | INV [isible]}
Show [Mode] {ON | OFF}
SQLBL [Anklines] {ON | OFF}
SQLC [ASE] {Mix [ED] | LO [WER] | UP [PER]}
SQLCO [NTINUE] {> | Text}
SQLN [UMBER] {ON | OFF}
Sqlpre [fix] {# | c}
SQLP [ROMPT] {SQL> | text}
SQLT [Erminator] {; | c | on | OFF}
SUF [fix] {sql | text}
Tab {on | OFF}
Term [out] {ON | OFF}
Ti [ME] {ON | OFF}
TIMI [NG] {ON | OFF}
Trim [out] {on | OFF}
Trims [pool] {on | OFF}
Und [Erline] {- | C | ON | OFF}
Ver [IFY] {ON | OFF}
WRA [P] {ON | OFF}
1). Set whether the current session is automatically submitted to the modified data
SQL> SETO [COMMIT] {ON | OFF | IMM [ediate] | n}
2). When performing a SQL script with the start command, whether the SQL statement in the script is displayed in the script
SQL> Set echo {on | OFF}
3) Whether the number of rows that current SQL statements query or modify
SQL> Set feed [back] {6 | N | ON | OFF}
The default only shows the number of lines of the results in the result of greater than 6 rows. If SET feedback 1, no matter how many rows are queried. When it is OFF, the number of lines of the query will not be displayed.
4) Whether to display the column header
SQL> Set Hea [Ding] {ON | OFF}
When SETHEADING OFF, the column header is not displayed on the top of each page, but instead of blank line.
5). Set the number of characters that can be accommodated
SQL> SET Lin [Esize] {80 | N}
If the output content of a row is greater than the number of characters that can be accommodated, the fold is displayed.
6). Settings between page and pages
SQL> Set newp [age] {1 | n | none}
When SET NewPage 0, there is a small black box at the beginning of each page.
When SET NewPage N, there is a n space between the pages and pages.
When Set NewPage None, there is no interval between page and pages.
7). When displaying, use the TEXT value to replace the NULL value.
SQL> Set Null Text8). Setting up the number of rows of numbers
SQL> SET PAGES [Ize] {24 | N}
If set to 0, all output content is a page and does not display columns
9). Whether to display the information output with the dbms_output.put_line package.
SQL> SET Serverout [PUT] {ON | OFF}
When writing a stored procedure, we sometimes use dbms_output.put_line to output the necessary information to debug the stored procedure, only after setting the serveroutput variable to ON, information can be displayed on the screen.
10). When the length of the SQL statement is greater than the LINSIZE, it is intercepted the SQL statement when displayed.
SQL> SET WRA [P] {ON | OFF}
When the length of the output row is greater than the length of the set row (set with the set lineize n command), when the SET WRAP is ON, the output row will be displayed separately, otherwise, more than one is more than Character resection, not displayed.
11). Whether to display the output on the screen, mainly used in conjunction with SPOOL.
SQL> Set Term [OUT] {ON | OFF}
When using the spool command to output a large table to a file, the content output will take a lot of time on the screen, and then set the set termspool off, the output content will only be saved in the output file. Displayed on the screen, greatly enhances the speed of Spool.
12). Remove the extra spaces after each line in the spool output
SQL> Set Trims [out] {on | OFF}
13) Show the execution time of each SQL statement
Set timing {on | OFF}
14. Modify the first string that appears in the current line in SQL Buffer
C [hange] / old_value / new_value
SQL> L
1 * SELECT * from de PEPT
SQL> C / DEPT / EMP
1 * SELECT * FROM EMP
15. Edit SQL statement in SQL Buffer
EDI [T]
16. Displays the SQL statement in SQL Buffer, List N displays the Nth line in SQL Buffer and makes the nth line become the current line.
L [IST] [N]
17. Add or more lines below the current row of SQL Buffers
I [NPUT]
18. Add the specified text to the current line of SQL Buffer
A [ppend]
SQL> SELECT Deptno,
2 DNAME
3 from dept;
Deptno DNAME
---------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10 Accounting
20 Research
30 Sales
40 Operations
SQL> L 2
2 * DNAME
SQL> A, LOC
2 * DNAME, LOC
SQL> L
1 SELECT Deptno,
2 DNAME, LOC
3 * from dept
SQL> /
DEPTNO DNAME LOC
---------- ---------------------------
10 Accounting New York
20 Research Dallas
30 Sales Chicago
40 Operations Boston
19. Save the SQL statement in SQL BUFFER to a file
Save file_name
20. Import the SQL statement in a file into SQL Buffer
Get file_name21. Perform the SQL statement that has just been executed again
Run
oral
/
twenty two. Perform a stored procedure
Execute Procedure_name
twenty three. Connect to the specified database in SQL * Plus
Connect
User_name / passwd @ db_alias
twenty four. Set top headings for each report
Ttitle
25. Set the tail title of each report
Btitle
26. Write a comment
Remark [Text]
27. Out the specified information or an empty line to the screen
Prompt [Text]
28. Pause the execution process, wait for the user to continue execution
Pause [text]
SQL> PAUSE Adjust Paper and Press Return to Continue.
29. Copy some of the data in a database to another database (such as copying data of a table to another)
Copy {from database | To database | From database to database}
{Append | crete | insert | replace} destination_table
(Column, Column, Column, ...)] USING Query
SQL> COPY FROM
Scott / Tiger @ HQ To
John / chrome @ West
CREATE EMP_TEMP
Using SELECT * FROM EMP
30. Do not quit SQL * Plus, execute an operating system command in SQL * Plus:
Host
SQL> Host Hostname
This command may be supported under Windows.
31. In SQL * Plus, switch to the operating system command prompt, after running the operating system command, you can switch back to SQL * Plus again:
!
SQL>!
$ Hostname
$ EXIT
SQL>
This command is not supported under Windows.
32. Show the help of the SQL * Plus command
Help
How to install help files:
SQL> @? /Sqlplus/admin/help/hlpbld.sql? /Sqlplus/admin/help/helpus.sql
SQL> HELP INDEX
33. Display the value of the SQL * PLUS system variable or the value of the SQL * PLUS environment variable
Syntax
SHO [W] Option
WHERE OPTION REPRESENTS One of the Following Terms OR CLAUSES:
System_variable
All
BTI [TLE]
Err [ORS] [{Function | Procedure | Package | Package Body |
Trigger | View | Type | TYPE BODY} [Schema.] Name]
LNO
Parameters [parameter_name]
Pno
REL [EASE]
REPF [OOTER]
REPH [EAder]
SGA
Spoo [l]
Sqlcode
TTI [TLE]
User
1). Display the value of the current environment variable:
SHOW ALL
2). Display error information of the current object, stored procedure, trigger, package and other objects
Show Error
When you create a function, the stored procedure, etc., change can be used to view the error and the corresponding error message in that place, and compile it again.
3). Display the value of the initialization parameter:
Show parameters [parameter_name]
4). Display the version of the database: show rel [ease]
5) Display SGA size
Show sga
6) Show current username
SHOW User
