SQL * Plus command usage
??
??? 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 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 will be 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, COLUMN_HEADING
For example:
SQL> SELECT * from dept;
??? Deptno dname? ???????????? Log
---------- -------------------------------------
???? 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 EMP
Department ?? Name??????? Salry
---------- --------------------
???? 10 aaa????????? 11?????
SQL> Column Ename Heading 'Employee | Name'
SQL> SELECT * FROM EMP
??????? Employee
Department ?? Name??????? Salry
---------- ---------- ---------- ??
???? 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??????????????????
----------? --------------------------------------- -????? ---------
??? 7369 smith???????????? Clerk
??? 7499 Allen????????????????
? ?? 7521 ward????????????????
??
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????????????????
7521 Ward????????????????
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 Allen7521 Ward
??
6). Format the display of Number Types:
SQL> Column Sal Format $ 99,990
SQL> /
EMPLOYEE
Department name????? Salary?? Commission
---------- ----------------------------
30?????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
??
7). When the column value is displayed, if the column value is 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 ??? Entame? ?? ???
---------- -------------------
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 Text
??
8). How many numbers have been set one page
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 line is greater than the length of the set row (set with the set linesize n command), when the SET WRAP is ON, the output row is displayed separately, otherwise, the output line will be output. 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 in a file, the content output will take a lot of time on the screen. After setting the set termspool off, the output content will only be saved in the output file. Will not appear on the screen, greatly increase 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 DePT
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???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
??
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_name
??
twenty one. 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
