Friends who are familiar with Oracle know that SQL Plus is an important tool for dealing with Oracle. SQL Plus itself has a lot of commands to help us do a lot of things, including generating some specifications, providing human-computer interactions, etc. Below, I will simply explain it for your reference:
A help order
Help can help us, find the usage of each command of SQL Plus
Example:
SQL> Help Accept
ACCEPT
--------
Reads a line of infut and stores it in a given user variable.
ACC [EPT] Variable [NUM [BER] | char | DATE] [for [mat] format]
[Def [AULT] Default] [Prompt text | NOPR [OMPT]] [Hide]
Two SQLPLUS human machine interaction command
You can accept the user's input in SQLPLUS, the most common commands:
(1) accept
Accept the data of the whole line.
Syntax: ACC [EPT] Variable [NUM [BER] | char | DATE] [for [mat] format]
[Def [AULT] Default] [Prompt text | NOPR [OMPT]] [Hide]
for example:
Corner 1. SQL, Isolated the employee greater than the input amount.
Set feedback off
Set Pause Off
Accept Sal Number Prompt 'Please Input Salry (Weekly):'
SELECT Employee_ID, Last_Name, First_Name, Salary from Employee
WHERE SALY> & SAL;
Run in SQLPlus
SQL> @c: /1.sql
Please Input Salry (Weekly): 1000
Old 2: Where Salary> & Sal
NEW 2: Where Salary> 1000
Employee_id last_name first_name salary
---------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------
7499 Allen Kevin 1600
7505 Doyle Jean 2850
7506 Dennis Lynn 2750
7507 BAKER LESLIE 2200
7521 Ward Cynthia 1250
7555 Peters Daniel 1250
7557 Shaw Karen 1250
7560 Duncan Sarah 1250
7564 LANGE GREGORY 1250
7566 Jones Terry 2975
7569 Alberts Chris 3000
Note:
1 Accept accepts the input of a row, and put the input data to the variable according to the specified type (if not existing, automatically created). 2 Fomrat is used to develop the input data format, such as numbers can specify '999.99', characters can specify length 'A10'
3 If the type is char, the length is up to 240.
(2) PROMPT
Output prompt information.
Syntax: Pro [MPT] [Text]
Example:
SQL> Prompt Hello, World
Hello, World
(3) PAUSE
Output a line of information and wait for the user to enter the carriage return.
Syntax: PAU [se] [text]
Example:
SQL> Pause please press [Return]
Please press [Return]
SQL>
Three editorial commands
The last SQL statement sent by SQL PLUS is saved within a data buffer of SQL Plus, while the SQLPlus command can be edited by editing commands.
(1) /
Re-execute the SQL statement.
(2) append
Add characters in the command buffer.
Example:
SQL> SELECT * from Tab;
.
SQL> a where tabtype = 'view'
1 * Select * from tabwhere tabtype = 'view'
SQL> /
TName Tabtype ClusterID
----------------------------------------------
PLSQL_PROFILER_GRAND_TOTAL VIEW
PLSQL_PROFILER_LINES_CROSS_RUN VIEW
PLSQL_PROFILER_NOTEXEC_LINES VIEW
PLSQL_PROFILER_UNITS_CROSS_RUN VIEW
Sales view
(3) List or;
List the contents in the buffer, and the list can list the contents of a row, and it is all.
Example:
SQL>;
1 SELECT * from Tab Where Tabtype = 'View'
2 * Order by TName
SQL> L 2
2 * Order by TName
(4) INPUT
Increase a line of content
Example:
SQL> i Order by TNAME
SQL> L
1 SELECT * from Tab Where Tabtype = 'View'
2 * Order by TName
(5) Change
Replace the content of the first one of the current line to replace the new content,
Syntax: c [hange] sepchar old [sepchar [new [sepchar]]]]]]
Example:
SQL> L
1 * Select * from Employee WHERE SALY> 1000 and Salary <2000
SQL> C / Salary / Sal
1 * Select * from Employee WHERE SAL> 1000 and Salary <2000
Note that when there is two lines in the buffer, the content in the last row is replaced.
(6) Edit
In the specified text editor, edit the contents of the buffer. On Windows, the default is NOTEPAD.
(7) spool
Put the output result, specify it into a file, a bit like the DOS command> SPO [ol] [file_name [.ext] | Off | OUT]
SQL> Spool C: /1.txt
SQL> SELECT * from Tab;
SQL> spool off
Four execution commands
(1) @
Load corner, run.
Example:
SQL> @c: /1.sql
(2) Execute
Run the PL / SQL process and package.
to be continued.
ENHYDRABOY
ENHYDRABOY@yahoo.com.cn
